如何在 Python 中使用 MySQL 数据库进行数据备份和恢复？

一、核心前置说明

1. 备份/恢复的核心思路：
   • 备份：将数据库/表的数据导出为SQL文件（逻辑备份，可读性高、跨版本兼容）。
   • 恢复：将SQL文件中的语句重新执行，还原数据。
2. 环境要求：
   • 若使用mysqldump命令，需确保MySQL的bin目录（如C:\Program Files\MySQL\MySQL Server 8.0\bin）已加入系统环境变量（Windows），或mysqldump在Linux/Mac的PATH中。
   • 仍需安装mysql-connector-python库：pip install mysql-connector-python。

二、方案1：调用mysqldump/mysql命令（推荐，通用稳定）

这是生产环境中最常用的方式，直接调用MySQL官方的mysqldump工具备份，mysql命令恢复，兼容性和稳定性更好。

1. 数据库备份（导出为SQL文件）

import os
import subprocess

def backup_mysql_by_mysqldump(
    host='localhost',
    port=3306,
    user='root',
    password='123456',
    database='test_db',
    backup_path='./mysql_backup.sql'
):
    """
    调用mysqldump备份MySQL数据库
    :param host: 数据库主机
    :param port: 端口
    :param user: 用户名
    :param password: 密码
    :param database: 要备份的数据库名
    :param backup_path: 备份文件保存路径
    """
    try:
        # 构造mysqldump命令（不同系统路径可能需调整）
        cmd = [
            'mysqldump',
            '-h', host,
            '-P', str(port),
            '-u', user,
            '-p' + password,  # 注意-p和密码之间无空格
            database,
            '--default-character-set=utf8mb4'  # 避免中文乱码
        ]

        # 执行命令并将输出写入SQL文件
        with open(backup_path, 'w', encoding='utf8') as f:
            # 执行子进程，捕获输出
            result = subprocess.run(
                cmd,
                stdout=f,
                stderr=subprocess.PIPE,
                encoding='utf8'
            )

        if result.returncode == 0:
            print(f"备份成功！文件保存至：{os.path.abspath(backup_path)}")
        else:
            print(f"备份失败！错误信息：{result.stderr}")

    except Exception as e:
        print(f"备份异常：{str(e)}")

# 调用备份函数
backup_mysql_by_mysqldump(
    host='localhost',
    user='root',
    password='123456',
    database='test_db',
    backup_path='./test_db_backup_20260107.sql'
)

2. 数据库恢复（从SQL文件导入）

def restore_mysql_by_mysql(
    host='localhost',
    port=3306,
    user='root',
    password='123456',
    database='test_db',
    backup_path='./mysql_backup.sql'
):
    """
    调用mysql命令恢复数据库（需先确保数据库存在）
    :param backup_path: 备份SQL文件路径
    """
    # 先检查备份文件是否存在
    if not os.path.exists(backup_path):
        print(f"备份文件不存在：{backup_path}")
        return

    try:
        # 构造恢复命令
        cmd = [
            'mysql',
            '-h', host,
            '-P', str(port),
            '-u', user,
            '-p' + password,
            database,
            '--default-character-set=utf8mb4'
        ]

        # 读取SQL文件并执行
        with open(backup_path, 'r', encoding='utf8') as f:
            result = subprocess.run(
                cmd,
                stdin=f,
                stderr=subprocess.PIPE,
                encoding='utf8'
            )

        if result.returncode == 0:
            print(f"恢复成功！已从 {backup_path} 还原数据至 {database}")
        else:
            print(f"恢复失败！错误信息：{result.stderr}")

    except Exception as e:
        print(f"恢复异常：{str(e)}")

# 调用恢复函数（注意：恢复前确保test_db数据库已存在）
restore_mysql_by_mysqldump(
    host='localhost',
    user='root',
    password='123456',
    database='test_db',
    backup_path='./test_db_backup_20260107.sql'
)

三、方案2：纯Python代码（原生SQL导出，适合简单场景）

无需依赖mysqldump命令，纯通过Python查询数据并生成SQL文件，适合轻量备份（仅数据，不含表结构）。

1. 备份指定表数据

import mysql.connector
from mysql.connector import Error

def backup_table_data(
    host='localhost',
    user='root',
    password='123456',
    database='test_db',
    table='account',
    backup_path='./table_backup.sql'
):
    """备份指定表的数据（生成INSERT语句）"""
    connection = None
    try:
        # 1. 连接数据库
        connection = mysql.connector.connect(
            host=host,
            user=user,
            password=password,
            database=database
        )
        cursor = connection.cursor()

        # 2. 查询表的所有数据
        cursor.execute(f"SELECT * FROM {table}")
        rows = cursor.fetchall()
        # 获取表的字段名
        columns = [desc[0] for desc in cursor.description]

        # 3. 生成INSERT语句并写入文件
        with open(backup_path, 'w', encoding='utf8') as f:
            # 先写删除表数据的语句（恢复时先清空，可选）
            f.write(f"DELETE FROM {table};\n")
            # 遍历数据生成INSERT
            for row in rows:
                # 处理字符串/数值类型，避免语法错误
                values = []
                for val in row:
                    if isinstance(val, str):
                        # 转义单引号，避免SQL语法错误
                        values.append(f"'{val.replace(''', ''')}'")
                    elif val is None:
                        values.append('NULL')
                    else:
                        values.append(str(val))
                insert_sql = f"INSERT INTO {table} ({','.join(columns)}) VALUES ({','.join(values)});\n"
                f.write(insert_sql)

        print(f"表 {table} 备份成功！共 {len(rows)} 条数据，文件路径：{backup_path}")

    except Error as e:
        print(f"备份失败：{e}")
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()

# 备份account表
backup_table_data(table='account', backup_path='./account_backup.sql')

2. 恢复指定表数据

def restore_table_data(
    host='localhost',
    user='root',
    password='123456',
    database='test_db',
    backup_path='./table_backup.sql'
):
    """从SQL文件恢复表数据"""
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host,
            user=user,
            password=password,
            database=database
        )
        connection.autocommit = False  # 事务控制，确保恢复完整
        cursor = connection.cursor()

        # 读取SQL文件并执行
        with open(backup_path, 'r', encoding='utf8') as f:
            sql_commands = f.read().split(';')  # 按分号分割SQL语句

        # 执行每条SQL
        for cmd in sql_commands:
            cmd = cmd.strip()
            if cmd:  # 跳过空行
                cursor.execute(cmd)

        connection.commit()
        print(f"数据恢复成功！已从 {backup_path} 还原至 {database}")

    except Error as e:
        if connection:
            connection.rollback()
        print(f"恢复失败，事务回滚：{e}")
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()

# 恢复account表
restore_table_data(backup_path='./account_backup.sql')