解决MySQL忘记root密码

浏览数：115 / 时间：2015年06月12日

网上有很多关于忘记MySQL root密码的一些文章，里面都有写怎么去解决，但有时觉得写得太恶心，要么一字不漏的抄别人的，要么就说得不清不楚，好了，不吐槽了，以下是解决的整个过程。

首先我们要知道忘记MySQL root密码后，能否重启mysql，能重启的操作是怎么样的？不能重启的操作又会是怎么样的？

情况一：（能重启情况下）

修改my.cnf配置文件，在mysqld栏下添加skip-grant-tables选项，意思是mysqld server启动之后并不使用权限系统（privilege system），也可以理解为跳过授权表。为了安全起见，通常加上skip-networking，mysqld不侦听任何TCP/IP连接请求。

重启mysqld，然后空密码连接：

[root ~]$mysql -uroot -S /data/mysql-5.5/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.40-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, 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>

可以看到已经成功登录了，然后修改root密码：

mysql> update mysql.user set password=password(‘123456‘) 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.01 sec)

已经成功修改密码了，但还有事情要做，就是把刚刚添加到my.cnf里的skip-grant-tables和skip-networking删除掉或者注释掉。

情况二：(不能重启mysql的情况)

如果不能重启，mysql.user 刚好有权限比较低的用户，如果没有，你请神仙来帮你吧，哈哈

1、为了测试，我自己创建一个用户，可以没什么权限

mysql> create user xuanzhi@‘localhost‘ identified by ‘123456‘;
Query OK, 0 rows affected (0.00 sec)

2、进到数据目录下：

[root mysql-5.5]$ pwd
/data/mysql-5.5
[root mysql-5.5]$ cp mysql/user.* test/
[root mysql-5.5]$ chown mysql.mysql test/user.*

3、用权限比较小的用户登录：

[root mysql-5.5]$mysql -uxuanzhi -p123456 -S /data/mysql-5.5/mysql.sock 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.40-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, 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> use test
Database changed
mysql> update user set password=password(‘123123‘) where user=‘root‘;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

4、把修改后的user.MYD和user.MYI复制到mysql目录下，记得备份之前的文件。

[root mysql-5.5]$ pwd
/data/mysql-5.5
[root mysql-5.5]$ mv mysql/user.MYD mysql/user.MYD.bak
[root mysql-5.5]$ mv mysql/user.MYI mysql/user.MYI.bak
[root mysql-5.5]$ cp test/user.MY* mysql/
[root mysql-5.5]$ chown mysql:mysql  mysql/user.*

5.查找mysql进程号，并且发送SIGHUP信号，重新加载权限表。（有时加载一次不行的时候，再加载多一次@。@）

[root mysql]$ pgrep -n mysql
23166
[root mysql]$ kill -SIGHUP 23166
[root mysql]$ /usr/local/mysql-5.5.40/bin/mysql -uroot -p123123 -S /data/mysql-5.5/mysql.sock 
ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using password: YES)
[root mysql]$ kill -SIGHUP 23166
[root mysql]$ /usr/local/mysql-5.5.40/bin/mysql -uroot -p123123 -S /data/mysql-5.5/mysql.sock 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.40-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, 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>