此處Mysql版本為5.6，其他版本類似

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 5.6.26 |
+-----------+
1 row in set (0.00 sec)

方式一：
先停掉mysql服務（似乎不能透過常規方法，只能用kill的方法）
[op@sAno1y bin]$ ps -ef | grep mysql
op 12274 3281 0 18:31 pts/2 00:00:00 /bin/sh /home/op/softwares/mysql/bin/mysqld_safe --skip-grant-tables
op 12375 12274 0 18:31 pts/2 00:00:00 /home/op/softwares/mysql/bin/mysqld --basedir=/home/op/softwares/mysql --datadir=/home/op/softwares/mysql/data --plugin-dir=/home/op/softwares/mysql/lib/plugin --skip-grant-tables --log-error=/home/op/softwares/mysql/data/sAno1y.err --pid-file=/home/op/softwares/mysql/data/sAno1y.pid
op 12412 3281 0 18:37 pts/2 00:00:00 grep mysql
[op@sAno1y bin]$ kill -9 12274
[op@sAno1y bin]$ kill -9 12375

然後以mysqld_safe的方式啟動：
當然此處需要加上引數 --skip-grant-table 這樣可以跳過授權表
[op@sAno1y bin]$ mysqld_safe --skip-grant-table &
[1] 12422
[op@sAno1y bin]$ 150908 18:38:53 mysqld_safe Logging to '/home/op/softwares/mysql/data/sAno1y.err'.
150908 18:38:53 mysqld_safe Starting mysqld daemon with databases from /home/op/softwares/mysql/data

然後直接輸入mysql -uroot即可進入：
[op@sAno1y bin]$ mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.26 Source distribution

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT user();
+--------+
| user() |
+--------+
| root@ |
+--------+
1 row in set (0.00 sec)

此時不能透過set password修改密碼，如果修改，會報錯：
mysql> SET password=PASSWORD('root');
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

需要透過update方式來修改密碼：
mysql> UPDATE mysql.user SET password=PASSWORD('root') WHERE user='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 4 Changed: 0 Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

此時，密碼修改成功，同時更新授權表。

接下來需要關閉mysql來測試第二種方法。
透過mysqladmin正常關閉，同時可以測試之前更新的密碼是否有效。

[op@sAno1y bin]$ mysqladmin shutdown -uroot -p
Enter password:
150908 18:44:51 mysqld_safe mysqld from pid file /home/op/softwares/mysql/data/sAno1y.pid ended
[1]+ Done mysqld_safe --skip-grant-table

關閉後可以驗證一下是否成功關閉

[op@sAno1y bin]$ ps -ef | grep mysql
op 12569 3281 0 18:46 pts/2 00:00:00 grep mysql

方式二：
（承接上一步，此時mysql已經關閉，如果沒有關閉，請kill掉）

首先修改你應用的配置檔案，此處我用的是生效優先順序最高的配置檔案路徑~/.my.cnf
在[mysqld]下加入
skip-grant-tables

然後啟動

[op@sAno1y bin]$ mysqld_safe &
[1] 13087
[op@sAno1y bin]$ 150908 18:57:07 mysqld_safe Logging to '/home/op/softwares/mysql/data/sAno1y.err'.
150908 18:57:07 mysqld_safe Starting mysqld daemon with databases from /home/op/softwares/mysql/data

[op@sAno1y bin]$

進入mysql
[op@sAno1y bin]$ mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.26 Source distribution

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT user();
+--------+
| user() |
+--------+
| root@ |
+--------+
1 row in set (0.00 sec)

同樣，已經繞過密碼了。
按第一步的方法一樣，修改密碼即可

mysql> UPDATE mysql.user SET password=PASSWORD('root') WHERE user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> exit

當然最後需要將mysql的配置檔案改回來，註釋掉#skip-grant-tables，或者直接刪除都可以。（當然是下次重啟之後才生效）

Linux平臺MySQL忘記root密碼解決方案

相關文章