Why Does MySQL Return Error 1045: Access Denied Even with Correct Credentials?

MySQL Error 1045: Access Denied for User 'bill'@'localhost' Despite Correct Credentials

When attempting to access a remote MySQL database through the command line, users may occasionally encounter the "Error 1045: Access denied for user 'bill'@'localhost'" message, indicating that the connection was rejected despite providing the correct login credentials.

Investigation:

The user has created a new user named 'bill', granted the user all necessary privileges, and then exited the root user account. However, subsequent attempts to log in as user 'bill' from the local machine ('localhost') result in the error.

Root Cause:

Often, this issue arises due to the presence of an anonymous user (a user with an empty username and 'localhost' as the host) in the 'mysql.user' table. When connecting from 'localhost,' the server prioritizes matching user accounts based on the most specific host name or IP address.

Solution 1: Remove Anonymous User

To resolve the issue, remove the anonymous user from the 'mysql.user' table:

DELETE FROM mysql.user WHERE user = '' AND host = 'localhost';

Solution 2 (For MySQL Versions >= 5.7):

If using MySQL version 5.7 or later, you can disable the use of anonymous accounts through the 'skip_name_resolve' variable:

SET GLOBAL skip_name_resolve = ON;

Additional Considerations:

Socket Connections:

In the provided example, the user is connecting via a socket. It is important to ensure that the 'user' and 'password' fields are properly specified, as any anonymous users configured in the login path can potentially override the provided credentials.

TCP Connections:

When connecting through TCP, the -h flag should be used to specify the hostname or IP address, as opposed to localhost:

mysql -u bill -p -hremote_hostname

Empty Usernames:

Some client libraries do not properly handle empty usernames. Instead of '', the '@' symbol should be used to denote an anonymous user:

mysql -u'@'localhost'-ppassword

The above is the detailed content of Why Does MySQL Return Error 1045: Access Denied Even with Correct Credentials?. For more information, please follow other related articles on the PHP Chinese website!