Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
share|improve this question

closed as off-topic by Paul White, Phil, Max Vernon, Aaron Bertrand Jan 26 '15 at 14:48

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Shopping list question - questions about which tool, library, product or resource you should use are off-topic here because they quickly become obsolete and often are just about the preferences of the answerer. If you have an issue with or a question about a specific tool, please revise your question to conform to that scope." – Paul White, Phil, Max Vernon, Aaron Bertrand
If this question can be reworded to fit the rules in the help center, please edit the question.

just clarifying your edit: 'Percona XtraDB Cluster' won't work for you. 'Percona with XtraDB' is percona's mysql fork, with their XtraDB storage engine replacing InnoDB. It is not by itself a HA solution any more than running a standalone MySQL is a HA solution. – Derek Downey Oct 12 '12 at 17:56
up vote 2 down vote accepted

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.

share|improve this answer
At least Galera doesn't seem to fully support MyISAM - couldn't find any information about XtraDB solving this? (Ref. NOW() -…) – Jon Skarpeteig Oct 12 '12 at 14:35
@JonSkarpeteig wow, I completely skipped over MyISAM requirement in your question. That does remove XtraDB Cluster as an option. Only supporting innodb is the first limitation! – Derek Downey Oct 12 '12 at 14:50
I still like this answer. Bummer the OP mentions MyISAM. +1 !!! – RolandoMySQLDBA Oct 12 '12 at 16:15
If all MyISAM tables are read-only, you can use PXC just fine. You would have to copy the MyISAM tables to all nodes in the Cluster. Even though the mysql schema is most MyISAM tables, you can still perform GRANT commands. Such commands will replicate. – RolandoMySQLDBA Oct 12 '12 at 16:40
In recent versions of Galera it is possible to replicate MyISAM tables by setting wsrep_replicate_myisam=ON – Jon Skarpeteig Oct 31 '13 at 21:23

Not the answer you're looking for? Browse other questions tagged or ask your own question.