Nodejs实现MySQL数据迁移优化版本

Nodejs实现MySQL数据迁移优化版本

概述

主要是在之前写的基础版本上，加了异步和mysql2依赖的使用，同时使用了批量插入优化了迁移性能。下面做一个简单的演示，以下代码就不做详细的解释，注释已经大致讲明白了。

创建测试数据库

先建立目标数据库来测试

const mysql = require('mysql2/promise');

// 创建测试数据库
async function createTestDatabase() {
  const connection = await mysql.createConnection({
    host: 'target_host',    // 替换为你的目标数据库MySQL账号信息
    user: 'target_user',
    password: 'target_password' 
  });

  const databaseName = 'test_db';

  try {
    // 创建目标数据库
    await connection.query(`CREATE DATABASE IF NOT EXISTS ${databaseName}`);
    console.log(`Database "${databaseName}" created successfully!`);

    // 使用创建的数据库
    await connection.query(`USE ${databaseName}`);

    // 创建目标数据库用户表
    await connection.query(`CREATE TABLE IF NOT EXISTS simple_users (
      id INT PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(50) NOT NULL,
      email VARCHAR(50) NOT NULL
    )`);
    console.log('Table "simple_users" created successfully!');

  } catch (err) {
    console.error('Failed to create test database:', err);
  } finally {
    // 关闭数据库连接
    await connection.end();
  }
}

// 创建测试数据库和插入测试数据
createTestDatabase().catch((err) => {
  console.error('Test database creation failed:', err);
});

再建立源数据库，同时插入十万条测试数据

const mysql = require('mysql2/promise');

// 创建测试数据库
async function createTestDatabase() {
  const connection = await mysql.createConnection({
    host: 'source_host',// 替换为你的源数据库MySQL账号信息
    user: 'source_user',
    password: 'source_password' 
  });

  const databaseName = 'test_db';

  try {
    // 创建源数据库
    await connection.query(`CREATE DATABASE IF NOT EXISTS ${databaseName}`);
    console.log(`Database "${databaseName}" created successfully!`);

    // 使用创建的数据库
    await connection.query(`USE ${databaseName}`);

    // 创建源数据库用户表
    await connection.query(`CREATE TABLE IF NOT EXISTS users (
      id INT PRIMARY KEY AUTO_INCREMENT,
      firstName VARCHAR(50) NOT NULL,
      lastName VARCHAR(50) NOT NULL,
      email VARCHAR(50) NOT NULL
    )`);
    console.log('Table "users" created successfully!');
    // 插入10w条测试数据
    const testData = [];
    for(var i = 0; i < 100000; i++){
      testData.push(["Zhan"+i,"runyuan"+i,"EXAMPLE@qq.com"]);
    }
    await connection.query('INSERT INTO users (firstName, lastName, email) VALUES ?', [testData]);
    console.log(`${testData.length} users inserted successfully!`);
  } catch (err) {
    console.error('Failed to create test database:', err);
  } finally {
    // 关闭数据库连接
    await connection.end();
  }
}

// 创建测试数据库和插入测试数据
createTestDatabase().catch((err) => {
  console.error('Test database creation failed:', err);
});

执行完毕后，在源数据库MySQL中输入select count(*) from test_db.users;

可以看到十万条数据已经插入：

编写node迁移脚本

随后开始编写脚本进行迁移

// 引入依赖
const mysql = require('mysql2/promise');
const log4js = require('log4js');

// 配置日志，此处格式需要自定义
log4js.configure({
  appenders: {
    errorLog: { type: 'file', filename: 'error.log' }
  },
  categories: {
    default: { appenders: ['errorLog'], level: 'error' }
  }
})
const logger = log4js.getLogger()

// 数据迁移配置
const migrationConfig = {
  batchSize: 1000, // 每批处理的数据量
  sourceDB: {
    host: 'source_host', // 替换为你的源数据库MySQL账号信息
    user: 'source_user',
    password: 'source_password',
    database: 'test_db',
    connectionLimit: 10,  // 设置连接数限制
    queueLimit: 100,  // 设置队列限制
    acquireTimeout: 30000,  // 设置获取连接的超时时间为30秒
    waitForConnections: true,  // 等待连接可用
    acquireRetryCount: 3,  // 获取连接的重试次数
    acquireRetryWait: 1000,  // 获取连接的重试间隔时间为1秒
    supportBigNumbers: true,
    bigNumberStrings: true
  },
  targetDB: {
    host: 'target_host', // 替换为你的目标数据库MySQL账号信息
    user: 'target_user',
    password: 'target_password',
    database: 'test_db',
    connectionLimit: 20,  // 设置连接数限制
    queueLimit: 200,  // 设置队列限制
    acquireTimeout: 5000,  // 设置获取连接的超时时间为5秒
    waitForConnections: true,  // 等待连接可用
    acquireRetryCount: 5,  // 获取连接的重试次数
    acquireRetryWait: 500,  // 获取连接的重试间隔时间为0.5秒
    supportBigNumbers: true,
    bigNumberStrings: true
  }
};

// 创建源数据库连接池
const sourcePool = mysql.createPool(migrationConfig.sourceDB);

// 创建目标数据库连接池
const targetPool = mysql.createPool(migrationConfig.targetDB);

// 从源数据库连接池获取连接
async function getConnectionFromSourcePool() {
  try {
    return await sourcePool.getConnection();
  } catch (err) {
    console.error('Failed to get connection from source pool:', err);
    throw err;
  }
}

// 从目标数据库连接池获取连接
async function getConnectionFromTargetPool() {
  try {
    return await targetPool.getConnection();
  } catch (err) {
    console.error('Failed to get connection from target pool:', err);
    throw err;
  }
}

// 从源数据库中选择数据并迁移到目标数据库
async function migrateData() {
  let sourceConnection, targetConnection;

  try {
    sourceConnection = await getConnectionFromSourcePool();
    targetConnection = await getConnectionFromTargetPool();

    let offset = 0;
    while (true) {
      const [rows] = await sourceConnection.query(`SELECT * FROM users LIMIT ${offset}, ${migrationConfig.batchSize}`);

      if (rows.length === 0) {
        // 没有更多数据，数据迁移完成
        break;
      }

      const users = rows.map(row => [row.id, `${row.firstName} ${row.lastName}`, row.email.toLowerCase()]); // 对批量插入的数据进行数据转换

      try {
        await targetConnection.query('INSERT INTO simple_users (id, name, email) VALUES ?', [users]); // 批量插入
        users.forEach(user => {
          console.log(`User with ID ${user[0]} inserted successfully!`);
        });
      } catch (err) {
        // 处理迁移过程中的错误
        logger.error(`Error inserting users: ${users.map(user => user[0]).join(', ')}`, err);
        console.error('Failed to insert users:', err);
        // 可以选择继续处理下一批数据或者中断迁移
        continue;
      }

      offset += migrationConfig.batchSize;
    }

    console.log('Data migration completed successfully!');
  } catch (err) {
    console.error('Data migration failed:', err);
    throw err;
  } finally {
    // 释放连接回连接池
    if (sourceConnection) {
      sourceConnection.release();
    }
    if (targetConnection) {
      targetConnection.release();
    }
  }
}

// 执行数据迁移
migrateData().catch((err) => {
  console.error('Data migration failed:', err);
});

执行完毕后，十万条数据转化迁移成功，我源数据库和目标数据库用是都是双核2G的服务器，十万条数据迁移花了30s左右。

最后到目标数据库MySQL中输入select *count*(*) from test_db.simple_users;

可以看到十万条数据已经迁移：

源数据库数据：

目标数据库数据