How to Perform an Online Upgrade of Percona XtraDB Cluster to Percona XtraDB Cluster 8.0

Percona XtraDB Cluster based on MySQL 8.0 has been out there for some time, so it’s not surprising more and more people are attempting to migrate into it. You may wonder how to approach this process – in this short blog post we would like to give you some suggestions on how to handle the upgrade of Galera Cluster based on MySQL 5.7 to Galera Cluster based on MySQL 8.0.

First things first, you may have noticed we are talking about specific versions of Percona XtraDB Cluster – 5.7 into 8.0. This is because Galera, on which PXC is based, is an addition to MySQL and as such it has to follow all the guidelines for MySQL upgrade. Those guidelines are clear – the only supported upgrade to MySQL 8.0 is from MySQL 5.7. As the first step you have to ensure that there are no roadblocks on the MySQL side. You can refer to our previous blog post, in which we shared some tips related to upgrading MySQL from 5.7 to 8.0. Make yourself familiar with the differences between those versions, verify that neither of the incompatible changes really impact your application. Once this is confirmed, ideally through tests, we can start planning the upgrade of PXC. There are two major ways of performing the online upgrade for Galera Cluster, let’s take a look at them now.

In-Place Upgrade

While not recommended, with some it is possible to have both PXC 5.7 and PXC 8.0 nodes running in the same cluster, this allows us to perform an in-place upgrade in which we will upgrade nodes in the cluster one by one, resulting in a cluster running on PXC 8.0. There are a couple of gotchas you have to keep in mind while doing so. For starters, as we said, you have to tick all the boxes on the MySQL 8.0 upgrade checklist. Then there are a couple of steps you have to take.

First, download the percona-release package:

wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb

and install it:

dpkg -i percona-release_latest.generic_all.deb

Then, enable Percona XtraDB Cluster 8.0 in the repositories: 

percona-release enable pxc-80 release

Finally, update apt and install percona-xtradb-cluster package:

apt update

apt install percona-xtradb-cluster

While installing, you will be presented with two dialog boxes.

In the first one you can decide if you want to enable caching_sha2_password or you want to use the old mechanism for hashing passwords. For the compatibility sake we decided to use the old one for now – this is something you can always change at the later time.

The next dialog asks if we want to use existing my.cnf or replace it. Please keep in mind that in PXC 8.0 the structure of the configuration files in /etc/ directory has changed. We would probably want to keep the old my.cnf as it contains working configuration for our cluster, some tuned settings etc, but eventually this would have to be reviewed and migrated into new configuration file structure.

After PXC 8.0 is installed, before you start it, there are a couple of settings you want to check in your my.cnf. Those are changes from standard my.cnf generated by ClusterControl, your mileage may vary therefore look out for the error log in case you won’t be able to start PXC 8.0.

For starters, this will be a mixed cluster for a while therefore we have to disable cluster traffic encryption added in PXC 8.0:

pxc-encrypt-cluster-traffic=OFF

Then we had to disable following settings that were incompatible with PXC (or MySQL) 8.0:

#log_warnings=2

#ignore-db-dir=lost+found

#ignore-db-dir=.s9s_do_not_delete

#query_cache_type = 0

#query_cache_size = 0

#wsrep_convert_LOCK_to_trx=0

#wsrep_drupal_282555_workaround=0

#wsrep_causal_reads=0

#wsrep_sst_auth=user:pass

Please also make sure that you have set:

read_only=OFF

super_read_only=OFF

This may not be the case if the cluster you are upgrading has been created through the “Create Slave Cluster” job in ClusterControl.

That’s pretty much all – start PXC:

systemctl start mysql

It should finish just fine. In case you run into any sorts of troubles, try to force SST – remove the data directory on the node that you upgrade and start the mysqld process again. Please keep in mind that SST from PXC 5.7 to PXC 8.0 works just fine.

The Slave Cluster Approach

With more free nodes available, you can approach the upgrade differently: two clusters on different major versions running in parallel and connected through asynchronous replication. The main advantage of this approach is that it will let you test the PXC 8.0 more thoroughly as you will have it up and running for a while (basically for as long as you need it) and you can test your application on this cluster. At any point in time you can move some of the read-only workload to the PXC 8.0 seeing how queries perform, looking for possible errors or performance problems. If you use ClusterControl, this can be accomplished by using the “Create Slave Cluster” job.

You will be asked to decide where the initial data should come from, master PXC node or from the backup.

You will also need to pass the connectivity details like SSH user and key path.

Then you will be asked to pick the vendor and version – you probably want to use PXC 5.7 for now, you will upgrade the nodes later, testing the upgrade process itself.

Finally, you have to pass the node hostnames or IP addresses for ClusterControl to connect to and start setting the nodes up.

As the result you will have a second Percona XtraDB Cluster running in version 5.7, replicating from the original cluster. That cluster can be upgraded to the new version, as we discussed in the “In-place upgrade” section and, eventually, you can switch your traffic towards it and deprecate the old cluster.

In case of any problems with the replication link, you should be able to recreate it easily with CHANGE MASTER TO … command, executed using the credentials used for the initial setup (if this has been done from ClusterControl, you’ll see them in /etc/cmon.d/cmon_X.cnf as repl_user and repl_password, where X is the cluster ID of the master cluster).

We hope this short blog post will help you to go through the process of upgrading Percona XtraDB Cluster from version 5.7 to version 8.0.