MySQL忘記密碼解決方案

一. 在配置檔案/etc/my.cnf添加一行: skip-grant-tables, 跳過密碼驗證.

二. 重新開機mysql資料庫主程序

/etc/init.d/mysqld restart           

三. 不使用密碼登入資料庫, 然後修改密碼

mysql> use mysql;
 Database changed
mysql> update user set authentication_string=password('yourNewPassword') where user='root' and host='localhost'; 
 Query OK, 1 row affected (0.00 sec)
mysql> flush privileges;
 Query OK, 0 rows affected (0.00 sec)
mysql> exit           

這裡需要修改的字段是authentication_string, 這點和老版本不同. 舊版本為

set password=password(‘admin')           

四. 如果你設定的密碼太簡單, 則在資料庫執行任何指令都會報類似如下錯誤(需要跳到第1步重新開始)

mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> use mysql;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.           

五. 如果隻想設定簡單密碼需要修改兩個全局參數

mysql> set global validate_password_policy=0;
 Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=1; 
 Query OK, 0 rows affected (0.00 sec)
mysql> update user set authentication_string=password('yourNewPassword') where user='root' and host='localhost';
 Query OK, 1 row affected (0.00 sec)
mysql> use mysql;
 Database changed