CentOS7下MySQL5.7重置root密码方法-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

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

简介: 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>

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享: