本人博客原来有很多内容，可是一次不小心把数据库删了，尝试恢复无果，因此编写此脚本。

#备份数据库
import os
import datetime
import pymysql
from re import match

PATH = "/home/wang/app/mysql-backup/" #备份到的位置
NUMBER = 7 #保留几天的备份
servers = [
    {
        "host": "x.x.x.x", #数据库地址
        "port": "3306", #数据库端口
        "user": "root", #数据库用户名
        "pwd": "the password", #数据库密码
        "regex": r".*", #用正则表达式筛选要备份的库
    }, #可以在后面放其他数据库信息 格式和上面相同
]

# 删除NUMBER天前的备份
dirs = os.listdir(PATH)
now = datetime.datetime.now()
now = datetime.datetime(now.year, now.month, now.day)
for dir in dirs:
    date_dir = datetime.datetime.strptime(dir, "%Y-%m-%d")
    if (now - date_dir).days >= NUMBER:
        os.system("rm -rf " + os.path.join(PATH, dir))

PATH = os.path.join(PATH, now.strftime("%Y-%m-%d"))
PATH = os.path.abspath(PATH)
if not os.path.exists(PATH):
        os.makedirs(PATH)
print("备份到 %s" % PATH)

os.chdir(PATH)
for server in servers:
    regex = server["regex"]
    today_path = os.path.join(PATH, server["host"])
    today_path = os.path.abspath(today_path)
    if not os.path.exists(today_path):
        os.makedirs(today_path)
    print("备份 %s:%s" % (server["host"], server["port"]))
    conn = pymysql.connect(host=server["host"], port=int(server["port"]), user=server["user"], passwd=server["pwd"])
    cur = conn.cursor()
    cur.execute("show databases")
    databases = cur.fetchall()
    dbs = []
    for database in databases:
        database = database[0]
        if database in ["information_schema", "mysql", "performance_schema", "sys"]:
            continue
        if match(regex, database) is not None:
            dbs.append(database)
    cur.close()
    conn.close()
    for database in dbs:
        file_name = "%s.sql" % database
        file_path = os.path.join(today_path, file_name)
        cmd = "mysqldump -h%s -P%s -u%s -p%s  --opt --extended-insert=false --triggers -R --hex-blob -x %s > %s" % (server["host"], str(server["port"]), server["user"], server["pwd"], database, file_path)
        os.system(cmd)

    
    #压缩today_path
    os.system("tar -zcvf %s.tar.gz %s" % (server["host"], server["host"]))
    #删除today_path
    os.system("rm -rf %s" % server["host"])

在运行之前需要先安装PyMySQL库。

$ pip3 install pymysql

将该文件保存到你想要保存的目录中，并编写cron脚本，每天0点执行。

0 0 * * * /usr/bin/python3 /home/user/python/mysql-backup/backup.py >> /home/user/python/mysql-backup/run.log

效果如下：

效果还不错。