mysql.user 表中無 root@localhost 或 Host 欄位不匹配(如只有 root@%)。caching_sha2_password,舊客戶端不支援。# 停止服務
systemctl stop mysqld
# 跳過授權表啟動
mysqld_safe --skip-grant-tables --skip-networking &
# 另開終端,無密碼登入
mysql -u root
-- 5.7
USE mysql;
UPDATE user SET authentication_string=PASSWORD('NewPass123!') WHERE User='root' AND Host='localhost';
-- 若無 localhost 行,插入一條
INSERT INTO user (Host,User,Authentication_string) VALUES ('localhost','root',PASSWORD('NewPass123!'));
-- 8.0
USE mysql;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'NewPass123!';
-- 或保留 caching_sha2_password
-- ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPass123!';
FLUSH PRIVILEGES;
# 關閉 skip-grant-tables 進程
mysqladmin -u root -p shutdown
# 或 kill PID
# 正常啟動
systemctl start mysqld
# 驗證
mysql -u root -p'NewPass123!' -e "SELECT USER();"
C:\mysql-init.txt,內容:ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPass123!';"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld" --init-file=C:\mysql-init.txt --console-- 查看當前認證插件
SELECT User, Host, plugin FROM mysql.user WHERE User='root';
-- 統一改為 mysql_native_password(相容舊驅動)
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'NewPass123!';
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'NewPass123!';
FLUSH PRIVILEGES;
mysql_secure_installation 設定強密碼、移除匿名用戶、禁止 root 遠程登入。~/.my.cnf 或密碼管理工具,避免腳本明文。mysql.user 表,清理無用/過期帳號。Access denied 90% 為密碼或 Host 不匹配。跳過授權表啟動 → 修正 user 表 → FLUSH PRIVILEGES → 正常重啟 是通用修復鐵律。MySQL 8.0 額外注意認證插件相容性。