MySQL 禁止遠端存取的兩種解決方法

現象

預設僅允許 localhost 連線，遠端連線報錯：Host 'x.x.x.x' is not allowed to connect to this MySQL server

方法一：修改 user 表 Host 欄位（改表法）

mysql -u root -p
USE mysql;
UPDATE user SET Host = '%' WHERE User = 'root';
FLUSH PRIVILEGES;

-- 驗證
SELECT Host, User FROM user;

將 root 的 Host 從 localhost 改為 %（任意主機）。務必執行 FLUSH PRIVILEGES 生效。

方法二：GRANT 授權建立遠端用戶（授權法，推薦）

允許任意 IP 連線

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;

限定特定 IP 連線

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.6' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;

限定特定資料庫 + 特定 IP

GRANT ALL PRIVILEGES ON dk.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;

補充：root 遠端登入設定（不建議生產使用）

# 在 MySQL 伺服器本機執行
mysql -h localhost -u root
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EXIT;

安全性最佳實務