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

Users on same server connect to MySQL with different hosts

1  Asked on December 12, 2021 by crm-dev


MSSQL TempDb full

2  Asked on December 8, 2021


MySQL: order tables by entity relations?

0  Asked on December 8, 2021


Schema level encryption in Postgres

0  Asked on December 8, 2021 by turgs


How to use GROUP BY on a CLOB Column

1  Asked on December 8, 2021 by dallincha


Ask a Question

Get help from others!

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