Robust Mysql failover using free tools

Question

I am looking for a free solution to set up Mysql in an active/passive configuration, in order to provide high-availability. In particular, a Bugzilla installation, and MediaWiki needs to stay alive (which produce a dependency for MyISAM support).

I have two sites, where one site is the primary site, and one site is the backup site, with minimal latency between the two (<1ms). My plan is to set up replication between two Mysql installations, and use Mysql proxy or HAproxy at the webserver node as the failover mechanism. The goal is to allow for the primary site to go down, without affecting the backup site (users are located at both sites).

For replication, I was looking at semisyncronous replication - mainly since it appears to provide limited data loss on outage, and have a solution for network congestion.

My concerns are as follows:

• Would the above constitute a robust solution?
• What would happen if primary site is in a flapping state?
• What would happen on network outage between the sites (split brain)?
• Coping with data corruption (faulty hw)?

How can I set up my servers to provide full consistent database service for the web applications?

*Edit: Due to a dependency on MyISAM, I've found that the following solutions won't work for me:

• Galera/Percona
• DRDB

Answer 1

There are two tools that jump to mind, with the caveat being I haven't tried either, just have read about them. Percona's* XtraDB Cluster (based on Galera) and Percona Replication Manager (PRM).

PRM is based on a single-master with multiple slave nodes. It uses pacemaker to handle the failover. I can't answer how it handles your last two bullet points, but as to a flapping state, read about GitHub's experience. That experience and subsequent posting caused quite a commotion, questioning the validity of automatic failover. Here is one such post that links to some of the others.

The basic gist is, be careful with automatic failover. You might save yourself a lot of time restoring service by monitoring the state of the master and getting notification to do a manual failover.

I don't know much about XtraDB Cluster, but from the documentation, below is a list of features. However, you require MyISAM support, which XtraDB Cluster/Galera does not handle.

• Synchronous replication. Transaction either commited on all nodes or none.
• Multi-master replication. You can write to any node.
• Parallel applying events on slave. Real “parallel replication”.
• Automatic node provisioning.
• Data consistency. No more unsynchronized slaves.

disclaimer: I linked a lot of Percona stuff, but I am not affiliated with them in any capacity.