caching_sha2_password: The 8.0 Default Auth That Broke Half Our Clients

The database upgrade itself was the easy part. Our primary went from 5.7 to 8.0 on a Saturday with four minutes of downtime, replication caught up in ninety seconds, and by midnight every dashboard was green. The failure arrived on Monday at 09:14, when the PHP billing worker — a service nobody had deployed in eleven months — started logging error 2059: Authentication plugin caching_sha2_password cannot be loaded. Twenty minutes later a Nagios-era Perl cron that reconciles invoices answered with error 1251, Client does not support authentication protocol, and by lunch we had a tally: fourteen services reconnected fine because they ran modern drivers, and three did not, because their client libraries predated the plugin by design. The root cause was one line in the 8.0 release notes that our upgrade checklist had absorbed as a warning but not as a task: the default authentication plugin changed from mysql_native_password to caching_sha2_password, and any account created or altered on the new server inherits it. Our runbook user-creation step for the new read-only accounts had quietly minted caching_sha2_password credentials that half the fleet could not speak.

What actually changed in 8.0?

Two things, and both matter. First, the default plugin: accounts created with CREATE USER and no explicit plugin, or altered with ALTER USER and a new password, land on caching_sha2_password unless you say otherwise or set default_authentication_plugin back to mysql_native_password. Second, the mechanism: caching_sha2_password performs SHA-256-based challenge-response and comes in two modes. Fast authentication applies once the server has seen you succeed before — it matches a cached hash of your scrambled credential and completes in a single round trip, which is where the caching in the name earns its keep. Full authentication is required the first time, after a restart, or after a password change, and it demands either a TLS connection or an RSA public-key exchange to carry the password safely. That second mode is where old clients fall over: a libmysqlclient older than 8.0.4 simply does not contain the plugin code, so the handshake ends before credentials are even discussed. New accounts on a fresh 8.0 server hit full authentication on every connection until the cache warms, which is also why connection-storm workloads saw a small but real CPU bump from RSA operations until we sorted the rollout. The plugin is the better protocol — mysql_native_password’s SHA-1 construction is aged — so the goal was migration, not retreat.

-- who is on which plugin, fleet-wide inventory in one query
SELECT user, host, plugin,
       password_last_changed
FROM mysql.user
ORDER BY plugin, user;

-- how is this server minting new accounts by default?
SELECT @@default_authentication_plugin;

-- the escape hatch while a client gets upgraded (deprecated in 8.4,
-- so treat it as a bridge, not a destination)
ALTER USER 'billing_worker'@'10.0.4.%'
  IDENTIFIED WITH mysql_native_password BY '…';

-- the target state, once the client library supports it
ALTER USER 'billing_worker'@'10.0.4.%'
  IDENTIFIED WITH caching_sha2_password BY '…';

How do you find every client that will break before it breaks?

Inventory from both sides, because each side lies by omission. From the server side, the mysql.user query above lists every account’s plugin, and the accounts already on caching_sha2_password are your exposed surface — cross-reference them against the connection sources in performance_schema by host to see which services actually use them. From the client side, the honest test is a staging instance with the account flipped to the new plugin and the real service pointed at it, because driver capability matrices on vendor sites are optimistic: our PHP worker’s mysqlnd build claimed 8.0 support but had been compiled against a libmysqlclient from 2017, and the Perl cron’s DBD::mysql was older than some of the engineers paged that Monday. The practical sweep: every service that connects to MySQL gets a line in a spreadsheet with its driver, driver version, and the result of a forced reconnection against a caching_sha2_password account. That sounds tedious, and it is, and it takes one afternoon for a forty-service fleet — less time than the incident bridge took. One more trap: clients behind proxies or connectors that terminate and re-initiate the MySQL protocol themselves need their own check, because the proxy’s server-side plugin support is a separate question from the application’s.

What is the staged cutover that does not page anyone?

Three stages, in this order, each gated on evidence rather than calendar. Stage one: upgrade or shim the clients. Where a driver upgrade is trivial, do that. Where it is not — the Perl cron got a container rebuild it had deserved for years — bridge the specific account back to mysql_native_password with the ALTER USER above, and put a ticket with an expiry date on the bridge, because 8.4 deprecates the old plugin and a bridge with no expiry becomes load-bearing. Stage two: flip accounts to caching_sha2_password one service at a time, off-peak, with the connection error rate on that service watched for thirty minutes before moving on. If you are rotating passwords as part of the same project, the dual-password rotation notes describe how to keep the old credential valid during the window, which composes cleanly with a plugin switch. Stage three: change the default. Set default_authentication_plugin back-and-forth during the migration if you must, but end with caching_sha2_password as the server default so new accounts stop re-creating the problem. Along the way, prefer TLS for every connection and you sidestep the RSA key-exchange question entirely — full authentication over TLS needs no get_server_public_key dance, and connection establishment stays one round trip plus the handshake you already pay for.

What does this cost at runtime once everyone is migrated?

Almost nothing, provided your clients hold connections instead of churning them — and that provided is doing real work. Fast authentication is a memory lookup and a hash compare; at steady state with pooled connections it is indistinguishable from the old plugin. The cost concentrates at cold starts: after a restart or a password change, every new connection pays full authentication, and a fleet that opens thousands of fresh connections per second will feel the RSA exchange in connection latency and CPU unless TLS is in use. We measured it deliberately before rollout: with TLS everywhere, auth time added under a millisecond at p99 and vanished into network jitter; the one service that still created a connection per request got fixed for unrelated reasons, and this was the push. The operational residue is small but worth writing down: password_last_changed in mysql.user gives you a cheap credential-hygiene audit, the plugin column tells you when someone created an account with an explicit legacy plugin against policy, and both belong in the same periodic review as your user-and-host wildcard sweep. Authentication changes are the kind of change that is invisible when done right and unforgettable when done wrong; the spreadsheet and the three stages are what invisible looks like.

Where MonPG stands on MySQL

For MonPG product capabilities and setup information, see the MySQL monitoring page. Use the diagnostics in this article to identify the measurements and operational checks your deployment needs.

Related documentation

• MySQL: upgrading