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 backup.sh script
Create an executable script with the following contents:
#!/bin/sh 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 rm -rf $BACKUP_FILE mv temp.sql.gz $BACKUP_FILE s3cmd put $BACKUP_FILE s3://$S3_BUNDLE/$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/backup_db.sh
Congratulations, your database will be backed up to S3 every night for you.