Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
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
add comment

1 Answer 1

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

share|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 10.0.1.20/24 and B will be 10.0.2.20/24 –  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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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