[script] automatic backups (incremental & full) and restore :)

Hi, first of all, thanks to the community for this awesome tool, is amazing the things you can do :slight_smile:

Yesterday I spent the day writing this script, you can use it to do full or incremental backups of your /var/lib/mysql using xtrabackup. It automatically creates the incremental folders in order and in case of restore, it automatically applies the differential in order to get a functional preparated full backup.

Please read it and if you think there’s something weird/wrong dont hesitate to tell me


#!/bin/bash -x
#Tue Jul 2 15:32:21 CEST 2013
#Made by Edward Z.
set -e #stops execution if a variable is not set
set -u #stop execution if something goes wrong

usage() { 
echo "usage: $(basename $0) [option]" 
echo "option=full: do a full backup of vinnie /var/lib/mysql using innobackupex, aprox time 6 hours."
echo "option=incremental: do a incremental backup"
echo "option=restore: this will restore the latest backup to vinnie, BE CAREFUL!"
echo "option=help: show this help"
}

full_backup() {
date
if [ ! -d $BACKUP_DIR ]
then
echo "ERROR: the folder $BACKUP_DIR does not exists"
exit 1
fi
echo "doing full backup..."
echo "cleaning the backup folder..."
rm -rf $BACKUP_DIR/*
echo "cleaning done!"
innobackupex $ARGS $BACKUP_DIR/FULL
date
echo "backup done!, now uncompressing the files..."
for bf in `find $BACKUP_DIR/FULL -iname "*\.qp"`; do qpress -d $bf $(dirname $bf) ;echo "processing" $bf; rm $bf; done
date
echo "uncompressing done!, preparing the backup for restore..."
innobackupex --apply-log --redo-only $BACKUP_DIR/FULL
date
echo "preparation done!"
}
incremental_backup()
{
if [ ! -d $BACKUP_DIR/FULL ]
then
echo "ERROR: no full backup has been done before. aborting"
exit -1
fi

#we need the incremental number
if [ ! -f $BACKUP_DIR/last_incremental_number ]; then
NUMBER=1
else
NUMBER=$(($(cat $BACKUP_DIR/last_incremental_number) + 1))
fi
date
echo "doing incremental number $NUMBER"
if [ $NUMBER -eq 1 ]
then
innobackupex $ARGS --incremental $BACKUP_DIR/inc$NUMBER --incremental-basedir=$BACKUP_DIR/FULL
else
innobackupex $ARGS --incremental $BACKUP_DIR/inc$NUMBER --incremental-basedir=$BACKUP_DIR/inc$(($NUMBER - 1))
fi
date
echo $NUMBER > $BACKUP_DIR/last_incremental_number
echo "incremental $NUMBER done!, now uncompressing the files..."
for bf in `find $BACKUP_DIR/inc$NUMBER -iname "*\.qp"`; do qpress -d $bf $(dirname $bf) ;echo "processing" $bf; rm $bf; done
date
echo "uncompressing done!, the preparation will be made when the restore is needed"

}

restore()
{
echo "WARNING: are you sure this is what you want to do? (Enter 1 or 2)"
select yn in "Yes" "No"; do
case $yn in
Yes ) break;;
No ) echo "aborting... that was close."; exit;;
esac
done

echo "cross your fingers :)"
date
echo "doing restore..."
#innobackupex --apply-log --redo-only $BACKUP_DIR/FULL

#we append all the increments
P=1
while [ -d $BACKUP_DIR/inc$P ] && [ -d $BACKUP_DIR/inc$(($P+1)) ]
do
echo "processing incremental $P"
innobackupex --apply-log --redo-only $BACKUP_DIR/FULL --incremental-dir=$BACKUP_DIR/inc$P
P=$(($P+1))
done

if [ -d $BACKUP_DIR/inc$P ]
then
#the last incremental has to be applied without the redo-only flag
echo "processing last incremental $P"
innobackupex --apply-log $BACKUP_DIR/FULL --incremental-dir=$BACKUP_DIR/inc$P
fi

#we prepare the full
innobackupex --apply-log $BACKUP_DIR/FULL

#finally we copy the folder
cp -r $DATA_DIR $DATA_DIR.back
rm -rf $DATA_DIR/*
innobackupex --copy-back $BACKUP_DIR/FULL

chown -R mysql:mysql $DATA_DIR

}

#######################################
#######################################
#######################################

BACKUP_DIR=/tmp/backup
DATA_DIR=/var/lib/mysql
USER_ARGS=" --user=root --password=password"

ARGS="--rsync $USER_ARGS --no-timestamp --compress --compress-threads=4"

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

case $1 in
"full")
full_backup
;;
"incremental")
incremental_backup
;;
"restore")
restore
;;
"help")
usage
break
;;
*) echo "invalid option";;
esac



Good script. Weird that innobackupex incrementals requires you to backup non-innodb artifacts in addition to the log files…forces the size of my incrementals to be at least the size of all my databases…any way around this?

Thanks for this script! Why use --compress just to uncompress the files immediately?

I was wondering the same thing but I think I ran across the answer, but it is pretty bizarre. There are two types of compression, there is the xbstream format and then there is the pipe to tar.gz. Apparently, you can’t ‘prepare’ a compressed backup if I’m reading correctly. So that means that if you do a hotbackup, you can write it out compressed (for some reason) then you have to decompress it so you can ‘prepare’/sync the logs and then you are done and can compress it again. Just sounds really stupid, I’m hoping someone can explain if I’m wrong or not because it will change my strategy.

My colleague changed the following to eliminate the unnecessary compress/decompress:

ARGS="--rsync $USER_ARGS --no-timestamp --compress --compress-threads=4"

changed to:

ARGS="--rsync $USER_ARGS --no-timestamp"

and in both full_backup() and incremental_backup(), remove the for loops that decompress.

Good Script, thanks brother. It is working fine.

Hello!

First of all, thank you so much. This script is exactly what I was looking for. And it works like a charm. :slight_smile:

I’ve made some minor modifications related to compression.
The latest innobackupex version supports –decompress
There is no need to uncompress the files after backup. You can create full and incremental backups using only compressed files.
You need to uncompress only before restore, so I did that: use –decompress before –apply-log

I’ve changed the BACKUP_DIR to use $(date +%Y-%m) parameter, so I can use this script in my crontab and have control over the periodicity.

I’m open to suggestions if I misunderstood something.

#!/bin/bash -x
#Tue Jul 2 15:32:21 CEST 2013
#Made by Edward Z.
set -e #stops execution if a variable is not set
set -u #stop execution if something goes wrong

usage() { 
echo "usage: $(basename $0) [option]" 
echo "option=full: do a full backup of vinnie /var/lib/mysql using innobackupex."
echo "option=incremental: do a incremental backup"
echo "option=restore: this will restore the latest backup to vinnie, BE CAREFUL!"
echo "option=help: show this help"
}

full_backup() {
date
if [ ! -d $BACKUP_DIR ]
then
mkdir $BACKUP_DIR
#echo "ERROR: the folder $BACKUP_DIR does not exists"
#exit 1
fi
echo "doing full backup..."
echo "cleaning the backup folder..."
rm -rf $BACKUP_DIR/*
echo "cleaning done! Starting backup"
innobackupex $ARGS $BACKUP_DIR/FULL
date
echo "backup done!" #, now uncompressing the files..."
# for bf in `find $BACKUP_DIR/FULL -iname "*\.qp"`; do qpress -d $bf $(dirname $bf) ;echo "processing" $bf; rm $bf; done
# date
# echo "uncompressing done!, preparing the backup for restore..."
# innobackupex --apply-log --redo-only $BACKUP_DIR/FULL
# date
# echo "preparation done!"
}

incremental_backup()
{
if [ ! -d $BACKUP_DIR/FULL ]
then
echo "ERROR: no full backup has been done before. aborting"
exit -1
fi

#we need the incremental number
if [ ! -f $BACKUP_DIR/last_incremental_number ]; then
NUMBER=1
else
NUMBER=$(($(cat $BACKUP_DIR/last_incremental_number) + 1))
fi
date
echo "doing incremental number $NUMBER"
if [ $NUMBER -eq 1 ]
then
innobackupex $ARGS --incremental $BACKUP_DIR/inc$NUMBER --incremental-basedir=$BACKUP_DIR/FULL 
else
innobackupex $ARGS --incremental $BACKUP_DIR/inc$NUMBER --incremental-basedir=$BACKUP_DIR/inc$(($NUMBER - 1)) 
fi
date
echo $NUMBER > $BACKUP_DIR/last_incremental_number
echo "incremental $NUMBER done!" #, now uncompressing the files..."
# for bf in `find $BACKUP_DIR/inc$NUMBER -iname "*\.qp"`; do qpress -d $bf $(dirname $bf) ;echo "processing" $bf; rm $bf; done
# date
# echo "uncompressing done!, the preparation will be made when the restore is needed"
}

restore()
{
echo "WARNING: are you sure this is what you want to do? (Enter 1 or 2)"
select yn in "Yes" "No"; do
case $yn in
Yes ) break;;
No ) echo "aborting... that was close."; exit;;
esac
done

echo "cross your fingers :)"
date
echo "decompressing full backup"
innobackupex --decompress $BACKUP_DIR/FULL 
echo "doing restore..."
innobackupex --apply-log --redo-only $BACKUP_DIR/FULL 

#we append all the increments
P=1
while [ -d $BACKUP_DIR/inc$P ] && [ -d $BACKUP_DIR/inc$(($P+1)) ]
do
echo "decompressing incremental $P"
innobackupex --decompress $BACKUP_DIR/inc$P 
echo "processing incremental $P"
innobackupex --apply-log --redo-only $BACKUP_DIR/FULL --incremental-dir=$BACKUP_DIR/inc$P 
P=$(($P+1))
done

if [ -d $BACKUP_DIR/inc$P ]
then
#the last incremental has to be applied without the redo-only flag
echo "decompressing incremental $P"
innobackupex --decompress $BACKUP_DIR/inc$P 
echo "processing last incremental $P"
innobackupex --apply-log $BACKUP_DIR/FULL --incremental-dir=$BACKUP_DIR/inc$P 
fi

#we prepare the full
innobackupex --apply-log $BACKUP_DIR/FULL 

#finally we copy the folder
cp -r $DATA_DIR $DATA_DIR.back
rm -rf $DATA_DIR/*
innobackupex --copy-back $BACKUP_DIR/FULL 

chown -R mysql:mysql $DATA_DIR

}

#######################################
#######################################
#######################################
BACKUP_DIR=/home/user/xtrabackup/$(date +\%Y-\%m)
DATA_DIR=/var/lib/mysql
USER_ARGS=" --user=backup--password=backuppass"

ARGS="--rsync $USER_ARGS --no-timestamp --compress --compress-threads=4"

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

case $1 in
"full")
full_backup
;;
"incremental")
incremental_backup
;;
"restore")
restore
;;
"help")
usage
break
;;
*) echo "invalid option";;
esac

Hi bigzaqui,

The script is working fine, but if end user choice the incremental backup to restore, in this case how need perform about it?

Thanks,

Hello all,

thanks for sharing,

but, after I try it, script not running, it’s only print string in echo.

script

#!/bin/bash -x
#Tue Jul 2 15:32:21 CEST 2013
#Made by Edward Z.
set -e #stops execution if a variable is not set
set -u #stop execution if something goes wrong

#######################################
#######################################
#######################################
BACKUP_DIR=/data/backups/$(date +\%Y-\%m)
DATA_DIR=/var/lib/mysql
USER_ARGS=" --user=userbackup--password=12345Ssh!"

ARGS="--rsync $USER_ARGS --no-timestamp --compress --compress-threads=4"

usage() {
echo "usage: $(basename $0) [option]" 
echo "option=full: do a full backup of vinnie /var/lib/mysql using innobackupex."
echo "option=incremental: do a incremental backup"
echo "option=restore: this will restore the latest backup to vinnie, BE CAREFUL!"
echo "option=help: show this help"
}

full_backup() {
date
if [ ! -d $BACKUP_DIR ]
then
mkdir $BACKUP_DIR
#echo "ERROR: the folder $BACKUP_DIR does not exists"
#exit 1
fi
echo "doing full backup..."
echo "cleaning the backup folder..."
rm -rf $BACKUP_DIR/*
echo "cleaning done! Starting backup"
innobackupex $ARGS $BACKUP_DIR/FULL
date
echo "backup done!" #, now uncompressing the files..."
# for bf in `find $BACKUP_DIR/FULL -iname "*\.qp"`; do qpress -d $bf $(dirname $bf) ;echo "processing" $bf; rm $bf; done
# date
# echo "uncompressing done!, preparing the backup for restore..."
# innobackupex --apply-log --redo-only $BACKUP_DIR/FULL
# date
# echo "preparation done!"
}

incremental_backup()
{
if [ ! -d $BACKUP_DIR/FULL ]
then
echo "ERROR: no full backup has been done before. aborting"
exit -1
fi

#we need the incremental number
if [ ! -f $BACKUP_DIR/last_incremental_number ]; then
NUMBER=1
else
NUMBER=$(($(cat $BACKUP_DIR/last_incremental_number) + 1))
fi
date
echo "doing incremental number $NUMBER"
if [ $NUMBER -eq 1 ]
then
innobackupex $ARGS --incremental $BACKUP_DIR/inc$NUMBER --incremental-basedir=$BACKUP_DIR/FULL
else
innobackupex $ARGS --incremental $BACKUP_DIR/inc$NUMBER --incremental-basedir=$BACKUP_DIR/inc$(($NUMBER - 1))
fi
date
echo $NUMBER > $BACKUP_DIR/last_incremental_number
echo "incremental $NUMBER done!" #, now uncompressing the files..."
# for bf in `find $BACKUP_DIR/inc$NUMBER -iname "*\.qp"`; do qpress -d $bf $(dirname $bf) ;echo "processing" $bf; rm $bf; done
# date
# echo "uncompressing done!, the preparation will be made when the restore is needed"
}

restore()
{
echo "WARNING: are you sure this is what you want to do? (Enter 1 or 2)"
select yn in "Yes" "No"; do
case $yn in
Yes ) break;;
No ) echo "aborting... that was close."; exit;;
esac
done

echo "cross your fingers :)"
date
echo "decompressing full backup"
innobackupex --decompress $BACKUP_DIR/FULL
echo "doing restore..."
innobackupex --apply-log --redo-only $BACKUP_DIR/FULL

#we append all the increments
P=1
while [ -d $BACKUP_DIR/inc$P ] && [ -d $BACKUP_DIR/inc$(($P+1)) ]
do
echo "decompressing incremental $P"
innobackupex --decompress $BACKUP_DIR/inc$P
echo "processing incremental $P"
innobackupex --apply-log --redo-only $BACKUP_DIR/FULL --incremental-dir=$BACKUP_DIR/inc$P
P=$(($P+1))
done

if [ -d $BACKUP_DIR/inc$P ]
then
#the last incremental has to be applied without the redo-only flag
echo "decompressing incremental $P"
innobackupex --decompress $BACKUP_DIR/inc$P
echo "processing last incremental $P"
innobackupex --apply-log $BACKUP_DIR/FULL --incremental-dir=$BACKUP_DIR/inc$P
fi

#we prepare the full
innobackupex --apply-log $BACKUP_DIR/FULL

#finally we copy the folder
cp -r $DATA_DIR $DATA_DIR.back
rm -rf $DATA_DIR/*
innobackupex --copy-back $BACKUP_DIR/FULL

chown -R mysql:mysql $DATA_DIR

}

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

case $1 in
"full")
full_backup
;;
"incremental")
incremental_backup
;;
"restore")
restore
;;
"help")
usage
break
;;
*) echo "invalid option";;
esac

Output after running script

[root@inc2 home]# /usr/bin/xtrabackup.sh 
+ set -e
+ set -u
++ date +%Y-%m-%d
+ BACKUP_DIR=/home/xtrabackup/2018-04-17
+ DATA_DIR=/var/lib/mysql
+ USER_ARGS=' --user=backupuser --password=password'
+ ARGS='--rsync --user=backupuser --password=password --no-timestamp --compress --compress-threads=4'
+ '[' 0 -eq 0 ']'
+ usage
++ basename /usr/bin/xtrabackup.sh
+ echo 'usage: xtrabackup.sh [option]'
usage: xtrabackup.sh [option]
+ echo 'option=full: do a full backup of vinnie /var/lib/mysql using innobackupex.'
option=full: do a full backup of vinnie /var/lib/mysql using innobackupex.
+ echo 'option=incremental: do a incremental backup'
option=incremental: do a incremental backup
+ echo 'option=restore: this will restore the latest backup to vinnie, BE CAREFUL!'
option=restore: this will restore the latest backup to vinnie, BE CAREFUL!
+ echo 'option=help: show this help'
option=help: show this help
+ exit 1

what’s wrong with my script?
please give me advice.

Thanks

Hi yudorahadya;

Looks like you need to pass it an argument to tell it what to do. Try “/usr/bin/xtrabackup.sh full” to do a full backup.

-Scott

Thanks Scott for advice.

But I have new problem after setup that script in cron tab.
for one month, running script full backup and incremental backup. But, file incremental it’s very large. So, it’s make hardisk memory fast fully in server.
I already check root cause. In my database many create table version with myisam. But, after read documentation percona, compressed file percona has only support in table version with innodb.
And then, I adding script to make file tar after create full backup and incremental backup. But, it didn’t solve problem. it same, make hardisk memory fast fully.

And the secound question, If make incremental backup, mysql running FLUSH TABLES WITH READ LOCK and it’s problem if there any problem for activity query in mysql. What can you do ?

So, Any Idea about it ?

Thank you

yudorahadya