Mysql innobackupex full and incremental backups

Mysql backup strategy using Innobackupex/Xtrabackup :

If we are planning to automate backup of our Mysql production instance, we can think of two approaches :

  1. A full backup followed by cumulative backups i.e All incremental backups with last full backup as base directory.
  2. A full backup followed by differential backups i.e All incremental backups with last incremental/Full backup as base directory.

Both approaches have their own benefits and drawbacks.

The first approach would need little bit more backup storage since we would always perform incremental backup from the last full backup checkpoint. Also the time required to perform incremental backup would be more compared to the second approach.

The main drawback of having the second approach is that we would be spending more time during backup restore process, mainly we need to to prepare the full backup by applying all the subsequent incrementals to it.

A better approach would be definitely the first one since it saves our time while we need to restore the database to a particular point, we just need to apply required incremental to the base backup.

Below are few innobackupex examples :

Full Backup :

/usr/bin/innobackupex --slave-info --user=${User} --password=${Password} --defaults-file=${DefaultsFile} --compress /u01/mysql/backup/fullbackup/

We are using compress option which would compress our backups and save us atleast 2x times of backup storage. slave-info useful while backing up the slave server, generates xtrabackup_slave_info file with master information.

Incremental Backups :

Incremental backup with full backup as base:

/usr/bin/innobackupex --slave-info --user=${User} --password=${Password} --defaults-file=${DefaultsFile} --incremental /u01/mysql/backup/incremental/ --incremental-basedir=/u01/mysql/backup/fullbackup/2017-02-09_02-03-43

Here incrementals can be compressed as well (depends on size of database).

Incremental backup with incremental backup as base:

/usr/bin/innobackupex --slave-info --user=${User} --password=${Password} --defaults-file=${DefaultsFile} --incremental /u01/mysql/backup/incremental/ --incremental-basedir=/u01/mysql/backup/incremental/2017-02-09_02-23-56

/usr/bin/innobackupex --slave-info --user=${User} --password=${Password} --defaults-file=${DefaultsFile} --incremental /u01/mysql/backup/incremental/ --incremental-basedir=/u01/mysql/backup/incremental/2017-02-09_03-07-34

Lets copy them to custom folders just to be clear

cp -r /u01/mysql/backup/fullbackup/2017-02-09_02-03-43 /u01/mysql/backup/full
cp -r /u01/mysql/backup/incremental/2017-02-09_02-23-56 /u01/mysql/backup/incr1
cp -r /u01/mysql/backup/incremental/2017-02-09_03-07-34 /u01/mysql/backup/incr2
cp -r /u01/mysql/backup/incremental/ 2017-02-09_03-29-46 /u01/mysql/backup/incr3

We can automate the backup process by writing a simple shell/perl script, example here.

Restoring backups :

Uncompress Full backup

innobackupex --decompress /u01/mysql/backup/full

Apply all incremental backups to full backup

innobackupex --apply-log --redo-only /u01/mysql/backup/full
innobackupex --apply-log --redo-only /u01/mysql/backup/full --incremental-dir=/u01/mysql/backup/incr1
innobackupex --apply-log --redo-only /u01/mysql/backup/full --incremental-dir=/u01/mysql/backup/incr2
innobackupex --apply-log /u01/mysql/backup/full --incremental-dir=/u01/mysql/backup/incr3
innobackupex --apply-log /u01/mysql/backup/full

–redo-only option skips rollback, it should be used when merging all incrementals except the last one.

Cleanup existing data in Mysql data directory (assume /u01/mysql/mysql_new/data/) and copy all the files from backup directory to original locations.

rm -rf /u01/mysql/mysql_new/data/*
innobackupex --copy-back --defaults-file=/u01/mysql/mysql_new/my.cnfnew /u01/mysql/backup/full


Note that decompress option does not remove all the previous compressed(*.qp) files. We need to remove them to save some storage inside mysql datadir.

for bf in `find . -iname "*.qp"` ; do rm -rf $bf ; done




One thought to “Mysql innobackupex full and incremental backups”

Leave a Reply

Your email address will not be published.