MySQL upgrade done right

Ok, so out of a random urge to get a new version of mysql for my docker image which was showing  last updated 12 months ago, I changed the Dockerfile build to use the latest version of mysql. This means that I would go from 5.7 to 8.x. Well, of course I thought it would be as easy as using mysql:latest, restarting the container and all my databases would magically appear, guess not!

So, if you are like me and did the same mistake I did, hold on don’t freak out, your databases are not lost!

Here’s what you need to do:

Step 1 – run the container with new image like you would normally do except make the following change:

Add an argument to use native authentication plugin.

--default-authentication-plugin=mysql_native_password

If not done, you may receive an error that reads like the one below:

The server requested authentication method unknown to the client

Here’s an example docker run command with the argument added.

docker run -d \
   --name mysql\
   --restart=always \
   -v /somedir:/var/lib/mysql \
   -e "MYSQL_UID=2222" \
   -e "MYSQL_GID=2222" \
   -e "MYSQL_ROOT_PASSWORD=somerootpass" \
   -p 3306:3306 \
   vikramchauhan/mysql \
   --character-set-server=utf8mb4 \
   --collation-server=utf8mb4_unicode_ci \
   --default-authentication-plugin=mysql_native_password

The container should start and mysql should be available to accept connections

Step 2 – (this is important), run the following upgrade command:

docker exec -it yourmysqlcontainername mysql_upgrade -uroot -p

This is the magic you need to upgrade your databases from 5.7 to 8. Let it complete as it takes some time

Step 3 – restart your app containers that link to mysql container

Now you should be all set!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.