MySQL Rejecting Correct Password “Error 1045: Access denied for user (using password: YES)”

Introduction

This article is for users who are trying to log into MySQL with a correct password but are getting an error: “Error 1045: Access denied for user (using password: YES)”

If you’ve forgotten your MySQL root password, please see this article:

• How to Reset the MySQL root password.

If you are trying to log in as root via phpMyAdmin, please see:

• Can’t log into phpMyAdmin: mysqli_real_connect(): (HY000/1698): Access denied for user ‘root’@’localhost’

Understanding MySQL Users and Hosts

MySQL restricts user access based on the host value of a user. By default, the host value for the root account and new users is set to localhost, meaning you can only log in via the local machine or via phpMyAdmin if installed on the same localhost. If you try to log into a localhost account remotely, it may reject your correct password with “Access denied for user (using password: YES)”.

If we look at this simple MySQL user list below as an example:

+------------------+-------------+
| User             | Host        |
+------------------+-------------+
| test-user1       | localhost   |
| test-user3       | 10.0.53.185 |
| test-user2       | %           |

Above we have three MySQL users with different host values. 

Let’s have a look at a few scenarios where your correct password may be rejected depending on the host value:

• User test-user1@locahost
  • ✅ Can log in from the localhost, that is, the machine on which the MySQL service is running.
  • ✅ Can log in from phpMyAdmin if installed on the same localhost.
  • ❌Cannot log in remotely and will get “Access denied for user (using password: YES)”.
• User [email protected]
  • ✅ Can log in only from the IP address 10.0.53.185.
  • ❌ Cannot log in from any other IP address “Access denied for user (using password: YES)”.
  • ❌ Cannot log in from localhost “Access denied for user (using password: YES)”.
  • ❌ Cannot log in from phpMyAdmin “Access denied for user (using password: YES)”.
• User test-user3@%
  • ✅ Can log in from anywhere (% is a wildcard).

Changing a MySQL User’s Host Value

You can change the host value for a user using the MySQL root account or any account with superuser privileges. This can be done via command line or phpMyAdmin.

Via Command Line

You will need to access your MySQL database using root or a superuser that has access to the MySQL Users table.

The root account is by default only accessible from localhost, so you may get “Access denied for user (using password: YES)” if you try and log in remotely. In such eventuality, you will need to somehow gain local access to the server.

To log into a local MySQL database:

mysql -u root -p

To log into a remote MySQL database, simply replace hostname_or_ip with your own.

mysql -u root -h hostname_or_ip -p

Once logged into MySQL, list all MySQL users and their corresponding hosts in your database.

SELECT User,Host FROM mysql.user;

Result:

+------------------+-------------+
| User             | Host        |
+------------------+-------------+
| test-user1       | localhost   |
| test-user3       | 10.0.53.185 |
| test-user2       | %           |
+------------------+-------------+
3 rows in set (0.00 sec)

Above we can see a list of MySQL users and their hosts.

If we want to log in using test-user1 remotely from anywhere, we must change the host value to the % wildcard, which will allow remote login from anywhere. For heightened security, you could change this to an IP address.

In the example below, we are changing the host for test-user1 from localhost to %

% means wildcard and will allow this user to log in remotely from anywhere.

RENAME USER 'test-user1'@'localhost' TO 'test-user1'@'%';

You must flush MySQL privileges for changes to take affect.

FLUSH PRIVILEGES;

Now try logging in again remotely.

Via phpMyAdmin

Log into phpMyAdmin using your root account or an account with superuser privileges.

1. Once logged into phpMyadmin, click the mysql database in the left-hand column.
2. Click the SQL tab.
3. Type or paste into the SQL query box: SELECT User,Host FROM mysql.user; to list all MySQL users in the database.
4. Click Go to run the query.

You should now see a list of all MySQL users and their corresponding hosts.

To change the host value of a MySQl user:

1. Double-click the host field beside the user your wish to change (in red below).
2. Type % to allow anyone to log into this user from anywhere. You could also change this to a remote IP for better security.
3. Press ENTER on your keyboard to save changes.

You’re not done yet. You must now flush MySQL privileges.

1. Click the SQL tab again
2. Type or paste into the SQL query box: FLUSH PRIVILEGES;
3. Click Go to run the query.

You should now be able to log in remotely.

Let me know if this helped. Follow me on Twitter, Facebook and YouTube, or 🍊 buy me a smoothie.