MySQL主从复制不一致诊断与Binlog修复方案

1. 主从复制不一致的典型症状与诊断

当MySQL主从复制出现严重不一致时，通常会表现出以下几种典型症状：

• 从库的Seconds_Behind_Master值持续增长或显示为NULL
• 从库SQL线程报错停止（Last_SQL_Errno和Last_SQL_Error显示具体错误）
• 主从数据校验工具（如pt-table-checksum）报告大量不一致表
• 业务层面发现从库查询结果与主库不一致

1.1 初步诊断方法

首先通过以下命令检查复制状态：

SHOW SLAVE STATUS\G

重点关注以下字段：

• Slave_IO_Running：IO线程状态
• Slave_SQL_Running：SQL线程状态
• Last_IO_Errno/Last_IO_Error：IO线程错误
• Last_SQL_Errno/Last_SQL_Error：SQL线程错误
• Seconds_Behind_Master：复制延迟
• Exec_Master_Log_Pos：已执行的binlog位置

注意：当发现Last_SQL_Error显示"Could not execute Write_rows event on table db.table; Duplicate entry 'X' for key 'PRIMARY'"这类错误时，通常表明主从数据已经不一致。

1.2 不一致程度评估

根据不一致的严重程度，我们可以将问题分为三类：

1. 轻微不一致：少量表存在少量记录不一致（<1%记录）
2. 中度不一致：多个表存在不一致，但表结构完整
3. 严重不一致：大量表不一致，甚至出现表结构差异

对于严重不一致的情况，简单的跳过错误或单表修复往往无法解决问题，需要采用系统性的修复方案。

2. 基于Binlog Position的修复方案设计

2.1 方案选择考量

对于严重不一致的情况，通常有以下几种修复方案：

1. 重建复制：完全重新搭建从库

   • 优点：彻底解决问题
   • 缺点：停机时间长，对大库不友好

2. 基于备份恢复：从最近备份恢复

   • 优点：相对快速
   • 缺点：可能丢失部分数据

3. 基于Binlog Position的增量修复（本文方案）

   • 优点：最小化停机时间，精确修复
   • 缺点：操作复杂，技术要求高

我们选择第三种方案，因为它能在保证数据完整性的前提下，最小化业务影响。

2.2 修复流程概览

完整的修复流程包括以下步骤：

1. 停止复制并记录当前状态
2. 数据一致性校验
3. 确定修复起始点
4. 应用差异数据
5. 重建复制关系
6. 验证修复结果

3. 详细修复操作步骤

3.1 准备工作

1. 备份当前状态：

   # 备份从库数据 mysqldump -uroot -p --all-databases --single-transaction --master-data=2 > slave_backup.sql # 记录当前复制状态 mysql -uroot -p -e "SHOW SLAVE STATUS\G" > slave_status.txt

2. 准备工具：

   • 安装percona工具集：

     sudo yum install percona-toolkit

   • 准备校验工具：

     pt-table-checksum --replicate=test.checksums h=master,u=root,p=password

3.2 停止复制并记录状态

STOP SLAVE;

记录关键位置信息：

SHOW SLAVE STATUS\G -- 记录Relay_Master_Log_File和Exec_Master_Log_Pos

3.3 数据一致性校验

使用pt-table-checksum进行校验：

pt-table-checksum --replicate=test.checksums \ --recursion-method=hosts \ h=master,u=root,p=password

然后使用pt-table-sync生成修复SQL：

pt-table-sync --replicate=test.checksums \ h=master,u=root,p=password \ --sync-to-master \ h=slave,u=root,p=password \ --print

注意：务必先使用--print查看生成的SQL，确认无误后再执行--execute

3.4 确定修复起始点

通过以下方式确定修复起始点：

1. 查找最后一个确认一致的binlog位置
2. 如果没有明确的一致点，可以选择：
   • 最近一次备份的位置
   • 从库的Relay_Master_Log_File和Exec_Master_Log_Pos

-- 在主库查找binlog事件 SHOW BINLOG EVENTS IN 'mysql-bin.000123' FROM 123456 LIMIT 20;

3.5 应用差异数据

对于少量差异，可以直接应用pt-table-sync生成的SQL。对于大量差异，建议：

1. 导出差异数据：

   mysqldump -uroot -p --skip-add-drop-table --no-create-info \ --where="id IN (1,2,3)" db table > patch.sql

2. 在从库应用：

   mysql -uroot -p < patch.sql

3.6 重建复制关系

1. 重置复制：

   RESET SLAVE ALL;

2. 重新配置复制：

   CHANGE MASTER TO MASTER_HOST='master_host', MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000123', MASTER_LOG_POS=456789;

3. 启动复制：

   START SLAVE;

4. 关键问题与解决方案

4.1 常见错误处理

1. Duplicate entry错误：

   -- 临时跳过（仅用于紧急恢复） SET GLOBAL sql_slave_skip_counter = 1; START SLAVE; -- 更安全的做法是手动修复数据

2. 表不存在错误：

   • 检查主从表结构差异
   • 使用SHOW CREATE TABLE对比
   • 手动创建缺失表或修改表结构

4.2 大表修复策略

对于大表不一致的情况：

1. 使用pt-table-sync的--chunk-size参数分块修复

   pt-table-sync --chunk-size=1000 --execute ...

2. 对于特别大的表，可以考虑：

   • 在业务低峰期操作
   • 使用--sleep参数减少负载
   • 分批执行修复

4.3 校验与修复的负载控制

为避免对生产环境造成影响：

1. 使用--max-load控制校验负载：

   pt-table-checksum --max-load Threads_running=25 ...

2. 使用--sleep间隔：

   pt-table-sync --sleep 0.5 --execute ...

5. 修复后的验证与监控

5.1 验证方法

1. 再次运行pt-table-checksum验证一致性
2. 检查关键业务表记录数：

   SELECT COUNT(*) FROM important_table;

3. 比对主从关键数据样本

5.2 监控建议

1. 部署定期校验任务（每周一次）

   pt-table-checksum --replicate=test.checksums \ --recursion-method=hosts \ --create-replicate-table \ h=master,u=monitor,p=password

2. 设置复制告警：

   • 监控Seconds_Behind_Master
   • 监控Slave_SQL_Running状态
   • 监控Last_SQL_Errno错误

6. 预防措施与最佳实践

6.1 配置优化建议

1. 启用严格的复制校验：

   [mysqld] slave_exec_mode = STRICT

2. 配置自动跳过错误（谨慎使用）：

   slave_skip_errors = 1062,1053

6.2 日常维护建议

1. 定期检查复制状态
2. 建立定期数据校验机制
3. 保持主从服务器配置一致
4. 监控磁盘空间和网络延迟

6.3 备份策略建议

1. 配置定期全量备份+binlog备份
2. 测试备份恢复流程
3. 考虑使用Percona XtraBackup进行热备份

在实际操作中，我发现最有效的预防措施是建立自动化的监控和告警系统，能够在出现不一致的早期就发现问题。同时，定期演练修复流程也非常重要，这样在真正出现问题时能够快速响应。