rama
Comments
Reactions

Automatically backup mysql database to Amazon S3 using django (Python) script

By : rama

Many of us use Amazon S3 to backup  their mysql databases.As we do it so often we thought of writing an automated script which does this task of taking the backup of a mysql database and then moving it to the Amazon S3 .  Below is the django script .

download

   1: def upload_db_to_Amazons3():
   2: """ function which uploadd mysql database to amazon s3"""
   3:  
   4: AWS_ACCESS_KEY_ID = settings.AWS_ACCESS_KEY_ID
   5: AWS_SECRET_ACCESS_KEY = settings.AWS_SECRET_ACCESS_KEY
   6: BUCKET_NAME = settings.BUCKET_NAME
   7: MYSQL_DUMP_PATH = settings.MYSQL_DUMP_PATH
   8: DATABASE_NAME = settings.DATABASE_NAME
   9: ARCHIVE_NAME = settings.ARCHIVE_NAME
  10: .....
  11:  
  12: proc1 = subprocess.Popen(settings.MYSQL_DUMP_PATH+" --no-data  -u root -pwelcome   -x  --databases  doloto",shell=True,stdout=subprocess.PIPE,stderr=subprocess.STDOUT)
  13: #create data file
  14: proc3 = subprocess.Popen(settings.MYSQL_DUMP_PATH+" --no-create-info  -u root -pwelcome   -x  --databases  doloto",shell=True,stdout=subprocess.PIPE,stderr=subprocess.STDOUT)
  15: ...
  16:  
  17: t1.write(proc1.communicate()[0])
  18: t2.write(proc3.communicate()[0])
  19:  
  20: #create  tar.gz for the above two files
  21: tar = tarfile.open( (os.path.join(os.curdir, settings.ARCHIVE_NAME+"_archive.tar.gz")), "w|gz")
  22: tar.add(t1.name,ARCHIVE_NAME+"_struct.sql")
  23: tar.add(t2.name,ARCHIVE_NAME+"_data.sql")
  24: ........
  25: #upload file to Amazon S3
  26: tardata = open(os.path.join(os.curdir, settings.ARCHIVE_NAME+"_archive.tar.gz") , "rb").read()
  27: response = conn.put(BUCKET_NAME,settings.ARCHIVE_NAME+"_archive.tar.gz",S3.S3Object(tardata))
  28:  
  29: upload_db_to_Amazons3()

Download the script by using the below link

1) http://uswaretech.com/blog/wp-content/uploads/2009/02/mysql_to_amazons3.zip

Things to do before using the script:

1) You need to put down Amazon S3 library for python in the sys.path .you can download the python S3 library by following the below link.(http://developer.amazonwebservices.com/connect/entry.jspa?externalID=134)

2)For a given project keep the script mysql_to_amazons3.py  at the  same level as that of settings.py.

3) Define the following variables in settings.py

   1: #MySQL information
   2: #MYSQL_DUMP_PATH should be the path to the mysqldump executable file
   3: #To know where  mysqldump executable is present in your local system
   4: #use the command "which mysqldump".
   5: #database name, user_name and password will be taken from settings.DATABASE_NAME,DATABASE_USER etc...
   6: MYSQL_DUMP_PATH = "/usr/bin/mysqldump"
   7:  
   8:  
   9: #Amazon S3 credentials
  10: #Bucket_Name The name of the bucket in Amazon S3
  11: AWS_ACCESS_KEY_ID = ''
  12: AWS_SECRET_ACCESS_KEY = ''
  13: BUCKET_NAME = 'tempstore'
  14:  
  15: #Archive name with out any extension (i.e):
  16: #what name do you want for the file which is uploaded to AMazon S3
  17: #please give the name  without any extension
  18: #Also note that a copy of this file will be stored in the directory where the script resides
  19: ARCHIVE_NAME = "" 

a.MYSQL_DUMP_PATH=  “/usr/bin/mysqldump”

    mysqldump is the command using which we take database backup.Here you should mention path to this mysqldump executable.Read the comments above that variable for more details.

b.AWS_ACCESS_KEY_ID = ''

Amazon access key which amazon will provide after signing up for their webservices.

