MySQL DRP Done Right

When your systems are up and running and money is getting into the bank, it is the best time to ask yourself: What if my system fails? What if my data center (yes, even your cloud one) goes down?


The Instant DRP Solution
The instant solution is preparing a backup data center. Then you should make sure that the web and application servers will be ready to take over when needed. Yet, what about taking care of your data? How will the database be managed across two different data centers?


Many cloud, web, mobile and service providers base their systems on MySQL. Therefore, this post is dedicated to best MySQL DRP strategies..


Fail and Then Manually Recover
The most simple and easy to implement method is the Master-Slave schema. This method supports:

1. "Master": A single database server that serves for write and read.
2. "Slaves" One or more servers that serves for read only.

When the master fails, you can promote one of the slaves to become the master.
This method can also be used to reduce stress on the master and enable various partitioning modes.


Fail and Recover Automatically
A more complex way to implement, but faster to recover (if nothing goes wrong) is implementing the Master-Master schema. In this schema both servers serve as masters and recover when one fails. Please notice that this method is a little bit tricky and you should avoid conflicts since the conflict resolution is poor:

1. You should need to either avoid auto increment fields or make sure that only one server is an active master at a time.
2. You should avoid same record modified by both parties.

Do It the Cloud Way
Cloud providers propose these days cloud based database (PaaS). These solutions remove the database management headache from your task list. Amazon for example provides the Rational Database Service (Amazon RDS). RDS provides a high available managed MySQL instance that supports instant recovery to any point in time. This service is more expensive than having your own MySQL instance, but if you are a medium shop, it may be the best offer for you.


Bottom Line
Select your MySQL DRP method based on your resources, recovery needs and the effort you willing to allocate. Choose Master-Slave for novice, Master-Master for experts and the cloud way to avoid the headache.


Keep Performing,
Moshe Kaplan 


P.S feel free to contact for DRP and MySQL consulting