I am trying to set up highly-available master-slave MySQL replication. I plan to have two servers on different networks with one running as master and the other running as slave like so:
A B
10.0.1.0/24 | 10.0.2.0/24
__________________________
Master -----|---> Slave
10.0.1.20 | 10.0.2.20
My application servers will connect to (and perform mysql writes to) 10.0.1.20
if the system is fully operational.
In a failover condition, the slave will be promoted and the master will be down like so:
A B
10.0.1.0/24 | 10.0.2.0/24
__________________________
(DOWN) | Master
10.0.1.20 | 10.0.2.20
So, server A
is now down and B
has been promoted to MySQL master. However, I need my application server to know that B
has been promoted and all future writes should be made to 10.0.2.20
.
This is trivial for the case where there is only one master and one slave, however how should this be handled if there is one master and 3 slaves? How will the PHP application know which mysql server to write to? I need the mysql slave promotion to be automatic and occur within 5 seconds. I cannot have both servers share a VIP using heartbeat because they are on two different networks in completely different physical locations.
How is this failover normally handled with similar topologies?