mysql增备

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！

 

转载于:https://www.cnblogs.com/changbo/p/7470325.html