CentOS7下MySQL5.7重置root密码方法

本文主要记录MySQL5.7忘记root密码后快速重置密码的方法，步骤有点繁琐，但是经过实践确实真实可用。

1.编辑MySQL配置文件(跳过权限校验)

[root@centos ~]# vim /etc/my.cnf

在[mysqld]选项中添加skip-grant-tables属性
例：

[mysqld]
skip-grant-tables
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

注意：添加这行命令的目的就是跳过MySQL的权限校验，这样就可以以root身份空密码的形式登录MySQL服务器然后进行相关修改密码操作。但是需要注意的是执行完重置密码操作后需要还原操作并重启MySQL。

2.重启MySQL服务

[root@centos ~]# systemctl restart mysqld

3.root用户无密码登录

[root@centos ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.

这里-p不需要输入密码，然后一直Enter键就可以进入MySQL看到欢迎语

4.选择系统数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jt_cloudstitch     |
| learn_mysql_01     |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

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

5.选择用户表并修改密码

mysql> select host,user,authentication_string from user;
+----------------+---------------+-------------------------------------------+
| host           | user          | authentication_string                     |
+----------------+---------------+-------------------------------------------+
| %              | root          | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost      | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost      | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| 101.88.233.217 | root          | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| %              | jt_test       | *9F69E47E519D9CA02116BF5796684F7D0D45F8FA |
| %              | jt_test0      | *9F69E47E519D9CA02116BF5796684F7D0D45F8FA |
+----------------+---------------+-------------------------------------------+
6 rows in set (0.00 sec)

mysql> update user set authentication_string=password('123456') where user='root';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 2  Changed: 0  Warnings: 1

6.刷新系统权限

mysql> flush privileges;

7.还原my.conf配置

注意：这里一定要重做第一步并且删除skip-grant-tables值后保存重启MySQL服务

到这里root密码就已经重新设置完成了，接下来可以登录查看验证是否修改成功。

注意：MySQL的终端登录方式分为显示密码登录和隐式密码登录两种方式。其中不推荐使用显示密码登录方式，这种登录方式很容易泄露数据库密码。下面把两种登录方式都列举一下

第一种：显示登录方式

[root@centos ~]# mysql -uroot -p'xxxxxxxxxxx'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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@centos ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.19 MySQL Community Server (GPL)

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

两种最主要的区别是否把密码显示到终端