MySQL 主从复制延迟排查与治理实战

dbaai

MySQL 主从复制延迟排查与治理实战

一、具体的问题

做 MySQL 运维的，大概都遇到过这个场景：主库写入一切正常，业务却投诉"刚下的单查不到"。登录从库一查，数据是几分钟前的。执行 SHOW SLAVE STATUS\G，Seconds_Behind_Master 那一行赫然显示着一个三位数甚至四位数。

这个指标有个很坑的地方：它并不总是可信。我接手的一个订单库，从库延迟长期显示 0，但业务侧读从库就是拿不到最新数据。查到最后发现，复制线程因为一个唯一键冲突停了，Seconds_Behind_Master 为 NULL 被某些监控脚本当成 0 上报，告警因此完全没触发。另一个库则是白天延迟 3 秒、晚上跑批时冲到 1800 秒，第二天早上又自动回落，运维很难在白天复现。

真正麻烦的是，延迟不只是"读旧数据"这么简单。主从切换时，如果从库落后太多，要么切换等待时间不可控，要么强行切换丢数据。所以延迟治理的目标不是"让它变成 0"，而是搞清楚延迟来自哪一环，把它压进业务可接受的窗口，并且让它可观测、可告警。

本文按"定位延迟来源 → 判断是哪一类延迟 → 针对性治理"这条线走，给出可直接执行的命令和对比结果。

二、核心原理

1. 复制的三个环节

MySQL 主从复制由三个线程串起来：主库的 dump 线程（binlog dump）、从库的 IO 线程、从库的 SQL 线程（MySQL 8.0 里叫 replication applier）。

• IO 线程负责把主库 binlog 拉到本地写成 relay log，这一步慢通常表现为网络或主库写 binlog 压力大；
  
• SQL 线程负责读 relay log 回放，这一步慢通常表现为从库回放能力跟不上。
  

判断属于哪一环，看的是位点而不是 Seconds_Behind_Master：

• Master_Log_File / Read_Master_Log_Pos：IO 线程读到的主库位点；
  
• Relay_Master_Log_File / Exec_Master_Log_Pos：SQL 线程已执行到的主库位点；
  
• Slave_IO_Running / Slave_SQL_Running：两个线程是否活着，以及 Last_IO_Error / Last_SQL_Error 的具体报错。
  

2. 五类典型延迟成因

• 大事务：主库一个 DELETE 删掉 500 万行，binlog 里就是 500 万条行记录，从库必须一行一行回放。主库执行 8 秒，从库回放可能要 3 分钟。这是"延迟尖峰"最常见的来源。
  
• 从库无主键表回放：没有主键时，从库回放 UPDATE/DELETE 只能全表扫描定位行。RBR 格式下这一条能慢出数量级差异。
  
• 从库承担大量读：报表、导出、大分页查询把 IO 和 CPU 吃满，SQL 线程抢不到资源。
  
• 单线程回放瓶颈：MySQL 5.6 及以前 SQL 线程是单线程，主库并发 32 个连接写，从库一个线程慢慢追，天然跟不上。
  
• 从库配置低于主库：磁盘用 SATA、buffer pool 只有主库一半、没开 doublewrite 优化，回放自然慢。
  

3. 并行复制能解决哪一部分

MySQL 5.7 引入基于 LOGICAL_CLOCK 的并行复制，8.0 默认 replica_parallel_workers（5.7 叫 slave_parallel_workers）。它的原理是：主库在同一组 commit（同一 last_committed）里提交的事务之间没有冲突，从库可以并行回放。

关键结论：并行复制只对主库并发度高的场景有效。如果主库本身就是串行大事务，开 16 个 worker 也没用，因为一个大事务仍然只能由一个 worker 串行回放。所以"开并行复制"不是万能解，先得确认延迟是不是由并发小事务堆积造成的。

4. MySQL 8.0 的两个注意点

• 术语变了：SLAVE 系列改成了 REPLICA/SOURCE，SHOW SLAVE STATUS 在 8.0.22 之后推荐用 SHOW REPLICA STATUS，旧写法仍兼容但已废弃。
  
• 8.0 默认 binlog_transaction_compression=OFF。开启后 binlog 体积能降 40%~70%，对 IO 线程这一步的延迟改善很明显，代价是主库多一点点 CPU。
  

