Restore Xtrabackup on Galera Cluster

This article describes how to restore a backup on a Galera Cluster.

It applies to backups that have been taken using s9s_backup (either from command line or from GUI) and that is using Xtrabackup as the backup method.

Note: if you run as 'root' you can omit the 'sudo' below.

Example setup:

CC server / Controller:   192.168.56.101
galera node1:   192.168.56.102
galera node2:   192.168.56.103
galera node3:   192.168.56.104

In this example the cluster has crashed badly, we have a backup of 192.168.56.102 that we want to restore, and let the other nodes sync from 192.168.56.102.

Datadir is /var/lib/mysql for the mysql servers.

1. Get or update the s9s_admin tools

On the CC server, aka CMON Controller do:

git clone git://github.com/severalnines/s9s-admin.git

Then do

cd s9s-admin/
git pull 

This package contains s9s_galera, a command line tool that is required to use in order to safely restore a backup.
Make sure the latest s9s-admin software is installed on the nodes:

cd s9s-admin
bash ./install.sh -i1

2. Stop the cluster - make sure all nodes are stopped

### DISABLE AUTO RECOVERY IN THE CONTROLLER:
EDIT /etc/cmon.cnf

and add the line

enable_autorecovery=0

Restart the controller:

service cmon restart

Then stop the cluster:

cd s9s-admin/cluster
sudo ./s9s-galera --stop-cluster -i 1 

This will stop all nodes in the cluster with id = 1, and put them in "User Initiated Shutdown" mode.

3. Restore the backup

Determine from the GUI the backup set that you want to restore. The backup set contains one full backup and zero or more incremental backups. s9s_backup will restore the full backup and apply increments if they exist.

Go to the node that has the backup files, in this example we login to 192.168.56.102

sudo s9s_backup --restore -i<cluster id> -b<backup set> -t /restore -B /path/to/mysql-client -ucmon -p<cmon password> -h <cmon hostname>
# Example:
sudo s9s_backup --restore -i1 -b33 -t/restore -B /usr/bin/mysql -ucmon -pcmon -h192.168.56.101

The backup will now be restored into the directory /restore/<backup set>  as an intermediate step.

(To view the content of a Xtrabackup backup file you need to specify the -i option to tar:  tar -tfvi <backup file> ) 

Example: If the <backup set> is 1, then the restored data will be in:

/restore/1/ 

Move or remove the old mysql data directory (remove shown below), and we assume the mysql data dir is /var/lib/mysql

Moreover, the s9s_backup --restore will also write a grastate.dat file in /restore/<backup set>, which will be copied in place with the --copy-back command below.

You can verify it is there by doing:

sudo cat /restore/1/grastate.dat

Before copying the restored database into the data directory you must delete or move  MySQL's data directory (please note that your data directory may be different, e.g /mnt/data/mysql or /storage/data/mysql):

sudo rm -rf /var/lib/mysql/*

Copy back the restored files to the datadir (here we have restored backup set 1 in /restore/). The grastate.dat file is copied in the process:

sudo innobackupex --copy-back /restore/1/

Change the owner of the mysql datadir:

sudo chown -R mysql:mysql /var/lib/mysql/

4. Start the node you have restored, and the rest of the nodes

Pretend the IP address of the node you restored is 192.168.56.102

On the controller do:

cd s9s-admin/cluster
sudo ./s9s_galera --reinit-cluster -i1 -d 192.168.56.102 -r

This command will reinit the cluster. The -r will clear the grastate.dat files on the non-donor nodes. This must happe or the correct galera state cannot be recovered.

When running the command you will see it prints out:

192.168.56.102 is Synced
Preparing non-donor nodes
-r (RESET) was specified, removing /var/lib/mysql//grastate.dat
192.168.56.103: Executing 'rm -rf /var/lib/mysql//grastate.dat' [ok]
192.168.56.103: Executing 'rm -rf /tmp/xtrabackup_pid' [ok]
-r (RESET) was specified, removing /var/lib/mysql//grastate.dat
192.168.56.104: Executing 'rm -rf /var/lib/mysql//grastate.dat' [ok]
192.168.56.104: Executing 'rm -rf /tmp/xtrabackup_pid' [ok]

and it the prompts you to start the other nodes:

Start another node (one at a time):
s9s_galera --start-node -i 1 -d 192.168.56.102 -h 192.168.56.103
s9s_galera --start-node -i 1 -d 192.168.56.102 -h 192.168.56.104

VERIFY: Before starting the remaining nodes you can of course connect to the 192.168.56.102 server and check that your backup is okay.

That's it, Your Galera cluster has now been restored.

The file README.s9s_galera contains more information about the s9s_galera tool.