Andrew Morgan on Databases

MySQL 5.6 has now been declared Generally Available (i.e. suitable for production use). This is a very exciting release from a MySQL replication perspective with some big new features. These include:

• Global Transaction Identifiers (GTIDs) – a unique identifier that is used accross your replication topology to identify a transaction. Makes setting up and managing your cluster (including the promotion of a new master) far simpler and more reliable.
• Multi-threaded slaves (MTS) – Increases the performance of replication on the slave; different threads will handle applying events to different databases.
• Binary Log Group Commit – Improves replication performance on the master.
• Optimized Row Based Replication – reduces the amount of data that needs to be replicated; reducing network usage and potentially speeding up replication.
• Crash-Safe Replication – makes replication transactional. The data and replication positioning information are both updated as part of the same transaction.
• Replication Event Checksums – checks to ensure that the data being replicated hasn’t become corrupted, avoids writing corrupted data to the slave.
• Time-Delayed Replication – configure one or more of your slaves to only apply replicated events after a configured time. This can be useful to protect against DBA mistakes.
• Informational Logs – includes the original statement in the binary log when using row-based-replication to aid in debugging.

To coincide with the GA announcement, a new white paper has been released – MySQL 5.6 Replication An Introduction – that provides a lot more details on these new features. We’ve also released a second white paper – MySQL 5.6 Replication, Enhancing Scalability and Availability – A Tutorial that steps you through using MySQL Replication, with a particular focus on the new features.

If you’re already familiar with using MySQL Replication, here are a few pointers on what to do differently for the new MySQL 5.6 features but you should refer to the full tutorial to see these changes in context.

First of all, here are some extra configuration parameters to include…

[mysqld]
gtid-mode=on				# GTID
enforce-gtid-consistency=true		# GTID
master-info-repository=TABLE            # Chrash-safe replication
relay-log-info-repository=TABLE         # Chrash-safe replication
slave-parallel-workers=2                # MTS
binlog-checksum=CRC32                   # Checksums
master-verify-checksum=1                # Checksums
slave-sql-verify-checksum=1             # Checksums
binlog-rows-query-log_events=1          # Informational logs

When initiating (or restarting) replication on the slave, it is no longer necessary to include the binary log positioning information as the master and slave will automatically negotiate what outstanding events need to be replicated based on any GTIDs previously received by the slave…

slave> CHANGE MASTER TO MASTER_HOST='black', 
                        MASTER_USER='repl_user', 
                        MASTER_PASSWORD='billy', 
                        MASTER_AUTO_POSITION=1;

Please try out these new features and let us know what you think.