mysql meb物理备份脚本---新增命令执行结果实时输出

# coding:utf-8
# encoding=UTF-8
# !/usr/bin/python3
# encoding: utf-8
# filename: meb-mysql-backups.py
# author: gaohaixiang
# writetime:202210091630

import subprocess
import time
import os
import shutil
import re
import sys
import logging
import threading

"""
使用说明：
需要数据库对应版本的 mysqlbackup
运行脚本之前，需要主机只保留备份数据库的运行
恢复数据之前，需要清除被恢复数据库存放数据目录中内容
运行被恢复数据库之前需要授权数据存放目录

脚本运行需要的数据：
备份数据库的账密，数据备份目录，被恢复数据库的配置文件

脚本运行命令：
压缩备份
python3 meb-mysql-backups.py InputCompressedBackup >> /data/databak/mysqlbakup.log
全量备份
python3 meb-mysql-backups.py InputFullBackup >> /data/databak/mysqlbakup.log
增量备份
python3 meb-mysql-backups.py InputIncrementalBackupSecond >> /data/databak/mysqlbakup.log
压缩备份还原
python3 meb-mysql-backups.py InputCompressedBackupReduction >> /data/databak/mysqlbakup.log
全量备份还原
python3 meb-mysql-backups.py InputFullBackupReduction >> /data/databak/mysqlbakup.log
增量备份还原
python3 meb-mysql-backups.py InputIncrementalBackupReduction >> /data/databak/mysqlbakup.log
"""

_logger = logging.getLogger(__name__)
class TextReadLineThread(threading.Thread):
    def __init__(self, readline, callback, *args, **kargs):
        super().__init__(*args, **kargs)
        self.readline = readline
        self.callback = callback

    def run(self):
        for line in iter(self.readline, ""):
            if len(line) == 0:
                break
            self.callback(line)

def cmd_exec(command: str):
    process = subprocess.Popen(
        command,
        shell=True,
        text=True,
        stdout=subprocess.PIPE,
        stderr=subprocess.PIPE,
        )

    def log_warp(func):
        def _wrapper(line: str):
            return func("	" + line.strip())
        return _wrapper

    read_stdout = TextReadLineThread(process.stdout.readline, log_warp(_logger.info))
    read_stderr = TextReadLineThread(process.stderr.readline, log_warp(_logger.warning))
    read_stdout.start()
    read_stderr.start()

    read_stdout.join()
    read_stderr.join()
    ret = process.wait()

    if ret != 0:
        print(ret)

# 数据库信息获取,备份数据库的配置文件，socket文件
def MysqlInformationGet():
    commands = " ps -ef |grep mysql|grep pid-file"
    getoutput = subprocess.getoutput(commands)
    if getoutput:
        BackDefaultsFile = ""
        BackSocket = ""
        for lines in getoutput.split():
            if re.findall("defaults-file", lines):
                BackDefaultsFile = lines.split("=")[1]
            elif re.findall("socket", lines):
                BackSocket = lines.split("=")[1]
        return BackDefaultsFile, BackSocket
    else:
        print("无数据库进程")


# 目录结构获取
def DirStructureGet(dirname):
    dirnames = []
    if os.path.exists(dirname):
        dirnames = os.listdir(dirname)
    else:
        os.makedirs(dirname)
    dirnames.sort()
    return dirnames


# 实时时间格式获取
def TimestapGet():
    gettimestamp = time.strftime('%Y%m%d%H%M%S')
    return gettimestamp


# 压缩备份
def CompressedBackup(BackDefaultsFile, MysqlUser, MysqlPassword, BackSocket, CompressedDirName):
    commands = "mysqlbackup --defaults-file=%s  --user=%s --password=%s \
        --socket=%s --compress-level=1 --backup-dir=%s  backup" % (
        BackDefaultsFile, MysqlUser, MysqlPassword, BackSocket, CompressedDirName)
    cmd_exec(commands)


