MySQL 主从复制延迟:从定位到恢复的排查手册
主从复制延迟是 MySQL 运维中最常见、也最容易被误判的一类故障。读请求打到从库却查不到刚写入的数据,报表数字对不上,缓存与数据库不一致,很多线上问题最后都能追溯到复制延迟。本文给出一条从定位到恢复的完整排查路径,照着走,大多数延迟都能在一小时内找到根因。
一、什么是复制延迟,延迟多少算问题
主从复制的基本链路是:主库写 binlog,从库 IO 线程拉取 binlog 写入 relay log,从库 SQL 线程回放 relay log。任何一个环节变慢,都会表现为从库落后于主库。Seconds_Behind_Master 是最常用的观测值,它表示从库 SQL 线程与主库最新事务的时间差。需要注意,这个值在从库空闲、复制中断或 SQL 线程卡住时都可能显示为 0 或 NULL,单独看它并不够。业务上更直接的判断标准是:主库写入后,从库在可接受时间内是否可见。一般业务可以容忍 1 秒以内的延迟,超过 5 秒就应当告警,超过 30 秒基本可以认定为故障,需要立即介入。
二、先确认:延迟是真的吗
动手之前,先在从库上确认复制状态是否正常。
SHOW REPLICA STATUS\G
重点看四项:Replica_IO_Running 与 Replica_SQL_Running 是否都为 Yes,Seconds_Behind_Master 的具体数值,以及 Last_Error 是否有报错信息。MySQL 5.7 及更早版本使用 SHOW SLAVE STATUS 语句,字段名对应为 Slave_IO_Running、Slave_SQL_Running 和 Last_Error,含义相同。如果两个线程都是 Yes 但延迟持续增大,说明复制在跑,只是跟不上;如果 SQL 线程为 No,说明回放已经中断,先看 Last_Error 的报错内容。如果 IO 线程为 No,先检查主从之间的网络连通性和主库 binlog 是否已被清理。
三、定位四步法
第一步,看延迟曲线。去监控里拉出过去 24 小时 Seconds_Behind_Master 的曲线,确认延迟是突发、缓慢爬升还是周期性出现。突发延迟往往对应大事务或 DDL,缓慢爬升对应从库回放能力不足,周期性延迟往往对应定时任务或批量导入。
第二步,看从库在忙什么。在从库上执行 SHOW PROCESSLIST,重点关注 system user 的 SQL 线程正在执行什么语句,是否长时间停留在同一条语句上。再执行 SHOW OPEN TABLES WHERE In_use 大于 0,确认是否有长事务持有锁。
第三步,看主库在写什么。在主库上执行 SHOW PROCESSLIST 和 SHOW MASTER STATUS,确认主库 binlog 的写入速度,检查同一时间段是否有大事务、批量导入或 DDL 操作。可以结合慢查询日志,找出写入量最大的几条语句。
第四步,看资源瓶颈。分别检查主从的 CPU、内存、磁盘 IO 和磁盘空间。从库磁盘 IO 饱和是最常见的硬件原因,用 iostat -x 1 观察磁盘利用率,如果 util 持续接近 100,说明磁盘已经成为瓶颈。
四、七类常见原因与处理
原因一,大事务与长事务。主库上一个几十万行的批量更新或删除,会在 binlog 里生成一个巨大的事务,从库必须完整回放这个事务才能继续,期间延迟直线上升。处理方法是把大事务拆成小批次,每批控制在 1000 到 5000 行,批次之间适当停顿。事后可以在从库上通过 performance_schema 查看正在回放的事务信息。
SELECT * FROM performance_schema.replication_applier_status_by_worker\G
原因二,从库回放是单线程。默认情况下从库只有一个 SQL 线程回放,而主库可以多线程并发写入,写放大后从库自然跟不上。MySQL 5.7 之后支持基于写集的并行复制,开启方法如下。
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 4;
并行度建议从 4 开始,根据从库 CPU 核数逐步调整,改完后需要重启复制线程生效:先执行 STOP SLAVE,再执行 START SLAVE。
原因三,从库读负载过重。很多团队把从库当作报表库和统计库,大查询会抢占回放线程的 IO 和 CPU。处理方法是把重查询迁移到专门的只读实例,或者限制报表查询的并发数,核心原则是:回放优先,查询让路。
原因四,DDL 与锁等待。主库执行 ALTER TABLE 加字段、加索引时,从库回放 DDL 同样会锁表,期间所有后续事务都要排队。大表 DDL 应当使用在线 DDL 工具,例如 pt-online-schema-change 或 gh-ost,避免长时间锁表。
原因五,网络与磁盘。跨机房、跨可用区复制时,网络抖动会直接拖慢 IO 线程拉取 binlog 的速度;从库磁盘写满或磁盘老化,会导致 relay log 写入变慢。先检查磁盘剩余空间:df -h,再检查主从之间的网络延迟与丢包:ping 主库地址,观察往返时延是否稳定。
原因六,主从配置差异。从库硬件比主库差、innodb_buffer_pool_size 配得过小、sync_binlog 与 innodb_flush_log_at_trx_commit 在主从上设置不一致,都会放大延迟。建议从库的缓冲池不小于主库,磁盘使用 SSD,关键参数与主库保持一致。
原因七,版本与参数问题。主从版本跨度过大、binlog_format 设置为 STATEMENT 而非 ROW,都可能导致回放效率下降。生产环境推荐使用 ROW 格式,配合 binlog_row_image 设置为 MINIMAL,减少 binlog 体积。
五、追赶与恢复操作
确认根因并处理后,延迟一般会自行追赶。如果延迟长时间不下降,可以按以下顺序操作:先确认没有新的大事务写入,再确认从库 SQL 线程正常运行,最后观察 Seconds_Behind_Master 是否开始下降。不要轻易执行 RESET SLAVE 或重建从库,那会丢失已有的复制进度。如果复制线程报错停止,先记录 Last_Error 的完整信息,处理报错原因后再执行 START SLAVE,不要直接跳过错误继续复制,否则可能造成数据不一致。确实需要重建从库时,优先使用备份加 binlog 位点的方式恢复,或者使用物理备份工具做全量同步,重建完成后重新核对主从数据一致性。
六、预防与监控
第一,对 Seconds_Behind_Master 设置两级告警:超过 5 秒发警告,超过 30 秒发严重告警,同时监控复制线程状态,一旦 IO 或 SQL 线程停止立即告警。第二,监控主从 binlog 写入量和 relay log 积压量,写入量突增往往是延迟的前兆。第三,建立大事务与 DDL 的上线审批,批量写入必须分批,大表 DDL 必须走在线工具,变更窗口避开业务高峰。第四,定期做主从切换演练,验证从库确实能接管读流量,避免平时不用的从库在关键时刻掉链子。
七、速查清单
从库执行 SHOW REPLICA STATUS,确认两个线程状态与延迟值;执行 SHOW PROCESSLIST,确认 SQL 线程是否卡在某条语句;检查监控曲线,判断延迟是突发还是爬升;检查主库慢查询与大事务;检查从库磁盘 IO 与空间;检查网络延迟;核对主从参数与版本;确认并行复制是否开启。按照这个顺序走一遍,九成以上的复制延迟都能定位到具体环节。
结语
复制延迟并不可怕,可怕的是没有监控、没有预案。先用 Seconds_Behind_Master 与复制线程状态确认事实,再用四步法定位环节,最后对症处理并做好监控与变更管控,延迟就能从救火变成可控的日常指标。建议把本文的速查清单保存下来,下次告警响起时直接照着执行。
- 点赞
- 收藏
- 关注作者
评论(0)