啟動 MySQL 時加入 --skip-grant-tables 參數,**跳過權限表驗證**,允許無密碼以 root 身分登入,進而修改密碼。
# 以管理員身分開啟 CMD / PowerShell
net stop MySQL80
# 或服務名不同
sc query MySQL* | findstr "SERVICE_NAME"
cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"
mysqld --skip-grant-tables --skip-networking
# 視窗會卡住,保持開啟
mysql -u root
-- 8.0 必須先 flush
FLUSH PRIVILEGES;
-- 修改密碼
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStrongPass123!';
-- 5.7 語法
-- UPDATE mysql.user SET authentication_string=PASSWORD('NewStrongPass123!') WHERE User='root' AND Host='localhost';
-- FLUSH PRIVILEGES;
EXIT
# 關閉步驟 2 的 mysqld 視窗
net start MySQL80
# 驗證
mysql -u root -p'NewStrongPass123!' -e "SELECT 'OK'"
systemctl stop mysqld
# 或
service mysql stop
# 確認
ps -ef | grep mysqld
mysqld_safe --skip-grant-tables --skip-networking &
# 或 systemd 臨時覆蓋
systemctl set-environment MYSQLD_OPTS="--skip-grant-tables --skip-networking"
systemctl start mysqld
mysql -u root
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStrongPass123!';
EXIT
# 方式一:殺掉 skip-grant 進程
pkill -f "mysqld.*skip-grant-tables"
systemctl start mysqld
# 方式二:systemd 移除參數
systemctl unset-environment MYSQLD_OPTS
systemctl restart mysqld
mysql -u root -p'NewStrongPass123!' -e "
SELECT User, Host, plugin FROM mysql.user WHERE User='root';
-- 確保有三條記錄
-- root | localhost | caching_sha2_password
-- root | 127.0.0.1 | caching_sha2_password
-- root | ::1 | caching_sha2_password
-- 缺少則補充
CREATE USER IF NOT EXISTS 'root'@'127.0.0.1' IDENTIFIED BY 'NewStrongPass123!';
CREATE USER IF NOT EXISTS 'root'@'::1' IDENTIFIED BY 'NewStrongPass123!';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'::1' WITH GRANT OPTION;
FLUSH PRIVILEGES;"
| 現象 | 原因 | 解決 |
|---|---|---|
| mysqld 啟動即退出 | datadir 權限、配置錯誤、端口佔用 | 查看錯誤日誌 .err |
| FLUSH PRIVILEGES 報錯 | 8.0 權限表結構變更 | 確保用 ALTER USER 而非 UPDATE |
| 修改後仍拒絕連線 | Host 不匹配、Plugin 不支援 | 補全三條 Host 記錄、改用 mysql_native_password |
| Windows 服務啟動失敗 | 手動啟動 mysqld 留下 PID 文件/鎖 | 刪除 data 目錄下 *.pid、*.lock 文件 |
CREATE USER 'dba'@'localhost' IDENTIFIED BY '...' WITH GRANT OPTION;忘記 Root 密碼標準解法:**停服務 → skip-grant-tables+skip-networking → 免密登入 → FLUSH PRIVILEGES → ALTER USER → 正常重啟**。Windows 注意服務名、PID 殘留;Linux 推薦 systemd 環境變數注入參數。修改後務必驗證三條 Host 記錄完整,避免 localhost 登入再次失敗。