MJ Convento

/

MySQL 8.4 LTS, and the authentication plugin that ended the upgrade

1,776 words, about 8 min read

The primary was down for 6 minutes 40 seconds. I had booked ninety. The data dictionary upgrade, the system table pass, the restart, all of it finished while I was still reading the log, and for about four minutes I thought this was going to be the cleanest database upgrade I had ever run. Then a cron box started emailing failures, and I spent the rest of the night on one authentication plugin.

Notes from taking a production MySQL 8.0 install to 8.4.10, written down mostly because the interesting part was not the upgrade.

Why 8.4 and not 9.7

We were pushed, not pulled. Oracle’s end of life notice says that as of 21 April 2026, MySQL 8.0 is covered under Sustaining Support, and it tells you to move to 8.4 LTS or 9.7 LTS. Sustaining Support is the tier where you stop getting new fixes, so from April onward every CVE conversation with the client had the same unpleasant shape.

9.7.0 came out on the same day, 21 April 2026, and it is also an LTS. I looked at it for about an hour before the upgrade paths table settled it: a bugfix or LTS series cannot be skipped. From an LTS you upgrade to the next LTS, so 8.0 goes to 8.4, and 8.4 goes to 9.7. There is no 8.0 to 9.7. Two upgrades, and we are doing the first one this quarter and the second one when I have forgotten about this one.

The release model is worth understanding once, properly, because I have watched two teams pick the wrong track. Per the releases documentation, an LTS series gets the Oracle Lifetime Support Policy, which is five years of premier support and three years of extended support, and nothing is removed inside the series: features can only be removed in the first LTS release, which is why 8.4.0 is where all the removals landed. Innovation releases get new features and behaviour changes, and are supported only until the next Innovation release. If your team does not have the automated testing to absorb behaviour changes every quarter, the Innovation track will hurt you. We are an LTS shop by temperament.

There is no mysql_upgrade any more

I went looking for it out of muscle memory. The mysql_upgrade utility was deprecated back in 8.0.16 and is removed in 8.4, along with mysqlpump and mysql_ssl_rsa_setup. The server does the work itself at startup now, in two documented steps: the data dictionary tables in the mysql schema, the Performance Schema and INFORMATION_SCHEMA first, then everything else, meaning the remaining system tables, the sys schema and user schemas.

That is controlled by --upgrade, which defaults to AUTO and does both steps. MINIMAL does only the first, and the docs warn that Group Replication will not start afterwards. NONE does neither, and if the data dictionary is out of date the server refuses to run at all:

