MySQL 1045 Access Denied — Fix User, Host, Plugin

ERROR 1045 (28000): Access denied for user 'shop'@'10.0.4.18' (using password: YES). If you've run a MySQL-backed service for any length of time, you've stared at this line at the worst possible moment — right after a deploy, a password rotation, or a replica promotion. The database is up. The network is fine. Yet your app can't get in, and every retry burns another failed-login entry in the log.

The frustration is that 1045 is a bouncer, not a diagnostician. It won't tell you whether the username is wrong, the host part doesn't match, the password changed, or the authentication plugin disagrees with your client library. You have to interrogate each of those yourself, in the right order.

This guide gives you that order. You'll learn how MySQL matches 'user'@'host' rows, how to spot a caching_sha2_password versus mysql_native_password mismatch in one query, when FLUSH PRIVILEGES matters, and the exact GRANT syntax that creates least-privilege accounts. By the end, a 1045 will take you five minutes instead of fifty.

Read the Error Line Like a Log, Not a Complaint

Every 1045 line carries three facts if you slow down and parse it. The quoted pair 'shop'@'10.0.4.18' is the identity the server evaluated — username first, then the host as the server resolved it. The error code (28000) is the SQLSTATE for invalid authorization, useful when grepping logs across mixed database fleets. And the parenthetical is the password hint: (using password: YES) means a credential was offered and rejected, while (using password: NO) means the client sent nothing, which almost always means an empty secret, an unset env var, or a DSN that dropped the password field.

Your first two queries should come from a privileged account and should answer: which rows exist for this user, and which identity does a real connection actually match? SELECT user, host, plugin FROM mysql.user WHERE user='shop' lists every candidate row plus the plugin each one speaks. SELECT USER(), CURRENT_USER() shows the requested identity versus the row the server matched — when those differ, a shadowing row (often anonymous) is eating your login. Run both before you change anything, because guessing between wrong-password and wrong-row wastes the most time in these incidents. Keep both outputs in the ticket — the row list and the identity pair — since every escalation starts by asking for exactly those two.

-- Which account rows exist for this user (host + plugin matter)
SELECT user, host, plugin FROM mysql.user WHERE user = 'shop';

-- Requested identity vs the row the server actually matched
SELECT USER(), CURRENT_USER();

-- Grants for the EXACT row your app matches (host part required)
SHOW GRANTS FOR 'shop'@'10.0.4.18';

MySQL Users Are user@host Pairs, Not Bare Names

This is the mental model that fixes most 1045s permanently. MySQL never authenticates a bare 'shop' — it authenticates 'shop' coming from somewhere. 'shop'@'localhost' (socket connections), 'shop'@'10.0.4.18' (TCP from that IP), and 'shop'@'%' (any TCP host) are fully independent accounts: each has its own password hash, its own plugin, and its own grants. Creating one does nothing for the others, and rotating one leaves the others stale.

Matching follows specificity: the server sorts candidate rows and picks the most specific Host value that fits your source. That rule has two sharp edges. First, '%' does not cover socket connections — an app connecting via the Unix socket needs a 'localhost' row even if a '%' row exists. Second, leftover anonymous rows like ''@'localhost' (shipped by old installers) match broadly and get picked over your account, producing a 1045 that survives every password reset.

The fix is explicit provisioning: one CREATE USER per source your fleet uses, each with least-privilege GRANTs, and a cleanup pass that drops anonymous rows. When hosts are elastic, prefer a subnet pattern like 'shop'@'10.0.4.%' over '%' so a compromised box elsewhere can't reuse the credential.

-- One account per source your fleet actually uses
CREATE USER 'shop'@'10.0.4.18' IDENTIFIED WITH mysql_native_password BY 'change-me-strong';
CREATE USER 'shop'@'10.0.4.%' IDENTIFIED WITH mysql_native_password BY 'change-me-strong';

-- Least privilege: only what checkout needs, only on its schema
GRANT SELECT, INSERT, UPDATE ON shop.* TO 'shop'@'10.0.4.18';
GRANT SELECT, INSERT, UPDATE ON shop.* TO 'shop'@'10.0.4.%';
FLUSH PRIVILEGES;

-- Remove anonymous rows that shadow real accounts
SELECT user, host FROM mysql.user WHERE user = '';
-- DROP USER ''@'localhost';

Auth Plugin Mismatch: caching_sha2_password vs mysql_native_password

Since MySQL 8.0, new accounts default to caching_sha2_password, a stronger challenge-response handshake. The catch is purely client-side: connectors that predate support — old PHP mysqlnd, ancient MySQLdb, pinned Java drivers — can't complete that handshake, so the login dies with 1045 even though the password is right. You'll see this exactly once per stack: right after a server upgrade or when a fresh account meets a legacy app.

Diagnosis is one column: SELECT user, host, plugin FROM mysql.user WHERE user='shop' shows which handshake each row demands. Then check the client: mysql --version, the driver version in your lockfile, and a direct CLI login attempt. If the CLI from a modern client succeeds while the app fails, the app's connector is the problem, not the credential.

You have two honest fixes. The fast one is aligning the row to the client with ALTER USER ... IDENTIFIED WITH mysql_native_password BY 'secret'. The durable one is upgrading the connector so you can keep the stronger plugin. What you shouldn't do is downgrade every account server-wide because one legacy service complained — scope the exception, ticket the upgrade, and keep the default strong everywhere else.

