MySQL忘记root密码后修改

MySQL忘记root密码后可以使用下面的方法修改。

1、登录MySQL所在的服务器，手工kill掉MySQL进程

    kill `cat $mysql_data_dir/hostname.pid`

    $mysql_data_dir/hostname.pid为MySQL数据目录，它记录了MySQL服务的进程号。

[root@rhel6 ~]# ps -ef |grep mysql root      6602     1  0 21:39 ?        00:00:00 /bin/sh ./mysqld_safe --skip-grant-tables --user=mysql mysql     6754  6602  0 21:39 ?        00:00:01 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/lib/mysql/rhel6.er r --pid-file=/var/lib/mysql/rhel6.pidroot      6851  6830  0 21:47 pts/3    00:00:00 grep mysql [root@rhel6 ~]# cat /var/lib/mysql/rhel6.pid  6754 [root@rhel6 ~]# kill `cat /var/lib/mysql/rhel6.pid ` root      6859  6830  0 21:48 pts/3    00:00:00 grep mysql

2、使用--skip-grant-tables选项重启MySQL服务

    --skip-grant-tables意思是启动MySQL服务时跳过权限表认证。启动后，连接到MySQL的root将不需要口令。或者在/etc/my.cnf中添加skip-grant-tables参数，启动mysql，service mysql start/systemctl start mysql

[root@rhel6 bin]# ./mysqld_safe --skip_grant-tables --user=mysql & [1] 6900 [root@rhel6 bin]# 161122 21:52:03 mysqld_safe Logging to '/var/lib/mysql/rhel6.err'. 161122 21:52:03 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql [root@rhel6 bin]# ps -ef |grep mysql root      6900  6830  0 21:52 pts/3    00:00:00 /bin/sh ./mysqld_safe --skip_grant-tables --user=mysql mysql     7052  6900  1 21:52 pts/3    00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/lib/mysql/rhel6.er r --pid-file=/var/lib/mysql/rhel6.pidroot      7075  6830  0 21:52 pts/3    00:00:00 grep mysql [root@rhel6 bin]# mysql -uroot Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.34-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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> 

3、修改root密码

mysql> set password=password('123456'); ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> update user set password=password('123456') where user='root' and host='localhost'; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0

    由于使用了--skip-grant-tables选项启动，使用"set password"命令更改密码失败，直接更新user表的password字段后更改密码成功。

在5.7版本中password有规则策略，123456不符合。

4、刷新权限表

mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)

5、重新用root登录时，必须输入新密码

[root@rhel6 bin]# mysql -uroot  ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) [root@rhel6 bin]# mysql -uroot -p Enter password:  Your MySQL connection id is 4

参考：《深入浅出MySQL》

     本文转自hbxztc 51CTO博客，原文链接：http://blog.51cto.com/hbxztc/1875966，如需转载请自行联系原作者