Password Management in MySQL 8: Policies, Reuse, and Dual Passwords

At 2:47 AM, a platform team's PagerDuty alert fired: every application process connecting to a freshly promoted MySQL 8.0 replica was returning Authentication plugin 'caching_sha2_password' cannot be loaded. The upgrade had completed cleanly, replication was healthy, and all the pre-migration checklist items were ticked — but the application driver, a legacy Java connector pinned to MySQL Connector/J 5.1.x, had no idea caching_sha2_password existed. The team spent the next three hours rolling back the driver, patching connection strings, and manually switching authentication plugins for every service account while the on-call rotation burned. The root cause was not the upgrade itself — it was that MySQL 8.0 changed the default authentication plugin in a way that silently breaks older drivers, and nobody had audited the account-plugin matrix before the maintenance window opened.

The Authentication Plugin Change: caching_sha2_password vs mysql_native_password

Prior to MySQL 8.0, mysql_native_password was the universal authentication plugin. It uses a challenge-response protocol based on SHA-1, where the client hashes the password twice and XORs it with the server's random challenge. The approach is fast but has known weaknesses: SHA-1 is cryptographically broken for collision resistance, the hash is replayable in certain network positions, and the protocol does not provide forward secrecy.

MySQL 8.0 introduced caching_sha2_password as the new default. It uses SHA-256 hashing with a server-side in-memory cache for fast re-authentication of previously seen credentials. On first login from a new connection, caching_sha2_password requires either an SSL/TLS connection or an RSA key exchange to transmit the plaintext password for hashing server-side. Subsequent logins from the same host hit the cache and use the faster challenge-response path. This is the right tradeoff for a production environment that already terminates TLS at the MySQL listener.

-- Check which plugin each account uses
SELECT
    user,
    host,
    plugin,
    password_expired,
    account_locked
FROM mysql.user
ORDER BY plugin, user;

Run this on your existing instance before any upgrade. Any account showing mysql_native_password that is used by an application connector needs its driver verified against caching_sha2_password support before you proceed.

Why mysql_native_password Was Deprecated in MySQL 8.4

MySQL 8.4 LTS, released in 2024 as the first long-term support release in the new MySQL Innovation/LTS lifecycle, takes the deprecation of mysql_native_password a step further: the plugin is still present but disabled by default. You must explicitly load it via the --mysql-native-password=ON server option or the plugin-load-add configuration to use it at all.

The deprecation is driven by three converging factors. First, SHA-1's collision resistance has been practically broken since 2017; retaining a production authentication path built on it is a compliance liability. Second, every major connector ecosystem — JDBC, Python's mysql-connector-python, PHP's mysqlnd, Node.js's mysql2 — has supported caching_sha2_password for years. Third, MySQL 8.4 ships with authentication_fido and authentication_webauthn for multi-factor authentication, and the forward direction is clearly away from password-only authentication for privileged accounts.

In MySQL 9.x (the Innovation series), mysql_native_password is removed entirely. If you are running any application accounts still using it, your migration window is the MySQL 8.4 LTS support period — which ends in 2032.

-- On MySQL 8.4: verify whether mysql_native_password is loaded
SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM information_schema.PLUGINS
WHERE PLUGIN_NAME = 'mysql_native_password';

-- If not loaded, enable it temporarily in my.cnf during migration:
-- [mysqld]
-- mysql-native-password = ON
-- (restart required)

Switching Authentication Plugins Per Account

The cleanest way to switch an individual account is ALTER USER with an explicit plugin and password. This does not require restarting the server and takes effect immediately for subsequent logins.

-- Switch a specific user to caching_sha2_password
ALTER USER 'app_user'@'10.0.0.%'
    IDENTIFIED WITH caching_sha2_password BY 'StrongPassw0rd!';

-- Switch a user back to mysql_native_password (migration rollback or legacy driver support)
ALTER USER 'legacy_app'@'%'
    IDENTIFIED WITH mysql_native_password BY 'StrongPassw0rd!';

-- Verify the change took effect
SELECT user, host, plugin FROM mysql.user WHERE user IN ('app_user','legacy_app');

If you need to change the global default plugin for new accounts created without an explicit IDENTIFIED WITH clause, set it in my.cnf:

[mysqld]
# MySQL 8.0 default: caching_sha2_password
# Change only if you have confirmed all connectors support the target plugin
default_authentication_plugin = caching_sha2_password

Application Compatibility: Driver Support Matrix

