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_日期命名的文件, 也没有清理
挤时间写出来的, 粗略测了一下没有问题, 以实现功能为主, 没有进一步封装, 后续会以此为基础进行优化.
来源: http://www.linuxidc.com/Linux/2019-08/159780.htm