將以下代碼存為 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";
}
}
| 錯誤碼 | 常數 | 典型原因 | 解決方向 |
|---|---|---|---|
| 1045 | ER_ACCESS_DENIED_ERROR | 密碼錯誤、用戶不存在、主機不匹配 | 檢查 mysql.user 表 Host 欄位、FLUSH PRIVILEGES |
| 2002 | CR_CONNECTION_ERROR | Socket 連線失敗 | 檢查 mysqld 運行、bind-address、skip-networking |
| 2003 | CR_CONN_HOST_ERROR | TCP 連線失敗 | 防火牆、安全組、Port 3306、bind-address=0.0.0.0 |
| 1049 | ER_BAD_DB_ERROR | 資料庫不存在 | 建立資料庫或修正名稱 |
| 1698 | ER_ACCESS_DENIED_NO_PASSWORD_ERROR | root 使用 unix_socket 認證 | 建立專用用戶或修改 root plugin 為 mysql_native_password |
systemctl status mysqld / service mysql statusnetstat -ntlp | grep 3306 或 ss -ntlpfirewall-cmd --add-port=3306/tcp/etc/my.cnf bind-address = 0.0.0.0(允許遠端)GRANT ALL ON db.* TO 'user'@'%' IDENTIFIED BY 'pass'; FLUSH PRIVILEGES;php -m | grep -i mysql 確認 mysqli、pdo_mysql 已載入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)定期探測,實現故障秒級感知。