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

问题: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';

  1. http://stackoverflow.com/questions/8348506/grant-remote-access-of-mysql-database-from-any-ip-address

  2. http://stackoverflow.com/questions/8348506/grant-remote-access-of-mysql-database-from-any-ip-address

  3. http://dev.mysql.com/doc/refman/5.7/en/default-privileges.html

  4. http://stackoverflow.com/questions/10299148/mysql-error-1045-28000-access-denied-for-user-billlocalhost-using-passw/11216911#11216911

  5. http://dev.mysql.com/doc/refman/5.5/en/connection-access.html

阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/ppdouble/article/details/52802149
文章标签: mysql maria db
个人分类: 数据库
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