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?

Ethan Hayon
  • 235
  • 1
  • 6

1 Answers1

0

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

Jure1873
  • 3,702
  • 1
  • 22
  • 28
  • I will +1 this one. You should actually demonstrate/explain ucarp a little more. That might get you more votes – RolandoMySQLDBA Jul 08 '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 09 '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 09 '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 09 '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: http://www.openlogic.com/wazi/bid/259864/Simple-database-load-balancing-with-MySQL-Proxy – Jure1873 Jul 11 '13 at 16:24