Python实现数据库备份与恢复（定时&手动）

基于上个版本进行优化与新增
优化：日志输出更详细，触发异常的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，评论区留言进行反馈！