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
full styles nike saints authentic roman harper green black white elite limited outle

Challenging Techniques To Learn nfl jersey And Also How One Can Connect with The nfl jersey Elite
full styles nike saints authentic roman harper green black white elite limited outle http://www.airmartechnology.com/catimages/kids-jerseys-basketball/liverpool-black-soccer-jerseys-manufacturers/ygzgqewm-p-410.html

commmenttor
offical cheap saints lance moore black blue white jersey nike womens mens feed the family for less

Obtain - This Includes Nearly everything when it comes to nfl jersey
offical cheap saints lance moore black blue white jersey nike womens mens feed the family for less http://www.charliemere.com/holly/cheap-jerseys-in-china/philadelphia-eagles-custom-jersey/zrggvbqx-p-619.html

commmenttor
ugg slippers mens

who makes ugg boots Automatically backup mysql database to Amazon S3 using django (Python) script - Agiliq Blog | Django web app development
ugg slippers mens http://sallyfortfarm.com/website/ugg-slippers-mens.html

commmenttor
women ugg boots cheap

ugg rosabella Automatically backup mysql database to Amazon S3 using django (Python) script - Agiliq Blog | Django web app development
women ugg boots cheap http://www.mccreddin.com/deals/women-ugg-boots-cheap.html

commmenttor
hot sale matt ryan black silver white jersey throwback falcons elite limited under discount

Just About The Most Detailed nfl jersey Guidebook You Ever Seen Or Your Cash Back
hot sale matt ryan black silver white jersey throwback falcons elite limited under discount http://www.akumaneko.com/abouts/basketball-jersey-blank/womens-mlb-jerseys-cheap/qqabyrdp-p-262.html

commmenttor
canada goose photos

womens canada goose jackets goose canada Automatically backup mysql database to Amazon S3 using django (Python) script - Agiliq Blog | Django web app development canada goose kensington down parka canada goose reviews
canada goose photos http://www.fedhaltero.qc.ca/canada-goose-coats/canada-goose-photos.html

commmenttor
grey cardy ugg boots

ugg boots for less Automatically backup mysql database to Amazon S3 using django (Python) script - Agiliq Blog | Django web app development
grey cardy ugg boots http://www.millinerysupplies.ie/clearance/grey-cardy-ugg-boots.html

commmenttor
how to tie ugg slippers

original ugg boots Automatically backup mysql database to Amazon S3 using django (Python) script - Agiliq Blog | Django web app development
how to tie ugg slippers http://landkfurniture.com/prices/how-to-tie-ugg-slippers.html

commmenttor
find great prices bobby wagner jersey nike seahawks authentic bobby wagner red black white wholesale

Right here is the nfl jersey truth your mother and father does not want you to find out
find great prices bobby wagner jersey nike seahawks authentic bobby wagner red black white wholesale http://allencountyfairgroundsin.com/node/jabbar-yellow-nba-basketball-jerseys/bobby-wagner-jersey-nike-seahawks-authentic-bobby-wagner-red-black-white-p-2431.html

commmenttor
limited stocks at limited time antonio gates jersey

The thing that Everybody Need To Know Regarding The nfl jersey Business
limited stocks at limited time antonio gates jersey http://www.ebangkok.org/abouts/russell-football-jerseys/shopping-wholesale-storecheap-mlb-jerseys/hmrarnnm-p-196.html

commmenttor
luxury nnamdi asomugha jersey nike 49ers authentic nnamdi asomugha orange navy white outlet shop

The Incredible State of the art nfl jersey method Found By My Associate
luxury nnamdi asomugha jersey nike 49ers authentic nnamdi asomugha orange navy white outlet shop http://www.dvmlabs.com/products/sleeve-basketball-jerseys/nfl-jerseys-for-sales-2012/avggkfgp-p-81.html

commmenttor
wholesale cheap seahawks golden tate navy orange white jersey nike womens mens outlet online shop

nfl jersey Fake photos ; A Really Perfect nfl jersey 'cheat' Which experts claim Fools 96.4% of the purchasers
wholesale cheap seahawks golden tate navy orange white jersey nike womens mens outlet online shop http://www.dhabaindianbistro.com/images/women-nfl-jerseys-for-cheap/dhohegor-p-453.html

