linux mysql8忘记密码的解决方案_Mysql

1、关闭mysql登录验证

停止mysql

$ systemctl stop mysqld.service

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

在my.cnf 下面添加

skip-grant-tables

启动mysql

$ systemctl start mysqld.service

2、重置mysql密码

重置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