Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am currently using Master-Slave Replication, separating reads from writes at the application level. The only kind of fail-over currently implemented is application level detection of master failure, making the slave the new master so the application can run uninterrupted. As one can guess, it is a lot of work to switch back, get in sync, tidy up and so on.

I have searched a lot, and read dozens of articles, but I did not come across a solution for HA MySQL if you have mostly MyISAM Tables, Mysql Cluster, Heartbeat/DRBD, Schooner, these are all fine for InnoDB-only setups, but not suitable for MyISAM.

I would appreciate some suggestions or real-world experience with HA Setups. It does not need to be open-source (read free), it just needs to work.

share|improve this question
    
Did you try multi-master replication in mysql? –  Khaled Jan 31 '12 at 8:23
    
i did not try master-master replication because of the problems any small incosistency will induce. As of now, mysql replication is still a little flimsy, i would not want to rely on it in a master-master setup. –  Niko S P Jan 31 '12 at 16:41
1  
@NikoSP: I'd say that any mechanism working on OS level is going to be even more flimsy. –  Hubert Kario Feb 2 '12 at 8:49
    
its gets a lot better from 5.0.x to 5.1.x, and then another big jump to 5.5, which is much better for reliability. –  Tom H Feb 2 '12 at 23:18
    
@TomH: i am currently using 5.1.51, do you have any sources regarding more reliable replication in 5.5? The changelog does not reveal significant changes to the replication mechanism except for the semi-synchronous method –  Niko S P Feb 3 '12 at 0:36
add comment

1 Answer

up vote 2 down vote accepted
+100

We use MMM combined with circular replication.

We have two MySQL servers (foo-db1, foo-db2) and each is slaved to the other, so an update on any one will be sent to the other:

http://onlamp.com/onlamp/2006/04/20/advanced-mysql-replication.html

Now that each instance has the data, we've found that it's best that writes only go to one. To accomplish this we create a virtual IP (vIP) under a different service hostname (foo-db) that clients send INSERTs and UPDATEs to. Under Debian you would install the "mysql-mmm-agent" package and generally follow this guide:

http://mysql-mmm.org/mmm2:guide

We use this configuration on most of our web sites: two front-end web servers talking to two back-end database servers. The MMM monitor runs on one of the web servers (which one is determined by Heartbeat):

www.howtoforge.com/high-availability-load-balancer-haproxy-heartbeat-debian-etch

So in summary: use circular replication to make sure two (or more?) MySQL servers are in sync; use MMM to control where the database service vIP lives; use Heartbeat to make sure the MMM agent only runs on one machine at a time.

share|improve this answer
    
Sorry, i misread. –  Aaron Brown Feb 2 '12 at 14:13
    
How do you handle split brain scenarios? (E.G network outage between hosts, with clients on both sides) –  Jon Skarpeteig Oct 15 '12 at 7:38
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.