mysql master slave replication with haproxy

Question

I have configured MySQL Master-Slave replication and have an automation to perform the failover when Master goes down and also it will take care of fail back to Master when it comes back online.

But I am trying to configure HaProxy to send the requests to Master always and when Master goes down then in few mins it has forward the requests to Slave server and when Master comes back online then all the requests has to be forwarded to Master again.

Is there any balance config which does this setup in HaProxy?

Karthi1234 Karthi1234 · Accepted Answer · 2017-04-12T03:13:21

I managed to make it work. Here is what I have used on haproxy config.

listen sql_cluster 10.0.0.4:3307
        mode tcp
        balance roundrobin
        option mysql-check user haproxy_check
        server masterdb 10.0.0.5:3306 check inter 30s fall 3 rise 100
        server slavedb 10.0.0.6:3306 check backup

So in this config, it will check 100 times in the time of 30s interval to fall back the connection to Master, in meantime the automation which I developed will take care of make the Master DB sync with Slave once it comes back online and will restarting haproxy should forward the connections to Master.

mysql master slave replication with haproxy

1 Answers