mysql5.7配置主从复制

矫情吗；* 2022-07-12 10:55 219阅读 0赞

**1.先登陆master，检查bin\_log是否开启**  
$ mysql -uroot -h127.0.0.1 -p

$ mysql> show binary logs;

如果已开启，则重启二进制日志

$ mysql> reset master;

**2.配置master**  
vim /etc/my.cnf    
\#\[必须\]启用二进制日志    
log-bin=mysql-bin    
\#\[必须\]服务器唯一ID，默认是1，一般取IP最后一段(一定要配置，否则启动报错)

server-id=106

**3.配置slave**  
vim /etc/my.cnf    
\#\[可选\]启用二进制日志(如果从不需要作为别的主，不需要配置)  
log-bin=mysql-bin    
\#\[必须\]服务器唯一ID，默认是1，一般取IP最后一段    
server-id=103  
\#\[可选\]只读

read-only

**4.重启服务**

$ service mysqld restart

**5.创建主从复制账号，这里要注意IP的设置**  
$ mysql> grant replication client,replication slave on \*.\* to repl@'192.168.1.%' identified by 'repl';

$ mysql> FLUSH PRIVILEGES;

**6.查看master服务器的主从复制状态，找出File和偏移量**  
$ mysql> show master status;  
\+------------------+----------+--------------+------------------+-------------------+  
| File             | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB | Executed\_Gtid\_Set |  
\+------------------+----------+--------------+------------------+-------------------+  
| mysql-bin.000002 |      467 |              |                  |                   |  
\+------------------+----------+--------------+------------------+-------------------+  
  
**7.在从服务器上面启动主从复制,注意master\_log\_file/master\_log\_pos参数都是上面查询得来的**  
$ mysql> change master to master\_host='192.168.1.106',master\_user='repl',master\_password='repl',master\_port=3306,master\_log\_file='mysql-bin.000002',master\_log\_pos=467;  
$ mysql> start slave;  
$ mysql> show slave status\\G;  
  
**如果这两项都是YES，说明主从复制ok了，**  
Slave\_IO\_Running: Yes  
Slave\_SQL\_Running: Yes  
  
**8.在主库写入，进行测试  
  
9.重置主从复制(如果需要，此时主从复制已经配置完毕)**  
9.1 重置slave (如果仅仅是将从库提升为主库，执行完下列指令，再将read-only配置去掉重启即可)  
$ mysql> stop slave io\_thread; (确定看到输出Slave has read all relay log; waiting for more updates)  
$ mysql> stop slave;  
$ mysql> reset master;  
$ mysql> reset slave all; (删除从库的replication参数，show slave status\\G;的信息返回空)  
  
9.2 重置master  
$ mysql> reset master;

9.3 重新执行一次主从复制的配置流程

原文出自：[http://blog.csdn.net/daiyudong2020/article/details/56314142][http_blog.csdn.net_daiyudong2020_article_details_56314142]

End;

[http_blog.csdn.net_daiyudong2020_article_details_56314142]: http://blog.csdn.net/daiyudong2020/article/details/56314142