MySQL: Access denied for user 'test'@'localhost' (using password: YES) except root user
Troubleshooting 'Access denied for user 'test'@'localhost'' in MySQL

Learn to diagnose and resolve the common MySQL error 1045 when a non-root user is denied access, even with a password.
The Access denied for user 'test'@'localhost' (using password: YES)
error (MySQL Error 1045) is a frequent hurdle for developers and administrators working with MySQL databases. This error indicates that the MySQL server rejected a connection attempt for a specific user from a specific host, despite a password being provided. While it might seem straightforward, the root cause can range from incorrect credentials to misconfigured user permissions or even network issues. This article will guide you through the common reasons for this error and provide systematic solutions to get your 'test' user (or any other non-root user) connected successfully.
Understanding the MySQL Access Denied Error
When MySQL reports Access denied for user 'username'@'hostname' (using password: YES)
, it means the server received a connection request with a username and password, but the authentication failed. The (using password: YES)
part confirms that a password was indeed supplied, ruling out a missing password as the immediate cause. The key components of this error message are:
username
: The MySQL user account attempting to connect (e.g., 'test').hostname
: The host from which the connection attempt originated (e.g., 'localhost'). This is crucial because MySQL user accounts are defined by both username and host.
This error typically points to one of the following issues:
- Incorrect Password: The most common reason. The password provided does not match the one stored for the user.
- Incorrect Username: The user 'test' might not exist, or you might be trying to connect with a different username.
- Incorrect Host: The user 'test'@'localhost' exists, but you're trying to connect from a different host (e.g., 'test'@'192.168.1.100'), and no corresponding user exists for that host.
- Missing or Insufficient Privileges: Even if the user exists and the password is correct, they might not have the necessary
GRANT
privileges to access the database or perform the desired operations. - Authentication Plugin Mismatch: In newer MySQL versions (8.0+), the default authentication plugin
caching_sha2_password
might not be compatible with older client libraries or applications. Themysql_native_password
plugin is often used for compatibility. - Firewall or Network Issues: Less common for 'localhost', but possible if the MySQL server isn't listening on the expected interface or a firewall is blocking connections (more relevant for remote connections).
flowchart TD A[Connection Attempt: 'test'@'localhost'] --> B{MySQL Server Receives Request} B --> C{User 'test'@'localhost' Exists?} C -- No --> D[Error: Access Denied (User/Host Mismatch)] C -- Yes --> E{Password Matches?} E -- No --> F[Error: Access Denied (Incorrect Password)] E -- Yes --> G{Authentication Plugin Compatible?} G -- No --> H[Error: Access Denied (Plugin Mismatch)] G -- Yes --> I{User 'test'@'localhost' Has Privileges?} I -- No --> J[Error: Access Denied (Insufficient Privileges)] I -- Yes --> K[Connection Successful!]
Flowchart of MySQL User Authentication Process
Diagnosing and Resolving the Error
To effectively troubleshoot this error, you need to systematically check each potential cause. The following steps will guide you through the process, starting with the most common issues.
1. Step 1: Verify User Credentials and Host
The first step is to confirm that the user 'test'@'localhost' actually exists and that you are using the correct password. Connect to MySQL as the root
user (or another user with sufficient privileges) and check the user table.
2. Step 2: Check User Existence and Host
Log in as root
and query the mysql.user
table to see if the user test
exists for localhost
and what its authentication plugin is. The Host
column is critical.
SELECT user, host, plugin FROM mysql.user WHERE user = 'test';
-- Expected output if user exists:
-- +------+-----------+-----------------------+
-- | user | host | plugin |
-- +------+-----------+-----------------------+
-- | test | localhost | caching_sha2_password |
-- +------+-----------+-----------------------+
Querying the mysql.user table for the 'test' user
If the query returns an empty set, the user test
does not exist for localhost
. You'll need to create it. If it exists but the host
is different (e.g., %
for any host, or a specific IP address), you might be connecting from the wrong host, or the user needs to be created for localhost
.
1. Step 3: Reset or Set User Password
If the user exists but you suspect an incorrect password, or if you just created the user, you can reset its password. This is also the step where you can change the authentication plugin if needed.
2. Step 4: Grant Privileges
Even with the correct username and password, the user might not have the necessary permissions to access databases. Grant appropriate privileges to the user. Replace your_database
with the actual database name.
-- To create a new user 'test' with password 'new_password' for localhost:
CREATE USER 'test'@'localhost' IDENTIFIED BY 'new_password';
-- To change password for an existing user 'test'@'localhost':
ALTER USER 'test'@'localhost' IDENTIFIED BY 'new_password';
-- To change password and authentication plugin (for compatibility):
ALTER USER 'test'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';
FLUSH PRIVILEGES;
Creating or resetting password for 'test'@'localhost'
-- Grant all privileges on 'your_database' to 'test'@'localhost':
GRANT ALL PRIVILEGES ON your_database.* TO 'test'@'localhost';
-- Grant specific privileges (e.g., SELECT, INSERT, UPDATE, DELETE):
GRANT SELECT, INSERT, UPDATE, DELETE ON your_database.* TO 'test'@'localhost';
-- Grant privileges on all databases (use with caution):
GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost';
FLUSH PRIVILEGES;
Granting privileges to 'test'@'localhost'
FLUSH PRIVILEGES;
after making changes to user accounts or permissions to ensure the MySQL server reloads the grant tables and applies your changes immediately.Authentication Plugin Compatibility
MySQL 8.0 and later versions use caching_sha2_password
as the default authentication plugin, which offers stronger security. However, older client libraries (like those used by some PHP versions, Java connectors, or older MySQL client tools) might not support this plugin and expect mysql_native_password
. If you've confirmed the username, host, and password are correct, but still face the error, an authentication plugin mismatch is a likely culprit.
You can change the default authentication plugin for new users by modifying the my.cnf
(or my.ini
) configuration file, or by altering existing users as shown in the code example above. For example, to set mysql_native_password
as the default for new users, add the following to your MySQL configuration file under the [mysqld]
section:
[mysqld]
default_authentication_plugin=mysql_native_password
Setting default authentication plugin in MySQL configuration
mysql_native_password
reduces security. It's recommended to update your client libraries to support caching_sha2_password
if possible, or only use mysql_native_password
for specific users that require it.Final Checks and Best Practices
After attempting the solutions above, try connecting again. If the issue persists, consider these additional checks:
- Check MySQL Server Status: Ensure the MySQL server is running correctly.
- Review MySQL Error Logs: The MySQL error log (usually found in
/var/log/mysql/error.log
on Linux or in the MySQL data directory on Windows) can provide more detailed information about connection failures. - Application Configuration: Double-check the connection string in your application code. Typos in the username, password, or host are common.
- Firewall: While less common for
localhost
, ensure no local firewall rules are blocking connections to MySQL's default port (3306).
By systematically working through these steps, you should be able to identify and resolve the Access denied
error for your MySQL users.