MySql 主从复制操作步骤

偏执的太偏执、 2022-11-15 05:57 261阅读 0赞

我们所用的单机mysql不能做高可用，高并发，那么解决该问题需要进行集群，集群就必须先做到主从复制

主从复制顾名思义就是主库作为和后台直接对接的库，从库一般是作为备份，当读写分离实现后，从库可以作为读库，主库既可以读，也可以写。

主从原理：

Mysql 进行sql更新插入操作 事务提交会记录到binLog日志中，从库通过I/O线程会读取binLog日志，将数据同步到relay日志中，sql线程再读取relay日志生成对应操作sql，在从库执行。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JveXdjeA_size_16_color_FFFFFF_t_70][]

操作步骤：

关闭主从库防火墙。避免端口无法被访问，由于主从库一般是在同一个内网中的，关闭防火墙并无大碍

centos7:sudo systemctl disable firewalld

主库操作：

1：要开启二进制binLog日志

(1):修改主库my.cnf文件

在\[mysqld\]中加入

#启用二进制日志
    log-bin=mysql-bin
    #服务器唯一ID，一般取IP最后一段
    server-id=133
    #指定复制的数据库(可选)
    binlog-do-db=kkb2
    binlog-ignore-db=kkb(和replicate-ignore-db是一样的)
    #指定不复制的数据库(可选，mysql5.7版本及以上)
    replicate-ignore-db=kkb
    #指定忽略的表(可选，mysql5.7版本及以上)
    replicate-ignore-table=db.table1

2：重新启动mysql服务

sudo systemctl restart mysql

3:进到mysql的命令行  进行主库给从库授权备份

mysql >  GRANT REPLICATION SLAVE ON \*.\* TO 'root'@'%' identified by 'root';

mysql >  GRANT ALL PRIVILEGES ON \*.\* TO 'root'@'%' identified by 'root';

mysql >  FLUSH PRIVILEGES;  //刷新权限

4:查看主库的状态

mysql >  show master status;

![20210331153225685.png][]

File：binlog日志名称  注意：binlog日志的名称只要数据库重启就会生成新的名称

Position：定位从库开始读的位置  也就是394之前的sql是不会同步到从库中的，如果要同步之前的数据，使用mysql dump进行复制即可

从库操作：

1：修改my.cnf文件

\[mysqld\]

server-id=135  \#服务器唯一ID，一般取IP最后一段

2：查看以前的slave状态，如果没有设置过是没

mysql >  show slave status \\G；

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JveXdjeA_size_16_color_FFFFFF_t_70 1][]

3：关闭从库

mysql >  stop slave

4：初始化重置从库的状态

mysql>change master to
    master_host='192.168.56.101',
    master_port=3306, 
    master_user='root',
    master_password='root',
    master_log_file='mysql-bin.000059',
    master_log_pos=394;

mysql>change master to  
master\_host='192.168.56.101',      \#主库的地址IP  
master\_port=3306,        \#主库数据库端口          
master\_user='root',       \#主库用户账号  
master\_password='root',    \#主库用户密码  
master\_log\_file='mysql-bin.000059',   \#主库binLog日志名称  
master\_log\_pos=394;         \#从库进行同步主库binLog日志开始的位置

5：启动从库

mysql>  start slave;

6：查看从库是否开启成功

mysql> show slave status \\G；

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JveXdjeA_size_16_color_FFFFFF_t_70 2][]

两个都为yes说明从库开启成功

此时两个库的数据会进行同步成功

这里需要说明一下，数据库表/存储过程等在主从同步时需要一致，然后再进行主从同步，不然在处理主库的一些sql时在从库会进行报错，然后从库就不会再进行同步了

可以设置global sql\_slave\_skip\_counter，进行忽略几次错误，但是建议不开。

mysql> set global sql\_slave\_skip\_counter =1; \# 忽略一个错误  
mysql> start slave

从库延时优化：

查看延时 show slave status中Seconds\_Behind\_Master=0则不延时

由于主库读写的操作量很大时，从库来不及读，那么会出现从库的同步延时问题

解决方案： 将thread改成多线程模式 MySQL5.6改表，MySQL5.7改GTID

网络延时导致的同步延时问题就用内网

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JveXdjeA_size_16_color_FFFFFF_t_70]: /images/20221022/74b272c2ecad456e96bfc6f09e8d9e21.png
[20210331153225685.png]: /images/20221022/66cf05dc9309421fac85ed0049e3d061.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JveXdjeA_size_16_color_FFFFFF_t_70 1]: /images/20221022/53d744aa0b3843aea45297503d46e0eb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JveXdjeA_size_16_color_FFFFFF_t_70 2]: /images/20221022/55c17adc833c41b0a5e831eba54c576b.png