如何在C++中批量复制MySQL数据库的所有表及数据至另一数据库

核心思路 #

通过查询MySQL系统表获取源数据库的所有表名，循环对每个表执行表结构复制和数据迁移操作，同时加入优化手段提升整体效率。

具体实现步骤及代码示例 #

以下示例基于MySQL原生C API实现，需确保已链接MySQL客户端库：

1. 初始化并建立数据库连接
   建立与数据库的连接，后续复用该连接完成所有操作。
2. 获取源库所有表名
   查询INFORMATION_SCHEMA.TABLES系统表，筛选出源库下的用户自定义表。
3. 循环处理每个表
   对每个表依次执行结构复制、数据迁移，过程中加入效率优化操作。

#include <mysql/mysql.h>
#include <iostream>
#include <string>
#include <vector>

// 数据库配置信息
const char* host = "localhost";
const char* user = "your_username";
const char* passwd = "your_password";
const char* source_db = "source_db_name";
const char* target_db = "target_db_name";

// 获取源数据库的所有用户表名
std::vector<std::string> get_all_tables(MYSQL* conn, const char* db_name) {
    std::vector<std::string> tables;
    std::string query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" + std::string(db_name) + "' AND TABLE_TYPE = 'BASE TABLE'";
    
    if (mysql_query(conn, query.c_str())) {
        std::cerr << "获取表列表失败: " << mysql_error(conn) << std::endl;
        return tables;
    }

    MYSQL_RES* res = mysql_store_result(conn);
    MYSQL_ROW row;
    while ((row = mysql_fetch_row(res)) != nullptr) {
        tables.push_back(row[0]);
    }
    mysql_free_result(res);
    return tables;
}

int main() {
    // 初始化MySQL库
    mysql_library_init(0, nullptr, nullptr);
    MYSQL* conn = mysql_init(nullptr);

    // 建立数据库连接
    if (!mysql_real_connect(conn, host, user, passwd, nullptr, 0, nullptr, 0)) {
        std::cerr << "连接数据库失败: " << mysql_error(conn) << std::endl;
        mysql_close(conn);
        return 1;
    }

    // 创建目标数据库（如果不存在）
    std::string create_db_query = "CREATE DATABASE IF NOT EXISTS " + std::string(target_db);
    if (mysql_query(conn, create_db_query.c_str())) {
        std::cerr << "创建目标数据库失败: " << mysql_error(conn) << std::endl;
        mysql_close(conn);
        return 1;
    }

    // 获取源库的所有表名
    std::vector<std::string> tables = get_all_tables(conn, source_db);
    if (tables.empty()) {
        std::cerr << "源数据库中无用户表" << std::endl;
        mysql_close(conn);
        return 1;
    }

    // 优化插入效率：关闭自动提交，批量操作后统一提交
    mysql_autocommit(conn, 0);

    for (const auto& table : tables) {
        // 1. 复制表结构
        std::string create_table_query = "CREATE TABLE IF NOT EXISTS " + std::string(target_db) + "." + table + " LIKE " + std::string(source_db) + "." + table;
        if (mysql_query(conn, create_table_query.c_str())) {
            std::cerr << "创建表 " << table << " 失败: " << mysql_error(conn) << std::endl;
            mysql_rollback(conn);
            mysql_close(conn);
            return 1;
        }

        // 2. 复制表数据
        std::string insert_data_query = "INSERT INTO " + std::string(target_db) + "." + table + " SELECT * FROM " + std::string(source_db) + "." + table;
        if (mysql_query(conn, insert_data_query.c_str())) {
            std::cerr << "插入表 " << table << " 数据失败: " << mysql_error(conn) << std::endl;
            mysql_rollback(conn);
            mysql_close(conn);
            return 1;
        }

        std::cout << "表 " << table << " 复制完成" << std::endl;
    }

    // 提交所有操作，恢复自动提交
    mysql_commit(conn);
    mysql_autocommit(conn, 1);

    std::cout << "所有表及数据复制完成" << std::endl;

    // 清理资源
    mysql_close(conn);
    mysql_library_end();
    return 0;
}

效率优化建议 #

• 禁用自动提交：批量操作时关闭自动提交，完成后一次性提交，减少磁盘IO和事务开销。
• 临时关闭非主键索引：对大表可先执行ALTER TABLE " + target_db + "." + table + " DISABLE KEYS，插入完成后执行ENABLE KEYS，避免每次插入更新索引。
• 分批次插入：单表数据量极大时，分批次查询源数据并批量插入，避免单次操作占用过多内存。
• 调整MySQL参数：临时调大bulk_insert_buffer_size参数，提升批量插入速度。

内容的提问来源于stack exchange，提问作者Charles