Python 服务化备份MySQL

[root@acs-hk-ctos7-prod-01 mysql_scripts]# cat backup_db.py 
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @time    : 2023/7/4 14:54
# @File    : backup_db.py
# @Author  : zk_linux
# @Software: PyCharm
# @Description： backup to demo lcd xxl_job data

import configparser
import subprocess
import os
import pymysql
import time
import datetime
import zipfile
import logging
import schedule

databases = ["demo", "lcd", "xxl_job"]
BACKUP_FILENAME = '%Y%m%d%H%M%S.sql'
ZIP_TIME = '%Y%m%d%H%M%S.zip'

dir_path = '/server/scripts/log/'
if not os.path.exists(dir_path):
    os.makedirs(dir_path)

BACKUP_DIR = '/server/backup_db'
if not os.path.exists(BACKUP_DIR):
    os.makedirs(BACKUP_DIR)

file_path = '/server/scripts/log/backup_db.log'
if not os.path.exists(file_path):
    with open(file_path, 'w') as file:
        pass  # 空操作，仅创建文件

logging.basicConfig(level=logging.INFO,
                    filename=file_path,
                    filemode='a',
                    format='%(asctime)s - %(pathname)s[line:%(lineno)d] - %(levelname)s: %(message)s'
                    )


class MysqlConfig:
    def __init__(self, config_file):
        self.config_file = config_file
        self.config = configparser.ConfigParser()
        self.config.read(self.config_file)

    def __getitem__(self, key):
        return self.config['MySQL'][key]


def run_backup():
    os.makedirs(BACKUP_DIR, exist_ok=True)
    try:
        for db_name in databases:
            backup_file = os.path.join(BACKUP_DIR, f'{db_name}_{datetime.datetime.now().strftime(BACKUP_FILENAME)}')
            backup_command = f'mysqldump -h {mysql_config["master_host"]} -u {mysql_config["master_user"]} -p{mysql_config["master_password"]} {db_name} > {backup_file}'
            subprocess.run(backup_command, shell=True, check=True)
            logging.info(f'Successfully created backup for database "{db_name}": {backup_file}')
            zip_pak = os.path.join(BACKUP_DIR, f'{db_name}_{datetime.datetime.now().strftime(ZIP_TIME)}')

            with zipfile.ZipFile(zip_pak, 'w', zipfile.ZIP_DEFLATED, allowZip64=True) as zipf:
                zipf.write(backup_file)
                logging.info(f'Compressing the backup{backup_file}')
            os.remove(backup_file)
            logging.info(f'delete backup db file:{zip_pak}')
    except subprocess.CalledProcessError as e:
        logging.error(f'Backup failed for database "{db_name}": {str(e)}')


def deletefile(PATH):
    data_retention_days = int(mysql_config["data_retention_days"])
    for eachfile in os.listdir(PATH):
        filename = os.path.join(PATH, eachfile)
        if os.path.isfile(filename):
            lastmodifytime = os.stat(filename).st_mtime
            endfiletime = time.time() - 3600 * 24 * data_retention_days
            if endfiletime > lastmodifytime:
                if filename[-4:] == ".zip":
                    os.remove(filename)
                    logging.info("del %s success!!!" % filename)
        elif os.path.isdir(filename):
            deletefile(filename)


if __name__ == '__main__':
    mysql_config = MysqlConfig('config.ini')
    print(mysql_config["scheduled_task"])
    schedule.every().day.at(mysql_config["scheduled_task"]).do(run_backup)
    while True:
         
    #    schedule.every().day.at(mysql_config["scheduled_task"]).do(run_backup)
        schedule.run_pending()
        time.sleep(1)
        deletefile(BACKUP_DIR)

[root@acs-hk-ctos7-prod-01 mysql_scripts]# cat config.ini
[MySQL]
#修改内网服务器IP
master_host=172.31.59.7
master_port=3306
master_user=root
master_password=
#定时任务执行时间,凌晨2:10执行
scheduled_task= 00:25
#数据保留时常
data_retention_days = 7

FROM python:3.7
RUN apt-get update
RUN apt-get install -y default-mysql-client
RUN mkdir -p /server/scripts/log /server/backup_db
ADD config.ini /server/scripts
ADD requirements.txt /server/scripts
ADD backup_db.py /server/scripts 
WORKDIR /server/scripts
RUN pip install -r /server/scripts/requirements.txt  >/dev/null 2>&1