Activities

July 2013
M T W T F S S
« Jun   Aug »
1234567
891011121314
15161718192021
22232425262728
293031  

MySQL : bash script for automated backup restoration at DR site

Here I’m sharing a bash script will restore one day only backup at DR server which will be placed 500KM away. This is very useful if you want to keep a offshore site backup.

The bash script have the following functionality.

1. Declaring local variables, FTP and MYSQL access details.
2. Pull the yesterday database backup from production network using FTP.
3. Script will maintain last 7 days backup files.
3. De-compress the bz2 file and restore to MySQL database.

In my scenario, daily backup file will be 30GB in compressed format. Also replication is not a good choice since the serves are located in diff. location. This script can be downloaded from here

#!/bin/sh
sync;echo 3 > /proc/sys/vm/drop_caches

##FTP backup location where the daily backup pushed"
USERNAME="bkp"
PASSWORD="password"
SERVER="server IP"

#### MySQL user access ##
SQL_USER="root"
SQL_PASS="pass"
SQL_HOST="localhost"

### Local Path and fodlers

# Log activity file
LOGFILE="/var/log/daily_table-pull.log
touch $LOGFILE
TMP_FOLDER="/home/tmp/"
RECENT_BACKUP="/home/backup/db_recent/"

#### Backup file pattern
HOST="prod_server"
db="livedb"

#Deleting recent backup...
/usr/bin/find $RECENT_BACKUP -mtime +7 -exec rm {} \;
echo "        ######### " $(date +%d-%m-%Y) " ####### "  >> $LOGFILE
rm -rf $TMP_FOLDER/*

# Get backupup file pattern, eg: dbname.production_host.date.bzz2.
#Bz2 is good for moving GB data backup to a files.Get date in dd-mm-yyyy format, This will shows date of yesterday's backup.

NOW=$(date -d "yesterday" +"%d-%m-%Y")

FILE=""
FILE="$db.$HOST.$NOW.bz2"
echo "db is pulling to DR ....... "  `date` >> $LOGFILE
echo $FILE

# login to remote server
ftp -d -v -n $SERVER <<EOF
user $USERNAME $PASSWORD
bin
lcd $TMP_FOLDER
get $FILE
quit
EOF
echo "Prodb db has been pulled to DR "  `date` >> $LOGFILE

# Taking last 7 days backup at DR site
cp $TMP_FOLDER/$FILE  $DB_RECENT
echo "Unzipping backup "  `date` >> $LOGFILE

bunzip2 $TMP_FOLDER/$FILE
echo "Completed the backup de-compression "  `date` >> $LOGFILE
cd $TMP_FOLDER/
mv $db.$HOST.$NOW  dbbackup.sql
echo "production database isrestoring .... "  `date` >> $LOGFILE
mysql  -u$SQL_USER -p$SQL_PASS  -e "use proddb;source /sqldata/tmp/tmp_folder/dbbackup.sql;"
# Pls note  Database name and dump file path is hard coded in mysql shell.

echo "Production database is restored sucessfully .... "  `date` >> $LOGFILE

cat $LOGFILE | tail -n 8 |mail -s "Completion :- Prod-DR: MySQL daily tables restore " liju@serveridol.com
echo " ####################################################### " >> $LOGFILE

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>