搭建mysql主从复制(主主复制)
1:设主库允许远程连接(注意:设置账号密码必须使用的插件是mysql_native_password,其他的会连接失败)
#切换到mysql这个数据库,修改user表中的host,使其可以实现远程连接 mysql>use mysql; mysql>update user set host ="%" where user ="root"; #改完之后一定要刷新信息 mysql>flush privileges;
2:主库开启bin-log日志在my.cnf的[mysqld]下添加
#修改主服务器Master #修改配置文件 vim /etc/my.cnf #主服务器唯一ID,这个id可以随便修改,只要不跟从服务器重复,注意连接是-,不是_ server-id=1 #启用二进制日志 log-bin=mysql-bin
2-1:重启主库mysql
[root@localhost ~]# systemctl retart mysql
2-2:查看主库server 相关信息
mysql> show global variables like '%server%'; +---------------------------------+--------------------------------------+ | Variable_name | Value | +---------------------------------+--------------------------------------+ | character_set_server | utf8mb4 | | collation_server | utf8mb4_0900_ai_ci | | innodb_dedicated_server | OFF | | innodb_ft_server_stopword_table | | | server_id | 1 | | server_id_bits | 32 | | server_uuid | b434ab49-df79-11ee-9c39-0242ac110002 | +---------------------------------+--------------------------------------+ 7 rows in set (0.00 sec) mysql>
2-2:查看主库是否开启bin-log日志
mysql> show global variables like '%log%';
(图片来源网络,侵删)
2-3:查看主库的最新状态
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000004 | 157 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> FLUSH LOGS; Query OK, 0 rows affected (0.01 sec) mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000005 | 157 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql>
2-4:查看全部的二进制文件,可以选择一步一步恢复
mysql> SHOW BINARY LOGS; +------------------+-----------+-----------+ | Log_name | File_size | Encrypted | +------------------+-----------+-----------+ | mysql-bin.000001 | 204 | No | | mysql-bin.000002 | 204 | No | | mysql-bin.000003 | 204 | No | | mysql-bin.000004 | 204 | No | | mysql-bin.000005 | 157 | No | +------------------+-----------+-----------+ 5 rows in set (0.00 sec) mysql>
3:从库开启bin-log日志在my.cnf的[mysqld]下添加
#修改从服务器slave 修改配置文件:vim /etc/my.cnf #从服务器唯一ID,注意连接是-,不是_ server-id=2 #启用中继日志 relay-log=mysql-relay
3-1:查看主库是否开启bin-log日志,从库也是一样
mysql> show global variables like '%log%';
4:在主库创建一个账号slave,密码123456,并且授权连接
mysql>GRANT REPLICATION SLAVE ON *.* to 'slave'@'%' identified by '123456';
4-1:在从库指定主库(添加 主节点主机,访问主节点的用户名及密码,主节点二进制文件信息,注意:主节点的二进制文件一定要是二进制列表中的最后一个二进制文件。如果需要设置主主就是主库也执行如下的命令,ip,账号,密码换成从库的)
mysql> change master to master_host='192.168.11.47',master_user='root',master_password='root',master_log_file='mysql-bin.000005',master_log_pos=157; Query OK, 0 rows affected, 8 warnings (0.01 sec)
5:检查从库复制功能状态(注意:Slave_IO_Running,Slave_SQL_Running,两个线程必须为yes状态,否则主从复制搭建不成功)
show slave status \G;
5-1:先停止从库的复制功能
mysql>stop slave;
5-2: 如果报错,异常原因,从库已经存在之前的relay log
mysql> start slave; ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
5-3:解决方案,使用reset slave语句,清除master信息和relay日志信息,并创建全新的中继日志.
mysql> stop slave; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> reset slave; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> change master to master_host='192.168.11.47',master_user='root',master_password='root',master_log_file='mysql-bin.000005',master_log_pos=157; Query OK, 0 rows affected, 8 warnings (0.02 sec) mysql> start slave; Query OK, 0 rows affected, 1 warning (0.01 sec)
5-4:再启动从库的复制功能
mysql>start slave;
5-5:再查看从库的状态
show slave status \G;
6:keepAlived的配置文件权限必须是644
(图片来源网络,侵删)
(图片来源网络,侵删)
文章版权声明:除非注明,否则均为主机测评原创文章,转载或复制请以超链接形式并注明出处。
还没有评论,来说两句吧...