Stumbling Blocks

MySQL Logical Upgrade to a New Host

The process for a logical upgrade of a MySQL server is quite straightforward and entails dumping the server with a tool such as mysqldump and then restoring it. In this article, I use mysqldump, but it's worth noting that you can get a speed boost from tools such as mydumper that use parallel backup and loading. Although not an officially supported approach, some have been able to upgrade directly from much older versions of MySQL directly to 8.0 in this way. If you choose this path, definitely test it thoroughly ahead of time.

For the sake of this example, assume you are using two instances – VMs, physical machines, or containers, it doesn't matter. One server will be the pre-existing MySQL 5.7 server, and the other will be your new MySQL 8.0 server. Technically, you can do this process on the same machine, but it's best to use separate instances if possible to make handling difficulties easier, as you will see.

Typically, you first take a backup from the old server by running the command

mysqldump -u root -p --all-databases | gzip > backup.sql
sudo systemctl mysql-server stop

Strictly speaking, the second command is not necessary but can be helpful to ensure no traffic is incorrectly routed to the old machine. Next, install a fresh copy of MySQL server 8.0, transfer the backup.sql file you made earlier, then run the commands

sudo systemctl mysql-server start
gunzip < backup.sql.gz | mysql -u root -p

If all goes well, this backup will be restored seamlessly. If not, for smaller databases, you can manually edit offending statements by running

gunzip backup.sql.gz

and then editing the file with a text editor. For larger databases, you can simply restart the original MySQL server on the original host, adjust or remove any offending features, and then re-dump and import – repeating the process as necessary until the restore succeeds.

In many cases, however, this procedure will go smoothly, and you can then switch traffic to your newly minted MySQL 8.0 server.

Replication Topologies

In this article, I've discussed upgrading a server from MySQL 5.7 to 8.0. Of course, many organizations have long outgrown a single server and use a cluster or clusters of MySQL servers.

You can employ a similar process to handle any upgrade of any MySQL topology, in which you test applications, test the database, backup, upgrade, backup, and then resume processing transactions. Of course, this process becomes more complex to manage with more machines.

Note that a MySQL 8.0 machine can replicate from a 5.7 machine, but not vice versa. You can, for example, add a new 8.0 replica to an existing 5.7 source to verify correctness – perhaps through the use of the pt-upgrade tool mentioned earlier.

Similarly, you can upgrade your 5.7 replicas one at a time before taking down your 5.7 source and upgrading that. In this way, you can reduce downtime because your source – and therefore your cluster – only needs to be down for a relatively brief period of time.

Finally, note that cloud products like Amazon Relational Database Service (RDS) and Google Cloud SQL typically have their own routines for handling the 5.7 to 8.0 upgrade; under the hood, it will likely be similar to the approaches discussed here, but the interface and procedures used will vary, so you should follow the procedures outlined in the vendor documentation.

Conclusion

Generally, the changes implemented in MySQL 8.0 are for the best (e.g., utf8mb4 is a better default than latin1); nevertheless, they could pose significant obstacles for the unwary. If you aren't prepared for an upgrade now, the other option is to team up with one of the vendors offering extended MySQL 5.7 support beyond the official EOL date.

Fortunately, although it might be rather difficult to avoid death and taxes, an ill-planned MySQL upgrade is one thing you can certainly avoid. With a little foresight, you can confidently and successfully pull off your upgrade.

This article was made possible by support from Percona LLC, through Linux New Media's Topic Subsidy Program (https://www.linuxnewmedia.com/Topic_Subsidy).