sql-snapshot.py

sql-snapshot.py

#!/usr/bin/env python
import sys
import os
import time
import getopt

def show_help():
    help_text = """Usage: %s <options>
Available Options:
    --help
    -h, --host      Default: 127.0.0.1
    -u, --user      Default: root
    -b, --database
    -w, --pass
    -p, --port      Default: 3306
    -o, --outdir    Default: ./snapshots
    -r, --repeat    (y/n) Default: false
    -i, --interval  Default: 3600 (1 hour)""" % sys.argv[0]
    print(help_text)
    exit(0)

def create_snapshot(args):
    if not os.path.exists(args['dir']):
        os.makedirs(args['dir'])
    current_datetime = time.strftime("%Y-%m-%d_%H:%M:%S")
    backup = "%s/%s_%s.sql" %(args['dir'], args['db'], current_datetime)
    os.system("mysqldump --column-statistics=0 -h %s -u %s -p'%s' %s > %s"
            %(args['host'], args['user'], args['pw'], args['db'], backup))
    print('Backup Saved: %s\n' %backup)

if __name__ == '__main__':
    config = {}
    config['host'] = '27.0.0.1'
    config['user'] = 'root'
    config['port'] = 3306
    config['repeat'] = False
    config['interval'] = 3600
    config['dir'] = '%s/snapshots' % os.getcwd()

    try:
        opts, args = getopt.getopt(sys.argv[1:], "h:u:b:w:p:o:r:i:",
                ['help', 'host=', 'user=', 'database=', 'pass=',
                    'port=', 'outdir=', 'repeat=', 'interval='])
    except getopt.GetoptError:
        print('Error!') # Super unhelpful
        show_help()
        sys.exit(2)

    for opt, arg in opts:
        if opt == '--help':
            show_help()
            sys.exit()
        elif opt in ('-h', '--host'):
            config['host'] = arg
        elif opt in ('-u', '--user'):
            config['user'] = arg
        elif opt in ('-b', '--database'):
            config['db'] = arg
        elif opt in ('-w', '--pass'):
            config['pw'] = arg
        elif opt in ('-p', '--port'):
            config['port'] = arg
        elif opt in ('-o', '--outdir'):
            config['dir'] = arg
        elif opt in ('-r', '--repeat'):
            if arg == 'y':
                config['repeat'] = True
        elif opt in ('-i', '--interval'):
            config['interval'] = int(arg)

    if not 'db' in config:
        print('Error: Database required')
        exit(2)
    if not 'pw' in config:
        print('Error: Password required')
        exit(2)

    create_snapshot(config)

    while config['repeat']:
        time.sleep(config['interval'])
        create_snapshot(config)

Example usage:

./sql-snapshot.py \
-h YOUR_DB_HOST \
-u YOUR_DB_USER \
--database YOUR_DB \
--pass YOUR_PASS