主从延迟与复制故障

主从延迟的麻烦之处在于它不报错：主库写入正常，从库只是“旧了”。等到用户反馈“刚下的单查不到”“改完的资料又变回去了”，延迟往往已经持续十几分钟。更糟的是复制中断，Slave_SQL_Running = No 意味着从库停止追赶，延迟无限增长，业务一致性彻底失守。

现象与背景

  • 写后读不一致：主库刚提交的数据，从库查不到或查到旧值。
  • 监控延迟曲线抬头：Seconds_Behind_Master 由 0 变成几十、几百秒。
  • 复制报错：Last_SQL_Error 有内容，Slave_SQL_Running = No。
  • 从库磁盘或 IO 打满，Relay_Log_Space 持续增长说明 SQL 线程追不上 IO 线程。

第一步：看清复制状态

-- 5.7 与 8.0 都支持；8.0 中 SHOW SLAVE STATUS 仍可用（推荐逐步迁到 SHOW REPLICA STATUS）
SHOW SLAVE STATUS\G
列判读标准异常含义
Slave_IO_Running应为 YesNo 表示拉不到主库 binlog，查网络、账号、主库 binlog 是否被删
Slave_SQL_Running应为 YesNo 表示回放中断，必须看 Last_SQL_Error
Seconds_Behind_Master0 为正常NULL 表示复制已断，延迟值不可信；仅表示 SQL 线程落后 IO 线程的时间差
Relay_Log_Space通常较小且稳定持续增长说明 SQL 线程回放慢，relay log 在堆积
Master_Log_File / Read_Master_Log_Pos应持续前进长时间不动表示 IO 线程卡住
Retrieved_Gtid_Set已拉取的 GTID 集合与 Executed_Gtid_Set 的差集就是待回放事务数
Executed_Gtid_Set已回放的 GTID 集合差集越大延迟越高
Last_SQL_Error应为空有内容即为复制中断的根因
Slave_SQL_Running_StateSlave has read all relay log; waiting for more updates若长期是 Waiting for ... lock 说明从库上有锁竞争

Seconds_Behind_Master 有三个已知局限，不要只信它：一是复制中断时它为 NULL 而不是变大；二是它按 binlog 事件时间戳计算，主库长时间无写入时会失真地显示为 0；三是多线程复制下它反映的是“最后一个提交的事务”与 IO 线程的差，不能代表所有库表的实际落后程度。

GTID 环境下更可靠的判断方式：

-- 比较主库与从库的 GTID 集合，看从库还差多少事务
SELECT @@GLOBAL.gtid_executed;        -- 分别在主库、从库执行

-- 从库上按时间看最近回放的事务（8.0）
SELECT * FROM performance_schema.replication_applier_status_by_worker\G

第二步：区分延迟根因

根因特征验证方式
大事务延迟呈锯齿状，一次涨几百秒再慢慢回落主库看单事务修改行数、binlog 事件大小
单线程回放主库并发写高，从库只有一条 SQL 线程在追slave_parallel_workers 是否为 0
从库硬件差从库 CPU/IO 长期饱和对比主从 IOPS、磁盘类型
DDL 阻塞从库上执行长 DDL，后续事务全部排队Slave_SQL_Running_State 显示 altering table
无主键表回放时需要全表扫描定位行，行数越多越慢SHOW TABLES 配合 information_schema.tables 反查
从库跑重查询从库上有人跑报表、备份、SELECT ... FOR UPDATESHOW PROCESSLIST 看非复制线程
网络抖动IO 线程断连重连，Last_IO_Error 有超时记录看 Last_IO_Errno 与网络监控

第三步：排查命令

# 主库侧：看当前 binlog 写入速率与文件位置
mysql -uroot -p -e "SHOW MASTER STATUS\G"
mysql -uroot -p -e "SHOW BINARY LOGS;"

# 从库侧：读取速率、错误与延迟
mysql -uroot -p -e "SHOW SLAVE STATUS\G" | grep -E \
 'Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master|Relay_Log_Space|Last_.*Error|Retrieved_Gtid|Executed_Gtid'

