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.