在平时的运维过程中,备份数据库这一项是我们必不可少的一个工作,这个事情好比是一把双刃剑,运用好了,会给工作和项目上带来很大效益;反之,如果没有一个好的备份方案,那这将会是一个无底洞
需求由来:公司内,备份数据库的方式可谓是多种多样,每一种备份方式都带有每一个人对它的理解。如果这些备份方案需要给各个项目中的同事去使用,难免会发生使用上的问题,通用性不强。领导后来选择了一位同事的方案(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容器当中,如果读者需要使用内容,还需把代码读懂后再进行修改,感谢阅读



