| 指標 | 來源 | 正常值 | 異常含義 |
|---|---|---|---|
| Slave_IO_Running | SHOW SLAVE STATUS | Yes | No → 網路斷線、認證失敗、主庫無 binlog |
| Slave_SQL_Running | SHOW SLAVE STATUS | Yes | No → SQL 執行錯誤、鍵衝突、觸發器失敗 |
| Seconds_Behind_Master | SHOW SLAVE STATUS | 0 或極小 | 大 → 從庫延遲，SQL 執行慢、大事務、鎖競爭 |
| Relay_Log_Space | SHOW SLAVE STATUS | 穩定 | 持續增長 → SQL Thread 處理不及 |
| Last_Error / Last_IO_Error | SHOW SLAVE STATUS | 空 | 具體錯誤訊息 |
mysql -e "SHOW SLAVE STATUS\G" | grep -E 'Running|Behind|Error'pt-heartbeat：精準測量延遲（不依賴 Seconds_Behind_Master）。pt-slave-restart：自動跳過特定錯誤重啟 SQL Thread。pt-table-checksum：驗證主從資料一致性（需預建 checksum 表）。check_mysql_slave 外掛腳本。#!/bin/bash
# mysql_slave_monitor.sh - 放入 cron 每分鐘執行
SERVER=$(hostname)
SEC_BEHIND=$(/usr/bin/mysql -e "SHOW SLAVE STATUS\G" |
awk -F': ' '/Seconds_Behind_Master/ {print $2}')
BROKEN_FLAG=/tmp/mysql_slave_broken.flag
DELAY_FLAG=/tmp/mysql_slave_delay.flag
THRESHOLD=60 # 秒
if [ "$SEC_BEHIND" = "NULL" ]; then
# 同步中斷
if [ ! -f "$BROKEN_FLAG" ]; then
echo "[$(date)] Slave replication BROKEN on $SERVER" | mail -s "MySQL Slave Broken" admin@example.com
touch "$BROKEN_FLAG"
fi
[ -f "$DELAY_FLAG" ] && rm "$DELAY_FLAG"
else
# 同步正常
[ -f "$BROKEN_FLAG" ] && { echo "[$(date)] Slave RESTORED on $SERVER" | mail -s "MySQL Slave Restored" admin@example.com; rm "$BROKEN_FLAG"; }
if [ "$SEC_BEHIND" -gt "$THRESHOLD" ]; then
[ ! -f "$DELAY_FLAG" ] && { echo "[$(date)] Slave DELAY ${SEC_BEHIND}s on $SERVER" | mail -s "MySQL Slave Delay" admin@example.com; touch "$DELAY_FLAG"; }
else
[ -f "$DELAY_FLAG" ] && { echo "[$(date)] Slave delay RECOVERED (${SEC_BEHIND}s) on $SERVER" | mail -s "MySQL Slave Recovered" admin@example.com; rm "$DELAY_FLAG"; }
fi
fi將上述腳本封裝為 Jenkins Job，設定 Build Trigger 每分鐘輪詢，郵件/Slack/Webhook 通知，零成本構建生產級監控。