开发者

基于Python编写MySQL数据库备份脚本

前言

以下是一个使用python编写的mysql数据库备份脚本,包含压缩、日志记录和自动清理旧备份功能:

脚本内容:

import subprocess
import gzip
import os
import logging
import argparse
from datetime import datetime, timedelta

# 配置日志记录
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=php[
        logging.FileHandler('db_backup.log'),
        logging.StreamHandler()
    ]
)

def backup_mysql(host, user, password, database, backup_dir, retain_days):
    try:
        # 创建备份目录
        os.makedirs(backup_dir, exist_ok=True)
        
php        # 生成备份文件名
        timestamp = datetime.now().strftime('%Y%m%d%H%M%S')
        backup_file = os.path.join(backup_dir, f"编程{database}_{timestamp}.sql")
        compressed_file = f"{backup_file}.gz"

        # 执行mysqldump命令
        logging.info(f"Starting backup for database: {database}")
        with open(backup_file, 'w') as f:
            cmd = [
                'mysqldump',
                f'-h{host}',
                f'-u{user}',
                f'-p{password}',
                '--single-transaction',
                '--routines',
                '--triggers',
                database
            ]
            result = subprocess.run(cmd, stdout=f, stderr=subprocess.PIPE, text=True)

        if result.returncode != 0:
            raise Exception(f"mysqldump failed: {result.stderr}")

        # 压缩备份文件
        with open(backup_file, 'rb') as f_in:
            with gzip.open(compressed_file, 'wb') as f_out:
                f_out.writelines(f_in)
        os.remove(backup_file)
        logging.info(f"Backup compressed: {compressed_file}")

        # 清理旧备份
        cutoff = datetime.now() - timedelta(days=retain_days)
        for fjavascriptilename in os.listdir(backup_dir):
            filepath = os.path.join(backup_dir, filename)
            if os.path.isfile(filepath) and filename.endswith('.sql.gz'):
                file_time = datetime.fromtimestamp(os.path.getmtime(filepath))
                if file_time < cutoff:
                    os.remove(filepath)
                    logging.info(f"Deleted old backup: {filename}")

    except Exception as e:
        logging.error(f"Backup failed: {str(e)}")
        raise

if __name__ == "__main__":
    parser = argparse.ArgumentParser(description='MySQL Database Backup Tool')
    parser.add_argument('--host', required=True, help='MySQL host')
    parser.add_argument('--user', required=True, help='MySQL username')
    parser.add_argument('--password', required=True, help='MySQL password')
    parser.add_argument('--database', required=True, help='Database name')
    parser.add_argument('--backup-dir', default='./backups', help='Backup directory')
    parser.add_argument('--retain-days', type=int, default=7, 
                       help='Number of days to retain backups')
    
    args = parser.parse_args()
    
    try:
        backup_mysql(
            args.host,
            args.user,
            args.password,
            args.database,
            args.backup_dir,
            args.retain_days
        )
        logging.info("Backup completed successfully")
    except:
        logging.error("Backup process terminated with errors")

功能说明:

  • 自动压缩:使用gzip压缩备份文件,节省存储空间
  • 日志记录:同时输出到文件和控制台
  • 旧备份清理:自动删除超过指定天数的旧备份
  • 事务备份:使用–single-transaction保证备份一致性
  • 存储过程/触发器:包含–routines和–triggers参数
  • 错误处理:完善的异常捕获和错误日志记录

使用方式

bash

python mysql_backup.py \

  --host=localhost \

  --user=root \

  --passwo编程rd=your_password \

  --database=your_database \

  --backup-dir=/path/to/backups \

  --retain-days=7

建议改进方向:

  • 添加邮件通知功能
  • 支持远程备份到云存储
  • 添加备份验证功能
  • 支持多数据库并行备份
  • 添加备份恢复功能

注意:需要提前安装MySQL客户端工具(包含mysqldump),建议在服务器上直接运行。

方法补充