commmenttor
designer cheap nike ryan tannehill blue navy white jersey dolphins pro shop best deals

Always remember When You Could get the nfl jersey free of charge, But You Never did ??
designer cheap nike ryan tannehill blue navy white jersey dolphins pro shop best deals http://www.hauinc.org/Pictures/cheap-baseball-hats-kids/new-canada-hockey-jerseys-for-2014-olympics/lysskagy-p-453.html

commmenttor
wholesale the newest lavonte david game jersey: buccaneers womens youth red orange white coupon code

This is basically the nfl jersey truths your folks does not want you to know!
wholesale the newest lavonte david game jersey: buccaneers womens youth red orange white coupon code http://www.hollandparkuganda.com/images/make-your-jersey-nba/nba-cheap-nfl-kids-jerseys-on-sale/aismswgv-p-308.html

commmenttor
designer julius peppers game jersey: bears womens youth red black white outlet online sale

Tired of the numerous nfl jersey news flashes? I am on this site for you!
designer julius peppers game jersey: bears womens youth red black white outlet online sale http://www.cash4giftcards.com/eby/reebok-nhl-jersey/iqvdkkkl-p-679.html

commmenttor
buying ugg boots online

classic ugg boots sale Automatically backup mysql database to Amazon S3 using django (Python) script - Agiliq Blog | Django web app development
buying ugg boots online http://www.on-siterefueling.com/cheap/buying-ugg-boots-online.html

commmenttor
uggs on sale

ugg boots cheap for women Automatically backup mysql database to Amazon S3 using django (Python) script - Agiliq Blog | Django web app development
uggs on sale http://www.towerviewkitchens.com/spray/uggs-on-sale.html

commmenttor
summer ugg boots

delaine ugg boots Automatically backup mysql database to Amazon S3 using django (Python) script - Agiliq Blog | Django web app development
summer ugg boots http://www.greenjokerpoker.com/outlet/summer-ugg-boots.html

commmenttor
genuine leather nike derrick johnson womens jersey chiefs navy white gold authentic online store

Specifically why the whole thing you find out about nfl jersey is entirely wrong and exactly what you need be aware of.
genuine leather nike derrick johnson womens jersey chiefs navy white gold authentic online store http://www.bonanza-tours.com/css/cheap-sport-jersey/monaco-soccer-jerseys/nike-derrick-johnson-womens-jersey-chiefs-navy-white-gold-authentic-p-6765.html

commmenttor
ugg boots kid

ugg men boots Automatically backup mysql database to Amazon S3 using django (Python) script - Agiliq Blog | Django web app development
ugg boots kid http://www.dublinmedievalsociety.com/zealand/ugg-boots-kid.html

commmenttor
Louis Vuitton M91532 sale

mens ugg butte boots sale ugg 2014 collection deckers uggs usa cardy uggs sizing pink uggs with bows and rhinestones kids uggs size 8 ugg australia natalee uggs for cheap magazine subscriptions ugg rain boots for sale ugg moccasins price scarpe nike air max skyline eu sand ugg bailey button boots 5803 clearance ugg fox fur tall boots air max nuove ugg kohala size 9 imitation ugg boots uk nike air max breathe collection release date ugg boots heels uggs bailey button 5803 for women sale chestnut nike air max 1 leopard uk 6
Louis Vuitton M91532 sale http://californiaconservativereview.com/?keyword=Louis-Vuitton-M91532-sale&id;=7

commmenttor
cheap ugg australia

male ugg boots Automatically backup mysql database to Amazon S3 using django (Python) script - Agiliq Blog | Django web app development
cheap ugg australia http://www.virgilpowerforklifts.net/infant/cheap-ugg-australia.html

commmenttor
buy discount gucci 2012 loafers in leather in black g642 hot sale online

gucci-gal has proved the most recent formula ; learn to make a lot of money from day 1
buy discount gucci 2012 loafers in leather in black g642 hot sale online http://www.hvcs.hu/images/gucci-factory-outlet-locations/gucci-new-york/gucci-2012-loafers-in-leather-in-black-g626-p-4199.html

commmenttor
65% discount gucci for men on sale,shoes gucci us sale

The Spectacular Innovative gucci secret Invented By My Good Friend
65% discount gucci for men on sale,shoes gucci us sale http://www.icc-greaterchicago.com/docs/gucci-for-men-on-sale/shoes-gucci/

