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 |
 Master -----|---> Slave   |

My application servers will connect to (and perform mysql writes to) 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 |
 (DOWN)      | Master   |

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

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?

  • Have you considered using a DNS hostname with a really short TTL? – Cry Havok Jul 8 '13 at 21:54
  • I haven't hear many good things about DNS failover. TTL is sometimes ignored and overwritten depending on DNS provider. – Ethan Hayon Jul 9 '13 at 2:26

Use keepalived, ucarp, heartbeat or somethins similar to have a virtual master IP that will answer to php applications.

| improve this answer | |
  • I will +1 this one. You should actually demonstrate/explain ucarp a little more. That might get you more votes – RolandoMySQLDBA Jul 8 '13 at 22:20
  • Will this still be possible if the two servers are on completely different networks? A will be and B will be – Ethan Hayon Jul 9 '13 at 14:25
  • That could be a problem. Maybe you could have a third network with the virtual IP and synchronize the state on the current network. Or have both servers listen on the other's IP and take over that IP in the case of a failure. Maybe you could put both servers on both networks and then have two virtual IPs - one for each network. Is having separate networks a requirement? – Jure1873 Jul 9 '13 at 22:22
  • Yes, for redundancy and to prevent SPOF we would like to place the servers on completely different networks in different datacenters (under different ISPs). – Ethan Hayon Jul 9 '13 at 23:57
  • Then you may have to create a proxy or router to join them. Did you know about the mysql proxy project? I haven't tried it yet, because we are using vrrp and it covers all our needs but it may solve your problem. Some more info: openlogic.com/wazi/bid/259864/… – Jure1873 Jul 11 '13 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.