MySQL 主从延迟分析与解决

核心概念

主从延迟是指从库回放数据落后于主库的时间差，是 MySQL 复制中最常见的问题。

延迟产生原因

主库执行 → 写 binlog → 网络传输 → 从库接收 → 写入 relay log → SQL 线程回放

延迟可能产生在任一环节：
1. 网络延迟
2. 从库性能不足
3. 大事务
4. 单线程回放

延迟影响

延迟监控方法

方法 1：Seconds_Behind_Master

SHOW SLAVE STATUS\G

-- 关键字段
Seconds_Behind_Master: 10  -- 延迟 10 秒

原理：

Seconds_Behind_Master = 
  当前时间 - SQL 线程正在执行的事件的时间戳

局限性：

• 网络中断时显示 NULL
• 主库负载高时不准确
• 并行复制时不准确

方法 2：时间戳对比

-- 主库
SELECT NOW() AS master_time;

-- 从库
SELECT NOW() AS slave_time;
SELECT TIMESTAMPDIFF(SECOND, NOW(), @master_time) AS delay;

脚本实现：

#!/bin/bash
# 主库时间
master_time=$(mysql -h master -e "SELECT UNIX_TIMESTAMP()" -N)

# 从库时间
slave_time=$(mysql -h slave -e "SELECT UNIX_TIMESTAMP(MAX(executed_time))" -N)

# 计算延迟
delay=$((master_time - slave_time))
echo "Replication delay: ${delay}s"

方法 3：pt-heartbeat（推荐）

# 安装 pt-heartbeat
# Percona Toolkit 自带

# 主库：持续写入心跳时间
pt-heartbeat --create-slave-heartbeat \
  --database=percona \
  --table=heartbeat \
  --update \
  --host=master \
  --user=monitor \
  --password=password \
  --daemonize

# 从库：检查延迟
pt-heartbeat --check \
  --database=percona \
  --table=heartbeat \
  --host=slave \
  --user=monitor \
  --password=password

优点：

• 精确到毫秒
• 持续监控
• 支持告警

方法 4：Prometheus 监控

# mysqld_exporter 配置
- job_name: 'mysql'
  static_configs:
    - targets: ['localhost:9104']

# Prometheus 查询
# 复制延迟
mysql_slave_status_seconds_behind_master

# 告警规则
groups:
  - name: mysql_replication
    rules:
      - alert: ReplicationLagWarning
        expr: mysql_slave_status_seconds_behind_master > 60
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "MySQL 复制延迟超过 60 秒"
          
      - alert: ReplicationLagCritical
        expr: mysql_slave_status_seconds_behind_master > 300
        for: 5m
        labels:
          severity: critical
        annotations:
          summary: "MySQL 复制延迟超过 5 分钟"

延迟原因分析

原因 1：大事务

-- 主库执行大事务
DELETE FROM logs WHERE create_time < '2024-01-01';  -- 100 万行

-- 从库串行回放，耗时长

影响：

• 阻塞后续事务
• 延迟累积

解决方案：

-- 分批执行
DELETE FROM logs WHERE create_time < '2024-01-01' LIMIT 10000;
-- 循环执行直到影响行数为 0

原因 2：单线程回放

MySQL 5.6 及之前：SQL 线程单线程回放
MySQL 5.7+：支持并行复制

解决方案：

# 开启并行复制
[mysqld]
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8

原因 3：从库性能不足

从库配置低于主库：
- CPU 核心数少
- 内存小
- 磁盘 IO 慢

解决方案：

• 升级从库硬件
• 使用 SSD 磁盘
• 优化从库配置

原因 4：网络延迟

异地复制场景：
主库（北京）→ 从库（广州）
网络延迟：30ms

解决方案：

• 同城部署
• 专线连接
• 降低同步频率

原因 5：锁等待

-- 从库执行查询，持有锁
SELECT * FROM large_table WHERE ... FOR UPDATE;

-- SQL 线程等待锁，延迟增加

解决方案：

• 从库只读
• 避免在从库执行写操作
• 使用单独的查询库

延迟优化方案

方案 1：并行复制（推荐）

[mysqld]
# MySQL 5.7+
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8
slave_preserve_commit_order = ON

