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: