MySQL major version upgrade using Database Migration Service

As hopefully you all know by now, migrating your SQL database got easier this past November when Google’s Database Migration Service went GA for MySQL. If you didn’t know, migrating your SQL database to Cloud SQL is now easier! Something you might have noticed, or I’ll happily point out if you haven’t noticed, is that Database Migration Service lets you pull from a number of different sources, including on premises, AWS, and even Cloud SQL for MySQL.

Side plug - we just announced DMS supports Cloud SQL for PostgreSQL as a source, so you can use DMS for your PostgreSQL major version upgrades too. But THIS post is about MySQL...

Because something ELSE I wanted to call out... notice this, it appears on the “Create a destination” page of the DMS process:

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.

#1 thing is latency. If you’ve gotten comfortable having a near-instantaneous response from your database because it lived right next to your application, be prepared for a bit of a wakeup call. Unless your application also lives in the Cloud, there’s now some extra time that will pass between your application and your database. This might be totally fine! Or, it could introduce some really difficult-to-debug re-entrancy bugs into your application. A colleague of mine wrote a great post that covers all the things you need to watch out and potentially change for when moving your database to Cloud SQL from another source here.

#2 is how do I even now get to my database?! Network connectivity can be really difficult. Firewalls, routing and DNS can all throw a wrench in the works. You had everything all nicely connected before you did this, and now all of a sudden your database lives (potentially) in an entirely different part of the internet with different requirements to connect to it. This particular rabbit hole I’ve gone very deep on already. I wrote an in-depth blog post on connecting source to destination instances with Database Migration Service here. If you run into problems with connectivity, that post should cover most situations.

More migrating!

DMS makes the moving your data piece of the process easy, but since we rely on the database engine’s native migration mechanisms, we’re also subject to its limitations. This means there’s some steps to complete once you’re done migrating your database regardless of where it came from. For example, you know, your IP address will change. Because we’re using a migration method to upgrade our version, we’re instantiating a new instance. Once we get in-place upgrades this is a non-issue, but in the meantime we have to remember to update any applications, load-balancers, etc that are pointing at the old IP address.

Another thing is, don’t forget, your users are stored in system tables that aren’t migrated either. Those will need to be brought over once the DMS portion is complete. There’s a few more bits and bobs, and there’s a complete post here that lists the things you’ll need to finish off before your new instance is ready for use.

What’s next?

Now that you’ve got your newly upgraded instance all set, it’s time to get it ready for production. There are a few things that you’ll likely want that can’t be set up until the Cloud SQL instance is fully online.

By default, for example, SSL-only connections are off by default and we know many organizations enforce SSL-only connections. This will need to be enabled on the server and certificates will need to be set up. This can be done after the data is migrated but before the DMS target instance is promoted.

Disaster recovery is another must-have for production instances. It’s also a requirement for our SLA which guarantees uptime. Absolutely need to set this up. If you edit your instance it’ll be on the main Overview page:

Setting to “Multiple zones” enables this feature.

Also falling into this category are read replicas. Optimizing your data infrastructure to support your application is key and this is an integral part of that. If you have a read-heavy application, you’ll want to set up your read replicas at this stage of the game as well.

Setting up high availability and the read replicas can only be done after you’ve promoted the instance at the end of the DMS process.

One last bit before I leave you. You’ve done all this work to upgrade your instance to the next version. You’ve set up all the extra pieces you need. You’ve connected your application and it can totally talk to your database. Now is NOT the time to hit the push to prod button. This may absolutely come across as obvious to many of you reading this. But I’m going to say it anyway. TEST! Test all the things. Test all the edge cases you can think of. Test adding even more latency into your system arbitrarily to be sure that you can handle random internet lag spikes between application and database. The high availability feature once it’s on allows you to test automatic failover to simulate your primary instance going down and the fallback instance taking over. So test. Test all these scenarios to ensure that you have a smooth launch of your new major version SQL instance.

Hopefully this all helped to guide you to successfully using Database Migration Service to upgrade your MySQL instance to a new version! If you have questions or comments, please feel free to reach out to me on Twitter, my DMs are open! Thanks for reading.