Backup MySQL on Amazon EC2 to Amazon S3

As I’m running hanneng.sg on the Amazon Elastic Compute Cloud. The WordPress is running quite stable. The next thing on my mind is about backup.

Amazon Cloud is offering 10GB of Elastic Block Storage (EBS) plus 1 million I/Os for free. So, I’m thinking of just utilizing the free capacity.

I don’t plan to back up WordPress script, theme, plugins and images inside the Amazon EC2, because I could re-install WordPress easily. I also have local copy of these images too, there are mostly store in Flick or ImageShark.

To me, the most important is the data stored in the MySQL database. In this post, I’m going to show you how to set up a simple shell script to generate a daily MySQL dump and a simple script to upload the backup to Amazon S3 Storage Service.

Now, uses mysqldump command to do the initial full backup

# Begin the backup process
rm -f $BACKUPDIR/sqldump.sql
mysqldump -u Username -pPassword -A >$BACKUPDIR/sqldump.sql
rm -f $BACKUPDIR/sqldump.tgz
tar -czvf $BACKUPDIR/sqldump.tgz $BACKUPDIR/sqldump.sql


I uses s3cmd command line tool to manages data in Amazon S3. Next, this code is to copy the backup file to Amazon S3.

NOW=$(date +_%b_%d_%y)
$S3CMD put $BACKUPDIR/sqldump.tgz s3://$S3BUCKET/sqldump$NOW.tgz

Amazon S3 only provides 10GB disk space. Therefore, I need to do housekeeping regularly, to ensure disk usage doesn’t exceeded 10GB.

S3cmd has a command to list all content in your Amazon bucket.

# list all file in Amazon S3
s3cmd ls s3://$S3BUCKET

# count no of files in Amazon S3
countline=`s3cmd ls s3://$S3BUCKET | wc -l`

I need only to keep 2 copies of the database backup files. This part is to keep latest 2 files and delete the oldest file.

if test $countline -gt 2
then
s3cmd ls s3://$S3BUCKET | head -1 | awk '{print $4}'> filetodelete.txt
delfile=`cat filetodelete.txt`
echo "File to delete $delfile"
s3cmd del $delfile
rm -f filetodelete.txt
fi

Learn more about Amazon Cloud
Host Your Web Site In The Cloud: Amazon Web Services Made Easy: Amazon EC2 Made Easy

Startup@Cloud: 33 tips for running your company using cloud-based software

Posted in Shell Script Tagged with: , , , , ,

Leave a Reply