备份db 避免丢失...

鉴于我的服务都是docker部署方式(db, project等) 很多时候 都在迁移服务器... 很经常丢失SQL文件!!!

结合cron定时自动化备份整库

1. 首先 docker ps查看到自己的容器id(运行中的)
2. 构建执行备份的命令
3. 注意修改花括号里面的参数值

shell
docker exec {container_id} /usr/bin/mysqldump -u {user} --password='{password}' {database_name} > {backup_file}

python脚本方式

备份完成/失败 都会给一个通知

python
import os
import subprocess
import time
from operator import itemgetter

import requests

commonPush = f"""
#### %s
[推送内容] %s \n
"""


def pushDeer(title, content, pushKey="PDU25*****f0dsZgH") -> None:
    data = {"text": title, "desp": content, "type": "markdown", "pushkey": pushKey}
    url = 'https://api2.pushdeer.com/message/push'
    response = requests.post(url, data=data).json()
    print(f"{pushKey}: 推送结果: {response}")


def start():
    # 请替换以下的参数为你的实际情况
    container_id = "03****4"  # your_mysql_container_id
    user = "********"  # your_mysql_user
    password = "***********"  # your_mysql_password
    database_name = "db name"  # your_database_name
    backup_dir = "/home/bak/" # your backup sql file path
    backup_file = os.path.join(backup_dir, f"{database_name}_{int(time.time())}.sql")

    # 创建备份命令
    cmd = (f"docker exec {container_id} /usr/bin/mysqldump -u {user} "
           f"--password='{password}' {database_name} > {backup_file}")

    try:
        # 执行备份命令
        # status, output = subprocess.run(cmd, shell=True)
        status, output = subprocess.getstatusoutput(cmd)
        # 自行试错方式打印结果查看
        print(status)
        print(output)
    except Exception as e:
        pushDeer("数据备份失败", commonPush % (database_name, f"数据库备份失败 请检查 {e}"))
    else:
        pushDeer("数据备份成功", commonPush % (database_name, f"数据库备份成功, {backup_file}"))

    # 获取备份目录中的所有文件
    files = [(f, os.path.getmtime(os.path.join(backup_dir, f))) for f in os.listdir(backup_dir) if
             os.path.isfile(os.path.join(backup_dir, f))]

    # 按文件的修改时间排序
    files.sort(key=itemgetter(1))
    # 如果文件数量超过5个，就删除最旧的文件
    while len(files) > 5:
        oldest_file = files.pop(0)[0]
        os.remove(os.path.join(backup_dir, oldest_file))


if __name__ == '__main__':
    start()

使用cron进行任务管理

crontab -e 增加一条 例如 我希望 每天凌晨3点自动执行

shell
0 3 * * * /usr/bin/python3 /home/xxxxxx/dbBakJob.py

ESC :wq

不确认的命令对不对? 试运行即可

shell
/usr/bin/python3 /home/xxxxxx/dbBakJob.py