創建用戶 appadmin@'%' (允許任意主機),但本地 mysql -u appadmin -p 登錄失敗:
ERROR 1045 (28000): Access denied for user 'appadmin'@'localhost' (using password: YES)
MySQL 認證時按 user 表排序:Host 具體度降序 → User 具體度降序。
'%' (通配符) 最不具體,排最後。'localhost' 比 '%' 具體,排前面。''@'localhost' 比 'appadmin'@'%' 更具體**,排在前面!mysql.user 排序SELECT Host, User FROM mysql.user ORDER BY Host DESC, User DESC;
+-----------+------------------+
| Host | User |
+-----------+------------------+
| localhost | root |
| localhost | | ← 匿名用戶,User 為空
| localhost | debian-sys-maint |
| % | appadmin | ← 你創建的用戶
+-----------+------------------+
本地連接時,MySQL 先匹配到 ''@'localhost',要求**無密碼**,導致你的密碼登錄失敗。
localhost 用戶 (最直接)CREATE USER 'appadmin'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON test.* TO 'appadmin'@'localhost';
FLUSH PRIVILEGES;
mysql_secure_installation 會做此步)DROP USER ''@'localhost';
DROP USER ''@'%'; -- 如果存在
FLUSH PRIVILEGES;
UPDATE mysql.user SET Password=PASSWORD('newpass') WHERE User='' AND Host='localhost';
FLUSH PRIVILEGES;
-- 1. 創建用戶 (指定具體 host 或 %)
CREATE USER 'appadmin'@'%' IDENTIFIED BY 'strong_password';
CREATE USER 'appadmin'@'localhost' IDENTIFIED BY 'strong_password'; -- 同步創建 localhost 版本
-- 2. 授權
GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'appadmin'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'appadmin'@'localhost';
-- 3. 刷新權限
FLUSH PRIVILEGES;
-- 4. 驗證
SELECT Host, User, authentication_string FROM mysql.user WHERE User='appadmin';
-- 8.0+ 推薦用 caching_sha2_password (默認)
CREATE USER 'appadmin'@'%' IDENTIFIED BY 'strong_password';
-- 兼容舊客戶端可指定
CREATE USER 'appadmin'@'%' IDENTIFIED WITH mysql_native_password BY 'strong_password';
-- 授權語法不變
GRANT SELECT, INSERT ON `test`.* TO 'appadmin'@'%';
''@'localhost' 搶先匹配。user@'localhost' 或刪除匿名用戶。%' 和 'localhost' 兩條記錄**。mysql_secure_installation 自動清理匿名用戶、禁止 root 遠程登錄、刪除 test 庫。