How to upgrade WordPress to MySQL 8 on CentOS

Today we are going to show you how to upgrade WordPress to MySQL 8. This is what anyone who is in charge of managing the server can do.

If we were to look at the MySQL 8 upgrade notes, we would see that upgrading from a version older than 5.7.x is not supported and is not an option. We prefer to trust the MySQL team for this and not risk upgrading directly.

The steps for upgrading from 5.7 to 8.0 are very similar, so we’ll just go over them briefly. Except for a few caveats that you will need to pay attention to. We’ll elaborate on this below. Which leaves us with the following plan:

  1. Prepare to upgrade to the latest version 5.7.x
  2. Update to latest version 5.7.x
  3. Check the integrity of the database
  4. Prepare to upgrade to the latest 8.0.x
  5. Update to latest version 8.0.x
  6. Fix WordPress database connection
  7. Check the integrity of the database

So let’s just get straight to that.

Step 1. Prepare to upgrade to the latest 5.7.x version

Back up your DB

In our case, we chose cold backup over the traditional sqldump approach. With a cold backup, all you need to do is carefully shut down the MySQL server process and copy its data directory to a safe location:

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

Which produces a very nice .tar.gz archive of everything that MySQL stores in its repository. And this backup can be restored by simply unpacking this archive back into the MySQL data directory.

Get an update of the repository package

We always recommend using the official repositories to get service packs. In the case of MySQL, you need to go to http://repo.mysql.com/ and go to the package managers (yum in our case). Where can you find the release package that will add the repository to 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 the first command is remove, a previously added MySQL Release Repository. If you haven’t added it through your package manager, you can skip this command.

You now have everything ready for the update process.

Step 2. Upgrade MySQL to 5.7.x

Make an update

Updating the packages themselves is just as trivial:

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

Data transfer

We have noticed that many people stumble upon the fact that they also need to migrate data from old to newer versions! Without data migration, your database may become corrupted. In case of update 5.6 -> 5.7 we can transfer data without starting the server. This is done with:

$ sudo mysql_upgrade -u root -p

In general, data migration should go smoothly in most cases (if we follow the guidelines of the MySQL team here). This was done for us, but if you come across some error messages during this process, please let us know in the comments.

Step 3. Check the integrity of the database

We can now start our recently updated database. If everything above was done without any error messages, then this should just work:

$ sudo systemctl start mysqld

For a more thorough check, you can simply check the status of your website if WordPress can connect and is not showing any errors – we are all ready for this stage and can move on to upgrading to MySQL 8.

Step 4. Prepare to upgrade to the latest 8.0.x version

Back up again

This step is very similar to preparing to upgrade from 5.6 to 5.7. You MUST do the backup again as the data no longer matches:

$ sudo systemctl stop mysqld
$ sudo tar zcvf /backup/mysql-cold-backup.20191115.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

Make an update

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

Data transfer

Note So here is the first difference from previous updates – mysql_upgrade in 8.0.x requires a working server!

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

Same as before, if you come across error or warning messages at this point, go to the comments here.

Step 6. Fix the connection to the WordPress database

Now, after we updated MySQL to the latest version, our WordPress stopped working! All we see on my site page is this error message:

ErrorError establishing a database connection.

You can see this in our other article on How to Fix WordPress Database Connection for MySQL 8.

Step 7. Checking the integrity of the database

Since we have already started MySQL with the obvious steps, all we need to do is make sure our sites are loading and working properly.

Please let us know in the comments if you have any problems with this guide and if you need any additions or updates!

Sidebar