Skip to content

Instantly share code, notes, and snippets.

@jlisee
Forked from ajessup/mysql_backup.py
Created September 9, 2012 22:33
Show Gist options
  • Save jlisee/3687716 to your computer and use it in GitHub Desktop.
Save jlisee/3687716 to your computer and use it in GitHub Desktop.
Simple python script for performing and optionally encrypting a rolling backup a mysql DB
#!/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()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment