I wanted to try and upgrade my MySQL database from 5.7 to 8.0 however I have containerised my database install. What I thought was a immutable container would be a simple burn and switch turned out to be a little more involved.
Persistence in containers has never been an easy thing to manage in my opinion and serves well to enforce the layers of abstraction.
For database, I have the data for the schemas themselves as well as the configuration for the server such as logins etc. In MySQL, these are stored in
Run the upgrade checker to make sure MySQL is running with the latest configuration. This should run automatically when a new version of the container is started but it never hurts to check.
With the container running, run the following:
docker exec -it [container] mysql_upgrade -uroot -p
Replacing [container] with the name or short hash reference. If you’re using my configuration then the command would be:
docker exec -it mysql mysql_upgrade -uroot -p
It will prompt for the root password. Give the command time to run. It can take a wile depending on the size of your database.
Next step is to shutdown the existing container, change the image to the version 8 and start it up.
WARNING: Please backup before going ahead!
I won’t detail the steps here because if you’re at this point I will assume you have setup your own container before.
When I tried to bring up MySQL 8.0 container it would exit with a message in docker logs along the lines of:
Upgrade after a crash is not supported. This redo log was created with MySQL 5.7.29
This meant prior to the upgrade, the container didn’t shutdown properly.
To diagnose this, start the container with the older version e.g 5.7 again but don’t start it in daemon mode i.e
sudo docker-compose up
(not the missing -d).
Once it’s up and running (please check by connecting to the database) from another terminal, issue the stop command:
sudo docker-compose stop
Watch for the exist code in the first window. For mine it came up with Exit code 137.
To get the upgrade to work I had to “remove” (I moved them) the checkpoint files:
mv ib_logfile* ~/
Start the container in the old version. Wait for the server to come up successfully and then stop the container again.
After that was done, MySQL 8.0 started up and upgraded the database.
Not the easiest and smoothest way to an upgrade but a little massaging got the server back up again.