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