栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Python

Python定时备份数据库(支持整库、单库、多库备份与邮件通告)

Python 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

Python定时备份数据库(支持整库、单库、多库备份与邮件通告)

在平时的运维过程中,备份数据库这一项是我们必不可少的一个工作,这个事情好比是一把双刃剑,运用好了,会给工作和项目上带来很大效益;反之,如果没有一个好的备份方案,那这将会是一个无底洞

需求由来:公司内,备份数据库的方式可谓是多种多样,每一种备份方式都带有每一个人对它的理解。如果这些备份方案需要给各个项目中的同事去使用,难免会发生使用上的问题,通用性不强。领导后来选择了一位同事的方案(shell脚本+crontab+mailx)来集成了一个定时备份后并发送邮件通知。不久,因为好多同事对Linux的服务配置和shell语言不太精通,导致很多问题的出现。为了使方案更简单、更人性化,我用了一个python脚本集成了这三个动作,使用起来,也都是人性化的配置

一:配置文件(config.py)

#!/usr/bin/python3
#!encoding=utf-8

"""连接数据库信息"""
DB_information ={
    'db_ip':'192.168.1.xxx',
    'db_user':'postgres',
    'db_password':'xxxx',
    'db_database':'postgres',
    'db_port':5432
}

"""数据库容器ID"""
container_id = 'a74ee76ee144'

"""备份目录路径,结尾加 / (必须是已存在的目录)"""
Backup_path = r"C:UsersAdministratorDesktopDB_br"

"""定时执行的时间,为24小时制"""
cron_time = {
    'hour':18,
    'minute':0
}

"""选择备份的模式:1为整库备份 2为单库备份 3为多库备份"""
schm = '1'

"""单库备份,填库名"""
backup_one = 'postgres001'

"""多库备份,填库名"""
backup_double = [
    'data',
    'shilidb',
    'postgres'
]

"""发送邮件"""
#username_send:发送人的用户名  password:发送人邮箱的授权码  username_recv:收件人用户名  port:邮箱端口
email_information = {
    mailserver:'smtp.163.com',
    username_send:'xxxxxxxxxx@163.com',
    password:'xxxxxxxxxxxx',
    username_recv:'xxxxxxxxxx@qq.com',
    port:25
}
"""邮件内容"""
#mail_head:邮件标题  mail_body:邮件内容
email_text = {
    mail_head:'数据库服务器备份通知',
    mail_body:'数据库正在备份,请知晓'
}

二、主程序代码(Postgres_backup.py)

运行方式:nohup python3 Postgres_backup.py  &

#!/usr/bin/python3
#!encoding=utf-8
"""作者:陈浩
   更新时间:2022.5.19
   名称:Python定时备份数据库(整库备份、单库备份、多库备份)
"""
import os
import os.path
import logging
import time
import psycopg2 as pgsql
import traceback
import smtplib
from email.mime.text import MIMEText
from apscheduler.schedulers.blocking import BlockingScheduler
from config import DB_information
from config import Backup_path
from config import cron_time
from config import container_id
from config import schm
from config import backup_one
from config import backup_double
from config import email_information
from config import email_text

