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

Nicolas Payart · 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
    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)

Mariadb master master replication supported by HAPROXY for automatic failover

One Answer

Add your own answers!

Related Questions

Why does MySQL wait for a table lock for a validation query?

how to update SSRS execution account automatically through power-shell?

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

Does pg_dump flag -j give performance improvement up to -j 16 or -j 32 or even -j 64?

MySQL nested groupby with count other field

How can I transpose multiple unrelated columns into rows in SQL Server?

MySQL Query causing high CPU and taking forever to execute

Postgres files postgresql.conf and pg_hba.conf – which take priority?

Drizzle database system design questions – architecture &c

Update staging table records in SQL Server 2017 with INSERT trigger and table-valued function, using INSERTED records

What’s the meaning of “resources external to the SQL Server computer are needed” in Microsoft’s SQL documentation?

Article table has a lot of null. What is the best way to prevent null in MySQL

Full text search on larger text rows

Select a count of record sharing the same value than record X

GRANT ROLE TO USER in MySQL

Error passing variable table type to a stored procedure

Is there any unique identifier for PostgreSQL? How can the master identify its slave unqiuely?

Why BCP creates such big files?

How to maintain customer addresses for Orders?

Table size and row count

Ask a Question