c.AWS_SECRET_ACCESS_KEY = ''

Amazon secret key which amazon will provide after  signing up for their webservices.

d.BUCKET_NAME = 'tempstore'

Bucket name to which to which you want to store the database archive.More details about what a bucket means at http://tinyurl.com/5nlrkz

e.ARCHIVE_NAME = " "

Name of archive file.What name do you want to give to this archive?.

f.Please note that the script will take the database name, database user,database password from already existing settings variables DATABASE_NAME , DATABASE_USER  and DATABASE_PASSWORD

Running the script:

1) python mysql_to_amazons3.py to upload the database archive of settings.DATABASE_NAME to Amazon S3 into the respective bucket.

2) It also places the archive in the  directory where the script resides for your convenience.

3)The archive contains two files  ***_data.sql which contains database data, ***_struct.sql which contains database structure.We intentionally separate the data and structure as it is a good practice.

4) if you want to run this script as a cron job use crontab –e for setting up the cron job.

How to run this script in python?

if you want to run this script in python then do the following steps

1)Remove the top 4 lines of code in above script.

2) In upload_db_to_Amazons3()  function replace all the capital variables with their corresponding values.

3)Run the script.

How does the script work?

1.It uses subprocess.Popen (pipe open) functions to first execute the commands (mysqldump -  -  - ).

2.It  then reads the output from the pipes to create a .tar.gz archive.

3.It then uses  Amazon S3 web services API to upload the above file to Amazon S3.

Do you have any problems with the script?

if you have any problems with the script or any other feedback please let me know in comments.

Resources:

1)http://tinyurl.com/5nlrkz

2)http://developer.amazonwebservices.com/connect/entry.jspa?externalID=134

3)http://www.holovaty.com/code/update_s3.py

4)http://www.djangosnippets.org/snippets/580/

Related Posts


Can we help you build amazing apps? Contact us today.

Topics : Amazon mysql

Comments

RJ 23rd Feb., 2009

If your Django webservers are running on EC2, you can use EBS snapshots for mySQL backups

commmenttor
Rama Vadakattu

Thanks RJ for letting us know about that.

commmenttor
Vlad

Great script. But I had to fix it up a little bit. On my system (CentOS 5.2) the backups ended up missing a few kilobytes at the end of the files. Looks like that tar doesn't wail for mysqldump to finish. I resolved this by adding t1.flush() and t2.flush() between the lines 18 and 20

commmenttor
wordpress developer

We use something similar for our Wordpress backups.

commmenttor
winson 2nd June, 2010

From amazon s3 document,the object have a 5GB size limited,if we use this tool to backup to S3,Is that a problem ?

commmenttor
Alex Green

