How to find every user still on mysql_native_password before MySQL 8.4 or 9
Quick answer: Run one query against
mysql.userwhile the old server is still up. The break starts at MySQL 8.4, which shipsmysql_native_passworddisabled, 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 isALTER USER ... IDENTIFIED WITH caching_sha2_password, one account at a time, with a new password each.
What is the problem?
Section titled “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_passwordauthentication 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, 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
Section titled “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.
[mysqld]mysql_native_password=ONThat 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=ONor by includingmysql_native_password=ONin the[mysqld]section of your MySQL configuration file.”MySQL 8.4 manual, Native Pluggable Authentication, 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] AbortingAn 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?
Section titled “How do I confirm it?”This is the inventory. Run it on the server you are upgrading from, while it is still running:
CREATE DATABASE IF NOT EXISTS nativeaudit;
SELECT user, host, pluginFROM mysql.userWHERE 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:
SELECT plugin, COUNT(*) AS accountsFROM mysql.userGROUP BY pluginORDER 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:
SELECT plugin_name, plugin_statusFROM information_schema.pluginsWHERE 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_pluginsystem variable, deprecated in MySQL 8.0.27, is removed as of MySQL 8.4.0. Useauthentication_policyinstead.”MySQL 8.4 manual, What Is New in MySQL 8.4 since 8.0, read 2026-09-20.
authentication_policyreads*,,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_passwordbeing the default), and that factors 2 and 3 are optional and each can use any authentication plugin.”MySQL 8.4 manual,
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 withERROR 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
Section titled “What breaks, and what the error looks like”On a stock 8.4 server and on 9, creating the account is refused:
CREATE USER 'legacy'@'%' IDENTIFIED WITH mysql_native_password BY 'x';ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loadedOn 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
Section titled ““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.soAsking 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 libraryThe missing symbol shows that the file is the client plugin.
The release notes say which side kept it.
“For backward compatibility,
mysql_native_passwordremains 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, 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?
Section titled “How do I fix it?”One statement per account:
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, read 2026-09-20. Measured in a MySQL 9.7.2 container on 2026-09-20, one account, always over TCP, with
FLUSH PRIVILEGESused 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
Section titled “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:
SELECT USER, HOST, CURRENT_CONNECTIONS, TOTAL_CONNECTIONSFROM performance_schema.accountsORDER BY TOTAL_CONNECTIONS DESCLIMIT 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:
SELECT USER, HOST, ERROR_NUMBER, ERROR_NAME, SUM_ERROR_RAISEDFROM performance_schema.events_errors_summary_by_account_by_errorWHERE SUM_ERROR_RAISED > 0ORDER 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.
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?
Section titled “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?
Section titled “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 do I list every MySQL user on mysql_native_password?
SELECT user, host, plugin FROM mysql.user WHERE plugin = 'mysql_native_password'. Run it on the running server before you upgrade anything, because after the upgrade the accounts are still there and simply cannot authenticate, which looks like an application fault rather than an upgrade step you skipped.
Is mysql_native_password removed in MySQL 9?
The server-side plugin is. Measured in a MySQL 9.7.2 container on 2026-09-20 it is absent from information_schema.plugins and a native-password account gets ERROR 1524 on login. The client-side plugin is still shipped, so the file mysql_native_password.so is on disk on a MySQL 9.7.2 server and the server refuses to load it with ERROR 1127.
Does MySQL 8.4 break native-password accounts?
Only while the plugin stays disabled, which is the default. Measured in a MySQL 8.4.11 container on 2026-09-20, the same account on the same datadir logged in when the server was started with --mysql-native-password=ON and got ERROR 1524 when it was not. That switch is a bridge with a deadline, because MySQL 9.0 removes the option and a server started with it in my.cnf aborts.
How do I move an account to the new plugin?
ALTER USER 'name'@'host' IDENTIFIED WITH caching_sha2_password BY 'new-password'. It is one statement per account and it needs the password, because the stored hash cannot be converted. Measured in a MySQL 9.7.2 container on 2026-09-20, that statement works on a server that has no native-password plugin at all, so an account stranded by an upgrade is repairable in place.
Which of these accounts is actually still used?
performance_schema.accounts does not answer it. Measured in a MySQL 8.4.11 container on 2026-09-20, an account that could not log in at all showed TOTAL_CONNECTIONS of 7, because a client that cannot connect retries. Use performance_schema.events_errors_summary_by_account_by_error, which names the account against ER_PLUGIN_IS_NOT_LOADED.