#encoding=UTF-8
#!/usr/bin/python
# encoding: utf-8
#filename: mysql8-increment-backup.py
#author: gaohaixiang
#writetime:20210901
import os
import time
import subprocess
import re
import sys
"""
脚本使用示例:
将正在使用的数据库的配置文件复制一份为 /data/mysql8/my.cnf ,或将该文件目录更改为正在使用的配置文件,--defaults-file=/data/mysql8/my.cnf
创建用于备份的用户密码,更改参数中的用户密码和数据库端口号, --user='backuser' --password='123456' --port=3306 -S /data/mysql/mysql.sock
创建存放备份数据的目录/data/backup/,或更改该选项 DirName = "/data/backup/"
全量备份(若想再次执行全量备份,直接执行下面命令,增量备份会依据最新的全量备份来执行,因此无影响)
python3 mysql8-increment-backup.py TotalQuantityBackup
增量备份(可以备份多次)
python3 mysql8-increment-backup.py IncrementBackup
恢复数据(需要删除原先数据库存放数据目录里面的内容,即 my.cnf 文件中 datadir=/data/mysql 目录中的内容)
python3 mysql8-increment-backup.py RecoverData
"""
#说明
def Explain():
a = 1
"""
备份原则:
每周全量备份一次,每天增量备份一次
一次循环设置一个标签,全量备份使用一个标签后,增量备份每次也要使用该标签,以达到增量
和全量备份相匹配
shell的增量备份及备份还原如下:
#创建备份用户
CREATE USER 'backuser'@'localhost' IDENTIFIED BY '123456';
GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backuser'@'localhost';
GRANT SELECT ON performance_schema.log_status TO 'backuser'@'localhost';
FLUSH PRIVILEGES;
#全量备份
xtrabackup --defaults-file=/data/mysql8/my.cnf --user='backuser' --password='123456' --port=3306 -S /data/mysql/mysql.sock \
--backup --compress --compress-threads=8 --use-memory=4G --parallel=8 --target-dir=/data/backup/base
#增量备份
xtrabackup --defaults-file=/data/mysql8/my.cnf --user='backuser' --password='123456' --port=3306 -S /data/mysql/mysql.sock \
--backup --compress --compress-threads=8 --use-memory=4G --parallel=8 --target-dir=/data/backup/inc1 --incremental-basedir=/data/backup/base
xtrabackup --defaults-file=/data/mysql8/my.cnf --user='backuser' --password='123456' --port=3306 -S /data/mysql/mysql.sock \
--backup --compress --compress-threads=8 --use-memory=4G --parallel=8 --target-dir=/data/backup/inc2 --incremental-basedir=/data/backup/inc1
#解压缩备份
xtrabackup --defaults-file=/data/mysql8/my.cnf --use-memory=4G --decompress --parallel=8 --remove-original --target-dir=/data/backup/base
xtrabackup --defaults-file=/data/mysql8/my.cnf --use-memory=4G --decompress --parallel=8 --remove-original --target-dir=/data/backup/inc1
xtrabackup --defaults-file=/data/mysql8/my.cnf --use-memory=4G --decompress --parallel=8 --remove-original --target-dir=/data/backup/inc2
#准备备份
xtrabackup --defaults-file=/data/mysql8/my.cnf --prepare --use-memory=4G --parallel=8 --apply-log-only --target-dir=/data/backup/base
xtrabackup --defaults-file=/data/mysql8/my.cnf --prepare --use-memory=4G --parallel=8 --apply-log-only --target-dir=/data/backup/base --incremental-dir=/data/backup/inc1
xtrabackup --defaults-file=/data/mysql8/my.cnf --prepare --use-memory=4G --parallel=8 --target-dir=/data/backup/base --incremental-dir=/data/backup/inc2
#恢复数据
xtrabackup --defaults-file=/data/mysql8/my.cnf --copy-back --use-memory=4G --parallel=8 --target-dir=/data/backup/base
"""
# 标签获取
def GetLabel():
a = 1
"""
数据存放规则:
使用标签以及日期新建一个目录,用于存放数据,该数据包括一次全量备份和其他的增量备份,全量备份,及增量备份名称命名均按此规则
目录命名示例:202109011020
全量命名示例:base-202109011020--00--
增量命名示例:inc-202109011020--01--,inc-202109011020--02--
目录创建规则:
全量备份时创建 整体数据存储目录(202109011020) 和 全量数据存储目录(base-202109011020--00--)
增量备份时创建 增量数据存储目录(inc-202109011020--01--,inc-202109011020--02--)
"""
#时间串获取
def GetTimeStamp():
#print(time.strftime('%Y%m%d%H%M%S'))
#20210901152607
pass
#全量备份
def TotalQuantityBackup(DirName):
dirnames = os.listdir(DirName)
#print(dirnames)
#时间格式串
gettimestamp = time.strftime('%Y%m%d%H%M')
#创建数据总的存储目录
os.mkdir(DirName + gettimestamp)
#创建全量备份数据放置的目录
os.mkdir(DirName + gettimestamp + "/base-" + gettimestamp + "--00--")
#放置数据的总目录名称
DirNames = os.listdir(DirName)
LastBackupName = DirNames[-1]
#print(LastBackupName)
#全量备份的目录名称
IncTotalDirName = os.path.join(DirName,LastBackupName)
#print(IncTotalDirName)
TotalDirNames = os.listdir(IncTotalDirName)
#print(TotalDirNames)
#全量备份的目录名称,绝对路径
TotalDirNamesRoute = os.path.join(IncTotalDirName,TotalDirNames[0])
#print(TotalDirNamesRoute)
#执行全量备份命令
#全量备份的shell备份命名如下
#xtrabackup --defaults-file=/data/mysql8/my.cnf --user='backuser' --password='123456' --port=3306 -S /data/mysql/mysql.sock \
# --backup --compress --compress-threads=8 --use-memory=4G --parallel=8 --target-dir=/data/backup/base
#python调用shell命令
Command = "xtrabackup --defaults-file=/data/mysql8/my.cnf --user='backuser' --password='123456' --port=3306 -S /data/mysql/mysql.sock \
--backup --compress --compress-threads=8 --use-memory=4G --parallel=8 --target-dir=%s " % TotalDirNamesRoute
print(Command)
subprocess.call(Command, shell=True)
#增量备份
def IncrementBackup(DirName):
#时间格式串
gettimestamp = time.strftime('%Y%m%d%H%M')
#print(DirName)
DirNames = os.listdir(DirName)
#最近的备份数据总的目录名称为
LastBackupName = DirNames[-1]
#print(LastBackupName)
#最近的完全备份数据的目录名称为
IncTotalDirName = os.path.join(DirName,LastBackupName)
#print(IncTotalDirName)
#全量备份目录的名称
TotalDirNames = os.listdir(IncTotalDirName)
#print(TotalDirNames)
#print(len(TotalDirNames))
if len(TotalDirNames) >= 1:
#前一份目录名称
TotalDirname = TotalDirNames[-1]
#print(TotalDirname)
#绝对路径
TotalDirnameRoute = os.path.join(IncTotalDirName,TotalDirname)
#print(TotalDirnameRoute)
#现需要使用增量备份的目录命名及绝对路径
#目录名称,inc-202109011020--01--
Dirname = TotalDirname.split("--")[1]
#print(Dirname)
Dirnames = "inc-" + gettimestamp + "--0" + str(int(Dirname)+1) + "--"
#print(Dirnames)
#绝对路径
DirnamesRoute = os.path.join(IncTotalDirName,Dirnames)
#print(DirnamesRoute)
# 执行增量备份命令
# 全量备份的shell备份命名如下
"""#增量备份
xtrabackup --defaults-file=/data/mysql8/my.cnf --user='backuser' --password='123456' --port=3306 -S /data/mysql/mysql.sock \
--backup --compress --compress-threads=8 --use-memory=4G --parallel=8 --target-dir=/data/backup/inc1 --incremental-basedir=/data/backup/base
xtrabackup --defaults-file=/data/mysql8/my.cnf --user='backuser' --password='123456' --port=3306 -S /data/mysql/mysql.sock \
--backup --compress --compress-threads=8 --use-memory=4G --parallel=8 --target-dir=/data/backup/inc2 --incremental-basedir=/data/backup/inc1"""
# python调用shell命令
Command = "xtrabackup --defaults-file=/data/mysql8/my.cnf --user='backuser' --password='123456' --port=3306 -S /data/mysql/mysql.sock \
--backup --compress --compress-threads=8 --use-memory=4G --parallel=8 --target-dir=%s --incremental-basedir=%s " % (DirnamesRoute,TotalDirnameRoute)
print(Command)
subprocess.call(Command, shell=True)
else:
print("没有做全量备份,请先做全量备份")
#解压备份
def DecompressionBackup(DirName):
a = 1
"""#解压缩备份
xtrabackup --defaults-file=/data/mysql8/my.cnf --use-memory=4G --decompress --parallel=8 --remove-original --target-dir=/data/backup/base
xtrabackup --defaults-file=/data/mysql8/my.cnf --use-memory=4G --decompress --parallel=8 --remove-original --target-dir=/data/backup/inc1
xtrabackup --defaults-file=/data/mysql8/my.cnf --use-memory=4G --decompress --parallel=8 --remove-original --target-dir=/data/backup/inc2
"""
#DirName = "D:\\1Work\\pycharm\\python\\XML\\"
#放置数据的总目录名称
DirNames = os.listdir(DirName)
LastBackupName = DirNames[-1]
#print(LastBackupName)
#全量备份的目录名称
IncTotalDirName = os.path.join(DirName,LastBackupName)
#print(IncTotalDirName)
TotalDirNameList = os.listdir(IncTotalDirName)
#print(TotalDirNameList)
for TotalDirName in TotalDirNameList:
# TotalDirNames = os.listdir(IncTotalDirName)[0]
# print(TotalDirNames)
#各个目录名称,绝对路径
TotalDirNamesRoute = os.path.join(IncTotalDirName,TotalDirName)
#print(TotalDirNamesRoute)
# python调用shell命令
Command = "xtrabackup --defaults-file=/data/mysql8/my.cnf --use-memory=4G --decompress --parallel=8 --remove-original --target-dir=%s " % TotalDirNamesRoute
print(Command)
subprocess.call(Command, shell=True)
#准备备份
def GetReadyBackup(DirName):
a = 1
"""#准备备份
xtrabackup --defaults-file=/data/mysql8/my.cnf --prepare --use-memory=4G --parallel=8 --apply-log-only --target-dir=/data/backup/base
xtrabackup --defaults-file=/data/mysql8/my.cnf --prepare --use-memory=4G --parallel=8 --apply-log-only --target-dir=/data/backup/base --incremental-dir=/data/backup/inc1
xtrabackup --defaults-file=/data/mysql8/my.cnf --prepare --use-memory=4G --parallel=8 --target-dir=/data/backup/base --incremental-dir=/data/backup/inc2
"""
#DirName = "D:\\1Work\\pycharm\\python\\XML\\"
#放置数据的总目录名称
DirNames = os.listdir(DirName)
LastBackupName = DirNames[-1]
#print(LastBackupName)
#全量备份的目录名称
IncTotalDirName = os.path.join(DirName,LastBackupName)
#print(IncTotalDirName)
TotalDirNameList = os.listdir(IncTotalDirName)
#print(TotalDirNameList)
if len(TotalDirNameList) == 1:
TotalDirNames = os.listdir(IncTotalDirName)[0]
#print(TotalDirNames)
# python调用shell命令
Command = "xtrabackup --defaults-file=/data/mysql8/my.cnf --prepare --use-memory=4G --parallel=8 --apply-log-only --target-dir=%s " % os.path.join(IncTotalDirName, TotalDirNames)
print(Command)
subprocess.call(Command, shell=True)
elif len(TotalDirNameList) > 1:
TotalDirNames = os.listdir(IncTotalDirName)[0]
#print(TotalDirNames)
# python调用shell命令
Command = "xtrabackup --defaults-file=/data/mysql8/my.cnf --prepare --use-memory=4G --parallel=8 --apply-log-only --target-dir=%s " % os.path.join(IncTotalDirName, TotalDirNames)
print(Command)
subprocess.call(Command, shell=True)
for TotalDirName in TotalDirNameList:
# TotalDirNames = os.listdir(IncTotalDirName)[0]
# print(TotalDirNames)
# 各个目录名称,绝对路径
TotalDirNamesRoute = os.path.join(IncTotalDirName, TotalDirName)
#print(TotalDirNamesRoute)
if re.findall("base",TotalDirName):
pass
else:
TotalDirNameIndes = TotalDirNameList.index(TotalDirName)
# python调用shell命令
Command = "xtrabackup --defaults-file=/data/mysql8/my.cnf --prepare --use-memory=4G --parallel=8 --apply-log-only --target-dir=%s --incremental-dir=%s " % (os.path.join(IncTotalDirName,TotalDirNameList[TotalDirNameIndes - 1]),TotalDirNamesRoute)
print(Command)
subprocess.call(Command, shell=True)
else:
print("备份错误")
#恢复数据
def RecoverData(DirName):
a = 1
"""#恢复数据
xtrabackup --defaults-file=/data/mysql8/my.cnf --copy-back --use-memory=4G --parallel=8 --target-dir=/data/backup/base
"""
#DirName = "D:\\1Work\\pycharm\\python\\XML\\"
#放置数据的总目录名称
DirNames = os.listdir(DirName)
LastBackupName = DirNames[-1]
#print(LastBackupName)
#全量备份的目录名称
IncTotalDirName = os.path.join(DirName,LastBackupName)
#print(IncTotalDirName)
TotalDirNames = os.listdir(IncTotalDirName)[0]
#print(TotalDirNames)
#全量备份的目录名称,绝对路径
TotalDirNamesRoute = os.path.join(IncTotalDirName,TotalDirNames)
#print(TotalDirNamesRoute)
#python调用shell命令
Command = "xtrabackup --defaults-file=/data/mysql8/my.cnf --copy-back --use-memory=4G --parallel=8 --target-dir=%s " % TotalDirNamesRoute
print(Command)
subprocess.call(Command, shell=True)
if __name__ == '__main__':
DirName = "/data/backup/"
#DirName = "D:\\1Work\\pycharm\\python\\XML\\202109011020"
if sys.argv[1] == "TotalQuantityBackup":
# 全量备份
TotalQuantityBackup(DirName)
elif sys.argv[1] == "IncrementBackup":
# 增量备份
IncrementBackup(DirName)
#将数据库的存放数据的目录清除
elif sys.argv[1] == "RecoverData":
# 解压备份
DecompressionBackup(DirName)
# 准备备份
GetReadyBackup(DirName)
# 恢复数据
RecoverData(DirName)
else:
print("请输入正确的参数")
mysql8 增量备份 python3脚本
最新推荐文章于 2024-07-19 19:44:57 发布
本文介绍了一个Python脚本,用于实现MySQL数据库的全量和增量备份,以及数据恢复。脚本使用xtrabackup工具,支持每周全量备份一次,每日增量备份一次,并详细解释了备份和恢复的流程。用户需配置数据库连接信息和备份目录。
摘要由CSDN通过智能技术生成