MySQL的root密码忘记后重置方法

weewee

如果忘记了MySQL的root密码怎么办？
我们默认的情况下是没有给MySQL设置密码的，如下
默认的登录MySQL
[iyunv@LAMPLINUX ~]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.40-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>quit
Bye
正常情况下为了安全考虑我们应该给MySQL去设置一个密码

[iyunv@LAMPLINUX ~]# mysqladmin -uroot password 'lamlinux'
设置完成，我们再次登录就要输入密码了
[iyunv@LAMPLINUX ~]# mysql -uroot -plamlinux
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.40-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>quit
Bye
假如说我们密码忘记了怎么办，登陆不上MySQL怎么办？

把密码初始化，重新设置一个，
进入配置文件
[iyunv@LAMPLINUX ~]# vim /etc/my.cnf
在‘慢查询’下面加入一句话
skip-grant （跳过授权）
：wq
重启MySQL

[iyunv@LAMPLINUX ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
初始化完成
可以直接用“mysql”命令，不需要输入-p密码，就可以直接进入MySQL了，

[iyunv@LAMPLINUX ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.40-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
然后去使用mysql库

mysql> use mysql
No connection. Trying to reconnect...
Connection id:    2
Current database: *** NONE ***
Database changed
然后去更新一个表，即更改root用户的密码
mysql> update user set password=password('lam2linux') where user='root';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    4
Current database: mysql
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0
从信息中我们看到Query OK, 3 rows affected (0.01 sec)显示第3行发生改变，
用以下命令可以查看变更信息
mysql>select * from user where user='root'\G;

退出MySQL
mysql>exit
我们再把MySQL配置文件里的“skip-grant”去掉

[iyunv@LAMPLINUX ~]# vim /etc/my.cnf
去掉 skip-grant
：wq
重启MySQL


[iyunv@LAMPLINUX ~]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL. SUCCESS!
再次登录MySQL需要输入密码

[iyunv@LAMPLINUX ~]# mysql -uroot -plam2linux
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.40-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
至此，修改密码后并成功登录