September 4, 2013
By Severalnines

Performing regular backups of your database cluster is imperative for high availability and disaster recovery. If for any reason you lost your entire cluster and had to do a full restore from backup, you would need a reliable and up-to-date backup to start from. 

 

Best Practices for Backups

 

Some recommendations to consider for a good scheduled backup regime:

  • You should be able to completely recover from a catastrophic failure from at least two previous full backups. Just in case the most recent full backup is damaged, lost, or corrupt,
  • Your backup should contain at least one full backup within a chosen cycle, normally weekly,
  • Store backups away from the current data location, preferably off site, 
  • Use a mixture of mysqldump and Xtrabackup for extra safety, and not rely on one method, 
  • Test restore your backups on a regular basis, e.g. every two months.

A weekly full backup combined with daily incremental backup is normally enough. Keeping a number of backups for a period of time is always a good plan, maybe keep each weekly backup for one month. This allows you to recover an older database in case of emergencies or if for some reason you have local backup file corruption.

 

mysqldump or Xtrabackup

 

mysqldump is very likely the most popular way of backing up MySQL. It does a logical backup of the data, reading from each table using SQL statements then exporting the data into text files. Restoration of a mysqldump is as easy as creating the dump file. The main drawbacks are that it is very slow for large databases, it is not ‘hot’ and it wipes out the innodb_buffer_pool

Xtrabackup performs hot backups, does not lock the database during the backup and is generally faster. Hot backups are important for high availability, as they run without blocking the application. This is also an important factor when used with Galera, as Galera relies on synchronous replication. However, restoring an Xtrabackup can be a little tricky. 

ClusterControl supports the scheduling of both mysqldump and Xtrabackup (full and incremental).

 

Full Restore from Backup

 

In this post, we will show you how to restore Xtrabackup (full + incremental) onto empty cluster running on MariaDB Galera Cluster. These steps should also work on Percona XtraDB Cluster or Galera Cluster for MySQL from Codership. 

In our original cluster, we had a full xtrabackup scheduled daily, with incremental backups created every hour. We used the Online Backup Storage feature of ClusterControl to store our backup files on Amazon Glacier, a low cost storage service. 

 

Now, let’s assume we have lost our original cluster and have to do a full restore onto a new cluster. 

The steps include:

  1. Set up a new MariaDB Cluster and copy the backup files on one of the Galera nodes (maria1), 
  2. Prepare the backup files,
  3. Stop the MySQL service in all Galera nodes and restore the backup files on maria1,
  4. Start the Galera cluster with maria1 as the donor.

 

Step 1 - Set up New MariaDB Cluster and copy backup files

 

Once we have deployed our new MariaDB cluster, we will copy the backup files on maria1.

Here is the list of backup files on maria1:

$ ls ~/backup -1R
/home/user/backup/full:
backup-full-2013-08-29-10-00-00.tar.gz
 
/home/user/backup/incremental:
backup-incr-2013-08-29-11-00-20.tar.gz
backup-incr-2013-08-29-12-00-09.tar.gz
backup-incr-2013-08-29-13-00-15.tar.gz

 

We will disable auto recovery in ClusterControl. Log into the ClusterControl node and add the following line in /etc/cmon.cnf:

enable_autrecovery=0

 

And restart the cmon service:

$ /etc/init.d/cmon restart

 

Step 2 - Prepare the Backup Files

 

The backup data files are not point-in-time consistent until they have been prepared, since they were copied at different times as the program ran. This step makes the files consistent at a single point in time.

 

(a) Create a directory called “fullbackup” and extract the full backup into it:

$ mkdir ~/backup/fullbackup
$ zcat ~/backup/full/backup-full-2013-08-29-10-00-00.tar.gz | tar -xif - -C ~/backup/fullbackup/

 

(b) We need to create three directories to extract all increments separately. Following command will create three directories under incrementalbackup:

$ mkdir ~/backup/incrementalbackup
$ for i in {1..3}; do mkdir ~/backup/incrementalbackup/$i; done

 

(c) Extract all increments using xbstream to the respective directory (1 to 3) that we just created:

$ i=1; for p in `ls -1 ~/backup/incremental`; do zcat ~/backup/incremental/$p | xbstream -x - -C ~/backup/incrementalbackup/$i; ((i++)); done

 

(d) Prepare the full backup:

$ xtrabackup --prepare ~/backup/fullbackup

 

(e) Now we will append the incremental backups to it. Remember that the full data will always be in ~/backup/fullbackup:

$ innobackupex --apply-log --redo-only ~/backup/fullbackup/ --incremental-dir=/home/user/backup/incrementalbackup/1
$ innobackupex --apply-log --redo-only ~/backup/fullbackup/ --incremental-dir=/home/user/backup/incrementalbackup/2
$ innobackupex --apply-log ~/backup/fullbackup/ --incremental-dir=/home/user/backup/incrementalbackup/3

Note that we omit --redo-only when appending the last incremental backup. This option is used when merging all incrementals except the last one.

 

IMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run, innobackupex will print "completed OK!".

 

Step 3 - Restore the Backup on maria1

 

Restoration requires MySQL to be shutdown and data directory must be empty. In this case, the MariaDB Galera Cluster datadir is located at /storage/data/mysql. Shutdown the MySQL service in all nodes, you can do this e.g., from ClusterControl.

 

Clear all files under /storage/data/mysql:

$ rm -rf /storage/data/mysql/*

 

Start the restoration using --copy-back option. This will restore the data to the datadir location defined in my.cnf:

$ innobackupex --copy-back ~/backup/fullbackup/

 

Apply correct ownership to datadir:

$ chown -Rf mysql.mysql /storage/data/mysql

 

Step 4 - Start the Galera Cluster

 

Bootstrap the Galera cluster by starting MySQL service on maria1:

$ service mysql start --wsrep-cluster-address=gcomm://

 

Take note that our new cluster has been reinitialized and the data has been overwritten. We need to reassign ‘cmon’ privileges on the database nodes from ClusterControl host. Run following query on maria1:

mysql> GRANT ALL ON *.* TO 'cmon'@'clustercontrol' IDENTIFIED BY 'cmon' WITH GRANT OPTION;

 

SSH into ClusterControl node to start maria2 with maria1 as the donor node:

$ s9s_galera --start-node -i 1 -d maria1 -h maria2

 

Wait until maria1 status turned to Synced, then start the third MariaDB node:

$ s9s_galera --start-node -i 1 -d maria1 -h maria3

 

Once ClusterControl detects that all the nodes are running (green tick icons in the top bar), it is safe to enable back auto recovery by commenting out the following line in /etc/cmon.cnf:

#enable_autorecovery=0

 

And restart the cmon service:

$ /etc/init.d/cmon restart

 

The restoration is now complete and you can expect DB Growth to report the updated size of our newly restored data set: