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

bigzaqui · July 3, 2013, 6:14am

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

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

ifeatu · November 19, 2015, 9:51pm

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?

JohnLofgren · June 29, 2016, 9:35am

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

smga3000 · June 30, 2016, 3:14pm

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.

JohnLofgren · July 13, 2016, 9:01am

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.

ma.prakash · April 4, 2017, 7:20am

Good Script, thanks brother. It is working fine.

renanceratto · August 3, 2017, 9:16am

Hello!

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

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

tiench · November 1, 2017, 4:47am

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,

yudorahadya · May 2, 2018, 4:52am

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&#64;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

scott.nemes · May 3, 2018, 6:09pm

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

yudorahadya · July 9, 2018, 2:38am

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

prosaied · May 29, 2021, 12:07am

Thank you man, I really appreciated what you have done.

aranjith0 · May 5, 2022, 12:38pm

Hello,
I’m looking to schedule Percona backup for mongoDB scheduled via crontab.

Please provide any reference scripts which runs “pbm backup” automatically without manual intervention.

Planning to schedule below on cron:

pbm backup
PITR
pbm restore.

any help is appreciated.

Kunal_Bajpai · September 2, 2022, 7:24am

same problem here sir , actually i run command but it will print string of echo , how to resolve it.

Kunal_Bajpai · September 2, 2022, 8:59am

I am trying to run script, but as usually, it will print script same as which is wrote in echo. please give me a solution for this.

aranjith0 · September 5, 2022, 11:16am

Hi,

How to schedule Percona backup for mongoDB via crontab, which runs “pbm backup” automatically and notify through email when its successful/failed.
Please provide any reference scripts.

Kunal_Bajpai · September 5, 2022, 11:44am

Is percona also perfom a backup of master-slave replication?
if it is possible then please share me script of master slave also.

Topic		Replies	Views
percona xrabackup not restore increment backup Percona XtraBackup	0	498	April 24, 2017
[code] XtraBackup Automation in python Percona XtraBackup mysql , percona	2	1499	December 6, 2022
innobackupex backup Percona XtraBackup	0	437	March 7, 2016
Proper Backup Techniques? Percona XtraBackup	3	546	September 4, 2015
broken backups Percona XtraBackup	3	562	May 31, 2016

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

Related topics