MYSQL主从复制数据不一致的原因和解决方案

原因

造成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
• 从库开启只读，防止认为写入
• 定期进行主从一致性检验