监控主从复制状态与减少延迟的实践方案
概述
主从延迟是 MySQL 运维中最常见的问题之一。延迟监控和主动优化是保证读写分离架构稳定运行的关键。本篇介绍如何建立有效的复制监控体系，以及各类延迟减少方案。
监控主从复制状态
核心监控指标
-- 需要在监控脚本中定期执行的 SQL
-- 主库：检查 Binlog 生成情况
SHOW MASTER STATUS;
-- 从库：全面复制状态检查
SHOW SLAVE STATUS\G
监控脚本示例（Shell）
#!/bin/bash
# check_replication.sh
MYSQL_USER="monitor"
MYSQL_PASS="password"
MASTER_HOST="192.168.1.10"
SLAVE_HOST="192.168.1.11"
# 1. 检查从库复制状态
SLAVE_STATUS=$(mysql -u $MYSQL_USER -p$MYSQL_PASS -h $SLAVE_HOST -e "SHOW SLAVE STATUS\G")
IO_RUNNING=$(echo "$SLAVE_STATUS" | grep "Slave_IO_Running" | awk '{print $2}')
SQL_RUNNING=$(echo "$SLAVE_STATUS" | grep "Slave_SQL_Running" | awk '{print $2}')
SECONDS_BEHIND=$(echo "$SLAVE_STATUS" | grep "Seconds_Behind_Master" | awk '{print $2}')
# 检查 I/O 线程
if [ "$IO_RUNNING" != "Yes" ]; then
echo "CRITICAL: Slave IO thread is not running!"
# 发送告警
fi
# 检查 SQL 线程
if [ "$SQL_RUNNING" != "Yes" ]; then
echo "CRITICAL: Slave SQL thread is not running!"
# 发送告警
fi
# 检查延迟
if [ "$SECONDS_BEHIND" -gt 60 ]; then
echo "WARNING: Replication lag is ${SECONDS_BEHIND}s (threshold: 60s)"
# 发送告警
elif [ "$SECONDS_BEHIND" -gt 300 ]; then
echo "CRITICAL: Replication lag is ${SECONDS_BEHIND}s (threshold: 300s)"
# 发送告警
fi
Prometheus + Grafana 监控
使用 mysqld_exporter 采集复制指标：
# prometheus.yml
scrape_configs:
- job_name: 'mysql'
static_configs:
- targets: ['192.168.1.10:9104', '192.168.1.11:9104']
Grafana 中配置以下告警规则：
| 指标 | 阈值 | 严重级别 | 说明 |
|---|---|---|---|
mysql_slave_seconds_behind_master |
> 60 秒 | 警告 | 延迟过大 |
mysql_slave_seconds_behind_master |
> 300 秒 | 严重 | 需立即处理 |
mysql_slave_io_running |
!= 1 | 严重 | I/O 线程异常 |
mysql_slave_sql_running |
!= 1 | 严重 | SQL 线程异常 |
mysql_slave_relay_log_space |
> 80% 磁盘 | 警告 | Relay Log 占满磁盘 |
减少延迟的实践方案
方案一：启用并行复制
这是最直接有效的方案：
# 从库配置
slave_parallel_workers = 8
slave_parallel_type = LOGICAL_CLOCK
slave_preserve_commit_order = ON
# 主库配置（让组提交更高效）
binlog_group_commit_sync_delay = 100000
binlog_group_commit_sync_no_delay_count = 10
binlog_transaction_dependency_tracking = WRITESET
方案二：从库硬件升级
# 从库的 innodb_buffer_pool_size 建议与主库一致
innodb_buffer_pool_size = 64G
# 从库的 IO 能力
# SATA SSD → NVMe SSD（随机写性能提升 5-10 倍）
方案三：分离读写负载
-- 不要让从库执行大查询
-- 不好的做法：分析报表和复制在同一从库
SELECT COUNT(*), SUM(amount) FROM orders WHERE YEAR(create_time) = 2026;
-- 好的做法：从库只处理业务读请求
-- 分析报表走专用的分析库（从该从库同步过去）
架构模式：
主库 → 从库（业务读）→ 分析库（报表查询）
↑ 复制用 ↑ 分析查询在这里执行
不影响复制线程
方案四：控制大事务
-- 不好的做法
DELETE FROM old_logs WHERE create_time < '2025-01-01';
-- 一次删除 1000 万行，延迟飙升
-- 好的做法：分批删除
DELIMITER $$
CREATE PROCEDURE batch_delete()
BEGIN
DECLARE affected_rows INT DEFAULT 1;
WHILE affected_rows > 0 DO
DELETE FROM old_logs WHERE create_time < '2025-01-01' LIMIT 1000;
SET affected_rows = ROW_COUNT();
DO SLEEP(0.1); -- 让从库有机会追赶
END WHILE;
END$$
DELIMITER ;
方案五：使用半同步复制
减少网络传输阶段的延迟影响：
rpl_semi_sync_master_enabled = ON
rpl_semi_sync_master_timeout = 5000
方案六：GTID + MASTER_AUTO_POSITION
确保故障切换后从库能快速找到同步点：
gtid_mode = ON
enforce_gtid_consistency = ON
延迟容忍策略
在生产系统中，延迟无法完全消除时，需要建立容忍策略：
延迟 0-5 秒：正常范围，无需处理
延迟 5-30 秒：健康检查，观察趋势
延迟 30-60 秒：预警，排查原因
延迟 >60 秒：紧急处理，可能需要介入
处理动作：
1. 检查大事务（是否在跑批量操作）
2. 检查从库负载（是否有大查询）
3. 检查网络延迟
4. 考虑启用并行复制（如果未启用）
5. 考虑临时停止从库的读流量
面试要点
- 延迟监控的核心 SQL：
SHOW SLAVE STATUS中的三剑客——I/O Running、SQL Running、Seconds_Behind_Master - 最有效的减延迟方案：并行复制 + 从库硬件升级
- 从库不要跑大查询：分析报表走专用分析库
- 大事务分批处理：是减少延迟的最基本操作
- 监控告警阈值：延迟 >60 秒告警，>300 秒紧急
- 延迟无法完全消除：目标是控制在可接受范围内
© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
暂无评论内容