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

Learn mysql: access denied for user 'test'@'localhost' (using password: yes) except root user with practical examples, diagrams, and best practices. Covers mysql, mysql-error-1045 development techn...

Troubleshooting 'Access denied for user 'test'@'localhost'' in MySQL

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

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:

  1. Incorrect Password: The most common reason. The password provided does not match the one stored for the user.
  2. Incorrect Username: The user 'test' might not exist, or you might be trying to connect with a different username.
  3. 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.
  4. 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.
  5. 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. The mysql_native_password plugin is often used for compatibility.
  6. 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'

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

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.