# 全量备份
def FullBackup(BackDefaultsFile, MysqlUser, MysqlPassword, BackSocket, FullDirName):
    commands = "mysqlbackup --defaults-file=%s  --user=%s --password=%s \
        --socket=%s --backup-dir=%s backup" % (BackDefaultsFile, MysqlUser, MysqlPassword, BackSocket, FullDirName)
    cmd_exec(commands)


# 增量备份，第一次
def IncrementalBackupFirst(BackDefaultsFile, MysqlUser, MysqlPassword, BackSocket, IncrementalBackupFirstDirName,
                           LastFullBackupDirName):
    commands = "mysqlbackup  --defaults-file=%s  --user=%s \
        --password=%s --socket=%s \
        --incremental --incremental-backup-dir=%s \
        --incremental-base=dir:%s  backup" % \
               (BackDefaultsFile, MysqlUser, MysqlPassword, BackSocket, IncrementalBackupFirstDirName,
                LastFullBackupDirName)
    cmd_exec(commands)


# 增量备份，第二次，及第 N 次
def IncrementalBackupSecond(BackDefaultsFile, MysqlUser, MysqlPassword, BackSocket, IncrementalBackupNDirName,
                            LastIncrementalBackupDirName):
    commands = "mysqlbackup  --defaults-file=%s  --user=%s \
        --password=%s --socket=%s  \
        --incremental --incremental-backup-dir=%s  \
        --incremental-base=dir:%s  backup" % \
               (BackDefaultsFile, MysqlUser, MysqlPassword, BackSocket, IncrementalBackupNDirName,
                LastIncrementalBackupDirName)
    cmd_exec(commands)


# 压缩备份还原
def CompressedBackupReduction(RdeuctionMysqlFile, LastCompressedBackupDirName):
    # 第一步:检测事务日志,并解压
    commands1 = "mysqlbackup --defaults-file=%s  --uncompress \
        --backup-dir=%s apply-log" % (RdeuctionMysqlFile, LastCompressedBackupDirName)
    cmd_exec(commands1)

    # 第二步:copy物理文件
    commands2 = "mysqlbackup --defaults-file=%s \
        --backup-dir=%s copy-back" % (RdeuctionMysqlFile, LastCompressedBackupDirName)
    cmd_exec(commands2)


# 全量备份还原
def FullBackupReduction(ff, RdeuctionMysqlFile, LastFullBackupDirName):
    # 第一步:检测事务日志
    commands1 = "mysqlbackup --defaults-file=%s  \
        --backup-dir=%s  apply-log" % (RdeuctionMysqlFile, LastFullBackupDirName)
    cmd_exec(commands1)

    # 第二步:copy物理文件
    commands2 = "mysqlbackup --defaults-file=%s \
        --backup-dir=%s  copy-back" % (RdeuctionMysqlFile, LastFullBackupDirName)
    cmd_exec(commands2)


# 增量备份还原,一次增量
def IncrementalBackupReductionOne(ff, RdeuctionMysqlFile, LastFullBackupDirName, FirstIncrementalBackupDirName):
    # 1. 全备检测匹配释放事务日志
    commands1 = "mysqlbackup --defaults-file=%s  \
        --backup-dir=%s apply-log" % (RdeuctionMysqlFile, LastFullBackupDirName)
    cmd_exec(commands1)

    # 2. 检测匹配释放第一次的增量备份
    commands2 = "   mysqlbackup  --backup-dir=%s  \
        --incremental-backup-dir=%s  apply-incremental-backup" % (LastFullBackupDirName, FirstIncrementalBackupDirName)
    cmd_exec(commands2)

    # 4. 最后进行物理文件复制
    commands4 = "mysqlbackup --defaults-file=%s  \
        --backup-dir=%s copy-back" % (RdeuctionMysqlFile, LastFullBackupDirName)
    cmd_exec(commands4)


