mysql每隔5分钟增备一次
1,逻辑示意图
2,目录结构图
3,producer
#!/usr/local/bin/python3 # -*- coding: UTF-8 -*- # ==================================================== # Author: changbo - 541330702@qq.com # Last modified: 2017-9-1 # Filename: mysqlproduct.py # Description: backup mysql files,base percona xtrabackup # http://www.cnblogs.com/changbo # ==================================================== import stomp import time import threading import logging logging.basicConfig(level=logging.DEBUG, format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s', datefmt='%a, %d %b %Y %H:%M:%S', filename='product.log', filemode='a') def product(timestamp): conn = stomp.Connection10([('127.0.0.1', 61613)]) conn.start() conn.connect() # 如果时间等于0030则传送一个‘bakold’,否则传‘startjob’ if timestamp == '0030': conn.send('SampleQueue', 'bakold') else: conn.send('SampleQueue', 'startjob') # conn.disconnect() def execCommond(): count = 0 while True: nowtime = time.strftime("%H%M", time.localtime()) if nowtime == '0030': product(nowtime) count -= 5 logging.debug('bak time: ' + nowtime) del nowtime time.sleep(60) elif count == 5: product(nowtime) count -= 5 logging.debug('send startjob time: ' + nowtime) del nowtime else: count += 1 logging.debug('sleep time: ' + nowtime) del nowtime time.sleep(60) if __name__ == '__main__': t = threading.Thread(target=execCommond()) t.start() t.join()
4,consumer
#!/usr/local/bin/python3 # -*- coding: UTF-8 -*- # ==================================================== # Author: changbo - 541330702@qq.com # Last modified: 2017-9-1 # Filename: mysqlconsumer.py # Description: backup mysql files,base percona xtrabackup # http://www.cnblogs.com/changbo # ==================================================== import stomp import mysqlincrement import threading import os import time import logging logging.basicConfig(level=logging.DEBUG, format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s', datefmt='%a, %d %b %Y %H:%M:%S', filename='consumer.log', filemode='a') class SampleListener(object): def on_message(self, headers, message): nowday = time.strftime("%Y%m%d", time.localtime()) basedir = '/home/yunwei/dbbakup/' mysqlbakdir = basedir + nowday logging.debug(message) try: if message == 'startjob': if not os.path.exists(mysqlbakdir): mysqlincrement.createDir() logging.debug("begin to create") else: logging.debug("begin to incre") mysqlincrement.incremBak() elif message == 'bakold': mysqlincrement.mvBak() logging.debug('begin to mvbak') else: pass except Exception as e: logging.debug(e) pass def resiveMessage(): conn = stomp.Connection10([('127.0.0.1', 61613)]) conn.set_listener('SampleListener', SampleListener()) conn.start() conn.connect() time.sleep(10) conn.subscribe('SampleQueue') conn.disconnect() if __name__ == '__main__': while True: t = threading.Thread(target=resiveMessage) t.start() t.join()
5,mysql增备脚本
#!/usr/local/bin/python3 # -*- coding: UTF-8 -*- # ==================================================== # Author: changbo - 541330702@qq.com # Last modified: 2017-9-3 # Filename: mysqlincrement.py # Description: backup mysql files,base percona xtrabackup # http://www.cnblogs.com/changbo # ==================================================== import time import datetime import os import subprocess import logging logging.basicConfig(level=logging.DEBUG, format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s', datefmt='%a, %d %b %Y %H:%M:%S', filename='backup.log', filemode='a') # 获取昨日备份文件目录 def getYesterday(): today = datetime.date.today() oneday = datetime.timedelta(days=1) yesterday = str((today - oneday)).replace('-', '') return yesterday def createDir(): nowday = time.strftime("%Y%m%d", time.localtime()) basedir = '/home/yunwei/dbbakup/' mysqlbakdir = basedir + nowday perfectdir = mysqlbakdir + '/perfectbak' incremdir = mysqlbakdir + "/incrembak" nowtime = time.strftime("%H%M", time.localtime()) childdir = "%s/%s" % (incremdir, nowtime) # 创建备份根目录 if not os.path.exists(basedir): os.mkdir(basedir) # 创建日备份目录 if not os.path.exists(mysqlbakdir): os.mkdir(mysqlbakdir) # 创建全备目录并执行全备 if not os.path.exists(perfectdir): os.mkdir(perfectdir) command1 = '/usr/bin/innobackupex --defaults-file=/usr/my.cnf --no-timestamp %s' % perfectdir os.system(command1) # 创建增备目录 if not os.path.exists(incremdir): os.mkdir(incremdir) # 创建分备份目录并增备 if not os.path.exists(childdir): os.mkdir(childdir) command2 = '/usr/bin/innobackupex --defaults-file=/usr/my.cnf --no-timestamp --incremental-basedir=%s --incremental %s' % ( perfectdir, childdir) os.system(command2) def incremBak(): nowday = time.strftime("%Y%m%d", time.localtime()) basedir = '/home/yunwei/dbbakup/' mysqlbakdir = basedir + nowday perfectdir = mysqlbakdir + '/perfectbak' incremdir = mysqlbakdir + "/incrembak" nowtime = time.strftime("%H%M", time.localtime()) childdir = "%s/%s" % (incremdir, nowtime) # 获取最后被创建的文件夹目录名 filename = (((subprocess.Popen("ls -l " + incremdir + "| head -2 | tail -1 | awk '{print $9}'", shell=True, stdout=subprocess.PIPE)).stdout.read()).decode()).strip() #time.sleep(120) # 创建增备目录 os.mkdir(childdir) command3 = '/usr/bin/innobackupex --defaults-file=/usr/my.cnf --no-timestamp --incremental-basedir=%s/%s --incremental %s' % (incremdir, filename, childdir) os.system(command3) def mvBak(): nowday = time.strftime("%Y%m%d", time.localtime()) basedir = '/home/yunwei/dbbakup/' mysqlbakdir = basedir + nowday filetime = getYesterday() # 压缩昨天备份目录 command4 = "/usr/bin/tar czf /home/yunwei/mysqlbak/hkmysqlbak%s.tar.gz %s%s" % (filetime, basedir, filetime) logging.debug(os.system(command4)) # 移除昨天备份目录 command5 = "mv -f %s%s /tmp/trash" % (basedir, filetime) logging.debug(os.system(command5)) if __name__ == '__main__': nowday = time.strftime("%Y%m%d", time.localtime()) basedir = '/home/yunwei/dbbakup/' mysqlbakdir = basedir + nowday #if not os.path.exists(mysqlbakdir): # createDir() #else: # incremBak() mvBak()
python新手,欢迎吐槽
END!