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.
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.