三、实例参考（动手步骤）

下面这套步骤在我手上的订单库（主库 5.7、从库 8.0.32，1 主 2 从）上完整跑过。

步骤 1：先确认延迟是真是假

2. -- 8.0.22+ 用 SHOW REPLICA STATUS，5.7 用 SHOW SLAVE STATUS
3. SHOW REPLICA STATUS\G

复制代码

重点看四行：

2. Slave_IO_Running: Yes
3. Slave_SQL_Running: Yes
4. Seconds_Behind_Master: 1834
5. Last_SQL_Error:

复制代码

如果 Slave_IO_Running 或 Slave_SQL_Running 是 No，Seconds_Behind_Master 就不可信（通常是 NULL 或僵在旧值）。这时先修线程，别去调延迟参数。

顺带说一句，自动化监控脚本里千万不要把 NULL 当 0 处理。正确写法：

2. SELECT CASE
3.          WHEN Slave_IO_Running <> 'Yes' OR Slave_SQL_Running <> 'Yes' THEN -1
4.          WHEN Seconds_Behind_Master IS NULL THEN -1
5.          ELSE Seconds_Behind_Master
6.        END AS delay_sec
7. FROM performance_schema.replication_applier_status_by_worker LIMIT 1;

复制代码

返回 -1 就告警，这条规则帮我抓到了开头说的那个"延迟显示 0"的坑。

步骤 2：定位是 IO 线程慢还是 SQL 线程慢

在主库上取当前 binlog 位点：

2. SHOW MASTER STATUS;
3. -- File: mysql-bin.000428, Position: 736251904

复制代码

在从库上对比三个坐标：

2. SHOW REPLICA STATUS\G
3. -- Master_Log_File: mysql-bin.000428      <- IO 线程读到这
4. -- Read_Master_Log_Pos: 736251900         <- 差 4 字节，说明 IO 线程几乎没落后
5. -- Relay_Master_Log_File: mysql-bin.000425 <- SQL 线程还停在 3 个文件之前
6. -- Exec_Master_Log_Pos: 118432771

复制代码

判定规则很直接：

• IO 线程位点贴近主库、SQL 线程位点落后 → 回放慢，问题在从库；
  
• 两者都落后主库 → 传输慢，问题在网络或主库 binlog 落盘；
  
• 两者都贴近主库但 Seconds_Behind_Master 大 → 可能是主库时钟问题或长事务正在执行。
  

本次实测结果：IO 线程落后 4 字节，SQL 线程落后 3 个 binlog 文件。结论是回放慢，问题在从库。

步骤 3：找出卡住的那个大事务

看 relay log 里当前正在回放什么：

2. SELECT worker_id, thread_id, service_state,
3.        last_error_number, last_error_message,
4.        last_applied_transaction, last_applied_transaction_original_commit_timestamp
5. FROM performance_schema.replication_applier_status_by_worker;

复制代码

再从 processlist 里看这个线程在跑什么 SQL：

2. SELECT id, user, db, command, time, state, LEFT(info, 120) AS sql_text
3. FROM information_schema.processlist
4. WHERE command <> 'Sleep' AND time > 10\G

复制代码

本次抓到的是一条跑了 1830 秒的 DELETE FROM order_detail WHERE create_time < '2023-01-01'。这条语句在主库上执行了 11 秒，删了 420 万行；从库因为 create_time 上没有索引、且表上只有联合主键的一部分可用，回放走全表扫描，一条一条删，卡了半小时。

步骤 4：治理动作与前后对比

针对"大事务 + 无索引"，做了三件事：

（1）从库补索引（不影响主库）

2. -- 从库上单独建，主库不动（注意别让这条 DDL 回放到其它从库）
3. ALTER TABLE order_detail ADD INDEX idx_create_time (create_time);

复制代码

提示：从库单独加索引后，如果发生主从切换，新主库会缺这个索引，需要在切换预案里补上。

（2）大事务拆成小批量

原来的写法：

2. DELETE FROM order_detail WHERE create_time < '2023-01-01';

复制代码

改成循环分批：

2. -- 每次删 5000 行，循环执行直到影响行数为 0
3. DELETE FROM order_detail
4. WHERE create_time < '2023-01-01'
5. ORDER BY id
6. LIMIT 5000;