性能提升：

• 4 worker：延迟降低 60%
• 8 worker：延迟降低 80%

方案 2：优化主库配置

[mysqld]
# 减少 binlog 刷盘次数
sync_binlog = 100  # 每 100 次提交刷盘一次

# 使用 ROW 格式（5.7+ 优化过）
binlog_format = ROW

# 限制 binlog 大小
max_binlog_size = 1G

方案 3：优化从库配置

[mysqld]
# 增大缓冲池
innodb_buffer_pool_size = 4G

# 禁用从库日志
skip_log_bin

# 降低刷盘频率
innodb_flush_log_at_trx_commit = 2
sync_binlog = 0

# 临时表优化
tmp_table_size = 256M
max_heap_table_size = 256M

方案 4：避免大事务

-- 错误：大事务
DELETE FROM orders WHERE create_time < '2020-01-01';  -- 500 万行

-- 正确：分批执行
DELETE FROM orders WHERE create_time < '2020-01-01' LIMIT 10000;
-- 循环执行

应用层优化：

// 分批处理
public void deleteOldOrders() {
    int batchSize = 10000;
    int affected;
    do {
        affected = orderMapper.deleteOld(batchSize);
        // 每批休眠 100ms，减少主从压力
        Thread.sleep(100);
    } while (affected > 0);
}

方案 5：延迟复制（特定场景）

-- 故意延迟从库复制（用于误操作恢复）
CHANGE MASTER TO MASTER_DELAY = 3600;  -- 延迟 1 小时

-- 场景：主库误删数据，从库 1 小时后执行
-- 有时间窗口恢复

方案 6：复制过滤

-- 只复制需要的数据库
CHANGE MASTER TO REPLICATE_DO_DB = ('db1', 'db2');

-- 忽略某些表
CHANGE MASTER TO REPLICATE_IGNORE_TABLE = 'db1.logs';

作用：

• 减少从库负载
• 降低延迟

生产环境案例

案例 1：电商大促延迟

问题：

• 大促期间主从延迟达 30 分钟
• 从库读数据严重滞后

原因分析：

1. 主库并发高，binlog 生成快
2. 从库单线程回放
3. 大事务批量更新

解决方案：

# 1. 开启并行复制
slave_parallel_workers = 16

# 2. 优化从库配置
innodb_buffer_pool_size = 8G
innodb_io_capacity = 2000

# 3. 拆分大事务
UPDATE orders SET status = 1 WHERE id BETWEEN 1 AND 100000;
-- 拆分为 10 批

效果：

• 延迟从 30 分钟降至 10 秒

案例 2：日志表删除延迟

问题：

• 每天删除旧日志，延迟 1 小时
• 影响正常业务

解决方案：

-- 方案 1：使用 pt-archiver
pt-archiver \
  --source h=master,D=db,t=logs \
  --purge \
  --where "create_time < DATE_SUB(NOW(), INTERVAL 30 DAY)" \
  --limit 1000 \
  --sleep 1

-- 方案 2：分区表
ALTER TABLE logs PARTITION BY RANGE (YEAR(create_time)) (
  PARTITION p2020 VALUES LESS THAN (2021),
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION p2022 VALUES LESS THAN (2023)
);

-- 删除分区（瞬间完成）
ALTER TABLE logs DROP PARTITION p2020;

案例 3：异地复制延迟

问题：

• 北京 → 广州，网络延迟 30ms
• 累积延迟 5 分钟

解决方案：

架构调整：
北京主库 → 北京从库（半同步）
北京从库 → 广州从库（异步，级联）

效果：

• 广州延迟降至 1 分钟内

最佳实践

监控告警

# 告警阈值
- 延迟 > 60s：警告
- 延迟 > 300s：严重
- 延迟 > 3600s：紧急

配置检查清单

[ ] 开启并行复制
[ ] 从库硬件不低于主库
[ ] 使用 SSD 磁盘
[ ] 避免大事务
[ ] 配置监控告警
[ ] 定期演练主从切换

参考资料

• MySQL 官方文档 - 复制延迟
• Percona Toolkit: pt-heartbeat
• 《高性能 MySQL》第 10 章：复制