嘗試遠程連線 MySQL 時出現:
ERROR 1130 (HY000): Host '10.0.0.1' is not allowed to connect to this MySQL server
原因是 MySQL 預設只允許 localhost 本地連線,遠程主機需額外授權。
mysql -u root -p
USE mysql;
UPDATE user SET host = '%' WHERE user = 'root';
FLUSH PRIVILEGES;
缺點:允許任意 IP 以 root 身分連線,安全風險極高。
允許任意 IP 連線:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'your_password' WITH GRANT OPTION;
限定特定 IP 連線(更安全):
-- 僅授予必要權限
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'202.11.10.253' IDENTIFIED BY 'strong_pass';
-- 或授予所有權限
GRANT ALL PRIVILEGES ON mydb.* TO 'app_user'@'202.11.10.253' IDENTIFIED BY 'strong_pass';
執行完畢必須刷新權限:
FLUSH PRIVILEGES;
CREATE USER 再 GRANTFLUSH PRIVILEGES 即時生效root@'%',應建立專用帳號、最小權限原則使用 Navicat、DBeaver 或命令列測試:
mysql -h db_server_ip -u app_user -p