萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

Linux 下 PHP 監控 MySQL 主從同步狀態(Shell + PHP 双模式)

前言

MySQL 主從同步故障會導致數據不一致。本文提供 Shell 腳本(適合 crontab 定時巡檢)與 PHP Web 介面(適合儀表盤監控)兩種實作,檢測 Slave_IO_Running 與 Slave_SQL_Running 狀態,異常時發送郵件告警。

Shell 版本(適合 crontab)

#!/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

PHP Web 監控版(適合納入監控儀表盤)

1. 配置檔 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;
}
?>

2. 核心檢測 API 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;
}
?>

3. 顯示頁面 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(建議加簡單金鑰驗證)

生產級監控建議

  • 使用 Prometheus + mysqld_exporter 採集指標,Grafana 視覺化,Alertmanager 告警。
  • 監控關鍵指標:Seconds_Behind_Master、Slave_IO_Running、Slave_SQL_Running、Last_IO_Error、Last_SQL_Error。
  • 從庫設定 read_only=1、skip_slave_start=1 防誤寫入與自動恢復衝突。
copyright © 萬盛學電腦網 all rights reserved