python mysql备份脚本 

#!/usr/bin/env python 
# -*- coding: utf-8 -*-
# @Time    : 2023/9/15 15:32
# @File    : backup_db.py
# @Author  : zk_linux
# @Software: PyCharm
# @Description：
import zipfile
import time
import configparser
import os
import datetime
import logging
import subprocess
import gzip
import schedule

exclude_tables = "item"

backup_dir = '/server/backup_db'
database_list = ["demo", "lcd", "xxl_job"]
file_path = '/server/scripts/log/backup_db.log'

exclude = "--ignore-table=demo.item --ignore-table=demo.ap_import_log --ignore-table=demo.ap_upgrade_log --ignore-table=demo.bind_log --ignore-table=demo.esl_firmware_log --ignore-table=demo.item_import_log --ignore-table=demo.mail_log --ignore-table=demo.operation_log --ignore-table=demo.operation_log_active --ignore-table=demo.shelves_pricetag_item_log --ignore-table=demo.sign_import_log --ignore-table=demo.store_inventory_log --ignore-table=demo.user_login_log"

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

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

if not os.path.exists(backup_dir):
    os.makedirs(backup_dir)


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]


class GetFileName:

    def __init__(self, database, mysql_config, backup_dir, exclude=""):
        self.database = database
        self.mysql_config = mysql_config
        self.backup_dir = backup_dir
        self.exclude = exclude

    def generate_backup_file_name(self, prefix):
        '''

        :param prefix:(prefix='demo lcd xxl_job')  ()
        :return:
        '''

        return os.path.join(
            self.backup_dir,
            f'{prefix}_{datetime.datetime.now().strftime("%Y%m%d%H%M%S")}.sql'
        )

    def get_backup_item_file_name(self):
        backup_item_file_name = self.generate_backup_item_file_name()
        print(backup_item_file_name)
        return backup_item_file_name

    def generate_backup_command(self, table_name=""):
        '''

        :param table_name: item
        '''
        command = f'mysqldump -h {self.mysql_config["master_host"]} -u {self.mysql_config["master_user"]} -p{self.mysql_config["master_password"]} {self.database}  '
        command += f' {table_name}'

        if table_name == "item":
            command += f' > {self.generate_backup_item_file_name()}'
        else:
            command += f'> {self.generate_backup_file_name(self.database)}'
        return command

    def generate_backup_item_file_name(self):
        '''

        :return: ./backup_db/demo_item_20230918114451.sql
        '''
        item_prefix = f'{self.database}'
        return os.path.join(
            self.backup_dir,
            f'{item_prefix}_item_{datetime.datetime.now().strftime("%Y%m%d%H%M%S")}.sql'
        )

    def get_backup_file_name(self):
        backup_file_name = self.generate_backup_item_file_name(str(self.database))
        return backup_file_name


class BackupDatabaseRun:
    def __init__(self, database_list, mysql_config, backup_dir, exclude=""):
        self.database_list = database_list
        self.mysql_config = mysql_config
        self.backup_dir = backup_dir
        self.exclude = exclude

    def run_get_names(self):
        try:
            zip_file_name = []
            for database in self.database_list:

                get_backup_names = GetFileName(database, self.mysql_config, self.backup_dir, self.exclude)
                backup_demo_file_name = get_backup_names.generate_backup_file_name(database)
                if database.startswith("demo"):

                    backup_command = get_backup_names.generate_backup_command(exclude)
                    zip_file_name.append(backup_demo_file_name)
                    subprocess.run(backup_command, shell=True, check=True)

                    logging.info(f'Back up demo library instructions: {backup_command}')

                    item_backup_file_name = get_backup_names.generate_backup_item_file_name()
                    item_backup_command = get_backup_names.generate_backup_command(exclude_tables)
                    subprocess.run(item_backup_command, shell=True, check=True)
                    logging.info(f'Back up the itme table command:{item_backup_command}')

                    zip_file_name.append(item_backup_file_name)

                else:

                    backup_xxl_lcd_command = get_backup_names.generate_backup_command()
                    subprocess.run(backup_xxl_lcd_command, shell=True, check=True)
                    logging.info(f'Back up xxl lcd library instructions:{backup_xxl_lcd_command}')
                    zip_file_name.append(backup_demo_file_name)
            return zip_file_name
        except subprocess.CalledProcessError as e:
            logging.error(f'Backup failed for database : {str(e)}')

    def zip_file(self):

        sql_file_name = backup_runner.run_get_names()

        for file_name in sql_file_name:
            new_filename = os.path.splitext(file_name)[0] + '.zip'

            with zipfile.ZipFile(new_filename, 'w', zipfile.ZIP_DEFLATED, allowZip64=True) as zipf:
                zipf.write(file_name)
                logging.info(f'Original file:{file_name}--->>Target compressed file:{new_filename}')
            os.remove(file_name)


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(f'Deleting backup files:{filename}')

        elif os.path.isdir(filename):
            deletefile(filename)


if __name__ == '__main__':
    mysql_config = MysqlConfig('config.ini')
    backup_runner = BackupDatabaseRun(database_list, mysql_config, backup_dir, exclude_tables)
    schedule.every().day.at(mysql_config["scheduled_task"]).do(backup_runner.zip_file)
    logging.info("Wait for backup")

    while True:
        schedule.run_pending()
        time.sleep(1)
        deletefile(backup_dir)

　　


 
posted @ 2023-10-23 14:39  地铁昌平线  阅读(65)  评论(0)    收藏  举报