3

I am testing out some infrastructure options before I start writing a web application. I would like to have two separate MySQL database servers at different physical locations, but if one of them goes down, I would like to be able to fail over to the other with minimal data loss.

From what I can see, MySQL only supports one "master" server and multiple "slave" servers. I'm assuming the reasoning behind this setup is to do your writes on the master, and try to do your "reads" on the slave servers.

Is there a way to set it up so that I have the ability to fail over and write to the slave server if the master server goes down?

Things to keep in mind for my test- I have two VPS from different providers with one external IP each.

4 Answers 4

3

Checkout Galera. They say it replicates over a WAN or Cloud. Galera has been getting a lot of attention recently in MySQL circles but it's not a mature product so you need to assess your reward/risk carefully.

1

The MySQL Switching Masters During Failover reference document describes how you might implement failover in a master/slave replication scheme and the Multi-Master and Circular Replication document describes several other replication schemes which may suit your requirements better than master/slave replication with failover.

1

MySQL only supports one "master" server and multiple "slave" servers

No, even leaving aside the option of ndb clustering you can have multiple masters and multiple slaves. Master-master replication may well meet your requirements. Have a look at mysqlproxy / HAproxy for tools for handling failed nodes.

It is possible to script promotion of a slave node in the event of an outage on a master/slave replication - but unless you've got a good reason for taking this approach I'd recommend steering clear.

0

I would like to recommend the following:

A combination of four(4) things for good High Availability

  • MySQL Circular Replication
  • DRBD
  • ucarp
  • InnoDB

What's funny about this question is that this is the third time I have ever answered a question like this. I actually addressed this question in the DBA StackExchange 3 times (2 Answers + Commentary Answer in Meta):

https://dba.stackexchange.com/questions/5153/mysql-replication-1-slave-multiple-masters/5174#5174

https://dba.stackexchange.com/questions/1974/mysql-high-availability-failover-and-replication-with-latency/1983#1983

https://dba.meta.stackexchange.com/questions/207/mysql-high-availability-failover-and-replication/215#215

You must log in to answer this question.

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