MySQL5.5DBA工具多进程dump多进程load多进程备份还原Pytho

需要安装 python MySQL-python gzip ： yum install python MySQL-python gzip -y dump 脚本 1. 2.Created on 2012-8-20 3.mysql dump to load 4.@author: tudou@b2c.xiaomi.com 5. 6.import os,time,MySQLdb,multiprocessing 7. 8.mysql_bak=/tmp/mysqlbak 9

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

dump 脚本

1.&＃39;&＃39;&＃39;&＃39;&＃39;
2.Created on 2012-8-20
3.mysql dump to load
4.@author: tudou@b2c.xiaomi.com
5.&＃39;&＃39;&＃39;
6.import os,time,MySQLdb,multiprocessing 7.
8.mysql_bak=&＃39;/tmp/mysqlbak&＃39; 9.mysql_base=&＃39;/opt/soft/mysql_5.5.25&＃39; 10.unix_socket=&＃39;/tmp/mysql.sock&＃39; 11.dump_user=&＃39;root&＃39; 12.dump_pwd=&＃39;123456&＃39; 13.dump_database=[&＃39;test&＃39;,&＃39;mysql&＃39;] 14.
15.def start_process(): 16. print (&＃39;MySQLdump starting&＃39;, multiprocessing.current_process().name) 17.
18.class mysqldump(object): 19. def __init__(self,conf): 20. self.cOnf=conf 21. self.dumpdir=mysql_bak 22.
23. def dump(self): 24. #create dir 25. self.dumpdir += &＃39;/&＃39;+str(time.strftime(&＃39;%Y-%m-%d-%H-%M-%S&＃39;,time.localtime(time.time()))) 26. for dir in dump_database: 27. os.system(&＃39;mkdir -p &＃39;+self.dumpdir+&＃39;/&＃39;+dir) 28. os.system(&＃39;mkdir -p &＃39;+self.dumpdir+&＃39;/&＃39;+dir+&＃39;/schema&＃39;) 29. os.system(&＃39;chmod 777 -R &＃39;+self.dumpdir) 30. #get create table 31. for dir in dump_database: 32. self.getschemainfo(dir) 33. #dump per table 34. self.getdbinfo() 35.
36. def getschemainfo(self,dbconf): 37. os.system(mysql_base+&＃39;/bin/mysqldump -d --add-drop-table -u&＃39;+dump_user+&＃39; -p&＃39;+dump_pwd+&＃39; -S&＃39;+unix_socket+&＃39; &＃39;+dbconf+&＃39; > &＃39;+self.dumpdir+&＃39;/&＃39;+dbconf+&＃39;/schema/schemainfo&＃39; ) 38. os.system(&＃39;gzip &＃39;+self.dumpdir+&＃39;/&＃39;+dbconf+&＃39;/schema/schemainfo&＃39;) 39. os.system(mysql_base+&＃39;/bin/mysqldump -tdRE -u&＃39;+dump_user+&＃39; -p&＃39;+dump_pwd+&＃39; -S&＃39;+unix_socket+&＃39; &＃39;+dbconf+&＃39; > &＃39;+self.dumpdir+&＃39;/&＃39;+dbconf+&＃39;/schema/objectinfo&＃39; ) 40. os.system(&＃39;gzip &＃39;+self.dumpdir+&＃39;/&＃39;+dbconf+&＃39;/schema/objectinfo&＃39;) 41.
42. def getdbinfo(self): 43. con=db(self.conf) 44. sql="SELECT TABLE_SCHEMA,TABLE_NAME FROM information_schema.`TABLES` WHERE TABLE_SCHEMA IN (&＃39;"+ "&＃39;,&＃39;".join(dump_database) +"&＃39;)" 45. re = list(con.execute(sql))
46. #inputs=list() 47. pool_size = multiprocessing.cpu_count()
48. pool = multiprocessing.Pool(processes=pool_size,initializer=start_process,)
49. for tb in re: 50. #inputs.append({&＃39;dbname&＃39;:tb[0],&＃39;tablename&＃39;:tb[1]}) 51. pool.apply_async(self.dumplay({&＃39;dbname&＃39;:tb[0],&＃39;tablename&＃39;:tb[1]})) 52. #self.dumplay({&＃39;dbname&＃39;:tb[0],&＃39;tablename&＃39;:tb[1]}) 53. #print inputs 54. pool.close() # no more tasks 55. pool.join() # wrap up current tasks 56.
57. def dumplay(self,dbconf): 58. loadname=self.dumpdir+"/"+dbconf[&＃39;dbname&＃39;]+"/"+dbconf[&＃39;tablename&＃39;]+".sql" 59. con=db(self.conf) 60. sql="SELECT * FROM "`+dbconf[&＃39;dbname&＃39;]+"`."`+dbconf[&＃39;tablename&＃39;]+"` INTO OUTFILE &＃39;"+loadname+"&＃39;" 61. #print sql 62. con.executeNoQuery(sql)
63. self.dogzip(loadname) 64.
65. def dogzip(self,fileconf): 66. os.system(&＃39;gzip &＃39;+fileconf) 67.
68.&＃39;&＃39;&＃39;&＃39;&＃39; 69.
70.&＃39;&＃39;&＃39;
71.class MySQLHelper(object): 72. @staticmethod
73. def getConn(conf): 74. pot = 3306 75. if(conf.has_key(&＃39;port&＃39;)): 76. pot=conf[&＃39;port&＃39;] 77. dbname=&＃39;test&＃39; 78. if(conf.has_key(&＃39;db&＃39;)): 79. dbname=conf[&＃39;db&＃39;] 80.
81. if(conf.has_key(&＃39;socket&＃39;)): 82. return MySQLdb.connect(host=conf[&＃39;host&＃39;],unix_socket=conf[&＃39;socket&＃39;],user=conf[&＃39;user&＃39;],passwd=conf[&＃39;pwd&＃39;],db=dbname) 83. else: 84. return MySQLdb.connect(host=conf[&＃39;host&＃39;],port=pot,user=conf[&＃39;user&＃39;],passwd=conf[&＃39;pwd&＃39;],db=dbname) 85.&＃39;&＃39;&＃39;&＃39;&＃39; 86.
87.&＃39;&＃39;&＃39;
88.class db (object): 89. def __init__(self,conf): 90. self.cOnn=None 91. self.cOnn=MySQLHelper.getConn(conf) 92.
93. def execute(self,sql,mod=&＃39;&＃39;): 94. if(mod==&＃39;dict&＃39;): 95. cursor=self.conn.cursor(MySQLdb.cursors.DictCursor) 96. else: 97. cursor=self.conn.cursor() 98. cursor.execute(sql)
99. set=cursor.fetchall()
100. return set 101.
102. def executeNoQuery(self,sql,param={}): 103. cursor=self.conn.cursor() 104. try: 105. if(param=={}): 106. rownum=cursor.execute(sql)
107. else: 108. rownum=cursor.executemany(sql,param)
109. self.conn.commit() 110. return rownum 111. finally: 112. cursor.close()
113.
114. def __del__(self): 115. if (self.conn!=None): 116. self.conn.close() 117.
118.if __name__ == &＃39;__main__&＃39;: 119. cOnf={&＃39;host&＃39;:&＃39;localhost&＃39;,&＃39;socket&＃39;:unix_socket,&＃39;user&＃39;:dump_user,&＃39;pwd&＃39;:dump_pwd,&＃39;db&＃39;:&＃39;information_schema&＃39;} 120. dump=mysqldump(conf);
121. dump.dump();
122. print "dump success"
load 脚本

1.&＃39;&＃39;&＃39;&＃39;&＃39;
2.Created on 2012-8-20
3.mysql dump to load
4.@author: tudou@b2c.xiaomi.com
5.&＃39;&＃39;&＃39;
6.import os,MySQLdb,multiprocessing 7.
8.mysql_bak=&＃39;/tmp/mysqlbak/2012-09-18-21-44-34&＃39; 9.mysql_base=&＃39;/opt/soft/mysql_5.5.25&＃39; 10.unix_socket=&＃39;/tmp/mysql.sock&＃39; 11.dump_user=&＃39;root&＃39; 12.dump_pwd=&＃39;123456&＃39; 13.dump_database={&＃39;test&＃39;:&＃39;test&＃39;}#dump_database={&＃39;test&＃39;:&＃39;test&＃39;,&＃39;mysql&＃39;:&＃39;mysql&＃39;} 14.
15.def start_process(): 16. print (&＃39;MySQLinput starting&＃39;, multiprocessing.current_process().name) 17.#input shcema 18.#load data 19.#input object 20.class mysqlinput(object): 21. def __init__(self,conf): 22. self.cOnf=conf 23.
24. def input(self): 25. os.system(&＃39;chmod 777 -R &＃39;+mysql_bak) 26. dirnames=os.listdir(mysql_bak)
27. for dirname in dirnames: 28. #print dirname 29. if dump_database.has_key(dirname): 30. self.inputschema(dirname) 31.
32. for dirname in dirnames: 33. if dump_database.has_key(dirname): 34. self.loadata(dirname) 35.
36. for dirname in dirnames: 37. if dump_database.has_key(dirname): 38. self.inputobject(dirname) 39.
40. def inputschema(self,dbconf): 41. print "gunzip -cd "+mysql_bak+"/"+dbconf+"/schema/schemainfo.gz | "+mysql_base+"/bin/mysql -u"+dump_user+" -p****** -S"+unix_socket 42. os.system("gunzip -cd "+mysql_bak+"/"+dbconf+"/schema/schemainfo.gz | "+mysql_base+"/bin/mysql -u"+dump_user+" -p"+dump_pwd+" -S"+unix_socket+" "+dbconf) 43.
44. def loadata(self,dbconf): 45. pool_size = multiprocessing.cpu_count()
46. pool = multiprocessing.Pool(processes=pool_size,initializer=start_process,)
47. filenames=os.listdir(mysql_bak+"/"+dbconf) 48. for filename in filenames: 49. filepath=mysql_bak+"/"+dbconf+"/"+filename 50. if os.path.isfile(filepath): 51. filecOnf={&＃39;dbname&＃39;:dbconf,&＃39;gzfile&＃39;:filepath,&＃39;filename&＃39;:filepath,&＃39;tablename&＃39;:filename[0:len(filename)-4]} 52. c=fileconf[&＃39;gzfile&＃39;] 53. if c[len(c)-3:len(c)]==&＃39;.gz&＃39;: 54. filecOnf={&＃39;dbname&＃39;:dbconf,&＃39;gzfile&＃39;:filepath,&＃39;filename&＃39;:filepath[0:len(filepath)-3],&＃39;tablename&＃39;:filename[0:len(filename)-7]} 55. pool.apply_async(self.mygunzip(fileconf)) 56.
57. pool.close() # no more tasks 58. pool.join() # wrap up current tasks 59.
60. def mygunzip(self,fileconf): 61. c=fileconf[&＃39;gzfile&＃39;] 62. if c[len(c)-3:len(c)]==&＃39;.gz&＃39;: 63. os.system("gunzip "+fileconf[&＃39;gzfile&＃39;]) 64.
65. self.loadpertable(fileconf) 66.
67. def loadpertable(self,fileconf): 68. sql="TRUNCATE \"`+fileconf[&＃39;dbname&＃39;]+"\`.\"`+fileconf[&＃39;tablename&＃39;]+"\`;LOAD DATA INFILE &＃39;"+fileconf[&＃39;filename&＃39;]+"&＃39; INTO TABLE \"`+fileconf[&＃39;dbname&＃39;]+"\`.\"`+fileconf[&＃39;tablename&＃39;]+"\`;" 69. print sql 70. os.system(mysql_base+"/bin/mysql -u"+dump_user+" -p"+dump_pwd+" -S"+unix_socket+" -e\""+sql+"\"") 71.
72. def inputobject(self,dbconf): 73. print "gunzip -cd "+mysql_bak+"/"+dbconf+"/schema/objectinfo.sql.gz | "+mysql_base+"/bin/mysql -u"+dump_user+" -p****** -S"+unix_socket 74. os.system("gunzip -cd "+mysql_bak+"/"+dbconf+"/schema/objectinfo.gz | "+mysql_base+"/bin/mysql -u"+dump_user+" -p"+dump_pwd+" -S"+unix_socket+" "+dbconf) 75.
76.if __name__ == &＃39;__main__&＃39;: 77. cOnf={&＃39;host&＃39;:&＃39;localhost&＃39;,&＃39;socket&＃39;:unix_socket,&＃39;user&＃39;:dump_user,&＃39;pwd&＃39;:dump_pwd,&＃39;db&＃39;:&＃39;information_schema&＃39;} 78. input=mysqlinput(conf);
79. input.input();
80. print "load success"