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.
Best Answer
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.