mysql 自动备份_干货！python实现mysql自动化备份、清理、通知-CSDN博客

前言

本文是阿里云RDS迁移后拓展，解决自动备份并通过邮件通知备份结果。

关于阿里云RDS迁移详见

阿里云RDS迁移，极简安装 MySQL TokuDB 引擎

阿里云RDS迁移，innobackupex 大数据迁移神器

放一张代码运行后的效果图

前置

安装 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