Mariadb master master replication supported by HAPROXY for automatic failover

Database Administrators Asked by Gagandeep Singh on December 16, 2020

We have implemented master-master setup in maria-db. Master A is being used for readwrite 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:

enter image description here

One Answer

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
    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)

Answered by Nicolas Payart on December 16, 2020

Add your own answers!

Related Questions

SQL Server :: how to move country code column to another table?

1  Asked on January 9, 2021 by francesco-mantovani


MySQL nested groupby with count other field

0  Asked on January 8, 2021 by jayakusumah



1  Asked on January 3, 2021 by rj_n


Why BCP creates such big files?

1  Asked on January 1, 2021 by racer-sql


Table size and row count

1  Asked on December 31, 2020


Ask a Question

Get help from others!

© 2023 All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP