Errors After Upgrading to MySQL 9.0

I recently ran into an error with my Wordpress/mysql docker containers since I was using mysql-native-password and MySQL 9.0 removed support for the native password plugin. I think I was using that flag because of this very helpful DigitalOcean Guide for installing Wrodpress with Docker Compose.

Getting out of it was a bit tricky because once you upgrade to MySQL 9.0 you can’t roll back to 8.x, so you have to find a way to fix the authentication entries in the database by hand. I hope that writing up my steps here will save someone else some frustration when they hit this same problem.

My Setup

I have a docker compose file for my Wordpress instance with a MySQL server that looks like this:

  wp-allison-db:
    image: mysql:latest
    container_name: wp-allison-db
    volumes:
      - ./db:/var/lib/mysql
    restart: always
    env_file:
      - .env
    environment:
      MYSQL_DATABASE: blog_wp
    command: '--mysql-native-password=ON'
    networks:
      - backend

I ran docker compose pull to upgrade things and I got bit by MySQL 9.0 dropping support for mysql_native_password.

The Problem

When I started the container back up I saw a “Could not connect to database” error from Wordpress, and the database container was logging this error:

wp-allison-db  | 2024-11-20T20:28:42.057966Z 0 [ERROR] [MY-000067] [Server] unknown variable 'mysql-native-password=ON'.
wp-allison-db  | 2024-11-20T20:28:42.058678Z 0 [ERROR] [MY-010119] [Server] Aborting

Just turning off mysql_native_password doesn’t work because the users have mysql_native_password set as their plugin in the users table:

mysql> SELECT User, Host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| User             | Host      | plugin                |
+------------------+-----------+-----------------------+
| root             | %         | mysql_native_password |
| wordpress        | %         | mysql_native_password |

How To Fix It

To get out of this I needed to update the users and set their password again using the caching_sha2_password plugin.

First, turn off the grant tables for mysql by adding the --skip-grant-tables command to replace the --mysql-native-password command.

  wp-allison-db:
    image: mysql:latest
    container_name: wp-allison-db
    volumes:
      - ./db:/var/lib/mysql
    restart: always
    env_file:
      - .env
    environment:
      MYSQL_DATABASE: blog_wp
    command: '--skip-grant-tables --user=mysql'
    networks:
      - backend

Restart the docker container so that mysql starts with the new flag. Then, in another terminal, launch a bash session in your database container and connect to mysql:

$ docker exec -it wp-allison-db bash
bash-5.1# mysql -u root -p

Now that you’re in you can update the users to use the new authentication plugin:

ALTER USER 'root'@'%' IDENTIFIED WITH 'caching_sha2_password' BY 'your_root_password_here';
ALTER USER 'wordpress'@'%' IDENTIFIED WITH 'caching_sha2_password' BY 'your_password_here';
FLUSH PRIVILEGES;

Exit out of msql, remove the --skip-grant-tables command from your docker-compose, and restart the docker container. You should be back in business!