定时备份数据库到邮箱脚本

使用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"]

备份效果：