linux+python自动备份数据库

linux+python自动备份数据库

1.在linux上安装mysqldump：
yum -y install holland-mysqldump.noarch
2.编写python脚本：
#!/usr/bin/python3 python
import pymysql
import os
import time

TIME = time.strftime(“%Y-%m-%d”, time.localtime())
connect = pymysql.connect(host=‘填写IP地址’, user=填写账号’, password=‘填写密码’, db=‘填写数据库名字’, port=3306)
conn = connect.cursor()
os.system(“”“mysqldump -h127.0.0.1 -u"账号” -p"密码" “数据库名字” > /opt/mysqlbackup/sql/数据库名字{0}.sql"“”.format(TIME))

3.执行crontab -e配置定时任务时间
插入：
每天0点更新一次
00 00 * * * python3 /opt/mysqlbackup/zs_database_backup.py && echo “runing succeed!” >> /opt/mysqlbackup/log/mysql.log 2>&1

4.直接备份所有数据库：
mysqldump -uroot -proot --all-databases >/tmp/all.sql
指定备份某些数据库
mysqldump -uroot -proot --databases db1 db2 >/tmp/user.sql
指定某些数据库的某些表
mysqldump -uroot -proot --databases db1 --tables a1 a2 >/tmp/db1.sql

封装Python

import subprocess
from datetime import datetime


class MySQLBackup:
    def __init__(self, host, user, password, db, port):
        """初始化数据库数据
        """
        self.host = host
        self.user = user
        self.password = password
        self.db = db
        self.port = port

    def backup(self):
        """执行shell命令,生成文件
        """
        timestamp = datetime.now().strftime("%Y%m%d%H%M%S")
        output_file = f"backup_{timestamp}.sql"
        command = f"mysqldump -h {self.host} -P {self.port} -u {self.user} -p{self.password} {self.db} > {output_file}"
        subprocess.run(command, shell=True)


if __name__ == '__main__':
    # 使用示例
    backup_instance = MySQLBackup(host='localhost', user='root', password='123456', db='test',port='3306')
    backup_instance.backup()