MySQL主从同步中断的修复与预防实践

1. 问题背景与场景还原上周五凌晨3点我负责维护的电商平台数据库突然告警从库同步延迟持续增长。登录服务器检查发现主从同步线程报错Could not execute Write_rows event on table order_detail; Duplicate entry xxxx for key PRIMARY——典型的重复键冲突导致同步中断。这种场景在MySQL主从架构中并不罕见当主库和从库数据出现不一致时简单的STOP SLAVE; START SLAVE往往无法解决问题。此时使用mysqldump重新构建从库数据成为最可靠的修复手段。主从同步异常通常发生在以下几种情况人为在从库执行了写操作比如开发同学误操作主库执行了非确定性语句如使用了UUID()、RAND()等函数网络中断导致部分binlog事件丢失从库服务器异常重启造成同步位点错乱2. 修复方案选型分析面对主从同步中断DBA通常有以下几种修复方案方案适用场景优缺点对比跳过错误(SET GLOBAL sql_slave_skip_counter)明确知道是单条语句引起且可跳过快速但可能造成数据不一致手动修复不一致数据能精确定位少量不一致记录耗时且容易遗漏使用mysqldump重建大面积不一致或不确定不一致范围可靠但需要停服较长时间使用Percona XtraBackup超大数据库(500GB)速度快但配置复杂这次我选择mysqldump方案的原因数据库规模适中120GB左右不确定具体有多少表出现不一致业务允许有30分钟的只读窗口期团队对mysqldump工具最熟悉3. 详细修复操作流程3.1 前期准备工作1. 通知业务方停写并确认状态# 在主库创建维护标志表 mysql -h master -uroot -p -e CREATE TABLE maintenance_flag (note TEXT); \ INSERT INTO maintenance_flag VALUES (开始主从维护禁止写入);2. 检查主从同步状态SHOW SLAVE STATUS\G -- 重点关注以下字段 -- Slave_IO_Running: Yes -- Slave_SQL_Running: No -- Last_Errno: 1062 (重复键错误代码) -- Seconds_Behind_Master: NULL3. 记录主库binlog位置关键SHOW MASTER STATUS; -- 记录File和Position值例如 -- File: mysql-bin.000123 -- Position: 456789013.2 数据导出与导入1. 使用mysqldump导出数据# 带GTID的导出方式MySQL 5.7推荐 mysqldump -h master -uroot -p \ --single-transaction \ --master-data2 \ --flush-logs \ --routines \ --triggers \ --all-databases full_backup.sql # 关键参数说明 # --single-transaction: 保证导出数据一致性 # --master-data2: 在导出文件中记录binlog位置 # --flush-logs: 主库切换binlog文件2. 从库数据清理# 安全停止从库服务 systemctl stop mysqld # 备份原有数据目录保险起见 mv /var/lib/mysql /var/lib/mysql_bak_$(date %Y%m%d) # 重建数据目录 mkdir /var/lib/mysql chown mysql:mysql /var/lib/mysql3. 初始化并导入数据# 重新初始化数据库 mysqld --initialize-insecure --usermysql # 启动临时实例不启用复制 systemctl start mysqld # 导入数据 mysql -h localhost -uroot -p full_backup.sql3.3 重建复制关系1. 配置复制参数CHANGE MASTER TO MASTER_HOSTmaster_ip, MASTER_USERrepl_user, MASTER_PASSWORDrepl_password, MASTER_LOG_FILEmysql-bin.000123, -- 之前记录的File MASTER_LOG_POS45678901; -- 之前记录的Position2. 启动复制并验证START SLAVE; SHOW SLAVE STATUS\G -- 检查 -- Slave_IO_Running: Yes -- Slave_SQL_Running: Yes -- Seconds_Behind_Master: 逐渐减小至04. 关键注意事项与避坑指南4.1 时间窗口控制导出阶段使用--single-transaction参数时长时间运行的事务会导致导出过程阻塞。我曾遇到一个未提交的事务导致导出卡住2小时的情况。解决方法-- 导出前检查长时间运行的事务 SELECT * FROM information_schema.INNODB_TRX\G导入阶段对于大表建议分批导入。例如先导入表结构再按主键范围分批导入数据# 只导出表结构 mysqldump -h master -uroot -p --no-data --all-databases schema.sql # 分批导出数据 mysqldump -h master -uroot -p --whereid1000000 db1 table1 data_part1.sql4.2 空间不足预防导出文件大小估算-- 预估需要导出的数据量 SELECT table_schema, table_name, ROUND(data_length/1024/1024) AS size_mb FROM information_schema.TABLES ORDER BY data_length DESC;使用管道压缩传输适用于网络环境mysqldump -h master -uroot -p --all-databases | gzip | \ ssh slave gunzip | mysql -uroot -p4.3 特殊对象处理存储过程和触发器确保使用--routines和--triggers参数否则这些对象不会包含在导出中。GTID处理MySQL 5.6-- 如果使用GTID导入后可能需要重置 RESET MASTER; SET GLOBAL.GTID_PURGEDxxxx;5. 验证与监控方案修复完成后必须进行严格验证数据一致性校验# 使用pt-table-checksum工具Percona Toolkit pt-table-checksum hmaster,uroot,ppassword监控关键指标复制延迟时间Seconds_Behind_Master从库QPSQueries Per Second主从服务器负载对比业务验证-- 抽样检查关键业务表 SELECT COUNT(*) FROM orders WHERE create_time DATE_SUB(NOW(), INTERVAL 1 HOUR); -- 对比主从库结果6. 后续预防措施配置自动告警-- 监控复制错误的脚本示例 if [ $(mysql -e SHOW SLAVE STATUS\G | grep Last_Errno | awk {print $2}) -ne 0 ]; then alert Slave SQL error detected! fi定期校验机制每周凌晨低峰期自动运行pt-table-checksum对核心表设置checksum定时任务开发规范强化禁止在从库执行任何写操作避免在主库使用非确定性函数重要操作必须通过DBA审核这次修复总共耗时28分钟比预计的30分钟窗口期提前完成。过程中最大的教训是在导入前忘记禁用外键约束导致部分表导入顺序问题。后来通过添加--skip-add-drop-table和--disable-keys参数解决了这个问题。对于200GB以上的数据库下次我会考虑使用XtraBackup的增量备份方案来减少停机时间。