MySQL 主从复制延迟排查与优化

适用版本：MySQL 5.7 / 8.0
工作模式：异步复制（GTID 模式）
核心场景：MySQL 主从复制 Seconds_Behind_Master 持续增大，SQL 线程回放滞后导致延迟

一、环境信息

二、问题现象

• Seconds_Behind_Master 持续增大，主从延迟不断拉大
• 业务读写分离场景下，从库读到过期数据，影响业务正确性

三、排查步骤

3.1 从 IO 线程开始排查

第一步：查看主库当前 binlog 文件及 Position

-- mysql-8.0 / mysql-5.7
-- 在主库执行，查看当前写入的 binlog 文件及 Position
SHOW MASTER STATUS\G

示例输出：

*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 149654676
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 56929ffe-5d09-11ea-bb4e-02000aba3da2:1-67963

第二步：查看从库 IO 线程状态

-- mysql-8.0 / mysql-5.7
-- 在从库执行，重点关注 IO 线程状态及 Retrieved_Gtid_Set
SHOW SLAVE STATUS\G
-- MySQL 8.0.22+ 可使用新语法：
-- SHOW REPLICA STATUS\G

关键字段说明：

字段	含义
Master_Log_File	IO 线程当前读取的主库 binlog 文件
Read_Master_Log_Pos	IO 线程已读取到的主库 binlog position
Retrieved_Gtid_Set	IO 线程已从主库接收的 GTID 集合
Executed_Gtid_Set	SQL 线程已回放的 GTID 集合
Seconds_Behind_Master	估算的主从延迟秒数

判断依据：

若主库的 Executed_Gtid_Set 与从库的 Retrieved_Gtid_Set 相同，如本例均为 1-67963，且 binlog 文件与 Position 一致（mysql-bin.000001:149654676），则可以判定：

✅ IO 线程无延迟，数据已全部传输到从库中继日志，问题在 SQL 线程。

3.2 切入 SQL 线程排查

第三步：查看 SQL 线程回放状态

-- mysql-8.0 / mysql-5.7
-- 在从库执行，查看 SQL 线程当前回放情况
SHOW SLAVE STATUS\G
-- MySQL 8.0.22+ 使用：SHOW REPLICA STATUS\G

重点关注如下字段：

由此可得出：已接收 67963 个事务，仅回放了 42311 个，当前正在回放第 42312 个事务，积压了 25652 个事务。

3.3 定位具体大事务

第四步：从主库 binlog 中解析第 42312 号事务内容

# mysql-8.0 / mysql-5.7
# 解析指定 GTID 事务，-vv 显示行事件详细内容
# 替换 UUID 为实际主库 server_uuid，替换 mysql-bin.000001 为实际文件名
mysqlbinlog -vv \
  --include-gtids='56929ffe-5d09-11ea-bb4e-02000aba3da2:42312' \
  /var/lib/mysql/mysql-bin.000001 \
  | grep -v "# at" \
  | less

小贴士： 若不清楚 binlog 文件路径，可通过 SHOW VARIABLES LIKE 'log_bin_basename'\G 查看。

版本差异说明：

• MySQL 5.7：mysqlbinlog 默认安装路径为 /usr/bin/mysqlbinlog
• MySQL 8.0：同上，但增加了 --require-row-format 参数以增强安全性（可选）

示例解析输出（ROW 格式 binlog）：

### UPDATE `业务库`.`订单明细表`
### WHERE
###   @1=12345678 /* INT meta=0 nullable=0 is_null=0 */
###   @2='2024-01-01' /* DATE meta=0 nullable=1 is_null=0 */
###   ...
### SET
###   @1=12345678
###   @2='2024-01-02'
###   ...
# 此事务包含数万行 UPDATE 操作，属于典型大事务

第五步：核查问题表结构

-- mysql-8.0 / mysql-5.7
-- 检查目标表是否有主键及索引，判断 SQL 线程回放时能否利用索引
SHOW CREATE TABLE 业务库.订单明细表\G

若表有主键与索引但单次操作行数过多（如一次 UPDATE 数万行），即为典型大事务问题。

根因总结：
频繁执行大批量 UPDATE 语句，单个事务涉及行数过多，导致从库 SQL 线程回放耗时远超主库写入速度，积压效应随时间不断放大。

四、解决方案

4.1 临时措施：降低从库刷盘频次，加速回放追赶

⚠️ 注意：以下参数调整会降低数据安全性，仅建议在延迟追赶期间临时使用，恢复后必须改回 1。

-- mysql-8.0 / mysql-5.7
-- 在从库执行（动态生效，无需重启）
-- 降低 redo log 刷盘频次：0 = 每秒刷一次，无需每次事务提交刷盘
SET GLOBAL innodb_flush_log_at_trx_commit = 0;

-- 关闭 binlog 同步刷盘：0 = 由 OS 决定，减少 fsync 调用
SET GLOBAL sync_binlog = 0;

追上延迟后立即恢复：

-- 恢复安全值，保证崩溃恢复能力
SET GLOBAL innodb_flush_log_at_trx_commit = 1;
SET GLOBAL sync_binlog = 1;

参数对比说明：

参数	值=1（安全模式）	值=0（高性能模式）
innodb_flush_log_at_trx_commit	每次事务提交均 fsync redo log	每秒异步 fsync，崩溃最多丢 1s 数据
sync_binlog	每次写 binlog 均 fsync	OS 缓存，崩溃可能丢若干事务

