MySQL 5.5 DBA工具 多进程dump 多进程load 多进程备份还原 python 脚本

mysqldump 目前只支持但线程dump，物理备份innodb因为额ibdata又有天生的缺陷，例如将一个实例中的某个库导入到另一个实例中，就比较麻烦了。

所以写了这套脚本。

适用场景：

1 多库多表，尤其是多表数据比较均衡的

2 备份和恢复数据瓶颈在于单线程的

3 即便多核，强力磁盘iops也无用的

4 使用slave来备份数据，原因是此脚本dump的时候上读锁，会阻塞主库

需要安装 python gzip ： yum install python gzip -y

原文链接：http://blog.csdn.net/ylqmf/article/details/7993701

dump 脚本

'''
Created on 2012-8-20
mysql dump to load
@author: tudou@b2c.xiaomi.com
'''
import os,time,multiprocessing


mysql_bak='/tmp/mysqlbak'
mysql_base='/opt/soft/mysql_5.5.25'
unix_socket='/tmp/mysql.sock'
dump_user='root'
dump_pwd='123456'
dump_database=['test','mysql']


def dogzip(fileconf):
    os.system('gzip '+fileconf)


def mysqlexec(sql,outfile='',gz=''):
    if outfile=='':
        os.system(mysql_base+"/bin/mysql -u"+dump_user+" -p"+dump_pwd+" -S"+unix_socket+" --default-character-set=utf8 -e\""+sql+"\"")
    elif gz=='':
        os.system(mysql_base+"/bin/mysql -u"+dump_user+" -p"+dump_pwd+" -S"+unix_socket+" --default-character-set=utf8 -e\""+sql+"\" > "+outfile)
    else:
        os.system(mysql_base+"/bin/mysql -u"+dump_user+" -p"+dump_pwd+" -S"+unix_socket+" --default-character-set=utf8 -e\""+sql+"\" | gzip > "+outfile)


def dumplay(dbconf):
    loadname=dbconf['dumpdir']+"/"+dbconf['dbname']+"/"+dbconf['tablename']+".sql"
    schemaname=dbconf['dumpdir']+'/'+dbconf['dbname']+"/schema/tables/"+dbconf['tablename']+".sql"
    sql="SHOW CREATE TABLE \`"+dbconf['dbname']+"\`.\`"+dbconf['tablename']+"\`\G"
    mysqlexec(sql,schemaname)
    os.system("sed -i '1,2d;s/Create Table:/DROP TABLE IF EXISTS "+dbconf['tablename']+";/g' "+schemaname)
    dogzip(schemaname)
    sql="SELECT * FROM \`"+dbconf['dbname']+"\`.\`"+dbconf['tablename']+"\` INTO OUTFILE '"+loadname+"';"
    #print sql
    mysqlexec(sql)
    dogzip(loadname)


