当前位置： 首页 > news >正文

MySQL 主从延迟排查全流程：不是只看 Seconds_Behind_Master【转】

news 2026/3/26 17:29:54

一、概述

1.1 背景介绍

复制延迟一上来，很多人先盯 Seconds_Behind_Master。这个指标当然要看，但它只能告诉你“延迟已经发生了”，不能告诉你是网络拉取慢、Relay Log 堆积、SQL 线程执行慢、并行复制没吃满，还是下游被长事务、DDL、热点表拖住了。

生产环境更稳的排查方式是：先分清 IO 线程和 SQL 线程，再判断是拉不到、写不进、还是应用不过来。如果只是看单个秒数，最容易把真正的问题藏掉。

1.2 技术特点

  • 从复制链路拆问题：Source → 网络 → IO Thread → Relay Log → SQL / Applier Thread。
  • 兼顾新旧版本字段：MySQL 8.0 推荐 SHOW REPLICA STATUS，旧环境仍可能看到 Seconds_Behind_Master。
  • 贴近线上治理：不仅说怎么查，也说怎么避免复制被长事务拖死。

1.3 适用场景

  • 场景一：业务读流量打到从库，延迟突然升高导致读到旧数据。
  • 场景二：主库写入正常，从库 relay log 堆积。
  • 场景三：发布、DDL、批处理后复制延迟持续几分钟甚至几十分钟。

1.4 环境要求

组件版本要求说明
MySQL 8.0+ 推荐 示例以 SHOW REPLICA STATUS 为主
复制模式 GTID 或传统 binlog 位点 两者命令略有差异
指标采集 mysqld_exporter 监控复制延迟与线程状态
权限 具备复制和性能视图查询权限 需要查 performance_schema

二、详细步骤

2.1 准备工作

2.1.1 系统检查

SHOW REPLICA STATUS\G
SHOW PROCESSLIST;
SHOW VARIABLES LIKE 'server_id';
SHOW VARIABLES LIKE 'gtid_mode';
SHOW VARIABLES LIKE 'slave_parallel_workers';

先回答：

  1. IO 线程是否正常拉日志
  2. SQL / Applier 线程是否正常执行
  3. 延迟是持续增长还是可追平
  4. 并行复制有没有启用，是否真的吃满

2.1.2 安装依赖

sudo apt update || true
sudo apt install -y mysql-client jq || true
sudo yum install -y mysql jq || true

2.1.3 第一轮确认

SHOW REPLICA STATUS\G
SELECT * FROM performance_schema.replication_connection_status\G
SELECT * FROM performance_schema.replication_applier_status_by_worker\G

2.2 核心配置

2.2.1 第一步：先区分“拉取慢”还是“执行慢”

核心判断字段：

  • Replica_IO_Running / Slave_IO_Running
  • Replica_SQL_Running / Slave_SQL_Running
  • Seconds_Behind_Source / Seconds_Behind_Master
  • Relay_Log_Space
  • Last_IO_Error、Last_SQL_Error

如果 IO 线程不正常，优先查：

SHOW REPLICA STATUS\G

如果 SQL 线程正常但追不上，优先查：

SELECT WORKER_ID, LAST_APPLIED_TRANSACTION, APPLYING_TRANSACTION,
       LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP,
       LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP
FROM performance_schema.replication_applier_status_by_worker;

2.2.2 第二步：标准配置示例

# 文件路径：/etc/my.cnf.d/replication.cnf
[mysqld]
server_id=102
log_bin=mysql-bin
binlog_format=ROW
gtid_mode=ON
enforce_gtid_consistency=ON
relay_log_recovery=ON
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=8
read_only=ON
super_read_only=ON

参数说明：

  • binlog_format=ROW：复制一致性更稳
  • relay_log_recovery=ON：异常重启后更容易恢复 relay log 状态
  • slave_parallel_workers=8：并行复制要按业务写入模型调，不是越大越好
  • super_read_only=ON：避免业务误写从库

2.2.3 第三步：按三条线下钻

看复制状态：

SHOW REPLICA STATUS\G
SHOW BINARY LOG STATUS;

看热点事务和锁：

SHOW PROCESSLIST;
SELECT * FROM information_schema.innodb_trx\G
SHOW ENGINE INNODB STATUS\G

看延迟是否卡在单个事务：

SELECT THREAD_ID, EVENT_NAME, TIMER_WAIT
FROM performance_schema.events_stages_current
WHERE EVENT_NAME LIKE 'stage/sql/%';

