MySQL 主从复制延迟排查

一、案例背景：分表操作引发的 “隐性” 延迟

某业务团队为解决单表数据量过大（千万级）的查询性能问题，设计了分表方案：从 1 张历史大表中读取数据，经过业务逻辑计算后，批量写入 100 个按规则拆分的分表（命名格式为db.t_sharding_XX）。上线初期未发现异常，但次日运维监控告警显示：从库与主库的同步延迟持续扩大，最终达到 37325 秒（约 10.3 小时），且从库relay log（中继日志）体积异常膨胀。

关键异常现象梳理

通过show slave status\G命令查看从库状态，核心异常指标如下：

指标名称	异常值	正常参考	说明
Seconds_Behind_Master	37325 秒	0-10 秒	从库落后主库约 10.3 小时，同步严重滞后
Relay_Log_Space	7046814599 字节（约 6.56GB）	随业务波动，无固定值	中继日志总大小远超常规，说明从库 SQL 线程执行缓慢
Relay_Master_Log_File	mysql-bin.003731	与主库 Master_Log_File 一致	从库仍在执行主库较早的 binlog（主库已到 mysql-bin.003735），相差 4 个 binlog 文件
Slave_IO_Running / Slave_SQL_Running	Yes / Yes	均为 Yes	从库 IO 线程（接收主库 binlog）与 SQL 线程（执行 relay log）未中断，排除线程异常导致的延迟

二、分步排查：从 “现象” 定位 “根源”

主从延迟的原因多样（如网络带宽、索引缺失、大事务、参数配置等），需按 “先排查事务→再查日志→最后解析 binlog” 的逻辑逐步缩小范围，避免盲目优化。

步骤 1：排查从库活跃事务 —— 锁定 “大事务” 嫌疑

从库 SQL 线程执行延迟的核心原因之一是大事务阻塞：若主库执行的事务包含大量数据操作（如批量插入、多表更新），从库需完整复现该事务，耗时会远超过主库（主库可并行写入，从库默认单线程执行）。

通过查询INFORMATION_SCHEMA.INNODB_TRX表（InnoDB 事务状态表），定位从库当前活跃事务：

mysql> select * from information_schema.innodb_trx\G;

 

关键结果：

• trx_state: RUNNING：存在持续运行的活跃事务；
• trx_rows_modified: 2598408：事务已修改近 260 万行数据，属于典型大事务；
• trx_operation_state: inserting：事务仍在执行插入操作，未结束；
• trx_tables_locked: 100：事务锁定了 100 张表，与业务分表数量（100 个分表）完全匹配。

这一步初步锁定：分表操作引发的多表写入大事务，是导致从库延迟的核心嫌疑对象。

步骤 2：检查从库 Relay Log 大小 —— 验证 “执行缓慢”

relay log是从库 IO 线程接收的主库 binlog 副本，由 SQL 线程逐句执行。若 SQL 线程执行速度慢于 IO 线程接收速度，relay log会持续堆积，体积异常增大。

通过 Linux 命令查看relay log文件大小：

root@node-1:/# ls -lh /var/lib/mysql/nep-1-relay-bin.*

 

关键结果：

• 多个relay log文件大小达到 1.6GB，远超 MySQL 默认max_relay_log_size（1.1GB）；
• 新的relay log文件持续生成（如 nep-1-relay-bin.006184 至 006195），说明 IO 线程仍在接收主库 binlog，但 SQL 线程处理滞后严重。

这一步进一步验证：从库 SQL 线程被大事务阻塞，无法及时处理relay log，导致日志堆积。

步骤 3：解析主库 Binlog—— 排除 “单条大批量操作”

大事务有两种常见形式：①单条 SQL 操作大量数据（如INSERT INTO ... SELECT * FROM 大表）；②多条小 SQL 组成的长事务（如循环插入 100 张分表，未及时提交）。需通过解析主库 binlog，明确事务的具体形态。

