mysql - MySQL错误1045（28000）：用户'bill'@'localhost'的访问被拒绝（使用密码：是）(MySQL ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES))

Question

First let me mention that I've gone through many suggested questions and found no relevent answer.

(首先，我要提一提，我经历了许多建议的问题，没有找到任何相关的答案。)

Here is what I'm doing.

(这是我在做什么。)

I'm connected to my Amazon EC2 instance.

(我已连接到我的Amazon EC2实例。)

I can login with MySQL root with this command:

(我可以使用以下命令以MySQL root登录：)

mysql -u root -p

Then I created a new user bill with host %

(然后我用主机％创建了一个新的用户账单)

CREATE USER 'bill'@'%' IDENTIFIED BY 'passpass';

Granted all the privileges to user bill:

(授予用户帐单所有特权：)

grant all privileges on *.* to 'bill'@'%' with grant option;

Then I exit from root user and try to login with bill:

(然后，我从root用户退出并尝试使用bill登录：)

mysql -u bill -p

entered the correct password and got this error:

(输入正确的密码并收到此错误：)

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

(错误1045（28000）：用户'bill'@'localhost'的访问被拒绝（使用密码：是）)

  ask by Ali translate from so

Answer 1

You probably have an anonymous user ''@'localhost' or ''@'127.0.0.1' .

(您可能有一个匿名用户''@'localhost'或''@'127.0.0.1' 。)

As per the manual :

(按照手册 ：)

When multiple matches are possible, the server must determine which of them to use.

(当可能有多个匹配项时，服务器必须确定要使用哪个匹配项。)

It resolves this issue as follows: (...)

(它可以解决此问题，如下所示：（...）)

• When a client attempts to connect, the server looks through the rows [of table mysql.user] in sorted order.

  (当客户端尝试连接时，服务器将按排序顺序浏览[表mysql.user]的行。)

• 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 .

(（...）服务器使用排序规则，该规则首先对具有最特定Host值的行进行排序。)

Literal host names [such as 'localhost'] and IP addresses are the most specific.

(文字主机名[例如'localhost']和IP地址是最具体的。)

Hence, such an anonymous user would "mask" any other user like '[any_username]'@'%' when connecting from localhost .

(因此，当从localhost连接时，这样的匿名用户将“屏蔽”任何其他用户，例如'[any_username]'@'%' 。)

'bill'@'localhost' does match 'bill'@'%' , but would match (eg) ''@'localhost' beforehands.

('bill'@'localhost'确实与'bill'@'%'匹配，但事先会与（例如） ''@'localhost'匹配。)

The recommended solution is to drop this anonymous user (this is usually a good thing to do anyways).

(推荐的解决方案是删除该匿名用户（无论如何通常这样做是一件好事）。)

---

Below edits are mostly irrelevant to the main question.

(下面的编辑与主要问题无关。)

These are only meant to answer some questions raised in other comments within this thread.

(这些仅用于回答此主题中其他注释中提出的一些问题。)

Edit 1

(编辑1)

Authenticating as 'bill'@'%' through a socket.

(通过套接字认证为'bill'@'%' 。)

root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass --socket=/tmp/mysql-5.5.sock
    Welcome to the MySQL monitor (...)

    mysql> SELECT user, host FROM mysql.user;
    +------+-----------+
    | user | host      |
    +------+-----------+
    | bill | %         |
    | root | 127.0.0.1 |
    | root | ::1       |
    | root | localhost |
    +------+-----------+
    4 rows in set (0.00 sec)

    mysql> SELECT USER(), CURRENT_USER();
    +----------------+----------------+
    | USER()         | CURRENT_USER() |
    +----------------+----------------+
    | bill@localhost | bill@%         |
    +----------------+----------------+
    1 row in set (0.02 sec)

    mysql> SHOW VARIABLES LIKE 'skip_networking';
    +-----------------+-------+
    | Variable_name   | Value |
    +-----------------+-------+
    | skip_networking | ON    |
    +-----------------+-------+
    1 row in set (0.00 sec)

Edit 2

(编辑2)

Exact same setup, except I re-activated networking, and I now create an anonymous user ''@'localhost' .

(完全相同的设置，除了我重新激活了网络外，现在我创建了一个匿名用户''@'localhost' 。)

root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql
    Welcome to the MySQL monitor (...)

    mysql> CREATE USER ''@'localhost' IDENTIFIED BY 'anotherpass';
    Query OK, 0 rows affected (0.00 sec)

    mysql> Bye

    root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass 
        --socket=/tmp/mysql-5.5.sock
    ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)
    root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass 
        -h127.0.0.1 --protocol=TCP
    ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)
    root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass 
        -hlocalhost --protocol=TCP
    ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)

Edit 3

(编辑3)

Same situation as in edit 2, now providing the anonymous user's password.

(与编辑2中的情况相同，现在提供了匿名用户的密码。)

root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -panotherpass -hlocalhost
    Welcome to the MySQL monitor (...)

    mysql> SELECT USER(), CURRENT_USER();
    +----------------+----------------+
    | USER()         | CURRENT_USER() |
    +----------------+----------------+
    | bill@localhost | @localhost     |
    +----------------+----------------+
    1 row in set (0.01 sec)

Conclusion 1, from edit 1: One can authenticate as 'bill'@'%' through a socket.

(结论1，来自编辑1：一个人可以通过套接字认证为'bill'@'%' 。)

Conclusion 2, from edit 2: Whether one connects through TCP or through a socket has no impact on the authentication process (except one cannot connect as anyone else but 'something'@'localhost' through a socket, obviously).

(结论2，来自编辑2：无论是通过TCP连接还是通过套接字连接都不会对身份验证过程产生任何影响（除了一个人不能通过套接字连接之外，只能通过套接字连接'something'@'localhost' ）。)

Conclusion 3, from edit 3: Although I specified -ubill , I have been granted access as an anonymous user.

(结论3，来自编辑3：尽管我指定了-ubill ，但已授予我匿名用户访问权限。)

This is because of the "sorting rules" advised above.

(这是由于上面建议的“排序规则”。)

Notice that in most default installations, a no-password, anonymous user exists (and should be secured/removed).

(请注意，在大多数默认安装中， 存在无密码的匿名用户 （并且应予以保护/删除）。)