Third party solutions for master-master replication in MySQL

Two of the more popular solutions areĀ  MySQL Master HA and Percona Replication Manager.

MySQL Master HA

MHA is based on a set of Perl scripts that monitors for replication and server health. When a failover scenario will happen, it can do automatic failover to a slave OR from a selection of slaves you have configured to make as the new master. The good thing about this is, after the initial failover, there will be no succeeding attempts to fail back, this is to protect your data and consistency of the cluster. You can also configure to only have manual failover for scheduled maintenance and the like. Between PRM and MHA, MHA is really easier to manage.

Percona Replication Manager

PRM is based on Pacemaker cluster manager (either via Heartbeat, Corosync or cman), being said that it requires knowledge of these clustering technology if you wish to maintain the cluster yourself. Pacemaker based solutions are the most stable as it can be more resilient to possible failure scenarios from non-responsive nodes especially with the help of of an arbitrating node. Similar to MHA, you can also enabled or disable automatic failover, do manual failovers, and elect only a few slaves as candidate masters. However, unlike MHA, if you are using VIPs, management of these resources are built-in, whereas with MHA you have to write your own, deploy and test if you are using any.

Aside from the above two, a Heartbeat based solution might also helpful, however, it can not handle the failover cleanly – at least without any help from an external script. In this case, PRM is a more likely better alternative.

Galera based solutions on the other hand, is quite different from these solutions. It is a synchronous replication based framework where you are able to utilize all nodes and in many cases, the term failover is only logical i.e. if you have the master VIP pointed to a single node and not load balanced.

Clustrix and Scalebase are commercial solutions quite similar to Galera based clusters as well.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.