Docker Compose with Ghost and MySQL 8.4, mysql_native_password startup errors - mitigation and solution

Weekend means time for maintenance tasks, this time upgrading this Ghost blog in Docker Compose on an Ubuntu VM. Unfortunately, pulling the latest MySQL 8 container led to the following error:

cd /docker/ghost

docker-compose pull
docker-compose down
docker-compose up -d 

docker-compose logs 

[...]

ghost-db_1      | 2024-05-11T12:01:50.565078Z 0 [System] [MY-015015] [Server] MySQL Server - start.
ghost-db_1      | 2024-05-11T12:01:50.988747Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.4.0) starting as process 1
ghost-db_1      | 2024-05-11T12:01:50.999884Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
ghost-db_1      | 2024-05-11T12:01:51.902227Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
ghost-db_1      | 2024-05-11T12:01:52.338932Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
ghost-db_1      | 2024-05-11T12:01:52.339265Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
ghost-db_1      | 2024-05-11T12:01:52.344769Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
ghost-db_1      | 2024-05-11T12:01:52.349330Z 0 [ERROR] [MY-000067] [Server] unknown variable 'default-authentication-plugin=mysql_native_password'.
ghost-db_1      | 2024-05-11T12:01:52.350385Z 0 [ERROR] [MY-010119] [Server] Aborting
ghost-db_1      | 2024-05-11T12:01:53.904686Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.4.0)  MySQL Community Server - GPL.
ghost-db_1      | 2024-05-11T12:01:53.904990Z 0 [System] [MY-015016] [Server] MySQL Server - end.
ghost_ghost-db_1 exited with code 1

Matching the error message

[Server] unknown variable 'default-authentication-plugin=mysql_native_password'.

with the docker-compose.yml configuration made it clear, something has changed with a more recent MySQL 8 version.

  ghost-db:
    image: mysql:8
    restart: always
    command: --default-authentication-plugin=mysql_native_password
    environment:
      MYSQL_ROOT_PASSWORD: xxx
    volumes:
      - /docker/ghost/mysql:/var/lib/mysql    

In 2021, I needed to modify the Ghost docker compose set for MySQL 8 support, and add the default-authentication-plugin parameter.

Root cause analysis

Fortunately, someone ran into the same problem three days ago, and shared their mitigation in this StackOverflow post.

mysql_native_password has been deprecated for a while now, and it seems that MySQL 8.4 removed the code that supports the deprecated flag, thus breaking any (Docker compose) setup. More insights in the Docker library image for MySQL project:

The problem is also reported in the Ghost docker image repository.

Mitigation

mysql-native-password=ON explicitly enables the otherwise disabled plugin, and makes it the default password encryption method.

MySQL upstream might decide to completely remove the functionality. For now it has been moved to a disabled plugin, which a new flag allows to enable. I have explicitly pinned the MySQL 8.4 version therefore.

  ghost-db:
    image: mysql:8.4
    restart: always
    command: --mysql-native-password=ON
    environment:
      MYSQL_ROOT_PASSWORD: xxx
    volumes:
      - /docker/ghost/mysql:/var/lib/mysql

How to migrate to caching_sha2_password?

The real challenge is to migrate to the proposed caching_sha2_password feature, but how? The following blog post discusses the problems in detail:

Open a terminal in the database container. You can fetch its name using docker ps. The SQL query prints the user, host, and plugin.

docker exec -ti ghost_ghost-db_1 bash

mysql -p

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT User, Host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| User             | Host      | plugin                |
+------------------+-----------+-----------------------+
| root             | %         | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | mysql_native_password |
| mysql.sys        | localhost | mysql_native_password |
| root             | localhost | mysql_native_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)

Since caching_sha2_password is the new default in MySQL 8.x, we do not need to override the password encryption method with an additional flag. The only requirement is to re-create the database users with the password.

Note: If you cannot recover the passwords from your credential store, follow these steps to reset the root password.

Alter the user password with caching_sha2_password

Create a database backup first. Extract the details from the docker-compose.yml configuration file.

docker run --rm --name mysql-dump --network ghost_default mysql:8 mysqldump -h ghost-db -u root -pMYSQLROOTPASSWORDHERE --databases ghost > backup-ghost-db.sql

Then modify the login users: Alter the auth plugin to use caching_sha2_password

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'xxx';

ALTER USER 'root'@'%' IDENTIFIED WITH caching_sha2_password BY 'xxx';

Remove the docker-compose.yml command entries.

  ghost-db:
    image: mysql:8.4
    restart: always
    ports:
      - "127.0.0.1:3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: xxx
    volumes:
      - /docker/ghost/mysql:/var/lib/mysql  

Restart the docker compose stack, and inspect the container logs.

docker-compose restart 

docker logs ghost_ghost-db_1
docker logs ghost_ghost-server_1 

Visit dnsmichi.at and verify it works.

Conclusion

Lesson learned: Create to-do issues to track workarounds for deprecations, they will bite you otherwise.

Hope this blog post helps not only with a mitigation but a migration strategy! :)