How to use innobackupex to online backup innodb database for Percona 5.5 or MariaDB 10.0

Jephe Wu - http://linuxtechres.blogspot.com

Environment: Percona server 5.5 running on both server db01 and db02,  full backup folder on db02 is /data/fullbackup, incremental backup folder on db02 is /data/incremental, mysql datadir on both server is /srv/mysql/data

This post is applicable to any MySQL, MariaDB database with innodb engine, not only Percona server .

Objective: online backup innodb database from db1 to db2 by Percona innobackupex

Concept:
Daily backup is incremental backup. On every Wednesday, we will apply incremental backup into mysql database on db02.



Steps

1. initial full backup on db01 and apply logs
We will do a full backup on db01 then apply transaction logs to make it consistent:

# innobackupex --user=root --password=password /data/fullbackup --slave-info --no-timestamp  --parallel=4
# innobackupex --apply-log --redo-only --use-memory=4G /data/fullbackup

2. Copy whole directory /data/fullbackup to db2 

# cd /data/fullbackup
# ssh db02 'rm -fr /data/fullbacukp/*'
# tar cpf - . | ssh db02 'cd /data/fullbackup; tar xvpf - '

3.  copy back to datadir on db02
ssh into db2 as root, assume mysql data directory on db02 is /srv/mysql/data, ssh into db02 to run commands below which performs the restoration of a backup to database server's datadir

# rm -fr /srv/mysql/data/*
# innobackupex --copy-back /data/fullbackup
# chown mysql:mysql -R /srv/mysql/data'

Now you can start up mysql from db02.

4. incremental backup done by cronjob script daily

[root@db01 cron.d]# more /root/bin/innobackupex.sh
#!/bin/sh
# do incremental backup with stream on db01 daily as follows
LSN=`ssh db02 'grep to_lsn /data/fullbackup/xtrabackup_checkpoints' | cut -d ' ' -f 3`
LOG=/var/log/innobackupex.log

(innobackupex --user=root --password=password  --use-memory=4G --incremental --incremental-lsn=$LSN --stream=xbstream ./ | ssh root@db02 "cat - | xbstream -x -v -C  /data/incremental" ) > $LOG 2>&1


# apply incremental log with --redo-only on db02
ssh db02 'innobackupex --apply-log --redo-only --use-memory=4G /data/fullbackup --incremental-dir=/data/incremental' >> $LOG 2>&1

# clean up
ssh db02 'rm -fr /data/incremental/*'

# actual restore on db02, only run on every Wednesday
DAY=`date +%w`
if [ $DAY -eq 3 ];then

ssh db02 '/etc/init.d/mysql stop; sync; sleep 10'
ssh db02 'rm -fr /srv/mysql/data/*;innobackupex --copy-back /data/fullbackup;chown mysql:mysql -R /srv/mysql/data'
# Note: you can use --move-back instead of above --copy-back if you are confident

ssh db02 '/etc/init.d/mysql restart'
mutt -s "db01 incremental innobackpuex - `tail -1 $LOG`" jephewu@gmail.com < $LOG
fi

5. setup cronjob on db01 to run from Mon-Fri on 8am daily

[root@db01 cron.d]# more /etc/cron.d/innobackupex
0 8 * * 1-5 root /root/bin/innobackupex.sh >/dev/null 2>&1

6. References
http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/innobackupex_script.html