Hotbackup of Galera Cluster for MySQL using Xtrabackup

Tweet
Hot backups are important for high availability, they can run without blocking the application. Percona Xtrabackup is a great tool for backing up InnoDB data. 
We have created a backup tool called s9s_backup that makes Xtrabackup really easy to use, and is fully integrated with ClusterControl, which means that you can schedule backups with ease and view the backups that you have taken, and also restore the backups with no pain.
s9s_backup is available in the lastest version of ClusterControl or you can download it here.

Is mysqldump totally useless then?

No. If you would like to isolate and load only one table, mysqldump is great, or if you want to load your data into another storage engine or database (e.g. NDB). Then mysqldump comes in very hand. Within ClusterControl you can create schedules to make a mysqldumps, full and incremental backups using Xtrabackup.

How to use s9s_backup

s9s_backup is included in ClusterControl and automatically installed in either /usr/bin or /usr/local/cmon/bin. 
Running/usr/bin/s9s_backup prints out the set of options that s9s_backup supports:

/usr/bin/s9s_backup <--backup|--list|--install|--purge|--restore>  

Full Backup

/usr/bin/s9s_backup --backup full  /path/to/my.cnf     <backupdir>

This will take a full backup of the host where you run the tool and store the backup in <backupdir>/full/backupdir.
s9s_backup writes a log  (you can change it easily in the s9s_backup script if you want it on screen instead) to /tmp/s9s_backup.log.
Status information is written into the CMON Database, so you can see if a backup has failed or succeeded, size information, md5 etc.

Incremental Backup 

/usr/bin/s9s_backup --backup incr  /path/to/my.cnf    <backupdir>

This will take an incremental backup of the host where you run the tool and store the backup in <backupdir>/incremental.

In order to take an incremental backup you must have already made a full backup on the host. So when creating the backup schedule (in either cron or ClusterControl), make user the first backup that will run is a full backup, else the incremental backups will fail.
s9s_backup writes a log to /tmp/s9s_backup.log. 

List backups

/usr/bin/s9s_backup --list

This prints out a list of backup sets (a full backup plus the subsequent incremental backups) together with a backup id. This is called the backup set:

root@server01:~# ./s9s_backup --list
===========================================================================
Full backup taken '2012-October-23_07:40:37':
   server:     server01
   backup set: 2
   status:     completed
   location:   /backups//full/backup-full-...tar.gz (796873 bytes)
   lsn:        8791838
   md5:        31f84543c997a28a6bca7917776a5fac
   >> incremental backup taken '2012-October-23_07:58:48':
      server:   server01
      parent:   2
      status:   completed
      location: /backups//incremental/backup-incr-...tar.gz (2037959 bytes)
      lsn:      35177200
      md5:      5f845dd7a478fa0a99b4d97f6868c747

The list of a backup set is ordered by the LSN (Logical Sequence Number) and the backup, once restored, will be restored in LSN order.
The incremental backups have as parent the full backup, so you can verify that the incremental backup indeed belongs to the correct backup set (parent). Orphaned backups cannot be restored.&nbsp

Restore backups 

Do not try to unpack the files in the backup set files manually. Instead you should use s9s_backup’s restore option to do it in the correct way and in the correct order:

/usr/bin/s9s_backup --restore <backup id>  <my.cnf>  <restoredir>

To restore a backup, you must specify a backup set id. From the ClusterControl Web interface you can easily see which backup files are part of a backup set, or you can run the --list command.

The restoredir specifies where the restored backup files in the backup set will be applied and combined by xtrabackup. Please note that the restoredir is not, and must not be the same as the mysql datadir.  The increments are restored in LSN order to ensure you have a consistent restore point. This means that you as a user don’t have to worry about what order to apply the increments in. s9s_backup takes care of that, which is great when you are under pressure.

When restoring, s9s_backup compares the md5sum of the backup files when the backup was taken with the backup files that are currently restored. If there is a mismatch, s9s_backup will abort the restoration.

Once the restore is completed, the restoredir contains a complete restored backup that can be copied back to the mysql datadir.

Assuming you have a total cluster crash, and the cluster is unable to repair itself, then do on one node only:

1. Make sure that the ClusterControl controller is stopped to prevent auto-recovery (you are now in charge of the recovery). On the controller do: service cmon stop
2. Verify the other mysql servers are stopped.
3. cd <restoredir>/<backup set id>
4. make sure the is empty (use e.g rm -rf /var/lib/mysql) before running the innobackupex –copy-back command in the next step
5. innobackupex  --copy-back  <restoredir>/<backup set id>
6. chown mysql:mysql –R <mysql datadir>
7. mysqld_safe --defaults-file=/path/to/my.cnf --wsrep-cluster-address='gcomm://' &  
8. Start the ClusterControl controller (on the controller do: service cmon start),and it will orchestrate the synchronization of the other nodes.

Purge backups 

If you want to purge old backups, or failed backups, you can do:

/usr/bin/s9s_backup --purge <retention_period>   

Backups older than retention_period will be removed from the file system.
The retention, if not specified on the command line, will be fetched from the cmon database (in the cmon_configuration table), which you can set from the Web interface under Cluster Settings.

Installation of Xtrabackup and Cron Job

If Percona Xtrabackup is not already installed, then s9s_backup will automatically download and install Xtrabackup.
To install Xtrabackup and to also install a cron job do:

/usr/bin/s9s_backup --install cron

The 'cron' at the end means Install Cron job.
This will install Percona Xtrabackup on this server, but no cron job:

/usr/bin/s9s_backup --install 

In both cases above, the Percona Xtrabackup will be downloaded and installed.
The cron job is defined in the s9s_backup script, and you can easily modify it to suit your needs. The cron job is installed in /etc/cron.d/s9s_backup
The cron job will take a full backup every Sunday at 0300 and then incremental backups Mon-Sat at 0300.

If you want to modify the cron job table you can do it directly in the s9s_backup script or post installation by changing /etc/cron.d/s9s_backup

Finally, you can of course edit, change and make whatever modifications you like to the s9s_backup script if you want to customize it further.