Skip to content

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

Last updated

View as Markdown

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.

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, 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:

serverstartup optionplugin statuscan a native account log in?
MySQL 8.0.46noneACTIVEyes
MySQL 8.4.11--mysql-native-password=ONACTIVEyes
MySQL 8.4.11noneDISABLEDno, ERROR 1524
MySQL 9.7.2noneabsent from information_schema.pluginsno, 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.

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

[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, 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.

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, 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:

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:

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, 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, 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

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 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

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.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, 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.

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 PRIVILEGES used to empty the server’s cache between runs:

server cacheconnectionresult
empty--ssl-mode=DISABLED, no keyERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
empty--ssl-mode=DISABLED --get-server-public-keyconnected
populated--ssl-mode=DISABLED, no keyconnected
eitherdefault, TLSconnected

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_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:

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.

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.

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.

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.