class DatabaseBR(object):
    """备份、恢复、定时执行的类"""
    Time = time.strftime('%Y-%m-%d %H:%M:%S')
    Dirtime = time.strftime('%Y%m%d%H')
    Dirbackup = 'pg_back_' + Dirtime
    sql = "select datname from pg_database;"

    logging.basicConfig(level=logging.DEBUG, filename=os.path.abspath(os.path.dirname(__file__)) + '/pgbackup.log',
                        format='%(asctime)s - %(funcName)s{} -%(levelname)s : %(message)s'.format("方法"))
    logging.info("检测备份目录是否存在……")
    if os.path.exists(Backup_path):
        if not os.path.exists(Backup_path + Dirbackup):
            logging.warning("正在创建备份目录")
            os.mkdir(Backup_path + Dirbackup)
        else:
            logging.info("备份目录 %s 已存在"%Dirbackup)
    else:
        logging.error("目录 %s 不存在"%Backup_path)

    def conn_pgsql(self):
        '''连接数据库'''
        logging.info("进入连接数据库的方法")
        conn = None
        try:
            conn = pgsql.connect(database=DB_information['db_database'], user=DB_information['db_user'],
                                 password=DB_information['db_password'], host=DB_information['db_ip'],
                                 port=DB_information['db_port'])
            logging.info("连接Postgres:{}".format(conn))
        except Exception as e:
            logging.error("pgsql连接报错n%s"%traceback.format_exc())
        if conn != None:
            return conn

    def backing(self):
        """备份所有库的方法"""
        con_pg = None
        logging.info("进入执行备份所有库的方法")
        logging.info("正在连接数据库……")
        try:
            con_pg = self.conn_pgsql()
            curs = con_pg.cursor()
            curs.execute(self.sql)
            results = curs.fetchall()
            databases = []
            for result in results:
                result = list(result)
                databases.append(result)
            logging.info("当前的可备份的数据库有n{}".format(databases))
            for database in databases:
                logging.info("开始备份 %s 库"%database[0])
                dumps = "docker exec -i -d {0} /bin/bash -c 'pg_dump -U {1} {2} > /{3}.sql'".format(
                        container_id, DB_information['db_user'], database[0], database[0])
                os.popen(dumps)
                time.sleep(30)
                logging.warning(dumps)
            for cpsql in databases:
                copy_sql = "docker cp {0}:/{1}.sql '{2}'".format(
                            container_id, cpsql[0], Backup_path + self.Dirbackup + '/')
                logging.warning("开始从容器%s中拷贝到宿主机备份目录"%container_id)
                os.popen(copy_sql)
                time.sleep(30)
                logging.warning(copy_sql)
            curs.close()
            logging.warning("提交事务")
            con_pg.commit()
        except Exception as e:
            logging.error("执行备份所有库的方法报错%s"% traceback.format_exc())
        finally:
            con_pg.close()
            logging.info("关闭数据库连接")

    def backup_onedb(self):
        """备份单库的方法"""
        logging.info("进入备份单库的方法")
        con_pg = None
        logging.info("获取到要备份的数据库名称%s"%backup_one)
        logging.info("正在连接数据库……")
        try:
            con_pg = self.conn_pgsql()
            curs = con_pg.cursor()
            curs.execute(self.sql)
            results = curs.fetchall()
            databases = []
            for result in results:
                result = list(result)
                databases.append(result[0])
            if backup_one in databases:
                logging.info("开始备份 %s 库"%backup_one)
                dumps = "docker exec -i -d {0} /bin/bash -c 'pg_dump -U {1} {2} > /{3}.sql'".format(
                        container_id, DB_information['db_user'], backup_one, backup_one)
                os.popen(dumps)
                time.sleep(30)
                copy_sql = "docker cp {0}:/{1}.sql {2}".format(
                            container_id, backup_one, Backup_path + self.Dirbackup + '/')
                logging.warning("开始从容器%s中拷贝到宿主机备份目录" % container_id)
                os.popen(copy_sql)
            else:
                logging.error("数据库 %s 不存在,请检查后重新运行!"%schm)
        except Exception as e:
            logging.error("执行备份单库的方法报错%s"%traceback.format_exc())
        finally:
            con_pg.close()
            logging.info("关闭数据库连接")

    def backup_doubledb(self):
        """备份多个库的方法"""
        con_pg = None
        logging.info("进入备份多个库的方法")
        logging.info("正在连接数据库……")
        try:
            con_pg = self.conn_pgsql()
            curs = con_pg.cursor()
            curs.execute(self.sql)
            results = curs.fetchall()
            database = []
            for result in results:
                result = list(result)
                database.append(result[0])
            for backup_doubles in backup_double:
                if backup_doubles in database:
                    logging.info("开始备份 %s 库"%backup_doubles)
                    dumps = "docker exec -i -d {0} /bin/bash -c 'pg_dump -U {1} {2} > /{3}.sql'".format(
                        container_id, DB_information['db_user'], backup_doubles, backup_doubles)
                    os.popen(dumps)
                    time.sleep(30)
                    copy_sql = "docker cp {0}:/{1}.sql {2}".format(
                        container_id, backup_doubles, Backup_path + self.Dirbackup + '/')
                    logging.warning("开始从容器%s中拷贝到宿主机备份目录" % container_id)
                    os.popen(copy_sql)
                    time.sleep(30)
                else:
                    logging.error("数据库 %s 不存在,请检查后重新运行!"%backup_doubles)

        except Exception as e:
            logging.error("执行备份多库方法报错%s"%traceback.format_exc())
        finally:
            con_pg.close()
            logging.info("关闭数据库连接")

    def emails(self):
        """发送邮件的方法"""
        try:
            mailserver = email_information['mailserver']
            username_send = email_information['username_send']
            password = email_information['password']
            username_recv = email_information['username_recv']

            mail = MIMEText(email_text['mail_body'])
            mail['Subject'] = email_text['mail_head']
            mail['From'] = username_send
            mail['To'] = username_recv

            smtp = smtplib.SMTP(mailserver, port=email_information['port'])
            smtp.login(username_send, password)
            smtp.sendmail(username_send, username_recv, mail.as_string())
            smtp.quit()
        except Exception as e:
            logging.error("执行发送邮件的方法报错%s"%traceback.format_exc())
        else:
            logging.info("执行发送邮件的方法成功,请到邮箱查收!")

    def con_task(self, func):
        """执行定时任务的方法"""
        logging.info("进入执行定时任务的方法")
        try:
            blocking = BlockingScheduler() #实例化父类
            blocking.add_job(func, 'cron', hour=cron_time['hour'],
                             minute=cron_time['minute'])
            blocking.start()
        except (Exception, SystemExit, KeyboardInterrupt) as e:
            logging.error("定时任务执行发生错误%s"%traceback.format_exc())

