MySQL 8.0 主从复制延迟排查 SOP + 3 种治理方案取舍对比

一、排查 SOP（6 步链路）

Step 1：确认延迟范围与趋势

-- 主库
SHOW MASTER STATUS;
-- 从库（每个从节点都查）
SHOW REPLICA STATUS\G
-- 关注字段：
--   Seconds_Behind_Source（实时延迟秒数）
--   Replica_IO_Running / Replica_SQL_Running（是否 Running）
--   Last_IO_Err / Last_SQL_Err（最近一次错误）
--   Seconds_Behind_Master（v8.0.26+ 推荐用 Replica_Delay）
-- 连续 3 次采样（间隔 5s）判断是瞬时抖动还是持续延迟

判定指标：Seconds_Behind_Source > 2 且 3 次采样持续上升 → 进入 Step 2；若仅单次尖峰 → 可能是大事务回放，跳到 Step 4。

Step 2：定位延迟根因——从库回放端

-- 2a. 查看当前正在回放的 SQL 线程
SELECT * FROM performance_schema.replication_applier_status_by_worker\G
-- 关注：THREAD_ID, SERVICE_STATE, LAST_ERROR_NUMBER, LAST_ERROR_MESSAGE
--      LAST_APPLIED_TRANSACTION（最近完成的 GTID）

-- 2b. 查看回放线程的等待事件
SELECT EVENT_NAME, WAIT_COUNT, AVG_TIMER_WAIT
FROM performance_schema.events_waits_summary_by_thread
WHERE THREAD_ID = (SELECT PROCESSLIST_ID FROM information_schema.PROCESSLIST WHERE COMMAND = 'Slave SQL')
ORDER BY AVG_TIMER_WAIT DESC LIMIT 10;

-- 2c. 检查是否有 DDL 在串行回放
SHOW PROCESSLIST;  -- 找 State = 'copy to tmp table' 或 'altering table'
SELECT * FROM performance_schema.data_lock_waits WHERE OBJECT_SCHEMA = 'your_db';

判定指标：Last_SQL_Err 非空 → 复制中断（需 START REPLICA）；events_waits_summary 中 wait/io/table/sql/handl... 占比 > 60% → IO 瓶颈；data_lock_waits 有等待 → 大事务阻塞。

Step 3：定位延迟根因——主库发送端

-- 3a. 检查主库 binlog 写入压力
SHOW BINARY LOG STATUS;
-- 关注：Binlog_Bytes_Received（8.0.30+），Binlog_Bytes_Written

-- 3b. 检查主库是否有大事务堆积
SELECT * FROM information_schema.INNODB_TRX
WHERE TRX_STATE = 'RUNNING' AND TRX_ROWS_MODIFIED > 10000;

-- 3c. 检查 binlog 组提交（group commit）延迟
SHOW GLOBAL STATUS LIKE 'Com_commit';
SHOW GLOBAL STATUS LIKE 'Innodb_commit_concurrency';

判定指标：INNODB_TRX 有 TRX_ROWS_MODIFIED > 100000 的长事务 → 大事务阻塞复制；Com_commit 增长速率 > 从库回放速率 → 主库写入过快。

Step 4：定位延迟根因——网络与 IO

# 4a. 主从网络延迟（在从节点执行）
ping -c 10 <master_ip>
# 预期 RTT < 1ms（同机房），若 > 5ms 则网络是瓶颈

# 4b. 从节点磁盘 IO
iostat -x 1 5
# 关注：%util > 90% 或 await > 20ms → 从库 IO 瓶颈

# 4c. 从节点 CPU 与内存
top -bn1 | head -20
free -m
# 关注：CPU > 90% 或 available memory < 1G

判定指标：网络 RTT > 5ms → 网络抖动；iostat %util > 90% → 从库磁盘瓶颈；CPU 饱和 → 回放端计算瓶颈。

Step 5：检查 GTID 一致性

-- 主库
SELECT GTID_SUBSET(@@global.gtid_executed, @@global.gtid_executed) AS all_ok;
SELECT @@global.gtid_executed;

-- 从库
SELECT GTID_SUBSET(@@global.gtid_executed, @@global.gtid_executed) AS all_ok;
SELECT @@global.gtid_executed;

-- 对比：主库 gtid_executed 应包含从库 gtid_executed 的所有 GTID
-- 用 GTID_SUBSET 函数验证（返回 1 表示子集关系成立）

