If you’re certain the password is correct, it is most likely that MySQL is denying you access based on the specified
host
value for the user. In this article we will learn how to change a user’s host value in command line or phpMyAdmin.
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 | % |
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:
+------------------+-------------+
| User | Host |
+------------------+-------------+
| test-user1 | localhost |
| test-user3 | 10.0.53.185 |
| test-user2 | % |
+------------------+-------------+
3 rows in set (0.00 sec)
