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 脚本
'''原文链接：http://blog.csdn.net/ylqmf/article/details/7993701
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"