判定指标：从库 GTID 落后主库 > 100 个事务 → 回放严重滞后；GTID 不连续 → 可能有复制中断历史。

Step 6：检查参数配置

SHOW GLOBAL VARIABLES WHERE Variable_name IN (
  'rpl_semi_sync_master_enabled',
  'rpl_semi_sync_slave_enabled',
  'replica_parallel_workers',
  'replica_parallel_type',
  'replica_preserve_commit_order',
  'innodb_flush_log_at_trx_commit',
  'sync_binlog',
  'binlog_cache_size',
  'slave_net_timeout'
);

判定指标：replica_parallel_workers = 1 → 单线程回放（MTS 未开启）；rpl_semi_sync_master_enabled = OFF → 半同步未开启。

二、3 种延迟根因的定位与判定

根因 1：大事务阻塞（最常见）

现象：Seconds_Behind_Source 突然从 0 跳到 50+，SQL 线程 State = System lock 或长时间 Waiting for table metadata lock。 定位命令：

SELECT TRX_ID, TRX_STATE, TRX_ROWS_MODIFIED, TRX_QUERY
FROM information_schema.INNODB_TRX
WHERE TRX_ROWS_MODIFIED > 10000 ORDER BY TRX_ROWS_MODIFIED DESC;

判定指标：TRX_ROWS_MODIFIED > 100000 且 TRX_STATE = 'RUNNING' 持续 > 30s。 临时缓解：KILL <主库对应线程ID>（需确认业务影响）；长期方案：拆分大事务（见方案 2）。

根因 2：从端 IO 瓶颈

现象：iostat %util > 90%，SQL 线程 events_waits_summary 中 IO 等待占比高。 定位命令：

iostat -x 1 10 | grep -E 'Device|sd[a-z]|nvme'
# 关注：await > 20ms, %util > 90%

判定指标：从库 innodb_buffer_pool_size 不足（< 物理内存 50%）+ 磁盘 await > 20ms。 修复：SET GLOBAL innodb_buffer_pool_size = 16106127360;（15G，需预留 2G 给 OS）——注意：此参数需 RESTART，SET GLOBAL 在 8.0 中不支持热改 buffer pool。

根因 3：DDL 串行回放

现象：SQL 线程 State = altering table，SHOW PROCESSLIST 看到 copy to tmp table。 定位命令：

SELECT * FROM performance_schema.data_lock_waits
WHERE OBJECT_SCHEMA = 'your_db' AND OBJECT_NAME = 'your_table';

判定指标：data_lock_waits 有等待链且 OBJECT_TYPE = 'TABLE'。 修复：使用 gh-ost 或 pt-online-schema-change 在线 DDL，避免锁表。

根因 4：网络抖动

现象：ping RTT 抖动大，IO 线程频繁重连（Last_IO_Err 出现 errno 110 timeout）。 定位命令：

ping -c 20 -i 0.5 <master_ip> | tail -5
mtr --report <master_ip> -c 20

判定指标：RTT 平均 > 5ms 或丢包率 > 0.1%。 修复：检查交换机/网卡；SET GLOBAL slave_net_timeout = 60;（热生效，增大超时容忍度）。

三、3 种治理方案对比

方案 A：半同步复制（rpl_semi_sync）

适用场景：

1. 主库故障切换时要求从库数据不丢失（金融/支付类 OLTP）
2. 主从 RTT < 1ms 的同机房部署（半同步的 RTT 放大代价可接受）
3. 写 QPS < 3000（半同步的 ACK 等待会限制写入吞吐）

配置示例（主库 my.cnf，需 RESTART）：

[mysqld]
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 3000
rpl_semi_sync_master_wait_point = AFTER_SYNC
# 从库
rpl_semi_sync_slave_enabled = 1

-- 安装插件（8.0.26+ 用语义化名称）
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;  -- 热生效
SET GLOBAL rpl_semi_sync_slave_enabled = 1;   -- 热生效

性能收益：数据一致性从异步（可能丢最后 N 个事务）提升到半同步（至少一个从库确认）。写延迟增加 ≈ RTT × 0.5（同机房 < 0.5ms）。

代价与风险：