The compatibility landscape for caching_sha2_password broke along connector version lines in MySQL 8.0's early releases. The following represents the minimum version where full support — including the RSA key exchange path for non-TLS connections — landed in each ecosystem:

If you cannot upgrade a connector — common in organizations with pinned dependency versions — switch that service account to mysql_native_password individually and schedule the connector upgrade separately. Do not let one legacy connector block your entire server's plugin posture.

Password Policy Enforcement: validate_password, History, and Reuse Intervals

Authentication plugin selection is only half of a production password security posture. The validate_password component (note: in MySQL 8.0 it became a component, not a plugin) enforces composition rules at the moment a password is set.

-- Install the validate_password component (MySQL 8.0+)
INSTALL COMPONENT 'file://component_validate_password';

-- Check current policy settings
SHOW VARIABLES LIKE 'validate_password%';

-- Example hardened policy via my.cnf:
-- [mysqld]
-- validate_password.policy = STRONG
-- validate_password.length = 14
-- validate_password.mixed_case_count = 1
-- validate_password.number_count = 1
-- validate_password.special_char_count = 1
-- validate_password.dictionary_file = /etc/mysql/dictionary.txt

Password history and reuse interval controls prevent credential recycling, a common gap in database security audits:

-- Require the last 6 passwords to be unique before reuse
ALTER USER 'app_user'@'10.0.0.%'
    PASSWORD HISTORY 6;

-- Alternatively, enforce a 365-day reuse interval
ALTER USER 'app_user'@'10.0.0.%'
    PASSWORD REUSE INTERVAL 365 DAY;

-- Both constraints can be combined on the same account
ALTER USER 'app_user'@'10.0.0.%'
    PASSWORD HISTORY 6
    PASSWORD REUSE INTERVAL 180 DAY;

-- Set global defaults in my.cnf (account-level overrides these):
-- [mysqld]
-- password_history = 6
-- password_reuse_interval = 365

Password Expiry: default_password_lifetime and ACCOUNT EXPIRE

Password expiry is a compliance requirement in many regulated environments. MySQL 8.0 gives you both global and per-account control.

-- Set a global default lifetime of 180 days (0 = never expires)
SET PERSIST default_password_lifetime = 180;

-- Override for a specific account: expire every 90 days
ALTER USER 'dba_user'@'%'
    PASSWORD EXPIRE INTERVAL 90 DAY;

-- Override for a service account: never expire
ALTER USER 'replication_user'@'replica-host'
    PASSWORD EXPIRE NEVER;

-- Force immediate expiry (user must change password on next login)
ALTER USER 'compromised_account'@'%' PASSWORD EXPIRE;

-- Check which accounts have expired passwords right now
SELECT user, host, password_expired, password_lifetime, password_last_changed
FROM mysql.user
WHERE password_expired = 'Y';

When a password is expired, the account can still connect but can execute only ALTER USER ... IDENTIFIED BY and SET PASSWORD until the credential is updated. This sandbox mode is controlled by disconnect_on_expired_password: when set to ON, the server disconnects expired accounts immediately rather than entering sandbox mode. For automated service accounts, PASSWORD EXPIRE NEVER paired with mandatory rotation enforced by your secrets manager is the correct pattern.

Zero-Downtime Credential Rotation: The Dual Password Feature

MySQL 8.0.14 introduced dual passwords, one of the most operationally useful features added to MySQL in years. It allows an account to have two active passwords simultaneously — the primary and a secondary — enabling a coordinated rotation without a maintenance window.

-- Step 1: Set a new primary password and retain the current one as secondary
ALTER USER 'app_user'@'10.0.0.%'
    IDENTIFIED BY 'NewStrongPassw0rd!'
    RETAIN CURRENT PASSWORD;

-- At this point, both the old AND new passwords authenticate successfully.
-- Deploy application changes to use the new password (rolling deploy, no downtime).

-- Step 2: After all application instances are using the new password,
-- discard the old (secondary) password
ALTER USER 'app_user'@'10.0.0.%'
    DISCARD OLD PASSWORD;

-- Verify: only the new password should now work
SELECT user, host, plugin FROM mysql.user WHERE user = 'app_user';

Locking Down Service Accounts with mysql_no_login

Many MySQL deployments have accounts that exist purely as proxy targets or for stored procedure execution contexts — accounts that should never authenticate interactively under any circumstances. The mysql_no_login plugin is the correct tool for these. An account using mysql_no_login cannot be used to log in at all, regardless of what password is supplied.

