MySQL主从同步延迟排查:从30秒优化到毫秒级
一、概述
1.1 背景介绍
做过读写分离的都知道,主从延迟是个绕不开的坑。
我们线上有个订单系统,用户下单后立刻查询订单状态,结果经常查不到——因为写走主库,读走从库,从库还没同步过来。用户以为订单没下成功,又下了一单,结果后来发现有两笔一样的订单,退款的时候一堆扯皮。
更惨的是去年双11,从库延迟飙到30多秒。运营问我:"后台看到的数据怎么和用户看到的不一样?"我说延迟了,她问能修吗,我说正在修。结果那30秒我改了一晚上参数、调了一星期架构。
这篇文章就是把这些经验整理出来,从原理到排查到优化,让你少踩坑。
1.2 技术特点
先搞清楚MySQL复制是怎么工作的,不然排查的时候两眼一抹黑。
MySQL复制原理(简化版):
主库写入 → 生成binlog → IO线程拉取binlog → 写入relay log → SQL线程回放relay log → 从库数据更新
延迟可能发生在任何一个环节:
- • 从库IO线程写relay log慢(磁盘IO)
为什么SQL线程最容易慢?
因为主库可以并行写(多个连接同时写),但从库的SQL线程默认是单线程回放。想象一下,主库10个连接同时写,从库1个线程慢慢回放,能不延迟吗?
1.3 适用场景
1.4 环境要求
| | |
|---|
| | 5.7开始支持基于LOGICAL_CLOCK的并行复制 |
| | |
| CentOS 7+ / Ubuntu 18.04+ | |
二、详细步骤
2.1 延迟检测
◆ 2.1.1 查看从库状态
这是最基本的检查命令:
SHOW SLAVE STATUS\G
输出里重点关注这几个字段:
Slave_IO_Running: Yes -- IO线程是否在跑
Slave_SQL_Running: Yes -- SQL线程是否在跑
Seconds_Behind_Master: 30 -- 延迟秒数(这个数字不一定准)
Master_Log_File: mysql-bin.000123 -- 当前读取的主库binlog文件
Read_Master_Log_Pos: 12345678 -- 读取位置
Relay_Master_Log_File: mysql-bin.000120 -- 正在回放的binlog
Exec_Master_Log_Pos: 87654321 -- 回放位置
Seconds_Behind_Master的坑:
这个值的计算方式是:当前时间 - relay log中正在执行的事件的时间戳
问题是:
- 1. 如果IO线程卡住了,这个值会变成0(因为没有新事件),但实际上延迟在增加
所以不能完全相信这个数字。
◆ 2.1.2 使用pt-heartbeat精确测量
pt-heartbeat是Percona Toolkit里的工具,原理是:主库每秒往一个表里写入当前时间戳,从库读这个表计算差值。
安装:
# CentOS
yum install -y percona-toolkit
# Ubuntu
apt install -y percona-toolkit
# 或者下载安装
wget https://downloads.percona.com/downloads/percona-toolkit/3.5.5/binary/tarball/percona-toolkit-3.5.5_x86_64.tar.gz
tar xzf percona-toolkit-3.5.5_x86_64.tar.gz
配置使用:
# 1. 在主库创建心跳表
mysql -uroot -p -e "CREATE DATABASE IF NOT EXISTS percona"
pt-heartbeat --database=percona --create-table --update --daemonize \
-h 127.0.0.1 -u root -p'password'
# 2. 在从库监控延迟
pt-heartbeat --database=percona --monitor --master-server-id=1 \
-h 从库IP -u root -p'password'
# 输出示例:
# 0.00s [ 0.00s, 0.00s, 0.00s ]
# 0.01s [ 0.00s, 0.00s, 0.00s ]
# 格式:当前延迟 [ 1分钟平均, 5分钟平均, 15分钟平均 ]
◆ 2.1.3 对比主从位点差距
另一个判断延迟的方法是对比binlog位点:
-- 主库执行
SHOW MASTER STATUS;
-- 输出:mysql-bin.000123, Position: 98765432
-- 从库执行
SHOW SLAVE STATUS\G
-- 对比 Relay_Master_Log_File 和 Exec_Master_Log_Pos
如果binlog文件差了好几个,那延迟就大了。
2.2 延迟原因分析
◆ 2.2.1 主库写入量过大
这个很好理解,主库写得太猛,从库追不上。
检查方法:
-- 主库看binlog生成速度
SHOWBINARY LOGS;
-- 对比每个文件的大小和生成时间
-- 或者用这个命令看写入量
SHOWGLOBAL STATUS LIKE'Binlog_cache%';
SHOWGLOBAL STATUS LIKE'Com_insert%';
SHOWGLOBAL STATUS LIKE'Com_update%';
SHOWGLOBAL STATUS LIKE'Com_delete%';
◆ 2.2.2 从库单线程回放
这是最常见的原因。MySQL 5.6之前,从库SQL线程是单线程的。5.6支持基于库的并行复制(不同库可以并行),5.7支持基于LOGICAL_CLOCK的并行复制(同一库也能并行)。
检查当前并行复制配置:
SHOW VARIABLES LIKE'slave_parallel%';
-- slave_parallel_type: LOGICAL_CLOCK (5.7推荐)
-- slave_parallel_workers: 16 (并行线程数)
如果 slave_parallel_workers = 0,那就是单线程,必须改。
◆ 2.2.3 大事务阻塞
一个大事务(比如批量update几百万行)在从库回放时是原子的,必须全部执行完才算完成。这期间其他事务都得等着。
检查方法:
-- 看正在执行的大事务
SHOW PROCESSLIST;
-- 看binlog中的大事务
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000123 | grep -A 5 "UPDATE\|DELETE\|INSERT" | head -100
-- 看当前SQL线程在执行什么
SHOW SLAVE STATUS\G
-- 关注 Slave_SQL_Running_State 字段
◆ 2.2.4 网络延迟
如果主从是跨机房甚至跨地域部署,网络延迟会影响binlog传输。
检查方法:
# 从从库ping主库
ping -c 10 主库IP
# 测试网络带宽
iperf3 -c 主库IP
# 看IO线程状态
SHOW SLAVE STATUS\G
-- 如果 Seconds_Behind_Master 在增加,但 Master_Log_File 和 Read_Master_Log_Pos 变化很慢
-- 说明IO线程拉取binlog的速度跟不上
◆ 2.2.5 从库硬件瓶颈
从库磁盘IO、CPU可能是瓶颈。
检查方法:
# 看磁盘IO
iostat -x 1 5
# 重点看 %util,如果接近100%说明磁盘已经满载
# 看CPU
top
# 看mysqld进程的CPU使用率
# 看MySQL线程CPU使用
SELECT * FROM performance_schema.threads WHERE NAME LIKE '%slave%';
2.3 优化方案
◆ 2.3.1 开启并行复制(最有效)
这是解决从库单线程瓶颈最直接的办法。
MySQL 5.7+ 配置:
# /etc/my.cnf 从库配置
[mysqld]
# 基于逻辑时钟的并行复制
slave_parallel_type = LOGICAL_CLOCK
# 并行线程数,建议设置为CPU核数的1-2倍
slave_parallel_workers = 16
# 保证事务提交顺序(5.7.19+可以设成OFF提升性能,但要评估业务影响)
slave_preserve_commit_order = ON
# 从库的binlog也需要写(用于级联复制或GTID)
log_slave_updates = ON
MySQL 8.0 配置:
# MySQL 8.0.26+ 参数有变化
[mysqld]
# 新参数名
replica_parallel_type = LOGICAL_CLOCK
replica_parallel_workers = 16
replica_preserve_commit_order = ON
配置后重启:
# 先停止从库复制
STOP SLAVE;
# 重启MySQL
systemctl restart mysqld
# 重新开始复制
START SLAVE;
# 确认配置生效
SHOW VARIABLES LIKE 'slave_parallel%';
踩坑经验:
开启并行复制后,如果主库的写入没有遵循GTID规范,可能会导致复制错误。建议同时开启GTID模式:
[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
◆ 2.3.2 优化大事务
大事务是延迟的大敌。
拆分大事务:
-- 原来的大事务(不好)
UPDATE orders SET status ='closed'WHERE create_time <'2024-01-01';
-- 拆分成小批量(好)
-- 用存储过程或脚本分批执行
SET@batch_size=10000;
SET@last_id=0;
WHILE EXISTS (SELECT1FROM orders WHERE id >@last_idAND create_time <'2024-01-01' LIMIT 1) DO
UPDATE orders SET status ='closed'
WHERE id >@last_idAND create_time <'2024-01-01'
ORDERBY id LIMIT @batch_size;
SELECTMAX(id) INTO@last_idFROM orders WHERE status ='closed'AND id >@last_id;
SELECT SLEEP(0.1); -- 控制速度
END WHILE;
限制binlog大小:
# 大事务会生成大binlog event,限制一下
max_binlog_size = 100M
binlog_row_image = MINIMAL # 只记录变更的列,减少binlog大小
◆ 2.3.3 升级从库硬件
如果确认是从库IO或CPU瓶颈,该加钱加钱。
常见优化:
配置优化:
[mysqld]
# 从库可以关闭双写,提升性能(主库挂了会丢数据,从库无所谓)
innodb_doublewrite = OFF
# 日志刷盘可以放松(从库数据可以从主库恢复)
innodb_flush_log_at_trx_commit = 2
sync_binlog = 0
# 加大relay log缓冲
relay_log_recovery = ON
relay_log_info_repository = TABLE
master_info_repository = TABLE
◆ 2.3.4 调整参数配置
主库优化(加快binlog生成):
[mysqld]
# 使用ROW格式,并行复制效果更好
binlog_format = ROW
# 组提交优化
binlog_group_commit_sync_delay = 100# 微秒,等待更多事务一起提交
binlog_group_commit_sync_no_delay_count = 10# 累积10个事务就提交,不等了
从库优化:
[mysqld]
# 禁用binlog(如果从库不需要级联复制)
# skip-log-bin
# 关闭query log
slow_query_log = OFF# 从库可以关闭
# 增大relay log空间
relay_log_space_limit = 0# 不限制
三、示例代码和配置
3.1 主库配置优化
# /etc/my.cnf 主库配置
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
# GTID配置
gtid_mode = ON
enforce_gtid_consistency = ON
# 组提交优化
binlog_group_commit_sync_delay = 100
binlog_group_commit_sync_no_delay_count = 10
# 刷盘策略(主库要保证数据安全)
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
# binlog大小和过期
max_binlog_size = 500M
binlog_expire_logs_seconds = 604800# 7天
# 半同步复制(可选,保证数据一致性)
# plugin-load = "rpl_semi_sync_master=semisync_master.so"
# rpl_semi_sync_master_enabled = 1
# rpl_semi_sync_master_timeout = 1000
3.2 从库配置优化
# /etc/my.cnf 从库配置
[mysqld]
server-id = 2# 必须唯一
# GTID配置
gtid_mode = ON
enforce_gtid_consistency = ON
# 并行复制(关键配置)
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 16
slave_preserve_commit_order = ON
# 复制信息存储
relay_log_info_repository = TABLE
master_info_repository = TABLE
relay_log_recovery = ON
# 性能优化(从库可以放松)
innodb_flush_log_at_trx_commit = 2
sync_binlog = 0
innodb_doublewrite = OFF
# 中继日志
relay-log = relay-bin
relay_log_purge = ON
max_relay_log_size = 500M
# 只读(防止误写)
read_only = ON
super_read_only = ON
# 如果不需要级联复制,可以不写binlog
# skip-log-bin
3.3 延迟监控脚本
这个脚本可以定时检测主从延迟并告警:
#!/bin/bash
# mysql_repl_monitor.sh - MySQL主从延迟监控脚本
MYSQL_USER="monitor"
MYSQL_PASS="monitor_password"
SLAVE_HOST="127.0.0.1"
ALERT_THRESHOLD=10 # 延迟超过10秒告警
CRITICAL_THRESHOLD=60 # 延迟超过60秒严重告警
# 钉钉/飞书webhook(可选)
WEBHOOK_URL="https://oapi.dingtalk.com/robot/send?access_token=xxx"
check_repl_status() {
result=$(mysql -h${SLAVE_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e "SHOW SLAVE STATUS\G" 2>/dev/null)
io_running=$(echo"$result" | grep "Slave_IO_Running:" | awk '{print $2}')
sql_running=$(echo"$result" | grep "Slave_SQL_Running:" | awk '{print $2}')
seconds_behind=$(echo"$result" | grep "Seconds_Behind_Master:" | awk '{print $2}')
last_error=$(echo"$result" | grep "Last_SQL_Error:" | cut -d: -f2-)
echo"========================================"
echo"检查时间: $(date '+%Y-%m-%d %H:%M:%S')"
echo"IO线程: $io_running"
echo"SQL线程: $sql_running"
echo"延迟秒数: $seconds_behind"
echo"========================================"
# IO或SQL线程停止
if [ "$io_running" != "Yes" ] || [ "$sql_running" != "Yes" ]; then
alert_msg="[严重] MySQL复制停止! IO线程: $io_running, SQL线程: $sql_running, 错误: $last_error"
echo"$alert_msg"
send_alert "$alert_msg"
return 1
fi
# 延迟告警
if [ "$seconds_behind" == "NULL" ]; then
echo"[警告] 无法获取延迟时间"
elif [ "$seconds_behind" -gt "$CRITICAL_THRESHOLD" ]; then
alert_msg="[严重] MySQL主从延迟 ${seconds_behind}秒,超过阈值 ${CRITICAL_THRESHOLD}秒"
echo"$alert_msg"
send_alert "$alert_msg"
elif [ "$seconds_behind" -gt "$ALERT_THRESHOLD" ]; then
alert_msg="[警告] MySQL主从延迟 ${seconds_behind}秒,超过阈值 ${ALERT_THRESHOLD}秒"
echo"$alert_msg"
send_alert "$alert_msg"
else
echo"[正常] 延迟在正常范围内"
fi
}
send_alert() {
local msg=$1
# 发送钉钉告警
if [ -n "$WEBHOOK_URL" ]; then
curl -s -H "Content-Type: application/json" -d "{\"msgtype\":\"text\",\"text\":{\"content\":\"$msg\"}}""$WEBHOOK_URL" > /dev/null
fi
# 也可以发邮件
# echo "$msg" | mail -s "MySQL复制告警" admin@company.com
}
# 执行检查
check_repl_status
使用方法:
chmod +x mysql_repl_monitor.sh
# 手动执行
./mysql_repl_monitor.sh
# 加入crontab每分钟检查
echo"* * * * * /opt/scripts/mysql_repl_monitor.sh >> /var/log/mysql_repl.log 2>&1" | crontab -
3.4 实际应用案例
◆ 案例一:从30秒优化到毫秒级的全过程
背景:某电商平台,主库TPS约3000,从库延迟经常在20-30秒徘徊。
排查过程:
-- 1. 看延迟情况
SHOW SLAVE STATUS\G
-- Seconds_Behind_Master: 28
-- 2. 看并行复制配置
SHOW VARIABLES LIKE'slave_parallel%';
-- slave_parallel_workers: 0 (单线程,这就是问题!)
-- 3. 看主库写入量
SHOWGLOBAL STATUS LIKE'Com_insert';
-- 每秒约2000次insert
解决方案:
# 从库my.cnf添加
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 16
slave_preserve_commit_order = ON
-- 重启后验证
STOP SLAVE;
-- 重启MySQL
START SLAVE;
SHOW VARIABLES LIKE'slave_parallel%';
-- slave_parallel_workers: 16 ✓
-- 观察延迟
SHOW SLAVE STATUS\G
-- Seconds_Behind_Master: 0 ✓
效果:延迟从30秒降到毫秒级,问题解决。
◆ 案例二:大事务导致延迟的排查
背景:正常情况延迟在1秒以内,但每天凌晨2点会飙到几分钟。
排查过程:
# 1. 对应时间段从库在执行什么
# 查看慢查询日志
grep "2:00" /var/log/mysql/slow.log | head -20
# 发现有个DELETE语句执行了180秒
# 2. 分析binlog
mysqlbinlog --no-defaults --start-datetime="2024-03-15 02:00:00" \
--stop-datetime="2024-03-15 02:05:00" mysql-bin.000123 | grep -c "DELETE"
# 发现大量DELETE
# 3. 确认是什么任务
# 原来是凌晨的数据清理任务,一次性删除几百万条历史数据
解决方案:
# 改造清理脚本,分批删除
#!/bin/bash
# clean_old_data.sh
whiletrue; do
deleted=$(mysql -uroot -p'xxx' -e "DELETE FROM logs WHERE create_time < DATE_SUB(NOW(), INTERVAL 90 DAY) LIMIT 10000; SELECT ROW_COUNT();" -N)
if [ "$deleted" -eq 0 ]; then
echo"清理完成"
break
fi
echo"已删除 $deleted 条,休息1秒..."
sleep 1
done
效果:凌晨清理任务从一次性删除变成分批删除,延迟再也没飙过。
四、最佳实践和注意事项
4.1 最佳实践
◆ 4.1.1 复制架构设计
1. 使用GTID复制
GTID(全局事务标识符)是MySQL 5.6引入的特性,让复制管理更简单:
# 主库和从库都要配置
gtid_mode = ON
enforce_gtid_consistency = ON
好处:
2. 半同步复制
如果对数据一致性要求高,可以开启半同步:
-- 主库
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SETGLOBAL rpl_semi_sync_master_enabled =1;
SETGLOBAL rpl_semi_sync_master_timeout =1000; -- 1秒超时
-- 从库
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SETGLOBAL rpl_semi_sync_slave_enabled =1;
半同步保证主库提交事务前,至少一个从库已收到binlog。
3. 考虑读写分离中间件
用ProxySQL或MaxScale做读写分离,而不是业务代码里硬编码:
-- ProxySQL配置示例
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight) VALUES
(10, '主库IP', 3306, 1), -- 写组
(20, '从库1IP', 3306, 1), -- 读组
(20, '从库2IP', 3306, 1);
-- 读写分离规则
INSERT INTO mysql_query_rules(rule_id, match_pattern, destination_hostgroup) VALUES
(1, '^SELECT', 20),
(2, '.*', 10);
◆ 4.1.2 参数调优
主库参数:
# 关键参数
binlog_format = ROW
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
# 组提交优化
binlog_group_commit_sync_delay = 100
binlog_group_commit_sync_no_delay_count = 10
从库参数:
# 并行复制
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 16
# 可以放松的参数
sync_binlog = 0
innodb_flush_log_at_trx_commit = 2
◆ 4.1.3 监控告警
必须监控的指标:
# Prometheus告警规则
groups:
-name:mysql_replication
rules:
-alert:MySQLSlaveDelay
expr:mysql_slave_status_seconds_behind_master>10
for:2m
labels:
severity:warning
annotations:
summary:"MySQL从库延迟 {{ $value }}秒"
-alert:MySQLSlaveStopped
expr:mysql_slave_status_slave_sql_running==0
for:1m
labels:
severity:critical
annotations:
summary:"MySQL从库SQL线程停止"
4.2 注意事项
◆ 4.2.1 配置注意事项
- • server-id必须唯一:同一个复制拓扑里每个MySQL的server-id不能重复
- • 改配置要谨慎:改主库binlog_format等参数需要重启,要选择低峰期
- • 并行复制不是万能的:如果主库写入本身就是串行的,从库开并行也没用
- • GTID开启后不要关闭:开启后关闭会导致复制中断
◆ 4.2.2 常见错误
| | |
|---|
| | |
| | |
| | pt-table-checksum检查,pt-table-sync修复 |
| | |
◆ 4.2.3 兼容性问题
- • 跨大版本复制:MySQL 8.0做主库,5.7做从库可能有问题
五、故障排查和监控
5.1 常见问题排查
问题一:复制停止,Last_SQL_Error: Duplicate entry
-- 查看错误
SHOW SLAVE STATUS\G
-- Last_SQL_Error: Error 'Duplicate entry '12345' for key 'PRIMARY''
-- 方案1:跳过这个错误(简单但不推荐长期使用)
STOP SLAVE;
SETGLOBAL sql_slave_skip_counter =1;
START SLAVE;
-- 方案2:用pt-table-sync修复数据不一致
pt-table-sync --print --replicate=percona.checksums h=从库IP
pt-table-sync --execute --replicate=percona.checksums h=从库IP
问题二:复制停止,binlog找不到
-- 错误:Got fatal error 1236 from master when reading data from binary log
-- 原因:主库binlog被清理了
-- 解决方案1:用最新的备份恢复从库
-- 解决方案2:如果有其他从库,从其他从库同步
问题三:延迟越来越大,追不上
-- 1. 确认并行复制开启了
SHOW VARIABLES LIKE'slave_parallel%';
-- 2. 看SQL线程在执行什么
SHOW PROCESSLIST;
-- 3. 如果是大事务,考虑临时增加worker数
SETGLOBAL slave_parallel_workers =32;
STOP SLAVE SQL_THREAD;
START SLAVE SQL_THREAD;
5.2 监控指标
MySQL自带监控:
-- 复制状态
SHOW SLAVE STATUS\G
-- 性能指标
SHOWGLOBAL STATUS LIKE'Slave%';
SHOWGLOBAL STATUS LIKE'Rpl%';
Performance Schema监控:
-- 复制线程状态
SELECT*FROM performance_schema.replication_applier_status_by_worker;
-- 复制延迟详情
SELECT*FROM performance_schema.replication_applier_status_by_coordinator;
5.3 告警规则
#!/bin/bash
# 简易监控脚本
# 检查项1:复制状态
io_running=$(mysql -e "SHOW SLAVE STATUS\G" | grep "Slave_IO_Running:" | awk '{print $2}')
sql_running=$(mysql -e "SHOW SLAVE STATUS\G" | grep "Slave_SQL_Running:" | awk '{print $2}')
if [ "$io_running" != "Yes" ] || [ "$sql_running" != "Yes" ]; then
echo"CRITICAL: 复制停止"
exit 2
fi
# 检查项2:延迟
delay=$(mysql -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master:" | awk '{print $2}')
if [ "$delay" == "NULL" ]; then
echo"WARNING: 无法获取延迟"
exit 1
elif [ "$delay" -gt 60 ]; then
echo"CRITICAL: 延迟 ${delay}秒"
exit 2
elif [ "$delay" -gt 10 ]; then
echo"WARNING: 延迟 ${delay}秒"
exit 1
else
echo"OK: 延迟 ${delay}秒"
exit 0
fi
六、总结
6.1 技术要点回顾
- • 延迟检测不能只看Seconds_Behind_Master:用pt-heartbeat更准确
- • 开启并行复制是第一优先级:slave_parallel_workers配置起来
6.2 进阶学习方向
- 1. MGR(MySQL Group Replication):多主复制,自动故障切换
- 2. PXC(Percona XtraDB Cluster):同步复制方案,强一致性
- 4. ProxySQL:智能读写分离,可以感知延迟自动路由
6.3 参考资料
附录
A. 命令速查表
-- 查看从库状态
SHOW SLAVE STATUS\G
-- 查看并行复制配置
SHOW VARIABLES LIKE'slave_parallel%';
-- 停止/启动复制
STOP SLAVE;
START SLAVE;
-- 跳过一个错误
SETGLOBAL sql_slave_skip_counter =1;
-- 重置从库
RESET SLAVE ALL;
-- 配置复制(GTID模式)
CHANGE MASTER TO MASTER_HOST='主库IP', MASTER_USER='repl', MASTER_PASSWORD='xxx', MASTER_AUTO_POSITION=1;
# pt-heartbeat监控延迟
pt-heartbeat --database=percona --monitor --master-server-id=1 -h 从库IP
# pt-table-checksum检查一致性
pt-table-checksum --replicate=percona.checksums h=主库IP
# pt-table-sync修复不一致
pt-table-sync --execute --replicate=percona.checksums h=从库IP
B. 复制状态字段解释
C. 术语表