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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值