This is very interesting topic from sysops’ point of view. Honestly – It’s not that common to find a well organized HA MySQL environment (I’m talking here about good failover solution in master – slave scenario).

I won’t write here details for the most known solutions / scenarios – I think it’s enough to point those that one can check if it fits his needs.

1. Master – master replication. In this scenario we don’t need automated failover – when we have some outage of one of those masters then it’s no problem as second one is still working. It’s good to use some LB layer between application and MySQL hosts that makes the app always hit the working server.
2. MySQL cluster. This is very comfortable scenario as MySQL cluster takes care of almost everything – this is “no single point of failure” solution. You can read about those here http://www.mysql.com/products/cluster/ and here http://dev.mysql.com/downloads/cluster/ –
3. MySQL Galera cluster – for synchronous, active – active multi – master topology: http://www.codership.com/content/using-galera-cluster
4. Failover – for now I know 2 solutions worth mentioning:
   1. Matsunobu Yoshinori created mysql-master-ha (working on MySQL servers 5.0, 5.1 and later) – this is very good tool, that handles automatic, manual and semi – manual failovers (even when slaves are in different relaylog positions). In addition You can use it when MySQL master host migration is needed. I recommend watching those slides: http://www.slideshare.net/matsunobu/automated-master-failover
   2. MySQL utilities – this is quite simple daemon which primary job is to perform automated failover when needed. You can read about it here: http://www.clusterdb.com/mysql/mysql-utilities-webinar-qa-replay-now-available/

Comments