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方案的原因：

  1. 数据库规模适中（120GB左右）
  2. 不确定具体有多少表出现不一致
  3. 业务允许有30分钟的只读窗口期
  4. 团队对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: NULL

3. 记录主库binlog位置（关键！）

SHOW MASTER STATUS; -- 记录File和Position值，例如： -- File: mysql-bin.000123 -- Position: 45678901

3.2 数据导出与导入

1. 使用mysqldump导出数据

# 带GTID的导出方式（MySQL 5.7+推荐） mysqldump -h master -uroot -p \ --single-transaction \ --master-data=2 \ --flush-logs \ --routines \ --triggers \ --all-databases > full_backup.sql # 关键参数说明： # --single-transaction: 保证导出数据一致性 # --master-data=2: 在导出文件中记录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/mysql

3. 初始化并导入数据

# 重新初始化数据库 mysqld --initialize-insecure --user=mysql # 启动临时实例（不启用复制） systemctl start mysqld # 导入数据 mysql -h localhost -uroot -p < full_backup.sql

3.3 重建复制关系

1. 配置复制参数

CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='repl_user', MASTER_PASSWORD='repl_password', MASTER_LOG_FILE='mysql-bin.000123', -- 之前记录的File MASTER_LOG_POS=45678901; -- 之前记录的Position

2. 启动复制并验证

START SLAVE; SHOW SLAVE STATUS\G -- 检查： -- Slave_IO_Running: Yes -- Slave_SQL_Running: Yes -- Seconds_Behind_Master: 逐渐减小至0

4. 关键注意事项与避坑指南

4.1 时间窗口控制

  1. 导出阶段：使用--single-transaction参数时，长时间运行的事务会导致导出过程阻塞。我曾遇到一个未提交的事务导致导出卡住2小时的情况。解决方法：

    -- 导出前检查长时间运行的事务 SELECT * FROM information_schema.INNODB_TRX\G

  2. 导入阶段：对于大表，建议分批导入。例如先导入表结构，再按主键范围分批导入数据：

    # 只导出表结构 mysqldump -h master -uroot -p --no-data --all-databases > schema.sql # 分批导出数据 mysqldump -h master -uroot -p --where="id<1000000" db1 table1 > data_part1.sql

4.2 空间不足预防

  1. 导出文件大小估算：

    -- 预估需要导出的数据量 SELECT table_schema, table_name, ROUND(data_length/1024/1024) AS size_mb FROM information_schema.TABLES ORDER BY data_length DESC;

  2. 使用管道压缩传输（适用于网络环境）：

    mysqldump -h master -uroot -p --all-databases | gzip | \ ssh slave "gunzip | mysql -uroot -p"

4.3 特殊对象处理

  1. 存储过程和触发器：确保使用--routines和--triggers参数，否则这些对象不会包含在导出中。

  2. GTID处理（MySQL 5.6+）：

    -- 如果使用GTID，导入后可能需要重置 RESET MASTER; SET @@GLOBAL.GTID_PURGED='xxxx';

5. 验证与监控方案

修复完成后，必须进行严格验证：

  1. 数据一致性校验：

    # 使用pt-table-checksum工具（Percona Toolkit） pt-table-checksum h=master,u=root,p=password

  2. 监控关键指标：

    • 复制延迟时间（Seconds_Behind_Master）
    • 从库QPS（Queries Per Second）
    • 主从服务器负载对比

  3. 业务验证：

    -- 抽样检查关键业务表 SELECT COUNT(*) FROM orders WHERE create_time > DATE_SUB(NOW(), INTERVAL 1 HOUR); -- 对比主从库结果

6. 后续预防措施

  1. 配置自动告警：

    -- 监控复制错误的脚本示例 if [ $(mysql -e "SHOW SLAVE STATUS\G" | grep "Last_Errno" | awk '{print $2}') -ne 0 ]; then alert "Slave SQL error detected!" fi

  2. 定期校验机制：

    • 每周凌晨低峰期自动运行pt-table-checksum
    • 对核心表设置checksum定时任务

  3. 开发规范强化：

    • 禁止在从库执行任何写操作
    • 避免在主库使用非确定性函数
    • 重要操作必须通过DBA审核

这次修复总共耗时28分钟，比预计的30分钟窗口期提前完成。过程中最大的教训是：在导入前忘记禁用外键约束，导致部分表导入顺序问题。后来通过添加--skip-add-drop-table和--disable-keys参数解决了这个问题。对于200GB以上的数据库，下次我会考虑使用XtraBackup的增量备份方案来减少停机时间。