萬盛學電腦網

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

PHP 測試 MySQL 連線可用性:獨立診斷腳本與常見錯誤排查

快速診斷腳本

將以下代碼存為 db_test.php 置於 Web 目錄,瀏覽器訪問即可驗證連線:

<?php
/**
 * MySQL 連線測試腳本
 * 用途:快速隔離 PHP 設定、網路、帳號密碼、權限問題
 */

// 配置區(請修改為實際環境值)
$host = '127.0.0.1';
$user = 'root';
$pass = 'your_password';
$db   = 'test_db';
$port = 3306;

header('Content-Type: text/plain; charset=utf-8');

echo "=== MySQL 連線測試 ===\n";
echo "目標:$host:$port | 用戶:$user | 資料庫:$db\n\n";

// 1. 基礎連線測試(mysqli)
echo "--- 1. mysqli 連線 ---\n";
$mysqli = @new mysqli($host, $user, $pass, $db, $port);
if ($mysqli->connect_errno) {
    echo "❌ 失敗:{$mysqli->connect_error} (錯誤碼:{$mysqli->connect_errno})\n";
    printCommonCauses($mysqli->connect_errno);
} else {
    echo "✅ 成功!Server version: {$mysqli->server_info}\n";
    echo "   字元集:{$mysqli->character_set_name()}\n";
    $mysqli->close();
}

echo "\n--- 2. PDO 連線 ---\n";
try {
    $dsn = "mysql:host=$host;port=$port;dbname=$db;charset=utf8mb4";
    $pdo = new PDO($dsn, $user, $pass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_TIMEOUT => 5,
    ]);
    echo "✅ 成功!Driver: {$pdo->getAttribute(PDO::ATTR_DRIVER_NAME)}\n";
    $stmt = $pdo->query('SELECT VERSION()');
    echo "   版本:{$stmt->fetchColumn()}\n";
} catch (PDOException $e) {
    echo "❌ 失敗:{$e->getMessage()} (Code: {$e->getCode()})\n";
    printCommonCauses($e->getCode());
}

echo "\n--- 3. 選擇資料庫與簡易查詢 ---\n";
try {
    $pdo = new PDO("mysql:host=$host;port=$port;charset=utf8mb4", $user, $pass);
    $pdo->exec("USE `$db`");
    $stmt = $pdo->query('SELECT 1 AS test');
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    echo "✅ 查詢測試:", json_encode($row), "\n";
} catch (Throwable $e) {
    echo "❌ 失敗:{$e->getMessage()}\n";
}

function printCommonCauses(int $code): void {
    $hints = [
        1045 => "帳號或密碼錯誤,或該主機無連線權限",
        2002 => "無法連線至 MySQL Server(服務未啟動、Host/Port 錯誤、防火牆阻擋、Socket 路徑錯誤)",
        2003 => "網路不可達(主機 IP/埠錯誤、防火牆、雲端安全組)",
        1049 => "資料庫不存在",
        1044 => "用戶無該資料庫權限",
        1698 => "Plugin authentication 錯誤(如 root 使用 auth_socket,需密碼認證)",
    ];
    if (isset($hints[$code])) {
        echo "   💡 可能原因:{$hints[$code]}\n";
    }
}

常見錯誤碼對照表

錯誤碼常數典型原因解決方向
1045ER_ACCESS_DENIED_ERROR密碼錯誤、用戶不存在、主機不匹配檢查 mysql.user 表 Host 欄位、FLUSH PRIVILEGES
2002CR_CONNECTION_ERRORSocket 連線失敗檢查 mysqld 運行、bind-address、skip-networking
2003CR_CONN_HOST_ERRORTCP 連線失敗防火牆、安全組、Port 3306、bind-address=0.0.0.0
1049ER_BAD_DB_ERROR資料庫不存在建立資料庫或修正名稱
1698ER_ACCESS_DENIED_NO_PASSWORD_ERRORroot 使用 unix_socket 認證建立專用用戶或修改 root plugin 為 mysql_native_password

進階排查清單

  1. 服務狀態:systemctl status mysqld / service mysql status
  2. 監聽埠口:netstat -ntlp | grep 3306 或 ss -ntlp
  3. 防火牆/安全組:雲端廠商控制台開放 3306,本機 firewall-cmd --add-port=3306/tcp
  4. MySQL 綁定位址:/etc/my.cnf bind-address = 0.0.0.0(允許遠端)
  5. 用戶權限:GRANT ALL ON db.* TO 'user'@'%' IDENTIFIED BY 'pass'; FLUSH PRIVILEGES;
  6. PHP 擴展:php -m | grep -i mysql 確認 mysqli、pdo_mysql 已載入
  7. SELinux/AppArmor:setsebool -P httpd_can_network_connect_db 1(CentOS/RHEL)

自動化健康檢查(納入監控)

// 回傳 JSON 供監控系統抓取
header('Content-Type: application/json');
$checks = [
    'mysqli' => false,
    'pdo'    => false,
    'query'  => false,
    'latency_ms' => 0,
];

$start = microtime(true);
try {
    $mysqli = @new mysqli($host, $user, $pass, $db, $port);
    if (!$mysqli->connect_errno) {
        $checks['mysqli'] = true;
        $mysqli->close();
    }
} catch (Throwable) {}

try {
    $pdo = new PDO("mysql:host=$host;port=$port;dbname=$db;charset=utf8mb4", $user, $pass, [
        PDO::ATTR_TIMEOUT => 3,
    ]);
    $checks['pdo'] = true;
    $pdo->query('SELECT 1');
    $checks['query'] = true;
} catch (Throwable) {}

$checks['latency_ms'] = round((microtime(true) - $start) * 1000, 2);
$checks['timestamp'] = date('c');
echo json_encode($checks);

總結

獨立測試腳本能快速切分「PHP 環境」、「網路通達」、「MySQL 服務」、「帳號權限」四大故障域。建議將 JSON 版本納入監控系統(Zabbix、Prometheus、Uptime Kuma)定期探測,實現故障秒級感知。

copyright © 萬盛學電腦網 all rights reserved