mysql增量备份脚本（python3）

最新推荐文章于 2025-06-14 07:38:46 发布

原创最新推荐文章于 2025-06-14 07:38:46 发布 · 708 阅读

CC 4.0 BY-SA版权

本文介绍了一个Python脚本，用于自动化MySQL Binlog日志的备份过程。脚本连接到本地MySQL服务器，获取当前的Binlog文件，将其压缩并保存到指定的备份目录下。完成备份后，脚本会刷新Binlog日志，确保后续的日志记录从新的位置开始。通过定时运行此脚本，可以实现定期的Binlog备份，便于数据库的恢复和管理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

#!/usr/bin/python3
import time
import mysql.connector
import os
import zipfile

binlog_path = '/var/lib/mysql'
backup_path = '/root'
time_YMD = time.strftime("/%Y/%m/%d", time.localtime())
time_MHS = time.strftime("_%H-%M-%S", time.localtime())

def mysql_conn():
    mydb = mysql.connector.connect(
     host="127.0.0.1",
     user="root",
     passwd="cbst789",
     port="3360"
    )
    return mydb

def binlog_zip(inpath,outpath):
    db_file = zipfile.ZipFile(outpath,'w',zipfile.ZIP_DEFLATED)
    db_file.write(inpath)
    db_file.close()

def get_binlog(sql):
    mydb = mysql_conn()
    mycursor = mydb.cursor()
    mycursor.execute(sql)
    for i in mycursor:
       binlog = i
    mycursor.close()
    return binlog

def flush_binlog(sql):
    mydb = mysql_conn()
    mycursor = mydb.cursor()
    mycursor.execute(sql)
    mycursor.close()

def start_backup():
    binlog_file = get_binlog('show master status')
    in_name = binlog_path + '/' + binlog_file[0]
    out_name = backup_path + time_YMD + '/binlog' + time_MHS + '.zip'
    if not os.path.isdir(backup_path + time_YMD):
       os.makedirs(backup_path + time_YMD)
    binlog_zip(in_name,out_name)
    if os.path.isfile(out_name):
       flush_binlog('flush logs')
       new_binlog = get_binlog('show master status')
       print ('备份成功，路径为 {0} 最新binlog日志为 {1}'.format(out_name,new_binlog[0]))
       #os.remove(in_name)    #删除原binlog文件
    else:
       print ('备份失败')

start_backup()