Skip to content

Instantly share code, notes, and snippets.

@jlisee

jlisee/mysql_backup.py

Forked from ajessup/mysql_backup.py
Created September 9, 2012 22:33
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save jlisee/3687716 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save jlisee/3687716 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @ajessup ajessup created this gist Apr 13, 2012.
    100 changes: 100 additions & 0 deletions mysql_backup.py
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,100 @@
    #!/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()