• 写吞吐下降 5%-15%（每次提交需等待从库 ACK）
• 若从库不可用，半同步自动降级为异步（Rpl_semi_sync_master_status = OFF），需监控告警
• rpl_semi_sync_master_timeout 设置过短会导致频繁降级

回滚步骤：

SET GLOBAL rpl_semi_sync_master_enabled = 0;  -- 热生效，立即回退为异步
UNINSTALL PLUGIN rpl_semi_sync_master;
UNINSTALL PLUGIN rpl_semi_sync_slave;
-- 重启后生效（可选）

方案 B：MTS 并行复制（replica_parallel_workers）

适用场景：

1. 多业务库/多 schema 的 OLTP 集群（并行度收益最大）
2. 从库 CPU 多核（≥ 8 核）但回放单线程打不满
3. 主库写 QPS > 3000 且从库回放跟不上

配置示例（从库 my.cnf，需 RESTART）：

[mysqld]
replica_parallel_workers = 16
replica_parallel_type = LOGICAL_CLOCK
replica_preserve_commit_order = 1
# 注意：8.0.26+ 用 replica_ 前缀（旧版 slave_ 已废弃）

性能收益：

• 单线程回放：~2000 TPS（瓶颈在单核 CPU）
• 16 线程 MTS（LOGICAL_CLOCK）：~8000-12000 TPS（接近线性，受锁竞争影响）
• 大事务场景：MTS 无法并行（单事务内仍串行），收益有限

代价与风险：

• replica_preserve_commit_order = 1 会引入全局锁，降低并行度（但保证一致性）
• 若设为 0（不保序），可能出现从库上 SELECT ... FOR UPDATE 读到"未来"数据
• 内存开销：每个 worker 线程 ~2MB，16 线程 ≈ 32MB（可忽略）

回滚步骤：

# my.cnf 改回
replica_parallel_workers = 1
# RESTART 后生效

方案 C：Proxy 读路由分流

适用场景：

1. 读写比 > 5:1 的 OLTP 业务（你的场景 10:1 完美匹配）
2. 从库延迟对读请求的容忍度 > 2s（最终一致性可接受）
3. 需要按业务维度路由（如按租户/按 schema 分流到不同从库）

配置示例（ProxySQL，热生效）：

-- 主库（只写）
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections)
VALUES (10, 'master.example.com', 3306, 1, 5000);

-- 从库（只读）
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections)
VALUES (20, 'replica1.example.com', 3306, 100, 5000);
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections)
VALUES (20, 'replica2.example.com', 3306, 100, 5000);

-- 读路由：SELECT 走 hostgroup 20
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, '^SELECT', 20, 1);

-- 写路由：INSERT/UPDATE/DELETE 走 hostgroup 10
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (2, 1, '^(INSERT|UPDATE|DELETE)', 10, 1);

LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;

性能收益：

• 读请求 100% 分流到从库，主库只承担写（QPS 从 5000 降到 500）
• 从库压力均摊（2 台从库各承担 50% 读流量）
• 主从延迟对读的影响：读到的数据可能落后 0-2s（业务需容忍）

代价与风险：

• 引入 ProxySQL 单点（需 HA：主备 ProxySQL + keepalived/VIP）
• 读路由到从库可能读到旧数据（主从延迟窗口内）
• 配置复杂度高：需维护 query rules、hostgroup、monitor 规则
• 回滚成本高：需切换回直连主库

回滚步骤：

-- 关闭读路由，所有请求回主库
UPDATE mysql_query_rules SET active = 0 WHERE rule_id = 1;
LOAD MYSQL QUERY RULES TO RUNTIME;
-- 或直接在应用层改连接串回主库 IP

四、对比表格

| 维度 | 半同步复制 | MTS 并行复制 | Proxy 读路由 | |------|-----------|-------------|-------------| | 适用场景 | 金融/支付、数据零丢失、RTT<1ms | 多 schema OLTP、CPU≥8核、写QPS>3000 | 读写比>5:1、最终一致性可接受 | | 配置复杂度 | 低（2个参数+插件） | 中（3个参数，需选并行类型） | 高（ProxySQL+query rules+monitor） | | 性能收益 | 一致性↑，写吞吐↓5-15% | 回放TPS↑4-6x（多核） | 主库负载↓80%（读写分离） | | 故障半径 | 从库挂→降级异步（静默） | 单worker挂→自动跳过 | ProxySQL挂→全链路断 | | 回滚成本 | 低（SET GLOBAL 热回滚） | 中（需 RESTART） | 高（需切回直连） |

