Python 服务化备份MySQL

1. 通过配置进行更更改数据备份时间和数据保留时间

创建所需环境

mkdir /usr/local/esl/mysql_scripts/log -p

mkdir /usr/local/esl/backup_db -p

[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)

#!/usr/bin/env python 
# -*- coding: utf-8 -*-
# @Time    : 2023/5/31 09:32
# @Author  : zk_linux
# @File    : main.py
# @Software: PyCharm

import os
import requests
import socket
import configparser


def get_system_info():
    s = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
    s.connect(('8.8.8.8', 80))
    local_ip = s.getsockname()[0]
    return local_ip


local_ip = get_system_info()

config = configparser.ConfigParser()
config.read("config.ini", encoding="utf-8")
config.set("MySQL", "master_host", local_ip)
with open("config.ini", "w") as f:
    config.write(f)

root@rancher-server mysql_scripts]# cat config.ini 
[MySQL]
master_host = 192.168.0.56
master_port = 3306
master_user = root
master_password = 1234
scheduled_task = 00:25
data_retention_days = 3

FROM esl/python:3.7.4.1
#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  

#

67459162-1c40-47ff-b260-9ec5dfee5f12