复制代码

用 shell 包一层：

2. while : ; do
3.   n=$(mysql -udba -p"$PWD" -N -e \
4.       "DELETE FROM order_detail WHERE create_time < '2023-01-01' ORDER BY id LIMIT 5000; SELECT ROW_COUNT();" orderdb)
5.   [ "$n" -eq 0 ] && break
6.   sleep 0.2
7. done

复制代码

分批的好处是每批事务小，从库能在毫秒级回放完，延迟不会堆成尖峰；缺点是总耗时变长，但延迟曲线是平的。

（3）开启并行复制

2. STOP REPLICA;
3. SET GLOBAL replica_parallel_type = 'LOGICAL_CLOCK';   -- 5.7: slave_parallel_type
4. SET GLOBAL replica_parallel_workers = 8;              -- 一般设 CPU 核数的一半到相等
5. START REPLICA;

复制代码

写进配置文件避免重启失效：

2. [mysqld]
3. replica_parallel_type=LOGICAL_CLOCK
4. replica_parallel_workers=8
5. replica_preserve_commit_order=ON   # 保证从库提交顺序与主库一致，避免读到中间态

复制代码

治理前后对比：

指标	治理前	治理后
跑批峰值延迟	1834 秒	6 秒
白天日常延迟	2~5 秒	0~1 秒
420 万行清理耗时	主库 11 秒 / 从库回放 1830 秒	分批共 14 分钟，延迟全程 < 2 秒
延迟告警触发次数/周	9 次	0 次

步骤 5：把延迟变成可观测指标

2. -- 持续观察 20 次，每 3 秒一次
3. mysql -udba -p"$PWD" -e \
4.   "SHOW REPLICA STATUS\G" | grep -E 'Seconds_Behind_Master|Slave_SQL_Running'

复制代码

生产上建议直接采集 performance_schema.replication_applier_status_by_worker，比解析 SHOW 输出稳定。告警阈值我一般设：持续 3 次采样 > 30 秒告警，> 300 秒电话。别设成"超过 0 就告警"，那会淹死在噪音里。

四、实操检查清单

• [ ] 先确认 Slave_IO_Running 和 Slave_SQL_Running 都是 Yes，不是就先修线程，不要直接调延迟参数
  
• [ ] 监控脚本里把 NULL / 非 Yes 映射成 -1 并告警，绝不当成 0 上报
  
• [ ] 对比 Master_Log_File 与 Relay_Master_Log_File，判定是传输慢还是回放慢
  
• [ ] 检查是否有跑批大事务，把 DELETE/UPDATE 改成 LIMIT 5000 分批循环
  
• [ ] 确认从库回放的表都有主键或合适索引，RBR 下无主键表回放会慢出数量级
  
• [ ] 按 CPU 核数设置 replica_parallel_workers（建议 4~8），并配 replica_preserve_commit_order=ON
  
• [ ] 若 binlog_row_image=FULL 且带宽紧张，评估改为 MINIMAL（需确认无依赖全镜像的工具）
  
• [ ] 8.0 环境评估开启 binlog_transaction_compression=ON，IO 线程延迟可显著下降
  
• [ ] 从库单独加的索引要写进主从切换预案，防止切换后新主库缺索引
  
• [ ] 从库硬件配置不低于主库，尤其是磁盘 IOPS 和 buffer pool 大小
  
• [ ] 报表类大查询迁到独立只读实例，避免和 SQL 线程抢资源
  
• [ ] 设置分级告警：>30 秒告警、>300 秒电话，避免"超过 0 就告警"的噪音
  
• [ ] 定期做延迟演练：人为停 SQL 线程 5 分钟，验证告警链路真的会响
  

五、小结

主从延迟不是一个参数能解决的事，它是一条链路上某个环节的外在表现。我的经验是先花五分钟把"传输慢还是回放慢"判定清楚，再决定是加索引、拆事务、开并行复制还是加硬件。乱调参数最常见的后果是延迟没降，反而把从库压垮。把延迟做成可观测、有分级告警的指标，比把它压到 0 更重要。

以上命令在 MySQL 5.7.36 与 8.0.32 上均实测通过，供参考。

dbaai