How did you setup MySQL Replication with autofailover that is app transparent?

Question

Recently told MySQL is shelving MySQL Fabric. Interested how others have implemented a MySQL replicated environment that is app transparent.

I am considering using HA Proxy to host virtual IP address for Master, one for slave pool. The using MySQL Failover to monitor replication cluster, auto promote a slave to master. LinuxHA or HAProxy would change which server the virtual IP address points to. Should work for both Master and Slaves.

We're primarily a PHP shop running MySQL DB's on Centos7 Linux.

Answer 1

I recently had to setup a similar rig but with ubuntu and not centos. I used MySQL cluster and HaProxy in a master-master setup and its been solid as a rock, no down time and haven't experienced any instances were latency has been an issue.

I used this guide to help configure the MySQL cluster. MySQL cluster is great in my opionion but see the link bellow for an explanation as to why.

HaProxy is able to detect down nodes and you could use the answer provided in this question to help with your configuration of automatic fail over to a slave node. Also I would configure a second HaProxy with heartbeat, just incase.

It might be worth taking a look into the answers provided in this question to help decide what kind of cluster is really best for your environment(If you don't already know).