Backup MySQL to S3 in 30 seconds

One of the most tedious parts of setting up a database for your new app is backing it up. So much so that you will almost always leave it for last if you remember to do it at all. However, with the risk of data corruption, server corruption or simple server failure looming it’s an important part of service continuity.

The good news is that there is a 30 second way to set up regular backups of your MySQL database into Amazon S3. This assumes you already have a MySQL database set up and an S3 account with a bundle created for your backups.

Step 1. Install s3cmd

s3cmd is a set of easy tools for working with S3 from the shell. If you are using Ubuntu it’s as easy as:

$sudo apt-get install s3cmd

Step 2. Create a script 

Create an executable script with the following contents:

export BACKUP_FILE=my_db.backup.sql.gz
export DATABASE_SCHEMA_NAME=my_schema
export S3_BUNDLE=myBundle
mysqldump -uroot $DATABASE_SCHEMA_NAME > temp.sql
gzip temp.sql
mv temp.sql.gz $BACKUP_FILE

This uses mysqldump to dump the database contents to a temporary SQL file, gzip compresses it and uploads it to S3 using the s3cmd put. Note that you should change DATABASE_SCHEMA_NAME, S3_BUNDLE and BACKUP_FILE to the values specific to your application.

Step 3. Set up Cron

Finally, just use <code>crontab -e</code> to create a cron entry to run this script every night. An example that runs it everynight at midnight:

# m h  dom mon dow   command
0  0  *  *  *  /home/ubuntu/backup/

You’re Done!

Congratulations, your database will be backed up to S3 every night for you.