6

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.

Scott Pack
  • 14,717
  • 10
  • 51
  • 83
Niko S P
  • 1,182
  • 8
  • 15
  • Did you try multi-master replication in mysql? – Khaled Jan 31 '12 at 08: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 02 '12 at 08: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 Feb 02 '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 03 '12 at 00:36

1 Answers1

3

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.

DAM
  • 154
  • 1