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