前言
本文是阿里云RDS迁移后拓展,解决自动备份并通过邮件通知备份结果。
关于阿里云RDS迁移详见
阿里云RDS迁移,极简安装 MySQL TokuDB 引擎
阿里云RDS迁移,innobackupex 大数据迁移神器
放一张代码运行后的效果图
![7eaf5a1d5229eed5011ae66793792bc6.png](https://i-blog.csdnimg.cn/blog_migrate/2fdcee703a7fea39296d89cd57c36c04.jpeg)
前置
- 安装 python3.6
- 安装 Percona XtraBackup (备份工具)
依赖模块
pip3 install jinja2 MarkupSafe
使用说明
1. 编辑配置文件 vim xb_back.cnf
2. 运行 xb_back.py 脚本
python3.6 ./xb_back.py -f ./xb_back.cnf
代码 xb_back.py
# -*- coding:utf-8 -*-# edit by hokeimport argparseimport configparserimport loggingimport shutilimport smtplibimport socketimport subprocessimport sysimport osimport jinja2import timeimport datetimefrom email.mime.text import MIMETextfrom os.path import isfilefrom tempfile import TemporaryFile# Set loggerLOG_FORMAT = "%(asctime)s - %(levelname)s - %(message)s"DATE_FORMAT = "%Y-%m-%d %H:%M:%S"logging.basicConfig(level=logging.DEBUG, format=LOG_FORMAT, datefmt=DATE_FORMAT)logger = logging.getLogger(__name__)# Get HOSTNAMEHOSTNAME = socket.gethostname()# Get os timeCURRENT_TIME = time.strftime("%Y-%m-%d_%H:%M:%S", time.localtime())# Set statistics infoSTATISTICS_INFO = {'备份主机': HOSTNAME}# Set render html templatemodule_path = os.path.dirname(__file__)html_template = os.path.join(module_path + '/table_template.html')def render_to_template(html_path, html_context): path, filename = os.path.split(html_path) env = jinja2.Environment( loader=jinja2.FileSystemLoader(path) ) if isinstance(html_context, dict): return env.get_template(filename).render({'data': html_context}) else: return env.get_template(filename).render({'err_msg': html_context})def get_arguments(): """ Get user input """ parser = argparse.ArgumentParser(description='This a backup help document.') parser.add_argument('-f', '--file', type=str, help='xtrabackup read config file') args = parser.parse_args() return args.filedef check_config_valid(file): """ Check config file content valid """ config = configparser.ConfigParser() try: config.read(file) except configparser.MissingSectionHeaderError: logger.warning('WARN: this first line must contains section headers') config_items = [x for x in config.keys()] config_keys = [] for i in [config.items(x) for x in config.keys()]: for j in i: config_keys.append(j[0]) keep_items = ['mysql', 'xtrabackup', 'compress', 'mail', 'expired'] keep_keys = [ 'user', 'host', 'password', 'port', 'backup_tool', 'defaults-file', 'backupdir', 'title', 'mail_sender', 'mail_receiver', 'mail_host', 'mail_port', 'mail_user', 'mail_pass', 'expire_day' ] for header in keep_items: if header not in config_items: logger.error(f'FAILED: this section header [{header}] not found.') sys.exit(1) for key in keep_keys: if key not in config_keys: logger.error(f'FAILED: this argument [{key}] not found.') sys.exit(1) return Trueclass General(object): """ Process the config file and Generate variables """ def __init__(self, file): if isfile(file): config = configparser.ConfigParser(allow_no_value=True) config.read(file) Mysql = config['mysql'] self.user = Mysql['user'] self.host = Mysql['host'] self.password = Mysql['password'] self.port = Mysql['port'] Xtrabackup = config['xtrabackup'] self.backup_tool = Xtrabackup['backup_tool'] self.defaults_file = Xtrabackup['defaults-file'] self.backupdir = Xtrabackup['backupdir'] self.fmt_backupdir = '/'.join((Xtrabackup['backupdir'], CURRENT_TIME)) if 'xtra_options' in Xtrabackup: self.xtra_options = Xtrabackup['xtra_options'] Compress = config['compress'] if 'compress' in Compress: self.compress = Compress['compress'] if 'compress_chunk_size' in Compress: self.compress_chunk_size = Compress['compress_chunk_size'] if 'compress_threads' in Compress: self.compress_threads = Compress['compress_threads'] Mail = config['mail'] self.title = Mail['title'] self.mail_sender = Mail['mail_sender'] self.mail_receiver = Mail['mail_receiver'] self.mail_host = Mail['mail_host'] self.mail_port = Mail['mail_port'] self.mail_user = Mail['mail_user'] self.mail_pass = Mail['mail_pass'] Expired = config['expired'] self.expire_day = Expired['expire_day']class ToolsUtils(General): """ Define some tools """ def __init__(self, file): self.file = file General.__init__(self, self.file) self.xb_output_log = '/'.join((self.fmt_backupdir, 'xb_output.log')) STATISTICS_INFO['备份目录'] = self.fmt_backupdir STATISTICS_INFO['备份工具'] = self.backup_tool def create_backup_dir(self): """ create backup directory """ if not os.path.exists(self.fmt_backupdir): os.makedirs(self.fmt_backupdir) logger.info(f'OK: the backup dir not exisit, create {self.fmt_backupdir}') FMT_INFO = '{:.2f}GB' def get_backup_file_size(self): size = 0 for root, dirs, files in os.walk(self.fmt_backupdir): size += sum([os.path.getsize(os.path.join(root, name)) for name in files]) logger.info(f'OK: get backup file size') STATISTICS_INFO['备份大小'] = self.FMT_INFO.format(float(size / 1024 / 1024 / 1024)) return True def get_partition_size(self): vfs = os.statvfs(self.fmt_backupdir) free = (vfs.f_bavail * vfs.f_bsize) / (1024 * 1024 * 1024) total = (vfs.f_blocks * vfs.f_bsize) / (1024 * 1024 * 1024) partition_free_size = self.FMT_INFO.format(free) partition_total_size = self.FMT_INFO.format(total) logger.info(f'OK: get disk partition usage') STATISTICS_INFO['可用空间'] = partition_free_size STATISTICS_INFO['总空间'] = partition_total_size return True def send_mail(self, data): """ Send mail notice Read TemporaryFile content """ msg = MIMEText(data, _subtype='html', _charset='utf-8') msg['Subject'] = '{} from {}'.format(self.title, HOSTNAME) msg['From'] = self.mail_sender msg['To'] = ";".join(list(self.mail_receiver.split(','))) mail_receiver = list(self.mail_receiver.split(',')) try: server = smtplib.SMTP() server.connect(self.mail_host, self.mail_port) # server.ehlo() # enable tls encrypt server.starttls() server.set_debuglevel(1) server.login(self.mail_user, self.mail_pass) server.sendmail(self.mail_sender, mail_receiver, msg.as_string()) server.close() logger.info(f'OK: send mail success') except Exception as err: logger.error(f'FAILED: send mail fail') logger.error(err) def remove_expired_directory(self): #当前时间 today = datetime.datetime.now() today_init = int(today.strftime('%Y%m%d')) print("today_init:", today_init) # #n天前时间 # n_days = datetime.timedelta(days=int(expire_time)) # n_days_agos = today - n_days # n_days_agos_init = int(n_days_agos.strftime('%Y%m%d')) remove_dir = [] print("backupdir", self.backupdir) list = os.listdir(self.backupdir) print("list:", list) for directory_name in list: abs_dir = os.path.join(self.backupdir, directory_name) print("abs_dir:", abs_dir) dir_timestamp = os.path.getctime(abs_dir) dir_init = int(datetime.datetime.fromtimestamp(dir_timestamp).strftime('%Y%m%d')) print("dir_init:", dir_init) print("expire_day:", int(self.expire_day)) print(today_init - dir_init) if today_init - dir_init >= int(self.expire_day): print("remove:", os.path.join(self.backupdir, directory_name)) shutil.rmtree(os.path.join(self.backupdir, directory_name)) logger.info(f"OK: the directory {directory_name} remove success") remove_dir.append(directory_name) STATISTICS_INFO['过期备份'] = remove_dirclass Prepare(General): """ Generate command """ def __init__(self, file): self.file = file General.__init__(self, self.file) @property def generate_xb_cmd(self): mysql_cmd = f"--user={self.user} --password={self.password} --host={self.host} --port={self.port}" cmd_list = [] if hasattr(self, 'compress') and hasattr(self, 'compress_chunk_size') and hasattr(self, 'compress_threads'): compress_cmd = f"--compress={self.compress} --compress-chunk-size={self.compress_chunk_size} --compress-threads={self.compress_threads}" cmd_list.append(compress_cmd) STATISTICS_INFO['是否压缩'] = 'Yes' else: STATISTICS_INFO['是否压缩'] = 'No' if hasattr(self, 'xtra_options'): xtra_options = self.xtra_options cmd_list.append(xtra_options) xb_cmd = f"{self.backup_tool} --defaults-file={self.defaults_file}" return ' '.join((xb_cmd, mysql_cmd, ' '.join(cmd_list), self.fmt_backupdir)) class RunCommand(object): def __init__(self, command): self.command = command @property def runner(self): status, output = subprocess.getstatusoutput(self.command) return {'status': status, 'output': output}def main(): start_time = time.time() config_file = get_arguments() # check whether the config file is valid check_config_valid(config_file) # instance Prepare and Toolskit prepare = Prepare(config_file) tools = ToolsUtils(config_file) xb_cmd = prepare.generate_xb_cmd logger.info(f'OK: generate xtrabackup command {xb_cmd}') # exec backup backup_result = RunCommand(xb_cmd).runner logger.info(f'OK: perform backup process, please waiting.') with TemporaryFile('w+t', encoding='gbk') as f: if backup_result['status'] == 0: logger.info(f'OK: xtrabackup backup success') tools.get_backup_file_size() tools.get_partition_size() tools.remove_expired_directory() end_time = time.time() STATISTICS_INFO['备份耗时'] = '{:0.2f}s'.format(end_time - start_time) result = render_to_template(html_template, STATISTICS_INFO) f.write(result) else: logger.error(f"ERROR: {backup_result['output']}") f.write(backup_result['output']) f.seek(0) TEXT_DATA = f.read() tools.send_mail(TEXT_DATA)if __name__ == '__main__': main()
配置文件 xb_back.cnf
[mysql]# mysql backup useruser = rootpassword = host = 127.0.0.1port = 3306[xtrabackup]backup_tool = /usr/bin/innobackupexdefaults-file = /etc/my.cnfbackupdir = /home/hoke/zabbixDB-backup/AutoBackupxtra_options = --no-version-check --rsync[compress]# Optional# Enable only if you want to use compression.compress = quicklzcompress_chunk_size = 64kcompress_threads = 4[mail]# mail settingtitle = [ZabbixDB AutoBackup]mail_sender = rd_sys@xxx.xxxmail_receiver = runchain_ops@xxx.xxx,hoke58@qq.commail_host = smtp.263.netmail_port = 25mail_user = rd_sys@xxx.xxxmail_pass = password[expired]# Enable only if you want to clean expired backup.expire_day = 5
邮件模板 table_template.html
{% if data %}
{% for key in data %} {{ key }} {{ data[key] }} {% endfor %} {% elif err_msg %}
err_msg
{% endif %}
调试好后别忘了crontab 定期执行
![4116ca789afe0d143434f3c9d7ff6bed.png](https://i-blog.csdnimg.cn/blog_migrate/340032e3efbc5a0b562098faa95de1ce.jpeg)