Unable to access MySQL, error code 1045 (28000): Access is denied for user 'bill'@'localhost' (using password: yes)

Question

Let me first mention that I have looked at many of the suggested questions and found no relevant answers. Here's what I'm doing.

I'm connected to my Amazon EC2 instance. I can log into the MySQL root user using the following command:

mysql -u root -p

Then I created a new user bill with the host %

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

Grant all permissions to user bill:

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

Then I exit the root user and try to log in using the bill user:

mysql -u bill -p

After entering the correct password, the following error occurs:

Error 1045 (28000): Access denied for user "bill"@"localhost" (using password: YES)

Answer 1

try:

~$ mysql -u root -p
输入密码：

mysql> grant all privileges on *.* to bill@localhost identified by 'pass' with grant option;

Answer 2

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

According to Manual:

Therefore, such an anonymous user will "block" any other user such as '[any_username]'@'%' when connecting from localhost.

'bill'@'localhost' matches 'bill'@'%', but will be matched before (for example) ''@'localhost'.

The recommended solution is to delete this anonymous user (this is usually a good idea).

---

The edits below are mostly irrelevant to the main question. These are just to answer some of the questions asked in other comments in this thread.

Edit 1

Authenticate via socket as 'bill'@'%'.

    root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass --socket=/tmp/mysql-5.5.sock
    欢迎使用 MySQL monitor（...）
    
    mysql> SELECT user, host FROM mysql.user;
    +------+-----------+
    | user | host      |
    +------+-----------+
    | bill | %         |
    | root | 127.0.0.1 |
    | root | ::1       |
    | root | localhost |
    +------+-----------+
    共有 4 行（0.00 秒）
    
    mysql> SELECT USER(), CURRENT_USER();
    +----------------+----------------+
    | USER()         | CURRENT_USER() |
    +----------------+----------------+
    | bill@localhost | bill@%         |
    +----------------+----------------+
    共有 1 行（0.02 秒）
    
    mysql> SHOW VARIABLES LIKE 'skip_networking';
    +-----------------+-------+
    | Variable_name   | Value |
    +-----------------+-------+
    | skip_networking | ON    |
    +-----------------+-------+
    共有 1 行（0.00 秒）
    

Edit 2

Exactly the same setup, except I reactivated the network and now created an anonymous user ''@'localhost'.

    root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql
    欢迎使用 MySQL monitor（...）
    
    mysql> CREATE USER ''@'localhost' IDENTIFIED BY 'anotherpass';
    查询 OK，0 行受影响（0.00 秒）
    
    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

Same situation as in edit 2, now provide the password for the anonymous user.

    root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -panotherpass -hlocalhost
    欢迎使用 MySQL monitor（...）

    mysql> SELECT USER(), CURRENT_USER();
    +----------------+----------------+
    | USER()         | CURRENT_USER() |
    +----------------+----------------+
    | bill@localhost | @localhost     |
    +----------------+----------------+
    共有 1 行（0.01 秒）

Conclusion 1, from edit 1: Authentication of 'bill'@'%' is possible over sockets.

Conclusion 2, from edit 2: Whether connecting via TCP or via sockets has no impact on the authentication process (other than not being able to connect via sockets other than 'something'@'localhost' any other user).

Conclusion 3, from edit 3: Even though I specified -ubill, I was granted access as an anonymous user. This is because of the "ordering rules" suggested above. Note that in most default installations, there is an anonymous user with no password (which should be secured/removed).