使用python实现innobackup自动化备份、清理、通知

说明

上篇博客【MySQL】TokuDB引擎安装及数据迁移迁移后拓展，解决自动备份。

前置

• 安装 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 = langke121
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>