commmenttor
brand nike justin tucker womens jersey ravens red white gold authentic sale online 2014

Rumors that nfl jersey pulls to a shut, let me reveal the follow-up
brand nike justin tucker womens jersey ravens red white gold authentic sale online 2014 http://3dbeaufort.com/news/cheap-nfl-jerseys-big-and-tall/old-champion-nba-jerseys/fdwnyauz-p-928.html

commmenttor
bailey ugg boots sale

search ugg boots Automatically backup mysql database to Amazon S3 using django (Python) script - Agiliq Blog | Django web app development
bailey ugg boots sale http://www.aaltobioreagents.ie/slippers/bailey-ugg-boots-sale.html

commmenttor
us nike demaryius thomas womens jersey broncos navy red white authentic your best choose

By Far The Most Thorough nfl jersey Strategy guide You Ever Witnessed Or else Your Money Back
us nike demaryius thomas womens jersey broncos navy red white authentic your best choose http://wisea.org/css/custom-cheap-soccer-jerseys-for-sale/penguins-throwback-jersey/nike-demaryius-thomas-womens-jersey-broncos-navy-red-white-authentic-p-3718.html

commmenttor
luxurious authentic reggie bush navy white silver ladies mens kids australia outlet store

Renovate an nfl jersey in half the time without having to spend additional cash!
luxurious authentic reggie bush navy white silver ladies mens kids australia outlet store http://www.victorianvintagespeedway.com/flash/cheap-replica-nfl-jerseys/peyton-manning-broncos-nfl-jerseys/szsbriuv-p-193.html

commmenttor
price explosion nike authentic jets santonio holmes jersey womens mens youth signed unbeatable price

Abroad News : nfl jersey Thought of as An Absolute Must In modern times
price explosion nike authentic jets santonio holmes jersey womens mens youth signed unbeatable price http://www.dcgwest.com/contact/reebok-nfl-jerseys-limited/nike-store-jerseys/nike-authentic-jets-santonio-holmes-jersey-p-1253.html

commmenttor
new navorro bowman black gold white jersey nike throwback 49ers elite sales promotion

The Astonishing " inside info " Of Methods One Can Reign over nfl jersey With Zero Practical experience!
new navorro bowman black gold white jersey nike throwback 49ers elite sales promotion http://ckprod.com/contact/cheap-premier-league-soccer-jerseys/navorro-bowman-black-gold-white-jersey-nike-throwback-49ers-elite-p-7030.html

commmenttor
ugg boots model

emu uggs Automatically backup mysql database to Amazon S3 using django (Python) script - Agiliq Blog | Django web app development
ugg boots model http://www.walkair.ie/sizing/ugg-boots-model.html

commmenttor
replica hermes jewellry

replica hermes jewellry hermes luggage replica loafers vqafgne

commmenttor
uggs sale clearance

snug australia ugg boots Automatically backup mysql database to Amazon S3 using django (Python) script - Agiliq Blog | Django web app development
uggs sale clearance http://www.njs.ie/cozy/uggs-sale-clearance.html

commmenttor
ugg boots originals discount code

ugg lexi slipper Automatically backup mysql database to Amazon S3 using django (Python) script - Agiliq Blog | Django web app development
ugg boots originals discount code http://www.shannonenergyvalley.com/delivery/ugg-boots-originals-discount-code.html

commmenttor
canada adrian peterson womens mens youth jersey authentic elite limited game sales promotion

The Only Solutions To Gain knowledge nfl jersey Plus The Way One Might Enroll with The nfl jersey Elite
canada adrian peterson womens mens youth jersey authentic elite limited game sales promotion http://www.asia-bolt.com/abouts/ice-hockey-jerseys-price/lzgxxqxt-p-834.html

commmenttor
cheap price jermaine gresham game jersey: bengals womens youth navy white silver clearance

Understand who's discussing about nfl jersey and also the particular reason why you ought to feel concerned.
cheap price jermaine gresham game jersey: bengals womens youth navy white silver clearance http://andeancap.com/abouts/nike-jerseys-designs/cheap-cute-baseball-hats-girls/jermaine-gresham-game-jersey--bengals-womens-youth-navy-white-silver-p-3296.html

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

© Agiliq, 2009-2012