Tag Archives: python
23/11/2010 

Backup Mysql database to Amazon S3 using Python

I wrote this script to do nightly backups of a mysql database to an Amazon S3 account. It’s the first bit of python I’ve ever done so please excuse any obvious mistakes.

It uses the Amazon S3 Library for REST in Python by EricW@AWS, which you can download here

The script does a mysqldump, compresses it and sends it to the S3 account. the KEEP var is the number days worth of backups you want to keep. It will delete the backup file which matches (today – KEEP). Its not ideal but it works for me.

from datetime import date, timedelta
 
import subprocess, tarfile, os, S3
 
#Mysql
mysql_user = "{DB USER}"
mysql_pass = "{DB PASS}"
mysql_db = "{DB NAME}"
mysql_host = "{DB HOST}"
mysql_dump = "mysqldump"
 
#S3
AWS_ACCESS_KEY_ID = "{AWS_ACCESS_KEY_ID}"
AWS_SECRET_ACCESS_KEY = "{AWS_SECRET_ACCESS_KEY}"
 
BUCKET_NAME = "{BUCKET NAME}"
 
#path name, leave empty if not required
FOLDER = "mysql-backup/"
 
#number days worth of backups to keep
KEEP = 5
 
#Output
output_dir = "/tmp/"
output_file = "db-" + str(date.today()) + ".sql"
 
print "start mysqldump..."
 
subprocess.call( mysql_dump + " --user " + mysql_user + " --password=" + mysql_pass + " --add-locks --flush-privileges --add-drop-table --complete-insert --extended-insert --single-transaction --database " + mysql_db + " > " + output_dir + output_file, shell=True )
 
print "compressing " + output_file + "..."
 
tar_file = output_file + ".tar.gz"
 
tar = tarfile.open( output_dir + tar_file , "w|gz")
tar.add(output_dir + output_file)
tar.close()
 
tar_data = open( output_dir + tar_file , "rb").read()
 
print "uploading to S3..."
 
conn = S3.AWSAuthConnection( AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY )
 
response = conn.put( BUCKET_NAME, FOLDER + tar_file, S3.S3Object( tar_data ) )
 
if response.http_response.status == 200 :
 
	oldest_backup = "db-" + str( date.today() - timedelta(days=KEEP) ) + ".sql.tar.gz"
 
	response = conn.delete(BUCKET_NAME, FOLDER + oldest_backup)
 
else:
 
    #should probably send an email here
 
    print "Error : "+response.message
 
print "deleting temporary files..."
 
os.remove(output_dir + output_file)
os.remove(output_dir + tar_file)
 
print "complete"