【案例】MySQL主从复制故障排查实录：从复制中断到恢复

作者
阿里云推广

MySQL主从复制故障排查实录

本文记录一个真实的MySQL主从复制故障排查过程，从发现问题到完全恢复。

问题现象

# 监控告警：从库延迟超过300秒
$ mysql -e "SHOW SLAVE STATUS\G" | grep Seconds_Behind_Master
Seconds_Behind_Master: NULL

# Slave_SQL_Running为No
$ mysql -e "SHOW SLAVE STATUS\G" | grep Running
Slave_IO_Running: Yes
Slave_SQL_Running: No

IO线程正常（能连上主库），但SQL线程停止（无法执行复制过来的SQL）。

排查过程

第1步：查看错误信息

mysql> SHOW SLAVE STATUS\G
...
Last_SQL_Error: Error 'Duplicate entry '12345' for key 'PRIMARY'' 
                on query. Default database: 'mydb'. 
                Query: 'INSERT INTO orders VALUES (...)'
Last_SQL_Error_Timestamp: 240409 14:30:00

发现问题：主键冲突。从库尝试插入一条已存在的记录。

第2步：分析原因

可能的原因：

  1. 从库被误写入数据（违反了read_only原则）
  2. 主库异常重启导致binlog位置不一致
  3. 主从数据本来就不一致

第3步：确认数据差异

# 在从库查询这条冲突记录
mysql> SELECT * FROM orders WHERE id = 12345;
+-------+--------+---------+---------------------+
| id    | user_id| amount  | created_at          |
+-------+--------+---------+---------------------+
| 12345 |    100 |  199.00 | 2026-04-09 14:25:00 |
+-------+--------+---------+---------------------+

# 在主库查询
mysql> SELECT * FROM orders WHERE id = 12345;
+-------+--------+---------+---------------------+
| id    | user_id| amount  | created_at          |
+-------+--------+---------+---------------------+
| 12345 |    100 |  299.00 | 2026-04-09 14:28:00 |
+-------+--------+---------+---------------------+

# 发现：金额和创建时间都不同！

第4步：调查数据差异来源

# 查看从库是否被写过
mysql> SHOW VARIABLES LIKE 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | OFF   |
+---------------+-------+

# 发现问题：从库没有设置read_only！
# 查询binlog确认是否从库写入
mysqlbinlog /var/lib/mysql/mysql-bin.000123 | grep -A5 "INSERT INTO orders" | head -20

确认：有应用误连到从库执行了写入操作。

解决方案

方案1：跳过错误（快速恢复，但可能丢失数据）

# 停止复制
mysql> STOP SLAVE;

# 跳过一个事务
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

# 启动复制
mysql> START SLAVE;

# 验证
mysql> SHOW SLAVE STATUS\G
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

⚠️ 警告：这会跳过主库的这个INSERT，导致主从数据不一致！

方案2：重建从库（推荐，数据一致）

# 1. 主库备份
$ mysqldump -u root -p --single-transaction --all-databases > full_backup.sql

# 2. 从库停止复制
mysql> STOP SLAVE;
mysql> RESET SLAVE ALL;

# 3. 导入数据
$ mysql -u root -p < full_backup.sql

# 4. 重新配置复制
mysql> CHANGE MASTER TO
    MASTER_HOST='master_host',
    MASTER_USER='repl',
    MASTER_PASSWORD='password',
    MASTER_AUTO_POSITION=1;

mysql> START SLAVE;

# 5. 验证
mysql> SHOW SLAVE STATUS\G

预防措施

# 1. 从库必须设置read_only
[mysqld]
read_only = ON

# 2. 监控复制延迟
#!/bin/bash
# check_replication.sh
DELAY=$(mysql -e "SHOW SLAVE STATUS\G" | grep Seconds_Behind_Master | awk '{print $2}')
if [ "$DELAY" = "NULL" ] || [ "$DELAY" -gt 60 ]; then
    echo "Replication delay: ${DELAY}s" | mail -s "MySQL Replication Alert" ops@example.com
fi

# 3. 定期检查主从数据一致性
pt-table-checksum --host=master --user=root --password=xxx

# 4. 应用连接配置检查
# 确保所有应用连接串都指向主库

经验总结

  1. 从库必须read_only – 这是基本原则
  2. 定期监控复制状态 – Seconds_Behind_Master是关键指标
  3. 数据一致性优先 – 跳过错误是临时方案，重建才是根本解决
  4. 使用GTID – 简化复制配置，避免binlog位置问题

总结

MySQL复制故障排查遵循”看错误→找原因→选方案→做预防”的流程。最重要的是建立完善的监控，在问题恶化前发现。

发表评论