Two version upgrade fun with MySQL 5.5 to 5.7

In a perfect world, one would upgrade databases one version at a time and not let them get too old.  But our databases are where the “crown jewels” are.  They must stay up 24×7.  When performance is acceptable, it’s acceptable, and sometimes old versions stay around too long.  We don’t live in a perfect world.  This idea applies to so many things.  There’s almost never a perfect data model.  There is always some type of resource constraint be it storage, memory, CPU, IOPS, or just plain dollars.

I will bring this concept of not living in a perfect world into a discussion about upgrades.

Ideally there would be…

  • …time to do two upgrades.  One upgrade to 5.6, the other to 5.7.   This is the way sane, normal people upgrade.
  • …a lot of extra hardware.  It sure would be nice to maybe combine a maintenance like this with a hardware refresh so that we could just have an entirely new MySQL 5.7 database shard, slap a MySQL 5.6 box in the middle as a relay, and once things are all caught up just do a VIP cutover to new machines.

The extra hardware idea I have actually worked with in the past.  A client of a company I worked for just had a standalone 5.5 machine with no replicants.  They were able to allocate two more servers, one to be a 5.6 relay and one new 5.7 machine that would serve as a final destination.  Added bonus… At the end the 5.6 server would be upgraded and left in place for a redundant machine.

In the absence of extra time and extra money / hardware resources, sometimes a little creativity is required, which brings me to an example of the most interesting 5.5 to 5.7 upgrade process I have been a part of.  However, it’s not “that creative”, but it does involve being willing to accept single points of failure for short periods of time.

The beginning setup is a MySQL 5.5 master-master pair in the primary site (machines 1&2 in 1st diagram below).  However when I say “master-master” pair, machine 1 is getting all the traffic.  Machine 3 is a secondary site master and Machine 4 is the secondary site slave.  And a side note: I am intentionally keeping this high level, so there are more steps involved than described here.

We upgrade machine 4 to be a 5.6 server and allow its slave to catch up.  We use a method of binary backup and restore to backup and restore machine 4 to machines 2 and 3.  Machines 2 and 3 become MySQL 5.7 servers.  After this is done the replication chain looks like this.

Then, we cutover production traffic to machine two, then use a binary backup/restore method to restore a copy of machine 3 to machines 1 and 4.  In the end we look just like we started, except now we’re all upgraded to MySQL 5.7.

What are some other interesting upgrade paths?  This is a fun topic that I enjoy hearing about in the community.  Cheers.

2 thoughts on “Two version upgrade fun with MySQL 5.5 to 5.7

    1. Updating my original reply:

      5.6 in the middle is the only “supported” way. You can also run into unforeseen problems depending on query content and whether you’re using STATEMENT, MIXED, or ROW binlog_format. STATEMENT is very forgiving but you can run into problems with the other two trying to replicate directly from 5.5 to 5.7. Some environments cannot use MHA / DB servers are on different subnets, etc. I agree: ideal scenario is a simple cutover. We don’t live in perfect worlds! Thanks for the feedback.

      It looks like I was partially wrong about 5.5 to 5.7 replication. This bug which points to this bug has been fixed (prior to the fix, this would prevent 5.5->5.7 setups), so technically MySQL will let you hook up and run a 5.7 slave to a 5.5 master. In a lab setting I was unable to get 5.5->5.7 replication to break with some very rudimentary checks. Also my sentence above may actually be the opposite. STATEMENT based replication could cause more issues depending on what statements are being run through replication. See documentation for more detail, and of course, test in your particular setup.

      Like

Comments are closed.