基于上个版本进行优化与新增
优化:日志输出更详细,触发异常的error信息定位更具体
新增:添加了恢复数据的功能
功能简介:每天定时备份,属于增量备份,同一个库的sql文件,只会产生一个,不存在备份过多导致磁盘满的情况,所以不用担心时间长了清理备份的文件;恢复的功能为手动恢复,按照实际业务的角度来看,一般不会有定时恢复数据的操作,所以恢复的操作需要手动执行。以上阐述的备份与恢复的操作的具体操作方法,请继续往下看
目录结构:基于Linux操作系统,新建一个目录来存放所涉及到的python脚本
python依赖的第三方库(需手动下载):pymysql、apscheduler
在当前目录下,需要手动新建一个文件作为python相互之间引用包的索引,文件名为“__init__.py”
放代码的目录:
备份目录:
配置文件的介绍与使用:DB_config.py(开头的编码一定要加,不然代码中的注释将无法识别!!!)
按照注释,进行修改
#!/usr/bin/python3
#!encoding=utf-8
"""连接数据库信息"""
DB_information ={
'db_ip':'192.168.1.198',
'db_user':'root',
'db_password':'Asd@123!',
'db_port':3306
}
"""备份目录路径,结尾加/"""
Backup_path = "/home/db_backup/"
"""定时执行的时间,为24小时制"""
cron_time = {
'hour':13,
'minute':31
}
执行定时备份:Data_Backup.py
代码不需要修改,运行方式有两种:前台运行python3 Data_Backup.py
后台运行:nohup python3 Data_Backup.py &
备份sql文件的目录,除了跑脚本生成的备份文件,别存放多余的文件和目录,也就是说,备份目录属于一个只读的状态
#!/usr/bin/python3
#!encoding=utf-8
"""作者:陈浩
更新时间:2022.4.25
名称:Python定时备份数据库与恢复
"""
#引入需要的库
import os
import os.path
import logging
import time
import sys
import pymysql
import traceback
from subprocess import getstatusoutput
from datetime import datetime
from apscheduler.schedulers.blocking import BlockingScheduler
import DB_config
class DatabaseBR:
"""备份、恢复、定时执行的类"""
Time = time.strftime('%Y-%m-%d %H:%M:%S')
Dirtime = time.strftime('%Y%m%d%H%M%S')
Dirbackup = DB_config.Backup_path + Dirtime
sql = "show databases;"
logging.basicConfig(level=logging.DEBUG, filename='./sql.log', filemode='a',
format='%(asctime)s - %(levelname)s: %(message)s') #参数filemode:a是追加,w是覆盖
logging.info("检测备份目录是否存在……")
if not os.path.exists(DB_config.Backup_path):
logging.warning("不存在,正在创建中……")
os.mkdir(DB_config.Backup_path)
else:
logging.info("存在!无需创建")
def backing(self):
"""数据库备份的方法"""
con = []
logging.info("进入执行备份数据库的方法")
logging.info("正在连接数据库……")
try:
con = pymysql.connect(host=DB_config.DB_information['db_ip'],
port=DB_config.DB_information['db_port'],
user=DB_config.DB_information['db_user'],
password=DB_config.DB_information['db_password'],
charset='UTF8')
cur = con.cursor()
cur.execute(self.sql)
logging.warning("正在执行sql……")
results = cur.fetchall()
for result in results:
result = list(result)
result = result[0]
logging.warning("开始备份……")
dumps = "mysqldump -u{0} -p{1} -h{2} -P{3} -E -R {4} > {5}.sql".format(
DB_config.DB_information['db_user'],
DB_config.DB_information['db_password'],
DB_config.DB_information['db_ip'],
DB_config.DB_information['db_port'],
result,
DB_config.Backup_path + result
)
logging.warning(dumps)
os.popen(dumps)
cur.close()
logging.warning("提交事务")
con.commit()
except Exception as e:
logging.error("发生未知错误! %s"% traceback.format_exc())
finally:
con.close()
logging.info("关闭数据库连接")
def recovering(self):
"""数据库恢复的方法"""
logging.info("启动恢复数据的方法")
logging.warning("恢复之前,进行>>>删除初始化库sql的方法")
try:
delete_information = os.remove(DB_config.Backup_path + 'information_schema.sql')
delete_mysql = os.remove(DB_config.Backup_path + 'mysql.sql')
delete_performance = os.remove(DB_config.Backup_path + 'performance_schema.sql')
delete_sys = os.remove(DB_config.Backup_path + 'sys.sql')
except Exception as e:
logging.error("删除初始化库sql文件时候,发生异常,已跳过……%s"% traceback.format_exc())
logging.info("正在连接数据库……")
try:
con = pymysql.connect(host=DB_config.DB_information['db_ip'],
port=DB_config.DB_information['db_port'],
user=DB_config.DB_information['db_user'],
password=DB_config.DB_information['db_password'],
charset='UTF8')
cur = con.cursor()
cur.execute('show databases;')
show = cur.fetchall()
databasenames = os.listdir(DB_config.Backup_path)
for databasename in databasenames:
name = databasename.split('.')[0]
cur.execute("create database if not exists %s character set utf8;"% name)
logging.warning("正在重新创建数据库")
cur.close()
logging.info("提交事务")
con.commit()
except Exception as e:
logging.error("发生未知错误!%s"%traceback.format_exc())
finally:
con.close()
logging.info("关闭数据库连接")
list_dirs = os.listdir(DB_config.Backup_path)
logging.info("当前备份的sql文件有:{}".format(list_dirs))
for list_dir in list_dirs:
listname = list_dir.split('.')[0]
try:
logging.warning("开始恢复……")
recover = "mysql -u{0} -p{1} -h{2} -P{3} {4} < {5}.sql".format(
DB_config.DB_information['db_user'],
DB_config.DB_information['db_password'],
DB_config.DB_information['db_ip'],
DB_config.DB_information['db_port'],
listname,
DB_config.Backup_path + listname
)
logging.warning(recover)
os.popen(recover)
logging.info("恢复完毕!")
except Exception as e:
logging.error("恢复失败!%s"% traceback.format_exc())
def con_task(self):
"""执行定时任务的方法"""
logging.info("进入执行定时任务的方法")
try:
blocking = BlockingScheduler() #实例化父类
blocking.add_job(self.backing, 'cron', hour=DB_config.cron_time['hour'],
minute=DB_config.cron_time['minute'])
blocking.start()
except (Exception, SystemExit, KeyboardInterrupt) as e:
logging.error("定时任务执行发生错误%s"%traceback.format_exc())
if __name__ == '__main__':
"""主函数"""
databasebr = DatabaseBR()
databasebr.con_task()
执行数据恢复:Data_recover.py
这里可以发现,与备份的代码一样(问:为什么一样?答:方便维护管理)如果你懂python,可以自行拆解代码,这里不做过多讲述(与备份的代码对比,只变动了最后一行的内容)
运行:python3 Data_recover.py
#!/usr/bin/python3
#!encoding=utf-8
"""作者:陈浩
更新时间:2022.4.25
名称:Python定时备份数据库与恢复
"""
#引入需要的库
import os
import os.path
import logging
import time
import sys
import pymysql
import traceback
from subprocess import getstatusoutput
from datetime import datetime
from apscheduler.schedulers.blocking import BlockingScheduler
import DB_config
class DatabaseBR:
"""备份、恢复、定时执行的类"""
Time = time.strftime('%Y-%m-%d %H:%M:%S')
Dirtime = time.strftime('%Y%m%d%H%M%S')
Dirbackup = DB_config.Backup_path + Dirtime
sql = "show databases;"
logging.basicConfig(level=logging.DEBUG, filename='./sql.log', filemode='a',
format='%(asctime)s - %(levelname)s: %(message)s') #参数filemode:a是追加,w是覆盖
logging.info("检测备份目录是否存在……")
if not os.path.exists(DB_config.Backup_path):
logging.warning("不存在,正在创建中……")
os.mkdir(DB_config.Backup_path)
else:
logging.info("存在!无需创建")
def backing(self):
"""数据库备份的方法"""
con = []
logging.info("进入执行备份数据库的方法")
logging.info("正在连接数据库……")
try:
con = pymysql.connect(host=DB_config.DB_information['db_ip'],
port=DB_config.DB_information['db_port'],
user=DB_config.DB_information['db_user'],
password=DB_config.DB_information['db_password'],
charset='UTF8')
cur = con.cursor()
cur.execute(self.sql)
logging.warning("正在执行sql……")
results = cur.fetchall()
for result in results:
result = list(result)
result = result[0]
logging.warning("开始备份……")
dumps = "mysqldump -u{0} -p{1} -h{2} -P{3} -E -R {4} > {5}.sql".format(
DB_config.DB_information['db_user'],
DB_config.DB_information['db_password'],
DB_config.DB_information['db_ip'],
DB_config.DB_information['db_port'],
result,
DB_config.Backup_path + result
)
logging.warning(dumps)
os.popen(dumps)
cur.close()
logging.warning("提交事务")
con.commit()
except Exception as e:
logging.error("发生未知错误! %s"% traceback.format_exc())
finally:
con.close()
logging.info("关闭数据库连接")
def recovering(self):
"""数据库恢复的方法"""
logging.info("启动恢复数据的方法")
logging.warning("恢复之前,进行>>>删除初始化库sql的方法")
try:
delete_information = os.remove(DB_config.Backup_path + 'information_schema.sql')
delete_mysql = os.remove(DB_config.Backup_path + 'mysql.sql')
delete_performance = os.remove(DB_config.Backup_path + 'performance_schema.sql')
delete_sys = os.remove(DB_config.Backup_path + 'sys.sql')
except Exception as e:
logging.error("删除初始化库sql文件时候,发生异常,已跳过……%s"% traceback.format_exc())
logging.info("正在连接数据库……")
try:
con = pymysql.connect(host=DB_config.DB_information['db_ip'],
port=DB_config.DB_information['db_port'],
user=DB_config.DB_information['db_user'],
password=DB_config.DB_information['db_password'],
charset='UTF8')
cur = con.cursor()
cur.execute('show databases;')
show = cur.fetchall()
databasenames = os.listdir(DB_config.Backup_path)
for databasename in databasenames:
name = databasename.split('.')[0]
cur.execute("create database if not exists %s character set utf8;"% name)
logging.warning("正在重新创建数据库")
cur.close()
logging.info("提交事务")
con.commit()
except Exception as e:
logging.error("发生未知错误!%s"%traceback.format_exc())
finally:
con.close()
logging.info("关闭数据库连接")
list_dirs = os.listdir(DB_config.Backup_path)
logging.info("当前备份的sql文件有:{}".format(list_dirs))
for list_dir in list_dirs:
listname = list_dir.split('.')[0]
try:
logging.warning("开始恢复……")
recover = "mysql -u{0} -p{1} -h{2} -P{3} {4} < {5}.sql".format(
DB_config.DB_information['db_user'],
DB_config.DB_information['db_password'],
DB_config.DB_information['db_ip'],
DB_config.DB_information['db_port'],
listname,
DB_config.Backup_path + listname
)
logging.warning(recover)
os.popen(recover)
logging.info("恢复完毕!")
except Exception as e:
logging.error("恢复失败!%s"% traceback.format_exc())
def con_task(self):
"""执行定时任务的方法"""
logging.info("进入执行定时任务的方法")
try:
blocking = BlockingScheduler() #实例化父类
blocking.add_job(self.backing, 'cron', hour=DB_config.cron_time['hour'],
minute=DB_config.cron_time['minute'])
blocking.start()
except (Exception, SystemExit, KeyboardInterrupt) as e:
logging.error("定时任务执行发生错误%s"%traceback.format_exc())
if __name__ == '__main__':
"""主函数"""
databasebr = DatabaseBR()
databasebr.recovering()
日志文件在你执行脚本的当前目录下会生成一个sql.log文件,备份与恢复的操作全部都会以追加的方式写入
日志内容介绍:sql.log
红框圈起来的,是你需要关注的地方 ,从日志中可以看出,备份的时候是把所有包括mysql初始化库都备份,恢复到时候则 只恢复除mysql初始化库外的,自己创建使用的库
感谢阅读,如果这篇文章能对你有所帮助的话,可以直接使用(建议不要直接复制粘贴,可能产生TabError的异常!运气流属于是)。过程中,请大家多关注日志信息,如遇到代码级别的bug,评论区留言进行反馈!