# 增量备份还原,N次增量
def IncrementalBackupReductionN(ff, RdeuctionMysqlFile, LastFullBackupDirName, IncrementalDir,
                                IncrementalBackupDirNameList):
    # 1. 全备检测匹配释放事务日志
    commands1 = "mysqlbackup --defaults-file=%s  \
        --backup-dir=%s apply-log" % (RdeuctionMysqlFile, LastFullBackupDirName)
    cmd_exec(commands1)

    LenNumberIncrementalDir = len(IncrementalBackupDirNameList)
    for i in range(LenNumberIncrementalDir):
        if i == 0:
            FirstIncrementalBackupDirName = IncrementalDir + IncrementalBackupDirNameList[0]
            # 2. 检测匹配释放第一次的增量备份
            commands2 = "   mysqlbackup  --backup-dir=%s  \
                --incremental-backup-dir=%s  apply-incremental-backup" % (
                LastFullBackupDirName, FirstIncrementalBackupDirName)
            cmd_exec(commands2)
        else:
            NIncrementalBackupDirName = IncrementalDir + IncrementalBackupDirNameList[i]
            # 3.检测匹配释放第 N 次的增量备份
            commands3 = "  mysqlbackup  --backup-dir=%s  \
                --incremental-backup-dir=%s  apply-incremental-backup" % (
                LastFullBackupDirName, NIncrementalBackupDirName)
            cmd_exec(commands3)

    # 4. 最后进行物理文件复制
    commands4 = "mysqlbackup --defaults-file=%s  \
        --backup-dir=%s copy-back" % (RdeuctionMysqlFile, LastFullBackupDirName)
    cmd_exec(commands4)


"""压缩备份"""
def InputCompressedBackup(ff, BackDefaultsFile, MysqlUser, MysqlPassword, BackSocket, CompressedDir):
    ff.writelines("压缩备份\n")
    CompressedDirName = CompressedDir + gettimestamp
    DirStructureGet(CompressedDirName)  # 创建备份使用的目录
    CompressedBackup(BackDefaultsFile, MysqlUser, MysqlPassword, BackSocket, CompressedDirName)


"""全量备份"""
def InputFullBackup(ff, BackDefaultsFile, MysqlUser, MysqlPassword, BackSocket, FullDir):
    ff.writelines("全量备份\n")
    FullDirName = FullDir + gettimestamp
    DirStructureGet(FullDirName)  # 创建备份使用的目录
    FullBackup(BackDefaultsFile, MysqlUser, MysqlPassword, BackSocket, FullDirName)


"""增量备份"""
def InputIncrementalBackupSecond(ff, BackDefaultsFile, MysqlUser, MysqlPassword, BackSocket, FullDir, IncrementalDir):
    ff.writelines("增量备份\n")
    # 增量备份
    # 获取最后一次全量备份目录
    LastFullBackupDirName = ""
    LastFullBackupDirNameList = DirStructureGet(FullDir)
    if LastFullBackupDirNameList:
        if LastFullBackupDirNameList[-1] == "incremental":
            LastFullBackupDirName = FullDir + LastFullBackupDirNameList[-2]
        else:
            LastFullBackupDirName = FullDir + LastFullBackupDirNameList[-1]
    else:
        ff.writelines("无全量备份，请先全量备份，后再进行增量备份")
        print("无全量备份，请先全量备份，后再进行增量备份")
    # print(LastFullBackupDirName)
    if LastFullBackupDirName:
        # 判断增量备份是否是多次
        IncrementalBackupDirNameList = DirStructureGet(IncrementalDir)
        # print(IncrementalBackupDirNameList)
        # 最后一次增量备份
        if IncrementalBackupDirNameList:
            LastIncrementalBackupDirName = IncrementalDir + IncrementalBackupDirNameList[-1]
            # 第 N 次增量备份
            IncrementalBackupNDirName = IncrementalDir + gettimestamp
            DirStructureGet(IncrementalBackupNDirName)  # 创建备份使用的目录
            IncrementalBackupSecond(BackDefaultsFile, MysqlUser, MysqlPassword, BackSocket,
                                    IncrementalBackupNDirName,
                                    LastIncrementalBackupDirName)
        # 第一次增量备份
        else:
            IncrementalBackupFirstDirName = IncrementalDir + gettimestamp
            DirStructureGet(IncrementalBackupFirstDirName)  # 创建备份使用的目录
            IncrementalBackupFirst(BackDefaultsFile, MysqlUser, MysqlPassword, BackSocket,
                                   IncrementalBackupFirstDirName, LastFullBackupDirName)


