# How to find every user still on mysql_native_password before MySQL 8.4 or 9

> **Quick answer:** Run one query against `mysql.user` while the old server is
> still up. The break starts at MySQL 8.4, which ships `mysql_native_password`
> disabled, not at MySQL 9, which removes it. 8.4 has a switch that turns it
> back on and 9 does not, so 8.4 is a temporary bridge. The fix is
> `ALTER USER ... IDENTIFIED WITH caching_sha2_password`, one account at a time,
> with a new password each.

## What is the problem?

`mysql_native_password` is the old way MySQL stored and checked passwords. The
replacement, `caching_sha2_password`, has been the default since 8.0.

The manual gives the timeline in one sentence.

> "The `mysql_native_password` authentication plugin is deprecated as of MySQL
> 8.0.34, disabled by default in MySQL 8.4, and removed as of MySQL 9.0.0."
>
> [MySQL 8.4 manual, Native Pluggable Authentication](https://dev.mysql.com/doc/refman/8.4/en/native-pluggable-authentication.html), read 2026-09-20.

There are two boundaries. MySQL 8.4 is the first one, and the one most
estates hit first. The three states, measured on one datadir carried
forward through three containers on 2026-09-20:

| server | startup option | plugin status | can a native account log in? |
|---|---|---|---|
| MySQL 8.0.46 | none | `ACTIVE` | yes |
| MySQL 8.4.11 | `--mysql-native-password=ON` | `ACTIVE` | yes |
| MySQL 8.4.11 | none | `DISABLED` | no, `ERROR 1524` |
| MySQL 9.7.2 | none | absent from `information_schema.plugins` | no, `ERROR 1524` |

The same account, `legacy`@`%`, was created once and never touched again. Two of
those four rows use the same binary: on 8.4 this is a configuration state, not a
version.

The accounts themselves are the problem. They are not removed and
they are not migrated. On MySQL 9.7.2, started on that same 8.4 datadir, the
server came up with no error and `SELECT user, host, plugin FROM mysql.user`
still returned `legacy % mysql_native_password`. The row survives the upgrade
unchanged. The failure appears when an application cannot connect,
after an upgrade that reported success.

## The 8.4 bridge, and its deadline

If an application cannot be migrated in the same window, MySQL 8.4 can be
started with the plugin on.

```ini
[mysqld]
mysql_native_password=ON
```

That option is documented, along with its purpose.

> "The server-side plugin is built into the server, but is disabled by default.
> To enable it, start the MySQL Server with `--mysql-native-password=ON` or by
> including `mysql_native_password=ON` in the `[mysqld]` section of your MySQL
> configuration file."
>
> [MySQL 8.4 manual, Native Pluggable Authentication](https://dev.mysql.com/doc/refman/8.4/en/native-pluggable-authentication.html), read 2026-09-20.

Use it as a rollback measure with a deadline, not as a migration. The
plugin is deprecated and gone in 9.0. The option itself is gone too, so a MySQL
9.7.2 container started with it in place did not start on 2026-09-20:

```
[ERROR] [MY-000067] [Server] unknown variable 'mysql-native-password=ON'.
[ERROR] [MY-010119] [Server] Aborting
```

An operator who sets this for the 8.4 window and forgets it has turned a
deferred authentication problem into a server that will not come up.

## How do I confirm it?

This is the inventory. Run it on the server you are upgrading **from**,
while it is still running:

```sql
CREATE DATABASE IF NOT EXISTS nativeaudit;

SELECT user, host, plugin
FROM mysql.user
WHERE plugin = 'mysql_native_password'
ORDER BY user, host;
```

An empty result is the answer you want. Get it before you plan anything else.

The account is the pair, not the name. `'app'@'%'` and `'app'@'10.%'` are two
accounts, and migrating one leaves the other where it was. Both columns
are in the result for that reason.

The wider count tells you the scale rather than the names:

```sql
SELECT plugin, COUNT(*) AS accounts
FROM mysql.user
GROUP BY plugin
ORDER BY accounts DESC;
```

On a stock MySQL 8.0.46 container on 2026-09-20 that returned
`caching_sha2_password 6` and no other row, the six being the accounts the image
creates for itself. The number belongs to that fixture, not to the
version. On your own server the query shows the shape of your estate.

Check the plugin's status as well, because it tells you which of the states
above the server is in:

```sql
SELECT plugin_name, plugin_status
FROM information_schema.plugins
WHERE plugin_name LIKE '%password%';
```

On a stock MySQL 8.4.11 container on 2026-09-20 that returned
`sha256_password ACTIVE`, `caching_sha2_password ACTIVE` and
`mysql_native_password DISABLED`. On MySQL 9.7.2 the third row is not there.

One variable is easy to look for and no longer exists.
`default_authentication_plugin` was removed in 8.4.0. Measured in containers on
2026-09-20 it returns `caching_sha2_password` on MySQL 8.0.46 and `ERROR 1193,
Unknown system variable` on MySQL 8.4.11.

The manual gives the version and the replacement.

> "The `default_authentication_plugin` system variable, deprecated in MySQL
> 8.0.27, is removed as of MySQL 8.4.0. Use `authentication_policy` instead."
>
> [MySQL 8.4 manual, What Is New in MySQL 8.4 since 8.0](https://dev.mysql.com/doc/refman/8.4/en/mysql-nutshell.html), read 2026-09-20.
`authentication_policy` reads `*,,` on a default 8.4.11 install, and that does
not mean what it looks like.

The manual explains the three elements.

> "This means that factor 1 is required in account definitions and can use any
> authentication plugin (with `caching_sha2_password` being the default), and
> that factors 2 and 3 are optional and each can use any authentication plugin."
>
> [MySQL 8.4 manual, `authentication_policy`](https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html#sysvar_authentication_policy), read 2026-09-20.
Empty is optional, not forbidden. This page relies on the manual for that rather
than on a test, because the MySQL 8.4.11 container
it was measured on, on 2026-09-20, ships three authentication plugins, all three
of them password plugins, and every one of them is rejected as a second factor
with `ERROR 4052`. With no eligible plugin installed, a failure to create a second
factor here would have proved nothing about the policy.

## What breaks, and what the error looks like

On a stock 8.4 server and on 9, creating the account is refused:

<!-- sql-check: skip this cannot succeed with the plugin disabled; the error is quoted from running it on MySQL 8.4.11 -->
```sql
CREATE USER 'legacy'@'%' IDENTIFIED WITH mysql_native_password BY 'x';
```

```
ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded
```

On an 8.4 server started with `--mysql-native-password=ON` the same statement
succeeds. Measured in a MySQL 8.4.11 container on 2026-09-20 the account was
created and logged in.

Existing accounts do not produce a startup error. The server comes up, the
account is listed, and the login fails with the same `ERROR 1524` that
the `CREATE USER` gives.

### "MySQL 9 does not ship it at all" is not quite true

On a MySQL 9.7.2 server, on 2026-09-20, the file is in the server's own
`plugin_dir`:

```
/usr/lib64/mysql/plugin/mysql_native_password.so
```

Asking the server to load it:

```
INSTALL PLUGIN mysql_native_password SONAME 'mysql_native_password.so';
ERROR 1127 (HY000): Can't find symbol '_mysql_plugin_interface_version_' in library
```

The missing symbol shows that the file is the client plugin.

The release notes say which side kept it.

> "For backward compatibility, `mysql_native_password` remains available on the
> client; the client-side built-in authentication plugin has been converted into
> a dynamically loadable plugin."
>
> [MySQL 9.0.0 release notes](https://dev.mysql.com/doc/relnotes/mysql/9.0/en/news-9-0-0.html), read 2026-09-20.
A MySQL 9 client can still authenticate to an older server that has the
plugin. The server side is gone, and that is the side your accounts
live on.

## How do I fix it?

One statement per account:

```sql
CREATE DATABASE IF NOT EXISTS nativefix;

CREATE USER IF NOT EXISTS 'reporting_app'@'%'
  IDENTIFIED WITH caching_sha2_password BY 'use-a-generated-secret';

ALTER USER 'reporting_app'@'%'
  IDENTIFIED WITH caching_sha2_password BY 'use-a-different-generated-secret';

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

DROP USER 'reporting_app'@'%';
```

`CREATE USER IF NOT EXISTS` is in that block to make it runnable, and it is not
part of the migration. On a real estate it is a hazard: if you have the host
pattern wrong it creates a second account and silently does not touch the one
you meant to fix. `ALTER USER` is the statement that migrates.

The password is required. A `mysql_native_password` hash cannot be turned into a
`caching_sha2_password` verifier without the plaintext password, which nobody
has. This is a credential rotation, not a setting change, so the application
configuration changes in the same window.

Measured in a MySQL 9.7.2 container on 2026-09-20, that `ALTER USER` succeeded
against an account left stranded by the upgrade, on a server with no native-password plugin
at all, and the account then logged in. An estate that upgraded before doing the
inventory is not locked out of its own repair.

Write the new secret through a secret manager rather than into a terminal. A
password in a `mysql -e` argument lands in shell history, and one typed into a
client lands in that client's history file, and both land in any session
recording or query capture that is running.

Two things must happen in the same maintenance window.

**Turn on TLS for the connection.** `caching_sha2_password` needs either a
secure transport or an RSA exchange before it will carry a password.

The manual lists the ways to satisfy that requirement.

> "For each user account, the first client connection for the user after any of
> the following operations must use a secure connection (made using TCP using
> TLS credentials, a Unix socket file, or shared memory) or RSA key pair-based
> password exchange"
>
> [MySQL 8.4 manual, Caching SHA-2 Pluggable Authentication](https://dev.mysql.com/doc/refman/8.4/en/caching-sha2-pluggable-authentication.html), read 2026-09-20.
Measured in a MySQL 9.7.2 container on 2026-09-20, one account, always over
TCP, with `FLUSH PRIVILEGES` used to empty the server's cache between runs:

| server cache | connection | result |
|---|---|---|
| empty | `--ssl-mode=DISABLED`, no key | `ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.` |
| empty | `--ssl-mode=DISABLED --get-server-public-key` | connected |
| populated | `--ssl-mode=DISABLED`, no key | connected |
| either | default, TLS | connected |

`ERROR 2061` is the empty-cache result, not the general one, and the client can
ask the server for the key rather than hold a copy. The first row matters for a
migration because changing a password clears the cache.
Turning TLS on is still the right answer because it does not depend on what
happened before.

**Upgrade the connector.** A client library that cannot speak
`caching_sha2_password` cannot authenticate to an account that uses it. On MySQL
8.4 that is survivable, because enabling the server-side plugin lets that
account stay on `mysql_native_password` for the window. On MySQL 9 it is not,
because there is no server-side plugin to enable. Connector/J reports its own
refusal to fetch the key over an insecure connection as
`Public Key Retrieval is not allowed`, which is a driver policy rather than a
server error.

## Finding the accounts that are actually used

The inventory tells you which accounts exist. It does not tell you which ones
matter. The obvious counter is not enough:

```sql
SELECT USER, HOST, CURRENT_CONNECTIONS, TOTAL_CONNECTIONS
FROM performance_schema.accounts
ORDER BY TOTAL_CONNECTIONS DESC
LIMIT 20;
```

Three properties of this counter are easy to miss, all measured on MySQL
8.4.11 on 2026-09-20.

**It counts attempts, not logins.** An account on `mysql_native_password` was
carried from a server with the plugin on to a stock 8.4.11 server, where it made
seven connection attempts and every one of them returned `ERROR 1524`. Its
`TOTAL_CONNECTIONS` read 7, which was the highest of any named account on the
server. The accounts that are broken look busiest because a client that cannot
connect retries.

**It resets on restart.** At that same reading `Uptime` was 2 and the five
successful connections from before the restart were gone. Read it on a server
that has been up a while, and never read a zero as evidence of anything.

**It can drop accounts entirely.** On a server started with
`--performance-schema-accounts-size=2`, four accounts each connected once and
none of the four appeared in the table at all, while
`Performance_schema_accounts_lost` read 7. The accounts were missing rather than
undercounted, and no error was raised anywhere. The default for
`performance_schema_accounts_size` is `-1`, autosized, so check that status counter before trusting the table.

Joining the table to `mysql.user` looks like the fix and is worse than the
problem. `mysql.user.Host` is the account's host pattern and
`performance_schema.accounts.HOST` is the host the client connected from, so for
`'legacy'@'%'`, the join returned `NULL` for both counters in the same MySQL
9.7.2 container, on 2026-09-20, where the unjoined table read 12 connections for
it under the host it connected from. It reports a live
account as dead.

The question this page needs answered uses a different table:

```sql
SELECT USER, HOST, ERROR_NUMBER, ERROR_NAME, SUM_ERROR_RAISED
FROM performance_schema.events_errors_summary_by_account_by_error
WHERE SUM_ERROR_RAISED > 0
ORDER BY SUM_ERROR_RAISED DESC;
```

On that same stock 8.4.11 server on 2026-09-20 it returned
`reporting_app 127.0.0.1 1524 ER_PLUGIN_IS_NOT_LOADED 7`. That names the
account, the error and the count, which is what you want in the hour after an
upgrade. Run it before the upgrade too, and it should be empty for 1524.

None of this replaces asking who owns the account. A counter cannot tell you
about the batch job that runs quarterly, and the accounts that cause the delayed
outage are usually the ones no smoke test touches: replication and
distributed-recovery users, backup and restore jobs, change-data-capture and
extract jobs, monitoring, schema-migration tooling, proxies and poolers,
reporting connectors, continuous-integration jobs and break-glass logins.

## Scope

This page is about Oracle MySQL Server and was measured on the official
container images for MySQL 8.0.46, 8.4.11 and 9.7.2 on 2026-09-20. Managed
providers and forks are not the same thing here. Amazon Relational Database
Service, Aurora, Cloud SQL, HeatWave and Percona Server each decide their own
defaults, parameter mechanisms and upgrade gates, and MariaDB's authentication
plugins are not MySQL's. Check the provider's release notes before applying any
of the startup options on this page.

## Why does AI-generated code cause this?

Ask for a MySQL user and a large amount of training material answers with
`IDENTIFIED WITH mysql_native_password`, because for about a decade that was the
compatible answer and it appears in every tutorial written in that period. On 8.0
it still works, so the account gets created, the change is reviewed, and nothing
complains until an upgrade two years later.

The related pattern is the fix for a connection error. When a client fails to
authenticate, a common suggestion is to move the account back to
`mysql_native_password`, which makes the error go away while moving the estate
in the wrong direction.

## How do I stop it coming back?

**Make the inventory query a check, not a task.** Run it in the same place you
run other pre-upgrade checks and fail on a non-empty result.

**Never write the plugin name in a `CREATE USER`** unless you mean something
other than the default. Omitting it gets you `caching_sha2_password` on every
supported version.

**Fix connections before you fix accounts.** Turn on TLS and update the
connectors. Then the plugin change is a one-line statement rather than an
outage.

**Do the migration before the upgrade, not during it.** Both changes are
reversible on their own. Together, on the same night, they are hard to tell
apart when something breaks.

**Sequence the two upgrades separately.** Migrate the accounts, then go to 8.4,
and only go to 9 once the inventory query returns nothing. The 8.4 bridge exists
for the case where that order was not possible, and it should have a date
attached to it in the same ticket that turns it on.

## How DBGorilla helps

DBGorilla connects read-only and samples activity continuously, carrying the
account behind each session, so the question this page ends on, which of these
accounts is in use, has an answer from sessions observed over time
rather than from a counter that resets on restart and counts failed attempts as
connections. On MySQL it is the activity samples that answer this one: the
per-fingerprint workload there carries no account. Engine coverage is listed on
the product pages; check it there. It surfaces and explains; it does not write to
your database.
[Get started free →](https://app.dbgorilla.com/signup)

## Related

- [Can I upgrade from MySQL 8.0 straight to 9?](https://www.dbgorilla.com/learn/mysql/can-i-upgrade-from-mysql-8-0-straight-to-9/)
- [Why does MySQL say public key retrieval is not allowed?](https://www.dbgorilla.com/learn/mysql/why-does-mysql-say-public-key-retrieval-is-not-allowed/)
- [How to set up a read-only MySQL user for an AI agent](https://www.dbgorilla.com/learn/mysql/how-to-set-up-a-read-only-mysql-user-for-an-ai-agent/)