Choose the right MySQL or MariaDB authentication plugin

The fix

-- MySQL 8.4+
ALTER USER 'app'@'10.0.1.%' IDENTIFIED WITH caching_sha2_password BY 'secret';

-- MariaDB: root/admin, local only — already the Debian default
ALTER USER 'root'@'localhost' IDENTIFIED VIA unix_socket;

-- MariaDB: application accounts needing a password
INSTALL SONAME 'auth_ed25519';
ALTER USER 'app'@'10.0.1.%' IDENTIFIED VIA ed25519 USING PASSWORD('secret');

Read the safety section before running any of these. Changing an auth plugin is how you lock an application out of its database.

Why mysql_native_password has to go

It hashes passwords with SHA-1, and it does not salt them. Two accounts with the same password have the same hash, and the hash is a plain SHA-1 of the password — which means an offline crack against a leaked mysql.user table is about as cheap as it gets. There is no work factor to raise.

Oracle has been retiring it on a schedule:

Version	Status
8.0.34	Deprecated — warnings begin
8.4 LTS	Disabled by default — the plugin is not loaded
9.0	Removed entirely

That is not a deprecation you can defer indefinitely. On 8.4 an account using it cannot authenticate until the plugin is explicitly re-enabled; on 9.0 there is no plugin to enable.

MySQL and MariaDB need different answers

This is the part generic guides get wrong. They tell you to use caching_sha2_password, which is correct for MySQL and wrong for most MariaDB installs.

MySQL: use caching_sha2_password. It has been the default since 8.0, so a modern account already uses it. The work is migrating legacy accounts.

On 8.4 the relevant variable changed name — default_authentication_plugin was replaced by authentication_policy:

[mysqld]
authentication_policy = caching_sha2_password

MariaDB: caching_sha2_password only arrived in Community 12.1 (and Enterprise 11.8). If you’re on Debian trixie’s MariaDB 11.8 Community, it isn’t there, and mysql_native_password is still MariaDB’s default plugin for any account created without naming one. MariaDB’s own docs say it is “not recommended for new installations that require high password security.”

MariaDB’s two good options:

• unix_socket for local administrative accounts. This is already how Debian and Ubuntu create root@localhost — as IDENTIFIED VIA unix_socket OR mysql_native_password USING 'invalid', meaning the OS user vouches for identity and the password path is deliberately unusable. It is genuinely good and needs no work.
• ed25519 for accounts that must authenticate with a password over the network. The shared library ships with MariaDB but the plugin is not installed by default — INSTALL SONAME 'auth_ed25519' first, or the IDENTIFIED VIA ed25519 will fail.

Verify it worked

List every account and its plugin — this is the inventory you’re working from:

SELECT user, host, plugin FROM mysql.user ORDER BY plugin, user;

Count what still needs migrating:

SELECT COUNT(*) FROM mysql.user WHERE plugin = 'mysql_native_password';

Expect 0 on MySQL. On MariaDB, expect only accounts you have consciously decided to leave.

Confirm the plugin is actually loaded (MariaDB, after INSTALL SONAME):

SELECT plugin_name, plugin_status FROM information_schema.plugins
WHERE plugin_name LIKE '%ed25519%' OR plugin_name LIKE '%socket%';

Then prove a real connection uses the new plugin rather than trusting the catalogue:

mysql -u app -p -h 127.0.0.1 -e "SELECT VARIABLE_VALUE FROM performance_schema.session_status WHERE VARIABLE_NAME='Ssl_cipher'; SELECT CURRENT_USER();"

On MySQL, watch the error log during the migration — 8.0.34+ logs a deprecation warning every time an account authenticates with the old plugin, which is the cheapest way to find the clients you missed:

sudo grep -c 'mysql_native_password' /var/log/mysql/error.log

Before you apply this

Old clients cannot do the new plugins, and the failure is an authentication error that tells you nothing useful.

caching_sha2_password needs a client that speaks it: Connector/J 8.0+, mysqlclient 2.0+, PHP 7.4+ with mysqlnd, and reasonably current Go and Python drivers. Older PHP, vendored drivers, appliances and anything embedded will fail to connect — and cannot be fixed from the server side.

There’s a second trap specific to caching_sha2_password: it will refuse to send credentials over an unencrypted connection unless the server’s RSA public key is exchanged first. In practice, accounts on this plugin want TLS working. Migrating auth before TLS produces connection failures that look like a plugin problem and aren’t.

Order that avoids the outage:

1. Inventory: run the mysql.user query above. Know exactly which accounts and which clients.
2. Get TLS working first.
3. Migrate one account at a time, starting with one you can easily roll back. Test that application before moving on.
4. Leave root@localhost for last, and on MariaDB leave it on unix_socket.

Do not “fix” this by re-enabling mysql_native_password on 8.4. It works, and it buys you until your next major upgrade, at which point the plugin does not exist and the same accounts break with less warning and probably less convenient timing.

Keep a session open as root while you work — an authenticated session survives plugin changes and is your way back if you lock out an admin account.

Related

• Enable TLS — do this before migrating to caching_sha2_password.
• Remove anonymous users and the test database — accounts with no password at all.