Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
    
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
add comment

1 Answer 1

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() - support.severalnines.com/entries/…) –  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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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