linux(centos7)下mysql8.0密码重置

安装步骤¹

下载镜像

wget http://repo.mysql.com/mysql80-community-release-el8-1.noarch.rpm

安装

rpm -ivh mysql80-community-release-el8-1.noarch.rpm
yum install mysql-server

权限设置

chown -R mysql:mysql /var/lib/mysql

初始化

mysqld --initialize

启动服务

systemctl start mysqld

启动

mysql -u root -p
Enter password:

报错

输入密码为空

ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’(using password: NO)

输入密码错误

ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’(using password: YES)

这里是刚启动的mysql应该没有设定初始密码,但你能怎么办,只能改了.

重置密码

linux下mysql配置文件my.cnf默认目录在/etc下

vim /etc/my.cnf

在my.cnf里添入一句skip-grant-tables以跳过mysql权限验证
权限验证的意思不止是密码验证,哪怕用错误密码也能够登陆,所以记得在所有步骤之后删除这句

...
[mysqld]
skip-grant-tables
...

重新启动mysql服务

systemctl restart mysqld

直接进入mysql数据库搞破坏
mysql与mysql登陆有关的数据都存在与mysql表里

mysql> use mysql

user表里存储的是用户相关信息,包括用户名,密码,权限等,在mysql8.0中字段password被authentication_string代替所以修改的应该是authentication_string

先查看一下账号密码这里的root用户密码

mysql> select user,authentication_string from user;

+------------------+------------------------------------------------------------------------+
| user             | authentication_string                                                  |
+------------------+------------------------------------------------------------------------+
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | *6BB4837EB74329105EE4568DDA7DC67ED205EE4568DDA7DC67ED2CA2AD9           |
+------------------+------------------------------------------------------------------------+

这个密码在mysql中由password()函数加密,但在当前版本中是无法调用这个函数的.

mysql> select password('123456');

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

| password('123456') |

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

| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

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

1 row in set (0.00 sec)

在当前版本的效果如下

mysql> select password('123456');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('123456')' at line 1

但是mysql中这个函数只是被隐藏不提供给用户使用,但密码加密函数没有更改.于是我们就可以使用上面函数的输出值作为root用户的authentication_string,重置密码为123456.

mysql> update user set authentication_string ='*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' where user="root";

退出删除skip-grant-tables后重启mysql服务

vim /etc/my.cnf

systemctl restart mysqld

现在mysql中root用户的密码就设置为123456了

mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26

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>