Simple python script for performing and optionally encrypting a rolling backup a mysql DB

mysql_backup.py

#!/usr/bin/env python

'''
	Conduct rolling backups of a mysql DB server. 
	Daily backups are kept for the previous 7 days, 
	weekly for the previous 4 weeks, and monthly backups
	for the previous 12 months.

	Run ./mysql_backup --help for usage instructions

	@author Andrew Jessup
'''

import os
from optparse import OptionParser
import time
import datetime
import logging
import subprocess
import shlex
import shutil

SECONDS_IN_A_MONTH = 2629743.83
SECONDS_IN_A_WEEK = 604800
WEEKLY_BACKUP_COUNT = 4
MONTHLY_BACKUP_COUNT = 12

def main():
	parser = OptionParser()

	parser.add_option("-u", "--user", dest="username", help="Mysql username to connect with", action="store", default="root")
	parser.add_option("-p", "--password", dest="password", help="Mysql password to connect with", action="store", default="")
	parser.add_option("-H", "--host", dest="host", help="Mysql host to connect to", action="store", default="localhost")
	parser.add_option("-d", "--databases", dest="databases", help="Comma seperated list of databases to backup", action="store", default="")
	parser.add_option("-D", "--encrypt_dir", dest="encrypt_dir", help="If specfified, will cause encrypted files to be stored in --encrypt_dir with a gpg_key corresponding to this user")
	parser.add_option("-U", "--encrypt_user", dest="encrypt_user", help="If specfified along with --encrypt_user, will store an encrypted version of the files in a particular directory")
	parser.add_option("-t", "--temp_dir", dest="temp_dir", help="If specified, a user-writeable directory to store working files", default="/tmp", action="store")
	parser.add_option("-l", "--log_dir", dest="log_dir", help="Path to log to (logs to stdout if not specified)", default="", action="store")
	parser.add_option("-o", "--output_dir", dest="output_dir", help="Path where files are saved", action="store", default=os.path.dirname(__file__))

	options, arguments = parser.parse_args()

	logging.basicConfig(level=logging.DEBUG)

	if len(options.log_dir):
		logging.basicConfig(filename=options.log_dir, level=logging.DEBUG)

	now = datetime.datetime.utcnow() # this script can take a while to run

	backup_key = seconds_since_epoch = int(time.time())
	scratch_backup_directory = '%s/mysql_backup_%s' % (options.temp_dir, backup_key)

	logging.debug("Creating scratch backup directory %s" % scratch_backup_directory)
	if not os.path.exists(scratch_backup_directory):
	    os.makedirs(scratch_backup_directory)

	for database in options.databases.split(','):
		archive_path = "%s/%s.gz" % (scratch_backup_directory, database)

		logging.debug("Getting dump of database %s, archiving to temp archive_path" % database)
		subprocess.call("mysqldump -u%s -p%s -h%s %s > %s/%s.sql" % (options.username, options.password, options.host, database, scratch_backup_directory, database), shell=True);
		logging.debug("Zipping %s" % database)
		subprocess.call("gzip -c %s/%s.sql > %s" % (scratch_backup_directory, database, archive_path), shell=True)
		subprocess.call("rm %s/%s.sql" % (scratch_backup_directory, database), shell=True)


		# Create daily backup
		subprocess.call("cp %s/%s.gz %s/day%s_%s.gz" % (scratch_backup_directory, database, options.output_dir, now.weekday(), database), shell=True)
		_copy(options, archive_path, "day%s_%s" % (now.weekday(), database))
		_encrypt(options, archive_path, "day%s_%s" % (now.weekday(), database))

		# If this is the first day of the week, create the weekly backup
		if now.weekday() == 0:
			week_num = int(seconds_since_epoch / SECONDS_IN_A_WEEK) % WEEKLY_BACKUP_COUNT
			logging.debug("Creating weekly snapshot of %s for week %s" % (database, week_num))
			_copy(options, archive_path, "week%s_%s" % (week_num, database))
			_encrypt(options, archive_path, "week%s_%s" % (week_num, database))

		# If this is the first day of the month, create the monthly backup
		if now.day == 0:
			month_num = int(seconds_since_epoch / SECONDS_IN_A_MONTH) % MONTHLY_BACKUP_COUNT
			logging.debug("Creating monthly snapshot of %s for month %s" % (database, month_num))
			_copy(options, archive_path, "month%s_%s" % (month_num, database))
			_encrypt(options, archive_path, "month%s_%s" % (month_num, database))

	logging.debug("Deleting scratch backup directory %s" % scratch_backup_directory)
	shutil.rmtree(scratch_backup_directory)

def _copy(options, from_path, prefix):
	logging.debug("Copyting %s to %s/%s.gz" % (from_path, options.output_dir, prefix))
	subprocess.call("cp %s %s/%s.gz" % (from_path, options.output_dir, prefix), shell=True)

def _encrypt(options, from_path, prefix):
	if (options.encrypt_user and options.encrypt_dir):
		encrypt_file_path = "%s/%s.gpg" % (options.encrypt_dir, prefix)
		logging.debug("Encrypting %s to %s with user key %s" % (from_path, encrypt_file_path, options.encrypt_user))
		subprocess.call("gpg --trust-model always --batch --yes -r \"%s\" --output %s --encrypt %s" % (options.encrypt_user, encrypt_file_path, from_path), shell=True)

if __name__ == '__main__':
	main()