mysql 5.6忘记root密码_Linux平台MySQL忘记root密码解决方案

此处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 -912375

然后以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，或者直接删除都可以。(当然是下次重启之后才生效)