MySQL 'Access denied for user' (error 1045): find which account the server matched and fix it

#The symptom

An application or a person can't log in:

ERROR 1045 (28000): Access denied for user 'app'@'10.20.4.17' (using password: YES)

The message deliberately hides the reason. The password may be wrong, but just as often the server matched a different account than you expected, or the account exists only for a different host. The two most useful details are already in the error:

• 'app'@'10.20.4.17' is the user name and the client address as the server sees it. Behind NAT, a proxy or a load balancer, this is not the address of the app server.
• using password: YES or NO tells you whether the client sent a password at all. NO usually means an empty or missing password in the connection string or config file.

#Quick map: error to cause

#1. See what accounts exist for that user

Log in as an admin and list every row for the user name:

SELECT user, host, plugin, account_locked, password_expired,
       password_last_changed
FROM mysql.user
WHERE user IN ('app', '')
ORDER BY user, host;

Include user = '' on purpose: that's the anonymous account, a common reason a correct password is rejected (see step 2).

If there is no row whose host covers the client address from the error, that's the problem. 'app'@'10.20.%' matches 10.20.4.17; 'app'@'localhost' does not; 'app'@'appserver01' only matches if the server can resolve the name.

#2. Work out which row the server matched

MySQL sorts accounts by host from most specific to least specific (literal hosts and IPs first, % last) and uses the first row that matches the client. Only then does it check the password. So when several rows could match, a more specific row with a different password wins.

Classic case: an anonymous user ''@'localhost' exists. A local login as app matches ''@'localhost' before 'app'@'%', and your password for app is rejected. After a successful login you can confirm which account was used:

SELECT USER() AS you_asked_for, CURRENT_USER() AS account_matched;

Fix by dropping anonymous accounts:

DROP USER ''@'localhost';
DROP USER ''@'%';   -- if it exists

mysql_secure_installation removes them on self-managed installs.

#Hostnames and skip_name_resolve

SHOW VARIABLES LIKE 'skip_name_resolve';

When it's ON, accounts defined with a hostname never match; only IPs, localhost and patterns like 10.20.% work. Create the account with the IP range instead.

#localhost is not 127.0.0.1

On Linux, mysql -u app -p with no -h (or -h localhost) uses the Unix socket and matches @'localhost'. -h 127.0.0.1 uses TCP and matches @'127.0.0.1' or @'%'. If one works and the other doesn't, you have different rows for each.

#3. Fix the password or the host

Reset a password:

ALTER USER 'app'@'10.20.%' IDENTIFIED BY 'N3w-Str0ng-Secret';

Add an account for a new subnet, copying the grants from the existing one:

SHOW GRANTS FOR 'app'@'10.20.%';
CREATE USER 'app'@'10.30.%' IDENTIFIED BY 'N3w-Str0ng-Secret';
GRANT SELECT, INSERT, UPDATE, DELETE ON sales.* TO 'app'@'10.30.%';

Each user@host pair is a separate account with its own password and grants. Changing the password on one row doesn't change the others.

Warning

Avoid fixing this by creating 'app'@'%'. It lets the account log in from anywhere the network allows. Use the narrowest subnet that covers your app servers.

Then test from the client host itself, not from the database server:

mysql -h db01.internal -u app -p -e "SELECT CURRENT_USER();"

If the app reads the password from a secret store (Key Vault, Secrets Manager), confirm the app picked up the new version; many only read secrets at start-up.

#4. Plugin, TLS and lock problems

Check the plugin and TLS requirement of the account:

SELECT user, host, plugin, ssl_type FROM mysql.user WHERE user = 'app';

• mysql_native_password on 8.4: the plugin is disabled by default in 8.4 and removed in 9.x. Move the account: ALTER USER 'app'@'10.20.%' IDENTIFIED WITH caching_sha2_password BY '...';. Old client libraries may then need upgrading.
• caching_sha2_password over plain TCP: the first login after a server restart needs either TLS or RSA key exchange. A client that has neither fails. Use TLS (--ssl-mode=REQUIRED) or a connector that supports it.
• ssl_type not empty (ANY, X509, SPECIFIED): the account was created with REQUIRE SSL or similar, so non-TLS logins are rejected with 1045.
• Locked or blocked: ALTER USER 'app'@'10.20.%' ACCOUNT UNLOCK; clears both a manual lock and a FAILED_LOGIN_ATTEMPTS block. Then find out what was retrying with a bad password.

#5. Find the failures in the logs

Set error log verbosity to 3 to log failed logins with user and host:

SET PERSIST log_error_verbosity = 3;

Per-host authentication failure counters live in the host cache:

SELECT ip, host, count_authentication_errors, sum_connect_errors
FROM performance_schema.host_cache
ORDER BY count_authentication_errors DESC;

If a host is blocked by max_connect_errors (error 1129), clear it with:

TRUNCATE TABLE performance_schema.host_cache;

FLUSH HOSTS does the same but is deprecated from 8.0.23.

#Lost admin password

#Managed services

On RDS/Aurora and Azure Flexible Server, reset the admin password through the control plane:

aws rds modify-db-instance --db-instance-identifier prod-mysql \
  --master-user-password 'N3w-Str0ng-Secret' --apply-immediately

az mysql flexible-server update --resource-group rg-data --name prod-mysql \
  --admin-password 'N3w-Str0ng-Secret'

For Aurora, use aws rds modify-db-cluster --db-cluster-identifier ... with the same password flag.

#Self-managed

Use an init file. Put one line in a file only the mysql OS user can read:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'N3w-Str0ng-Secret';

Then restart with it once:

sudo systemctl stop mysqld
sudo -u mysql mysqld --init-file=/var/lib/mysql-files/reset.sql &
# after it is up, stop it and start the service normally
sudo systemctl start mysqld
sudo rm /var/lib/mysql-files/reset.sql

The service name is mysqld on RHEL-family systems and mysql on Debian/Ubuntu.

Caution

Avoid --skip-grant-tables on a server that can be reached over the network: anyone can log in as anyone while it runs. If you must use it, add --skip-networking (8.0 does this automatically) and restart normally straight afterwards.

#Prevent it next time

• Create application accounts per app and per subnet, never '%', and keep the CREATE USER and GRANT statements in source control.
• Drop anonymous accounts on every new instance as part of your build checklist.
• Move remaining mysql_native_password accounts to caching_sha2_password before upgrading to 8.4.
• Rotate passwords through the secret store and restart or reload the app in the same change.
• Alert on rising count_authentication_errors in performance_schema.host_cache or on Access denied lines in the error log.