python 备份数据库脚本

代码如下：

import os
import time
from datetime import datetime
import pymysql
import configparser
 
# 读取配置文件
config = configparser.ConfigParser()
config.read('config.ini')
 
# 连接数据库
def connect_db(host, user, password, db, charset):
    conn = pymysql.connect(host=host, user=user, password=password, db=db, charset=charset)
    return conn
 
# 备份数据库
def backup_db(host, user, password, db, charset, backup_path):
    # 创建数据库备份路径
    if not os.path.exists(backup_path):
        os.makedirs(backup_path)
    
    # 连接数据库
    conn = connect_db(host, user, password, db, charset)
    cursor = conn.cursor()
    
    # 执行数据库备份命令
    today = datetime.now().strftime("%Y%m%d%H%M%S")
    file_path = os.path.join(backup_path, f"{db}_{today}.sql")
    with open(file_path, 'w') as f:
        cursor.execute(f"SHOW CREATE DATABASE {db};")
        database_info = cursor.fetchone()
        f.write(f"/*!40100 SET NAMES utf8 */;\n")
        f.write(f"/*!40100 CREATE DATABASE `{db}` */;\n")
        f.write(database_info[1] + ";\n")
        cursor.execute("SHOW TABLES;")
        for table in cursor.fetchall():
            print(f"正在备份表: {table[0]}")
            cursor.execute(f"SHOW CREATE TABLE {table[0]};")
            create_table_info = cursor.fetchone()
            f.write(f"/*!40100 SET NAMES utf8 */;\n")
            f.write(create_table_info[1].replace(f"{db}.", '') + ";\n")
            cursor.execute(f"LOCK TABLES {table[0]} WRITE;")
            cursor.execute(f"SELECT * FROM {table[0]};")
            for row in cursor.fetchall():
                fields = ', '.join(str(value) for value in row)
                f.write(f"INSERT INTO `{table[0]}` VALUES ({fields});\n")
            cursor.execute("UNLOCK TABLES;")
    conn.close()
    print(f"数据库备份成功: {file_path}")
 
# 执行数据库备份
backup_db(
    config['DATABASE']['host'],
    config['DATABASE']['user'],
    config['DATABASE']['password'],
    config['DATABASE']['db'],
    config['DATABASE']['charset'],
    config['BACKUP']['backup_path']
)