2.3 启动和验证

2.3.1 启动服务

STOP REPLICA;
START REPLICA;
SHOW REPLICA STATUS\G

2.3.2 功能验证

SHOW REPLICA STATUS\G
SELECT * FROM performance_schema.replication_connection_status\G
SELECT * FROM performance_schema.replication_applier_status_by_worker\G

三、示例代码和配置

3.1 完整配置示例

3.1.1 主配置文件

# 文件路径：/etc/my.cnf.d/replication.cnf
[mysqld]
server_id=102
log_bin=mysql-bin
binlog_format=ROW
gtid_mode=ON
enforce_gtid_consistency=ON
relay_log_recovery=ON
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=8
read_only=ON
super_read_only=ON

3.1.2 辅助脚本

#!/usr/bin/env bash
set -euo pipefail

MYSQL_CMD="${MYSQL_CMD:-mysql -uroot -p}"
OUT_DIR="/tmp/mysql-replica-$(date +%F-%H%M%S)"
mkdir -p "$OUT_DIR"

$MYSQL_CMD -e "SHOW REPLICA STATUS\G" > "$OUT_DIR/replica-status.txt"
$MYSQL_CMD -e "SHOW PROCESSLIST" > "$OUT_DIR/processlist.txt"
$MYSQL_CMD -e "SELECT * FROM performance_schema.replication_connection_status\G" > "$OUT_DIR/connection-status.txt"
$MYSQL_CMD -e "SELECT * FROM performance_schema.replication_applier_status_by_worker\G" > "$OUT_DIR/applier-status.txt"
$MYSQL_CMD -e "SHOW ENGINE INNODB STATUS\G" > "$OUT_DIR/innodb-status.txt"

echo "artifacts saved to $OUT_DIR"

3.2 实际应用案例

案例一：不是网络慢，是单个大事务把 SQL 线程卡住

场景描述：从库延迟从几十毫秒涨到 18 分钟，主库没报错，网络也正常。

实现代码：

SHOW REPLICA STATUS\G
SHOW PROCESSLIST;
SELECT * FROM information_schema.innodb_trx\G

运行结果：

Seconds_Behind_Source: 1087
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Relay_Log_Space: 1293844832

根因是主库一次性提交了超大批量更新事务，从库 SQL 线程长时间卡在单事务 apply。处理动作：

  1. 把批处理切小批次
  2. 大表更新改成分段执行
  3. 保留并行复制但别指望它能拆开单个超大事务

案例二：不是 SQL 线程慢，是 IO 线程拉不到新 binlog

场景描述：主从延迟持续增长，Relay_Log_Space 却不大。

实现步骤：

  1. 看 IO/SQL 线程

    SHOW REPLICA STATUS\G

  2. 看错误信息

    SHOW REPLICA STATUS\G

  3. 查网络和权限

    mysql -h source-db -e "SHOW BINARY LOG STATUS;"
    telnet source-db 3306

根因是复制链路网络抖动叠加复制账号权限异常，IO 线程断续重连，延迟不断累积。

案例三：DDL 和元数据锁把复制线程长期卡住

场景描述：某次结构变更后，从库延迟持续 40 多分钟。IO 线程正常，Relay Log 也在增长，但 SQL 线程一直追不上。

实现步骤：

  1. 看复制状态

    SHOW REPLICA STATUS\G

  2. 查元数据锁和事务

    SELECT * FROM performance_schema.metadata_locks\G
    SELECT * FROM information_schema.innodb_trx\G
    SHOW PROCESSLIST;

  3. 看是否被 DDL 卡住

    SHOW ENGINE INNODB STATUS\G

运行结果：

Waiting for table metadata lock

根因是主库 DDL 进入复制链路后，从库上又有长查询占着元数据锁，SQL 线程一直卡在同一条语句。处理动作：

  • 先终止阻塞复制的长查询
  • 大表 DDL 改到低峰窗口
  • 对高风险 DDL 预演复制影响

这类延迟如果只看 Seconds_Behind_Source，你知道它慢了，但不知道它为什么永远追不上。

四、最佳实践和注意事项

4.1 最佳实践

4.1.1 性能优化

  • 优化点一：并行复制要开，但要结合业务写入模式验证，热点表和单大事务不会因为 worker 多就 magically 变快。
  • 优化点二：批处理、DDL、大事务必须做节流和窗口管理。
  • 优化点三：主从延迟监控不能只看秒数，还要看 IO/SQL 线程状态和 Relay Log 增长速度。

