mysql sys密码忘记_mysql8.0.22忘记密码和重置

步骤1、修改mysql配置文件

[root@localhost ~]# vi /etc/my.cnf

在底部添加一行

skip-grant-tables

保存退出

重启mysql

[root@localhost ~]# systemctl restart mysqld

步骤2、将root密码置空

[root@localhost ~]# mysql -uroot -p

直接回车

mysql> use mysql

mysql> select host, user, authentication_string, plugin from user;

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

mysql> \q

步骤3、改回mysql配置文件

返回步骤1，将添加的一行删或注释掉，保存退出，重启mysql.

[root@localhost ~]# vi /etc/my.cnf

#skip-grant-tables

[root@localhost ~]# systemctl restart mysqld

步骤4、修改root密码

[root@localhost ~]# mysql -uroot -p

直接回车

mysql> ALTER USER 'root'@'%' IDENTIFIED BY 'MyNewPass4!';

mysql> selecthost, user, authentication_string, plugin from user;+-----------+------------------+------------------------------------------------------------------------+-----------------------+

| host | user | authentication_string | plugin |

+-----------+------------------+------------------------------------------------------------------------+-----------------------+qH%eL3@wUvYXiOmjuqkVggHwIwYIWZA3.0kpC1HjNaGWxM7EH51cB | caching_sha2_password |

| % | userbook | *59B1E18AA534B3A2E3A33430582E9EE06AF3B729 | mysql_native_password |

| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |

| localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |

| localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |

+-----------+------------------+------------------------------------------------------------------------+-----------------------+

5 rows in set (0.00 sec)

这时会出现如下显示格式不正确的情况。

(思路：

可通过先将%设置为localhost，

再执行，

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';

将localhost设置为%，

mysql> update user set host='%' where user='root';

然后，

mysql> flush privileges;

解决。)