使用mysqlbinlog工具解析主库延迟相关的 binlog 文件（mysql-bin.003731），统计单条 SQL 的操作行数：

mysqlbinlog --base64-output=decode-rows -vv mysql-bin.003731 | awk '
BEGIN {
    s_type=""; s_count=0; count=0; insert_count=0; update_count=0; delete_count=0; flag=0;
}
{
    if(match($0, /^#.*Table_map:.*mapped to number/)) {
        printf "Timestamp : " $1 " " $2 " Table : " $(NF-4); flag=1
    }
    else if (match($0, /### INSERT INTO .*..*/)) {
        count++; insert_count++; s_type="INSERT"; s_count++;
    }
    else if (match($0, /### UPDATE .*..*/)) {
        count++; update_count++; s_type="UPDATE"; s_count++;
    }
    else if (match($0, /### DELETE FROM .*..*/)) {
        count++; delete_count++; s_type="DELETE"; s_count++;
    }
    else if (match($0, /^# at/) && flag==1 && s_count>0) {
        print " Query Type : " s_type " " s_count " row(s) affected";
        s_type=""; s_count=0;
    }
    else if (match($0, /^COMMIT/)) {
        print "Total: " count " rows (INSERT:" insert_count ", UPDATE:" update_count ", DELETE:" delete_count ")";
        count=0; insert_count=0; update_count=0; delete_count=0; s_type=""; s_count=0; flag=0;
    }
}' > 003731.txt

 

对结果按操作行数排序：

 

awk '{print $12}' 003731.txt | sort | uniq -c | sort -nr | head -n 5

 

关键结果：

• 单条 INSERT/UPDATE/DELETE 操作的行数多为 1-4 行，无 “单次插入几十万行” 的大批量 SQL；
• 最大单条操作行数仅为 4 行，排除 “单条 SQL 引发大事务” 的可能。

步骤 4：解析 Binlog 事务大小 —— 锁定 “长事务” 根源

既然单条 SQL 操作行数不大，为何会形成大事务？需进一步分析事务的整体大小（即一个事务包含的所有 SQL 对应的 binlog 体积）。

通过mysqlbinlog提取事务的起止位置，计算事务对应的 binlog 大小：

 

# 提取事务开始与结束的binlog位置
mysqlbinlog mysql-bin.003731 | grep "^BEGIN" -A 2 | grep -E "^# at" | awk '{print $3}' > 003731_trx_pos.txt

# 计算相邻位置差（即事务大小），并按大小排序
awk 'NR==1 {at=$1} NR>1 {print $1-at; at=$1}' 003731_trx_pos.txt | sort -nr | head -n 20 > 003731_top_20.txt

关键结果：

• 存在 2 个超大事务，binlog 体积分别为 822733047 字节（约 784MB）和 822623266 字节（约 766MB）；
• 结合relay log总大小（约 6.56GB），推测主库持续生成此类大事务，导致从库 SQL 线程 “疲于奔命”。

步骤 5：用 my2sql 深度分析 —— 还原大事务细节

为更直观地查看大事务的执行时长、涉及表、数据量，使用开源工具my2sql（MySQL binlog 解析工具，支持统计大事务、生成回滚 SQL 等）进一步分析：

 

./my2sql -user repuser -password repuserpassword -host 10.235.98.18 -port 3306 \
-work-type stats -start-file mysql-bin.003731 -stop-file mysql-bin.003735 \
-big-trx-row-limit 5000 -output-dir tmp/log/my2sql_output

 

分析输出文件biglong_trx.txt，得到核心结论：

1. 事务数量与时长：共存在 8 个大事务，从 2024-01-22 11:00 持续至 18:48，最长事务执行时长达 7 小时 48 分钟；
2. 数据量与涉及表：每个大事务涉及 400 万行数据操作，且均针对 100 个分表（db.t_sharding_00至db.t_sharding_99）执行插入；
3. 事务特征：事务内包含大量 “单表小批量插入”（每表插入 4 万行左右），但未拆分提交，最终形成 “多条小 SQL 累积的长事务”。

三、解决方案：从 “根源” 破解延迟

结合排查结果，延迟的核心是 “分表操作生成的长事务”—— 主库可并行处理多表插入，但从库 SQL 线程默认单线程执行，需逐表、逐行复现事务，耗时远超主库。解决方案需围绕 “拆分事务、优化分表逻辑、加强监控” 三个维度展开。

1. 拆分大事务：将 “长事务” 拆为 “小事务”

大事务的本质问题是 “单次执行时间过长”，通过分批提交将其拆分为多个独立小事务，可大幅减少从库单次执行的压力。具体操作：

• 按 “分表 + 数据量” 拆分：原事务一次性写入 100 个分表，改为每 10 个分表为一批，每批插入 40 万行后立即提交；
• 控制单事务数据量：设定单事务最大操作行数（如 50 万行），达到阈值后强制提交，避免事务累积过大；
• 示例优化前 vs 优化后：

  优化前	优化后
  1 个事务：100 个分表，400 万行，执行 7 小时	10 个事务：每事务 10 个分表，40 万行，每事务执行 40 分钟

拆分后，从库可逐批处理小事务，避免单事务阻塞导致的延迟堆积。

2. 优化分表逻辑：减少 “不必要的多表依赖”

原分表操作从 “1 张大表读数据→计算→写 100 个分表”，整个过程在一个事务内完成，存在 “读表与写表强耦合” 的问题。优化方向：

• 数据预处理离线化：将 “从大表读数据并计算” 的步骤迁移至离线任务（如 Spark、Flink），生成结构化数据文件（如 CSV），避免在线事务中包含读大表操作；
• 分表写入异步化：通过消息队列（如 Kafka）将分表插入任务拆分为 100 个独立任务，主库按 “单表单任务” 写入，减少事务内的表依赖；
• 避免跨表锁：分表写入时，确保每个小事务仅操作 1-2 个分表，避免事务锁定大量表导致的并发等待。

3. 建立监控与报警：提前发现异常

延迟的危害往往源于 “发现不及时”，需建立针对 “大事务 + 主从延迟” 的全链路监控，具体监控指标与阈值：

监控指标	监控工具	报警阈值	说明
Seconds_Behind_Master	Prometheus+Grafana	>300 秒（5 分钟）	从库延迟超过 5 分钟立即告警
大事务数量	my2sql + 自定义脚本	单事务 > 10 万行或执行 > 10 分钟	实时统计大事务，避免异常事务生成
Relay Log 大小	Linux 定时脚本	单文件 > 2GB 或总大小 > 10GB	及时发现从库 SQL 线程执行滞后
事务锁定表数量	INFORMATION_SCHEMA.INNODB_TRX	锁定表数 > 20 张	避免事务锁定过多表导致并发问题

四、日常预防：避免延迟 “卷土重来”

主从延迟的治理需 “治标 + 治本”，除了解决当前问题，还需通过开发规范 + 运维流程预防类似问题：

1. 开发规范：禁止在线业务中编写 “多表批量操作 + 不拆分提交” 的事务，代码评审时需重点检查事务大小；
2. 运维流程：新功能上线前（尤其是分表、批量导入等操作），需在测试环境模拟主从同步，评估延迟风险；
3. 参数优化：根据业务场景调整从库参数，如开启slave_parallel_workers（从库并行复制线程数），让多表操作可并行执行（需注意：并行复制仅对 “不同库 / 不同表” 的事务生效，需结合业务调整）。

五、总结

本次案例证明：MySQL 主从延迟的排查需 “从现象到根源，层层递进”—— 先通过show slave status定位异常指标，再通过事务表、日志、binlog 解析锁定大事务，最后针对性拆分事务与优化逻辑。核心启示是：大事务是主从延迟的 “隐形杀手”，尤其是多表操作场景下，需通过 “拆分 + 优化 + 监控” 三重手段，平衡业务需求与数据库同步效率。