基于Python和Xtrbackup的自动化备份与还原实现！

 xtrabackup是一个MySQL备份还原的常用工具，实际使用过程应该都是shell或者Python封装的自动化脚本，尤其是备份。

对还原来说，对于基于完整和增量备份的还原，还原差异备份需要指定增量备份等等一系列容易出错的手工操作，以及binlog的还原等，如果纯手工操作的话非常麻烦。

即便是你记性非常好，对xtrabackup非常熟悉，纯手工操作的话，非常容易出错，其实也上网找过，还原没有发现太好用的自动化还原脚本。

Python资源共享群：484031800

于是就自己用Python封装了xtrabackup备份和还原的过程，可以做到自动化备份，基于时间点的自动化还原等等。

需要对xtrabackup有一定的了解，包括流式备份，压缩备份，Xtrabackup还原，mysqlbinlog还原等等。

备份

1，基于xtrabackup的流式压缩备份。

2，周六/或者任意时间的第一次备份为完整备份，其他时间为基于上一次备份的增量备份。

3，将备份开始时间，结束时间，备份路径等信息写入一个日志文件，方便后续自动化还原的时候解析。

效果如下：不管是什么时候，第一次必须为完整备份，然后根据上述规则，继续执行备份的话为基于最新一次备份的增量备份，每备份完成后生成修改备份日志列表信息。

 

 

 

 

 

实现：

# -*- coding: utf-8 -*-

import os

import time

import datetime

import sys

import socket

import shutil

import logging

logging.basicConfig(level=logging.INFO

#handlers={logging.FileHandler(filename='backup_log_info.log', mode='a', encoding='utf-8')}

)

host = "127.0.0.1"

port = "7000"

user = "root"

password = "root"

cnf_file = "/usr/local/mysql57_data/mysql7000/etc/my.cnf"

backup_dir = "/usr/local/backupdata"

backupfilelist = os.path.join(backup_dir,"backupfilelist.log")

backup_keep_days = 15

#获取备份类型，周六进行完备，平时增量备份，如果没有全备，执行完整备份

def get_backup_type():

backup_type = None

if os.path.exists(backupfilelist):

with open(backupfilelist, 'r') as f:

lines = f.readlines()

if(lines):

last_line = lines[-1] #get last backup name

if(last_line):

if(time.localtime().tm_wday==6):

backup_type = "full"

else:

backup_type = "incr"

else:

backup_type = "full"

else:

backup_type = "full"

else:

#full backup when first backup

open(backupfilelist, "a").close()

backup_type = "full"

return backup_type

#获取最后一次备份信息

def get_last_backup():

last_backup = None

if os.path.exists(backupfilelist):

with open(backupfilelist, 'r') as f:

lines = f.readlines()

last_line = lines[-1] # get last backup name

if (last_line):

last_backup = os.path.join(backup_dir, last_line.split("|")[-1])

return last_backup.replace("\n","")

#探测实例端口号

def get_mysqlservice_status():

mysql_stat = 0

s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)

result = s.connect_ex((host, int(port)))

#port os open

if (result == 0):

mysql_stat = 1

return mysql_stat

#清理过期的历史备份信息

def clean_expired_file():

for backup_name in os.listdir(backup_dir):

if os.path.isdir(backup_name):

bak_datetime = datetime.datetime.strptime(backup_name.replace("_full","").replace("_incr",""), '%Y%m%d%H%M%S')

if(bak_datetime<datetime.datetime.now() - datetime.timedelta(days=backup_keep_days)):

shutil.rmtree(os.path.join(backup_dir, backup_name))

#完整备份

def full_backup(backup_file_name):

os.system("[ ! -d {0}/{1} ] && mkdir -p {0}/{1}".format(backup_dir,backup_file_name))

logfile = os.path.join(backup_dir, "{0}/{1}/backuplog.log".format(backup_dir,backup_file_name))

backup_commond = ''' innobackupex --defaults-file={0} --no-lock {1}/{6} --user={2} --password={3} --host="{4}" --port={5} --tmpdir={1}/{6} --stream=xbstream --compress --compress-threads=8 --parallel=4 --extra-lsndir={1}/{6} > {1}/{6}/{6}.xbstream 2>{7} '''.\

format(cnf_file,backup_dir,user,password,host,port,backup_file_name,logfile)

execute_result = os.system(backup_commond)

return execute_result

#增量备份

def incr_backup(backup_file_name):

os.system("[ ! -d {0}/{1} ] && mkdir -p {0}/{1}".format(backup_dir, backup_file_name))

current_backup_dir = "{0}/{1}".format(backup_dir, backup_file_name)

logfile = os.path.join(backup_dir, "{0}/{1}/backuplog.log".format(backup_dir, backup_file_name))

#增量备份基于上一个增量/完整备份

incremental_bas