Centos中mysql8忘记密码的操作步骤

场景：许久不用的虚拟机，今日打开后发现mysql数据库的密码忘记了，遂整理一份操作笔记，也许下次能用着~

方法：在 skip-grant-tables 模式下启动mysql，该模式下启动mysql时不启动授权表功能，可以直接免密登录；

步骤：

1、编辑/etc/my.cnf文件，在末尾出增加 skip-grant-tables

2、 重启mysql服务

systemctl restart mysqld

3、登录mysql，并重置root密码为空

1）、登录mysql

mysql -uroot

2）、修改root密码为空

user mysql;

update user set authentication_string='' where User='root';

3）、刷新权限并退出

flush privileges;

quit;

[root@localhost /]# systemctl restart mysqld
[root@localhost /]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> 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 authentication_string='' where User='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

4、将 /etc/my.cnf 中 的 skip-grant-tables 去掉；

5、再次重启mysql服务

6、再次登录mysql，登录密码为空，然后修改root密码

[root@localhost /]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> 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> ALTER user 'root'@'%' IDENTIFIED WITH mysql_native_password BY '你的密码';
Query OK, 0 rows affected (0.03 sec)

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

use mysql;
ALTER user 'root'@'%' IDENTIFIED WITH mysql_native_password BY '你的密码';
flush privileges;