Online Full/Incremental MySQL backup via xtrabackup open source tool

Hi Folks,
Percona XtraBackup is an open source, free MySQL hot backup software that performs non-blocking backups for InnoDB. With Percona XtraBackup, you can achieve the following benefits:

• Backups that complete quickly and reliably
• Uninterrupted transaction processing during backups
• Savings on disk space and network bandwidth
• Automatic backup verification
• Higher uptime due to faster restore time

It performs streaming, compressed, and incremental backups to your MySQL backup database.
So lets see, here I will demonstrate you following things-
(i) Automate Full / Incremental backup via shell script
(ii) Keep only last X days of backup

Pre-requisite:-

(i) Download xtrabackup :
      Here I am using xtrabackup version 2.1.4 ( percona-xtrabackup-2.1.4-656-Linux-x86_64.tar.gz )
(ii) qpress-11-linux-x64.tar (it is required for de-compression)
(iii) plugin " perl-DBD-MySQL" should be installed  
      yum install perl-DBD-MySQL
(iv) cp  xtrabackup_56 /usr/bin

(v) USER privilege required for 'xtrabackup' user


  mysql> CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'password';

  mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backupuser'@'localhost';

  mysql> FLUSH PRIVILEGES; 

 Now below is the full backup script

Full-backup.sh

#! /bin/bash

now=`date +%d-%m-%Y_%T`
TYPE="full-bak.log"
source /dbscriptlog/script/variable.sh
$XtraBackup/innobackupex --host=$h --user=$u --password=$p --slave-info $BACKUP_DIR/ > $LOG_DIR/$HOSTNAME-$TYPE 2>&1
mv $LOG_DIR/$HOSTNAME-$TYPE $LOG_DIR/$HOSTNAME-$TYPE-$now

## Get the latest backup directory from /backup path
ls -lhtr $BACKUP_DIR > $_file
LAST_BACKUP_DIR=`awk '{print $9}' $_file | tail -1`
rm $_file

## Delete X days older file ###
find $BACKUP_DIR -type d -name '*20*' -mtime +$DAYS_KEEP -exec rm -rf  {} \;

Contents of variable.sh file which is including in full-backup.sh and incr-backup.sh script.


_file=/tmp/p1.txt
HOSTNAME=`hostname`
BACKUP_DIR=/backup/mysql
LOG_DIR=/dbscriptlog/log/backuplog
XtraBackup=<PATH_OF_PERCONA_PKG>/percona-xtrabackup-2.1.4-Linux-x86_64/bin

u=backuser
p='backuppassword'
h='localhost'
DAYS_KEEP=X               ## (Number of Days to store mysql backup)

incr-backup.sh   

#! /bin/bash

now=`date +%d-%m-%Y_%T`
source /dbscriptlog/script/variable.sh
TYPE="incremental-bak.log"
ls -lhtr $BACKUP_DIR > $_file
LAST_BACKUP_DIR=`awk '{print $9}' $_file | tail -1`
rm $_file
$XtraBackup/innobackupex --host=$h --user=$u --password=$p --slave-info --incremental $BACKUP_DIR/ --incremental-basedir=$BACKUP_DIR/$LAST_BACKUP_DIR > $LOG_DIR/$HOSTNAME-$TYPE 2>&1
mv $LOG_DIR/$HOSTNAME-$TYPE $LOG_DIR/$HOSTNAME-$TYPE-$now

## Delete X days older file ###
find $BACKUP_DIR -type d -name '*20*' -mtime +$DAYS_KEEP -exec rm -rf  {} \;