Replication in MySQL 5.7 GA

MySQL 5.7 has been recently declared Generally Available and in it there is a set of new replication features. Over the last 6 years MySQL replication has evolved at a tremendous pace. MySQL 5.6 brought us a lot of new replication features, many that were ground breaking and yes, sometimes controversial. At the same time it was a major leap forward both in terms of technology and in some cases an entire paradigm shift. MySQL 5.7 continued this trend and brings contains many new and exciting replication related functionality. Moreover, under the hood it also includes very interesting changes which make a few things faster and provide exotic low level interfaces in the replication framework.

You, as part of the MySQL community and/or as a MySQL user, have played a big role in this story as well. For instance, there were contributions processed and many of them accepted. In addition, there was a lot of feedback provided either through bug reports, feature requests, comments on blog posts, or even on  interactions on conferences or occasional meetings. You deserve a big Thank You!

So, what is it that makes MySQL 5.7 Replication so exciting? What is it that makes it so compelling? Well, lets try to get some answers to these questions by walking through just a few of the new replication features.

Improvements to Multi-threaded (Slave) Applier

A more general parallelization method was devised and implemented. By taking advantage of some information that the master puts in the binary log, the slave applier is now able to schedule transactions in parallel regardless of which schema they had originally updated on the master.

Improvements to Replication Monitoring

Replication (slave) applier was instrumented and stats exposed through performance schema tables. This also provides a new framework to observe and monitor the new features that have been put into MySQL  5.7 such as Multi-source Replication, for which SHOW commands would fall short or not scale at all.

Improvements to Semi-sync Replication

There is a new semi-sync mode which removes the possibility to loose updates in cases of master crashes. The user can now configure the master to wait for acknowledgments before committing transactions to the storage engine instead of waiting after the commit is done. This way there is no possibility for a concurrent read operation to externalize changes on the master before they are actually acknowledged as received by the slave.

There is also the possibility to instruct the server to wait for multiple acknowledgments instead of just one before returning the session control to the application.

Oh… And Semi-sync got faster!

Improvements to Global Transaction Identifiers

Global transaction identifiers simplify fail-over, replication stream positioning and protect against general user mistakes by auto-skipping transactions that were already processed. And yes, it was hard to turn it ON online in MySQL 5.6, but not anymore in MySQL 5.7. Now one can turn ON and OFF global transaction identifiers across a replication topology dynamically and without having to block the flow of read/write transactions. You can read more about it here.

Another interesting new feature related to global transaction identifiers is the fact that you can turn it ON on a slave without having to have the binary log enabled on that server. This allows the slave to take advantage of the auto-positioning and transaction auto-skipping features without requiring a binary log. Instead of storing identifiers in the binary log, they are stored on an InnoDB system table, thus stored transactionally in this case as well.

There is also a new MySQL function that allows the applications to explicitly wait for transactions to be applied. Furthermore, it is possible to instruct the server to track which transactions have been processed when a transaction is executed and return this information in the MySQL protocol return packet. This opens interesting possibilities to automate more easily, on the client side, the tracking of the replication data flow throughout a replication topology.

Improved Dynamic Reconfiguration Support

MySQL 5.7 introduces the ability to reconfigure slave replication filters without having to restart the server. It is also possible to reconnect to a new master without stopping the replication threads from applying the remaining log from the old master. In fact, one can even do the opposite. One can reconfigure the applier threads without stopping the threads that are connected to the master. This way we can reconfigure the applier and the receiver parts of the slave independently.

Multi-source Replication

Last but not least, there is also new functionality allowing a MySQL slave to replicate from multiple masters instead of just one. This brings a lot of flexibility when designing new replication setups using MySQL 5.7. This is a big change and opens new possibilities to deploy MySQL replication in more advanced replication setups.

A Long List of Smaller Enhancements

There are also a lot of smaller, nonetheless interesting, new features. XA is now supported when the binary log is ON. In particular an XA prepared transaction is no longer rolled back when the server shuts down and the binary log is active. Multi-threaded slave will retry transactions on transient errors and there is even an option to commit transactions in the same order they were committed on the master. mysqlbinlog now supports SSL and has database rewrite filters itself. Lots of refactorings and improvements to the codebase (new internal interfaces and modularization work were done).  There are also micro-optimizations that result in a more clever resource usage (fewer memory allocations on the master dump thread for instance). And there are even options to fine tune the binary log group commit pipeline. In a nutshell, a lot of small enhancements that make replication more robust and faster.

MySQL Group Replication Related Refactorings

In addition to all of these features, MySQL 5.7 Replication was also changed in such a way that enabled MySQL Group Replication plugin development and support. This work was very important since it created new replication interfaces and drove some of the modularization and refactoring work done throughout MySQL 5.7 lifecycle.

A Word on MySQL 5.7.9

So what are the actual replication highlights specific to MySQL 5.7.9 GA? For starters it is more robust than on the previous release candidates. Furthermore, we also found an interesting performance optimization opportunity when the multi-threaded slave applier was configured to operate with a low number of worker threads. In addition we also worked on fixing a couple of issues around concurrency control for administrative commands such as STOP SLAVE.

As you can see, very exciting times! 😉 If you want to try and get some hands-on experience on MySQL 5.7 Replication, feel free to download it and give it a spin. You can also have a look at the excellent online reference manual for additional details. Enjoy and have fun with MySQL in general and Replication in particular.

About Luis Soares

Luis Soares is a Principal Software Engineer and the MySQL Replication Team lead at Oracle. His research interests include replication technologies, dependable systems and high availability. Before joining the MySQL team, he was both a post-graduate student and a researcher at the University of Minho, Portugal, where he designed and implemented group-based replication protocols. Before that, he worked for a Portuguese multi-national company as a system analyst.

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter. * Time limit is exhausted. Please reload CAPTCHA.