共计 17766 个字符，预计需要花费 45 分钟才能阅读完成。
xtrabackup 是一个 MySQL 备份还原的常用工具，实际使用过程应该都是 shell 或者 Python 封装的自动化脚本，尤其是备份。
对还原来说，对于基于完整和增量备份的还原，还原差异备份需要指定增量备份等等一系列容易出错的手工操作，以及 binlog 的还原等，如果纯手工操作的话非常麻烦。
即便是你记性非常好，对 xtrabackup 非常熟悉，纯手工操作的话，非常容易出错，其实也上网找过，还原没有发现太好用的自动化还原脚本。
于是就自己用 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_basedir = get_last_backup()
backup_commond = ”’innobackupex –defaults-file={0} –no-lock {6} –user={2} –password={3} –host={4} –port={5} –stream=xbstream –tmpdir={6} –compress –compress-threads=8 –parallel=4 –extra-lsndir={6} –incremental –incremental-basedir={7} 2> {8} > {6}/{9}.xbstream ”’\
.format(cnf_file,backup_dir,user,password,host,port,current_backup_dir,incremental_basedir,logfile,backup_file_name)
# print(backup_commond)
execute_result = os.system(backup_commond)
return execute_result
# 刷新 binlog，意义不大，原本计划在完整备份之后执行一个 binlog 的切换，暂时弃用
def flush_log():
flush_log_commond = ”’ mysql -h${0} -u${1} – p${2} -P${1} mysql – e”flush logs” ”’.format(user,password,host,port)
os.system(flush_log_commond)
if __name__ == ‘__main__’:
mysql_stat = get_mysqlservice_status()
backup_type = get_backup_type()
if mysql_stat <= 0 :
logging.info(“mysql instance is inactive,backup exit”)
sys.exit(1)
try:
start_time = datetime.datetime.now().strftime(‘%Y%m%d%_H%M%S’)
logging.info(datetime.datetime.now().strftime(‘%Y-%m-%d %H:%M:%S’)+”——–start backup”)
#flush_log()
backup_file_name = start_time
execute_result = None
if(backup_type == “full”):
backup_file_name = backup_file_name+”_full”
logging.info(“execute full backup……”)
execute_result = full_backup(backup_file_name)
if (execute_result == 0):
logging.info(datetime.datetime.now().strftime(‘%Y-%m-%d %H:%M:%S’) + “——–begin cleanup history backup”)
logging.info(“execute cleanup backup history……”)
clean_expired_file()
logging.info(datetime.datetime.now().strftime(‘%Y-%m-%d %H:%M:%S’) + “——–finsh cleanup history backup”)
else:
backup_file_name = backup_file_name + “_incr”
logging.info(“execute incr backup……”)
execute_result = incr_backup(backup_file_name)
if(execute_result==0):
finish_time = datetime.datetime.now().strftime(‘%Y%m%d%_H%M%S’)
backup_info = start_time+”|”+finish_time+”|”+start_time+ “_” + backup_type
with open(backupfilelist, ‘a+’) as f:
f.write(backup_info + ‘\n’)
logging.info(datetime.datetime.now().strftime(‘%Y-%m-%d %H:%M:%S’)+”——–finish backup”)
else:
logging.info(datetime.datetime.now().strftime(‘%Y-%m-%d %H:%M:%S’) + “——–xtrabackup failed.please check log”)
except:
raise
sys.exit(1)
还原
说直白一点，以这里的基于时间点或者是 position 的还原，就是一个不断找文件的过程，
1，首先任何还原，都需要一个创建于还原点前的完整备份。
2，基于上述完整备份，利用还原的时间点与 xtrbackup 的备份日志去做对比来获取所需的增量备份（0 个或者 1 个或者多个）。
3，基于上面两步找到的（完整 + 增量）备份，利用最后一个备份的 position，用于第一个 binlog 还原时指定 start-position，
同时利用 binlog 的最后修改时间与还原的时间点对比，决定使用那些 binlog，同时最后一个 binlog 要指定 stop-datime= 还原的时间点
1，如何还原时间点的最新的一个完整备份
备份的时候维护一个备份信息，如下, 这里是 backfilelist.log，包括备份开始时间，结束时间，备份类型，备份路径等。
可以根据备份开始时间，找到第一个早于还原时间点的完整备份
2，如果找到恢复所需要的差异备份
同 1，从完整备份开始，依次向后找各个增量备份，直到最后一个早于还原时间点的差异备份，可能有一个或者多个
3，如何找到差异备份之后，需要哪些 binlog
基于 binlog 文件自身的最后修改时间属性信息，从 2 中找到的最后一个差异备份的时间，开始向后依次找 binlog，可能有一个或者多个
自动还原 demo
如下是一个基于时间点来还原数据库的 demo，没写入两条数据，执行一次备份（上述备份会自动区分完整备份或者差异备份）
三次备份之后，继续写两条数据，flush logs, 然后继续分两次分别写两条数据，目的是将数据分散到不同的 binlog 中，最后删除全部数据
然后基于删除数据之前的时间点来自动生成还原数据库的 shell，执行 shell 即可达到还原数据库的目的。
如下执行基于时间点的 rextrabackup.py 文件之后，时间点为 ”2019-08-01 18:50:59″，也就是发生删除操作的前一个时间点，来生成的还原信息。
其实只需要重定向到一个 shell 文件中，执行 shell 文件即可自动化还原，或者直接在 python 脚本中执行这些命令，即可自动化完成还原操作。
这里为了显示，打印了出来。
可以发现，基于时间点的还原，找到的文件是预期的：
1 个完整备份，2 个增量备份，2 个 binlog 日志中的一部分数据，
其中 binlog 日志还原的 start-position 成功地衔接到最后一个增量备份的 position，同时最后一个 binlog 日志的还原停留在指定的时间点。
自动生成的 shell 还原代码
################uncompress backup file###################
innobackupex –apply-log –redo-only /temp/restoretmp/20190801184134_full
innobackupex –apply-log –redo-only /temp/restoretmp/20190801184134_full –incremental-dir=/temp/restoretmp/20190801184335_inc
innobackupex –apply-log –redo-only /temp/restoretmp/20190801184134_full –incremental-dir=/temp/restoretmp/20190801184518_inc
innobackupex –apply-log /temp/restoretmp/20190801184134_full
################stop mysql service###################
systemctl stop mysqld_7000
####################backup current database file###########################
mv /usr/local/mysql57_data/mysql7000/data /usr/local/mysql57_data/mysql7000/data_20190801185855
mkdir /usr/local/mysql57_data/mysql7000/data
chown -R mysql.mysql /usr/local/mysql57_data/mysql7000/data
################restore backup data###################
innobackupex –defaults-file=/usr/local/mysql57_data/mysql7000/etc/my.cnf –copy-back –rsync /temp/restoretmp/20190801184134_full
chown -R mysql.mysql /usr/local/mysql57_data/mysql7000/data
################stop mysql service###################
systemctl start mysqld_7000
################restore data from binlog###################
cd /usr/local/mysql57_data/mysql7000/log/bin_log
mysqlbinlog mysql_bin_1300.000001 –skip-gtids=true –start-position=982 | mysql mysql -h127.0.0.1 -uroot -proot -P7000
mysqlbinlog mysql_bin_1300.000002 –skip-gtids=true –stop-datetime=”2019-08-01 18:50:59″ | mysql -h127.0.0.1 -uroot -proot -P7000
日志信息
实现
# -*- coding: utf-8 -*-
import os
import time
import datetime
import sys
import socket
import logging
logging.basicConfig(level=logging.INFO
#handlers={logging.FileHandler(filename=’restore_log_info.log’, mode=’a’, encoding=’utf-8′)}
)
host = “127.0.0.1”
port = “7000”
user = “root”
password = “root”
instance_name = “mysqld_7000”
stop_at = “2019-08-01 18:50:59”
cnf_file = “/usr/local/mysql57_data/mysql7000/etc/my.cnf”
backup_dir = “/usr/local/backupdata/”
dest_dir = “/temp/restoretmp/”
xtrabackuplog_name = “backuplog.log”
backupfilelist = os.path.join(backup_dir,”backupfilelist.log”)
# 根据 key 值，获取 MySQL 配置文件中的 value
def get_config_value(key):
value = None
if not key:
return value
if os.path.exists(cnf_file):
with open(cnf_file, ‘r’) as f:
for line in f:
if (line.split(“=”)[0]):
if(line[0:1]!=”#” and line[0:1]!=”[“):
if (key==line.split(“=”)[0].strip()):
value =line.split(“=”)[1].strip()
return value
def stop_mysql_service():
print(“################stop mysql service###################”)
print(“systemctl stop {}”.format(instance_name))
def start_mysql_service():
print(“################stop mysql service###################”)
print(“systemctl start {0}”.format(instance_name))
# 返回备份日志中的最新的一个早于 stop_at 时间的完整备份，以及其后面的增量备份
def get_restorefile_list():
list_backup = []
list_restore_file = []
if os.path.exists(backupfilelist):
with open(backupfilelist, ‘r’) as f:
lines = f.readlines()
for line in lines:
list_backup.append(line.replace(“\n”,””))
if (list_backup):
for i in range(len(list_backup) – 1, -1, -1):
list_restore_file.append(list_backup[i])
backup_name = list_backup[i].split(“|”)[2]
if “full” in backup_name:
full_backup_time = list_backup[i].split(“|”)[1]
if(stop_at<full_backup_time):
break
else:
list_restore_file = None
#restore file in the list_restore_log
list_restore_file.reverse()
return list_restore_file
# 解压缩需要还原的备份文件，包括一个完整备份以及 N 个增量备份（N>=0）
def uncompress_backup_file():
print(“################uncompress backup file###################”)
list_restore_backup = get_restorefile_list()
#如果没有生成时间早于 stop_at 的完整备份，无法恢复，退出
if not list_restore_backup:
raise(“There is no backup that can be restored”)
exit(1)
for restore_log in list_restore_backup:
#解压备份文件
backup_name = restore_log.split(“|”)[2]
backup_path = restore_log.split(“|”)[2]
backup_full_name = os.path.join(backup_dir,backup_path,backup_name)
backup_path = os.path.join(backup_dir,restore_log.split(“|”)[-1])
#print(”'[ ! -d {0} ] && mkdir -p {0}”’.format(os.path.join(dest_dir,backup_name)))
os.system(”'[ ! -d {0} ] && mkdir -p {0}”’.format(os.path.join(dest_dir,backup_name)))
#print(“xbstream -x < {0}.xbstream -C {1}”.format(backup_full_name,os.path.join(dest_dir,backup_name)))
os.system(“xbstream -x < {0}.xbstream -C {1}”.format(backup_full_name,os.path.join(dest_dir,backup_name)))
#print(“cd {0}”.format(os.path.join(dest_dir,backup_name)))
os.system(“cd {0}”.format(os.path.join(dest_dir,backup_name)))
#print(”’for f in `find {0}/ -iname “*\.qp”`; do qpress -dT4 $f $(dirname $f) && rm -f $f; done ”’.format(os.path.join(dest_dir,backup_name)))
os.system(”’for f in `find {0}/ -iname “*\.qp”`; do qpress -dT4 $f $(dirname $f) && rm -f $f; done”’.format(os.path.join(dest_dir,backup_name)))
current_backup_begin_time = None
current_backup_end_time = None
#比较当前备份的结束时间和 stop_at, 如果当前备份开始时间小于 stop_at 并且结束时间大于 stop_at，解压缩备份结束
with open(os.path.join(dest_dir,backup_name,”xtrabackup_info”), ‘r’) as f:
for line in f:
if line and line.split(“=”)[0].strip()==”start_time”:
current_backup_begin_time = line.split(“=”)[1].strip()
if line and line.split(“=”)[0].strip()==”end_time”:
current_backup_end_time = line.split(“=”)[1].strip()
#按照 stop_at 时间点还原的最后一个数据库备份, 结束从第一个完整备份开始的解压过程
if current_backup_begin_time<=stop_at<=current_backup_end_time:
break
#返回最后一个备份文件，需要备份文件中的 xtrabackup_info，解析出当前备份的 end_time，从而确认需要哪些 binlog
return backup_name
# 根据返回最后一个备份文件，需要备份文件中的 xtrabackup_info，结合 stop_at，确认需要还原的 binlog 文件，以及 binlog 的 position 信息
def restore_database_binlog(last_backup_file):
print(“################restore data from binlog###################”)
binlog_dir = get_config_value(“log-bin”)
if not (backup_dir):
binlog_dir = get_config_value(“log_bin”)
print(“cd {0}”.format(os.path.dirname(binlog_dir)))
last_backup_file =os.path.join(dest_dir,last_backup_file,”xtrabackup_info”)
#parse backuplog.log and get binlog name and position
backup_position_binlog_file = None
backup_position = None
with open(last_backup_file, ‘r’) as f:
lines = f.readlines()
for line in lines:
if “binlog_pos = filename ” in line:
backup_position_binlog_file = line.replace(“binlog_pos = filename “, “”).split(“,”)[0]
backup_position_binlog_file = backup_position_binlog_file.replace(“‘”, “”)
backup_position = line.replace(“binlog_pos = filename “, “”).split(“,”)[1].strip()
backup_position = backup_position.split(” “)[1].replace(“‘”, “”)
pass
else:
continue
# /usr/local/mysql57_data/mysql8000/log/bin_log/mysql_bin_1300
binlog_config = get_config_value(“log-bin”)
binlog_path = os.path.dirname(binlog_config)
binlog_files = os.listdir(binlog_path)
#如果没有找到 binlog，忽略 binlog 的还原
if not binlog_files:
exit(1)
#对 binlog 文件排序，按顺序遍历 binlog，获取 binlog 的最后的修改时间，与 stop_at 做对比，判断还原的过程是否需要某个 binlogfile
binlog_files.sort()
binlog_files_for_restore = []
# 恢复数据库的指定时间点
stop_at_time = datetime.datetime.strptime(stop_at, ‘%Y-%m-%d %H:%M:%S’)
for binlog in binlog_files:
if (“.index” in binlog or “relay” in binlog):
continue
#保留最后一个备份中的 binlog，以及其后面的 binlog，这部分 binlog 会在还原的时候用到
if (int(binlog.split(“.”)[-1]) >= int(backup_position_binlog_file.split(“.”)[-1])):
binlog_files_for_restore.append(binlog)
binlog_file_count = 0
#第一个文件，从上最后一个差异备份的 position 位置开始，最后一个文件，需要 stop_at 到指定的时间
for binlog in binlog_files_for_restore:
if not os.path.isdir(binlog):
#binlog 物理文件的最后修改时间
binlog_file_updatetime = datetime.datetime.strptime(time.strftime(‘%Y-%m-%d %H:%M:%S’, time.localtime(os.stat(binlog_path+”/”+binlog).st_mtime)),’%Y-%m-%d %H:%M:%S’)
#判断 binlog 的生成时间，是否大于 stop_at，对于修改时间大于 stop_at 的日志，需要全部还原，不需要 stop_at 指定截止点
if stop_at_time > binlog_file_updatetime :
if (binlog_file_count < 1):
if (len(binlog_files_for_restore) == 1):
# 找到差异备份之后的第一个 binlog，需要根据差异备份的 position，来过来第一个 binlog 文件
restore_commond = ”’mysqlbinlog {0} –skip-gtids=true –start-position={1} –stop-datetime=”{2}” | mysql mysql -h{3} -u{4} -p{5} -P{6}”’ \
.format(binlog, backup_position, stop_at, host, user, password, port)
print(restore_commond)
binlog_file_count = binlog_file_count + 1
else:
# 找到差异备份之后的第一个 binlog，需要根据差异备份的 position，来过来第一个 binlog 文件
restore_commond = ”’mysqlbinlog {0} –skip-gtids=true –start-position={1} | mysql mysql -h{2} -u{3} -p{4} -P{5}”’ \
.format(binlog, backup_position, host, user, password, port)
print(restore_commond)
binlog_file_count = binlog_file_count + 1
else:
# 从第二个文件开始，binlog 需要全部还原
restore_commond = ”’mysqlbinlog {0} –skip-gtids=true | mysql mysql -h{1} -u{2} -p{3} -P{4}”’ \
.format(binlog, host, user, password, port)
print(restore_commond)
binlog_file_count = binlog_file_count + 1
else:
if (binlog_file_count < 1):
restore_commond = ”’mysqlbinlog {0} –skip-gtids=true –start-position={1} –stop-datetime={2} | mysql -h{3} -u{4} -p{5} -P{6}”’.format(binlog, backup_position,stop_at,host,user,password,port)
print(restore_commond)
binlog_file_count = binlog_file_count + 1
else:
if (binlog_file_count >= 1):
restore_commond = ”’mysqlbinlog {0} –skip-gtids=true –stop-datetime=”{1}” | mysql -h{2} -u{3} -p{4} -P{5}”’.format(binlog, stop_at,host,user,password,port)
print(restore_commond)
binlog_file_count = binlog_file_count + 1
break
def apply_log_for_backup():
list_restore_backup = get_restorefile_list()
start_flag = 1
full_backup_path = None
for current_backup_file in list_restore_backup:
#解压备份文件
current_backup_name = current_backup_file.split(“|”)[2]
current_backup_fullname = os.path.join(dest_dir, current_backup_name)
if(start_flag==1):
full_backup_path = current_backup_fullname
start_flag = 0
print(“innobackupex –apply-log –redo-only {0}”.format(full_backup_path))
else:
print(“innobackupex –apply-log –redo-only {0} –incremental-dir={1}”.format(full_backup_path,current_backup_fullname))
#apply_log for full backup at last(remove –read-only parameter)
print(“innobackupex –apply-log {0}”.format(full_backup_path))
def restore_backup_data():
print(“####################backup current database file###########################”)
datadir_path = get_config_value(“datadir”)
print(“mv {0} {1}”.format(datadir_path,datadir_path+”_”+ datetime.datetime.now().strftime(‘%Y%m%d%H%M%S’)))
print(“mkdir {0}”.format(datadir_path))
print(“chown -R mysql.mysql {0}”.format(datadir_path))
print(“################restore backup data###################”)
list_restore_backup = get_restorefile_list()
full_restore_path= dest_dir + list_restore_backup[0].split(“|”)[-1].replace(“.xbstream”,””)
print(“innobackupex –defaults-file={0} –copy-back –rsync {1}”.format(cnf_file,full_restore_path))
print(“chown -R mysql.mysql {0}”.format(datadir_path))
def restore_database():
#解压缩需要还原的备份文件
last_backup_file_path = uncompress_backup_file()
#对备份文件 apply-log
apply_log_for_backup()
#停止 mysql 服务
stop_mysql_service()
#恢复备份
restore_backup_data()
#启动 MySQL 服务
start_mysql_service()
#从 binlog 中恢复数据
restore_database_binlog(last_backup_file_path)
if __name__ == ‘__main__’:
restore_database()
最后不要忘了清理战场：
1，解压缩的备份文件还留在指定的路径中，
2，还原之前备份的 data 文件，以 data_日期命名的文件，也没有清理
挤时间写出来的，粗略测了一下没有问题，以实现功能为主，没有进一步封装，后续会以此为基础进行优化。
：
