3

I'm trying to figure out the best way to configure MySql so that I can have redundancy - so for example - I would have two servers, both through the same host, but at different datacenters in the event one dc has limited to no availability.

In my perfect world scenario I would do updates to Server 1- reboot and not worry about the DB going down as Server 2 would still be online. When Server 1 comes back, I could update Server 2, reboot, etc etc. You get the idea. Basically - having one Server pick up and take over if the other goes offline - or vice versa.

I've read about Master/Slave setup - however it doesn't sound like this will solve my problem as Slaves are only written to by the master.

I've also read about Master Master setup - which I had originally thought was the ticket - however, after further research i came across articles basically stating that writing to Server 2 while Server 1 was done in a Master - Master setup could be detrimental (I can't remember the exact reasoning) - so that scared me away from using the Master Master setup.

I'm not sure if what I want to accomplish is possible - in my ongoing research I can't seem to find a solid - "this is what you want to do" article. Would love any advice on how I could accomplish what i'm looking for (if its possible).

Thank you in advance.

2

What you are looking for is in fact possible. What you are looking to do (like many businesses) is build an automatic failover which is often called a hot standby. A hot standby is just a backup slave server that can be switched to either automatically or manually when you need to do maintenance or if your master server fails.

MySQL has some great documentation on setting that up here

You may also need to take a look at replication methods for replicating your data to the slave.

  • Curious, is it possible for the hot standby to be switched on automatically as you say - but then, once the original master is back online the original master takes back over? Just curious :) – 99823 Apr 27 '16 at 2:53
  • 1
    I don't think it is done automatically but I'm sure that you could write a script to keep checking the failed server and restart it when it recognizes that it is back online. check this out – Jmaurier Apr 27 '16 at 2:59
2

I like a Galera-based solution (PXC, MariaDB 10, or Galera on top of MySQL). Put 1 node in each of 3 datacenters. If one DC goes, down, the other two chatter and decide they have a quorum and continue running.

"Split Brain": If the dead DC is not really down, but instead the network makes it inaccessibly, that one node realizes it does not have a quorum so it stops taking writes. When (if) it comes back online, it chatters with the other two and repairs itself. Automagically.

You can, by design, write to all the nodes all the same time.

If you encounter a solution with only 2 things, be suspicious that it can fall victim to "split brain". It is a major disaster for both Masters in a dual-Master setup thinking that the other Master is dead. All sorts of difficult-to-repair stuff can be inserted/updated.

MHA is another excellent tool. But its approach to "split brain" is to discover it, then "do no harm". But that means that you still have to manually step in and decide what to do. Not quite an "automatic" failover for that case.

Fabric is Oracle's offering. But the "Fabric node" is a Single Point of Failure. (Update: Oracle quietly discontinued development of Fabric.)

InnoDB Cluster (Group Replication) is Oracle's new offering. It looks like a good contender for HA.

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.