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

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

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

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

dump 脚本

'''Created on 2012-8-20mysql dump to load@author: tudou@b2c.xiaomi.com'''import os,MySQLdb,multiprocessingmysql_bak='/tmp/mysqlbak/2012-09-18-21-44-34'mysql_base='/opt/soft/mysql_5.5.25'unix_socket='/tmp/mysql.sock'dump_user='root'dump_pwd='123456'dump_database={'test':'test'}#dump_database={'test':'test','mysql':'mysql'}def start_process():    print ('MySQLinput starting', multiprocessing.current_process().name)#input shcema#load data#input objectclass mysqlinput(object):    def __init__(self,conf):        self.conf=conf            def input(self):        os.system('chmod 777 -R '+mysql_bak)        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)            def inputschema(self,dbconf):        print "gunzip -cd "+mysql_bak+"/"+dbconf+"/schema/schemainfo.gz | "+mysql_base+"/bin/mysql -u"+dump_user+" -p****** -S"+unix_socket        os.system("gunzip -cd "+mysql_bak+"/"+dbconf+"/schema/schemainfo.gz | "+mysql_base+"/bin/mysql -u"+dump_user+" -p"+dump_pwd+" -S"+unix_socket+" "+dbconf)            def loadata(self,dbconf):        pool_size = multiprocessing.cpu_count()        pool = multiprocessing.Pool(processes=pool_size,initializer=start_process,)        filenames=os.listdir(mysql_bak+"/"+dbconf)        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':                    fileconf={'dbname':dbconf,'gzfile':filepath,'filename':filepath[0:len(filepath)-3],'tablename':filename[0:len(filename)-7]}                pool.apply_async(self.mygunzip(fileconf))                        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 loadpertable(self,fileconf):        sql="TRUNCATE \`"+fileconf['dbname']+"\`.\`"+fileconf['tablename']+"\`;LOAD DATA INFILE '"+fileconf['filename']+"' INTO TABLE \`"+fileconf['dbname']+"\`.\`"+fileconf['tablename']+"\`;"        print sql        os.system(mysql_base+"/bin/mysql -u"+dump_user+" -p"+dump_pwd+" -S"+unix_socket+" -e\""+sql+"\"")        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        os.system("gunzip -cd "+mysql_bak+"/"+dbconf+"/schema/objectinfo.gz | "+mysql_base+"/bin/mysql -u"+dump_user+" -p"+dump_pwd+" -S"+unix_socket+" "+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"