-- Which handshake does each row demand?
SELECT user, host, plugin FROM mysql.user WHERE user = 'shop';
SHOW VARIABLES LIKE 'default_authentication_plugin';

-- Align one legacy row to its old client (scoped exception)
ALTER USER 'shop'@'10.0.4.18' IDENTIFIED WITH mysql_native_password BY 'actual-secret';
FLUSH PRIVILEGES;

-- Verify from a host using TCP, like the app does
-- (run in shell) mysql -u shop -p -h db-primary -e 'SELECT CURRENT_USER();'

FLUSH PRIVILEGES and GRANT Syntax That Actually Works

FLUSH PRIVILEGES tells a running server to reload the grant tables into memory. You need it after any direct write to mysql.user (UPDATE, INSERT) and on older versions after CREATE USER performed oddly through automation — but modern GRANT, CREATE USER, and ALTER USER statements reload automatically, so a missing flush is rarely the cause on MySQL 8 unless something touched the tables by hand. Knowing this saves you from cargo-culting FLUSH after every statement while still running it where it counts.

GRANT syntax itself causes a quieter class of 1045-adjacent pain: privileges granted to the wrong host row. GRANT SELECT ON shop. TO 'shop'@'10.0.4.18' affects only that row; the fleet's other rows stay unprivileged. Always include the host part, always name the schema (never .* for app accounts), and always verify with SHOW GRANTS FOR the exact row. The verification query is the deploy gate: if SHOW GRANTS doesn't list what the app needs on the row the app matches, the login may succeed but the first query dies — which pages you just as loudly at 2 AM.

For rotation runbooks, make the sequence atomic from the app's view: ALTER the password on every host row, FLUSH once, verify SELECT 1 from an app host, then close the ticket. Partial rotations across rows are how 'it works from staging but not prod' is born.

-- Least-privilege grant pinned to the exact row
GRANT SELECT, INSERT, UPDATE ON shop.* TO 'shop'@'10.0.4.18';
FLUSH PRIVILEGES;

-- Verify what that row can actually do
SHOW GRANTS FOR 'shop'@'10.0.4.18';

-- Rotate every host row for the user, not just one
ALTER USER 'shop'@'10.0.4.18' IDENTIFIED BY 'new-secret-1';
ALTER USER 'shop'@'10.0.4.%' IDENTIFIED BY 'new-secret-1';
FLUSH PRIVILEGES;

Test the Login Exactly the Way Your App Does

Most 1045 debugging fails on fidelity: engineers test from laptops and bastions, whose source IPs match different rows than the app fleet. Reproduce with the same protocol, host, port, and credential source the app uses. If the app connects over TCP to db-primary:3306, your test is mysql -u shop -p -h db-primary -P 3306 -e 'SELECT 1' — not a socket login from localhost, which exercises a different row entirely.

Pull the credential from the same secret store the app reads rather than retyping it; typos in hand-copied passwords have burned hours that a vault read would have avoided. Watch for DSN traps too: special characters like @, /, and # in passwords must be URL-encoded in connection strings, and some frameworks silently truncate at an unescaped delimiter — producing a deterministic 1045 that looks exactly like a wrong password.

Finally, check the server side while you test. A tail on the error log shows the source IP MySQL actually saw, which settles every 'but I created that row' argument instantly. If the log's IP and your row's host don't match, stop touching the password — you're fixing the wrong row. If the app runs in containers, exec into a running container for the test, since its source IP and DNS path can differ from the node's.

# Reproduce over TCP exactly like the app (not via socket)
mysql -u shop -p -h db-primary -P 3306 -e 'SELECT CURRENT_USER();'

# Server health independent of credentials
mysqladmin -h db-primary ping

# What source IP did the server actually see?
sudo grep 'Access denied' /var/log/mysql/error.log | tail -20

# Confirm the client speaks the server's plugin
mysql --version

Stop It Recurring: Provisioning, Rotation, and Least Privilege

Individual 1045s are debugging; repeated 1045s are a provisioning gap. The durable fix is managing database users like code: CREATE USER and GRANT statements checked into version control, applied by the same pipeline that deploys the app, with one file per service account. When the fleet's subnets change, the user file changes in the same pull request — never as a forgotten manual step.

Rotation needs the same discipline. Rotate all host rows for the account in one job, verify a real login from an app host, and keep the previous secret valid until the fleet has converged — dual-secret support or a short overlap window prevents the all-red cliff. Log failed logins into your alerting (a spike of 1045s is often the first sign of a bad deploy or a brute-force probe), and never hand the app root or SUPER: a least-privilege account that leaks can only touch its own schema.

The deploy gate below belongs in CI: it fails the pipeline when the app can't authenticate before traffic shifts. Five seconds of check saves thirty-eight minutes of incident. Wire it into the same job that migrates the schema so credentials and schema always move together. Review the user file quarterly too: stale rows for decommissioned subnets become tomorrow's 1045 during the next failover.

# Deploy gate: fail the pipeline if the app identity can't log in
set -euo pipefail
PASS="$(vault kv get -field=password secret/shop/db)"
MYSQL_PWD="$PASS" mysql -u shop -h db-primary -e 'SELECT 1;' shop
MYSQL_PWD="$PASS" mysql -u shop -h db-primary -e 'SHOW GRANTS;' | grep -q 'ON `shop`' \
  && echo GATE-PASS || (echo GATE-FAIL; exit 1)