mysql4.0做主從時主庫的備份腳本
mysql4.0是老版本了，但是有些早期使用的企業依然在用，在創建主從時特別是線上服務器創建主從時，保證數據的一致性是個大問題：比如創建完從庫同步時出現重復數據重復執行（雖然數據條數一致，但數據有可能會不一致）等。
在mysql5.0以上版本中，此時備份主庫只用在mysqldump時加上-F、master-data=2，single-transaction參數，從庫同步時導入備份，在取備份文件開頭的bin-log和pos位置進行同步即可，不會出現數據重復執行等問題，他能確保同步時的一致性。比較悲劇的是，本人所用的數據庫還沒有升級，是4.0的版本，經過測試，寫了一個專一用於4.0主從同步時主庫備份的腳本，原理就是模擬5.0以上的備份過程來做的。也可以用於5.0以上的版本，但是5.0以上的版本沒有必要這麼做。大家可以參考。
#!/usr/bin/env python
# -*- coding: utf-8 -*-
www.2cto.com
import os,sys,time,MySQLdb
import subprocess,threading
class mysql_dump():
def __init__(self):
self.dumpname = "/root/alldata%s.bz2" % time.strftime("%Y%m%d%H%M")
self.STAT_IP = "192.168.0.39"
self.logfile = "/root/mysql_dump.log"
self.user = "root"
self.passwd = "1q2w3e4r"
def log_w(self,text):
now = time.strftime("%Y-%m-%d %H:%M:%S")
tt = str(now) + "\t" + text + "\n"
f = open(self.logfile,'a+')
f.write(tt)
f.close()
def dump(self):
cmd = "/usr/local/mysql/bin/mysqldump -A -Q -e --add-drop-table --add-locks --extended-insert --quick --no-autocommit --single-transaction -u%s -p%s | bzip2 -2 > %s" % (self.user,self.passwd,self.dumpname)
print time.strftime("%Y-%m-%d %H:%M:%S")
text = "Start mysqldump,Please wait ..."
print text www.2cto.com
self.log_w(text)
a = subprocess.Popen(cmd,shell=True)
while 1:
b = subprocess.Popen.poll(a)
if b == 0:
text = "Mysqldump complete"
print text
self.log_w(text)
break
elif b is None:
print 'Mysqldump running'
time.sleep(30)
else:
print a.pid,'term'
break
self.rsync()
def rsync(self):
cmd = "rsync -az %s %s::asktao_db/db_back/" % (self.dumpname,self.STAT_IP)
text = "Start rsync to server(%s) ,Please wait ..." % self.STAT_IP
print text
self.log_w(text)
a = subprocess.Popen(cmd,shell=True)
while 1:
b = subprocess.Popen.poll(a)
if b == 0:
text = "Rsync complete"
print text www.2cto.com
self.log_w(text)
break
elif b is None:
print 'Rsync running'
time.sleep(30)
else:
print a.pid,'term'
break
def lock(self):
try:
conn = MySQLdb.connect(host = '127.0.0.1',user = 'root',passwd = '1q2w3e4r', charset='utf8', connect_timeout=5)
cursor = conn.cursor()
text = "flush tables with read lock"
print text www.2cto.com
self.log_w(text)
cursor.execute("flush tables with read lock")
text = "flush logs"
print text
self.log_w(text)
cursor.execute("flush logs")
d = threading.Thread(target=self.dump, args=())
d.start()
while 1:
if os.path.isfile(self.dumpname) and os.path.getsize(self.dumpname) > 0:
text = "UNLOCK TABLES"
print text
self.log_w(text)
cursor.execute("UNLOCK TABLES")
break
except MySQLdb.Error,e:
text = e.args
print text
self.log_w(text)
def work(self):
t = threading.Thread(target=self.lock, args=())
t.start()
if __name__ == "__main__":
boss = mysql_dump()
boss.work()
