使用python脚本编写,大致流程:
1、使用mysqldump进行数据库脚本的导出
2、对导出的sql文件进行zip分卷压缩(单封邮件附件有大小限制)
3、进行邮件的发送
4、清理30天之前的binlog(可选,不需要的话删除purgeBinlog方法的调用即可)
5、通过sleep简单实现定时操作
完整脚本如下(backup.py):
import os
import time
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication
# 导出数据库文件
def backup(db_name):
sql_path = work_dir+db_name+".sql"
cmd = "mysqldump -h"+db_host+" -u"+db_user+" -p"+db_password+" --databases "+db_name+" > " + sql_path
print(cmd)
os.system(cmd)
return sql_path
# 清理binlog
def purgeBinlog():
cmd = "mysql -h"+db_host+" -u"+db_name+" -p"+db_password+" -e 'PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY);'"
print(cmd)
os.system(cmd)
# 发送邮件
def sendMail(title, content, file_dir, file_name):
subject = title + " " + file_name
msg = MIMEMultipart()
msg["Subject"] = subject
msg["From"] = mail_user
msg["To"] = mail_to
# 文字部分
part = MIMEText(content)
msg.attach(part)
# 附件部分
part = MIMEApplication(open(file_dir + file_name, "rb").read())
part.add_header("Content-Disposition", "attachment", filename=file_name)
msg.attach(part)
# 发送邮件
s = smtplib.SMTP_SSL("smtp.exmail.qq.com", 465, timeout=1800)
s.login(mail_user, mail_password)
print("send "+subject)
s.sendmail(mail_user, mail_to, msg.as_string())
s.close()
# 压缩文件
def zipfile(file_path):
zip_dir = work_dir+"zip/"
if not os.path.exists(zip_dir):
os.mkdir(zip_dir)
path, file_name = os.path.split(file_path)
file_name = file_name.replace(".sql",".zip")
cmd = "zip -r -s "+str(zip_size)+" "+zip_dir+file_name+" "+file_path
print(cmd)
os.system(cmd)
return zip_dir
# 备份及发送
def backupAndSend(db):
sql_file = backup(db)
zip_dir = zipfile(sql_file)
os.remove(sql_file)
title = "backup ["+ db + "] [" + time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + "]"
zip_list = os.listdir(zip_dir)
zip_list.sort()
content = ""
for zip_file in zip_list:
print(zip_file)
content = content + zip_file + "\n"
for zip_file in zip_list:
sendMail(title, content, zip_dir, zip_file)
os.remove(zip_dir + zip_file)
if __name__ == "__main__":
# 工作目录
work_dir = r"./"
# mysql账号
db_host = "db"
db_user = "root"
db_password = "xxxxx"
# 邮件配置
mail_user = "send@qq.com"
mail_password = "xxxxx"
mail_to = "xxxxx@qq.com"
# 分卷压缩配置(M)
zip_size = 32
# 重复等待时间(小时)
wait_time = 3
while True:
try:
backupAndSend("db1")
backupAndSend("db2")
purgeBinlog()
except Exception as e:
print(e)
print("-------")
time.sleep(60 * 60 * 3)
修改注释部分为自己的变量值(数据库连接地址账户密码,以及邮箱账户密码等)
环境依赖:
1、python环境
2、mysql连接客户端
3、linux下的zip压缩软件
环境依赖比较多,直接用docker进行打包,对应Dockerfile
FROM alpine:3.14
MAINTAINER 1102946234@qq.com
RUN sed -i 's/dl-cdn.alpinelinux.org/mirrors.tuna.tsinghua.edu.cn/g' /etc/apk/repositories
RUN apk update
RUN apk add tzdata \
&& cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime \
&& echo "Asia/Shanghai" > /etc/timezone \
&& apk del tzdata
RUN apk add python3 mysql-client zip
WORKDIR /home/app
ADD ./backup.py /home/app/backup.py
ENTRYPOINT ["python3","backup.py"]
备份效果: