使用Windows任务计划程序和Python备份Mysql数据库

目标：每日定时自动备份Mysql数据库

方案：

1、安装Python：

　　使用的Python版本是Python3.7.1，下载地址：https://www.python.org/downloads/release/python-371/，安装过程略过。

2、python脚本：

#!/usr/bin/env python3

# -*- coding: utf-8 -*-

from datetime import date,datetime

import os

import zipfile

import re

def search_file(path, pattern):

    result_file_list = []

    with os.scandir(path) as it:

        for entry in it:

            if not entry.name.startswith('.') and entry.is_file():

                if re.fullmatch(pattern, entry.name):

                    result_file_list.append(entry.name)

    return result_file_list

# 开始备份

print("开始备份数据库...")

# 获取当前时间

cur_date = date.today().strftime("%Y%m%d")

# 数据库IP

host = "localhost"

# 数据库名称

db_name = "db"

# 用户名

db_user_name = "root"

# 密码

db_user_pwd = "password"

# 备份目录

backup_dir = "E:\\DataBaseBackup\\db"

# 备份目录不存在则创建

if not os.path.exists(backup_dir):

    os.makedirs(backup_dir)

# 备份文件名

file_name = cur_date + ".sql"

# 压缩文件名

zip_file_name = cur_date + ".zip"

#切换到备份目录

os.chdir(backup_dir)

# 备份数据库命令

run_backup = "D:\\MySql\\mysql-5.7.21-winx64\\bin\\mysqldump.exe --host=" + host + " --user=" + db_user_name + " --password=" + db_user_pwd + " --protocol=tcp --port=3306 --default-character-set=utf8 " + db_name + " > " + file_name

print(run_backup)

# 执行备份

os.system(run_backup)

#文件是否存在

file_exist = os.path.exists(file_name)

if file_exist:

    # 压缩文件

    with zipfile.ZipFile(zip_file_name,"w",zipfile.ZIP_DEFLATED) as myzip:

        myzip.write(file_name)

# 删除文件

os.remove(file_name)

# 备份结束

print("备份数据库成功")

# 清理30天前的过期文件

max_days = 30

old_files = search_file(backup_dir, r"\d{8}.zip")

for old_file in old_files:

    str_date = old_file.split('.')[0]

    dt = datetime.strptime(str_date,"%Y%m%d")

    days = (datetime.today() - dt).days

    if days > max_days:

        os.remove(old_file)