Subscribe

Upgrading MySQL 5.7 to 8.4 when the old app needs mysql_native_password

MySQL 8.4 turns mysql_native_password off by default, and Ghost 4 cannot log in without it. Here is how I moved a MySQL 5.7 database to 8.4 with a dump, and used a temporary Compose override to let the old app connect just for the upgrade.

Upgrading MySQL 5.7 to 8.4 when the old app needs mysql_native_password

The database behind this blog was MySQL 5.7.42, running on the same 2020 box as Ghost 4. When I moved the blog to Docker Compose (the full move is in the post on migrating a Ghost-CLI blog to Docker Compose), the database had to move to a supported MySQL as well. The new version was easy to pick. Getting the old Ghost to log in to it was the part that took some reading.

In this blog we will look at moving a 5.7 database to 8.4 with a plain dump, and the small Compose override that lets an old client use mysql_native_password only for as long as it needs to.

Why 8.4 and why a dump

MySQL 8.0 reached end of life in April 2026. Moving onto it in November 2026 would mean doing this again soon, so I went straight to 8.4 LTS.

I did think about postgres for a minute. Ghost doesn't support it, only MySQL 8 in production, so that was a short discussion.

You can't upgrade 5.7 to 8.4 in place. The in-place upgrade path goes 5.7 to 8.0 to 8.4, one series at a time, and I didn't want to run a temporary 8.0 just to step over it. A logical dump skips all that. The database was only about 285 MB, so a dump was small and quick to move around.

The dump came from the old host with --single-transaction, so it didn't lock tables while the blog was still serving. Before restoring anything I check the last line. mysqldump writes a "Dump completed" comment only when it finishes cleanly, so a truncated file is easy to spot.

gunzip -c ghost_db.sql.gz | tail -1
# -- Dump completed on 2026-11-09 10:12:41

I do the same check in the nightly backup script now. A dump that silently stopped half way is worse than no dump, because you think you have one.

Restoring into MySQL 8.4

The new MySQL runs as a Compose service. The official image creates the app database and user on first start from MYSQL_DATABASE, MYSQL_USER and MYSQL_PASSWORD, so the restore is just piping the dump in as root.

docker compose up -d db
until [ "$(docker inspect -f '{{.State.Health.Status}}' ghost-db-1)" = healthy ]; do sleep 3; done

gunzip -c ghost_db.sql.gz | docker compose exec -T db sh -c \
  'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD" "$MYSQL_DATABASE"'

The single quotes are on purpose, $MYSQL_ROOT_PASSWORD is expanded inside the container, from the container's own environment, so it never shows up on the host command line.

Ghost 4 and caching_sha2_password

Here is the problem. MySQL 8 creates users with caching_sha2_password by default. Ghost 4 uses the old mysql npm driver, and that driver doesn't support caching_sha2_password. It only knows mysql_native_password. If you point Ghost 4 at a default 8.x user, the connection fails with an ER_NOT_SUPPORTED_AUTH_MODE error ("Client does not support authentication protocol requested by server").

On 8.0 the fix was just ALTER USER ... IDENTIFIED WITH mysql_native_password. On 8.4 there is one more step. The native password plugin is still shipped but it's disabled by default, so the server has to be started with it turned on first.

I didn't want that setting in the real config. Ghost 6 talks caching_sha2_password fine, and the legacy plugin is deprecated. So it went into a separate override file that is only used during the upgrade.

# compose.migrate.yml
# Used ONLY during the Ghost 4 -> 5 -> 6 upgrade.
services:
  db:
    command:
      - --character-set-server=utf8mb4
      - --collation-server=utf8mb4_0900_ai_ci
      - --innodb-buffer-pool-size=256M
      - --performance-schema=OFF
      - --max-connections=50
      - --mysql-native-password=ON

One gotcha with Compose overrides. A command list in an override replaces the whole list, it doesn't append. So every flag from the base file has to be repeated, plus the new one. It's easy to copy only the new flag and lose the rest without any error, so look at the merged result before you start anything.

docker compose -f compose.yml -f compose.migrate.yml config | grep -A8 'command:'

Then start MySQL with both files and switch the app user over.

MIG="docker compose -f compose.yml -f compose.migrate.yml"
$MIG up -d db

docker compose exec -T db sh -c 'mysql -uroot -p"$MYSQL_ROOT_PASSWORD" -e \
  "ALTER USER \`$MYSQL_USER\`@\`%\` IDENTIFIED WITH mysql_native_password BY \"$MYSQL_PASSWORD\""'

And check it took.

docker compose exec -T db sh -c 'mysql -uroot -p"$MYSQL_ROOT_PASSWORD" -e \
  "select user, host, plugin from mysql.user where user not like \"mysql.%\""'

The app user should show mysql_native_password. Root can stay on caching_sha2_password, the root shell inside the container has no problem with it.

Switching back

The Ghost 4 and Ghost 5 steps ran with the override on. I kept it on for Ghost 5 as well instead of switching in the middle. Fewer moving parts.

After Ghost 6 finished its migrations, the order of the next two steps matters. Switch the user back first, while the native plugin is still on. Then recreate MySQL without override.

docker compose exec -T db sh -c 'mysql -uroot -p"$MYSQL_ROOT_PASSWORD" -e \
  "ALTER USER \`$MYSQL_USER\`@\`%\` IDENTIFIED WITH caching_sha2_password BY \"$MYSQL_PASSWORD\""'

docker compose up -d --force-recreate db ghost

If you do it the other way round, MySQL comes up with the plugin disabled and a user that still needs it. That user then can't log in at all, and you are fixing it as root with the blog down. Not a big deal on a rehearsal copy. Annoying at cutover.

Collations after the restore

The server default in the new config is utf8mb4_0900_ai_ci, which is the 8.x default. But a restore keeps whatever collation the dump's CREATE TABLE statements say, and a 5.7 dump will have 5.7 collations in it. New tables that Ghost 5 and 6 create during migration get the server default. So you can end up with a mix.

It's worth looking at what you actually have.

SELECT table_collation, COUNT(*) AS tables
FROM information_schema.tables
WHERE table_schema = DATABASE()
GROUP BY table_collation;

A mix mostly matters when a query compares string columns across tables with different collations, and MySQL throws "Illegal mix of collations". I haven't seen that error from Ghost, and I left the tables alone instead of converting them. If you do convert, do it on a copy and compare row counts after, a collation change can turn two values that were different into duplicates on a unique index.

Quick checks before calling it done

I check three things on the new server. The version, that legacy auth is off again, and that the app user is back on the default plugin.

docker compose exec -T db sh -c 'mysql -uroot -p"$MYSQL_ROOT_PASSWORD" -N -e \
  "select version(); select plugin_name, plugin_status from information_schema.plugins \
   where plugin_name = \"mysql_native_password\"; \
   select user, plugin from mysql.user where user = \"$MYSQL_USER\""'

On 8.4 the plugin should be missing from that list, or not ACTIVE, once the override is gone. Once all three look right you can delete compose.migrate.yml, or keep it in git like I did as a record of what the upgrade needed.