MySQL 遠端連線設定完整指南：建立帳號、GRANT 授權、修改 Host 與驗證

前言

MySQL 預設僅允許本機連線。本文提供建立遠端存取帳號的標準流程：建立特定 IP 帳號 → 修改為通配符 '%' → GRANT 完整權限 → FLUSH PRIVILEGES 生效 → 實測驗證。

步驟 1：建立特定 IP 遠端帳號

mysql -u root -p
mysql> GRANT SELECT, UPDATE, INSERT, DELETE ON *.* TO 'root'@'192.168.1.12' IDENTIFIED BY 'root';

驗證 user 表變化

mysql> USE mysql;
mysql> SELECT host, user, password FROM user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| 192.168.1.12 | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+-----------+------+-------------------------------------------+

步驟 2：授權法 - 開放任意 IP 連線 (開發環境適用)

修改 host 為通配符

mysql> UPDATE user SET host = '%' WHERE user = 'root';

授予完整權限

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root';
mysql> FLUSH PRIVILEGES;  -- 關鍵！

驗證結果

mysql> SELECT host, user, password FROM user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| 192.168.1.12 | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| %         | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+-----------+------+-------------------------------------------+

步驟 3：改表法 - 直接修改 user 表 (快速但不建議生產環境)

mysql> USE mysql;
mysql> UPDATE user SET host = '%' WHERE user = 'root';
mysql> FLUSH PRIVILEGES;

網路與防火牆必要設定

• my.cnf： bind-address = 0.0.0.0 (或註解掉)，重啟 MySQL
• 防火牆： 開放 TCP 3306 埠
• 雲端安全組： 入站規則允許 3306 (限制來源 IP 更佳)

MySQL 8.0+ 額外注意

• 新建帳號預設使用 caching_sha2_password 插件
• 舊版客戶端需指定 mysql_native_password：

  CREATE USER 'remote'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

• 或修改既有帳號：

  ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

安全性最佳實踐

1. 禁止 root 遠端登入，建立專用應用帳號
2. 限定來源 IP，避免使用 '%'
3. 最小權限原則，僅授予必要資料庫/表/欄位權限
4. 啟用 SSL 加密傳輸
5. 定期審核 mysql.user 表，移除無用帳號

驗證遠端連線

# 遠端機器測試
mysql -h  -u root -p
# 成功進入 mysql> 提示符即設定完成

總結

標準流程：建立帳號 → GRANT 權限 → FLUSH PRIVILEGES → 設定 bind-address → 開放防火牆 → 實測驗證。生產環境嚴禁 root '%'，請遵循最小權限與來源 IP 限定原則。