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.

 
#!/bin/bash
 
#-- configuration
TMP_STORE=/tmp/csvstore
BACKUP_DIR
=/home/backup
 
#--
 
if test $# -eq 0
 
then
   
echo "usage $0 <dbname> [dbcredentials]"
   
exit 0
 
else
   
# pass database as 1st param
   
db=$1
   MYSQL_CMD
="/usr/bin/mysql -D $@"
   
MYSQL_DUMP="/usr/bin/mysqldump $@"
 
fi
 

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.


BKP_PATH
=$BACKUP_DIR/$db
 
if [ ! -d $BKP_PATH ]
  
then
    mkdir 
-p $BKP_PATH
fi
 
if [ ! -d $TMP_STORE ]
 
then
   mkdir 
-p $TMP_STORE
 
else
   
rm -rf $TMP_STORE/*
fi
 
#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.

 
     
dumpcsv
(){
 
$MYSQL_CMD -D $db -"show tables" grep -"Tables_in" $TMP_STORE/tables.lst
 cat $TMP_STORE
/tables.lst | while read tablename
    
do
     echo 
"SELECT * INTO OUTFILE '$TMP_STORE/${tablename}.csv' FIELDS TERMINATED BY '\t' FROM $tablename;"
    
done > /dev/shm/sqlcmd.sql
 $MYSQL_CMD 
-D $db < /dev/shm/sqlcmd.sql
}
 
dumpstruct(){
 
$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.

 
CWD
=`/bin/pwd`
 

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"
       
fi
       
#rename the last week to before last week
       
mv -"$BKP_PATH/Week_2.tar.bz2" "$BKP_PATH/Week_1.tar.bz2"
    
fi
    
#create a tar.bz2 file of the immediate past week.
    
cd $BKP_PATH
    
/bin/tar -cjf "Week_2.tar.bz2" Week_3/*
    
cd $CWD
    rm 
-rf $BKP_PATH/Week_3
  fi
fi
 

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..
dumpcsv
dumpstruct
 

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
   mkdir 
-"$BKP_PATH/Week_3/Full"
   
mv $TMP_STORE/* $BKP_PATH/Week_3/Full/
else
  
#-- we are now in the week day, subsequent runs need only the diff.
  
CWD=`/bin/pwd`
  
WeekDay=`date +%a`
  if [ ! -
d $BKP_PATH/Week_3/$WeekDay ]
   
then
      mkdir $BKP_PATH
/Week_3/$WeekDay
      cd $TMP_STORE
 
      
for i in *
        do
         
diff $BKP_PATH/Week_3/Full/$i $i > /dev/shm/diff.txt
         
if test -/dev/shm/diff.txt
           then
           mv 
/dev/shm/diff.txt $BKP_PATH/Week_3/$WeekDay/${i}.diff
     fi
        done
   fi
 
  cd $CWD
fi
 

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 (872)

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.

thirty nine − thirty six =