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 http://s3tools.org/s3tools 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.

backup.sh

#!/bin/bash
NOWDATE=`date +%Y-%m-%d`
BACKUPNAME=”$NOWDATE.sql.gz”
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…”
rm $BACKUPNAME
echo “Done”

 

restore.sh

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

 

About jeevan

Geek under construction, Tech Speaker, Tech Blogger @Geekospace.com, 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>