MySQL major version upgrade using Database Migration Service

So if you’re following along here, I’m telling you that you can ALSO use DMS to do major version upgrades on your MySQL database. We’ll all still wait with baited breath for in-place upgrades of course, but while we’re waiting this is a great option.

There’s a catch, because of course there is. The key here is that because we’re using a migration tool to upgrade, there’s two dimensions of complications to deal with. We have the migration piece and the upgrade piece. DMS helps manage a good amount of this, but there’s still some things you need to consider. In this post, I’m going to pull together all the pieces you need to think about and link to everything you need to do a major version upgrade for MySQL using Database Migration Service.

Why are we here?

Just to cover why you might want to upgrade MySQL in the first place. Between performance upgrades and feature updates, there’s plenty of reasons to do it. If you need convincing, here’s a nice list of the enhancements in MySQL 8.0. There’s also the elephant in the room: officially 5.6 was deprecated this past February. You might have been on 5.6 for the last 8 years, saw it’s officially end-of-lifed this year and are panicking a little bit. The good news here is that Cloud SQL will support 5.6 for a while longer, but that doesn’t mean now isn’t a good time to upgrade.

Version compatibility

The first thing to look at is what versions you’re upgrading between. So 5.6 to 5.7 or 5.7 to 8.0. 5.6 to 8.0 is right out. MySQL has significant changes between major versions that are likely to break compatibility, so you need to triple check your database for some of these incompatible changes.

For example, between 5.6 and 5.7 you need to keep an eye out for any system or status variables in the INFORMATION_SCHEMA tables in your 5.6 database. Those were all replaced by the Performance Schema tables in 5.7.6. There are also a lot of little things-like if you have a column with the YEAR(2) data type, you’ll need to update all those values to a 4-digit YEAR column before you’ll be able to use those columns again. If you’re going 5.6 to 5.7, you can go over the full list of changes here.

Between 5.7 and 8.0 of course there are yet more changes to watch out for. Big ones for me are that default flags were changed quite a bit between the two. While a lot of them might not break things with a segmentation fault, they could lead to some unintended behavior in your application. Also, you might want to take a peek at your AUTO_INCREMENT columns. It’s been deprecated for FLOAT and DOUBLE types. For a full list of what changed between these two versions, check out here.

Connecting the dots

If you’re planning on doing the major version upgrade from one Cloud SQL instance to another, you can skip this section because you’ve already done what I’m going to talk about. If, however, you’re changing from an on premises database, or in some small edge cases, a GCE (virtual machine) instance to Cloud SQL, there’s a few extra things to watch out for.