Back-up and restore the database between two AWS EC2 instances

By on December 21, 2011

Hi all,

I must say there are couple of blogs and websites which have already done this, I mean to say the they must have explained the process of creating mysql dumps and restoring it back whenever there is a failure.

I had some other scenario where we manage multiple EC2 instances. Say the best practice is to manage a separate instance for production and another instance for testing or development or for internal usage for a organization.

The production server consists of a web server, app server and also the database server, may be oracle,mysql or what ever.

In my scenario I am running a mysql server 5.1 along with apache2 web server and a ruby on rails app.

Let me explain what I was supposed to do and how I did it.

I was asking to create a mysql dump and upload it to S3 or any thing storage services, preferably on S3 .Then download the mysql dump fine from S3 and restore it in a development server.

Before we get started please do download the s3cmdtools from to your EC2 instance and also just quick googling about cron jobs and basic knowledge will help you a lot.


Step 1: Create the mysql dump from the database.

Step 2: Compress the file

Step 3: upload it S3

Step 4:download the mysql dump file in your test server.

step 5:extract it

step 6: restore it

Step 7:maintain only thelast 7 days back-up.

Here are bash scripts which does it for you.

NOWDATE=`date +%Y-%m-%d`
echo “Creating backup of database finances to $BACKUPNAME”
mysqldump –user=user –password=password database_name | gzip -9 > $BACKUPNAME
echo “Succesfully created database backup”
echo “Uploading backup to Amazon S3 bucket…”
/usr/local/bin/s3cmd put $BACKUPNAME s3://path /to /file/$BACKUPNAME
echo “Successfully uploaded backup to S3″
echo “Deleting backup file…”
echo “Done”

NOWDATE=`date +%Y-%m-%d`
echo “downloading the file $BACKUPNAME”
/usr/local/bin/s3cmd get s3://path_to/file/$BACKUPNAME
echo “Succesfully downloaded”
echo “unzipping the file”
echo “unzipped”
echo “Will restore it Please wait “
mysql -u user=user password=password database_name < $UNZIPNAME
echo “restoring the file $BACKUPNAME”
echo “Deleting backup file…”
echo “Done”


About jeevan

Geek under construction, Tech Speaker, Tech Blogger, foodie, Cloud Evangelist, Moviez freak passionate learner, startup lover, business and tech savya, social media addict, biker, musician, extrovert, realistic, seminal, approachable.

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>