MySQL忘记root密码后修改

231211

MySQL忘记root密码后可以使用下面的方法修改。
1、登录MySQL所在的服务器，手工kill掉MySQL进程
    kill `cat $mysql_data_dir/hostname.pid`
    $mysql_data_dir/hostname.pid为MySQL数据目录，它记录了MySQL服务的进程号。

[iyunv@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
[iyunv@rhel6 ~]# cat /var/lib/mysql/rhel6.pid
6754
[iyunv@rhel6 ~]# kill `cat /var/lib/mysql/rhel6.pid `
[iyunv@rhel6 ~]# ps -ef |grep mysql
root      6859  6830  0 21:48 pts/3    00:00:00 grep mysql

2、使用--skip-grant-tables选项重启MySQL服务
    --skip-grant-tables意思是启动MySQL服务时跳过权限表认证。启动后，连接到MySQL的root将不需要口令。

[iyunv@rhel6 bin]# ./mysqld_safe --skip_grant-tables --user=mysql &
[1] 6900
[iyunv@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
[iyunv@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
[iyunv@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字段后更改密码成功。

4、刷新权限表

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

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

[iyunv@rhel6 bin]# mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[iyunv@rhel6 bin]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
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>

参考：《深入浅出MySQL》