Data Migration

12002

The Significance and Necessity of Data Migration

Data migration is the process of transferring data from one storage system, database, or application to another. Its significance and necessity are primarily reflected in the following aspects:

• Platform Migration: When business requirements, technology stack, or infrastructure change, it is necessary to migrate data from one platform to another to ensure a smooth transition of the system.
• Version Upgrade: During the upgrade of a database engine or application version, data migration may be required to accommodate new data structures or standards.
• Merging and Splitting: When companies merge, departments split, or businesses consolidate, relevant data needs to be migrated to maintain data consistency.
• Data Cleaning: During data cleaning and optimization, it may be necessary to move data from one table or database to another for better management and maintenance.
• Legacy System Migration: When migrating from a legacy system to a modern system, historical data needs to be migrated to the new system to preserve business integrity and traceability.

1. Solution One: Navicat Copy Table

Navicat is a database management tool that allows data to be copied from one table to another using its copy table feature. This solution is suitable for simple data migration operations, especially when the data volume is not large. For example, copying data from the old users_old table to the new users_new table:

CREATE TABLE users_new AS SELECT * FROM users_old;  

2. Solution Two: Using MySQL View to Migrate Old Table to New Table

Using MySQL View allows the creation of virtual tables, with data sourced from one or more tables. This solution is suitable for situations where logical transformation or filtering of the source table is required.

• Step One: Prepare the data for the new table using MySQL view

DROP VIEW IF EXISTS `view01_migrate_student_new`;  
CREATE VIEW view01_migrate_student_new AS  
SELECT  
    `student`.`id` AS `id`,  
    `student`.`studentId` AS `studentId`,  
    `student`.`name` AS `studentName`,  
    `student`.`gender` AS `studentGender`,  
    `student`.`dateOfBirth` AS `dateOfBirth`,  
    `student`.`classId` AS `classId`,  
    `student`.`level` AS `level`,  
    `student`.`bodyHeight` AS `bodyHeight`,  
    `student`.`studentStatus` AS `studentStatus`,  
    `student`.`remarks` AS `remarks`,  
    `student`.`operation` AS `operation`,  
    `student`.`operationByUserId` AS `operationByUserId`,  
    `student`.`operationByUser` AS `operationByUser`,  
    `student`.`operationAt` AS `operationAt`  
FROM  
    `student`;  

• Step Two: Write view01_migrate_student_new into the student_new table

  -- Multiple executions require deletion of previous data  
  INSERT INTO student_new SELECT * FROM view01_migrate_student_new;  

3. Solution Three: Using JavaScript Script for Data Processing and Replacement

JavaScript scripts can process and replace data in a scripting language environment. This solution is suitable for complex data migration needs, such as when data transformation, cleaning, or merging is required. For example, using a Node.js script to connect two databases and migrate expired products from the products_old table to the products_archive table:

const oldProducts = connectToOldDatabase().query('SELECT * FROM products_old WHERE expiration_date < NOW()');
connectToNewDatabase().query('INSERT INTO products_archive SELECT * FROM ?', [oldProducts]);

Using scripts is a relatively cumbersome method, but it is also the most compatible method, allowing for easy handling of data issues arising from any structural differences.

const knex = require('knex')({
  client: 'mysql',
  connection: {
    host: 'source_database_host',
    user: 'source_database_username',
    password: 'source_database_password',
    database: 'source_database_name'
  }
});

async function migrateData() {
  const oldList = await knex('student').select();
  const newList = oldList.map(old => {
    const { name: studentName, gender: studentGender, ...other } = old;
    return { studentName, studentGender, ...other };
  })
  // Note: Multiple executions require deletion of previous data
  await targetDb('student_new').insert(newList);
}

Choosing the appropriate data migration solution depends on specific business needs, data structure, and migration complexity. It is recommended to make thorough plans and backups before performing data migration to ensure data security and integrity.