Sunday, October 10, 2010

Automated Mysql Backup

I've created a routine backup shell script that dumps the database on an sql file. Hope this would be helpful. ;)


script:

#!/bin/bash

# Database Backup
# exports a compressed dump sql file
# author: Ryan Icasiano
# date: 07-19-2010
# Disclaimer: By downloading this script, you agree that the author is not liable for any unintended damage to your database/application
# you may run this manually or via scheduled task in cron

# initalize
datetoday=`date +"%Y%m%d"`
daytoday=`date +"%d"`
# adjust if you wish...
daysago=`date +"%Y%m%d" --date="20 days ago"`
savedir="/path/to/backup/directory/"
dbhost="hostname"
dbuser="username"
dbpassword="password"
dbname="mydatabase"
olddatabase=$savedir"mydb-"$daysago".sql.tgz"
newdatabase=$savedir"mydb-"$datetoday".sql.tgz"
newmonthlydatabase=$savedir"monthly/mydb-"$datetoday".sql.tgz"
logfile=$savedir"log/"$datetoday".log"

# get current date
time_log()
{
echo "["`date +"%Y-%m-%d %H:%M:%S"`"] "
}
#directory check
if [ ! -d $savedir ] ; then
mkdir $savedir
fi
if [ ! -d $savedir"monthly" ] ; then
mkdir $savedir"monthly"
fi
if [ ! -d $savedir"log" ] ; then
mkdir $savedir"log"
fi

#create dump for today
if [ ! -f $newdatabase ] ; then
echo `time_log`"Dumping new data for today." >> $logfile
mysqldump -u$dbuser -p$dbpassword -h $dbhost $dbname | gzip > $newdatabase
#monthly archiving
if [ $daytoday = "01" ] ; then
echo `time_log`"Creating backup archive for this month." >> $logfile
mysqldump -u$dbuser -p$dbpassword -h $dbhost $dbname | gzip > $newmonthlydatabase
fi
fi
#delete old dumps
if [ -f $olddatabase ] ; then
echo `time_log`"Deleting old dump file("$daysago")." >> $logfile
rm -f $olddatabase
fi
echo `time_log`"Routine backup has finished." >> $logfile

2 comments:

  1. hai,
    how to take incremental backup by using script ,please help me.
    thank u

    ReplyDelete
  2. Hello,

    Nice solution for automated backup, but you forgot to check return code of mysqldump command sometimes when it fails (mostly when there are BLOB in your schema) you will get empty gziped file, which is very bad - recovery from that file will be impossible, I've developed similar solution for quick backup of MySQL but with checking returncode of mysqldump, you may wish to check how it is done at my blog.

    ReplyDelete