Need help?

Our experts will login to your server within 30 minutes to fix urgent issues.

Customer support or server support, make your own solution using our support configuration wizard.

MySQL 5.5: Advances in replication(II/II)

The MySQL replication team have been hard at work improving the features of MySQL replication. In this part, I’ll be talking about a few more features included in MySQL 5.5

Automatic Relay Log Recovery

If this features is enabled, when a Slave recovers from a crash, it will discard all unprocessed entries in its own relay log. It will then retrieve pending transactions from the Master. This ensures that incomplete/corrupt entries created during the crash are not processed.

This feature is disabled by default. To enable it, add the following entry to my.cnf:


Replication Per Server Filtering

In HA environments, multiple MySQL servers act as Masters, i.e. each Master is a Slave to another server in the cluster. This ensures the redundancy of data even if one server in the cluster fails, or is removed. MySQL updates as propagated from one Master to the next in the circle till it reaches back to the originating server, where it is terminated. In older versions of MySQL, if one of these servers failed, users had 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.

Let us assume we have MySQL servers A to D, replicating data in the order A -> B -> C -> D -> A. If B were to fail or is removed from the cluster, to filter out all server B events, the user simply has to enter the following commands on the next server in the calling chain(i.e. C):


Where IGNORE_SERVER_IDS takes a comma-separated list of 0 or more server IDs. Events originating from the corresponding servers are ignored, with the exception of log rotation and deletion events, which are still recorded in the relay log.

Replication Slave Side Data Type Conversions

MySQL 5.1 allowed data type conversion during Master to Slave replication, but only on a per statement basis. MySQL 5.5 now allows both statement and row based data type conversion. 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)

To find out more about replication in MySQL 5.5 and how to set it up, please refer to this URL. The MySQL 5.5 release candidate has been out since September, and can be downloaded here(Development Releases). As usual the date of general availability wont be announced till it is, but it shouldn’t be long. All major control panels should already be working on integration, so a couple of months after that, you should be able to offer it to your customers.

In recent benchmarks using the release candidate for MySQL 5.5, compared to MySQL 5.1, results demonstrated marked performance improvements:

*On Linux: up to 360 percent performance gain in Read/Write operations and up to 200 percent improvement in Read Only.
*On Windows: up to 1,500 percent performance gains for Read/Write operations, and up to 500 percent gain for Read Only.


About the Author:

Hamish joined Bobcares in July of 2004, and since then has grown to be well versed in the Control Panels and Operating systems used in the Web Hosting industry today. He is highly passionate about Linux and is a great evangelist of open-source. When not at work, he keeps himself busy populating this blog with both technical and non-technical posts. When he is not on his Xbox, he is an avid movie lover and critic

Bobcares is a server management company that helps businesses deliver uninterrupted and secure online services. Our engineers manage close to 51,500 servers that include virtualized servers, cloud infrastructure, physical server clusters, and more.