MySQL 主从延迟 25 分钟事故复盘:并行复制 + ProxySQL 工程治理

2023 年我们一个交易系统 MySQL 主从延迟突然飙到 30 分钟,大促前夕,从库读到的订单数据全是半小时前的,客服系统全乱套。复盘后做了系统性的主从延迟治理:并行复制、半同步、ProxySQL 读写分离、延迟感知路由、binlog 优化。改完后延迟稳定 < 100ms,从库可以放心承载 80% 的读流量。本文复盘 MySQL 主从复制的工程治理,覆盖原理、调优、监控、容灾。

事故现场

架构:MySQL 8.0 + 1 主 3 从(异步复制)
日常:主库 8000 QPS 写、从库 30000 QPS 读
事故:大促前一天

时间线:
20:00 大促预热,商品上架(批量更新)
20:30 监控告警:从库 1 延迟 5min
20:40 从库 2、3 也开始延迟
21:00 主从延迟稳定在 25min,持续上涨
21:15 业务:客服看订单是半小时前的,投诉激增
21:20 紧急扩容从库,切读流量(治标)
22:00 主库写流量降下来,延迟开始下降
00:00 延迟回到 < 1s

复盘原因:
1. binlog 写量太大(批量 UPDATE 全表扫)
2. 单线程 SQL_Thread 串行重放,跟不上主库
3. 从库 InnoDB 配置不优,IO 瓶颈
4. 没有延迟感知,业务读到旧数据

原理:为什么会延迟

主库              从库
======            =======
SQL 执行          IO_Thread 拉 binlog
↓                ↓
写 binlog        写 relay log
↓                ↓
返回客户端       SQL_Thread 重放(传统:单线程!)
                  ↓
                  应用到从库 InnoDB

延迟来源:
1. 网络延迟:主到从 binlog 传输(通常很小,1-10ms)
2. SQL_Thread 重放慢:
   - 单线程 vs 主库多线程并发(根本原因)
   - 大事务(20w 行 UPDATE 重放要几分钟)
   - 慢查询(从库索引缺失)
3. IO 瓶颈:从库磁盘 IOPS 跟不上
4. 锁等待:从库自己有查询持锁,SQL_Thread 等

# 查看延迟
mysql> SHOW SLAVE STATUS\G
Seconds_Behind_Master: 1234
Slave_SQL_Running_State: Reading event from the relay log

修复 1:并行复制(MTS)

-- 老:LOGICAL_CLOCK(MySQL 5.7+)
-- 新:WRITESET(MySQL 8.0+)更细粒度并行

-- 主库
SET GLOBAL binlog_transaction_dependency_tracking = WRITESET;
SET GLOBAL transaction_write_set_extraction = XXHASH64;
SET GLOBAL binlog_transaction_dependency_history_size = 25000;

-- 从库
STOP SLAVE;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 16;       -- 16 个并行 worker
SET GLOBAL slave_preserve_commit_order = ON;  -- 保证提交顺序
SET GLOBAL slave_pending_jobs_size_max = 1073741824;  -- 1GB
START SLAVE;

-- 持久化(my.cnf)
[mysqld]
binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction = XXHASH64
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 16
slave_preserve_commit_order = ON

-- 监控并行度
mysql> SELECT WORKER_ID, COUNT_TRANSACTIONS_RETRIES, LAST_APPLIED_TRANSACTION
       FROM performance_schema.replication_applier_status_by_worker;
+-----------+----------------------------+-------------------------+
| WORKER_ID | COUNT_TRANSACTIONS_RETRIES | LAST_APPLIED_TRANSACTION |
| 1         | 0                          | uuid:1-12345            |
| 2         | 0                          | uuid:1-12346            |
| ...                                                              |
| 16        | 0                          | uuid:1-12360            |
+-----------+----------------------------+-------------------------+

-- 效果:延迟峰值从 25min 降到 30s

修复 2:半同步复制

-- 默认异步:主库提交立即返回,不管从库收没收
-- 半同步:至少 1 个从库收到 binlog 才返回

-- 主库装插件
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 = ON;
SET GLOBAL rpl_semi_sync_master_timeout = 1000;  -- 1s 等不到从库就降级异步

