MySQL Development Milestone 5.6.2: Taking MySQL Replication to the Next Level

The announcements at the O'Reilly MySQL and Oracle Collaborate conferences last week mark an exciting milestone in the development of MySQL replication.  The purpose of this blog is to summarize those announcements and provide links to further reading, published directly by the engineers who have been working hard to take MySQL replication to the next level.

A number of significant replication enhancements were released as part of the MySQL 5.5 GA just a few months ago. 
We are always listening to our customers and the community.  And, based on their input, the MySQL engineering team has continued to rapidly evolve replication functionality - enabling new classes of applications to rely on MySQL for their fastest growing and most dynamic web services.

The latest replication functionality, delivered as part of MySQL 5.6.2 Development Milestone Release and through is available for evaluation now, the highlights of which are as follows:

Rock-solid data integrity: Crash-Safe Slaves and Replication Event Checksums
High performance: Multi-Threaded Slaves and Optimized Row Based Replication
Increased flexibility: Time-Delayed Replication, Multi-Source Replication and Informational Log Events
Enhanced ease-of-use: Remote Binary Log Backup and Server UUIDs

So what do all of these enhancements do, and why should you care?  

Hopefully the following will answer those questions, as well as providing you links to the blogs written by the engineers themselves who will discuss the implementation detail - and wax lyrical about the fruits of their labor....

Crash-Safe Slaves (aka Transactional Replication)
Extends the robustness and ease-of-use of MySQL replication by making the slaves crash-safe when using transactional storage engines such as InnoDB.

The slave can automatically recover from a failure and resume replicating DML updates, without the DBA having to access the and files to manually roll back replication to the last successfully committed transaction, or to skip transactions.  

As a result, data integrity is enhanced and DBAs can be free to concentrate on more strategic data management activities.
Learn more >>

Replication Event Checksums
Ensures the integrity of data being replicated to a slave by detecting data corruption and returning an error, preventing the slave itself from becoming corrupt.  

Checksums are implemented in the binary and relay logs as well as to individual replication events, allowing errors to be detected whether they are caused by memory, disk or network failures, or by the database itself.  Checksum checking can be implemented on a per-slave basis, giving maximum flexibility in how and where it is deployed.  
Learn more >>

Multi-Threaded Slaves
Replication performance is improved by using multiple execution threads to apply replication events to slave servers. The multi-threaded slave splits work between worker threads based on the database name, allowing updates to be applied in parallel rather than sequentially.

Multi-Threaded Slave Implementation

As a result, replication throughput is increased and latency is reduced which minimizes the risk of replication lag, enabling slaves to serve the freshest updates to the application.

The multi-threaded slave functionality is available now as part of the "mysql-5.6-labs-multi-threaded-slaves" build which can be downloaded from
Learn more >>

Optimized Row-Based Replication
By only replicating partial 'before' and 'after' images for INSERT, UPDATE and DELETE events where primary keys and/or explicit columns were set in the SQL statement, performance can be increased while binary log disk space, network resource and
server memory footprint are all reduced.

More detail is available as follows:

Time-Delayed Replication
The user can define a time delay for events to be replicated from a master to each slave, defined in millisecond increments up to a maximum of 68 years!

Time-Delayed Replication affords protection against operational errors made on the master, for example accidently dropping tables, in which event the slave can be promoted to the new master in order to restore the database to its previous state. It is possible to inspect the state of a database without reloading a back-up, and to testing application behavior by emulating any instances of replication lag that might occur during unexpected peaks.

Time-Delayed Replication is implemented at the per-slave level (via holding execution of the SQL_THREAD), so a user could configure multiple slaves to apply replication events immediately, and another slave to apply only after a delay of 5 minutes, therefore providing deployment flexibility.

Immediate and Delayed Replication

More detail is available as follows:

Informational Log Events
Enhances auditing and debugging when using Row-Based Replication by writing the original query to the binary log, which is then replicated with its associated row-based event to the slave.
Learn more >>
This feature is also documented here

Round-Robin Multi-Source Replication in Pure SQL
With the addition of the new tables to implement crash-safe replication - discussed above - we also get access to replication information through the SQL interface that can be used to implement multi-source round-robin replication. However, compared to other implementations that require a client to parse the output of SHOW SLAVE STATUS, this implementation is made in the server, using pure SQL.
Learn more >>

Remote Binlog Back-up
Enhances operational efficiency by using the replication channel to create real-time back-ups from the binary log.

By adding a "raw" flag, the binlog is written out to remote back-up servers, without having a MySQL database instance translating it into SQL statements, and without the DBA needing SSH access to each master server.

Server UUIDs
NOTE:  This is not the same as Global Transaction IDs and we plan to preview in a later Milestone Release

Server UUIDs enhance auto-discovery and analysis of replication topologies when using the MySQL Enterprise Monitor by automatically generating a Universally Unique Identifier (UUID) for each server. This is especially useful in large and highly dynamic replication environments, making auto-discovery more reliable and simplifying systems management.

MySQL Workbench Utilities
In addition to the enhancements discussed above, there are some great new external tools for managing MySQL servers. These are available in MySQL Workbench under the name "MySQL Workbench Utilities".

These utilities encapsulate a set of primitive commands bundling them so that you can perform macro operations with a single command, and include some great tools for MySQL replication.

Some of the key features of MySQL Workbench Utilities are:
    * Plug-in for MySQL Workbench 5.2.31
    * Available under the GPLv2 license
    * Written in Python
    * Easily to extend using the supplied library
Learn more >>

So in summary, some of these enhancements are big - in fact they don't get much bigger - while some may have appeal to only a select range of use-cases.

Either way, you can download and evaluate them now - select the Development Releases Tab, or in the case of Multi-Threaded Slaves, from (select "mysql-5.6-labs-multi-threaded-slaves") .

Let us know what you think of these enhancements directly in comments for each blog. We look forward to working with the community to perfect these new features.


Post a Comment:
  • HTML Syntax: NOT allowed

Get the latest updates on products, technology, news, events, webcasts, customers and more.




« July 2016