python备份mysql数据库

最新推荐文章于 2024-06-05 22:27:43 发布

原创最新推荐文章于 2024-06-05 22:27:43 发布 · 1.2k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#python #python数据库备份

运维同时被 2 个专栏收录

114 篇文章

订阅专栏

python

7 篇文章

订阅专栏

本文介绍了一种自动化MySQL数据库备份的脚本实现方案，通过Python脚本自动化执行数据库备份、压缩及清理工作，确保数据安全并提高管理效率。

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

#!/usr/bin/python3
# -*- coding:utf-8 -*-
import os
import time
import datetime
import logging
import sys

DB_HOST='localhost'
DB_USER='user'
DB_USER_PASSWD='password'
DB_NAME='/data/backup/mysql_from_test/dbname/dbnames.txt'
BACKUP_PATH='/data/backup/mysql_from_test/data/'

DATETIME = time.strftime('%Y%m%d%H%M%S')
TIME = time.strftime('%Y-%m-%d %H:%M:%S')
TODAYBACKUPPATH = BACKUP_PATH + DATETIME

#定义日志备份级别
logging.basicConfig(level=logging.DEBUG,filename='/data/backup/mysql_from_test/logs/mysql_backup_test.log',filemode='w',format='%(asctime)s - %(levelname)s: %(message)s')

# 备份时长
data = 30

#删除备份文件日志
#filerm_log = "/data/backup/mysql_from_test/logs/webapirm.log"

logging.info("检测备份文件夹，不存在则创建!")

#创建备份文件夹
if not os.path.exists(TODAYBACKUPPATH):
	os.makedirs(TODAYBACKUPPATH)

logging.info("检测需要备份的数据库文件%s是否存在,如果存在就继续运行,不存在则脚本退出",DB_NAME)

#定义执行备份脚本，读取文件中的数据库名称，注意按行读写，不校验是否存在该库
def run_backup():
	in_file = open(DB_NAME,"r")
	for dbname in in_file.readlines():
		dbname = dbname.strip()
		logging.info("now starting backup database %s" %dbname)
		dumpcmd = "docker exec -it NSW_mysql mysqldump --opt -t -u" +DB_USER + " -p"+DB_USER_PASSWD+" " +dbname+" > "+TODAYBACKUPPATH +"/"+dbname+".sql"
		logging.info(dumpcmd)
		logging.info("%s数据库备份完成!" %dbname)
		os.system(dumpcmd)
	file1.close()

#执行压缩的函数
def run_tar():
	logging.info('开始压缩%s文件夹' %TODAYBACKUPPATH)
	compress_file = TODAYBACKUPPATH + ".tar.gz"
	compress_cmd = "tar -czvf " +compress_file+" "+DATETIME
	os.chdir(BACKUP_PATH)
	os.system("pwd")
	os.system(compress_cmd)
	logging.info("compress complete!")
	#删除备份文件夹
	remove_cmd = "rm -rf "+TODAYBACKUPPATH
	os.system(remove_cmd)

#执行删除函数，删除前一个月的数据
def run_rm():
	logging.info('开始检测是否有一个月之前的备份数据')
	f = list(os.listdir(BACKUP_PATH))
	now_time = DATETIME[0:8]
	for i in f:
	    if i[-6:] == 'tar.gz':
		exit_time = i[0:8]
		update_time = int(exit_time) + data
		if update_time < int(now_time):
		    logging.info('删除%s %s' %  (BACKUP_PATH,i))
		    os.remove(BACKUP_PATH+i)

#备份数据库文件存在就执行备份和压缩，否则退出
if os.path.exists(DB_NAME):
	logging.info('数据库文件存在,脚本正式运行开始at %s',TIME)
	file1 = open(DB_NAME)
	logging.info("starting backup of all db listed in file "+DB_NAME)
	run_backup()
	run_tar()
	run_rm()
	logging.info('脚本运行结束!')
else:
	logging.error("数据库文件不存在,脚本退出,请新建数据库文件%s后再运行脚本!",DB_NAME)
	exit()