-- 从库配置(从库重启 IO 线程)
SET GLOBAL rpl_semi_sync_slave_enabled = ON;
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

-- 监控
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
Rpl_semi_sync_master_status: ON               -- 半同步生效
Rpl_semi_sync_master_yes_tx: 12345            -- 半同步成功事务数
Rpl_semi_sync_master_no_tx: 0                 -- 降级异步事务数
Rpl_semi_sync_master_avg_net_wait_time: 543   -- 平均等待 0.5ms

-- 注意:半同步只保证 binlog 到从库,不保证从库回放完
-- 真正零延迟读还是要走主库

修复 3:大事务拆分

-- 问题:UPDATE products SET status=1 WHERE category='3C';  -- 20w 行
-- 主库 5 秒搞定(并发线程),从库 SQL_Thread 串行重放 5min

-- 修复:分批(每次 1000 行)
DELIMITER //
CREATE PROCEDURE batch_update_products(IN target_cat VARCHAR(50))
BEGIN
    DECLARE batch_size INT DEFAULT 1000;
    DECLARE affected INT DEFAULT 1;

    WHILE affected > 0 DO
        UPDATE products SET status = 1
        WHERE category = target_cat AND status = 0
        LIMIT batch_size;
        SET affected = ROW_COUNT();

        SELECT SLEEP(0.1);                    -- 给从库喘息
    END WHILE;
END //
DELIMITER ;

-- 业务代码也要遵守
-- 每个事务尽量 < 100 行 / < 5 秒
-- INSERT INTO table SELECT FROM huge_table 这种禁止

修复 4:从库 InnoDB 优化

# my.cnf(从库专属)

# 1. 关 sync_binlog / innodb_flush_log_at_trx_commit
#    从库挂了大不了重新同步,不需要每事务 fsync
sync_binlog = 1000
innodb_flush_log_at_trx_commit = 2

# 2. 大 redo log
innodb_log_file_size = 4G
innodb_log_files_in_group = 2

# 3. 大 buffer pool(80% 内存)
innodb_buffer_pool_size = 100G
innodb_buffer_pool_instances = 16

# 4. IO 优化
innodb_io_capacity = 10000          # NVMe 给高点
innodb_io_capacity_max = 20000
innodb_flush_neighbors = 0          # SSD 关
innodb_read_io_threads = 16
innodb_write_io_threads = 16

# 5. 并行复制相关
slave_parallel_workers = 16
slave_preserve_commit_order = ON
binlog_group_commit_sync_delay = 100      # 主库:攒一会再 fsync
binlog_group_commit_sync_no_delay_count = 50

# 6. 关闭 query cache(MySQL 8.0 已移除)
# 关闭 perf 收集(从库主要承载读)
performance_schema = OFF       # 节省 200MB 内存

修复 5:延迟感知读写分离

# ProxySQL 配置
# 1. 监控所有从库延迟
mysql_replication_hostgroups (
  writer_hostgroup=10,
  reader_hostgroup=20,
  check_type='read_only',
  max_writers=1
)

# 2. 自动剔除超过 5s 延迟的从库
mysql_servers (
  hostgroup_id=20,
  hostname='slave1',
  max_replication_lag=5,
  weight=100
)

mysql_servers (
  hostgroup_id=20,
  hostname='slave2',
  max_replication_lag=5,
  weight=100
)

# 3. 路由规则
mysql_query_rules (
  rule_id=1,
  match_pattern='^SELECT.*FOR UPDATE',
  destination_hostgroup=10        # 走主库
)

mysql_query_rules (
  rule_id=2,
  match_pattern='^SELECT',
  destination_hostgroup=20        # 走从库
)

mysql_query_rules (
  rule_id=3,
  digest='0x...',                  # 特定 SQL 强制走主库
  destination_hostgroup=10
)

修复 6:业务侧延迟感知

// 写完立即读,走主库(避免读到旧数据)
@Component
public class TransactionContext {
    private static final ThreadLocal<Boolean> FORCE_MASTER = new ThreadLocal<>();

    public static void useMaster() {
        FORCE_MASTER.set(true);
    }

    public static boolean isMaster() {
        return Boolean.TRUE.equals(FORCE_MASTER.get());
    }

    public static void clear() {
        FORCE_MASTER.remove();
    }
}