[ERROR] [MY-013381] [Server] Server shutting down because upgrade is
required, yet prohibited by the command line option '--upgrade=NONE'.
[ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
[ERROR] [MY-010020] [Server] Data Dictionary initialization failed.

Before any of that I ran MySQL Shell’s upgrade checker, which the prerequisites page recommends and which I now consider mandatory. It found one thing I would never have guessed at: AUTO_INCREMENT on a FLOAT or DOUBLE column was deprecated in 8.0 and is removed in 8.4, and if you leave one in place the upgrade fails. We had exactly one, in a table written in 2017 that nobody had touched since, with 4.1 million rows in it. Fixing it was a MODIFY COLUMN and 90 seconds of copying.

Downtime numbers, ours, on a 24 GB buffer pool and roughly 310 GB of data: 6 minutes 40 seconds from shutdown to accepting connections. What the window does not cover is warmup. innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup are both enabled by default, and innodb_buffer_pool_dump_pct defaults to 25, so a quarter of the most recently used pages come back on their own. That got us to acceptable in about 9 minutes. Our p95 on the two heaviest list endpoints sat 3 to 4 times normal for 14 minutes and was flat by minute 20. If you need better than that, raise the dump percentage before you shut down, which I will do next time.

The plugin that ended the evening

Here is the one to plan for. mysql_native_password was deprecated in 8.0.34, is disabled by default in 8.4, and is removed entirely as of 9.0.0. Disabled means the server-side plugin is still compiled in but not loaded, and every account that authenticates with it stops working. The error the application sees is not helpful:

ERROR 1045 (28000): Access denied for user 'reporting'@'10.0.2.%' (using password: YES)

That is the same message you get from a wrong password, which is how I wasted twenty minutes. The server log says the same thing. Nothing anywhere says “the plugin this account uses is not loaded”. Worse, the obvious reflex fails too:

mysql> ALTER USER 'reporting'@'10.0.2.%' IDENTIFIED WITH 'mysql_native_password' BY '...';
ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded

Then the second flavour of the same problem, on an old box running an old client library against the new server. MySQL client programs in 8.4 use caching_sha2_password by default, and a client that predates the plugin cannot negotiate it:

ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded:
dlopen(/usr/lib64/mysql/plugin/caching_sha2_password.so, 2): image not found

Two choices at that point. Start the server with --mysql-native-password=ON, or mysql_native_password=ON in the [mysqld] section, and everything works exactly as it did. Or rotate the accounts. Re-enabling is one line and zero risk tonight, and it is also a line that becomes a hard blocker the moment you touch 9.0 or later, where the plugin does not exist. We turned it on for eleven days, rotated 23 accounts, then took the line back out. I do not regret the eleven days.

The statement that fixed it

ALTER USER 'reporting'@'10.0.2.%'
  IDENTIFIED WITH caching_sha2_password
  BY 'new-password-here';

SELECT user, host, plugin FROM mysql.user
 WHERE plugin <> 'caching_sha2_password';

Run that SELECT on 8.0 before you upgrade. It is the whole pre-flight check, it takes a second, and it would have saved me the night.

One catch that bites in containers. To connect with a caching_sha2_password account you need either a secure connection or an unencrypted connection that can do RSA key pair password exchange, and the server does not hand out its RSA public key to clients unless asked. Clients pass --get-server-public-key, or you ship the key file and point at it. Everything of ours talks TLS, so this cost us one confused hour on a local test container and nothing in production.

Also gone in 8.4: default_authentication_plugin, removed in 8.4.0 after being deprecated in 8.0.27. It is replaced by authentication_policy, which defaults to *,, and whose syntax changed as part of the removal. Ours was set explicitly in my.cnf, which meant the server would not start until I deleted the line.

The config lines that stopped the server starting

That was the shape of the rest of the work. Removed options raise an error, so a stale my.cnf is a failed start rather than a warning. Ours had four offenders: default_authentication_plugin, skip-host-cache (now --host-cache-size=0), ssl as a boolean along with a have_ssl check in a monitoring script (both removed, use --tls-version), and binlog_transaction_dependency_tracking, which is removed because the source now always uses writesets, the equivalent of the old WRITESET setting.

Replication is the other place where old habits fail loudly. The deprecated statements are not deprecated any more, they are gone: CHANGE MASTER TO, SHOW MASTER STATUS, RESET MASTER, PURGE MASTER LOGS, START SLAVE, STOP SLAVE, SHOW SLAVE STATUS, SHOW SLAVE HOSTS and RESET SLAVE. The replacements are CHANGE REPLICATION SOURCE TO, SHOW BINARY LOG STATUS, RESET BINARY LOGS AND GTIDS, PURGE BINARY LOGS, START REPLICA, STOP REPLICA, SHOW REPLICA STATUS, SHOW REPLICAS and RESET REPLICA. Three of our shell scripts and one Nagios check parsed SHOW SLAVE STATUS output. All four broke, and the fix in each case was a rename plus adjusting for the renamed columns.

mysqldump is gentler. --master-data and --dump-slave still work as deprecated aliases for --source-data and --dump-replica, so our backup scripts kept running. What changes is the output: a dump from 8.4 writes CHANGE REPLICATION SOURCE TO, which an 8.0 server will not accept. That is what --output-as-version is for, with BEFORE_8_0_23 writing the old terminology back out. We needed it exactly once, to seed a staging replica still on 8.0:

mysqldump --single-transaction --source-data=1 
  --output-as-version=BEFORE_8_0_23 
  --databases app > /var/backups/app.sql

One privilege detail: --source-data issues SHOW BINARY LOG STATUS, so the dump user needs privileges for that plus RELOAD, and the binary log has to be enabled.

Total human cost: about 14 hours across three weeks, of which the actual upgrade was one evening and the authentication work was six hours spread over eleven days. If you are sizing this yourself, run that mysql.user query first and count the rows. That number is your upgrade. Next on my list is 9.7, and the first thing I want to know is what else got disabled by default in it, because I am clearly the kind of person who finds out the hard way.

Sources

  • MySQL end of life notice: MySQL 8.0 under Oracle Sustaining Support as of 21 April 2026, with 8.4 LTS and 9.7 LTS named as the upgrade targets.
  • MySQL Releases: Innovation and LTS: five years premier plus three years extended support for an LTS series, no removals inside a series, Innovation supported only until the next Innovation release.
  • Upgrade paths: an LTS or bugfix series cannot be skipped, so 8.0 goes to 8.4 and 8.4 goes to 9.7.
  • Native pluggable authentication: mysql_native_password deprecated in 8.0.34, disabled by default in 8.4, removed as of 9.0.0, the --mysql-native-password=ON switch, and the 1045 and 1524 errors.
  • caching_sha2_password: the secure connection or RSA key pair requirement and --get-server-public-key.
  • What is new in MySQL 8.4 since 8.0: removed server options and variables including default_authentication_plugin, skip-host-cache, --ssl and binlog_transaction_dependency_tracking, the removed replication statements, the removal of mysql_upgrade and mysqlpump, and AUTO_INCREMENT on FLOAT and DOUBLE columns failing the upgrade.
  • What the upgrade process upgrades: the two steps the server performs at startup and the --upgrade values, including the MY-013381 error.
  • mysqldump: --master-data and --dump-slave as deprecated aliases, and --output-as-version with its BEFORE_8_0_23 value.
  • Saving and restoring the buffer pool state: dump at shutdown and load at startup enabled by default, innodb_buffer_pool_dump_pct default of 25.
  • Upgrade prerequisites: the MySQL Shell upgrade checker utility and target release selection.