Mariadb master master replication supported by HAPROXY for automatic failover

Question

We have implemented master-master setup in maria-db. Master A is being used for read\write and Master B is used for backups and MIS operations. We have built this setup so that we do not need to promote slave in case of failure.

Now we want to implement HA PROXY for automatic fail over. In case Master A goes down application should switch automatically to Master B.

Now my question is that if Master A goes down and applications start serving from Master B. But when Master A comes online and is not in sync with Master B what will HA PROXY do, will applications get connected to Master A instantly or HA Proxy will check synch status and let Master A synch up with Master B and then allow applications to connect with Master A.

What parameters need to be added in order to achieve this.

HA PROXY Configuration:

Answer 1

With your current configuration, if Server A goes down, HAProxy will switch to Server B. But as soon as Server A is back up and running on port 3306, HAProxy will switch back to Server A, which is not what you want.

The best way to handle this use case is to use the stick-table parameter.

With a stick table based on the destination IP address, HAProxy will redirect connections to server B even when server A is operational again.

It will only switch back to server A when you restart the HAProxy service or if server B fails or if you disable it manually.

Here is an example of a configuration:

listen mysql-cluster
    bind 0.0.0.0:3306
    mode tcp
    option mysql-check user haproxy_check
    balance source
    stick-table type ip size 1 nopurge
    stick on dst
    server MASTER_A X.X.X.X:3306 check
    server MASTER_B X.X.X.X:3306 check backup

You will find more information about this on this HAProxy blog post and in HAProxy documentation (see stick-table, stick on and peers options)