class mysqldump(object):
    def __init__(self,conf):
        self.conf=conf
        self.dumpdir=mysql_bak
        
    def dump(self):
        sql="FLUSH TABLES WITH READ LOCK;"
        mysqlexec(sql)
        #create dir
        self.dumpdir += '/'+str(time.strftime('%Y-%m-%d-%H-%M-%S',time.localtime(time.time())))
        os.system('mkdir -p '+self.dumpdir+'/mysql/schema')
        for dir in dump_database:
            os.system('mkdir -p '+self.dumpdir+'/'+dir+'/schema/tables')
        os.system('chmod 777 -R '+self.dumpdir)
        #get create table
        for dir in dump_database:
            self.getschemainfo(dir)
        #dump per table
        self.getdbinfo()
        sql="UNLOCK TABLES;"
        mysqlexec(sql)
        os.system("echo \"end backup `date +%Y-%m-%d-%HH-%MM-%SS` \" >> "+self.dumpdir+'/mysql/schema/dump_log')
    
    def getschemainfo(self,dbconf):
        os.system(mysql_base+'/bin/mysqldump -d --skip-triggers --add-drop-table -u'+dump_user+' -p'+dump_pwd+' -S'+unix_socket+' '+dbconf+' > '+self.dumpdir+'/'+dbconf+'/schema/schemainfo' )
        os.system('gzip '+self.dumpdir+'/'+dbconf+'/schema/schemainfo')
        os.system(mysql_base+'/bin/mysqldump -tdRE --triggers=true -u'+dump_user+' -p'+dump_pwd+' -S'+unix_socket+' '+dbconf+' > '+self.dumpdir+'/'+dbconf+'/schema/objectinfo' )
        os.system('gzip '+self.dumpdir+'/'+dbconf+'/schema/objectinfo')
    
    def getdbinfo(self):
        sql="SHOW MASTER STATUS\G"
        mysqlexec(sql,self.dumpdir+'/mysql/schema/master_date')
        sql="SHOW SLAVE STATUS\G"
        mysqlexec(sql,self.dumpdir+'/mysql/schema/slave_status')
        os.system("echo \"begin backup `date +%Y-%m-%d-%HH-%MM-%SS` \" > "+self.dumpdir+'/mysql/schema/dump_log')
        sql="SELECT TABLE_SCHEMA,TABLE_NAME FROM \`information_schema\`.\`TABLES\` WHERE TABLE_SCHEMA IN ('"+ "','".join(dump_database) +"') ORDER BY DATA_LENGTH DESC"
        #re = list(con.execute(sql))
        output=os.popen(mysql_base+"/bin/mysql -u"+dump_user+" -p"+dump_pwd+" -S"+unix_socket+" --default-character-set=utf8 -e\""+sql+"\"")
        re=output.read()
        #inputs=list()
        pool_size = multiprocessing.cpu_count()*2
        pool = multiprocessing.Pool(processes=pool_size)
        inputs=list()
        re=re.split('\n')
        i=0
        for tb in re:
            tmp=tb.split('\t')
            if len(tmp)==2 and i>0:
                inputs.append({'dbname':tmp[0],'tablename':tmp[1],'dumpdir':self.dumpdir})
            i+=1


        #print inputs
        pool.map(dumplay,inputs)
        pool.close() # no more tasks
        pool.join()  # wrap up current tasks
            
if __name__ == '__main__':
    conf={'host':'localhost','socket':unix_socket,'user':dump_user,'pwd':dump_pwd,'db':'information_schema'}
    dump=mysqldump(conf);
    dump.dump();
    print "dump success"

原文链接：http://blog.csdn.net/ylqmf/article/details/7993701

load 脚本

'''
Created on 2012-8-20
mysql dump to load
@author: tudou@b2c.xiaomi.com
'''
import os,multiprocessing,stat


mysql_bak='/tmp/mysqlbak/2012-09-21-15-10-27'
mysql_base='/opt/soft/mysql_5.5.25'
unix_socket='/tmp/mysql.sock'
dump_user='root'
dump_pwd='123456'
dump_database={'test':'test1'}#dump_database={'test':'test','mysql':'mysql'}


def loadpertable(fileconf):
    #os.system("gunzip -cd "+mysql_bak+"/"+fileconf['dbname']+"/schema/tables/"+fileconf['tablename']+".sql.gz | "+mysql_base+"/bin/mysql -u"+dump_user+" -p"+dump_pwd+" -S"+unix_socket+" "+dump_database[fileconf['dbname']])
    os.system("mkfifo --mode=0666 /tmp/"+dump_database[fileconf['dbname']]+"."+fileconf['tablename']+".tudou.p")
    os.system("setsid gzip --stdout -d "+fileconf['gzfile']+" > /tmp/"+dump_database[fileconf['dbname']]+"."+fileconf['tablename']+".tudou.p &")
    sql="ALTER TABLE \`"+dump_database[fileconf['dbname']]+"\`.\`"+fileconf['tablename']+"\` DISABLE KEYS;LOAD DATA INFILE '/tmp/"+dump_database[fileconf['dbname']]+"."+fileconf['tablename']+".tudou.p' INTO TABLE \`"+dump_database[fileconf['dbname']]+"\`.\`"+fileconf['tablename']+"\` CHARACTER SET utf8;ALTER TABLE \`"+dump_database[fileconf['dbname']]+"\`.\`"+fileconf['tablename']+"\` ENABLE KEYS;"
    print sql
    os.system(mysql_base+"/bin/mysql -u"+dump_user+" -p"+dump_pwd+" -S"+unix_socket+" -e\""+sql+"\"")
    os.system("rm -f /tmp/"+dump_database[fileconf['dbname']]+"."+fileconf['tablename']+".tudou.p")
