MySQL 用戶表 host 字段 '%'、'localhost'、'127.0.0.1' 三者權限差異深度解析

MySQL 權限系統核心：Host 匹配機制與三種連接方式的權限隔離

mysql.user 表中 Host 字段的 %、localhost、127.0.0.1 看似相通，實則對應三套完全獨立的權限檢查鏈路。誤解這一點是導致「本機能連、遠程不能連」或反之的首因。

三種 Host 值的本質區別

關鍵結論：% 無法覆蓋 localhost/127.0.0.1

• 用戶 'root'@'%' 不能 通過 mysql -h localhost 登錄
• 用戶 'root'@'localhost' 不能 通過 mysql -h 127.0.0.1 登錄
• 三者權限完全獨立，需分別授權

實戰驗證

-- 創建三個同名不同 Host 用戶
CREATE USER 'test'@'%' IDENTIFIED BY 'p1';
CREATE USER 'test'@'localhost' IDENTIFIED BY 'p2';
CREATE USER 'test'@'127.0.0.1' IDENTIFIED BY 'p3';

-- 分別測試
mysql -u test -p -h 192.168.1.10   # 需輸入 p1
mysql -u test -p -h localhost       # 需輸入 p2
mysql -u test -p -h 127.0.0.1       # 需輸入 p3

常見誤區與正確授權模板

❌ 錯誤：以為 % 覆蓋所有

GRANT ALL ON *.* TO 'app'@'%';  -- 只有 TCP/IP 遠程能用，本機 localhost/127.0.0.1 依然拒絕

✅ 正確：三重授權覆蓋所有場景

-- 1. 遠程 TCP/IP
GRANT ALL ON db.* TO 'app'@'%' IDENTIFIED BY 'pass';

-- 2. 本機 Socket（最常用）
GRANT ALL ON db.* TO 'app'@'localhost' IDENTIFIED BY 'pass';

-- 3. 本機 TCP 回環（部分框架默認用此）
GRANT ALL ON db.* TO 'app'@'127.0.0.1' IDENTIFIED BY 'pass';

FLUSH PRIVILEGES;

排查清單：連不上時按順序檢查

1. SELECT User, Host FROM mysql.user WHERE User='your_user'; 確認三條記錄是否存在
2. SHOW GRANTS FOR 'your_user'@'%'; 分別查看三組權限
3. 確認連接命令用的 -h 參數對應哪種 Host
4. 檢查 skip_name_resolve 設置（影響 DNS 解析）
5. 驗證防火牆/安全組放行 3306 端口

MySQL 8.0 新特性：CREATE USER ... IF NOT EXISTS

CREATE USER IF NOT EXISTS 'app'@'%' IDENTIFIED BY 'pass';
CREATE USER IF NOT EXISTS 'app'@'localhost' IDENTIFIED BY 'pass';
CREATE USER IF NOT EXISTS 'app'@'127.0.0.1' IDENTIFIED BY 'pass';