MySQL 主從同步故障會導致數據不一致。本文提供 Shell 腳本(適合 crontab 定時巡檢)與 PHP Web 介面(適合儀表盤監控)兩種實作,檢測 Slave_IO_Running 與 Slave_SQL_Running 狀態,異常時發送郵件告警。
#!/bin/bash
# /usr/local/bin/check_mysql_slave.sh
# crontab: */5 * * * * /usr/local/bin/check_mysql_slave.sh
MYSQL_USER="monitor"
MYSQL_PWD="secure_password"
MYSQL_SOCK="/tmp/mysql.sock"
LOG_FILE="/var/log/mysql_slave_check.log"
ALERT_EMAIL="dba@example.com"
# 獲取同步狀態
STATUS=$(/usr/local/mysql/bin/mysql -u$MYSQL_USER -p$MYSQL_PWD -S $MYSQL_SOCK -e "SHOW SLAVE STATUS\G" | grep -i running)
IO_RUNNING=$(echo "$STATUS" | grep "Slave_IO_Running" | awk '{print $2}')
SQL_RUNNING=$(echo "$STATUS" | grep "Slave_SQL_Running" | awk '{print $2}')
NOW=$(date '+%Y-%m-%d %H:%M:%S')
# 檢查 MySQL 進程
if ! netstat -lntp | grep -q ':3306'; then
echo "[$NOW] MySQL 進程異常" >> $LOG_FILE
echo "MySQL down on $(hostname)" | mail -s "【嚴重】MySQL Down" $ALERT_EMAIL
exit 1
fi
# 檢查同步狀態
if [[ "$IO_RUNNING" == "Yes" && "$SQL_RUNNING" == "Yes" ]]; then
echo "[$NOW] Slave OK" >> $LOG_FILE
else
MSG="[$NOW] Slave 異常: IO=$IO_RUNNING, SQL=$SQL_RUNNING"
echo "$MSG" >> $LOG_FILE
# 獲取錯誤詳情
ERROR=$(/usr/local/mysql/bin/mysql -u$MYSQL_USER -p$MYSQL_PWD -S $MYSQL_SOCK -e "SHOW SLAVE STATUS\G" | grep -E "Last_.*_Error")
echo "$ERROR" | mail -s "【告警】MySQL Slave 同步中斷 - $(hostname)" $ALERT_EMAIL
fi
mysql_instances.php<?php
// 授權:GRANT REPLICATION CLIENT ON *.* TO 'monitor'@'監控機IP' IDENTIFIED BY 'password';
$mysql_instances = [
'10.0.1.10:3306',
'10.0.1.11:3306',
];
function get_instances() {
global $mysql_instances;
return $mysql_instances;
}
?>
check_status_api.php<?php
function check_mysql_replication_status($host, $user, $pass) {
$result = ['result' => -1]; // -1 未知
$mysqli = @new mysqli($host, $user, $pass);
if ($mysqli->connect_errno) {
$result['result'] = -2; // 連接失敗
return $result;
}
$res = $mysqli->query("SHOW SLAVE STATUS");
if (!$res) {
$result['result'] = -3; // 查詢失敗
return $result;
}
$row = $res->fetch_assoc();
if (!$row) {
$result['result'] = 2; // 未配置主從
return $result;
}
$result = $row; // 直接返回所有狀態欄位
if ($row['Slave_IO_Running'] === 'Yes' && $row['Slave_SQL_Running'] === 'Yes') {
$result['result'] = 0; // 正常
} else {
$result['result'] = 1; // 同步失敗
}
return $result;
}
?>
check_repl.php<?php
require 'mysql_instances.php';
require 'check_status_api.php';
define('USERNAME', 'monitor');
define('PASSWORD', 'secure_password');
define('DEBUG', false);
$instances = get_instances();
if (!$instances) die('未配置監控實例');
echo "<meta http-equiv='refresh' content='180'>"; // 3分鐘自動刷新
echo "<table border=1 cellpadding=5><tr>
<th>實例</th><th>狀態</th><th>IO</th><th>SQL</th>
<th>Master_Host</th><th>Master_Port</th><th>Replicate_Do_DB</th><th>備註</th></tr>";
foreach ($instances as $host) {
$res = check_mysql_replication_status($host, USERNAME, PASSWORD);
$memo = '';
switch ($res['result']) {
case 0: $memo = 'OK'; break;
case 1: $memo = '同步失敗: ' . ($res['Last_IO_Error'] ?? '') . ' ' . ($res['Last_SQL_Error'] ?? ''); break;
case 2: $memo = '未配置主從'; break;
case -2: $memo = '連接失敗'; break;
case -3: $memo = '查詢失敗'; break;
default: $memo = '未知異常';
}
$color = $res['result'] === 0 ? 'green' : 'red';
echo "<tr>
<td>$host</td>
<td style='color:$color'>{$res['result']}</td>
<td>{$res['Slave_IO_Running']}</td>
<td>{$res['Slave_SQL_Running']}</td>
<td>{$res['Master_Host']}</td>
<td>{$res['Master_Port']}</td>
<td>{$res['Replicate_Do_DB']}</td>
<td>$memo</td>
</tr>";
}
echo "</table>";
?>
https://monitor-server/check_repl.php?key=your_secret_key(建議加簡單金鑰驗證)
Seconds_Behind_Master、Slave_IO_Running、Slave_SQL_Running、Last_IO_Error、Last_SQL_Error。read_only=1、skip_slave_start=1 防誤寫入與自動恢復衝突。