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

最新推荐文章于 2023-10-22 18:58:42 发布

ppdouble

最新推荐文章于 2023-10-22 18:58:42 发布

阅读量1.1k

点赞数

分类专栏：数据库文章标签： mysql maria db

本文链接：https://blog.csdn.net/ppdouble/article/details/52802149

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

问题：ERROR 1045 (28000): Access denied for user ‘myuser’@’localhost’ (using password: YES)

我的mysql里的信息：

MariaDB [(none)]> select Host, User, Password from mysql.user;

| Host         | User       | Password   |
|--------------|------------|------------|
| localhost    | root       | myrootpass |
| myhost       | root       | myrootpass |
| 127.0.0.1    | root       |            |
| ::1          | root       |            |
| localhost    |            |            |
| myhost       |            |            |
| %            | myauser    |            |
| %            | myuser     | mypass     |
| localhost    | anotheruser| mypass     |
10 rows in set (0.00 sec)

% 在这里是允许此用户名在任何主机访问 mysql

在网络中的其它机器使用密码myuser远程访问mysql ，成功访问。在服务器本地用密码访问的时候，出现错误：

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

在本地不使用密码的时候，可以成功访问，但是查看当前用户：

MariaDB [(none)]> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| @localhost     |
+----------------+
1 row in set (0.00 sec)

这是因为mysql在对mysql.user表进行匹配之前，先在内存进行排序，主机优先匹配，上面 localhost 优先 % 匹配，所以服务器上不用密码被匹配到''@localhost。

When multiple matches are possible, the server must determine which of them to use. It resolves this issue as follows:

Whenever the server reads the user table into memory, it sorts the rows.
When a client attempts to connect, the server looks through the rows in sorted order.
The server uses the first row that matches the client host name and user name.
The server uses sorting rules that order rows with the most-specific Host values first. Literal host names and IP addresses are the most specific.

排序详情：
示例1：

+-----------+----------+-
| Host      | User     | ...
+-----------+----------+-
| %         | root     | ...
| %         | jeffrey  | ...
| localhost | root     | ...
| localhost |          | ...
+-----------+----------+-

排序后：

+-----------+----------+-
| Host      | User     | ...
+-----------+----------+-
| localhost | root     | ...
| localhost |          | ...
| %         | jeffrey  | ...
| %         | root     | ...
+-----------+----------+-

从 localhost 发出的连接，两项被匹配到:'localhost'，'' 和'%' ，'jeffrey'。前者被优先使用，也即按前者进行连接。当我通过localhost myuser mypass 连接时被匹配到密码为空的匿名用户 localhost ''，所以会报错，而在远程访问的时候是直接用ip访问就可以正确匹配到myip myuser mypass 所在项，成功访问。

示例2：

+----------------+----------+-
| Host           | User     | ...
+----------------+----------+-
| %              | jeffrey  | ...
| thomas.loc.gov |          | ...
+----------------+----------+-

排序后：

+----------------+----------+-
| Host           | User     | ...
+----------------+----------+-
| thomas.loc.gov |          | ...
| %              | jeffrey  | ...
+----------------+----------+-

thomas.loc.gov 优先匹配

解决方法：
可以删除匿名用户
DROP USER ''@'localhost';