"""压缩备份还原"""
def InputCompressedBackupReduction(ff, RdeuctionMysqlFile, CompressedDir):
    ff.writelines("压缩还原\n")
    LastCompressedBackupDirNameList = DirStructureGet(CompressedDir)
    if LastCompressedBackupDirNameList:
        LastCompressedBackupDirName = CompressedDir + LastCompressedBackupDirNameList[-1]
        CompressedBackupReduction(RdeuctionMysqlFile, LastCompressedBackupDirName)
    else:
        ff.writelines("无压缩备份，无法进行压缩备份还原")
        print("无压缩备份，无法进行压缩备份还原")


"""全量备份还原"""
def InputFullBackupReduction(ff, RdeuctionMysqlFile, FullDir):
    ff.writelines("全量还原\n")
    LastFullBackupDirNameList = DirStructureGet(FullDir)
    if LastFullBackupDirNameList and LastFullBackupDirNameList[-1] != "incremental":
        LastFullBackupDirName = FullDir + LastFullBackupDirNameList[-1]
        FullBackupReduction(ff, RdeuctionMysqlFile, LastFullBackupDirName)
    elif LastFullBackupDirNameList and LastFullBackupDirNameList[-1] == "incremental":
        LastFullBackupDirName = FullDir + LastFullBackupDirNameList[-2]
        FullBackupReduction(ff, RdeuctionMysqlFile, LastFullBackupDirName)
    else:
        ff.writelines("无全量备份，无法进行全量备份还原")
        print("无全量备份，无法进行全量备份还原")


"""增量备份还原"""
def InputIncrementalBackupReduction(ff, RdeuctionMysqlFile, FullDir, IncrementalDir):
    ff.writelines("增量还原\n")
    LastFullBackupDirName = ""
    LastFullBackupDirNameList = DirStructureGet(FullDir)
    if LastFullBackupDirNameList and LastFullBackupDirNameList[-1] != "incremental":
        ff.writelines("无增量备份，无法进行增量备份还原")
        print("无增量备份，无法进行增量备份还原")
    elif LastFullBackupDirNameList and LastFullBackupDirNameList[-1] == "incremental":
        LastFullBackupDirName = FullDir + LastFullBackupDirNameList[-2]
    else:
        ff.writelines("无全量备份，无法进行增量和全量备份还原")
        print("无全量备份，无法进行增量和全量备份还原")
    IncrementalBackupDirNameList = DirStructureGet(IncrementalDir)
    LenNumberIncrementalDir = len(IncrementalBackupDirNameList)
    if LenNumberIncrementalDir == 1:
        FirstIncrementalBackupDirName = IncrementalDir + IncrementalBackupDirNameList[-1]
        IncrementalBackupReductionOne(ff, RdeuctionMysqlFile, LastFullBackupDirName, FirstIncrementalBackupDirName)
    elif LenNumberIncrementalDir > 1:
        IncrementalBackupReductionN(ff, RdeuctionMysqlFile, LastFullBackupDirName, IncrementalDir,
                                    IncrementalBackupDirNameList)
    else:
        ff.writelines("增量备份目录无文件，不能进行增量备份还原")
        print("增量备份目录无文件，不能进行增量备份还原")

