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>