if __name__ == '__main__':
    """主程序"""
    databasebr = DatabaseBR()
    if schm == '1':
        databasebr.con_task(databasebr.backing)
        databasebr.emails()
    elif schm == '2':
        databasebr.con_task(databasebr.backup_onedb)
        databasebr.emails()
    elif schm == '3':
        databasebr.con_task(databasebr.backup_doubledb)
        databasebr.emails()
    else:
        logging.error("config配置中的'schm'选择的备份模式错误!已结束")

三、日志信息

2022-05-19 13:43:01,436 - DatabaseBR方法 -INFO : 检测备份目录是否存在……
2022-05-19 13:43:01,437 - DatabaseBR方法 -INFO : 备份目录 pg_back_2022051913 已存在
2022-05-19 13:43:01,437 - backing方法 -INFO : 进入执行备份数据库的方法
2022-05-19 13:43:01,437 - backing方法 -INFO : 正在连接数据库……
2022-05-19 13:43:01,437 - conn_pgsql方法 -INFO : 进入连接数据库的方法
2022-05-19 13:43:01,449 - conn_pgsql方法 -INFO : 连接Postgres:
2022-05-19 13:43:01,454 - backing方法 -WARNING : 正在执行sql……
2022-05-19 13:43:01,454 - backing方法 -INFO : 当前的可备份的数据库有
[['postgres'], ['template1'], ['template0'], ['template_postgis'], ['postgres001'], ['data'], ['for study'], ['shilidb']]
2022-05-19 13:43:01,454 - backing方法 -INFO : ['postgres']
2022-05-19 13:43:01,454 - backing方法 -INFO : ['template1']
2022-05-19 13:43:01,455 - backing方法 -INFO : ['template0']
2022-05-19 13:43:01,455 - backing方法 -INFO : ['template_postgis']
2022-05-19 13:43:01,455 - backing方法 -INFO : ['postgres001']
2022-05-19 13:43:01,455 - backing方法 -INFO : ['data']
2022-05-19 13:43:01,455 - backing方法 -INFO : ['for study']
2022-05-19 13:43:01,455 - backing方法 -INFO : ['shilidb']
2022-05-19 13:43:01,455 - backing方法 -WARNING : 提交事务
2022-05-19 13:43:01,457 - backing方法 -INFO : 关闭数据库连接

本次内容就这多,使用起来是不是更简单、更方便呢,值得注意的是:我这里是正对pgsql进行备份的,而且是运行在docker容器当中,如果读者需要使用内容,还需把代码读懂后再进行修改,感谢阅读

转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/917856.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号