MySQL忘记root密码时找回密码方法

MySQL数据库忘记root用户密码时重新设置root用户密码的方法.

前提与核心:

需要有修改/etc/my.cnf文件的权限
需要有启停MySQL服务的权限

本次演示使用的MySQL版本是8.0.21, 如果是其它版本, 只需要有操作my.cnf文件和启停MySQL服务的权限, 其它操作大同小异.

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.21    |
+-----------+
1 row in set (0.00 sec)

mysql>

下面请看在没有密码的情况下重新设置root密码的过程.

Step1.修改my.cnf文件, 设置跳过密码登录

打开/etc/my.cnf文件, 在文件的[mysqld]栏目下添加跳过密码验证设置skip-grant-tables并保存.

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[mysqld]
skip-grant-tables
# 如果文件中有其它配置, 不需要管, 只需要[mysqld]下面有skip-grant-tables即可

Step2.重启MySQl服务

[root@vpn-server ~]# systemctl restart mysqld	# 重启服务命令, 某些无法使用此命令重启的, 使用其它命令重启也行
[root@vpn-server ~]# systemctl status mysqld	# 重启完成后, 查看MySQL服务状态
● mysqld.service - MySQL 8.0 database server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)
   Active: active (running) since Tue 2020-12-29 16:26:25 CST; 5s ago
  Process: 416910 ExecStopPost=/usr/libexec/mysql-wait-stop (code=exited, status=0/SUCCESS)
  Process: 417042 ExecStartPost=/usr/libexec/mysql-check-upgrade (code=exited, status=0/SUCCESS)
  Process: 416962 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mysqld.service (code=exited, status=0/SUCCESS)
  Process: 416937 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS)
 Main PID: 416999 (mysqld)
   Status: "Server is operational"
    Tasks: 38 (limit: 24005)
   Memory: 335.5M
   CGroup: /system.slice/mysqld.service
           └─416999 /usr/libexec/mysqld --basedir=/usr

Dec 29 16:26:24 vpn-server systemd[1]: mysqld.service: Succeeded.
Dec 29 16:26:24 vpn-server systemd[1]: Stopped MySQL 8.0 database server.
Dec 29 16:26:24 vpn-server systemd[1]: Starting MySQL 8.0 database server...
Dec 29 16:26:25 vpn-server systemd[1]: Started MySQL 8.0 database server.
[root@vpn-server ~]#

Step3.使用root用户无密码登录数据库

[root@vpn-server ~]# 
[root@vpn-server ~]# mysql -uroot -p
Enter password: # 此处无需输入密码, 直接enter即可
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.21 Source distribution

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

Step4.将root用户密码置空

MySQL数据库, 用户账号密码信息存储在mysql.user系统表中. 可以先查看一下用户当前密码信息.

mysql> select user, host, authentication_string from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| zhoujl           | %         | $A$005$g!KhQ;D+2 R\L!ifTlABgAHLTtjxuqQp/ytSgXq6wKtgQMb4QnurxJCL. |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost | $A$005$Sd\J*2W*`
                                                 aj4O.UaKpwamA351vi1UxG9dYvlhN9CLQLSsl2X1IjbS8 |
+------------------+-----------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)

mysql>

查询的三个字段中, user字段表示用户名, host字段表示登录的ip, host值为百分号时表示允许远程登录. authentication_string字段则为加密后的密码.

我们下面的操作就是要将user = 'root' and host = 'localhost'记录的authentication_string字段值设置为空字符串.

**对于MySQL5的版本, 在这一步直接使用更新语句将密码更新为想要的密码也可以. 更新语句如下: **

update mysql.user set authentication_string = password('明文密码') where user = 'root' and host = 'localhost';

因为MySQL8的版本取消了password()函数, 所以直接更新为想要的密码不太好处理了. 依次先将密码置空.

如果没有host = 'localhost'的记录, 但是有host = '%'的记录, 则使用host = '%'作条件进行更新.

mysql> update mysql.user set authentication_string = '' where user = 'root' and host = 'localhost';
Query OK, 1 row affected (0.17 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> 
mysql> 
mysql> 
mysql> select user, host, authentication_string from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| zhoujl           | %         | $A$005$g!KhQ;D+2 R\L!ifTlABgAHLTtjxuqQp/ytSgXq6wKtgQMb4QnurxJCL. |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost |                                                                        |
+------------------+-----------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)

mysql>

Step5.取消免密码登录设置

打开/etc/my.cnf文件, 在文件的[mysqld]栏目下将刚刚添加的跳过密码验证设置skip-grant-tables删除或注释并保存.

[root@vpn-server ~]# vi /etc/my.cnf

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[mysqld]
# skip-grant-tables

Step6.再次重启MySQL服务

重启命令见Step2

Step7.root用户使用空串密码登录数据库

[root@vpn-server ~]# mysql -uroot -p
Enter password: #因为密码是空串, 此处也是直接enter即可
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.21 Source distribution

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

Step8.重新设置root用户的登录密码

mysql> alter user 'root'@'localhost' identified with caching_sha2_password by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql>

这条sql语句中, caching_sha2_password为MySQL8版本的密码默认加密方式. 不加这个也可以, sql语句如下:

mysql> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql>

注意: 如果root用户没有host = 'localhost'的记录但有host = '%'的记录, 则需要使用下面的语句更新密码:

mysql> alter user 'root'@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql>

Step9.root用户使用新的密码登录验证修改是否生效

[root@vpn-server ~]# mysql -uroot -p123456	# 此处为了演示, 直接在命令行显示输入可密码, 实际上不建议这么使用, 建议隐式输入密码使用
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 10
Server version: 8.0.21 Source distribution

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

posted @ 2020-12-29 17:03 周建林阅读(581) 评论(0) 编辑收藏举报

刷新页面返回顶部

林家·小院

Welcome to you