4.2 临时措施：开启多线程并行复制（MySQL 5.7+）

若 MySQL 版本为 5.7 或 8.0，且从库 CPU 有余量，可开启并行复制进一步加速回放：

-- mysql-5.7 / mysql-8.0
-- 查看当前并行复制配置
SHOW VARIABLES LIKE 'slave_parallel%';
-- MySQL 8.0.22+ 使用：SHOW VARIABLES LIKE 'replica_parallel%';

-- 开启 LOGICAL_CLOCK 模式并行复制（推荐）
-- 在从库执行（需停止 SQL 线程后设置，或写入 my.cnf 后重启）
STOP SLAVE SQL_THREAD;
-- MySQL 8.0.22+ 使用：STOP REPLICA SQL_THREAD;

SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 8;  -- 建议设为 CPU 核心数的 50%~75%

START SLAVE SQL_THREAD;
-- MySQL 8.0.22+ 使用：START REPLICA SQL_THREAD;

版本差异说明：

• MySQL 5.6：支持 DATABASE 级别并行，不同库可并行，同一库内串行
• MySQL 5.7+：支持 LOGICAL_CLOCK 基于逻辑时钟的组提交并行，推荐使用
• MySQL 8.0：默认启用，slave_parallel_workers 默认 4，可按需增大

4.3 长期措施：将大事务拆分为小批量操作

问题根源： 业务侧执行了类似如下的全表/大范围 UPDATE：

-- ❌ 问题写法：一次性 UPDATE 数万行，产生超大事务
UPDATE 订单明细表
SET status = 2, updated_at = NOW()
WHERE order_date < '2024-01-01' AND status = 1;
-- 假设影响 50000 行，此事务在 binlog 中占用大量空间，从库回放极慢

推荐改造方案：分批循环处理

-- ✅ 推荐写法：每次只处理 1000 行，事务小、回放快
-- 可用存储过程或应用层循环实现

-- 方式一：直接在 MySQL 中用循环（适合运维脚本）
SET @batch_size = 1000;
SET @total_affected = 1;

WHILE @total_affected > 0 DO
    UPDATE 订单明细表
    SET status = 2, updated_at = NOW()
    WHERE order_date < '2024-01-01'
      AND status = 1
    LIMIT 1000;                          -- 每批最多 1000 行

    SET @total_affected = ROW_COUNT();   -- 获取本批影响行数
    SELECT SLEEP(0.1);                   -- 小间隔，避免从库持续高压
END WHILE;

# 方式二：Python 应用层分批（推荐，可控性强）
import pymysql
import time

conn = pymysql.connect(host='[主库IP]', user='app_user', password='[密码]',
                       database='业务库', charset='utf8mb4')
cursor = conn.cursor()

batch_size = 1000
total_updated = 0

while True:
    sql = """
        UPDATE 订单明细表
        SET status = 2, updated_at = NOW()
        WHERE order_date < '2024-01-01'
          AND status = 1
        LIMIT %s
    """
    affected = cursor.execute(sql, (batch_size,))
    conn.commit()
    total_updated += affected
    print(f"本批更新 {affected} 行，累计 {total_updated} 行")

    if affected < batch_size:
        break  # 最后一批不足 batch_size，说明已处理完毕

    time.sleep(0.1)  # 适当间隔，降低主库压力

cursor.close()
conn.close()
print(f"全部完成，共更新 {total_updated} 行")

小贴士（进阶）：
若原大 UPDATE 已在主库执行完毕，binlog 中已有大事务，无法撤回。此时只能通过上述临时措施（4.1 / 4.2）加速从库追赶，并在追赶完成后恢复参数。
后续可在代码规范、DBA 审核流程中加入大事务拦截机制，拦截超过阈值行数的 DML 操作。

4.4 补充：监控从库延迟

建议配合以下 SQL 实时监控延迟，便于确认追赶进度：

-- mysql-8.0 / mysql-5.7
-- 每隔一段时间执行一次，观察 Seconds_Behind_Master 是否在持续下降
SELECT
    CHANNEL_NAME,
    SERVICE_STATE AS io_thread,
    LAST_ERROR_MESSAGE AS io_error
FROM performance_schema.replication_connection_status;

SELECT
    CHANNEL_NAME,
    SERVICE_STATE AS sql_thread,
    LAST_ERROR_MESSAGE AS sql_error,
    LAST_APPLIED_TRANSACTION AS last_gtid
FROM performance_schema.replication_applier_status_by_worker;

-- 或直接查看延迟秒数（兼容 5.7 和 8.0）
SHOW SLAVE STATUS\G
-- 重点关注：Seconds_Behind_Master

五、总结 & 注意事项

核心避坑点：

1. IO 线程与 SQL 线程要分开排查，切勿直接跳到调参，先定位瓶颈在哪一层
2. Seconds_Behind_Master 并非绝对精确（大事务场景会跳变），建议结合 GTID 差值综合判断
3. 临时调参降低刷盘频次后，若从库发生宕机，可能造成 relay log 损坏，需做好应急预案
4. 并行复制 LOGICAL_CLOCK 模式在低并发主库场景下效果有限，需配合主库 binlog_group_commit 参数才能充分发挥效果
5. 大事务拆分是从根本上解决问题的方案，配合 DBA 代码审核流程可有效预防复发