Automatic incremental MySQL backup with xtrabackup.

#!/bin/bash

now=`date +%Y-%m-%d_%H-%M-%S`
backup_directory="/media/data/backup"
current_backup=$backup_directory/$now"_full"

logfile="/var/log/db-backup/"$now"_full"

mysql_user="root"
mysql_pass=""


echo "========================================="
echo "        MySQL Data Backup (Full)"
echo "========================================="

#perform full backup
innobackupex --ibbackup=xtrabackup_55 --parallel=4 --user=$mysql_user --password=$mysql_pass --no-timestamp $current_backup 2> $logfile

# see if the backup completed sucsessfully
if ! tail $logfile -n 1 | grep completed.OK
then
        rm -R $current_backup
        echo "\nERROR: backup failed! (log: "$logfile")"
  exit 1
fi

echo $current_backup > $backup_directory/last_backup

echo "Backup Completed OK!"

#!/bin/bash

now=`date +%Y-%m-%d_%H-%M-%S`
backup_directory="/media/data/backup"
current_backup=$backup_directory/$now"_in"
last_backup=`tail $backup_directory/last_backup -n1`

logfile="/var/log/db-backup/"$now"_in"

mysql_user="root"
mysql_pass=""


echo "========================================="
echo "      MySQL Data Backup (Incremental)"
echo "========================================="

#perform incremental backup from where the last backup stoped
innobackupex --ibbackup=xtrabackup_55 --parallel=4 --user=$mysql_user --password=$mysql_pass --no-timestamp --incremental --incremental-basedir=$last_backup $current_backup 2> $logfile

# see if the backup completed sucsessfully
if ! tail $logfile -n 1 | grep completed.OK
then
        rm -R $current_backup
        echo "\nERROR: backup failed! (log: "$logfile")"
  exit 1
fi

echo $current_backup >> $backup_directory/last_backup

echo "Backup Completed OK!"

#!/bin/bash

export PATH=$PATH:/usr/local/mysql/bin

echo "========================================="
echo "           MySQL Data Restore"
echo "========================================="

if [ $# -ne 1 ];
then
 echo "ERROR: missing parameter. Needs a filename that contains a list of backups to restore...."
exit 1
fi

full_backup=`head $1 -n 1`

active_backup="/media/data/backup/active_restore"
active_incremental="/media/data/backup/active_incremental"

logfile="/var/log/db-backup/restore_"`date +%Y-%m-%d_%H-%M-%S`

mysql_user="innobackup"
mysql_pass="b@ckstabber"
mysql_data_path="/media/data/mysql"
mysql_log_path="/media/logs/mysql"


if [ -d $active_backup ];
then
 rm -R $active_backup
fi

echo "copying full backup files: " $full_backup
mkdir $active_backup
cp -r $full_backup/* $active_backup

echo "preparing full backup..."
innobackupex --ibbackup=xtrabackup_55 --parallel=4 --user=$mysql_user --password=$mysql_pass --apply-log --redo-only $active_backup 2> $logfile

# see if the apply-log completed sucsessfully
if ! tail $logfile -n 1 | grep completed.OK
then
        echo "\nERROR: restore failed! (log: "$logfile")"
        rm -R $active_backup
        exit 1
fi


grep _in $1 | while read incremental_backup ; do

 if [ -d $active_incremental ];
 then
  rm -R $active_incremental
 fi

 echo "copying incremental backup: " $incremental_backup
 mkdir $active_incremental
 cp -r $incremental_backup/* $active_incremental

 find $incremental_backup -name .delta -empty -maxdepth 2 -exec rm {} \;

        echo "preparing incremetal backup..."
 innobackupex --ibbackup=xtrabackup_55 --parallel=4 --user=$mysql_user --password=$mysql_pass --apply-log --redo-only $active_backup --incremental-dir=$active_incremental 2>> $logfile

 rm -R $active_incremental

 # see if the apply incremental completed sucsessfully
 if ! tail $logfile -n 1 | grep completed.OK
 then
         echo "\nERROR: restore failed! (log: "$logfile")"
         rm -R $active_backup
         exit 1
 fi

done

echo "applying Logs"
innobackupex --ibbackup=xtrabackup_55 --parallel=4 --user=$mysql_user --password=$mysql_pass --apply-log $active_backup 2>> $logfile

# see if the apply log completed sucsessfully
if ! tail $logfile -n 1 | grep completed.OK
then
 echo "\nERROR: restore failed! (log: "$logfile")"
        rm -R $active_backup
        exit 1
fi

service mysql stop

echo "deleting mysql data files..."
rm -R $mysql_data_path/*


echo "restoring data files..."

#can use the innobackup script to restore data
#innobackupex --ibbackup=xtrabackup_55 --user=$mysql_user --password=$mysql_pass --copy-back $active_backup 2>> $logfile

#or just copy the data directory back
mv $active_backup/* $mysql_data_path/.

#delete all log files to start with fresh bin and mysql logs
rm -R $mysql_log_path/*
mkdir $mysql_log_path/bin

echo "setting file permissions.."
chmod 700 -R $mysql_data_path/ && chown mysql:mysql -R $mysql_data_path/
chmod 700 -R $mysql_log_path/ && chown mysql:mysql -R $mysql_log_path/

rm -R $active_backup/*
rmdir $active_backup
#rm $mysql_data_path/xtrabackup*

service mysql start

echo "Restore Completed OK!"

-- Vladimir Getmanshcuk aka vladget