Backup MySQL and Files on Amazon EC2 to S3

I was looking for a way to do daily and weekly backups from my EC2 Instances to Amazon’s fantastic S3 service. Finally worked out a way of getting it to all work, works pretty well if I say so myself

To do this you will need to have set up a S3 account and got your key and secret handy.

Step 1 – Download required Files

I am using the S3 PHP class file from here: http://undesigned.org.za/2007/10/22/amazon-s3-php-class

Step 2 – The Backup Instructions

I made two files, backup-db.php and backup-files.php

backup-files.php

// Place to store backup locally
$backup_path = '/mnt/sites/backups/web';
// Email address to send report (optional)
$email_address = "user@mydomain.com";
// Amazon S3 info (optional)
$s3bash_path = '.';
$s3_key = '1AB0ABCDE1ABCDEFG0A2';
$s3_secret = 'HJhhiy4895y3485u3853445+Fsdfuiu';
$s3_bucket = 'aws1.mydomain.com';
// Delete yesterday's backup?
$delete_lastweek = true;
// Delete local backup? (only if using s3)
$delete_local = true;

/* RUN THE BACKUP */

// Required file
require_once('/mnt/sites/cron/S3.php');

// Time variables
$startime = time();
$yesterday = date('Ymd', strtotime('-7 day'));
$today = date('Ymd');

// Create today's folder
echo "Creating today's folder...n";
if (!file_exists("$backup_path/$today")) {
	mkdir("$backup_path/$today");
}

// Making today's backup
echo "Creating hot copy...n";

// Tar and gzip today's backup
echo "Creating tarball...n";
$gzipfile = "$backup_path/$today/backup.tgz";
system("tar -czvf $gzipfile /mnt/sites/webs/website1/* /mnt/sites/webs/website2/*");
// Get size of backup
$a = array("B", "KB", "MB", "GB", "TB", "PB");
$pos = 0;
$size = filesize($gzipfile);
while ($size >= 1024) {
  	$size /= 1024;
    $pos++;
}
$fsize = round($size,2)." ".$a[$pos];

// Remove yesterday's backup
if ($delete_lastweek) {
	if (file_exists("$backup_path/$yesterday")) {
		echo "Deleting local backup from yesterday...n";
		system("rm -Rf $backup_path/$yesterday/*");
		system("rmdir $backup_path/$yesterday");
	}
}

// Send to Amazon
if ($s3_key && $s3_secret) {
	// Upload today's file
	echo "Uploading to Amazon...n";
	// Instantiate s3 class
	$s3 = new S3($s3_key, $s3_secret);
	// Upload file
	if ($s3->putObjectFile($gzipfile, $s3_bucket, "backup.$today.tgz", S3::ACL_PRIVATE)) {
		// Delete yesterday's file
		if ($delete_lastweek) {
			echo "Delete last weeks backup on Amazon...n";
			$s3->deleteObject($s3_bucket, "backup.$yesterday.tgz");
		}
		// Delete today's file
		if ($delete_local) {
			if (file_exists("$backup_path/$today")) {
				echo "Deleting local backup from today...n";
				system("rm -Rf $backup_path/$today/*");
				system("rmdir $backup_path/$today");
			}
		}
	}
}

// Email report
if ($email_address) {
	echo "Emailing report...n";
	$completed = (time() - $startime);
	$completed = floor($completed/60) . ' minutes and ' . ($completed%60) . ' seconds';
	$message = "Tonight's backup completed in $completed. Gzipped file size: $fsize";
	mail($email_address, "File Backup - " . date('m/d/Y'), $message);
}

// Done
echo "Done";

Looking through this code the bits you will have to change are:

Line 2: $backup_path = ‘/mnt/sites/backups/web’;
Change this to a folder on your site to put the temp backup files to

Line 4: $email_address = “user@mydomain.com”;
Change this to your email address to get email confirmation when the backup completes

Line 7 & 8: $s3_key = ’1AB0ABCDE1ABCDEFG0A2′; $s3_secret = ‘HJhhiy4895y3485u3853445+Fsdfuiu’;
Change these to your Amazon S3 security details (no they aren’t my real keys)

Line 9: $s3_bucket = ‘aws1.mydomain.com’;
Change this to the name of the bucket you have set up to receive your backups

Line 11: $delete_lastweek = true;
If you want to keep just the latest backup on S3 (saving you space and money) then make this true. If you want to be safer and keep all backups make this false

Line 13: $delete_local = true;
Set this to true to delete the temp backup from your EC2 machine.

Line 18: require_once(‘/mnt/sites/cron/S3.php’);
Set this to the location of the S3 file you downloaded earlier

