Backup MySQL schemas to a git repository

backup.py

#!/usr/bin/ python
import sys, yaml, datetime, re
from subprocess import call, Popen, PIPE, STDOUT
config_path = sys.argv[1]

#    Read config file
print 'Config: ' + config_path
config_file = open(config_path)
config_map = yaml.load(config_file)
config_file.close()

#    Read configs out of resulting YAML object
dump_folder = config_map['folder']
database_user = config_map['database']['user']
database_password = config_map['database']['password']
database_schemas = config_map['database']['schemas']
output_file = open(dump_folder + '/log.txt', 'a+')

#    Dump schemas
for schema in database_schemas:
    now = datetime.datetime.now()
    output_file.write(now.strftime('%Y-%m-%d %H:%M') + ' Dumping ' + schema + '\n')
    dump_call = 'mysqldump --add-drop-table -u ' + database_user + ' -p' + database_password + ' ' + schema + ' > ' + dump_folder + '/' + schema + '.sql'
    print dump_call
    call(dump_call, shell=True)
    
#    Commit to git
output_file.write(now.strftime('%Y-%m-%d %H:%M') + ' Commiting to git\n')
print 'Commiting to git'
git_prefix = 'cd ' + dump_folder + ' && '
#    Initialize git repo if uninitialized
try:
    git_config = open(dump_folder + '/.git/config')
    git_config.close()
except:
    print "Initializing new GIT repo"
    call(git_prefix + 'git init', shell=True)
call(git_prefix + 'git add .', shell=True)
call(git_prefix + 'git commit -m "database backup: ' + now.strftime('%Y-%m-%d %H:%M') + '"', shell=True)


#    Attempt push to git remote
print 'Attempting to push to git remote...'

#    Check config for SSH, use HTTPS as failover (HTTPS requires a password which is lousy for cron jobs)
#    Pull in configs
try:
    print 'Trying SSH...'
    push_string = 'git push ' + config_map['git']['remotes']['ssh']['name'] + ' ' + config_map['git']['remotes']['ssh']['branch']
    git_uri = config_map['git']['remotes']['ssh']['uri']
    git_name = config_map['git']['remotes']['ssh']['name']
except:
    print 'SSH missing... Trying HTTPS instead...'
    try:
        push_string = 'git push ' + config_map['git']['remotes']['https']['name'] + ' ' + config_map['git']['remotes']['https']['branch']
        git_uri = config_map['git']['remotes']['https']['uri']
        git_name = config_map['git']['remotes']['https']['name']
    except:
        print 'HTTPS not found'

#        Complete push
try:
    remote_output = Popen(git_prefix + 'git remote', shell=True, stdin=PIPE, stdout=PIPE, stderr=STDOUT, close_fds=True)
    remote_result = remote_output.stdout.read()
    re_remote_result = re.search(git_name, remote_result)
    if re_remote_result is None:
        print 'Adding git remote'
        call(git_prefix + 'git remote add ' + git_name + ' ' + git_uri, shell=True)
    print push_string
    output_file.write(now.strftime('%Y-%m-%d %H:%M') + ' Pushing to git remote ' + git_uri + '\n')
    call(git_prefix + push_string, shell=True)
except:
    print "Push failed"
    output_file.write(now.strftime('%Y-%m-%d %H:%M') + ' Push to git remote failed.\n')
output_file.close()

call_backup.sh

python backup.py ~/path/to/config.yml

config.yml

folder: /absolute/path/to/dump/folder

database:
  user: username
  password: password
  schemas:
    - db1name
    - db2name
    - db3name
    - db4name#

git:
  remotes:
    ssh: 
      name: ssh_origin
      branch: master
      uri: '[email protected]:deltaepsilon/repo-name.git'
    https:
      name: https_origin
      branch: master
      uri: https://[email protected]/deltaepsilon/repo-name.git