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

原创 2016年10月12日 23:31:25

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

版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)(YES也一样的)

#1.停止mysql数据库 /etc/init.d/mysqld stop#2.执行如下命令 mysqld_safe --user=mysql --skip-grant-tables --skip-n...

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

错误描述: Mysql中添加用户之后可能出现登录时提示ERROR 1045 (28000): Access denied for user的错误.删除user.user中值为NULL的,或更新NULL...
  • VMA_LMA
  • VMA_LMA
  • 2013年05月24日 12:16
  • 723

Ubuntu下MySQL的“ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)”

我不懂为什么我在网上找的解决方案千篇一律全是: 1、停止当前mMySQL服务 /etc/init.d/mysqld stop 或  service mysqld stop 2、用户 --skip-...

MySql数据库链接问题ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

当MySql数据库安装好后登陆发现报错ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YE...

MySql 出现ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES).问题解决方案

在登入mysql时,当输入mysql -uroot -p回车之后。   然后输入密码,但是却没有这正常的登入MySql ,却出现ERROR 1045 (28000): Access denied fo...

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)问题解决

由于/var/lib/mysql的容量有限,遂决定把数据目录移到容量较大的地方/mnt/resource目录下,以前是看着别人弄,所以感觉原理不怎么懂,现在自己弄遇到困难了。 安装方法:官网下载的...
  • mpchen
  • mpchen
  • 2017年03月29日 10:22
  • 133

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

linux版本:CentOS release 5.6   64位操作系统 mysql数据库:5.5.18  输入mysql -u root -p,然后输入密码,回车后报错如下: linux E...

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

win7 MySql5.6.17 提示:1045 access denied for user 'root'@'localhost' using password yes 从网上找到的解决方法,以...

RROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

错误描述: Mysql中添加用户之后可能出现登录时提示ERROR 1045 (28000): Access denied for user的错误.删除user.user中值为NULL的,或更新NULL...

完整过程解决 ERROR 1045 (28000): Access denied for user 'mysql'@'localhost' (using password: NO)

ERROR 1045 (28000): Access denied for user 'mysql'@'localhost' (using password: NO) 此问题网上大部分都是围绕下面的...
  • nel0511
  • nel0511
  • 2013年10月26日 10:06
  • 62964
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:ERROR 1045 (28000): Access denied for user 'myuser'@'localhost' (using password: YES)
举报原因:
原因补充:

(最多只允许输入30个字)