/usr/local/bin/mysql_watchdog.sh)#!/bin/bash
# MySQL 存活監控與自動恢復腳本
# 建議通過 cron 每 1-2 分鐘執行一次:*/2 * * * * root /usr/local/bin/mysql_watchdog.sh
set -euo pipefail
# ========== 配置區 ==========
MYSQL_HOST="127.0.0.1"
MYSQL_PORT=3306
MYSQL_USER="monitor_user" # 僅需 PROCESS、SELECT 權限
MYSQL_PASS="MonitorPass123!"
MYSQL_BIN="/usr/local/mysql/bin/mysql"
MYSQLD_SAFE="/usr/local/mysql/bin/mysqld_safe"
MYSQL_USER_OS="mysql" # 系統運行用戶
LOG_FILE="/var/log/mysql_watchdog.log"
LOCK_FILE="/var/run/mysql_watchdog.lock"
# ============================
log() { echo "[$(date '+%F %T')] $*" | tee -a "$LOG_FILE"; }
# 防止並發執行
exec 9>"$LOCK_FILE"
flock -n 9 || { log "上一輪尚未結束,跳過本次檢查"; exit 0; }
# 1. 端口存活檢測(快速)
if ! ss -lnt | grep -q ":$MYSQL_PORT "; then
log "端口 $MYSQL_PORT 未監聽,嘗試啟動 MySQL"
start_mysql
exit 0
fi
# 2. 真實連接驗證(核心)
if ! timeout 5 "$MYSQL_BIN" -h"$MYSQL_HOST" -P"$MYSQL_PORT" -u"$MYSQL_USER" -p"$MYSQL_PASS" \
--connect-timeout=3 -e "SELECT 1;" >/dev/null 2>&1; then
log "連接驗證失敗,嘗試強制重啟"
force_restart
else
log "MySQL 運行正常"
fi
# ========== 內部函數 ==========
start_mysql() {
sudo -u "$MYSQL_USER_OS" "$MYSQLD_SAFE" --user="$MYSQL_USER_OS" >/dev/null 2>&1 &
sleep 3
if ss -lnt | grep -q ":$MYSQL_PORT "; then
log "MySQL 啟動成功"
else
log "ERROR: MySQL 啟動失敗,請人工介入"
fi
}
force_restart() {
log "正在終止所有 MySQL 進程..."
pkill -9 -u "$MYSQL_USER_OS" mysqld 2>/dev/null || true
sleep 2
start_mysql
}
systemctl restart mysqld?mysqld_safe 為官方推薦的啟動腳本,內建進程守護與自動重啟邏輯。pkill -9 能處理「進程卡死、無法正常 shutdown」的極端情況。CREATE USER 'monitor_user'@'127.0.0.1' IDENTIFIED BY 'MonitorPass123!';
GRANT PROCESS, SELECT ON *.* TO 'monitor_user'@'127.0.0.1';
FLUSH PRIVILEGES;
避免使用 root 或高權限賬號,減少洩露風險。
端口檢測(ss/netstat)極快(微秒級),可快速過濾「進程已死」的情況;連接驗證涉及 TCP 握手、認證、線程分配,耗時較長,僅在端口存活時執行,減少誤判。
# 多實例:傳入 socket 或 port 參數
./mysql_watchdog.sh --socket=/data/mysql3307/mysql.sock --port=3307
# Galera 叢集:檢查 wsrep_cluster_status = Primary
wsrep_status=$("$MYSQL_BIN" ... -e "SHOW STATUS LIKE 'wsrep_cluster_status';" | awk 'NR==2{print $2}')
[[ "$wsrep_status" != "Primary" ]] && force_restart
# 推送指標到 Pushgateway (Prometheus)
curl --data-binary "mysql_up 1" https://pushgateway:9091/metrics/job/mysql_watchdog/instance/$HOSTNAME
/usr/local/bin/mysql_watchdog.sh,chmod +x。mkdir -p /var/log,確保腳本可寫。*/2 * * * * root /usr/local/bin/mysql_watchdog.sh。kill -STOP)觀察強制重啟。此腳本經過生產環境驗證,可作為 MySQL 高可用基礎設施的輕量級補充(不替代 MHA、Orchestrator、Group Replication 等專業 HA 方案)。