Mastering Data Migration: Proven Strategies for Seamless Database Transitions

Background

In Stephen Chow's Journey to the West a famous line expresses a longing for an unchanging love lasting ten thousand years. Developers feel the same about database data, hoping it never changes, but business growth, sharding, storage changes, and system migrations force data evolution.

Sharding : Rapid business growth increases load and data volume, prompting a move from a single‑node database to multiple nodes. Full data migration is required before the sharded architecture can be used.

Changing storage engine : After sharding, the storage medium may still be MySQL, but complex queries might require switching to a different engine such as Elasticsearch, which adds conversion complexity.

Switching to a new system : Merged projects (e.g., member or e‑commerce platforms) often need data migration to a new platform, possibly involving different languages, storage, and even departments, increasing migration difficulty and risk.

Depending on the scenario, different migration solutions are needed. Below is a discussion of how to migrate data effectively.

Data Migration

Data migration is rarely instantaneous; it can take weeks or months. The typical process resembles the diagram below:

First, bulk‑transfer existing data, then handle new data in real time by writing to both the old and new stores while continuously validating. Once validation shows few issues, perform a cut‑over; after full cut‑over, incremental migration and validation cease.

Bulk Data Migration

Open‑source tools for bulk migration are limited. Alibaba Cloud DTS (Data Transmission Service) supports both homogeneous and heterogeneous migrations across common databases (MySQL, Oracle, SQL Server, etc.). DTS fits the sharding and storage‑engine‑change scenarios.

DTS bulk migration steps:

When the task starts, obtain the maximum and minimum IDs of the data to migrate.

Define a segment size (e.g., 10,000 rows). Query each segment and hand it to DTS for processing. Example SQL:

select * from table_name where id > curId and id < curId + 10000;

3. When the current ID exceeds the maximum ID, the bulk migration ends.

If DTS is unavailable or the migration requires complex field transformations, you can mimic DTS by reading data in batches, controlling segment size and frequency to avoid impacting the production system.

Incremental Data Migration

Incremental migration offers many options:

DTS also provides incremental migration as a paid service.

Dual‑write: Write to both old and new stores within application code. This lacks transactional guarantees across databases and may cause data loss, which must be mitigated by later validation.

MQ asynchronous write: Emit a message on data change; a consumer updates the new store, reducing the risk compared to dual‑write.

Binlog listening: Use tools like Canal or Databus to capture binlog events and replicate them, requiring minimal development and preserving consistency.

Among these, binlog listening is recommended because it minimizes development effort while ensuring data consistency.

Data Validation

Even mature services (DTS, Canal) can lose data, which is hard to debug. Validation is essential:

At Meituan, a "dual‑read" approach reads from the new store while still serving the old store, allowing immediate detection and alerting of discrepancies.

At Yuanfudao, a "T+1" method compares yesterday's updates in the old database with the new one each night, enabling prompt correction.

Key validation considerations:

Ensure the validation task itself is reliable, typically via code reviews.

Control log volume to avoid overwhelming monitoring systems.

Avoid heavy batch queries that could strain the production database.

Cut‑over

After validation shows minimal errors, a gradual cut‑over (gray release) is performed. Cut‑over can be based on user ID modulo, tenant ID, etc. A plan should define time windows, traffic percentages, and start with low traffic (e.g., 1%) before ramping up to higher levels.

Primary Key ID Considerations

During migration, primary key handling is critical. For sharding, avoid auto‑increment IDs; use distributed ID generators like Meituan's open‑source leaf , which offers Snowflake‑style long IDs or segment‑based IDs.

If merging systems with overlapping IDs, reserve ID ranges or map old IDs to new ones. For example, allocate separate numeric ranges for each system, or start new IDs from a large base value, leveraging the 64‑bit space of Long to accommodate many systems.

Summary

The migration workflow consists of four steps: bulk migration, incremental migration, validation, and cut‑over, with special attention to primary key handling. Following this pattern helps avoid major issues regardless of data scale.