mysql中 meta_data表 mysql meb

转载
墨香四溢 2024-05-30 08:55:27
文章标签 mysql中 meta_data表数据库 python linux sql 文章分类 MySQL 数据库
# coding:utf-8
# encoding=UTF-8
# !/usr/bin/python3
# encoding: utf-8
# filename: meb-mysql-backups.py
# author: gaohaixiang
# writetime:202208241705

import subprocess
import time
import os
import re
import sys

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

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

脚本运行命令：
压缩备份
python3 meb-mysql-backups.py InputCompressedBackup

全量备份
python3 meb-mysql-backups.py InputFullBackup

增量备份
python3 meb-mysql-backups.py InputIncrementalBackupSecond

压缩备份还原
python3 meb-mysql-backups.py InputCompressedBackupReduction

全量备份还原
python3 meb-mysql-backups.py InputFullBackupReduction

增量备份还原
python3 meb-mysql-backups.py InputIncrementalBackupReduction
"""


# 数据库信息获取,备份数据库的配置文件，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(ff, 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)
    getoutput = subprocess.getoutput(commands)
    ff.writelines(getoutput)


# 全量备份
def FullBackup(ff, 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)
    getoutput = subprocess.getoutput(commands)
    ff.writelines(getoutput)


# 增量备份，第一次
def IncrementalBackupFirst(ff, 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)
    getoutput = subprocess.getoutput(commands)
    ff.writelines(getoutput)


# 增量备份，第二次，及第 N 次
def IncrementalBackupSecond(ff, 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)
    getoutput = subprocess.getoutput(commands)
    ff.writelines(getoutput)


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

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


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

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


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

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

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


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

    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)
            getoutput2 = subprocess.getoutput(commands2)
            ff.writelines(getoutput2)
        else:
            NIncrementalBackupDirName = IncrementalDir + IncrementalBackupDirNameList[i]
            # 3.检测匹配释放第 N 次的增量备份
            commands3 = "  mysqlbackup  --backup-dir=%s  \
                --incremental-backup-dir=%s  apply-incremental-backup" % (
            LastFullBackupDirName, NIncrementalBackupDirName)
            getoutput3 = subprocess.getoutput(commands3)
            ff.writelines(getoutput3)

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


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


"""全量备份"""
def InputFullBackup(ff, BackDefaultsFile, MysqlUser, MysqlPassword, BackSocket, FullDir):
    ff.writelines("全量备份\n")
    FullDirName = FullDir + gettimestamp
    DirStructureGet(FullDirName)  # 创建备份使用的目录
    FullBackup(ff, 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(ff, BackDefaultsFile, MysqlUser, MysqlPassword, BackSocket,
                                    IncrementalBackupNDirName,
                                    LastIncrementalBackupDirName)
        # 第一次增量备份
        else:
            IncrementalBackupFirstDirName = IncrementalDir + gettimestamp
            DirStructureGet(IncrementalBackupFirstDirName)  # 创建备份使用的目录
            IncrementalBackupFirst(ff, BackDefaultsFile, MysqlUser, MysqlPassword, BackSocket,
                                   IncrementalBackupFirstDirName, LastFullBackupDirName)


"""压缩备份还原"""
def InputCompressedBackupReduction(ff, RdeuctionMysqlFile, CompressedDir):
    ff.writelines("压缩还原\n")
    LastCompressedBackupDirNameList = DirStructureGet(CompressedDir)
    if LastCompressedBackupDirNameList:
        LastCompressedBackupDirName = CompressedDir + LastCompressedBackupDirNameList[-1]
        CompressedBackupReduction(ff, 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("增量备份目录无文件，不能进行增量备份还原")


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

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

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

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

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

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

    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":
        """压缩备份还原"""
        InputCompressedBackupReduction(ff, RdeuctionMysqlFile,CompressedDir)
    elif sys.argv[1] == "InputFullBackupReduction":
        """全量备份还原"""
        InputFullBackupReduction(ff, RdeuctionMysqlFile,FullDir)
    elif sys.argv[1] == "InputIncrementalBackupReduction":
        """增量备份还原"""
        InputIncrementalBackupReduction(ff, RdeuctionMysqlFile, FullDir,IncrementalDir)
    else:
        ff.writelines("请输入正确的参数")
        print("请输入正确的参数")

    ff.close()
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。