首页 » MySQL » 正文

MySQL 8.0 更改密码不生效的问题

名称版本
操作系统ubuntu 20.04
MySQL8.0.21

故障描述:使用apt install mysql-server 安装MySQL数据库后,直接空密码登陆MySQL,使用如下方式更改MySQL密码后不生效

grant all privileges on *.* to root@localhost identified by '123456' with grant option;
#提示以下错误
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 'identified by '123456' with grant option' at line 1

#原因:MySQL8.0开始将授权和创建用户分开了,分开执行即可
create user test@localhost identified by '123456';
grant all privileges on *.* to test@localhost;
flush privileges;

#修改root密码
alter user root@localhost identified by '123456';
flush privileges;

#执行完毕之后退出重新登陆发现root用户还是空密码登陆,尝试使用mysql_secure_installation命令初始化MySQL数据库修改密码,结果依旧失败。
#登陆MySQL数据库查看用户信息
mysql> select user,host,authentication_string from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| debian-sys-maint | localhost | $A$005$/K,'0zQ^Io<Oq
:!zSEzVvzwSw4W923U3cUDNiSma2mNHwP2UAaVS6gIZx6 |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost |                                                                        |
| test             | localhost | $A$005$\{    _/Ro%*cN~2E/Yl2njGP2IzXii5SsLzucmXfPE3QCiuIIchF1JULe0kJ. |
+------------------+-----------+------------------------------------------------------------------------+
6 rows in set (0.00 sec)

#发现MySQL用户为空密码,创建的用户没有任何问题,猜测应该是root用户密码策略发生了变更,经过搜索查到以下方法可以修改密码
#参考链接:https://www.cnblogs.com/hellozg/p/9333052.html

mysql>  ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.02 sec)

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

#再次查看用户信息发现数据库密码已经被成功更改
mysql> select user,host,authentication_string from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| debian-sys-maint | localhost | $A$005$/K,'0zQ^Io<Oq
:!zSEzVvzwSw4W923U3cUDNiSma2mNHwP2UAaVS6gIZx6 |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9                              |
| test             | localhost | $A$005$\{    _/Ro%*cN~2E/Yl2njGP2IzXii5SsLzucmXfPE3QCiuIIchF1JULe0kJ. |
+------------------+-----------+------------------------------------------------------------------------+
6 rows in set (0.01 sec)

#再次尝试执行以下命令发现以前的方式又能更改密码了
alter user root@localhost identified by '123123123';
flush privileges;

查看官方文档:https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html

发现密码修改方式并未发生变更,那么问题是什么原因导致root密码一直无法修改呢?经过资料查询,mysql_native_password 是MySQL 8.0之前的加密方式,默认情况下很多旧的MySQL数据库连接工具或者使用MySQL的应用都会连接失败,这个时候才会采用以下命令去修改MySQL加密方式

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

#查看MySQL 8.0的默认加密方式
mysql> select host,user,authentication_string,plugin from mysql.user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| localhost | debian-sys-maint | $A$005$/K,'0zQ^Io<Oq
:!zSEzVvzwSw4W923U3cUDNiSma2mNHwP2UAaVS6gIZx6 | caching_sha2_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 |
| localhost | root             | *FD571203974BA9AFE270FE62151AE967ECA5E0AA                              | mysql_native_password |
| localhost | test             | $A$005$\{    _/Ro%*cN~2E/Yl2njGP2IzXii5SsLzucmXfPE3QCiuIIchF1JULe0kJ. | caching_sha2_password,|
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
6 rows in set (0.00 sec)

#发现使用的是caching_sha2_password,从官方文档中也未查找到相应的更改,经过测试发现,在修改root密码的时候,指定加密方式初次修改之后便可修改成功。
#卸载MySQL并删除数据之后重新安装MySQL进行测试,使用空密码登陆MySQL之后查看用户信息

mysql> select host,user,authentication_string,plugin from mysql.user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| localhost | debian-sys-maint | $A$005$=i+7s{lrplax>"%OXp8Gtcw849CBBse1m.kxtD/2Uq8J4LMgOSFJXRvfAC | caching_sha2_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 |
| localhost | root             |                                                                        | auth_socket           |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
5 rows in set (0.00 sec)

#发现root用户使用的是auth_socket的加密方式,auth_socket表示的是不关心且不需要密码,如果需要使用密码需要在修改密码的时候指定密码加密方式,关于auth_socket的参考链接https://blog.csdn.net/weixin_41918841/article/details/82997651,但是实际上在此场景中,MySQL并没有任何报错信息。

经过测试,Ubuntu 20.04中使用自带的源安装MySQL 8.0之后,root用户的默认加密方式为auth_socket,而在CentOS 8.0中使用dnf安装的自带的源中的MySQL使用的默认加密方式为mysql_native_password,故此次故障原因为加密方式的问题造成。

[root@test-server-8 ~]# cat /etc/centos-release
CentOS Linux release 8.2.2004 (Core)

mysql> select host,user,authentication_string,plugin from mysql.user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| 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 |
| localhost | root             |                                                                        | mysql_native_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
4 rows in set (0.00 sec)

发表评论