dbs-maintenance/script/backup_database.py

# coding=UTF-8
############################################################################################
#
# Author:      Wenguan Ding
# Date:        2025/03/07
# Description: This script is developed to run logical hot backup for mysql database(s)
# Revision:
#              Date             Author              Comment
#             -------------    --------------      -----------------------------------------
#              2025/03/07       Wenguan Ding        Created
#              2025/03/11       Wenguan Ding        use auth_socket plugin to login database
#
############################################################################################
import os
import subprocess
import time
import gzip
import sys
import argparse

# 解析命令行参数
parser = argparse.ArgumentParser(description="MySQL热备份脚本")
parser.add_argument("-d", "--database", help="要备份的数据库名称(输入'all'备份所有数据库)", required=True)
parser.add_argument("-p", "--path", help="备份文件存储路径", required=True)
parser.add_argument("-t", "--day", help="保留备份天数", required=True)
args = parser.parse_args()

# 配置信息
MYSQL_USER = "backup_htfp"   # MySQL用户名
#MYSQL_PASSWORD = "xxxxxxx"  # MySQL密码. 设置了auth-socket,无需使用密码
#MYSQL_HOST = "localhost"    # MySQL主机. 设置了auth-socket, 无需指定host
#MYSQL_PORT = "12138"        # MySQL端口. 设置了auth-socket, 无需指定host

# 创建备份目录（如果不存在）
if not os.path.exists(args.path):
    os.makedirs(args.path)

def backup_database(db_name, backup_dir):
    """备份单个数据库"""
    timestamp = time.strftime("%Y%m%d_%H%M%S")
    backup_file = os.path.join(backup_dir, "{0}_backup_{1}.sql".format(db_name, timestamp))
    compressed_backup_file = backup_file + ".gz"

    try:
        print("开始备份数据库: {0}".format(db_name))
        with open(backup_file, "w") as f:
            subprocess.check_call(
                [
                    "mysqldump",
                    "--user={0}".format(MYSQL_USER),
                   # "--password={0}".format(MYSQL_PASSWORD),
                   # "--host={0}".format(MYSQL_HOST),
                   # "--port={0}".format(MYSQL_PORT),
                    "--single-transaction",  # 确保热备份
                    "--routines",  # 备份存储过程和函数
                    "--triggers",  # 备份触发器
                    "--events",  # 备份事件
                    db_name,
                ],
                stdout=f,
            )
        print("数据库备份成功，文件保存到: {0}".format(backup_file))

        # 压缩备份文件
        with open(backup_file, "rb") as f_in:
            with gzip.open(compressed_backup_file, "wb") as f_out:
                f_out.writelines(f_in)
        print("备份文件已压缩: {0}".format(compressed_backup_file))

        # 删除未压缩的备份文件
        os.remove(backup_file)
        print("已删除未压缩的备份文件: {0}".format(backup_file))

    except subprocess.CalledProcessError as e:
        print("备份失败: {0}".format(e))
    except Exception as e:
        print("发生错误: {0}".format(e))

def get_all_databases():
    """获取所有数据库名称"""
    try:
        output = subprocess.check_output(
            [
                "mysql",
                "--user={0}".format(MYSQL_USER),
              #  "--password={0}".format(MYSQL_PASSWORD),
              #  "--host={0}".format(MYSQL_HOST),
              #  "--port={0}".format(MYSQL_PORT),
                "-e",
                "SHOW DATABASES;",
            ]
        )
        databases = output.splitlines()[1:]  # 跳过第一行标题
        return databases
    except subprocess.CalledProcessError as e:
        print("获取数据库列表失败: {0}".format(e))
        return []



def delete_old_backup_files(directory, n):
    """开始清理过期备份文件"""
    print("开始清理过期备份文件 ...")
    
    # 获取当前时间
    current_time = time.time()
    # 计算n天前的时间戳
    time_threshold = current_time - (n * 86400)  # 86400秒 = 1天

    # 遍历目录中的文件
    for filename in os.listdir(directory):
        file_path = os.path.join(directory, filename)
        # 检查是否是文件，并且文件名包含"backup"并以".sql.gz"结尾
        if os.path.isfile(file_path) and "backup" in filename and filename.endswith(".sql.gz"):
            # 获取文件的创建时间
            file_creation_time = os.path.getctime(file_path)
            #print(file_path)
            # 如果文件创建时间早于阈值，则删除文件
            if file_creation_time < time_threshold:
                print("删除过期备份: {}".format(file_path))
                os.remove(file_path)
                
                      
# 执行备份
if args.database.lower() == "all":
    databases = get_all_databases()
    if databases:
        print("开始备份所有数据库: {0}".format(databases))
        for db in databases:
            if db not in ["information_schema", "performance_schema", "mysql", "sys"]:  # 跳过系统数据库
                backup_database(db, args.path)
    else:
        print("未找到可备份的数据库")
else:
    backup_database(args.database, args.path)


# 检查目录是否存在
if not os.path.isdir(args.path):
    print("Error: Directory '{}' does not exist.".format(directory))
    sys.exit(1)

# 调用函数删除符合条件的旧文件
delete_old_backup_files(args.path, int(args.day))
print("Done.")