MySQL 5.5: What's New in Replication

In my continuing MySQL 5.5 blog series, today I am covering what's new on the replication front.  MySQL replication is my favorite server feature and what drew me to MySQL during my tenure with Embarcadero Technologies.  Others seem to agree as based on community and customer surveys, MySQL replication is the most popular and widely used database feature.  Mostly because it is easy to set up and ease, it enables scalability and provides a pretty robust solution for data redundancy, backup and overall availability.  In MySQL 5.5 replication has been enhanced in response to user requests that MySQL replication:

  • Ensure data consistency between master and slave servers
  • Immediately detect if replication is not working
  • Allow a crashed slave to automatically recover from the master relay log
  • Allow users to filter events for specific servers
  • Correctly convert data types between master and slave servers

MySQL 5.5 replication includes the following enhancements that support users in these key areas:
Semi-synchronous Replication

MySQL replication is asynchronous by default meaning that a master and its slave/slaves are autonomous when it comes to data consistency.  Asynchronous replication provides optimal performance because a master is free to service other inbound transactional requests after
writing updates to its Binlog without waiting to verify that updates have been replicated to at least one slave in the topology.  While fast, this comes with a high risk of master/slave data inconsistency or even data loss at recovery if there is a failure on either end.  

MySQL 5.5 introduces semi-synchronous replication to ensure data consistency and redundancy between master and at least one slave in the calling chain.  In this configuration, a master and any number of its replicant slaves are configured so that at least one slave in the replication topology must acknowledge updates have been received and written to its relay log before the parent master commits the transaction.  In the event of a time-out, the originating master will temporarily switch to asynchronous replication until at least one of the slaves set up for semi-synchronous replication catches up.

Semi-synchronous replication must be enabled on both the master and slave servers, otherwise the master defaults to asynchronous replication.  MySQL 5.5 uses a new plug-in architecture to enable semi-synchronous replication.  To this end, the following commands and variable settings are used to enable 5.5 masters and slaves.  Static settings can also be added to the my.* configuration files:

To enable the semi-synchronous replicator on the master:

INSTALL PLUGIN 'rpl_semi_sync_master' SONAME '';
SET rpl_semi_sync_master_enabled=1;
SET rpl_semi_sync_master_timeout=1000; (1s, default 10s)

To enable the semi-synchronous replicator on a slave or slaves:

INSTALL PLUGIN 'rpl_semi_sync_slave' SONAME '';
SET rpl_semi_sync_slave_enabled=1;

Once enabled semi-synchronous replication exposes new system and status variables that can be used to check on configuration and operational status.  The values for each are exposed using SHOW VARIABLES and SHOW STATUS.  These include:
On master:

  • Rpl_semi_sync_master_status - indicates status of when master is using asynchronous or semi-sycnhronous replication.
  • Rpl_semi_sync_master_clients - shows how many slaves are configured for semi-synchronous replication.
  • Rpl_semi_sync_master_yes_tx - shows number of successfully acknowledged commits by slaves.
  • Rpl_semi_sync_master_no_tx - shows number of unsuccessfully acknowledged commits by slaves.

On Slave:

  • Rpl_semi_sync_slave_status - indicates if semi-synchronous replication is enabled on slave.

Replication Heartbeat

MySQL 5.5 provides a new replication heartbeat option that helps users know immediately when replication stops working.  The heartbeat is a message sent at regular intervals from a master node to slave nodes.  The slave can be configured to automatically check connection and message status; if the message is not received by the slave the slave knows that a connection to the master node has failed in some way. 

Replication heartbeat is an optional configuration and is enabled on the 5.5 slave using:

CHANGE MASTER TO master_heartbeat_period= milliseconds;

The following status variables can then be monitored to easily detect when a master is idle and to get a finer-grained estimate on slave seconds behind master for recovery purposes:

SHOW STATUS like 'slave_heartbeat period'
SHOW STATUS like 'slave_received_heartbeats'

Automatic Relay Log Recovery

MySQL 5.5 ensures master/slave consistency on a restart by allowing replication users to optionally configure slaves to automatically discard its own unprocessed relay logs and then recover pending transactions from the originating master.  This can be used after a slave crash to ensure that potentially corrupted relay logs are not processed.   For compatibility the default for this is disabled, but can be set using the new relay_log_recovery=value to 1 on the slave to be recovered.

Replication Per Server Filtering

Circular, or multi-master replication, provides a highly available deployment that ensures redundancy of data in the case any of the servers in a topology ring fails or is removed.  In this configuration master servers are configured so that each is also a slave of another server in the topology.  Updates written to any of the masters are then replicated around the ring until the transaction reaches the originating server which acts as the terminator of its own events.  In the event of a node failure the affected server is removed from the topology and its slave is simply redirected to another master in the ring and processing then continues.

In previous versions when a server is removed from the ring due to failure, maintenance, etc. users needed to manually ensure that all of its updates were terminated from the new calling chain.  MySQL 5.5 provides a new set of time-saving commands that allow users to easily filter out any events related to a removed server.


In the above case, when Server A is removed from the topology, users can now easily filter any Server A related events by entering the following command on the next server in the calling chain:


Replication Slave Side Data Type Conversions

In MySQL 5.1 precise data type conversions between master and slave are supported for statement-based replication only.  In this configuration column types could be different in the master and slave tables as long as the underlying data had high level compatibility (INT to TINYINT for example).  MySQL 5.5 now provides precise data type conversions between master and slave for both statement-based and row-based operations.   Conversions within integer, decimal, string, binary, BIT, ENUM and SET domains are supported.
A new SET variable in 5.5 enables the conversion, and requires that the slave be restarted to take effect.  The settings and what they enable are:

  • SET SLAVE_TYPE_CONVERSIONS="ALL_LOSSY' - enables conversions to types with smaller domain (INT to TINY for example)
  • SET SLAVE_TYPE_CONVERSION="ALL_NON_LOSSY" - enables conversions to types with larger domain (TINY to INT for example)

The above is not exhaustive so you can learn more about all of the new MySQL 5.5 replication features, including how to enable, monitor and tune them, here.

We have also published a new whitepaper and recorded webinar that cover the new replication and high availability features in 5.5.  I hope you enjoy them.  

Thanks for reading as always THANK YOU for helping make MySQL a better MySQL!


Excellent summary, Rob.

Posted by Giuseppe Maxia on November 15, 2010 at 11:34 PM GMT #

Giuseppe! You were the trail blazer on many of these new features, so THANK YOU for your leadership and activism in the MySQL Community!

Posted by Rob Young on November 16, 2010 at 06:49 AM GMT #

sounds good

I haven't got a chance to play with MySQL 5.5. replication yet but this sounds awesome

Posted by Shahryar Ghazi on November 22, 2010 at 08:30 AM GMT #

Does MySQL replication support Oracle11g Database as target? Or we have to use Goldengate for that?

Posted by guest on November 26, 2011 at 01:21 PM GMT #

Native MySQL replication supports MySQL to MySQL replication. New additions in 5.6 include the BinLog API that allows for heterogeneous use of MySQL data, but requires a custom solution to consume the data. Oracle Golden Gate supports easy to use bi-directional replication between MySQL and Oracle 11g.

Posted by Rob Young on November 28, 2011 at 09:19 AM GMT #

Post a Comment:
  • HTML Syntax: NOT allowed

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




« July 2016