mysql 数据库dump ----python之mysqldump

利用mysqldump工具对mysql的innodb库进行备份或者在同步主备的时候先将主库已经有的数据同步到备库,保证主备库的一致性

需要额外安装的模块: sqlparse

#!/usr/bin/env python#-*- coding:utf-8 -*-import osfrom warnings import filterwarningsimport MySQLdbfilterwarnings('ignore', category=MySQLdb.Warning)import ConfigParserimport osimport sysreload(sys)sys.setdefaultencoding("utf8")import sqlparsedef dump_mysql(host, port, data_base):    try:        dump_dir = os.getcwd() + "/tmp.sql"        print "dump指定host的数据库到本地"        os.popen("mysqldump -h%s -u%s -p%s -P %d --default-character-set=utf8 --single-transaction %s > %s"                                            % (host, "your user" ,                                            "your password", port,                                            data_base, dump_dir)) #注意user必须是远程账户        print "开启本地3306端口数据库接收dump数据"        os.popen("service mysqld start")        conn = MySQLdb.connect(host="127.0.0.1", user="your user",                                            passwd="your password", port=3306, charset="utf8")        cur = conn.cursor()            cur.execute("CREATE DATABASE if not exists %s" % data_base)        conn.select_db("%s" % data_base)        sql = open(dump_dir).read()        sql_parts = sqlparse.split(sql)        for sql_part in sql_parts:            if sql_part.strip() == '':                continue            elif str(sql_part.strip()).count(";") > 1:                for tmp in sql_part.strip().split(";"):                    if tmp.strip() == "":                        continue                    else:                        cur.execute(tmp.strip())            else:                cur.execute(sql_part.strip())        conn.commit()        cur.close()        conn.close()        print "done"        print "dump的tmp.sql文件可在当前目录下找到"    except Exception as ex:        raise exif __name__ == "__main__":    host = raw_input('input  host: ')    while True:        if not host:            print "please input correct host"            host = raw_input('input  host: ')        else:            break                    port = raw_input('input  port: ')    while True:        if not port:            print "please input correct port"            port = raw_input('input  port: ')        else:            try:                int(port)                break            except:                print "please input correct port"                port = raw_input('input  port: ')                    data_base = raw_input('input  database: ')    while True:        if not data_base:            print "please input correct database"            data_base = raw_input('input  database: ')        else:            break                    dump_mysql(host, int(port), data_base)

最近发现了一种较为简单的方式,而且上述方式在遇到编码问题时很头疼!~~~

process = Popen('mysql %s -h192.168.xxx.xxx -uxxxx -pxxxx, -P %d --default-character-set=utf8' %                                    (database, port), stdout=PIPE, stdin=PIPE, shell=True)process.communicate('source' + " " + dump_dir)#dump_dir为上面mysqldump到的xxx.sql