前言
本文是阿里云RDS迁移后拓展，解决自动备份并通过邮件通知备份结果。
关于阿里云RDS迁移详见
放一张代码运行后的效果图
前置
- 安装 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 hoke
import argparse
import configparser
import logging
import shutil
import smtplib
import socket
import subprocess
import sys
import os
import jinja2
import time
import datetime
from email.mime.text import MIMEText
from os.path import isfile
from tempfile import TemporaryFile
# Set logger
LOG_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 HOSTNAME
HOSTNAME = socket.gethostname()
# Get os time
CURRENT_TIME = time.strftime("%Y-%m-%d_%H:%M:%S", time.localtime())
# Set statistics info
STATISTICS_INFO = {'备份主机': HOSTNAME}
# Set render html template
module_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.file
def 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 True
class 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_dir
class 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 \n {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 user
user = root
password =
host = 127.0.0.1
port = 3306
[xtrabackup]
backup_tool = /usr/bin/innobackupex
defaults-file = /etc/my.cnf
backupdir = /home/hoke/zabbixDB-backup/AutoBackup
xtra_options = --no-version-check --rsync
[compress]
# Optional
# Enable only if you want to use compression.
compress = quicklz
compress_chunk_size = 64k
compress_threads = 4
[mail]
# mail setting
title = [ZabbixDB AutoBackup]
mail_sender = rd_sys@xxx.xxx
mail_receiver = runchain_ops@xxx.xxx,hoke58@qq.com
mail_host = smtp.263.net
mail_port = 25
mail_user = rd_sys@xxx.xxx
mail_pass = password
[expired]
# Enable only if you want to clean expired backup.
expire_day = 5邮件模板 table_template.html
<html lang="en">
<head>
<meta charset="gbk">
<style>
body {
font-size: 14px;
line-height: 1.42857143;
color: #333;
background-color: #fff;
}
.row {
margin-right: -15px;
margin-left: -15px;
}
.col-sm-10 {
width: 85%;
}
.col-sm-2 {
width: 15%;
}
.table {
width: 85%;
max-width: 85%;
margin-bottom: 1rem;
background-color: transparent;
border-collapse: collapse;
}
.table td, .table th {
padding: .65rem;
vertical-align: top;
border-top: 1px solid #dee2e6;
word-wrap: break-word;
word-break: break-all;
}
</style>
</head>
<body>
<div>
{% if data %}
<table class="table">
<tbody>
{% for key in data %}
<tr class="row">
<td class="col-sm-2">{{ key }}</td>
<td class="col-sm-10">{{ data[key] }}</td>
</tr>
{% endfor %}
</tbody>
</table>
{% elif err_msg %}
<p>err_msg</p>
{% endif %}
</div>
</body>
</html>调试好后别忘了crontab 定期执行