快捷導(dǎo)航

MySQL主從復(fù)制延遲原因分析、判斷方法與優(yōu)化方案

更新時間：2026年03月03日 08:33:53 作者：·云揚·

在 MySQL 主從架構(gòu)的生產(chǎn)環(huán)境中,主從復(fù)制延遲是最常見、最影響業(yè)務(wù)穩(wěn)定性的問題之一,今天我把實戰(zhàn)中總結(jié)的延遲核心原因、精準(zhǔn)判斷方法、可落地優(yōu)化方案全流程整理出來,希望能幫大家徹底搞定主從延遲問題,需要的朋友可以參考下

一、深度解析：主從復(fù)制延遲的 5 大核心原因

主從復(fù)制的核心流程：主庫寫入 Binlog → 從庫 IO 線程拉取 Binlog 生成 Relay Log → 從庫 SQL 線程回放 Relay Log。

延遲的本質(zhì)：主庫寫入速度＞從庫同步 + 回放速度。

主庫增刪改并發(fā)過高，從庫單線程 “接不住”
主庫可多線程并發(fā)寫入，從庫默認(rèn)單 SQL 線程回放；主庫并發(fā)突破從庫處理能力，延遲會持續(xù)累積。
可通過 Sysbench 壓測復(fù)現(xiàn)：

sysbench --db-driver=mysql --mysql-host=192.168.184.151 --mysql-port=3306 --mysql-user='repl_rw' --mysql-password='Uda_dQc63' --mysql-db=sysbench_db --threads=4 --table_size=500000 --tables=4 --time=100 oltp_write_only prepare

大表 DDL 操作：元數(shù)據(jù)鎖 + 同步機制導(dǎo)致延遲
ALTER TABLE 等 DDL 會生成元數(shù)據(jù)鎖，主庫執(zhí)行完才寫入 Binlog；從庫單線程回放時，所有事務(wù)排隊，延遲驟增。
建議：大表 DDL 優(yōu)先用 Online DDL，或在業(yè)務(wù)低峰期執(zhí)行。

從庫備份：FLUSH TABLE 阻塞寫入
mysqldump、xtrabackup 備份會觸發(fā)FLUSH TABLES WITH READ LOCK，長時間持有鎖會阻塞從庫 SQL 線程，直接引發(fā)延遲。

大事務(wù)：從庫單線程 “卡脖子”
主庫可并發(fā)執(zhí)行大事務(wù)，從庫單線程必須等大事務(wù)完整回放才能處理后續(xù)事務(wù)；批量插入百萬級數(shù)據(jù)，延遲會快速擴大。

從庫硬件 “拖后腿”
從庫 CPU、內(nèi)存、磁盤 IO 配置低于主庫，無法跟上主庫 Binlog 生成速度，是最容易被忽略的底層瓶頸。

二、科學(xué)判斷：3 種主從延遲檢測方法（含 GTID 腳本）

避免單一指標(biāo)誤判，推薦組合使用以下方法：

基礎(chǔ)指標(biāo)：Seconds_Behind_Master
從庫執(zhí)行show slave status\G，該值 > 0 代表有延遲；
局限：網(wǎng)絡(luò)異常時可能誤判，無法查看落后事務(wù)數(shù)。
精準(zhǔn)對比：基于位點的復(fù)制校驗
通過 4 個參數(shù)判斷同步狀態(tài)：
- Master_Log_File：IO 線程讀取的主庫 Binlog 文件
- Read_Master_Log_Pos：IO 線程讀取的位點
- Relay_Master_Log_File：SQL 線程執(zhí)行的 Binlog 文件
- Exec_Master_Log_Pos：SQL 線程執(zhí)行的位點
  文件 / 位點不一致，說明 IO 或 SQL 線程未追平。
高效方案：基于 GTID 的事務(wù)數(shù)校驗（生產(chǎn)首選）
對比Retrieved_Gtid_Set與Executed_Gtid_Set，精準(zhǔn)計算落后事務(wù)數(shù)。

實操：GTID 延遲檢測腳本

1）主庫創(chuàng)建監(jiān)控用戶

CREATE USER 'delay_check'@'%' IDENTIFIED BY 'Yd_asdfa15';
GRANT REPLICATION CLIENT ON *.* TO 'delay_check'@'%';
FLUSH PRIVILEGES;

2）從庫編寫腳本check_rel_delay.sh

#!/bin/bash
MYSQL_USER="delay_check"
MYSQL_PASS="Yd_asdfa15"
MYSQL_HOST="192.168.12.162"
MYSQL_PORT="3306"

while true; do
RETRIEVED=$(mysql -u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} -P${MYSQL_PORT} -N -e "show slave status\G" | grep "Retrieved_Gtid_Set" | awk -F: '{print $3}' | awk -F- '{print $2}')
EXECUTED=$(mysql -u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} -P${MYSQL_PORT} -N -e "show slave status\G" | grep "Executed_Gtid_Set" | awk -F: '{print $3}' | awk -F- '{print $2}')
DELAY=$((RETRIEVED - EXECUTED))
echo "$(date +'%Y-%m-%d %H:%M:%S') - 從庫落后主庫事務(wù)數(shù)：${DELAY}"
sleep 1
done

3）賦權(quán)并運行

chmod +x /data/script/check_rel_delay.sh
sh /data/script/check_rel_delay.sh

三、落地優(yōu)化：6 種主從延遲解決方案

對癥下藥，以下方案可直接上線：

開啟多線程復(fù)制（MTS）
MySQL 5.7 + 推薦配置：

slave_parallel_workers=4
slave_parallel_type=LOGICAL_CLOCK
slave_preserve_commit_order=1

配置后重啟 SQL 線程：

stop slave sql_thread; start slave sql_thread;

調(diào)整參數(shù)降低 IO 壓力
從庫臨時優(yōu)化（平衡性能與安全）：

set global innodb_flush_log_at_trx_commit=2;
set global sync_binlog=100;

升級從庫硬件
- CPU：與主庫同規(guī)格
- 內(nèi)存：innodb_buffer_pool_size設(shè)為物理內(nèi)存 50%~70%
- 磁盤：更換 SSD 提升 IO 速度
拆分大事務(wù)
單次操作控制在 1 萬行內(nèi)，避免長事務(wù)阻塞：

DELIMITER //
CREATE PROCEDURE batch_insert()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 100 DO
insert into sbtest1(k,c,pad) select k,c,pad from sbtest1 limit 10000;
COMMIT;
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
CALL batch_insert();

無鎖 DDL：pt-online-schema-change
避免大表 DDL 鎖表延遲：

pt-online-schema-change D=sysbench_db,t=sbtest1 --alter="add column d char(10) after c" -u repl_rw -p Uda_dQc63 -h 192.168.12.161 --port 3306 --execute