python备份数据库之发送端代码详情发送端代码详情
本着公开的原则，源代码如下，如果大家有更好更便捷的方式，不妨分享出来，目前是第32版
具体使用方法请看：python备份数据库之 备份程序使用手册（windows版）
#mdir_time=2021-02-20
#eg:wei-database
#to backup database
# 主要实现备份wei数据库到当前目录下面的database_backup目录i下面，备份文件为wei.bak
# 关键性数据库备份命令：Backup Database wei To disk='E:\python2\database_backup\wei.bak'
from socket import *
import configparser
import datetime
import _thread
import pymssql
import time
import os
#创建一个发送服务类
class send_server:
HOST = '0.0.0.0'
PORT = ''
backup_sql_path = ''
def __init__(self,HOST,PORT,backup_sql_path):#初始化一下参数
self.HOST = HOST
self.PORT = PORT
self.backup_sql_path = backup_sql_path
def send_data(self):
ADDR = (self.HOST,self.PORT)
FILE = self.backup_sql_path
# print(FILE)
# 启动socket端口
def socketa():
# 启动socket
server = socket(AF_INET, SOCK_STREAM)
# print(server)
server.bind(ADDR)
# 最大连接5个
server.listen(5)
return server
# 2 设置一个当有 其他服务器 连接上来发送文件的函数 文件以二进制的方式发送给连接上来的服务端
def tcplink(skt, addr):
# 以二进制打开文件
with open(FILE, 'rb') as f:
for data in f:
skt.send(data)
f.close()
log_data5 = time2 + ' 备份文件已经发送完毕>>>>>>'
print_data5 = time2 + ' 备份文件已经发送完毕>>>>>>'
log_print(log_data5, print_data5)
skt.close()
# 3 打印日志并输出
def log_print(log_txt,print_txt):
file = open(current_log_path, 'a', encoding='utf-8') # w 的含义为可进行读写
file.write(log_txt + "\n")
print(print_txt)
file.close()
# 4 设置当有机器连接上这个socket 发送 以二进制发送文件
# 循环监听
server = socketa()
time1 = str(datetime.datetime.now())
time2 = time1.split('.')[0]
time3 = time2.split(' ')[0]
while True:
# 当有socket 连接时
# global addr
# addr1 = str(addr)
# print(addr)
# print(addr1)
skt, addr = server.accept()
# 启动多线程 执行发送函数
# _thread.start_new_thread(tcplink, (skt, addr))
tcplink(skt, addr)
addr1 = addr[0]
time.sleep(10)
server.close()
log_data6 = time2 + ' 正在清理3天前文件>>>>>>'
print_data6 = time2 + ' 正在清理3天前文件>>>>>>'
rm_path_ls()
time.sleep(5)
break
#文件超过三天前处理
class ls_pwd():
file_name = ''
date = ''
def __init__(self,file_name,date):
self.file_name = file_name
self.date = date
def rm_backup(self):
# 获取当前时间
# print(self.file_name)
today = datetime.datetime.now()
# 计算偏移量,前3天
riqi = self.date
offset = datetime.timedelta(days=-self.date)
# 获取想要的日期的时间,即前3天时间
re_date = (today + offset)
# 前3天时间转换为时间戳
re_date_unix = time.mktime(re_date.timetuple())
# print("当前日期",today.strftime('%Y-%m-%d')) # 当前日期
# print("前3天日期",re_date.strftime('%Y-%m-%d')) # 前3天日期
file_name_rm = current_database_backup_path + '\\' + self.file_name
file_time = os.path.getmtime(file_name_rm) # 文件修改时间
timeArray = time.localtime(file_time) # 时间戳->结构化时间
otherStyleTime = time.strftime("%Y-%m-%d %H:%M:%S", timeArray) #格式化时间
# print("文件修改时间",otherStyleTime)
if file_time <= re_date_unix:
# print(time2+' 文件名为：',self.file_name,' 已经超过3天,需要删除')
log_data7 = time2+' 文件名为：'+self.file_name+' 已经超过3天,需要删除'
print_data7 = time2+' 文件名为：'+self.file_name+' 已经超过3天,需要删除'
log_print(log_data7, print_data7)
#进行删除文件，未测试，等待测试
os.remove(self.file_name)
else:
# print(time2+' 文件名为：',self.file_name," 未超过3天,无需处理!")
log_data8 = time2 + ' 文件名为：'+ self.file_name+ ' 未超过3天,无需处理!'
print_data8 = time2 + ' 文件名为：'+ self.file_name+ ' 未超过3天,无需处理!'
log_print(log_data8, print_data8)
# 连接数据库，执行备份操作
class conn_data_backup():
database_host = ''
database_port = ''
database_user = ''
database_password = ''
def __init__(self, database_host, database_port, database_user, database_password):
self.database_host = database_host
self.database_port = database_port
self.database_user = database_user
self.database_password = database_password
def run(self):
conn = pymssql.connect(host=self.database_host, port=self.database_port, user=self.database_user,
password=self.database_password)
if conn:
print(time2, " 连接数据库", self.database_host, "成功!")
e = str(time2 + ' 连接数据库' + self.database_host + '成功')
log_write(e)
else:
print(time2, " 连接数据库", self.database_host, "没有成功! 请仔细检查防火墙、数据库tcp/ip动态端口是否开启或者配置文件配置是否正确")
e1 = str(time2 + ' 连接数据库' + self.database_host + '出现问题，请仔细检查防火墙、数据库tcp/ip动态端口是否开启或者配置文件配置是否正确')
log_write(e1)
time.sleep(5)
conn.autocommit(True)
cursor = conn.cursor()
#backup_sql_path = current_database_backup_path + "\\" + time3 + "_" + backup_database + ".bak"
# print(backup_sql_path)
sql = "Backup Database " + backup_database + " To disk=" + '\'' + backup_sql_path + '\''
cursor.execute(sql)
sql1 = str("Backup Database " + backup_database + " To disk=" + '\'' + backup_sql_path + '\'')
f2 = str(time2 + ' 数据库执行备份操作已完成,备份语句为:' + sql1)
print(time2, ' 数据库执行备份操作已完成,备份语句为:' + sql1)
log_write(f2)
conn.commit()
time.sleep(10)
# 断开与数据库的连接
conn.close()
print(time2, " 已断开与数据库", self.database_host, "的连接!")
time.sleep(10)
f1 = str(time2 + " 已断开" + self.database_host + '数据库的连接!')
log_write(f1)
f3 = time2 + ' 正在准备发送备份文件到备份服务器上>>>>>>>>>'
print(time2, ' 正在准备发送备份文件到备份服务器上>>>>>>>>>')
log_write(f3)
#清理文件函数，里面调用了上面的清理文件的类
def rm_path_ls():
# path = os.getcwd()
path_ls = os.listdir(current_database_backup_path)
# print(path_ls)
for i in path_ls:
rm_file = ls_pwd(i,3)
rm_file.rm_backup()
else:
time.sleep(5)
log_data6 = time2 + ' 此次'+time2+'备份已正常结束>>>>>>'
print_data6 = time2 + ' 此次'+time2+'备份已正常结束>>>>>>'
log_print(log_data6, print_data6)
#判断日志文件
def path_txt_judge(txt):
if os.path.exists(txt):
print(time2, ' '+txt+'已存在，不需要创建')
file = open(current_log_path, 'a', encoding='utf-8')
# file.write('hello\nword')
a = str(time2 + " "+txt+"文件存在")
# print(a)
file.write(a + "\n")
file.close()
else:
file = open(txt, 'a', encoding='utf-8') # w 的含义为可进行读写
# file.write('hello\nword')
a1 = str(time2 + ' 创建'+txt+'文件' + current_log_path + '成功')
file.write(a1 + "\n")
file.close()
判断配置文件
def path_ini_judge(ini):
if os.path.exists(ini):
print(time2, ' '+ini+'已存在，不需要创建')
file = open(current_log_path, 'a', encoding='utf-8')
# file.write('hello\nword')
a = str(time2 + " "+ini+"文件存在")
# print(a)
file.write(a + "\n")
file.close()
else:
file = open(current_config_path, 'w', encoding='utf-8') # w 的含义为可进行读写
# file.write('hello\nword')
b1 = str(time2 + ' 新建配置文件' + current_config_path + '成功,程序即将退出，请完善配置文件后再次运行该程序')
file.write('[sql_server]\nhost=\nport=\nuser=\npassword=\n[database_name]\ndatabase_name=\nbackup_time=\n[send_config]\nsend_port=\nsend_host=')
file.close()
file = open(current_log_path, 'a', encoding='utf-8') # w 的含义为可进行读写
# file.write('hello\nword')
file.write(b1 + "\n")
file.close()
print(b1)
time.sleep(5)
exit(0)
#判断备份目录
def path_dir_judge(dir):
if os.path.exists(dir):
print(time2, ' 文件目录存在，不需要创建')
file = open(current_log_path, 'a', encoding='utf-8') # w 的含义为可进行读写
# file.write('hello\nword')
d = str(time2 + ' 备份目录' + current_database_backup_path + '存在')
file.write(d + "\n")
file.close()
else:
os.makedirs(dir)
file = open(current_log_path, 'a', encoding='utf-8') # w 的含义为可进行读写
# file.write('hello\nword')
d1 = str(time2 + ' 备份目录' + current_database_backup_path + '创建成功')
file.write(d1 + "\n")
file.close()
#进行日志和屏幕打印（类）
def log_print(log_txt,print_txt):
file = open(current_log_path, 'a', encoding='utf-8') # w 的含义为可进行读写
file.write(log_txt + "\n")
print(print_txt)
file.close()
#单纯的进行日志打印
def log_write(log):
file = open(current_log_path, 'a', encoding='utf-8') # w 的含义为可进行读写
file.write(log + "\n")
file.close()
#程序入口
##锁定当前日期为 2021-02-23 09:05:43格式
time2 = (str(datetime.datetime.now())).split('.')[0]
##配置文件，日志文件和备份目录
current_path = os.getcwd()
current_config_path = current_path + '\\' + 'send_config.ini'
current_log_path = current_path + '\\' + 'send_log.txt'
current_database_backup_path = current_path + '\\' + 'database_send_backup'
##调用配置、日志和备份目录函数
path_txt_judge(current_log_path)
path_ini_judge(current_config_path)
path_dir_judge(current_database_backup_path)
##调用配置文件里面的参数
cf = configparser.ConfigParser()
cf.read(current_config_path)
database_host = cf.get('sql_server', 'host')
database_port = cf.get('sql_server', 'port')
database_user = cf.get('sql_server', 'user')
database_password = cf.get('sql_server', 'password')
backup_database = cf.get('database_name', 'database_name')
backup_time = cf.get('database_name', 'backup_time')
send_port = int(cf.get('send_config', 'send_port'))
send_host = cf.get('send_config', 'send_host')
##通过用两个循环的方式，完美的解决定时执行这个程序，时间可通过配置文件自定义
while 2>1:
time1 = str(datetime.datetime.now())
time11 = time1.split('.')[0]
time3 = time11.split(' ')[0]
tim4 = time.strftime("%H:%M")
if tim4 == backup_time:
lock = 0
file = open(current_log_path, 'w', encoding='utf-8') #w，为重定向，即覆盖之前全部内容，确保日志文件不会太大
# file.write('hello\nword')
a1 = str(time2 + " 已清空"+time2+"之前所有日志")
# print(a)
file.write(a1 + "\n")
file.close()
while 2>1:
lock += 1
tim5 = time.strftime("%H:%M")
if lock == 1:
print(time2, ' 当前时间为备份时间，开始进行备份>>>>>>>')
backup_sql_path = current_database_backup_path + "\\" + time3 + "_" + backup_database + ".bak"
con_database = conn_data_backup(database_host,database_port,database_user,database_password)
con_database.run()
send_data_run = send_server(send_host,send_port,backup_sql_path)
send_data_run.send_data()
elif tim5 != backup_time:
break
else:
print(time2, ' 当前时间为：', tim4, '开始备份时间为：', backup_time, '不是备份时间')
time.sleep(55)