// AOP 切面:写后 N 秒内的读走主库
@Aspect
@Component
public class WriteAfterReadAspect {
    @AfterReturning("@annotation(org.springframework.transaction.annotation.Transactional)")
    public void afterWrite(JoinPoint pjp) {
        // 标记 5 秒内本用户的读走主库
        String userId = SecurityContext.getUserId();
        redis.setex("force_master:" + userId, 5, "1");
    }
}

// 路由
@Component
public class DataSourceRouter extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        if (TransactionContext.isMaster()) {
            return "master";
        }
        String userId = SecurityContext.getUserId();
        if (userId != null && redis.exists("force_master:" + userId)) {
            return "master";
        }
        return "slave";
    }
}

监控告警

# Prometheus + mysqld_exporter
- alert: ReplicationLagHigh
  expr: mysql_slave_status_seconds_behind_master > 5
  for: 2m
  annotations:
    summary: "从库 {{ $labels.instance }} 延迟 > 5s"

- alert: ReplicationStopped
  expr: mysql_slave_status_slave_sql_running == 0
        or mysql_slave_status_slave_io_running == 0
  for: 1m
  annotations:
    summary: "从库复制中断,IO 或 SQL 线程停了"
    runbook: "https://wiki/runbook/mysql-replication-broken"

- alert: SemiSyncFallback
  expr: increase(mysql_global_status_rpl_semi_sync_master_no_tx[5m]) > 100
  annotations:
    summary: "半同步降级异步频繁"

- alert: ParallelReplicationLow
  expr: rate(mysql_slave_parallel_workers_busy[5m]) < 0.5
  annotations:
    summary: "并行复制利用率低,可能 WRITESET 颗粒度太粗"

# 自动化操作:延迟 > 30s 自动剔除
- alert: AutoEjectSlave
  expr: mysql_slave_status_seconds_behind_master > 30
  for: 1m
  annotations:
    action: "proxysql_eject_slave"

优化效果

指标                  优化前           优化后
=====================================================
主从延迟(P50)        500ms            5ms
主从延迟(P99)        25min(事故)    100ms
主从延迟(峰值)      30min             2s
从库 SQL Thread CPU  100%(单核)      40%(16 核)
binlog 大小(天)    80GB              60GB(优化批量更新后)
主库写 QPS           8k                12k
从库读 QPS           30k(单机)       80k(三机分担)
半同步成功率         --                99.99%

业务影响:
- 客服系统看订单实时
- 风控读取交易数据延迟 < 100ms,有效拦截欺诈
- 大促可以放心走读写分离,从库分担 80% 读流量
- 节约主库扩容成本

避坑清单

1. MySQL 8.0 必开 WRITESET 并行复制(LOGICAL_CLOCK 太粗)
2. slave_parallel_workers = CPU 核数 / 2 起步
3. slave_preserve_commit_order = ON 保证提交顺序
4. 大事务拆分(> 1000 行强制 LIMIT 批处理)
5. 半同步 timeout = 1s,网络抖动自动降级
6. 从库 sync_binlog = 1000 减 fsync 开销
7. ProxySQL 配 max_replication_lag,自动剔除延迟从库
8. 写后立即读必须走主库(标记 5-30s)
9. 监控 Seconds_Behind_Master 不够,要看实际 GTID gap
10. 从库不要混跑 OLAP 大查询(独立 ANALYTICS 从库)

总结

MySQL 主从复制是高可用基础,但默认配置只够小流量用。这次系统性治理覆盖了原理、参数、业务感知、监控全链路,主从延迟从 25min 峰值降到 100ms 稳态。最大的认知改变:MySQL 5.6 那个"单线程 SQL_Thread 是单点瓶颈"的常识在 8.0 已经过时了,WRITESET 并行复制能让从库吃满主库写入。最被低估的是 ProxySQL 的延迟感知能力 — 写完业务代码不用关心读哪个库,Proxy 自动判断,大幅减轻应用层复杂度。最容易踩的坑是大事务:主库并发线程 5 秒,从库串行重放可能 5 分钟,大促前批量更新一定要拆。最后,写后立即读走主库这个细节非常关键,业务方往往不知道异步复制延迟,默默读到半小时前的数据,引发各种诡异 bug。

—— 别看了 · 2026