下面小编整理了一些其他Python备份mysql数据库的方法,希望对大家有所帮助

Python定时按日期备份MySQL数据并压缩

#-*- coding:utf-8 -*-
import os
import time
import tarfile
import zipfile
'''
mysqldump
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help
'''
db_host="localhost"
db_user="root"
db_passwd="root"
db_name="crm"
db_charset="utf8"
db_backup_name=r"c:\crm_%s.sql" %(time.strftime("%Y%m%d%H%M"))
zip_src = db_backup_name
zip_dest = zip_src + ".zip"
def zip_files():
f = zipfile.ZipFile(zip_dest, 'w' ,zipfile.ZIP_DEFLATED)
f.write(zip_src)
f.close()
if __name__ == "__main__":
print("begin to dump mysql database crm...");
os.system("mysqldump -h%s -u%s -p%s %s --default_character-set=%s > %s" %(db_host, db_user, db_passwd, db_name, db_charset, db_backup_name))
print("begin zip files...")
zip_files()
print("done, pyhon is great!")

python 自动备份 mysql数据库

import os
import sys  
# import glob 
import filecmp
import time
import datetime
import subprocess 
import sys 

databackup_dir_path = r'E:\databackup'
mysqldump_path = r'E:\Program Files\mariadb 10.5\bin\mysqldump'
mysql_ip = '127.0.0.1'
mysql_username='root'
mysql_password='root'
mysql_port=3306

backDatabaseNames =  ["kwjl5","kwjl5c2","kwjl5ga" ]

def deleteOldFile(path,day):
    for eachfile in os.listdir(path):
        filename = os.path.join(path, eachfile)
        # backup_ 开头的文件才会被删除
        if os.path.isfile(filename) :  
            lastmodifytime = os.path.getmtime(filename)
            # 设置删除多久之前的文件
            endfiletime = time.time() - 3600 * 24 * day
            if endfiletime > lastmodifytime:
                if filename[0:7] == "backup_" and filename[-4:] == ".sql":
                    os.remove(filename)
                    print ("删除文件 %s 成功" % filename)
        # 如果是目录则递归调用当前函数
        elif os.path.isdir(filename):  
            deleteOldFile(filename,day)


def backupmysql(dbname): 
  time1_str = datetime.datetime.now().strftime('%Y_%m_%d')
  cmd =[]
  cmd.append("\"" + mysqldump_path + "\"")  
  cmd.append("--opt")
  cmd.append("--single-transaction=TRUE")
  cmd.append("--user="+ mysql_username)
  cmd.append("--password="+ mysql_password)
  cmd.append("--host="+ mysql_ip)
  cmd.append("--protocol=tcp")
  cmd.append("--port="+ str(mysql_port))
  cmd.append("--default-character-set=utf8")
  cmd.append("--single-transaction=TRUE")
  cmd.append("--routines")
  cmd.append("--events \"%s\" > \"%s\\backup_%s_%s.sql\" " % (dbname, databackup_dir_path, dbname,time1_str) )
  #cmd.append(" \n pause")
  strcmd = str.join(" ",cmd )
  print("cmd=>" + strcmd)
  # os.system(strcmd) 
  try: 
      retcode = subprocess.call(strcmd, shell=True) 
      if retcode < 0: 
        print(sys.stderr, "Child was terminated by signal", -retcode )
      else:
        print(sys.stderr, "Child returned", retcode )
  except OSError as e: 
      print(sys.stderr, "Execution failed:", e )


# 删除老文件 15天以前的文件会被删掉
deleteOldFile(databackup_dir_path,15)

#逐个数据库备份
for f in backDatabaseNames: 
  backupmysql(f)

print("执行完成")
 

脚本简单易懂, 就几句话. 改改参数即可.

到此这篇关于基于Python编写MySQL数据库备份脚本的文章就介绍到这了,更多相关Python备份MySQL数据库内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)!

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新数据库

数据库排行榜