MySQL Master-Slave Replication When Master is Down

Question

I'm running MySQL as the database on Ubuntu instances. I'm using MySQL Master-Slave replication where master's changes will be written to slave and slave's changes will not be reflected on the master. That's fine. I'm using a HAProxy load balancer to front the MySQL instances where all the requests will be sent to master MySQL instance. If the master MySQL instance is down slave MySQL instance will act as master and HAProxy will send all the requests to salve. Active-Passive scenario.

HAProxy - 192.168.A.ABC
MySQL Master - 192.168.A.ABD
MySQL Slave - 192.168.A.ABE

Let's assume that the MySQL master(192.168.A.ABD) is down. Now all the requests will be sent to MySQL slave(192.168.A.ABE) by HAProxy where now he acts as the master MySQL server for the time being.

My problems are

What happens when original master MySQL instance(192.168.A.ABD) is up?
Will changes written to new MySQL master (192.168.A.ABE) be replicated to original master(192.168.A.ABD) again?

How should I address this scenario?

IGGt IGGt · Accepted Answer · 2015-04-02T14:15:36

First of all I should say that I have never used HA Proxy so con't comment on that directly.

However, in your current setup the Master (ABD) will be out of sync, and wont catch up. You will have to rebuild that using mysqlDump or similar tool.

What you would need is a Master < > Master setup (as opposed to Master > Slave), which enables you to write to either database and have it reflected in the other. This isn't quite as straight forward as it sounds though.

Assuming you already have your master > slave setup, and they are in sync

On the Master (ABD) you want to add:

auto_increment_increment=2
auto_increment_offset=1
log-slave-updates

On the Slave (ABE) add:

auto_increment_increment=2
auto_increment_offset=2
log-slave-updates

to your my.cnf files. Restart the Database. This will help to prevent Duplicate Key Errors. (n.b. that log-slave-updates isn't strictly required but makes it easier to add another slave in future)

Next you want to tell the Master (ABD) to replicate from the Slave (ABE).

Depending on what version of MySQL and if you are using GTID etc. the exact process differs slightly. But basically you are going to issue a CHANGE MASTER statement on the Master so it replicates from the slave.

And away you go. You probably want to avoid writing to both at the same time as that opens up a whole other kettle of fish. But if the Master goes down, you can switch your writes to the slave, and when the master comes back up, it will simply start replicating the missing data.

MySQL Master-Slave Replication When Master is Down

2 Answers