4.1.2 安全加固

  • 安全措施一：从库保持 super_read_only=ON，避免误写。
  • 安全措施二：复制账号最小权限，定期轮换密码。
  • 安全措施三：复制链路变更先在从库灰度验证。

4.1.3 高可用配置

  • HA 方案一：关键读流量不要只绑一台从库，延迟高时可自动摘流。
  • HA 方案二：复制监控和业务读延迟监控联动。
  • 备份策略：保留关键时段 SHOW REPLICA STATUS 和 performance_schema 快照。

4.2 注意事项

4.2.1 配置注意事项

⚠️ 警告：Seconds_Behind_Master 或 Seconds_Behind_Source 为 0，不等于绝对没问题。复制线程断开、SQL thread 卡住、延迟刚好被短暂追平，都可能让你误判。

  • ❗ 大事务和 DDL 是复制延迟的常见放大器
  • ❗ 并行复制没配置好，延迟会长期追不上
  • ❗ 从库性能不足时，复制延迟本质上是资源问题

4.2.2 常见错误

错误现象原因分析解决方案
秒数很高，但线程都正常 SQL 线程在追大事务 查长事务、DDL、热点表
秒数不高，但读流量读到旧数据 监控窗口太粗或刚短暂追平 增加更细粒度采样
Relay Log 持续增长 IO 正常拉取，SQL 执行跟不上 查 apply 能力、锁等待、资源

4.2.3 兼容性问题

  • 版本兼容：8.0 新旧字段名有差异，脚本里要兼容 Master/Source 命名。
  • 平台兼容：云盘性能、跨可用区网络时延会直接影响复制。
  • 组件依赖：GTID、MTS、binlog 格式和 DDL 策略都会影响复制行为。

五、故障排查和监控

5.1 故障排查

5.1.1 日志查看

grep -Ei 'replica|slave|relay|error' /var/log/mysqld.log | tail -50

5.1.2 常见问题排查

问题一：延迟升高，但 Relay_Log_Space 不大

SHOW REPLICA STATUS\G

解决方案：优先查 IO 线程、网络、权限、主库 binlog 获取。

问题二：Relay_Log_Space 很大，秒数持续增加

SHOW REPLICA STATUS\G
SHOW PROCESSLIST;

解决方案：优先查 SQL 线程 apply 慢、锁等待、大事务。

问题三：并行复制开了，还是追不上

  • 症状：slave_parallel_workers 大于 0，但延迟长期不降

  • 排查：

    SHOW VARIABLES LIKE 'slave_parallel%';
    SELECT * FROM performance_schema.replication_applier_status_by_worker\G

  • 解决：判断写入是否具备并行空间，热点表和单大事务场景要从业务侧拆解

5.1.3 调试模式

SHOW REPLICA STATUS\G
SHOW ENGINE INNODB STATUS\G

5.2 性能监控

5.2.1 关键指标监控

SHOW REPLICA STATUS\G

5.2.2 监控指标说明

指标名称正常范围告警阈值说明
复制延迟秒数 < 1s > 10s 持续 5m 只做结果告警
IO 线程状态 Running 非 Running 日志拉取异常
SQL 线程状态 Running 非 Running Apply 异常
Relay Log 空间 平稳 持续增长 15m 执行跟不上

5.2.3 监控告警配置

groups:
  - name: mysql-replication
    rules:
      - alert: MySQLReplicationLagHigh
        expr: mysql_slave_status_seconds_behind_master > 10
        for: 5m
      - alert: MySQLReplicationSQLThreadDown
        expr: mysql_slave_status_sql_running == 0
        for: 1m
      - alert: MySQLReplicationIOThreadDown
        expr: mysql_slave_status_slave_io_running == 0
        for: 1m

5.3 备份与恢复

5.3.1 备份策略

#!/usr/bin/env bash
set -euo pipefail
mysql -uroot -p -e "SHOW REPLICA STATUS\G" > /backup/replica-status-$(date +%F).txt
mysql -uroot -p -e "SHOW MASTER STATUS\G" > /backup/master-status-$(date +%F).txt

5.3.2 恢复流程

  1. 采样现场：bash ./mysql-replica-collect.sh
  2. 停止复制：STOP REPLICA;
  3. 修复根因后恢复：START REPLICA;
  4. 验证追平：SHOW REPLICA STATUS\G

六、总结

