原因
造成MYSQL主从数据不一致的可能原因汇总:
- Master的二进制日志格式(binlog_format)错误的设置成了statement,同步到slave时引起的不一致
- Master在数据更改前执行过set sql_log_bin=0,导致二进制日志没有被记录,slave接收不到日志
- Slave未设置只读,用户误操作写入数据
- Master或slave意外宕机,宕机可能会造成binlog或者relaylog文件出现损坏,导致主从不一致
- 主从实例版本不一致,特别是高版本是主,低版本为从的情况下,主数据库上面支持的功能,从数
据库上面可能不支持该功能 - MySQL自身bug导致
修复方案
MYSQL主从数据不一致,可以把整个从服务器的数据重新备份还原,然后重新开始同步。这种方法虽然简单,但是恢复时间太长,从节点在这段时间里不能承担查询操作。如果从节点只有几张表的数据和主节点不一致,可以先暂停从主节点复制数据,手工重建数据不一致的表,然后重新开启复制。
实现方法:
假设有一对正在运行的主从复制服务器,运行一段时间后,发现testdb数据库中有A、B、C三张表数据不一致。
修复数据,单独重建A、B、C
1.从服务器暂停复制,假设时间点是T1
stop replica;
2.主服务器单独备份这三张表,会经过一段时间,假设时间点是T2
mysqldump -uroot -p --single-transaction --master-data=2 testdb A B C > A_B_C.sql
3.查看A_B_C.sql文件,找到 “CHANGE MASTER TO” 这一行注释,记录下日志文件名和POS,也就是T2时间点日志的位置
MASTER_LOG_FILE=binlog.00000
MASTER_LOG_POS=xxx
4.把A_B_C.sql文件拷贝到从服务器,先不使用这个备份,把从数据库继续同步到刚才记录的位置,也就是时间点T2
start replica until MASTERLOGFILE='binlog.00000x',MASTERLOGPOS=xxx;
5.单独还原A、B、C表,这时候所有的数据都正确还原了,只是数据只更新到了T2这个时间点
mysql -uroot -p testdb
set sql_bin_log=0;
source A_B_C.sql
set sql_bin_log=1;
6.然后只要在从节点继续开启复制即可
start replica;
如何避免
MYSQL如何避免主从数据不一致
- 主节点binlog_format设置为ROW
- 主从数据库保持版本一致
- 主库做好账号权限把控,不可以执行set sql_log_bin=0
- 从库开启只读,防止认为写入
- 定期进行主从一致性检验