Home >Database >Mysql Tutorial >mysql4.0做主从时主库的备份脚本_MySQL
bitsCN.com
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 -*- 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 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 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 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() bitsCN.com