Alex blog All about IT!

How to Upgrade WordPress to MySQL 8

So today I’ve been upgrading a bunch of my servers to install latest releases of software, which is why I needed to upgrade wordpress to MySQL 8. Which is a thing that everyone who is responsible for managing number of servers is supposed to do once in awhile. A topic of struggle and pain of dealing with servers that were not updated for a couple of years could last me through 10 articles I am sure.

In my case the servers have been running something old enough of MySQL 5.6.x variety. If we would go to MySQL 8 upgrade notes, we would see that upgrade from anything older than 5.7.x is not supported and not an option. I rather choose to believe MySQL team on that and not risk it upgrading directly.

The steps for upgrading from 5.7 to 8.0 are in many ways similar, so I will just go through them very briefly. Except a few caveats to which you will need to pay attention. I will stop in details on that one below. Which leaves us with following plan:

  1. Prepare for upgrade to latest 5.7.x
  2. Upgrade to latest 5.7.x
  3. Verify database integrity
  4. Prepare for upgrade to latest 8.0.x
  5. Upgrade to latest 8.0.x
  6. Fix WordPress database connection
  7. Verify database integrity

So let’s just get straight to it.

Step 1. Prepare for upgrade to latest 5.7.x

Make a backup of your DB

In my case I opted for cold backup as opposed to more traditional sqldump approach. With cold backup all you need to is to cleanly shutdown MySQL server process, and copy its data directory to safe location:

$ sudo systemctl stop mysqld
$ sudo tar zcvf /backup/mysql-cold-backup.20181027.001.tar.gz /var/lib/mysql

Which produces very nice .tar.gz archive of everything that MySQL keeps in its storage. And this backup can be restored by simply unpacking this archive back to MySQL data directory.

Get upgrade package repository

I always recommend using official repos for getting the upgrade packages from. In case of MySQL you would need to navigate to http://repo.mysql.com/ and go to your flavor of package manager (which is yum in my case). Where you will find a release package that will add repository on the server:

$ sudo yum remove mysql-community-release
$ sudo yum install http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql57-community-release-el7-10.noarch.rpm
$ sudo yum makecache

Note that first command is to remove previously added MySQL release repository. If you did not add it via package manager, then you may skip this command.

Now you have everything ready for upgrade process

Step 2. Upgrade MySQL to 5.7.x

Do the upgrade

Upgrading packages themselves is as trivial as:

$ sudo systemctl stop mysqld
$ sudo yum update mysql-community-server mysql-community-client

Migrate data

I noticed that many people stumble on the fact that you also need to migrate data from older to newer release! Without migrating data your database may become corrupted. In case of 5.6 -> 5.7 upgrade, we can migrate data without starting server. This is done using:

$ sudo mysql_upgrade -u root -p

In general, most of the time migrating data should go smoothly (as long as we follow MySQL team recommendations here). It did so for me, but if you stumble on some error messages during this process please let me know in comments so we can investigate this and let others know.

Step 3. Verify database integrity

Now we can start our newly upgraded database. If everything above was done without any error messages, then it should just work:

$ sudo systemctl start mysqld

To do more thorough verification you can simply check your website status, if WordPress can connect and does not show any errors — we’re all set for this stage and can move on to upgrading to MySQL 8.

Step 4. Prepare for upgrade to latest 8.0.x

Do the backup again

This step is very similar to the preparation for upgrade from 5.6 to 5.7. You MUST take a backup again as data is not the same anymore:

$ sudo systemctl stop mysqld
$ sudo tar zcvf /backup/mysql-cold-backup.20181027.002.tar.gz /var/lib/mysql

Remove old and add new package repository

$ sudo yum remove mysql-community-release
$ sudo yum install http://repo.mysql.com/yum/mysql-8.0-community/el/7/x86_64/mysql80-community-release-el7-1.noarch.rpm
$ sudo yum makecache

Step 5. Upgrade MySQL to 8.0.x

Do the upgrade

$ sudo systemctl stop mysqld
$ sudo yum update mysql-community-server mysql-community-client

Migrate data

NOTE: So here is the first difference from previous upgrades – mysql_upgrade in version 8.0.x needs running server!

$ sudo systemctl start mysqld
$ sudo mysql_upgrade -u root -p

Same as before if at this stage you encounter any error messages or warnings please proceed to comments here.

Step 6. Fix WordPress database connection

Now after we’ve upgraded to latest MySQL 8 our WordPress stopped working! All I can see on my website page is this error message:

Error establishing a database connection.

You can see it in my other article on how to fix WordPress database connection for MySQL 8.

Step 7. Verify database integrity

Since we have already started MySQL in pevious steps, all that needs to be done is to verify that our sites load and work correctly.

Please let me know in the comments if you experienced any issues with this guide and if it needs any additions or updates!

1 comment

  • Spencer Wong on 06:19 #

    Awesome post! You are very intelligent about this! 🙂

Leave a Reply

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

%d bloggers like this: