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

5 Steps to Migrate from MySQL Community Edition to MariaDB

Why Should You Consider the Migration?
There are many reasons for it, but in bottom line in open source you should look for the community. 

MySQL CE, MariaDB or Percona DB?
Well every one should make their decisions. However, you should decide if you are looking for commercial support or community support. If you are looking for commercial support, choose the company that you most trust (and gives you the best deal).
If you are looking for community support take a look where community is, check if the forums are active and if bugs that are being reported by the community are being taken care of. Finally search on Linkedin. It's a great way to sense where the wind blows.

What Should You Expect?
Faster releases, better response to community, some performance boost and in the bottom line: no change is need from your client side.

How to Migrate?
Migration currently is very simple, just like upgrading to a new major MySQL release:

1. Backup:
   sudo service mysql stop
   sudo cp -R /var/lib/mysql /var/lib/mysql.old
   sudo cp -R /etc/mysql/my.cnf /etc/mysql/my.cnf.old
2. Uninstall MySQL
   dpkg --get-selections | grep -v deinstall | grep -i mysql
   sudo apt-get purge -y percona-toolkit
   sudo apt-get purge -y php5-mysql
   sudo apt-get purge -y libmysqlclient18 mysql-client  mysql-client-5.5 mysql-client-core-5.5 mysql-common mysql-server mysql-server-5.5 mysql-server-core-5.5
3. Install MariaDBsudo apt-get install software-properties-common
   sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
   sudo add-apt-repository 'deb http://sfo1.mirrors.digitalocean.com/mariadb/repo/10.0/ubuntu trusty main'
   sudo apt-get -y update
   sudo apt-get -y dist-upgrade
   sudo apt-get install mariadb-server mariadb-client percona-toolkit
4. Select your root password
5. Upgrade
   sudo mysql_upgrade -uroot -p

That's all. You will not need to change any client or recompile them.

Bottom Line
Migration is easier then you may expect, now you can test and verify if it fits your needs.

Keep Performing
Moshe Kaplan