How to fix mysql-native-password errors with MySQL 9.0 and Wordpress
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!