After studying the differences between MySQL 5.6 and 5.7, and going through a vigorous regression test process, it’s now time for perform the actual upgrade itself. How do we best introduce 5.7 in our live environment? How can we minimize risks? What do we do if something goes wrong? And what tools are available out there to assist us?
The upgrade process
You will most likely perform a rolling upgrade - this means that you will upgrade one slave at a time, taking them out of rotation for the time needed to complete the upgrade. As the binary, in-place upgrade is supported for 5.6 -> 5.7, we can save lot of time by avoiding long dump and reload operations. This makes the upgrade process prompt and easy to perform.
One of the very important steps while performing a binary upgrade is that we need to disable innodb_fast_shutdown on the slave before we stop it for the upgrade. This is needed to avoid potential problems with InnoDB incompatibilities. You also have to remember to execute mysql_upgrade - another step required to fix some of the incompatibilities which may otherwise impact your workload.
The switchover process
Once you have upgraded the slaves, you’ll have to execute a switchover and promote one of the slaves as a master. How to do that, it’s up to you. If you use GTID in your replication setup, switchover and subsequent reconfiguration will be easier. Some external tools can also be used to make the switchover process smooth - for example, ClusterControl can do the switchover for you, along with all preparations like reslaving hosts, setting up grants, etc.. The only requirement is that GTID is used.
In the proxy layer, you can think about using ProxySQL which, when configured correctly, allows for fully graceful master switches where no error is sent to the application. When combined with ClusterControl, you can execute a fully automated switchover without any rollbacked transaction and very slight impact to the application.
The upgrade process along with how to achieve a graceful switchover is covered in more details in our ebook “Upgrading to MySQL 5.7”.