1. 主从复制不一致的典型症状与诊断当MySQL主从复制出现严重不一致时通常会表现出以下几种典型症状从库的Seconds_Behind_Master值持续增长或显示为NULL从库SQL线程报错停止Last_SQL_Errno和Last_SQL_Error显示具体错误主从数据校验工具如pt-table-checksum报告大量不一致表业务层面发现从库查询结果与主库不一致1.1 初步诊断方法首先通过以下命令检查复制状态SHOW SLAVE STATUS\G重点关注以下字段Slave_IO_RunningIO线程状态Slave_SQL_RunningSQL线程状态Last_IO_Errno/Last_IO_ErrorIO线程错误Last_SQL_Errno/Last_SQL_ErrorSQL线程错误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%记录中度不一致多个表存在不一致但表结构完整严重不一致大量表不一致甚至出现表结构差异对于严重不一致的情况简单的跳过错误或单表修复往往无法解决问题需要采用系统性的修复方案。2. 基于Binlog Position的修复方案设计2.1 方案选择考量对于严重不一致的情况通常有以下几种修复方案重建复制完全重新搭建从库优点彻底解决问题缺点停机时间长对大库不友好基于备份恢复从最近备份恢复优点相对快速缺点可能丢失部分数据基于Binlog Position的增量修复本文方案优点最小化停机时间精确修复缺点操作复杂技术要求高我们选择第三种方案因为它能在保证数据完整性的前提下最小化业务影响。2.2 修复流程概览完整的修复流程包括以下步骤停止复制并记录当前状态数据一致性校验确定修复起始点应用差异数据重建复制关系验证修复结果3. 详细修复操作步骤3.1 准备工作备份当前状态# 备份从库数据 mysqldump -uroot -p --all-databases --single-transaction --master-data2 slave_backup.sql # 记录当前复制状态 mysql -uroot -p -e SHOW SLAVE STATUS\G slave_status.txt准备工具安装percona工具集sudo yum install percona-toolkit准备校验工具pt-table-checksum --replicatetest.checksums hmaster,uroot,ppassword3.2 停止复制并记录状态STOP SLAVE;记录关键位置信息SHOW SLAVE STATUS\G -- 记录Relay_Master_Log_File和Exec_Master_Log_Pos3.3 数据一致性校验使用pt-table-checksum进行校验pt-table-checksum --replicatetest.checksums \ --recursion-methodhosts \ hmaster,uroot,ppassword然后使用pt-table-sync生成修复SQLpt-table-sync --replicatetest.checksums \ hmaster,uroot,ppassword \ --sync-to-master \ hslave,uroot,ppassword \ --print注意务必先使用--print查看生成的SQL确认无误后再执行--execute3.4 确定修复起始点通过以下方式确定修复起始点查找最后一个确认一致的binlog位置如果没有明确的一致点可以选择最近一次备份的位置从库的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。对于大量差异建议导出差异数据mysqldump -uroot -p --skip-add-drop-table --no-create-info \ --whereid IN (1,2,3) db table patch.sql在从库应用mysql -uroot -p patch.sql3.6 重建复制关系重置复制RESET SLAVE ALL;重新配置复制CHANGE MASTER TO MASTER_HOSTmaster_host, MASTER_USERrepl, MASTER_PASSWORDpassword, MASTER_LOG_FILEmysql-bin.000123, MASTER_LOG_POS456789;启动复制START SLAVE;4. 关键问题与解决方案4.1 常见错误处理Duplicate entry错误-- 临时跳过仅用于紧急恢复 SET GLOBAL sql_slave_skip_counter 1; START SLAVE; -- 更安全的做法是手动修复数据表不存在错误检查主从表结构差异使用SHOW CREATE TABLE对比手动创建缺失表或修改表结构4.2 大表修复策略对于大表不一致的情况使用pt-table-sync的--chunk-size参数分块修复pt-table-sync --chunk-size1000 --execute ...对于特别大的表可以考虑在业务低峰期操作使用--sleep参数减少负载分批执行修复4.3 校验与修复的负载控制为避免对生产环境造成影响使用--max-load控制校验负载pt-table-checksum --max-load Threads_running25 ...使用--sleep间隔pt-table-sync --sleep 0.5 --execute ...5. 修复后的验证与监控5.1 验证方法再次运行pt-table-checksum验证一致性检查关键业务表记录数SELECT COUNT(*) FROM important_table;比对主从关键数据样本5.2 监控建议部署定期校验任务每周一次pt-table-checksum --replicatetest.checksums \ --recursion-methodhosts \ --create-replicate-table \ hmaster,umonitor,ppassword设置复制告警监控Seconds_Behind_Master监控Slave_SQL_Running状态监控Last_SQL_Errno错误6. 预防措施与最佳实践6.1 配置优化建议启用严格的复制校验[mysqld] slave_exec_mode STRICT配置自动跳过错误谨慎使用slave_skip_errors 1062,10536.2 日常维护建议定期检查复制状态建立定期数据校验机制保持主从服务器配置一致监控磁盘空间和网络延迟6.3 备份策略建议配置定期全量备份binlog备份测试备份恢复流程考虑使用Percona XtraBackup进行热备份在实际操作中我发现最有效的预防措施是建立自动化的监控和告警系统能够在出现不一致的早期就发现问题。同时定期演练修复流程也非常重要这样在真正出现问题时能够快速响应。
1. 主从复制不一致的典型症状与诊断当MySQL主从复制出现严重不一致时通常会表现出以下几种典型症状从库的Seconds_Behind_Master值持续增长或显示为NULL从库SQL线程报错停止Last_SQL_Errno和Last_SQL_Error显示具体错误主从数据校验工具如pt-table-checksum报告大量不一致表业务层面发现从库查询结果与主库不一致1.1 初步诊断方法首先通过以下命令检查复制状态SHOW SLAVE STATUS\G重点关注以下字段Slave_IO_RunningIO线程状态Slave_SQL_RunningSQL线程状态Last_IO_Errno/Last_IO_ErrorIO线程错误Last_SQL_Errno/Last_SQL_ErrorSQL线程错误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%记录中度不一致多个表存在不一致但表结构完整严重不一致大量表不一致甚至出现表结构差异对于严重不一致的情况简单的跳过错误或单表修复往往无法解决问题需要采用系统性的修复方案。2. 基于Binlog Position的修复方案设计2.1 方案选择考量对于严重不一致的情况通常有以下几种修复方案重建复制完全重新搭建从库优点彻底解决问题缺点停机时间长对大库不友好基于备份恢复从最近备份恢复优点相对快速缺点可能丢失部分数据基于Binlog Position的增量修复本文方案优点最小化停机时间精确修复缺点操作复杂技术要求高我们选择第三种方案因为它能在保证数据完整性的前提下最小化业务影响。2.2 修复流程概览完整的修复流程包括以下步骤停止复制并记录当前状态数据一致性校验确定修复起始点应用差异数据重建复制关系验证修复结果3. 详细修复操作步骤3.1 准备工作备份当前状态# 备份从库数据 mysqldump -uroot -p --all-databases --single-transaction --master-data2 slave_backup.sql # 记录当前复制状态 mysql -uroot -p -e SHOW SLAVE STATUS\G slave_status.txt准备工具安装percona工具集sudo yum install percona-toolkit准备校验工具pt-table-checksum --replicatetest.checksums hmaster,uroot,ppassword3.2 停止复制并记录状态STOP SLAVE;记录关键位置信息SHOW SLAVE STATUS\G -- 记录Relay_Master_Log_File和Exec_Master_Log_Pos3.3 数据一致性校验使用pt-table-checksum进行校验pt-table-checksum --replicatetest.checksums \ --recursion-methodhosts \ hmaster,uroot,ppassword然后使用pt-table-sync生成修复SQLpt-table-sync --replicatetest.checksums \ hmaster,uroot,ppassword \ --sync-to-master \ hslave,uroot,ppassword \ --print注意务必先使用--print查看生成的SQL确认无误后再执行--execute3.4 确定修复起始点通过以下方式确定修复起始点查找最后一个确认一致的binlog位置如果没有明确的一致点可以选择最近一次备份的位置从库的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。对于大量差异建议导出差异数据mysqldump -uroot -p --skip-add-drop-table --no-create-info \ --whereid IN (1,2,3) db table patch.sql在从库应用mysql -uroot -p patch.sql3.6 重建复制关系重置复制RESET SLAVE ALL;重新配置复制CHANGE MASTER TO MASTER_HOSTmaster_host, MASTER_USERrepl, MASTER_PASSWORDpassword, MASTER_LOG_FILEmysql-bin.000123, MASTER_LOG_POS456789;启动复制START SLAVE;4. 关键问题与解决方案4.1 常见错误处理Duplicate entry错误-- 临时跳过仅用于紧急恢复 SET GLOBAL sql_slave_skip_counter 1; START SLAVE; -- 更安全的做法是手动修复数据表不存在错误检查主从表结构差异使用SHOW CREATE TABLE对比手动创建缺失表或修改表结构4.2 大表修复策略对于大表不一致的情况使用pt-table-sync的--chunk-size参数分块修复pt-table-sync --chunk-size1000 --execute ...对于特别大的表可以考虑在业务低峰期操作使用--sleep参数减少负载分批执行修复4.3 校验与修复的负载控制为避免对生产环境造成影响使用--max-load控制校验负载pt-table-checksum --max-load Threads_running25 ...使用--sleep间隔pt-table-sync --sleep 0.5 --execute ...5. 修复后的验证与监控5.1 验证方法再次运行pt-table-checksum验证一致性检查关键业务表记录数SELECT COUNT(*) FROM important_table;比对主从关键数据样本5.2 监控建议部署定期校验任务每周一次pt-table-checksum --replicatetest.checksums \ --recursion-methodhosts \ --create-replicate-table \ hmaster,umonitor,ppassword设置复制告警监控Seconds_Behind_Master监控Slave_SQL_Running状态监控Last_SQL_Errno错误6. 预防措施与最佳实践6.1 配置优化建议启用严格的复制校验[mysqld] slave_exec_mode STRICT配置自动跳过错误谨慎使用slave_skip_errors 1062,10536.2 日常维护建议定期检查复制状态建立定期数据校验机制保持主从服务器配置一致监控磁盘空间和网络延迟6.3 备份策略建议配置定期全量备份binlog备份测试备份恢复流程考虑使用Percona XtraBackup进行热备份在实际操作中我发现最有效的预防措施是建立自动化的监控和告警系统能够在出现不一致的早期就发现问题。同时定期演练修复流程也非常重要这样在真正出现问题时能够快速响应。
