MySQL 遠程連線被拒絕？三大解決方案徹底搞定 Host not allowed

現象

客戶端連線報錯：Host 'xxx.xxx.xxx.xxx' is not allowed to connect to this MySQL server。核心原因：MySQL 授權表中無該 Host+User 組合記錄。

方法一：GRANT 精確授權（推薦）

mysql -u root -p
USE mysql;
GRANT SELECT,INSERT,UPDATE,DELETE ON db_name.* TO 'username'@'client_ip_or_hostname' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

全權限授予：
GRANT ALL PRIVILEGES ON db_name.* TO 'username'@'client_ip_or_hostname' IDENTIFIED BY 'password';

方法二：修改 user 表 Host 欄位為通配符

1. 建用戶：GRANT SELECT,UPDATE,INSERT,DELETE ON *.* TO 'xuys'@'192.168.88.234' IDENTIFIED BY 'xuys1234';
2. 查看：SELECT Host,User,Password FROM user;
3. 將 Host 改為 %（任意主機）：
   UPDATE user SET Host='%' WHERE User='xuys';
4. FLUSH PRIVILEGES;（或 mysqladmin reload）

若仍失效，檢查 db 表是否有對應記錄，需插入/更新：
INSERT INTO db VALUES('192.168.88.234','%','xuys','Y','Y',...);
UPDATE db SET Host='%' WHERE User='xuys';
再次 FLUSH PRIVILEGES。

方法三：phpMyAdmin 遠程配置補充

1. 伺服器端授權：
   GRANT SELECT,UPDATE,INSERT,DELETE ON *.* TO 'sss'@'192.168.0.114' IDENTIFIED BY 'sss';
UPDATE user SET Host='%' WHERE User='sss';
mysqladmin -u root -p reload && mysqladmin -u root -p shutdown && /etc/rc.d/init.d/mysqld start
2. 修改 phpMyAdmin config.inc.php：
   $cfg['Servers'][$i]['host'] = '遠程IP';
$cfg['Servers'][$i]['user'] = 'sss';
$cfg['Servers'][$i]['password'] = 'sss密碼';

提示：GRANT ALL PRIVILEGES 不包含 GRANT 權限，需額外：
UPDATE user SET Grant_priv='Y' WHERE User='sss';