Disaster Recovery Plan (DRP) for MySQL/MariaDB Galera Cluster

When S#!t Hits the Fan...

That is a good reason to prepare for failure to minimize data loss and downtime

Cluster Design Documentation

First document your cluster, and verify you have:

1. Odd number of instances (>=3) on at least 3 independent location
2. At least a daily backup using XtraBackup is saved remotely
3. Enabled Monitoring is enabled (warning you from low disk space or under performing instances). 
4. Enabled slow queries monitoring to make sure query performance is monitored and you take care of slow queries to maximize UX

Data Recovery Plan (DRP)

DR Cases

1. Data was deleted/modified accidentally. This case will require either:
1. Accept the data loss
2. Get back to daily backup and lose any data collected since last backup (T1+T2).
3. Recover the database on a new node, and cherry picking the changes on the current cluster (T1)
2. Single node was crushed
   Galera support an automatic recovery of a node w/o significant work. Recovery can be accelerated by recovering the node from the daily backup (T1)
3. All Cluster is not working
1. Requires recovery of a single node from daily backup (T1)
2. Setup the cluster (T2)

Technical Procedures

T1: Restore a node from daily backup

1. Bring back the files from the the remote backup to /mnt/backup/
2. Uncompress the files
   sudo tar xvfz $file_name
3. Shutdown the MySQL
   sudo service stop mysqld