We would recommend our backup tool MySql Backup And Ftp (MySqlBF, http://mysqlbackupftp.com/ ). MySqlBF allows to schedule backups using a Windows Task Scheduler or a custom windows service for a complex backup scenarios.

It supports connecting securely over an SSH connection and has such a unique feature as creating a backup via phpMyAdmin. The tool allows to archive, encrypt and save backups to HDD, FTP, Network or most popular cloud storage services (Dropbox, Amazon S3, Google Drive, SkyDrive, Box) and sends email notifications on success or failure.

commmenttor
ugg mocassin

Automatically backup mysql database to Amazon S3 using django (Python) script - Agiliq Blog | Django web app development

commmenttor
bottes ugg pas cheres

Automatically backup mysql database to Amazon S3 using django (Python) script - Agiliq Blog | Django web app development

commmenttor

Reactions

Web Dev Hobo

Personnaly, I just have PhpMyAdmin do the backup for me.

This comment was originally posted on Noupe

Binny V A

This is what I use – Perl script to backup mysql databases

This comment was originally posted on Noupe

insic

Nice list list of tips. Number 6 and #7 is handy.

This comment was originally posted on Noupe

Mani

Thank you noupe!! Nice list of resources to be used in different applications.

This comment was originally posted on Noupe

Willem

This list is incomplete imo, i’d reather see examples of using diff for db backups. This doesnt scale at all.

This comment was originally posted on Noupe

Matteo

Dump a remote db locally in one easy line:

ssh user@server “/usr/bin/mysqldump -u user -p password database_name” | dd of=/where/you/want/the/dump.sql

This comment was originally posted on Noupe

PS

Not a single one of these options will scale past a small to moderately sized database.

This comment was originally posted on Noupe

Farid Hadi

Currently I just use #11 but I’m going to look into #1 and #8.
Thanks for reminding me that I need to figure out a nice way to do this.

This comment was originally posted on Noupe

Ronaldo

Currently I use zmanda mysql backup. I use the comunity version

This comment was originally posted on Noupe

Peter De Berdt

We use version control to backup our database, saving us from having to rotate the dumps. This means we can go back in time as much as we like.

We use Git right now, but any SCM would do actually.

http://pastie.org/417454

You do need to initialize a Git repository at /path/to/dbbackups first. Then add a cron tab that calls the backup script.

This comment was originally posted on Noupe

john woah

if you’re stuck using mySQL…. sorry

This comment was originally posted on Noupe

Shane

Wow, I was kind of expecting more than just mysqldump, mysqldump, and mysqldump. Has any tried replicating the Mysql DB as a backup solution?

This comment was originally posted on Noupe

ShavenLunatic

brilliant. Thanks for a useful list

@john woah, what do you have against MySQL?

This comment was originally posted on Noupe

Rob

In many circumstances you would want to force the MYSQL dump to continue past any error with ‘-f’ so that you get the full database even if it contains oddness. Otherwise there is a chance it will crash out and give you only a fraction of your data.

This comment was originally posted on Noupe

Brandon Darbro

My MySql databases are on a hosting provider, here’s the tricks I use.

1) Via ssh, connect to host, issue mysqldump or your dump script(s), have them dump to a non-quota’d location, like /tmp, or /dev/shm (if there’s enough ram on the remote box). My scripts use:
-a -Q -q -l –add-drop-table –add-locks –complete-insert -uusername -ppassword database_name > database_name.sql

2) Use rsync over ssh to pull down the dump(s) to the local machine. If you already have an older version of the dump(s), rsync will only transfer what it needs to update your copy. Use compression, too (-z) if you like. Remove these temporary dumps from the remote server.

3) Now that your local copy of the dump(s) are up to date, here’s the real magic. Why keep multiple copies of backups? Similar to rsync, what you want to do is only store the delta of these backups since the last copy. For this, use the simple old code revisioning utility, RCS. Check in your latest backup using the ‘ci’. Use the -m option to automatically add a comment to this revision’s check in, so that way it doesn’t prompt you for it. RCS will now diff the current backup against the previous version, and then record just the diff info, or the delta.

Works great. Just remember to retire older revisions to keep it from growing out of hand some day.

What’s the benefits of this?

Using RCS’s ‘co’ command, I can check out any revision still kept in the repository. RCS uses all the diffs to re-construct the backup that was checked in at that specific revision. So I can jump back 30 days if I need to find a table’s previously known good condition.

This comment was originally posted on Noupe

Timothy

this is some good information. Thanks

This comment was originally posted on Noupe

Sarah

Wow, I was kind of expecting more than just mysqldump, mysqldump, and mysqldump. Has any tried replicating the Mysql DB as a backup solution??

This comment was originally posted on Noupe

Kingsly

@Sarah

Replication is a HA solution, not a backup solution.

a “delete * from tablename” will wipe out your “backup” too.

This comment was originally posted on Noupe

Sklep Zoologiczny W?dkarski

Thanks! You’re saving my life again

This comment was originally posted on Noupe

Dainis Graveris

I am still rookie in working with databases, very useful.

This comment was originally posted on Noupe

fail

none of them are correct. fail.

This comment was originally posted on Noupe

kissfang

actually i scacely use Mysql except in CMS,but CMS sets mySQL already ,

This comment was originally posted on Noupe

Rahul

Nice one. Web developers and admin can use these to backup their online database.

This comment was originally posted on Noupe

strony internetowe

great stuff

This comment was originally posted on Noupe

Post a comment Name :

Email :

Your site url:

Comment :

© Agiliq, 2009-2012