# 被恢复数据库 数据存储目录 处理
def DelDataDir(filename):
    basedir = ""
    datadir = ""

    # 获取配置文件中的 datadir目录
    ff = open(filename,"r",encoding="UTF8")
    ffs = ff.readlines()
    for lines in ffs:
        if re.findall("=",lines):
            lineLists = lines.strip("\n").split("=")
            if lineLists[0] == "basedir":
                basedir = lineLists[1]
            elif lineLists[0] == "datadir":
                datadir = lineLists[1]
    ff.close()

    # 若 datadir 目录存在，则删除，然后再重建授权
    if os.path.exists(datadir):
        shutil.rmtree(datadir)
    else:
        os.makedirs(datadir)

    return basedir,datadir

if __name__ == '__main__':
    # 获取现在的时间格式
    gettimestamp = TimestapGet()
    print(gettimestamp)

    # 压缩备份目录
    CompressedDir = '/data/databak/IPMIbackup/compress/'
    # 全量备份目录
    FullDir = '/data/databak/IPMIbackup/backup/'
    # 增量备份目录
    IncrementalDir = '/data/databak/IPMIbackup/backup/incremental/'

    # 创建备份目录
    DirStructureGet(CompressedDir)  # 创建压缩备份使用的目录
    DirStructureGet(FullDir)  # 创建全量备份使用的目录
    DirStructureGet(IncrementalDir)  # 创建增量备份使用的目录

    # 还原数据的配置文件
    RdeuctionMysqlFile = "/data/mysql8030/mysql.cnf"

    # 备份及还原日志文件
    LogFileName = "/data/databak/mysqlbakup.log"
    ff = open(LogFileName, "a+", encoding="UTF8")
    ff.writelines("#########" * 10)
    ff.writelines("\n"+gettimestamp + "\n")

    # 获取备份数据库信息
    BackDefaultsFile, BackSocket = MysqlInformationGet()
    MysqlUser = "root"
    MysqlPassword = "123456"

    if sys.argv[1] == "InputCompressedBackup":
        """压缩备份"""
        InputCompressedBackup(ff, BackDefaultsFile, MysqlUser, MysqlPassword, BackSocket, CompressedDir)
    elif sys.argv[1] == "InputFullBackup":
        """全量备份"""
        InputFullBackup(ff, BackDefaultsFile, MysqlUser, MysqlPassword, BackSocket, FullDir)
    elif sys.argv[1] == "InputIncrementalBackupSecond":
        """增量备份"""
        InputIncrementalBackupSecond(ff, BackDefaultsFile, MysqlUser, MysqlPassword, BackSocket, FullDir,
                                     IncrementalDir)
    elif sys.argv[1] == "InputCompressedBackupReduction":
        """压缩备份还原"""
        basedir,datadir = DelDataDir(RdeuctionMysqlFile)
        InputCompressedBackupReduction(ff, RdeuctionMysqlFile, CompressedDir)
        cmd_exec("chmod 755 -R %s %s; chown mysql.mysql -R %s %s;" % (basedir,datadir,basedir,datadir))
    elif sys.argv[1] == "InputFullBackupReduction":
        """全量备份还原"""
        basedir,datadir = DelDataDir(RdeuctionMysqlFile)
        InputFullBackupReduction(ff, RdeuctionMysqlFile, FullDir)
        cmd_exec("chmod 755 -R %s %s; chown mysql.mysql -R %s %s;" % (basedir,datadir,basedir,datadir))
    elif sys.argv[1] == "InputIncrementalBackupReduction":
        """增量备份还原"""
        basedir,datadir = DelDataDir(RdeuctionMysqlFile)
        InputIncrementalBackupReduction(ff, RdeuctionMysqlFile, FullDir, IncrementalDir)
        cmd_exec("chmod 755 -R %s %s; chown mysql.mysql -R %s %s;" % (basedir,datadir,basedir,datadir))
    else:
        ff.writelines("请输入正确的参数")
        print("请输入正确的参数")

    ff.close()