Should I, and how do I, backup my database for a web application that is hosted on an Amazon EC2 server?

Question

I set up Amazon EC2 instance using the Ubuntu server edition, and I installed the LAMP stack on it. I did up a PHP web application running on MySQL. I have tested the web application on Amazon EC2, and it works.

I have not officially launched, but I need to know this before launching. Should I backup my database data? If so, how should I do it as cost effective as possible?

Previously for another web application, I wrote a Perl or Bash script (cannot remember) that will be executed by cron on a daily basis.

The script will then backup the database into a single .sql file and send as email attachment to my Gmail account.

That web application was on shared hosting hence, I was quite sure I needed to do backup of my database. My files are on Git repository, so I am not worried about that.

For this new web application on Amazon Web Services (AWS), I am undecided because:

1. I do not think that it is a good solution as data sent over email is not safe. There is no SSL as far as I can recall though it was a cheap solution. Free. Easily retrievable by date.

2. Amazon may have made it redundant for me to do backup because they do that already. All I need to know is how to recover it in case of disaster (touch wood)

   • (I suspect) there is a superior and cost effective way for me to do backup using Amazon S3.

3. I allow users to upload files, so I need to somehow backup those files as well. Which I do not know how to and have never done it before in any form.

What I want: a daily back up of my database and image files as cost effective as possible and a clear, step-by-step playguide to implement this and to recover them in case of disaster.

Background:

• I am totally unfamiliar with AWS. Only know as much as setting up an account. That is all.

• << One year experience as someone newbie to Ubuntu. Most of my life in Windows.

• Mostly intimate with PHP programming. Command of other programming languages is not as good due to lack of usage.

Answer 1

Amazon keep your database files on redundant storage, but provide only limited information on how it's configured, so you'll have to form your own view on whether or not this is adequate for your needs. However, they don't keep old versions, so this will only protect you against hardware failure and not against user error of some kind (which is more likely than hardware failure).

Also, be warned that if your EC2 server is on an instance store, the data will be wiped if the server is ever stopped. For persistent storage, your data must be on an Elastic Block Storage (EBS) volume. Once it's on an EBS volume, you can take periodic snapshots (manually or automated using the Amazon API) which will then allow you to roll back to older versions. The AWS SDK for PHP is pretty good, and you can find it here: http://aws.amazon.com/sdkforphp/.

Answer 2

Amazon has very many services, sometimes it can get confusing with all the different technologies. Keep in mind that amazon is creating all these services to solve specific problems.

Backing up MySQL in S3 is very common and it's well documented in many blogs. I recommend following the guide here http://agiliq.com/blog/2009/02/automatically-backup-mysql-database-to-amazon-s3-u/

Never assume anything, S3 is designed to a fault tolerant storage device, but that wouldn't stop me from backing up everything locally on my computer or with some other provider. EBS volumes are not reliable enough to be the sole storage device for your backup much less so if your database is also stored on the same disk.

Whatever method you choose I would follow the follow steps when making you backup

1. Create backup daily
2. Send backup to S3 (make sure to use the MD5 checksum so you know your backup is good)
3. Download backup from S3 locally or on another provider not S3
4. Cleanup old backups using a rolling backup type history
   • Store a backup of the last 7 days
   • Store a backup of the Friday each week for 1 month
   • Store a backup of the last month for 1 year