MySQL新建用户无法登录的问题

最新推荐文章于 2022-05-24 11:27:13 发布

原创最新推荐文章于 2022-05-24 11:27:13 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

在MySQL中，尝试创建并登录新用户时遇到密码验证问题。通过查询和修改mysql.user表，解决了无法登录新用户的诡异问题。解释了MySQL为何保留空字段用户，以及如何有效删除这些用户。

今天尝试使用MySQL，新建了一个用户runner，

[10:30:27]mysql> insert into mysql.user(host,user,password) values('%','runner',PASSWORD('runner'));
[10:30:27]Query OK, 1 row affected, 3 warnings (0.00 sec)
[10:30:27]mysql> flush privileges;

然后尝试登录：

[10:59:48][root@localhost RuleRunner]# mysql -urunner -p
[10:59:49]Enter password: 
[10:59:49]ERROR 1045 (28000): Access denied for user 'runner'@'localhost' (using password: YES)

很诡异的问题。经过多次尝试，发现无密码反倒是可以登录：

[11:06:21][root@localhost RuleRunner]# mysql -urunner
[11:06:21]Welcome to the MySQL monitor.  Commands end with ; or \g.
[11:06:21]Your MySQL connection id is 14
[11:06:21]Server version: 5.1.66 Source distribution

无语。我之前知道mysql.user表里面会保留无密码的用户，于是决定去检查一下：

[11:02:52]mysql> select user,password from mysql.user;
[11:02:52]+--------+-------------------------------------------+
[11:02:52]| user   | password                                  |
[11:02:52]+--------+-------------------------------------------+
[11:02:52]| root   | *80926D3F75126C85D893B06075303123B69AAD58 |
[11:02:52]| root   |                                           |
[11:02:52]| root   |                                           |
[11:02:52]|        |                                           |
[11:02:52]|        |                                           |
[11:02:52]| root   | *80926D3F75126C85D893B06075303123B69AAD58 |
[11:02:52]| runner | *12750620CF570966293289036B62068F8AA9A42C |
[11:02:52]| runner |                                           |
[11:02:52]| reader | *FC69E042CE30D92E2952335F690CF2345C812E36 |
[11:02:52]| reader |                                           |
[11:02:52]+--------+-------------------------------------------+

网上有帖子简易删除所有用户字段为空的用户，我尝试了一下，发现没用。实际有效的方法是删除所有password为空的用户：

[11:08:35]mysql> delete from mysql.user where password='';
[11:08:35]Query OK, 4 rows affected (0.00 sec)

删除后的结果是这样的：

[11:08:38]mysql> select user,password from mysql.user;
[11:08:38]+--------+-------------------------------------------+
[11:08:38]| user   | password                                  |
[11:08:38]+--------+-------------------------------------------+
[11:08:38]| root   | *80926D3F75126C85D893B06075303123B69AAD58 |
[11:08:38]| root   | *80926D3F75126C85D893B06075303123B69AAD58 |
[11:08:38]| runner | *12750620CF570966293289036B62068F8AA9A42C |
[11:08:38]| reader | *FC69E042CE30D92E2952335F690CF2345C812E36 |
[11:08:38]+--------+-------------------------------------------+

然后，再次尝试以runner用户登录：

[11:09:37][root@localhost RuleRunner]# mysql -urunner -p
[11:09:39]Enter password: 
[11:09:39]Welcome to the MySQL monitor.  Commands end with ; or \g.
[11:09:39]Your MySQL connection id is 24
[11:09:39]Server version: 5.1.66 Source distribution

至此，问题解决。

但是，为什么MySQL会保留空字段的用户呢？为何会同时插入无密码的新用户？这些问题还不理解。求高人指点。