Follow us on Social Media:

MySQL Database Administrator

Boost Your Quick Knowledge in MySQL DBA Work, Concepts, Tips and Trick

Friday, 24 May 2019

MySQL Incremental Backup Script

How to take incremental backup in MySQL ?

::#Point-in-Time Backup and Recovery in MySQL

Using large production databases must required an Incremental backup to ensure Safe and Reliable database system. In this article, I'm writing a shell script for automatic MySQL FULL&Incremental Backup which can be schedule by cron job for automation of their result.
This Script is written in very simple way and logic which you can easily understand.

Features....

  • Automation of Daily Full Backup using Percona XtraBackup
  • No production loss during the backup 
  • Creating a mysql-binlog check-points in every backup so that you can easily use the same for point-in-time recovery or setup new replication server.
  • Create Backup Directory if not exist.
  • No repetition of same day backup.
  • Creating a Log File for whole process.
  • Sending Email Alerts for any failure/success backup  
  • Easy-to-Understand 

Limitation...

  • You must have to installed Percona XtraBackup 2.4
  • Must have to configured Mail smtp function (mailx) for sending email alerts
  • Occupy the disk storage if data size is very large

#!/bin/bash
#########################################
# By-Alok Kumar Singh@www.getmsql.info###
# Features-                           ###
# FULL , Incremental, Restore ,Daily/ ###
# Weekly Backup Rotation, Mail Alert  ###
# on Successful/Failure               ###
######################################### 


SECRET="--user=MySQLusername --password=MySQLpassword"

## Parameters

BACKUP_DIR_ROOT=/backup/xtrabackup
BACKUP_DIR_Daily=$BACKUP_DIR_ROOT/Daily
DATA_DIR=/var/lib/mysql
HOSTIP="192.168.1.1"
SERVERNAME="Production-Slave-DB"
TAR_COMPRESS="1"  ## 0 - for disable

emails="info.getmysql@gmail.com" ## Add Multiple emails with space

usage() { 
echo "usage: $(basename $0) [option]" 
echo "option=full: Perform Full Backup"
echo "option=incremental: Perform Incremental Backup"
echo "option=restore: Start to Restore! Be Careful!! "
echo "option=help: show this help"
}