-- Install the plugin (ships with MySQL but is not loaded by default)
INSTALL PLUGIN mysql_no_login SONAME 'mysql_no_login.so';

-- Create a service account that cannot login directly
CREATE USER 'proc_executor'@'localhost'
    IDENTIFIED WITH mysql_no_login;

-- Grant the necessary object-level privileges
GRANT EXECUTE ON PROCEDURE mydb.generate_report TO 'proc_executor'@'localhost';

-- Verify the plugin assignment
SELECT user, host, plugin FROM mysql.user WHERE user = 'proc_executor';

This pattern is particularly valuable for stored procedure definer accounts, replication filter accounts, and scheduler accounts that are referenced in CREATE EVENT ... DEFINER='...' clauses. Even if an attacker discovers the account name, the plugin refuses the connection at the authentication layer before any privilege check occurs.

Migrating from mysql_native_password to caching_sha2_password Safely

A systematic migration from mysql_native_password to caching_sha2_password across a production fleet requires a sequenced approach. Performing it in phases eliminates the risk of a simultaneous application failure across all services.

-- Phase 1: Inventory all accounts using mysql_native_password
SELECT
    user,
    host,
    plugin,
    password_expired,
    account_locked
FROM mysql.user
WHERE plugin = 'mysql_native_password'
ORDER BY user, host;

For each account returned, confirm the connector version in use by all applications that connect with those credentials. Cross-reference against the support matrix above. Only after confirming compatibility should you proceed to Phase 2.

-- Phase 2: Migrate accounts one at a time, starting with non-critical service accounts
-- Use RETAIN CURRENT PASSWORD to allow parallel deployment without a hard cutover
ALTER USER 'reporting_user'@'%'
    IDENTIFIED WITH caching_sha2_password BY 'CurrentPassw0rd!'
    RETAIN CURRENT PASSWORD;

-- Deploy the updated application (or verify connector supports caching_sha2_password).
-- Confirm successful logins from all application instances.
-- Then discard the old credential:
ALTER USER 'reporting_user'@'%' DISCARD OLD PASSWORD;

-- Phase 3: After all accounts are migrated, remove mysql_native_password
-- from plugin-load-add in my.cnf and restart.
-- This prevents any new accounts from accidentally using it.

-- Phase 4: On MySQL 8.4+, confirm the plugin is no longer active
SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM information_schema.PLUGINS
WHERE PLUGIN_NAME = 'mysql_native_password';
-- Expected: no rows (plugin not loaded)

Checking Plugin and Policy State: Your Operational Dashboard

These queries should be part of any DBA's daily operational checks or incorporated into a monitoring dashboard:

-- Accounts by plugin (quick summary)
SELECT plugin, COUNT(*) AS account_count
FROM mysql.user
GROUP BY plugin
ORDER BY account_count DESC;

-- Accounts with expired or soon-to-expire passwords
SELECT
    user,
    host,
    plugin,
    password_expired,
    password_last_changed,
    password_lifetime,
    DATE_ADD(
        password_last_changed,
        INTERVAL IFNULL(password_lifetime, @@default_password_lifetime) DAY
    ) AS expires_on
FROM mysql.user
WHERE password_expired = 'Y'
   OR DATE_ADD(
        password_last_changed,
        INTERVAL IFNULL(password_lifetime, @@default_password_lifetime) DAY
      ) < DATE_ADD(NOW(), INTERVAL 30 DAY)
ORDER BY expires_on;

-- Accounts that are not locked and not using a no-login plugin
SELECT user, host, plugin, account_locked
FROM mysql.user
WHERE account_locked = 'N'
  AND plugin NOT IN ('mysql_no_login')
ORDER BY user;

-- Password policy component status
SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME LIKE 'validate_password%';

Managing MySQL Authentication at Scale with JusDB

JusDB works with engineering teams who need production-grade MySQL without the operational overhead of maintaining a full internal DBA practice. Authentication plugin migrations, password policy hardening, credential rotation without downtime, and pre-upgrade compatibility audits are the kind of work we handle regularly — alongside replication health, schema optimization, and 24/7 incident response.

If you are planning a MySQL 8.0 or 8.4 LTS upgrade and want to complete the authentication migration cleanly without a 3 AM rollback, talk to us before the maintenance window.

Explore JusDB managed MySQL services →

Related reading:

• MySQL Foreign Keys: Behavior Changes from 5.7 to 8.4 LTS
• Geospatial Data Handling in MySQL 8.0