解决MySQL、MariaDB新建用户后无法登录问题

通过 grant all on jpress.* to jpress@'192.168.31.%' identified by '123456';创建用户并授权登录

后发现不管在本地还是远程都死活登录不上。

报错信息

ERROR 1045 (28000): Access denied for user 'jpress'@'localhost' (using password: YES)

查看用户权限

MariaDB [(none)]>  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
MariaDB [mysql]> select host,user,password from user;
+--------------+--------+-------------------------------------------+
| host         | user   | password                                  |
+--------------+--------+-------------------------------------------+
| localhost    | root   | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| gsg50        | root   |                                           |
| 127.0.0.1    | root   |                                           |
| ::1          | root   |                                           |
| localhost    |        |                                           |
| gsg50        |        |                                           |
| 192.168.31.% | jpress | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| %            | jpress | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+--------------+--------+-------------------------------------------+


update user set host = '%' where user = 'jpress';

发现已经允许了登录

修改密码

MariaDB [mysql]> update user set password=password("123456") where user="jpress";
Query OK, 0 rows affected (0.00 sec)

排除了密码错误

最后找到原因了：MySQL中默认存在一个用户名为空的账户，只要在本地，可以不用输入账号密码即可登录到MySQL中。而因为这个账户的存在，导致了使用密码登录无法正确登录。参考：https://www.shorey.cc/archives/67.html

解决思路：

删除这个用户名为空的账户即可

mysql -u root -p
#以root账户登录MySQL，需输入root密码。
use mysql;
#选择mysql库。
delete from user where User='';
#删除账号为空账户
flush privileges;
#刷新权限
exit
#退出