Home > General Articles, mysql > Using Bash to take incremental backups on MySQL database

Using Bash to take incremental backups on MySQL database

At Saturn in the development labs, we restructured the development mysql by logically splitting out or combining projects and spread it across four instances of mysql running on a dedicated server. Later we felt that the weekly backups of mysql data folder was not sufficient for some of our projects. And for these we decided on having a rotational backup. The backup plan was to have a full backup of data and structure every Sunday and then difference of the database from last sunday to every other day, rotate the backups every fourth week. The structure backup was decided to be taken with mysqldump and the options –routines –triggers –no-data –compact. Whereas the data backup was to be taken as tab-seperated-values, using select into outfile. We had specific reasons to decide these methods as well as to take data and structure seperately.

We decided on bash as the scripting, since the application will need to be invoked from cron. Like any other bash script we start with the interpreter signature, and have a couple of configuration directives. Also we make sure the paramters are passed properly. The script needs a minimum the database name to be passed. All connection paramters to mysql or mysqldump can be used as parameter to the script, but the first parameter should be database name.

#-- configuration
if test $# -eq 0
echo "usage $0 <dbname> [dbcredentials]"
exit 0
# pass database as 1st param
="/usr/bin/mysql -D $@"
MYSQL_DUMP="/usr/bin/mysqldump $@"

Then we continue to set the backup directory with the name of the db. Set the scratch folder empty, and make sure that mysql will be able to write into the scratch folder.

if [ ! -d $BKP_PATH ]
if [ ! -d $TMP_STORE ]
rm -rf $TMP_STORE/*
#make sure mysql can write the the folder
chmod 1777 $TMP_STORE

The functionality of dumping db, as well as exporting the table data as tsv were packed in as two shell functions for ease of use.

$MYSQL_CMD -D $db -"show tables" grep -"Tables_in" $TMP_STORE/tables.lst
/tables.lst | while read tablename
"SELECT * INTO OUTFILE '$TMP_STORE/${tablename}.csv' FIELDS TERMINATED BY '\t' FROM $tablename;"
done > /dev/shm/sqlcmd.sql
-D $db < /dev/shm/sqlcmd.sql
$MYSQL_DUMP --routines --triggers --no-data --compact $TMP_STORE/struct.sql

Just cache (remember) the working directory, such that we can later switch to the same.


My logic of keeping two past weeks backups as tar.bz2, as well as the current one as a folder.

#check if we are running on a sunday
if test `date +%w` -eq 0then
#do we have a previous week folder 
if [ -"$BKP_PATH/Week_3" ] ; then
#do we have a week before in tar.bz2 format
if [ -"$BKP_PATH/Week_2.tar.bz2" ] ; then
#do we have a week before last in tar.bz2 format
if [ -"$BKP_PATH/Week_1.tar.bz2" ] ; then
#we dont need the very old backup
rm -rf "$BKP_PATH/Week_1.tar.bz2"
#rename the last week to before last week
mv -"$BKP_PATH/Week_2.tar.bz2" "$BKP_PATH/Week_1.tar.bz2"
#create a tar.bz2 file of the immediate past week.
/bin/tar -cjf "Week_2.tar.bz2" Week_3/*
cd $CWD
-rf $BKP_PATH/Week_3

Here is where we call both our functions, regrett that I have to run this every other day, before I can take a diff against the sunday’s backup. But still we had to meet the requirements.

# take dump if we are running..

Now we test if we have the current working folder, if that is not existing, we just create that and move the full dump to there. This way if the backup is running for the first time, or if the backup is running on Sunday the current week folder will get created. And in case the folder is existing, then the weekday backup with individual table diff is taken, and any diff that is of zero bytes are discarded.

# if we dont have the current directory.. we are either running first time or
# running date is sunday, so take a full backup
if [ ! -"$BKP_PATH/Week_3" ] ; then
mv $TMP_STORE/* $BKP_PATH/Week_3/Full/
#-- we are now in the week day, subsequent runs need only the diff.
WeekDay=`date +%a`
  if [ ! -
d $BKP_PATH/Week_3/$WeekDay ]
      mkdir $BKP_PATH
      cd $TMP_STORE
for i in *
diff $BKP_PATH/Week_3/Full/$i $i > /dev/shm/diff.txt
if test -/dev/shm/diff.txt
/dev/shm/diff.txt $BKP_PATH/Week_3/$WeekDay/${i}.diff
  cd $CWD

Now that we have come to the end of the road, lets download the script for use. Shell Script to Backup MySQL DB with 3 week rotation (1131)

MySQL Backup, shell backup tool MySQL

Categories: General Articles, mysql Tags:
  1. Rabinson Dekosok
    March 6th, 2013 at 09:20 | #1

    Nice post and good option for incremental backup. For other option I have refered http://www.techflirt.com/mysql-incremental-backup-restore/ . This tutorial has explained some good point.

  1. No trackbacks yet.

eighty seven + = ninety seven