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

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”

 Copy from

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s