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