4. Copy the files to your data folder (/var/lib/mysql)
   sudo rm -rf /var/lib/mysql/*
   sudo innobackupex --copy-back /mnt/datadrive/mysqlbackup/
5. Verify the folder permissions
   sudo chown -R mysql:mysql /var/lib/mysql
6. Restart the MySQL and verify everything is working.
   sudo service mysql start

T2: Setup a cluster

1. Verify Galera was defined in my.cnf and define
   [mysqld]
   wsrep_cluster_address=gcomm://10.10.10.10
   wsrep_provider=/usr/lib64/libgalera_smm.so
2. Start the first node:
   sudo service mysql start --wsrep-new-cluster
3. Verify the cluster size
   mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
   +--------------------+-------+
   | Variable_name      | Value |
   +--------------------+-------+
   | wsrep_cluster_size | 1     |
   +--------------------+-------+
4. Repeat the process on the other nodes, this time just w/ a simple MySQL restart
   sudo service mysql start

T3: Add/Remove a node

1. Restore the node from the nightly backup (T1)
2. Perform step 4 in setup a cluster (T2)

Bottom Line

Being ready for the worst, can help you mitigate it with minimal data loss and minimal downtime

Keep Performing,
Moshe Kaplan

How to Replicate Part of the Tables in MySQL

When you build a DWH or another intensive processing on a database slave copy, you may not want to replicate the whole database. 


How can you replicate only some of the tables or even part of existing tables to a slave?
In General there are 4 ways to replicate data:

1. Database Definition: You can define what database names will or won't be replicated. You can place all the tables that need to be replicated in one database and all the other in another one. That way you can replicate only the first database.
2. Tables Definition: You can define what specific table names you can replicate and what not.
3. Part of a Table: Replication by SELECT limitation is supported starting from version 5.1.21 instantly. Therefore, you can easily replicate only several columns (replication discards non existing columns if they do not exist in the slave). In earlier versions, you could perform Vertical Sharding, where you could keep some of the columns in one table, and the other in another table. Then you may replicate only one of the tables.
4. Part of a Table: Replication by WHERE limitation is not supported instantly. Therefore, you cannot easily replicate only some of the rows from a single table. Yet, you can perform Horizontal Sharding, where you can keep rows groups in different tables. Then you may replicate only some of the tables to your slave.

When Should You Avoid Partial Replication?

If your slave is being used as a copy for DRP or HA, you may still want it to fully match your master. If the copy is done for specific processing and you want to avoid unneeded replication, this is definitely the right tool for you.

Syntax is the King

Most of the syntax is performed in the slave and it is detailed in Sunny Walia post.

Keep Performing,

Moshe Kaplan

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

The Path to the Cloud

You decided to make the big decision and migrate to the cloud. 

What Should You Do Now?

Well, migrating an active system between two hosting providers (and even from your small desktop under your desk), can be risky and may affect the business due to downtime or user frustration. Migration to the cloud that enables a new paradigm can be even more complex since it requires changing concepts and deal with a new brave world..

So How Should We Start?

The first rule is to not think of the cloud as a cloud. You should prepare for a simple migration (think of the cloud provider just as another hosting provider). This way you can minimize the major risk of incompatibility. Follow these steps to accomplish the first step:

1. Prepare your integration environment. If you don't have one, it's time for you to have one. If you do have, it's time to make sure that it's 100% compatible with your production environment. We'll use this environment in order to stimulate the migration.
2. Arrange your DNS records. Migrating to another location will probably require modifying your DNS records to reflect the new location. Some hosting providers do not allow naming for external locations (for example GoGrid is an example for such a provider). So you should verify that you know how to change your DNS records, or migrate the records to another register.
3. Implement a DRP environment. You should have one first in your current provider environment in order to eliminate issues such as access lists and network latency. Why is it so important?
1. Hosting migration is risky. You would like to have easy to roll back in case of non successful migration
2. Data Migration take a lot of time. If your databases (or NoSQL) are large, it will take a long time to migrate them between locations (even using a 100Mb channel, it will take a 3 hours downtime to migrate a 100GB database). The most efficient way to shorten this time will be by using Log shipping between the databases, or implement a replication between 2 NoSQL sites. When you'll choose to perform the migration to the new site, all you'll need to do is stopping the primary instance and turn the passive instance to primary one. Since the data is being replicated between sites all the time, the data migration time slot will be minimized to at most few minutes. 
4. Migrate your DRP environment to the new cloud provider. Start with the integration environment in order to verify there are no network issues in implementing it.
5. Verify the new provider stability. Now that you have servers in the new hosting provider, it's time to verify it stability, its network performance and any other issues that may arise and you were not expected to.
6. Implement a Reverse Proxy Solution. Some of your client will be ready for the migration. They may have old stale DNS records or they might use your IP address for some kind of reason. Using reverse proxy can help your routing traffic from your old location to the new one, minimizing lost traffic and downtime.
7. Perform migration test in the integration environment. You should turn the passive site into active one, and check that user can continue work after the downtime. You should document the process, and if times are not good enough, you should exercise the process. After you are satisfied with results, it's time for prime time.
8. Migrate your production DRP site the new hosting provider. Perform the process in a similar manner to the integration DRP migration.
9. Prepare for The big day. Transform your DRP (or the new hosting environment) into a production one.
10. Verify. Wait a few days and verify the system was stabilized, if so make few steps before closing your old hosting facilities and cut costs:
1. Implement a new DRP site both for the integration and the production environments.
2. Verify that the routed traffic from the old site is neglected.
11. The big day. it's time to shut down your old server, close the site and say bye-bye to your old provider.

Just Scale It
After doing so much work, it's time to decide what cloud items do you want to you. It may depend on the cloud provider offering, and your decision how and if you want to avoid vendor lock in:

1. CDN: if you have major static files traffic, or if you are able to turn some of the traffic into static one (think of Linkedin profiles for example), you could store it in CDN facilities that offer can reduce your web servers traffic and better prepare your for peaks. It also can help you 
2. Queues: Avoid taking care of queues availability and let this internet giants take care of it.
3. NoSQL stores: you could implement open source solutions such as Cassandra or chose the provider key value store.
4. Rational Databases: Some cloud providers offer these days a database as a Service that let you get rid of these log shipping, clustering and care of daily backups. If you are interested, drop me a note and I refer you to some interesting companies.
5. Load Balancing: You could choose using software based load balancers such as HAProxy, or choosing the cloud solution.
6. Monitoring: Some cloud provider offer you a system monitoring, saving you major effort to monitor your servers.

Take It One Step Further

Now that you chosen the tools you just have to make sure your system can scale out:

1. Separate back office services from web interfaces, creating pools of small machines that each can take care of their tasks.
2. Parallelize your back end services, making sure that no single service turns into a future bottleneck.
3. In case of push systems and communication between parties, create a directory system that can route events between different servers.

Bottom Line

If you felt that you are bounded to your current hosting provider, it's time to think again. Using this simple, yet not so short method, you could reduce risks and accomplish the change.

Keep Performing,

Moshe Kaplan