用python写一个mysql备份脚本

下面是一个简单的Python脚本，用于备份MySQL数据库：

import os
import time
import datetime
import shutil
import mysql.connector

# MySQL连接参数
db_config = {
    'user': 'username',
    'password': 'password',
    'host': 'localhost',
    'database': 'database_name',
}

# 备份目录
backup_dir = '/path/to/backup/directory/'

# 获取当前日期和时间
now = datetime.datetime.now()
date = now.strftime('%Y-%m-%d')
time = now.strftime('%H-%M-%S')

# 创建备份目录
if not os.path.exists(backup_dir):
    os.makedirs(backup_dir)

# 连接MySQL数据库
try:
    cnx = mysql.connector.connect(**db_config)
    cursor = cnx.cursor()
except mysql.connector.Error as err:
    print("MySQL连接错误：{}".format(err))
    exit(1)

# 备份数据库
backup_file = backup_dir + 'backup-' + date + '-' + time + '.sql'
try:
    with open(backup_file, 'w') as f:
        cursor.execute("SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='" + db_config['database'] + "'")
        tables = cursor.fetchall()
        for table in tables:
            cursor.execute("SHOW CREATE TABLE " + table[0])
            create_table = cursor.fetchone()[1]
            f.write(create_table + ";\n")
            cursor.execute("SELECT * FROM " + table[0])
            rows = cursor.fetchall()
            for row in rows:
                values = "','".join(str(x).replace("'", "\\'") for x in row)
                insert_sql = "INSERT INTO " + table[0] + " VALUES ('" + values + "')"
                f.write(insert_sql + ";\n")
    print("备份已完成，文件名为：{}".format(backup_file))
except mysql.connector.Error as err:
    print("备份失败：{}".format(err))
finally:
    cursor.close()
    cnx.close()

该脚本会连接MySQL数据库，获取所有表的结构和数据，然后将它们保存到一个.sql文件中，该文件将被保存在指定的备份目录中。备份文件的名称将包含日期和时间戳以识别备份的日期和时间。