MySQL 主從復制延遲監控腳本：精準感知 Binlog 差異與事件積壓

MySQL 主從復制延遲監控腳本：精準感知 Binlog 差異與事件積壓

復制延遲是主從架構的核心風險指標。Seconds_Behind_Master 僅反映 SQL 線程延遲，無法揭示 IO 線程積壓、多 Binlog 文件跨度等深層問題。本文提供一個增強版 Shell 監控腳本，通過對比 Master 最新 Binlog 位點與 Slave Relay_Master_Log_File、Read_Master_Log_Pos、Exec_Master_Log_Pos，精準計算未同步事件數與時間延遲。

腳本核心邏輯

1. 檢查 Slave_IO_Running、Slave_SQL_Running 均為 Yes，否則直接報警退出。
2. 獲取 Master SHOW BINARY LOGS 最後一條記錄（最新 Binlog 文件名、大小）。
3. 獲取 Slave SHOW SLAVE STATUS\G 關鍵字段。
4. 對比 Relay_Master_Log_File 與 Master 最新文件：

• 同一文件：計算 Read_Master_Log_Pos - Exec_Master_Log_Pos（SQL 線程積壓事件數）+ Master最新Pos - Read_Master_Log_Pos（IO 線程積壓）。
• 不同文件：累加中間所有 Binlog 文件大小 + 當前文件偏移差，得出總積壓事件數。

完整腳本（rep_delay_monitor.sh）

#!/bin/bash
# MySQL 復制延遲深度監控
# 依賴：mysql client、awk、sed、grep

# ===== 配置區 =====
MASTER_HOST="192.168.99.137"
SLAVE_HOST="192.168.1.220"
MYSQL_USER="root"
MYSQL_PASS="123"
# ===================

# 顏色輸出
RED='\033[1;31m'; GREEN='\033[1;32m'; YELLOW='\033[1;33m'; NC='\033[0m'
log() { echo -e "${GREEN}[INFO]${NC} $1"; }
warn() { echo -e "${YELLOW}[WARN]${NC} $1"; }
err() { echo -e "${RED}[ERROR]${NC} $1"; }

# 1. 檢查 Slave 線程狀態
check_slave_status() {
    local sql_run io_run behind
    sql_run=$(mysql -h"$SLAVE_HOST" -u"$MYSQL_USER" -p"$MYSQL_PASS" -N -e "SHOW SLAVE STATUS\G" | awk '/Slave_SQL_Running:/ {print $2}')
    io_run=$(mysql -h"$SLAVE_HOST" -u"$MYSQL_USER" -p"$MYSQL_PASS" -N -e "SHOW SLAVE STATUS\G" | awk '/Slave_IO_Running:/ {print $2}')
    behind=$(mysql -h"$SLAVE_HOST" -u"$MYSQL_USER" -p"$MYSQL_PASS" -N -e "SHOW SLAVE STATUS\G" | awk '/Seconds_Behind_Master:/ {print $2}')

    [[ "$sql_run" != "Yes" ]] && { err "Slave_SQL_Running: No，復制已停止"; exit 1; }
    [[ "$io_run" != "Yes" ]] && { err "Slave_IO_Running: No，IO 線程異常"; exit 1; }
    log "Slave 線程正常，Seconds_Behind_Master=${behind}s"
}

# 2. 獲取 Master 最新 Binlog 文件名(不含編號)與大小
get_master_binlog() {
    mysql -h"$MASTER_HOST" -u"$MYSQL_USER" -p"$MYSQL_PASS" -N -e "SHOW BINARY LOGS;" |
    tail -1 | awk '{print $1, $2}'
}

# 3. 獲取 Slave 關鍵位點
get_slave_pos() {
    mysql -h"$SLAVE_HOST" -u"$MYSQL_USER" -p"$MYSQL_PASS" -N -e "SHOW SLAVE STATUS\G" |
    awk -F': ' '
        /Relay_Master_Log_File:/ {relay_file=$2}
        /Read_Master_Log_Pos:/ {read_pos=$2}
        /Exec_Master_Log_Pos:/ {exec_pos=$2}
        /Seconds_Behind_Master:/ {behind=$2}
        END {print relay_file, read_pos, exec_pos, behind}
    '
}

# 4. 計算積壓事件數
calc_lag() {
    read -r master_file master_size <<< "$(get_master_binlog)"
    read -r relay_file read_pos exec_pos behind <<< "$(get_slave_pos)"

    master_num=${master_file##*.}
    relay_num=${relay_file##*.}

    if [[ "$master_num" -eq "$relay_num" ]]; then
        # 同一 Binlog 文件
        io_lag=$((master_size - read_pos))
        sql_lag=$((read_pos - exec_pos))
        total_lag=$((io_lag + sql_lag))
        log "同一文件: Master=$master_file($master_size) Relay=$relay_file Read=$read_pos Exec=$exec_pos"
        log "IO積壓事件: $io_lag, SQL積壓事件: $sql_lag, 總積壓: $total_lag, 時間延遲: ${behind}s"
        [[ $total_lag -eq 0 && $behind -eq 0 ]] && log "✅ 完全同步"
    else
        # 跨多個 Binlog 文件：累加中間文件大小
        log "跨文件: Master=$master_file($master_num) Relay=$relay_file($relay_num)"
        # 獲取 Master 從 relay_file 到最新文件的所有大小之和
        local sum_size=0
        mysql -h"$MASTER_HOST" -u"$MYSQL_USER" -p"$MYSQL_PASS" -N -e "SHOW BINARY LOGS;" |
        awk -v start="$relay_file" -v end="$master_file" '
            $1 == start {flag=1}
            flag {sum+=$2}
            $1 == end {flag=0; print sum; exit}
        ' | read sum_size
        io_lag=$((sum_size - read_pos))
        sql_lag=$((read_pos - exec_pos))
        total_lag=$((io_lag + sql_lag))
        warn "⚠️ 跨文件積壓: 總事件數≈$total_lag, 時間延遲: ${behind}s"
    fi
}

# 主流程
check_slave_status
calc_lag

部署與告警集成

# 1. 部署到監控節點
chmod +x rep_delay_monitor.sh

# 2. 測試運行
./rep_delay_monitor.sh

# 3. 接入 Prometheus (Node Exporter textfile collector)
# */1 * * * * /path/rep_delay_monitor.sh --prom > /var/lib/node_exporter/textfile_collector/mysql_repl_delay.prom

# 4. Grafana 告警規則示例
# mysql_repl_delay_events > 10000 FOR 5m
# mysql_repl_delay_seconds > 30 FOR 2m

關鍵指標解讀

總結

單一 Seconds_Behind_Master 易誤判（如大事務執行中顯示 0，實則積壓巨大）。本腳本通過雙端位點對比，量化「事件積壓」與「跨文件跨度」，為容量規劃、故障預警、切換決策提供精準數據支撐。建議納入常態化巡檢與自動化告警體系。