Line 37: system(“tar -czvf $gzipfile /mnt/sites/webs/website1/* /mnt/sites/webs/website2/*”);
This is where you put in the folders you want to backup. The * means backup everything inside the folder.

backup-db.php

This is very close to the previous file with some slight differences:

// Database connection
$mysql_user = 'dbuser';
$mysql_pass = 'dbpassword';
$mysql_db = array('wordpress','site1');	// Array of all databases you want to backup
// Place to store backup locally
$backup_path = '/mnt/sites/backups/database';
// Email address to send report (optional)
$email_address = "user@mydomain.com";
// Amazon S3 info (optional)
$s3bash_path = '.';
$s3_key = '1AB0ABCDE1ABCDEFG0A2';
$s3_secret = 'HJhhiy4895y3485u3853445+Fsdfuiu';
$s3_bucket = 'aws1.mydomain.com';
// Delete yesterday's backup?
$delete_yesterday = false;
// Delete local backup? (only if using s3)
$delete_local = true;

/* RUN THE BACKUP */

// Required file
require_once('/mnt/sites/cron/S3.php');

// Time variables
$startime = time();
$yesterday = date('Ymd', strtotime('-1 day'));
$today = date('Ymd');

// Create today's folder
echo "Creating today's folder...n";
if (!file_exists("$backup_path/$today")) {
	mkdir("$backup_path/$today");
}

// Making today's backup
echo "Creating hot copy...n";

// Loop through each database and run mysqlhotcopy
foreach ($mysql_db as $dbname) {
	system("mysqlhotcopy --user=$mysql_user --password=$mysql_pass $dbname $backup_path/$today/");
}

// Tar and gzip today's backup
echo "Creating tarball...n";
$gzipfile = "$backup_path/$today/backup.tgz";
system("tar -czvf $gzipfile $backup_path/$today/*");
// Get size of backup
$a = array("B", "KB", "MB", "GB", "TB", "PB");
$pos = 0;
$size = filesize($gzipfile);
while ($size >= 1024) {
  	$size /= 1024;
    $pos++;
}
$fsize = round($size,2)." ".$a[$pos];

// Remove yesterday's backup
if ($delete_yesterday) {
	if (file_exists("$backup_path/$yesterday")) {
		echo "Deleting local backup from yesterday...n";
		system("rm -Rf $backup_path/$yesterday/*");
		system("rmdir $backup_path/$yesterday");
	}
}

// Send to Amazon
if ($s3_key && $s3_secret) {
	// Upload today's file
	echo "Uploading to Amazon...n";
	// Instantiate s3 class
	$s3 = new S3($s3_key, $s3_secret);
	// Upload file
	if ($s3->putObjectFile($gzipfile, $s3_bucket, "mysql.$today.tgz", S3::ACL_PRIVATE)) {
		// Delete yesterday's file
		if ($delete_yesterday) {
			echo "Delete yesterday's backup on Amazon...n";
			$s3->deleteObject($s3_bucket, "mysql.$yesterday.tgz");
		}
		// Delete today's file
		if ($delete_local) {
			if (file_exists("$backup_path/$today")) {
				echo "Deleting local backup from today...n";
				system("rm -Rf $backup_path/$today/*");
				system("rmdir $backup_path/$today");
			}
		}
	}
}

// Email report
if ($email_address) {
	echo "Emailing report...n";
	$completed = (time() - $startime);
	$completed = floor($completed/60) . ' minutes and ' . ($completed%60) . ' seconds';
	$message = "Tonight's db backup completed in $completed. Gzipped file size: $fsize";
	mail($email_address, "MySQL Backup - " . date('m/d/Y'), $message);
}

// Done
echo "Done";

The changes are:

Line 2: $mysql_user = ‘dbuser’;
Set this to the mySql user to use to backup

Line 3: $mysql_pass = ‘dbpassword’;
Set this to the password for the above user

Line 5: $mysql_db = array(‘wordpress’,'site1′);
A comma seperated list of all the databases to backup – make sure the above user has access to these

Step 3 – Upload

Once all this is done upload the files to your EC2 Instance

Step 4 – Test the files

Open your SSH client (we use Putty) and log in as root. Then type (replacing uploaded-location with where you put the files:

/usr/bin.php /uploaded-location/backup-db.php
/usr/bin.php /uploaded-location/backup-files.php

You should see a message on the screen saying it has backed up the files and an email sent confirming. You can now check your S3 bucket to make sure the backups are there.

Step 5 – Set up Cron Job

Now we need to get these to run every day/week. So open up your SSH client (we use Putty) and log in as root

When you get the bash prompt type:

crontab -e

If you have no cronjobs set up this will come up blank, if you do they will be shown at the top.

Press i to allow editing and place your cursor on a new line using your arrow keys

Then type the following:

5 2 * * * /usr/bin.php /uploaded-location/backup-db.php
5 3 * * 7 /usr/bin/php /uploaded-location/backup-files.php

Replace /uploaded-location/ with where you put the files when uploading.

What this basically does is tell the server to run “backup-db.php” every day and “backup-files.php” every Sunday (the final 7 signifies Sunday and * means everyday, if you wanted to run on a Monday you would change this to a 1)

To save do the following:

ESC
:x

This will save the cronjobs and run them every day.