MySQL progressive backup

gistfile1.py

#!/usr/bin/python
import glob
import os
import re
from datetime import datetime

mysql_datadir = "/var/lib/mysql/"
mysql_username = "root"
mysql_password = "password"
outdir = "/home/mysql_backup/"
ignore_dbs = ["mysql"]

que = open(os.path.join(outdir, 'que.log'), "w" )

seq = 0
for path in glob.iglob(os.path.join(mysql_datadir, "*/*.frm")):
    database = os.path.basename(os.path.dirname(path))
    if database in ignore_dbs:
        continue
    table = os.path.basename(path[:-4])

    seq = seq + 1

    # 1. dump
    dumpinto = '%08d-%s.%s.sql' % (seq, database, table)
    output = os.path.join(outdir, dumpinto)
    cmd = 'mysqldump -u%s -p%s -e -q --single-transaction --master-data %s %s > %s' % \
        ( mysql_username, mysql_password, database, table, output )

    print datetime.now(), cmd
    os.system(cmd)
    print datetime.now(), "mysqldump finished"
    que.write( 'DUMP: %s\n' % dumpinto )

    # 2. get master log position
    try:
        f = open(output, "r")
    except:
        print "ERROR: Can't find mysqldump result"
        quit()


    l = 0
    master_log_file = None
    master_log_pos = None

    while l < 25:
        l = l + 1
        res = f.readline()

        m = re.match( "CHANGE MASTER TO MASTER_LOG_FILE='([^']+)', MASTER_LOG_POS=(\d+);", res )
        if m:
            master_log_file = m.group(1)
            master_log_pos = m.group(2)
            break;
    f.close()

    if not master_log_file:
        print "ERROR: Can't find MASTER LOG position."
        quit()

    print datetime.now(), "master_log_file = %s, master_log_pos = %s" % ( master_log_file, master_log_pos )
    que.write( 'SYNC: %s,%s\n' % ( master_log_file, master_log_pos ) )

que.close()