#input shcema
#load data
#input object
class mysqlinput(object):
    def __init__(self,conf):
        self.conf=conf
        
    def input(self):
        os.system('chmod 777 -R '+mysql_bak)
        os.system("echo \"begin load `date +%Y-%m-%d-%HH-%MM-%SS` \" > "+mysql_bak+'/mysql/schema/load_log')
        os.system(mysql_base+"/bin/mysql -u"+dump_user+" -p"+dump_pwd+" -S"+unix_socket+" -e\"SET GLOBAL unique_checks=0;SET GLOBAL foreign_key_checks=0;\"")
        dirnames=os.listdir(mysql_bak)
        for dirname in dirnames:
            #print dirname
            if dump_database.has_key(dirname):
                self.inputschema(dirname)


        for dirname in dirnames:
            if dump_database.has_key(dirname):
                self.loadata(dirname)
        
        for dirname in dirnames:
            if dump_database.has_key(dirname):
                self.inputobject(dirname)
        os.system(mysql_base+"/bin/mysql -u"+dump_user+" -p"+dump_pwd+" -S"+unix_socket+" -e\"SET GLOBAL unique_checks=1;SET GLOBAL foreign_key_checks=1;\"")
        os.system("echo \"end load `date +%Y-%m-%d-%HH-%MM-%SS` \" >> "+mysql_bak+'/mysql/schema/load_log')
        os.system("cat "+mysql_bak+"/mysql/schema/master_date")
        os.system("cat "+mysql_bak+"/mysql/schema/slave_status")
        
    def inputschema(self,dbconf):
        sql="CREATE DATABASE IF NOT EXISTS \`"+dump_database[dbconf]+"\`"
        os.system(mysql_base+"/bin/mysql -u"+dump_user+" -p"+dump_pwd+" -S"+unix_socket+" -e\""+sql+"\"")
        #print "gunzip -cd "+mysql_bak+"/"+dbconf+"/schema/schemainfo.gz | "+mysql_base+"/bin/mysql -u"+dump_user+" -p****** -S"+unix_socket+" "+dump_database[dbconf]
        os.system("gunzip -cd "+mysql_bak+"/"+dbconf+"/schema/schemainfo.gz | "+mysql_base+"/bin/mysql -u"+dump_user+" -p"+dump_pwd+" -S"+unix_socket+" "+dump_database[dbconf])
        
    def loadata(self,dbconf):
        pool_size = multiprocessing.cpu_count()*2
        pool = multiprocessing.Pool(processes=pool_size)
        filenames=os.listdir(mysql_bak+"/"+dbconf)
        inputs=list()
        for filename in filenames:
            filepath=mysql_bak+"/"+dbconf+"/"+filename
            
            if os.path.isfile(filepath):
                #fileconf={'dbname':dbconf,'gzfile':filepath,'filename':filepath,'tablename':filename[0:len(filename)-4]}
                #c=fileconf['gzfile']
                #if c[len(c)-3:len(c)]=='.gz':
                inputs.append({'dbname':dbconf,'gzfile':filepath,'filename':filepath[0:len(filepath)-3],'tablename':filename[0:len(filename)-7],'filesize':os.stat(filepath)[stat.ST_SIZE]})
                #pool.apply_async(self.mygunzip(fileconf))
        inputs.sort(cmp=lambda x,y:cmp(y['filesize'],x['filesize']))
        pool.map(loadpertable,inputs)
        pool.close() # no more tasks
        pool.join()  # wrap up current tasks
        
    def mygunzip(self,fileconf):
        c=fileconf['gzfile']
        if c[len(c)-3:len(c)]=='.gz':
            os.system("gunzip "+fileconf['gzfile'])
        self.loadpertable(fileconf)
        
    def inputobject(self,dbconf):
        print "gunzip -cd "+mysql_bak+"/"+dbconf+"/schema/objectinfo.sql.gz | "+mysql_base+"/bin/mysql -u"+dump_user+" -p****** -S"+unix_socket+" "+dump_database[dbconf]
        os.system("gunzip -cd "+mysql_bak+"/"+dbconf+"/schema/objectinfo.gz | "+mysql_base+"/bin/mysql -u"+dump_user+" -p"+dump_pwd+" -S"+unix_socket+" "+dump_database[dbconf])
        
if __name__ == '__main__':
    conf={'host':'localhost','socket':unix_socket,'user':dump_user,'pwd':dump_pwd,'db':'information_schema'}
    input=mysqlinput(conf);
    input.input();
    print "load success"

原文链接：http://blog.csdn.net/ylqmf/article/details/7993701