python之mysql db单表备份

单表备份

#!/usr/local/bin/python3
# coding:utf-8

# ====================================================
# Author: chang - EMail:changbo@hmg100.com
# Last modified: 2017-4-4
# Filename: tablebackup.py
# Description: backup mysql files,base mysqldump
# blog:http://www.cnblogs.com/changbo
# ====================================================

import time, os
import logging
import subprocess
import pymysql

# set log level Debug
logging.basicConfig(level=logging.DEBUG,
                    format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s',
                    datefmt='%a, %d %b %Y %H:%M:%S',
                    filename='backup.log',
                    filemode='a')

userdir = '/usr/my.cnf'
basedir = '/var/backup'
tmpfile = '%s/temp' % basedir
curtime = time.strftime('%m%d', time.localtime())
curfile = '%s/%s' % (basedir, curtime)


if not os.path.exists(tmpfile):
    commond3 = 'mkdir -p %s' % tmpfile
    subprocess.call(commond3, shell=True)

def mvbakfile():
    commend1 = 'mkdir %s' % curfile
    subprocess.call(commend1, shell=True)
    commend2 = 'mv %s/* %s  && tar czf %s/mysqlbak%s.tar.gz %s 1>/dev/null 2>&1' % (tmpfile, curfile, basedir, curtime, curfile)
    subprocess.call(commend2, shell=True)


def cleanold():
    commend1 = 'rm -rf %s' % curfile
    subprocess.call(commend1, shell=True)

def bakup():
    db = pymysql.connect("xxxxx", "xxxx", "xxxxxx")
    cursor = db.cursor()
    sqlcmd = 'show databases;'

    cursor.execute(sqlcmd)
    data = cursor.fetchall()
    db1 = []
    t1 = []

    for i in range(len(data)):
        dbi = data[i][0]
        db1.append(dbi)

    for i in db1:
        selectdb = 'use %s;' % i
        commond1 = 'mkdir %s/%s' % (tmpfile, i)
        subprocess.call(commond1, shell=True)
        cursor.execute(selectdb)
        showtables = 'show tables;'
        cursor.execute(showtables)
        data1 = cursor.fetchall()
        for x in data1:
            t1.append(x[0])
            sqlbak = 'mysqldump --defaults-file=/usr/my.cnf --flush-logs  --skip-lock-tables --quick  %s %s > %s/%s/%s.sql' % (i, x[0], tmpfile, i, x[0])
            try:
                subprocess.call(sqlbak, shell=True)
                logging.info(sqlbak)
            except Exception as e:
                pass

    db.close()
    cursor.close()


if __name__ == '__main__':
    bakup()
    mvbakfile()
    cleanold()