-- 从库上找“不是复制线程”的会话，排除人为干扰
SELECT id, user, host, db, command, time, state, LEFT(info, 100) AS sql_text
FROM information_schema.processlist
WHERE user NOT IN ('system user', 'repl');

检查主从关键参数是否一致（不一致的 sql_mode、字符集、binlog_format 会导致数据漂移或复制报错）：

SHOW VARIABLES WHERE Variable_name IN
 ('binlog_format','server_id','log_bin','gtid_mode','enforce_gtid_consistency',
  'slave_parallel_workers','slave_parallel_type','sql_mode','character_set_server');

第四步：处置动作

  • IO 线程报错：先确认主库 binlog 是否被 purge、账号权限是否失效、网络是否可达；修复后 START SLAVE IO_THREAD。
  • SQL 线程报错（例如 1062 主键冲突、1032 行不存在）：先定位具体表与 GTID，判断是数据漂移还是逻辑错误。
-- 5.7 与 8.0 通用；跳过单个事务是最后手段，务必先记录 GTID 并评估一致性
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;   -- 仅限非 GTID 模式；GTID 模式改用空事务跳过
START SLAVE;

-- GTID 模式下跳过指定事务（把 gtid 换成实际值）
STOP SLAVE;
SET GTID_NEXT = 'aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:1001';
BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START SLAVE;
  • 大事务导致延迟：等它回放完是最稳的做法；确需加速可在从库临时关闭 log_slave_updates 与 sync_binlog（有风险，须评估）。
  • 单线程回放：5.7 与 8.0 都支持并行复制，按需开启。
-- 5.7：基于组提交的并行复制，需主库 binlog_group_commit 配合
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 8;
SET GLOBAL slave_preserve_commit_order = 1;   -- 5.7.6+ / 8.0，保证提交顺序一致

-- 8.0：参数名从 slave_ 改为 replica_，slave_ 别名仍可用但会告警
SET GLOBAL replica_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL replica_parallel_workers = 8;
SET GLOBAL replica_preserve_commit_order = 1;

-- 主库侧配合，减少组提交间隔、提升从库并行度（会略微增加丢事务风险）
SET GLOBAL binlog_group_commit_sync_delay = 1000;      -- 微秒
SET GLOBAL binlog_group_commit_sync_no_delay_count = 10;

参数名差异要记牢：5.7 用 slave_*，8.0 起官方推荐 replica_*，而 SHOW SLAVE STATUS 在 8.0 中仍可用，但新写脚本建议用 SHOW REPLICA STATUS。

业务侧的应对

场景做法
写后立刻读（下单跳详情）强制走主库，或用 GTID/位点判断从库是否已追上
延迟超过阈值读流量按比例降级回主库，先保一致性
报表与统计查询放到单独的延迟不敏感的从库，不要和在线读混用
缓存兜底写后主动更新或失效缓存，减少对从库实时性的依赖

预防与巡检项

  • 大事务拆小：单事务修改行数控制在几千行以内，批量任务分批提交。
  • 所有表必须有主键，无主键表在行模式下回放代价极高。
  • 从库禁止跑重查询与备份以外的分析任务，必要时用独立延迟从库承接。
  • 延迟监控至少取两条线：Seconds_Behind_Master 与 GTID 差集，超过 10 秒告警。
  • DDL 变更先在从库执行或使用 gh-ost 这类工具，避免主库 DDL 一次性把从库压垮。

小结：主从问题先用 SHOW SLAVE STATUS 分清是 IO 线程故障还是 SQL 线程回放慢，Seconds_Behind_Master 只看趋势、复制中断时要看 GTID 差集；延迟根因多为大事务、单线程回放、从库资源不足与从库上的人为查询，处置按“先止血（读降级回主库）、再修复（修复制错误）、后优化（并行复制与拆大事务）”推进，业务侧用位点判断或强制主库解决写后读一致性问题。

笔记加载中…

标签：#故障排查 #运维

返回知识库