6.1 技术要点回顾

  • ✅ 主从延迟不能只看秒数
  • ✅ 先分 IO 线程和 SQL 线程，再看 Relay Log 和事务特征
  • ✅ 大事务、DDL、热点表是最常见的复制放大器
  • ✅ 并行复制能提速，但救不了单个超大事务

6.2 进阶学习方向

  1. GTID 与复制拓扑治理
  2. 并行复制与事务拆分
  3. 只读流量摘流与延迟感知路由

6.3 参考资料

  • MySQL Replication Status - 复制状态字段说明
  • Performance Schema Replication Tables - 复制相关性能视图
  • MySQL Replication Options - 从库复制参数

附录

A. 命令速查表

SHOW REPLICA STATUS\G
SHOW PROCESSLIST;
SELECT * FROM performance_schema.replication_connection_status\G
SELECT * FROM performance_schema.replication_applier_status_by_worker\G
SHOW ENGINE INNODB STATUS\G

B. 配置参数详解

  • slave_parallel_workers：并行复制 worker 数
  • relay_log_recovery：异常重启后 relay log 恢复
  • super_read_only：更严格的只读保护

C. 术语表

术语英文解释
复制延迟 Replication Lag 从库应用事务落后主库的时间差
中继日志 Relay Log 从库拉取主库 binlog 后本地保存的日志
应用线程 SQL / Applier Thread 在从库执行事务的线程
GTID Global Transaction Identifier 全局事务标识

 

转自

MySQL 主从延迟排查全流程：不是只看 Seconds_Behind_Master

https://mp.weixin.qq.com/s/S2IbAwT1WQv4RKCQlJvYeg

http://www.jsqmd.com/news/457112/

相关文章：

  • EDA 工具国产替代：2026年行业趋势与自主可控方案讲解 - 品牌2026
  • AI写专著新玩法！利用AI工具，轻松搞定专著选题与框架搭建
  • WPF新手村教程（四）—— Application类
  • 超简单的FFT IP RTL实现：用Cordic搞定蝶形运算
  • 掌握AI专著生成技巧！专业工具助力，轻松完成学术专著创作
  • 2026辊筒干燥机优质推荐榜适配多领域需求：红薯全粉设备、芋头全粉设备、辊筒刮板干燥机、酵母辊筒干燥机选择指南 - 优质品牌商家
  • 掌握AI写专著技巧，借助专业工具轻松打造百万字学术巨著
  • HTML,CSS与JS前端基础课堂笔记
  • Anthropic Agent 工程实战指南：从入门到生产落地 - 广东靓仔
  • 2026国产高端EDA工具推荐：功能、适配与替代方案 - 品牌2026
  • 杭州大厦购物卡回收方法教学与关键提示 - 京回收小程序
  • 揭秘AI写专著的神奇魔法！超实用工具让专著撰写易如反掌
  • 2026年薯类深加工全粉设备推荐指南 - 优质品牌商家
  • 2026年京津冀家庭装修公司年度排名，高性价比公司有哪些 - 工业品牌热点
  • AI写专著，专家评测靠谱工具，告别繁琐流程轻松完成专著
  • OpenClaw 完整指南 2026：从零开始打造你的 AI 助理
  • 分析避雷塔维修生产厂哪家更值得选，各地区靠谱厂家揭秘 - myqiye
  • MySQL EXPLAIN
  • 2026东南亚整柜交付到美国DDP，性价比高的品牌有哪些 - 工业推荐榜
  • 解锁AI专著生成新方式，优质工具助力学术专著创作
  • 弹窗（取消、确认、切换alert 确认/输入/上床文件弹框）
  • Ubuntu系统安装了Quartus 25.1.1，为什么terminal就是找不到quartus_pgm 和sopc-create-header-files（要设置环境变量）
  • 显示时间
  • AI专著生成工具哪家强？详细测评，为你选出最佳帮手
  • Ubuntu系统安装了Quartus 25.1.1，为什么terminal就是找不到quartus_pgm 和sopc-create-header-files
  • 织梦上传图片弹出提示302的解决办法dedecms
  • 【高精度气象】大宗商品的“天气溢价”：CTA基金已将气象因子纳入高频交易模型的秘密
  • Cursor 四种交互模式
  • 智能网联汽车故障诊断仿真教学软件技术解析——C/S架构落地与全模块实现
  • 2026年十大高端网站建设公司推荐一览表：精选综合实力强的网站搭建供应商与全行业匹配指南 - 博客万