Introduction

If root can log in but test@localhost cannot, the issue is usually not that MySQL is globally broken. It usually means the test account is missing, using the wrong password, bound to the wrong host, or missing the privileges or authentication settings that root already has.

Understand user@host in MySQL

MySQL accounts are identified by both username and host. test@localhost and [email protected] are different accounts. That is why one connection path can fail while another works.

The error message already tells you what MySQL tried to match. If it says Access denied for user 'test'@'localhost', you should inspect that exact account entry rather than a generic test user assumption.

Inspect the Existing Accounts

Start by logging in as a privileged user and looking at the current account table.

SELECT user, host, plugin
FROM mysql.user
ORDER BY user, host;

This shows whether test@localhost actually exists and which authentication plugin it uses. If the account is missing, no password will ever work. If the host differs, MySQL will not match the login request to the account you thought you created.

Create or Recreate the Correct User

If the account is missing, create it explicitly and grant the privileges it needs.

CREATE USER 'test'@'localhost' IDENTIFIED BY 'strong-secret';
GRANT ALL PRIVILEGES ON app_db.* TO 'test'@'localhost';
FLUSH PRIVILEGES;

Then test the login:

mysql -u test -p -h localhost app_db

If you connect through TCP instead of the local socket, test with 127.0.0.1 as well. That helps confirm whether the host portion of the account is the real mismatch.

Reset the Password If Needed

Sometimes the account exists, but the password is wrong or outdated.

ALTER USER 'test'@'localhost' IDENTIFIED BY 'new-secret';
FLUSH PRIVILEGES;

After that, retry the login with the new password. This is safer than guessing whether the stored password hash is still what you expect.

Check Privileges Separately from Login

Authentication and authorization are different. A user may authenticate successfully but still fail to use a database because privileges were never granted.

SHOW GRANTS FOR 'test'@'localhost';

If the account can log in but cannot use the target database, grant only the permissions that application actually needs.

GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'test'@'localhost';
FLUSH PRIVILEGES;

Avoid using root credentials in application code just because root happens to work. That turns a privilege mistake into a security problem.

Authentication Plugin Mismatches

On some MySQL setups, accounts use different authentication plugins. If a client library expects one plugin and the server account uses another, login can fail even with the correct password.

You can see the current plugin in the mysql.user query above. If you intentionally need a different plugin, update it explicitly and retest with your client stack.

Why root Still Works

The fact that root works does not prove the server is healthy for normal users. root may authenticate through a different plugin, a socket-based mechanism, or a specific local-only account entry with broad privileges. That makes root a poor comparison unless you inspect the exact account definitions.

Common Pitfalls

• Forgetting that MySQL accounts are user@host pairs is the most common source of confusion.
• Creating test@'%' and then assuming it covers every local connection path can still surprise you depending on account matching.
• Fixing the password without checking whether the account exists wastes time.
• Using root in production code because it works hides the real privilege problem.
• Changing privileges but never verifying with SHOW GRANTS makes it hard to see what actually changed.

Summary

• 'Access denied for test@localhost usually means an account, host, password, or privilege mismatch.'
• Inspect mysql.user and SHOW GRANTS before guessing.
• Create or alter the exact user@host account that the error message references.
• Test localhost and 127.0.0.1 separately when connection path matters.
• Do not treat successful root login as proof that normal accounts are configured correctly.