2

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?

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

1 Answer 1

0

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

5
  • I will +1 this one. You should actually demonstrate/explain ucarp a little more. That might get you more votes Jul 8, 2013 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 Jul 9, 2013 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, 2013 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). Jul 9, 2013 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, 2013 at 16:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .