Root 密碼遺失是資料庫運維的經典場景。本文提供 Windows、Linux/Unix 通用的「跳過權限表啟動 → 修改密碼 → 刷新權限 → 正常重啟」標準化重置流程。
MySQL 啟動參數 --skip-grant-tables 使伺服器:
mysql.user, db, tables_priv 等)必須採取隔離措施:防火牆封鎖 3306 埠、停止應用服務、僅本機操作、甚至拔網線。
mysqld.exe / mysqld-nt.exe,或 net stop mysqlcd C:\Program Files\MySQL\MySQL Server 8.0\bin
mysqld --skip-grant-tables --console
視窗會卡住不返回提示符,保持開啟
cd C:\Program Files\MySQL\MySQL Server 8.0\bin
mysql -u root
-- 進入 mysql> 提示符後
FLUSH PRIVILEGES; -- 關鍵:載入權限表使後續 UPDATE 生效
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStrongPass123!';
-- 或 5.7 語法
UPDATE mysql.user SET authentication_string=PASSWORD('NewStrongPass123!') WHERE User='root';
FLUSH PRIVILEGES;
EXIT;
--skip-grant-tables 視窗 (Ctrl+C),net start mysqlmysql -u root -p'NewStrongPass123!'my.ini,[mysqld] 區段加入 skip-grant-tablesskip-grant-tables,重啟服務systemctl stop mysqld 或 service mysql stopmkdir -p /etc/systemd/system/mysqld.service.d
cat > /etc/systemd/system/mysqld.service.d/skip-grant.conf <
systemctl daemon-reload
systemctl start mysqld
mysql -u root
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStrongPass123!';
EXIT;
rm /etc/systemd/system/mysqld.service.d/skip-grant.conf
systemctl daemon-reload
systemctl restart mysqld
# 1. 停止
/etc/init.d/mysql stop
# 或 killall mysqld
# 2. 跳過權限啟動 (背景執行)
mysqld_safe --skip-grant-tables &
# 3. 修改密碼
mysql -u root
FLUSH PRIVILEGES;
UPDATE mysql.user SET authentication_string=PASSWORD('NewPass') WHERE User='root';
FLUSH PRIVILEGES;
QUIT
# 4. 正常重啟
killall mysqld
/etc/init.d/mysql start
systemctl stop mysql
mysqld --skip-grant-tables --user=mysql &
mysql -u root
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPass';
QUIT
pkill mysqld
systemctl start mysql
| 版本 | 密碼欄位 | 修改語法 |
|---|---|---|
| 5.6 / 5.7 | password | UPDATE user SET password=PASSWORD('new') WHERE user='root'; |
| 5.7.6+ | authentication_string | UPDATE user SET authentication_string=PASSWORD('new') WHERE user='root'; |
| 8.0+ | authentication_string | ALTER USER 'root'@'localhost' IDENTIFIED BY 'new'; (推薦)UPDATE user SET authentication_string='new' WHERE user='root'; (舊語法仍支援) |
skip-grant-tables 已完全移除(配置檔、systemd override、啟動腳本)validate_password 插件強制複雜度Root 密碼重置是「破玻璃取斧頭」級操作,必須在受控、隔離、可審計環境下執行。建立標準化 SOP 並定期演練,將風險降至最低。