fullbackup() {
local TMPFILE=/tmp/XtraBackup$$.tmp
if [ ! -d $BACKUP_DIR_ROOT ]
then
mkdir $BACKUP_DIR_ROOT
fi
if [ ! -d $BACKUP_DIR_Daily ]
then
mkdir $BACKUP_DIR_Daily
fi

rm -rf $BACKUP_DIR_Daily/*

echo "$(date +%d%m%y" ""%T.%3N") :: Cleanup the backup folder is done! Starting backup" >> $BACKUP_DIR_Daily/xtrabackup_full.log

#xtrabackup --backup $SECRET -u root -p --history --compress --slave-info --compress-threads=4 --target-dir=$BACKUP_DIR_Daily/FULL

xtrabackup --backup $SECRET --history  --target-dir=$BACKUP_DIR_Daily/FULL > $TMPFILE 2>&1
#xtrabackup --backup --user=root --password=Mysqlroot@123 --history --parallel=8 --compress --compress-threads=8 --target-dir=/tmp/FULL /tmp/log 2>&1

if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then
echo "$(date +%d%m%y" ""%T.%3N") :: ERROR:XtraBackup_FULL failed" >> $BACKUP_DIR_Daily/xtrabackup_full.log
echo "$(date +%d%m%y" ""%T.%3N") :: Sending Mail Alert to $emails for Backup failed in execution time" >> $BACKUP_DIR_Daily/xtrabackup_full.log
#echo -e $ERRMSG | mail -r "root@alert.com" -s "XtraBackup failed" $emails
mail -r "root@alert.com" -s "ERROR:XtraBackup_FULL failed on $SERVERNAME@$HOSTIP at $(date)" $emails < $TMPFILE
rm -rf $TMPFILE
rm -rf $BACKUP_DIR_Daily/FULL
exit 1
fi
rm -rf $TMPFILE
echo "$(date +%d%m%y" ""%T.%3N") :: XtraBackup_FULL Done!" >> $BACKUP_DIR_Daily/xtrabackup_full.log
echo -e "XtraBackup_FULL Successful! at $(date)" | mail -r "root@alert.com" -s "OK:XtraBackup_FULL Successful! on $SERVERNAME@$HOSTIP at $(date)" $emails 
}

incremental_backup(){
local  TMPFILE=/tmp/XtraBackup_inc$$.tmp
if [ ! -d $BACKUP_DIR_Daily/FULL ]
then
echo "$(date +%d%m%y" ""%T.%3N") :: ERROR:Unable to find the FULL Backup. aborting.....!" >> $BACKUP_DIR_Daily/xtrabackup_inc.log
exit -1
fi

if [ ! -f $BACKUP_DIR_Daily/last_incremental_number ]; then
NUMBER=1
else
NUMBER=$(($(cat $BACKUP_DIR_Daily/last_incremental_number) + 1))
fi
echo "$(date +%d%m%y" ""%T.%3N") :: Starting Incremental backup inc$NUMBER" >> $BACKUP_DIR_Daily/xtrabackup_inc.log

if [ $NUMBER -eq 1 ]
then
xtrabackup --backup $SECRET --history  --incremental --target-dir=$BACKUP_DIR_Daily/inc$NUMBER --incremental-basedir=$BACKUP_DIR_Daily/FULL > $TMPFILE 2>&1
if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then

echo "$(date +%d%m%y" ""%T.%3N") :: ERROR:XtraBackup_inc$NUMBER failed" >> $BACKUP_DIR_Daily/xtrabackup_inc.log
echo "$(date +%d%m%y" ""%T.%3N") :: Sending Mail Alert to $emails for Backup failed in execution time" >> $BACKUP_DIR_Daily/xtrabackup_inc.log
mail -r "root@alert.com" -s "ERROR:XtraBackup_inc$NUMBER failed on $SERVERNAME@$HOSTIP at $(date)" $emails < $TMPFILE
rm -rf $TMPFILE
rm -rf $BACKUP_DIR_Daily/inc$NUMBER
exit 1
else
echo "$(date +%d%m%y" ""%T.%3N") :: XtraBackup_inc$NUMBER Done!" >> $BACKUP_DIR_Daily/xtrabackup_inc.log
echo -e "XtraBackup_inc$NUMBER Successful! at $(date)" | mail -r "root@alert.com" -s "OK:XtraBackup_inc$NUMBER Successful! on $SERVERNAME@$HOSTIP at $(date)" $emails 

fi
rm -rf $TMPFILE

else
xtrabackup --backup $SECRET --history --incremental --target-dir=$BACKUP_DIR_Daily/inc$NUMBER --incremental-basedir=$BACKUP_DIR_Daily/inc$(($NUMBER - 1)) > $TMPFILE 2>&1

if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then

echo "$(date +%d%m%y" ""%T.%3N") :: ERROR:XtraBackup_inc$NUMBER failed" >> $BACKUP_DIR_Daily/xtrabackup_inc.log
echo "$(date +%d%m%y" ""%T.%3N") :: Sending Mail Alert to $emails for Backup failed in execution time" >> $BACKUP_DIR_Daily/xtrabackup_inc.log
mail -r "root@alert.com" -s "ERROR:XtraBackup_inc$NUMBER failed on $SERVERNAME@$HOSTIP at $(date)" $emails < $TMPFILE
rm -rf $TMPFILE
rm -rf $BACKUP_DIR_Daily/inc$NUMBER
exit 1
else
echo "$(date +%d%m%y" ""%T.%3N") :: XtraBackup_inc$NUMBER Done!" >> $BACKUP_DIR_Daily/xtrabackup_inc.log
echo -e "XtraBackup_inc$NUMBER Successful! at $(date)" | mail -r "root@alert.com" -s "OK:XtraBackup_inc$NUMBER Successful! on $SERVERNAME@$HOSTIP at $(date)" $emails 

fi
rm -rf $TMPFILE

fi

echo $NUMBER > $BACKUP_DIR_Daily/last_incremental_number

}

restore()
{
local  TMPFILE=/tmp/XtraBackup_restore$$.tmp
timestamp=$(date +%Y%m%d_%H%M%S)
# echo `date '+%Y-%m-%d %H:%M:%S:%s'`": Decompressing the FULL backup" >> $BACKUP_DIR_Daily/xtrabackup-restore.log
# xtrabackup $SECRET --decompress --remove-original --parallel=4 --target-dir=$BACKUP_DIR_Daily/FULL 
# echo `date '+%Y-%m-%d %H:%M:%S:%s'`": Decompressing Done !!!" >> $BACKUP_DIR_Daily/xtrabackup-restore.log
echo "$(date +%d%m%y" ""%T.%3N") :: Prepareing FULL Backup ..." >> $BACKUP_DIR_Daily/xtrabackup_restore.log

xtrabackup $SECRET --prepare  --apply-log-only --target-dir=$BACKUP_DIR_Daily/FULL > $TMPFILE 2>&1

if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then

echo "$(date +%d%m%y" ""%T.%3N") :: ERROR:XtraBackup_FULL_Prepare failed" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
echo "$(date +%d%m%y" ""%T.%3N") :: Sending Mail Alert to $emails for Backup failed in execution time" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
mail -r "root@alert.com" -s "ERROR:XtraBackup_FULL_Prepare failed on $SERVERNAME@$HOSTIP at $(date)" $emails < $TMPFILE
rm -rf $TMPFILE
exit 1
else
echo "$(date +%d%m%y" ""%T.%3N") :: XtraBackup_FULL_Preparation Done!" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
echo -e "XtraBackup_FULL_Preparation Successful! at $(date)" | mail -r "root@alert.com" -s "OK:XtraBackup_FULL_Preparation Successful! on $SERVERNAME@$HOSTIP at $(date)" $emails 

fi
rm -rf $TMPFILE        
if [ ! -f $BACKUP_DIR_Daily/last_prepare_number ]; then
P=1
else
P=$(cat $BACKUP_DIR_Daily/last_prepare_number)
fi
#P=1
while [ -d $BACKUP_DIR_Daily/inc$P ] && [ -d $BACKUP_DIR_Daily/inc$(($P+1)) ]
do
  # echo `date '+%Y-%m-%d %H:%M:%S:%s'`": Decompressing incremental:$P" >> $BACKUP_DIR_Daily/xtrabackup-restore.log
  # xtrabackup $SECRET --decompress --remove-original --parallel=4 --target-dir=$BACKUP_DIR_Daily/inc$P 
  # echo `date '+%Y-%m-%d %H:%M:%S:%s'`": Decompressing incremental:$P Done !!!" >> $BACKUP_DIR_Daily/xtrabackup-restore.log
  echo "$(date +%d%m%y" ""%T.%3N") :: Prepareing incremental backup inc$P" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
  xtrabackup $SECRET --prepare --apply-log-only --target-dir=$BACKUP_DIR_Daily/FULL --incremental-dir=$BACKUP_DIR_Daily/inc$P > $TMPFILE 2>&1
  
if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then

echo "$(date +%d%m%y" ""%T.%3N") :: ERROR:XtraBackup_inc$P Prepare failed" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
echo "$(date +%d%m%y" ""%T.%3N") :: Sending Mail Alert to $emails for Backup failed in execution time" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
mail -r "root@alert.com" -s "ERROR:XtraBackup_inc$P Prepare failed on $SERVERNAME@$HOSTIP at $(date)" $emails < $TMPFILE
rm -rf $TMPFILE
exit 1
else
echo "$(date +%d%m%y" ""%T.%3N") :: XtraBackup_inc$P Preparation Done!" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
echo -e "XtraBackup_inc$P preparation Successful! at $(date)" | mail -r "root@alert.com" -s "OK:XtraBackup_inc$P Preparation Successful! on $SERVERNAME@$HOSTIP at $(date)" $emails 

fi
rm -rf $TMPFILE
P=$(($P+1))
echo $P > $BACKUP_DIR_Daily/last_prepare_number
done

if [ -d $BACKUP_DIR_Daily/inc$P ]
then
# echo `date '+%Y-%m-%d %H:%M:%S:%s'`": Decompressing the last incremental:$P" >> $BACKUP_DIR_Daily/xtrabackup-restore.log
# xtrabackup $SECRET --decompress --remove-original --parallel=4 --target-dir=$BACKUP_DIR_Daily/inc$P 
# echo `date '+%Y-%m-%d %H:%M:%S:%s'`": Decompressing the last incremental:$P Done !!!" >> $BACKUP_DIR_Daily/xtrabackup-restore.log
echo "$(date +%d%m%y" ""%T.%3N") :: Prepareing last incremental backup inc$P" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
xtrabackup $SECRET --prepare --target-dir=$BACKUP_DIR_Daily/FULL --incremental-dir=$BACKUP_DIR_Daily/inc$P > $TMPFILE 2>&1

if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then

echo "$(date +%d%m%y" ""%T.%3N") :: ERROR:XtraBackup_last_inc$P Prepare failed" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
echo "$(date +%d%m%y" ""%T.%3N") :: Sending Mail Alert to $emails for Backup failed in execution time" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
mail -r "root@alert.com" -s "ERROR:XtraBackup_last_inc$P Prepare failed on $SERVERNAME@$HOSTIP at $(date)" $emails < $TMPFILE
rm -rf $TMPFILE
exit 1
else
echo "$(date +%d%m%y" ""%T.%3N") :: XtraBackup_last_inc$P Preparation Done!" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
echo -e "XtraBackup_last_inc$P Preparation Successful! at $(date)" | mail -r "root@alert.com" -s "OK:XtraBackup_last_inc$P Preparation Successful! on $SERVERNAME@$HOSTIP at $(date)" $emails 

fi
rm -rf $TMPFILE

echo $P > $BACKUP_DIR_Daily/last_prepare_number 

fi
if [ ! -d $BACKUP_DIR_ROOT/Weekly ]
then
mkdir $BACKUP_DIR_ROOT/Weekly
fi
destdir="$BACKUP_DIR_ROOT/Weekly/FULLBACKUP_till_$timestamp"
mv $BACKUP_DIR_Daily $destdir

#### Compress........

if [ "$TAR_COMPRESS" == "1" ]; then
echo "$(date +%d%m%y" ""%T.%3N") :: $destdir compression Enabled!" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
echo "$(date +%d%m%y" ""%T.%3N") :: $destdir compression Starting!" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
#cd $BACKUP_DIR/$NOWDAY && sudo tar --create --gzip --file=$db.sql.tgz $db.sql --remove-files 
tar --create --gzip --file=$destdir.tgz $destdir --remove-files 
echo "$(date +%d%m%y" ""%T.%3N") :: $destdir compression Done!" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
fi

}

if [ $# -eq 0 ]
then
usage
exit 1
fi

case $1 in
"full")
fullbackup
;;
"incremental")
incremental_backup
;;
"restore")
restore
;;
"help")
usage

;;
*) echo "invalid option";;
esac
    

##########################################################
### Copyright@ Alok Kumar Singh with www.getmysql.info ###
##########################################################
How to use the Script ?

  • Save the script as xtrabackup.sh (Script must have 777 permission)
  • Define the below parameters according to your environment...
         SECRET="--user=MySQLusername --password=MySQLpassword"
         BACKUP_DIR_ROOT=/backup/xtrabackup
         BACKUP_DIR_Daily=$BACKUP_DIR_ROOT/Daily
         DATA_DIR=/var/lib/mysql
         HOSTIP="192.168.1.1"
         SERVERNAME="Production-Slave-DB"
         TAR_COMPRESS="1"  ## 0 - for disable
  • For taking full backup run -: ./xtrabackup.sh full
  • For taking incremental backup run -:  ./xtrabackup.sh incremental
  • For taking restore backup run -: ./xtrabackup.sh restore
  • Restore command is very critical . It will prepare FULL and all incremental in FULL.

How to configure the cron job for automation of this script ?

# Example of job definition:
# .---------------- minute (0 - 59)
# |  .------------- hour (0 - 23)
# |  |  .---------- day of month (1 - 31)
# |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...
# |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# |  |  |  |  |
# *  *  *  *  * user-name  command to be executed

##This CRON will take Daily FULL backup at 12:01 AM and then incremental on every 2 Hour . Before taking the full backup it will remove all the previous backups. You can change the remove policy according to your disk space.
## Herein I'm not automate restore command . I will perform this task manually once needed. You can do as per your policy.

01 00 * * * root cd /xtra-bkp-incr && ./xtrabackup.sh full    
01 */2 * * * root cd /xtra-bkp-incr && ./xtrabackup.sh incremental

For any Query on this script you can contact us or Submit you Query Here!

Please write you valuable comments and feedback in the comment box. Your valuable feedback is highly appreciated .

Please Follow us to update and notify for such type of articles and scripts. 


Please Like and Subscribe us ....

Please visit the website on daily basis , Your New articles will publish at any moment. Please Subscribe us for getting Latest Updates...