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 AmazonEC2 instance. I was able to log in to the MySQLroot user using: mysql-uroot -p Then I created a new user bill with host %CREATEUSER'bill'@'%'IDENTIFIEDBY'passpass'; granted all permissions to user bill: grantallprivilegeson*.* to

P粉186904731 · Answer

try:

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

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

P粉287345251 · Answer

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