一则mysql主从不同步问题分析

开发环境出现了主从不同步,在slave节点上显示的SlaveIORunning: Connecting，SlaveSQLRunning: Yes，导致有些查询出现不一致的情况

问题分析

一般这种问题出现的原因主要有以下五点:

1. 主库机器和从库机器网络不通 可以互ping的方式来查
2. 密码不对 mysql -uroot -p 以对应的用户名和密码登录master mysql server重新对slave授权来排查，具体授权方式见下文。
3. master和slave的pos不正确

• 在master机器上mysql -uroot -p 登录，然后执行show master status \G; 查看pos和binXX.log的情况;
• 在slave机器上mysql -uroot -p 登录，然后执行show slave status \G; 查看pos和binXX.log的情况;
• 比对两者的pos和binXX.log的情况

1. 有防火墙(以下命令针对centos7)

• firewall-cmd --state 查看状态
• systemctl stop firewalld.service 停止防火墙
• systemctl disable firewalld.service 禁止防火墙开机启动

1. server.id的问题 分别在两台机器上cat /etc/my.cnf查看server.id的值，如果相同则改成不同即可

具体问题

1. master和slave上的pos与bin.log对应不上,slave 上的比起master要延后很多。master的截图如下:

1. 先执行stop slave，然后执行CHANGE MASTER TO -> MASTERHOST='master host', -> MASTERUSER='host111', -> MASTERPASSWORD='Aa&12345', -> MASTERLOGFILE='bin.000094', -> MASTERLOGPOS= 530876999; 再start slave之后在slave上show slave status \G;仍然SlaveIO_Running: Connecting。
2. 从上面两点开始怀疑是不是用户权限的问题,于是到master mysql控制台操作:

mysql> grant all privileges on *.* to host112@"slave host" identified by "Aa&12345" with grant option;Query OK, 0 rows affected, 1 warning (0.00 sec)

注意这里如果想权限粒度更小可以对复制操作的用户授予REPLICATION SLAVE权限:

mysql > GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO host112@'slave host' IDENTIFIED BY ‘Aa&12345';

然后flush一下:

mysql> flush privileges;Query OK, 0 rows affected (0.01 sec)

1. 上面授权成功后，先执行stop slave，然后执行:CHANGE MASTER TO -> MASTERHOST='master host', -> MASTERUSER='host112', -> MASTERPASSWORD='Aa&12345', -> MASTERLOGFILE='bin.000094', -> MASTERLOG_POS= 530876999;
2. 执行start slave 之后show slave status \G;SlaveIORunning: Yes,SlaveSQLRunning: Yes，成功。
3. 在这过程中如果需要重置slave，可以删除master.info和slaverelaylog_info，然后在slave上执行:

    mysql> stop slave;

    Query OK, 0 rows affected (0.01 sec)

    mysql> reset slave;

    Query OK, 0 rows affected (0.01 sec)

    mysql> CHANGE MASTER TO

        -> MASTER_HOST='master host', 

        -> MASTER_USER='host112', 

        -> MASTER_PASSWORD='Aa&12345', 

        -> MASTER_LOG_FILE='bin.000094', 

        -> MASTER_LOG_POS= 530876999;

    Query OK, 0 rows affected, 2 warnings (0.01 sec)

    mysql> start slave;

    Query OK, 0 rows affected (0.01 sec)

再查询从库状态 看到 SlaveIORunning: Yes SlaveSQLRunning: Yes

总结

• 一般情况下出现主从不一致要从上面几个方面排查。
• 压力测试通过时业务上有些重要的查询可以强制走主库。
• 如果上述方法都解决不了问题，建议dump一份数据文件，对master和slave都进行reset，这或许是没有办法的办法。