Backing Up MySQL Databases using Amazon S3

The below script can be used via command line with a bit of configuration. All you need to do is set the MyUSER, MyPASS, MyHOST and S3Bucket variables at the top of the script and you can start backing up your databases.

#!/bin/bash
# Shell script to backup MySql database

# CONFIG – Only edit the below lines to setup the script
# ===============================

MyUSER=”root” # USERNAME
MyPASS=”password” # PASSWORD
MyHOST=”localhost” # Hostname

S3Bucket=”mysql-backup” # S3 Bucket

# DO NOT BACKUP these databases
IGNORE=”test”

# DO NOT EDIT BELOW THIS LINE UNLESS YOU KNOW WHAT YOU ARE DOING
# ===============================

# Linux bin paths, change this if it can not be autodetected via which command
MYSQL=”$(which mysql)”
MYSQLDUMP=”$(which mysqldump)”
CHOWN=”$(which chown)”
CHMOD=”$(which chmod)”
GZIP=”$(which gzip)”

# Backup Dest directory, change this if you have someother location
DEST=”/backup”

# Main directory where backup will be stored
MBD=”$DEST/mysql-$(date +”%d-%m-%Y”)”

# Get hostname
HOST=”$(hostname)”

# Get data in dd-mm-yyyy format
NOW=”$(date +”%d-%m-%Y”)”

# File to store current backup file
FILE=””

# Store list of databases
DBS=””

[ ! -d $MBD ] && mkdir -p $MBD || :

# Only root can access it!
$CHOWN 0.0 -R $DEST
$CHMOD 0600 $DEST

# Get all database list first

if [ “$MyPASS” == “” ];
then
DBS=”$($MYSQL -u $MyUSER -h $MyHOST -Bse ‘show databases’)”
else
DBS=”$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse ‘show databases’)”
fi

for db in $DBS
do
skipdb=-1
if [ “$IGNORE” != “” ];
then
for i in $IGNORE
do
[ “$db” == “$i” ] && skipdb=1 || :
done
fi

if [ “$skipdb” == “-1″ ] ; then
FILE=”$MBD/$db.$HOST.$NOW.gz”
# dump database to file and gzip
if [ “$MyPASS” == “” ]; then
$MYSQLDUMP -u $MyUSER -h $MyHOST $db | $GZIP -9 > $FILE
else
$MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE
fi
fi
done

# copy mysql backup directory to S3
s3cmd sync -rv –skip-existing $MBD s3://$S3Bucket/

You can use the above script in a cron too, so your server is backed up regularly. The below cronjob will run the MySQL database backup script everyday at 2am:
# Run everday at 2am
0 2 * * * /path/to/sql_backup.sh
view rawmysql_backup_cron.shThis Gist brought to you by GitHub.
Once the script has completed, you’ll see a folder on your S3 Bucket with the date of the backup. If you run the script multiple times in a day, the latest version of your database will be synched to the same folder.

Amazon S3 Database Backup

Advertisements

Share your thoughts with us!!!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s