0

We have an S1<-M1<->M2->S2 setup of MySQL replicated nodes. These are now to be brought to the back-end of an HA Proxy server to split read from writes. We also intend to achieve automatic fail-over with this. However, write requests to be routed to M2 only when M1 fails. In usual scenario, we will be good with not touching M2 at all. There seems to be no "balance" option in HA Proxy that switches to M2 only when M1 fails. Please suggest how this can be achieved using HA Proxy.

Writing round-robin across M1 and M2 is a time consuming solution to be taken up at this point in time.

  • 1
    Consider switching to a Galera-based solution. – Rick James Oct 10 '17 at 15:32
  • Note also that HAProxy is not a read/write splitter. – Michael - sqlbot Oct 10 '17 at 18:13
0

There is a 'backup' option that you can use in HAProxy that does what you are after. e.g. if M1 fails, use the the backup (M2)

https://www.haproxy.com/blog/failover-and-worst-case-management-with-haproxy/

Then once M1 is available again, it redirects back to M1.

n.b. you do of course need to think about replication delays, as you don't want to redirect back to M1 if it is an hour behind M2 when it comes back up. We use something like this to achieve it:

http://sysbible.org/2008/12/04/having-haproxy-check-mysql-status-through-a-xinetd-script/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.