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 -e "show tables" | grep -v "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 0; then
#do we have a previous week folder
if [ -d "$BKP_PATH/Week_3" ] ; then
#do we have a week before in tar.bz2 format
if [ -f "$BKP_PATH/Week_2.tar.bz2" ] ; then
#do we have a week before last in tar.bz2 format
if [ -f "$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 -f "$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 [ ! -d "$BKP_PATH/Week_3" ] ; then
mkdir -p "$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 -s /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. Download
MySQL Backup, shell backup tool MySQL