五、可复现延迟模拟脚本

脚本 1：大事务阻塞模拟

-- 前置条件：从库已建立复制，主库有 test_db 库
-- 预期输出：从库 Seconds_Behind_Source 在 5s 内从 0 跳到 30+
CREATE TABLE test_db.big_txn (
  id INT AUTO_INCREMENT PRIMARY KEY,
  val VARCHAR(255),
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 批量插入 20 万行（模拟大事务）
SET @batch = 0;
WHILE @batch < 200000 DO
  INSERT INTO test_db.big_txn (val) VALUES (CONCAT('row_', @batch + 1, '_', RAND()));
  SET @batch = @batch + 1;
END WHILE;
COMMIT;

-- 观察从库延迟
-- 从库执行：SHOW REPLICA STATUS\G → Seconds_Behind_Source
-- 预期：大事务回放期间延迟 30-120s（取决于从库 CPU/IO）

脚本 2：并发 DDL 串行回放模拟

#!/bin/bash
# 前置条件：主库有 test_db，从库已建立复制
# 预期输出：从库 SQL 线程 State = 'altering table'，延迟逐步累积

for i in $(seq 1 10); do
  mysql -h master -u root -p'${DB_PASS}' test_db -e "
    ALTER TABLE test_db.big_txn ADD COLUMN c_${i} INT DEFAULT 0;
  " &
done
wait

# 从库观察：
# SHOW PROCESSLIST → 看到 'altering table' 串行执行
# SHOW REPLICA STATUS\G → Seconds_Behind_Source 逐步上升
# 10 个 DDL 串行回放，每个 2-5s，总延迟 20-50s

六、快速决策树

告警触发：Seconds_Behind_Source > 2s
│
├─ [1] 从库 SQL 线程是否 Running？
│   ├─ NO → 检查 Last_SQL_Err → 修复后 START REPLICA → 结束
│   └─ YES → 继续
│
├─ [2] 是否有大事务（INNODB_TRX rows > 10000）？
│   ├─ YES → 大事务阻塞 → 等回放完成 / KILL 主库长事务 → 结束
│   └─ NO → 继续
│
├─ [3] 从库 iostat %util > 90% 或 await > 20ms？
│   ├─ YES → IO 瓶颈 → 升磁盘 / 加 buffer pool → 结束
│   └─ NO → 继续
│
├─ [4] 主库写 QPS > 3000 且 replica_parallel_workers < 2？
│   ├─ YES → 回放吞吐不足 → 开启 MTS（replica_parallel_workers=16）→ 结束
│   └─ NO → 继续
│
├─ [5] 网络 RTT > 5ms 或丢包 > 0.1%？
│   ├─ YES → 网络抖动 → 检查交换机/网卡 → 结束
│   └─ NO → 继续
│
├─ [6] 读写比 > 5:1 且业务容忍 2s 延迟？
│   ├─ YES → 读流量压主库 → 部署 ProxySQL 读路由 → 结束
│   └─ NO → 继续
│
└─ [7] 以上均不命中 → 检查半同步状态（Rpl_semi_sync_master_status）
    ├─ OFF（已降级）→ 从库异常 → 排查从库 → 重新开启半同步
    └─ ON → 参数调优（binlog_cache_size / slave_net_timeout）→ 观察 24h

七、关键参数热生效/重启标注

| 参数 | 生效方式 | 说明 | |------|---------|------| | rpl_semi_sync_master_enabled | 热生效（SET GLOBAL） | 半同步开关 | | rpl_semi_sync_slave_enabled | 热生效（SET GLOBAL） | 从库半同步开关 | | replica_parallel_workers | 需 RESTART | MTS 并行度 | | replica_parallel_type | 需 RESTART | MTS 并行类型 | | replica_preserve_commit_order | 需 RESTART | 是否保序 | | innodb_buffer_pool_size | 需 RESTART（8.0 不支持热改） | 缓冲池大小 | | slave_net_timeout | 热生效（SET GLOBAL） | 复制网络超时 | | binlog_cache_size | 热生效（SET GLOBAL） | binlog 缓存 | | sync_binlog | 热生效（SET GLOBAL） | binlog 同步策略 |