mysql8 忘记密码看这篇就够了

1、关闭mysql登录验证

停止mysql

$ systemctl stop mysqld.service

修改/etc/my.cnf,跳过权限验证

在my.cnf 下面添加

skip-grant-tables

启动mysql

$ systemctl start mysqld.service

2、重置mysql密码

使用下面命令登录，无密码进入mysql命令行 “`shell $ mysql -u root “`

重置root密码为空

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

查看root密码是否为空

select user, authentication_string from mysql.user;

保存修改

flush privileges;

示例如下：

$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 7
Server version: 8.0.32 Source distribution

Copyright (c) 2000, 2023, 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.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select user, authentication_string from mysql.user;
+------------------+------------------------------------------------------------------------+
| user             | authentication_string                                                  |
+------------------+------------------------------------------------------------------------+
| root             |                                                                        |
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
+------------------+------------------------------------------------------------------------+
6 rows in set (0.01 sec)

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

mysql> q
Bye

3、开启mysql登录验证

停止mysql

$ systemctl stop mysqld.service

修改/etc/my.cnf,把my.cnf 下面添加的skip-grant-tables删除

启动mysql

$ systemctl start mysqld.service

4、修改mysql密码

使用下面命令登录，在输入密码的那一步直接回车

$ mysql -uroot -p

修改root密码
‘root’@‘%’: root可以允许任务机器连接

alter user 'root'@'%' identified by '你的密码';

查看root密码

select user, authentication_string from mysql.user;

保存修改

flush privileges;

示例如下：

$ mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 8
Server version: 8.0.32 Source distribution

Copyright (c) 2000, 2023, 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 by 'mysql.root_2023';
Query OK, 0 rows affected (0.00 sec)

mysql> select user, authentication_string from mysql.user;
+------------------+------------------------------------------------------------------------+
| user             | authentication_string                                                  |
+------------------+------------------------------------------------------------------------+
| root             | *A780CA81542274F7A6F52BBC40B7B2E2F9BE8A0F                              |
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
+------------------+------------------------------------------------------------------------+
6 rows in set (0.01 sec)

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

mysql> q
Bye