MySQL 遠端連線失敗完整排查：Host not allowed、權限設定與防火牆

前言

「Host 'x.x.x.x' is not allowed to connect to this MySQL server」是遠端連線最經典的錯誤。本文系統梳理 MySQL 遠端連線失敗的三大核心原因：使用者帳號主機限制、權限授予、網路/防火牆，並提供分步驟排查與解決方案。

一、核心機制：MySQL 使用者 = '用戶名'@'主機'

MySQL 使用者身份由 用戶名 + 主機 組成，兩者必須完全匹配：

root@localhost      -- 僅允許本機連線
root@192.168.1.%    -- 允許 192.168.1.x 網段
root@%              -- 允許任意主機 (生產慎用)
root@'specific.host' -- 允許特定主機名

二、錯誤代碼對應原因

三、解決方案一：建立遠端存取帳號 (推薦)

1. 登入 MySQL 建立帳號並授權

-- 登入本機 root
mysql -u root -p

-- 建立帳號並授權特定資料庫
CREATE USER 'app_user'@'192.168.1.100' IDENTIFIED BY 'StrongPass123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'192.168.1.100';

-- 或授權所有權限 (慎用)
GRANT ALL PRIVILEGES ON mydb.* TO 'app_user'@'192.168.1.100';

-- 生效權限
FLUSH PRIVILEGES;

2. 通配符主機 (開發/測試環境)

CREATE USER 'dev_user'@'%' IDENTIFIED BY 'DevPass123!';
GRANT ALL PRIVILEGES ON dev_db.* TO 'dev_user'@'%';
FLUSH PRIVILEGES;

⚠️ 生產環境禁止使用 %_，應限定具體 IP/網段。

3. 修改現有帳號主機

-- 查看現有帳號
SELECT User, Host FROM mysql.user;

-- 方法 1：更新 Host 欄位 (需重啟或 FLUSH PRIVILEGES)
UPDATE mysql.user SET Host = '%' WHERE User = 'app_user' AND Host = 'localhost';
FLUSH PRIVILEGES;

-- 方法 2：重建帳號 (更乾淨)
DROP USER 'app_user'@'localhost';
CREATE USER 'app_user'@'%' IDENTIFIED BY 'password';
GRANT ...; FLUSH PRIVILEGES;

四、解決方案二：直接操作 mysql 資料庫表 (舊版/緊急)

USE mysql;

-- 插入 user 表
INSERT INTO user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, ...)
VALUES ('192.168.1.100', 'app_user', PASSWORD('StrongPass123!'), 'Y', 'Y', 'Y', 'Y', ...);

-- 插入 db 表 (資料庫級權限)
INSERT INTO db (Host, Db, User, Select_priv, Insert_priv, ...)
VALUES ('192.168.1.100', 'mydb', 'app_user', 'Y', 'Y', ...);

FLUSH PRIVILEGES;

⚠️ MySQL 5.7+ Password 欄位改為 authentication_string，建議用 CREATE USER / GRANT 方式。

五、解決方案三：phpMyAdmin 遠端連線設定

/* config.inc.php */
$cfg['Servers'][$i]['host'] = '192.168.1.50';  -- 遠端 MySQL IP
$cfg['Servers'][$i]['user'] = 'remote_user';
$cfg['Servers'][$i]['password'] = 'password';
$cfg['Servers'][$i]['port'] = 3306;
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['auth_type'] = 'cookie';

遠端 MySQL 必須已授權 remote_user@phpMyAdmin_Server_IP。

六、網路層排查清單

1. MySQL 監聽地址：

   -- my.cnf
   bind-address = 0.0.0.0  -- 監聽所有介面
   # bind-address = 192.168.1.50  -- 僅特定 IP
   -- 重啟生效
   systemctl restart mysqld

2. 防火牆開放 3306：

   # firewalld
   firewall-cmd --permanent --add-port=3306/tcp
   firewall-cmd --reload
   
   # iptables
   iptables -A INPUT -p tcp --dport 3306 -j ACCEPT
   
   # 雲端安全組 (AWS SG / Azure NSG / GCP Firewall) 亦需開放

3. SELinux / AppArmor：

   # SELinux
   setsebool -P mysql_connect_any 1
   # 或允許特定端口
   semanage port -a -t mysqld_port_t -p tcp 3306

4. 測試連通性：

   telnet 192.168.1.50 3306
   nc -zv 192.168.1.50 3306
   mysql -u app_user -h 192.168.1.50 -p

七、常見坑與避雷指南

八、驗證腳本

#!/bin/bash
# check_mysql_remote.sh
HOST=$1
USER=$2
PASS=$3
DB=$4

mysql -h "$HOST" -u "$USER" -p"$PASS" -e "SELECT USER(), CURRENT_USER(), VERSION();" "$DB"
if [ $? -eq 0 ]; then
    echo "✅ 連線成功"
else
    echo "❌ 連線失敗"
    # 診斷
    nc -zv "$HOST" 3306
    mysql -h "$HOST" -u "$USER" -p"$PASS" -e "SELECT 1" 2>&1 | head -5
fi

結語

遠端連線失敗 90% 是「帳號主機不匹配」+「防火牆未開放」。建立標準化授權流程：建立特定 IP 帳號 → 授權最小權限 → 開放防火牆 → 驗證連線 → 記錄變更，即可避免反覆踩坑。

1. 上一篇文章： MySQL 索引深度解析：類型、建立時機、失效場景與最佳實踐
2. 下一篇文章： MySQL 5.0 儲存程序完整指南：建立、呼叫、流程控制與常用運算子

MySQL 綜合教學

MySQL 索引從入門到精通：原理、優化、避坑與實戰技巧

MySQL 索引從入門到精通：原理、優化、避坑與實戰技巧

MySQL 常用查詢模式速查：GROUP BY、LEFT JOIN、子查詢、HAVING vs WHERE

面試高頻、業務高發的四大查詢模式：GROUP BY 聚合、L

Windows 本地搭建 WordPress 完整實踐：XAMPP 環境配置、數據庫創建與域名綁定

第一步：安裝 XAMPP 集成環境 下載：https://

MySQL JOIN 連接查詢圖解：INNER、LEFT、RIGHT、FULL 外連接全面解析

測試數據準備 CREATE TABLE `a` ( `a