Cloud Tips: Backing Up MySQL on Amazon EC2 to S3

Now that I’m all set up in the Amazon cloud I’m starting to think about backups. Elastic Block Storage (EBS) on Amazon is great and the Snapshots (backups) can be generated with a few clicks from the Management Console, but, for a few reasons I’d like to set up my own backup scripts and here’s why:

• Amazon EBS snapshots are cool, but there might be a situation where I’d like to restore only one file, or a few rows from a MySQL dump or whatever. EBS works in bundled mode, this means that you store an image of the hard-drive you’re working it, no matter how many files there are. It might be painful setting up an extra hard-drive just for backups and work with its snapshots
• I don’t believe there’s a feature to schedule or automate EBS snapshots
• I’d like a simple way to download backed up date onto my local PC
• Some of my clients like to get their weekly backups by FTP

I don’t really care about my php files, images and other stuff that’s located on my EBS, cause I’m sure I have local copies of all that. The most important part in all my projects is the data stored in my MySQL database, thus I’m going to show you how to setup a simple shell script to generate daily MySQL backups and a simple php script to upload them to a secure S3 bucket.

Take a look at this simple shell script:

filename=mysql.`date +%d.%m.%Y`.sql.gz
echo Generating MySQL Dump: ${filename}
mysqldump -uyour_username -pyour_password --all-databases | gzip -c9 > /ebs/backups/${filename}
echo Uploading ${filename} to S3 bucket
php /ebs/data/s3-php/upload.php ${filename}
echo Removing local ${filename}
rm -f /ebs/backups/${filename}
echo Complete

I’m assuming you’re familiar with shell scripting, thus there’s no need to explain the first few lines. Don’t forget to type in your own username and password for MySQL access, also, I used the path /ebs/backups/ for my daily MySQL backups. You choose your own.

There are a few scripts located in /ebs/data/s3-php/ including upload.php, which takes a single parameter – filename (don’t put your path there, let’s keep things simple). The script simply reads the given file and uploads it to a preset path into your preset S3 bucket. I’m working with the S3-php5-curl class by Donovan Schonknecht. It uses the Amazon S3 REST API to upload files and it’s just one php file called S3.php, which in my case is located in /ebs/data/s3-php right next to my upload.php script.

Before going on to upload.php take a look at this file which I called S3auth.php:

$access_id = 'your_access_id';
$secret_key = 'your_secret_key';
$bucket_name = 'bucket';
$local_dir = '/ebs/backups/';
$remote_dir = 'backups/';

This is the settings file which I use in upload.php. These particular settings assume your backups will be located in /ebs/backups and will be backed up to an Amazon S3 bucket called ‘bucket’ and in the ‘backups’ directory within that bucket. Using single quotes is quite important, especially with the secret_key, as Amazon secret keys often include the backslash symbol. Here’s the upload.php script:

require("S3.php");
require("S3auth.php");

$s3 = new S3($access_id, $secret_key);
$s3->putBucket($bucket_name, S3::ACL_PRIVATE);
$s3->putObjectFile($local_dir.$argv[1], $bucket_name, $remote_dir.$argv[1], S3::ACL_PRIVATE);

All simple here according to the S3.php class documentation. Notice the $argv[1], that’s the first argument passed to the upload.php script, thus the filename of the backup file.

That’s about everything. Try a few test runs with the shell script (remember to chmod +x it otherwise you’ll not be able to execute it) and finally setup a cron running the script daily. Mine works like a charm!