Data Migration Strategies: Dual‑Write and Gray‑Scale Switch Approaches

As business services grow, separating a module into an independent service with its own database becomes necessary for stability and scalability, raising questions about migration downtime, data transfer, and post‑migration consistency.

1. Background

The migration must consider whether downtime is allowed, how to move data from the old to the new database, and how to guarantee data consistency after the switch.

2. Data Migration Schemes

Two approaches are discussed: a dual‑write scheme and a gray‑scale switch scheme.

2.1 Scheme 1: Dual‑Write New and Old Databases

The dual‑write process involves synchronizing data, enabling dual writes, reading from the new database for verification, and finally cleaning up old‑database code.

Step 2: New‑Old Data Sync – DBA assists in full data migration and incremental sync.

Step 3: Enable Dual‑Write – Deploy code changes, verify data consistency, then cut off sync and start dual writes.

Step 4: Read New DB – Switch read traffic to the new DB for validation, with the ability to roll back.

Step 5: Code Cleanup – Fully switch read/write traffic to the new DB and remove old‑DB code.

Dual‑write offers seamless user experience but introduces cross‑database transaction complexity and requires a choice between synchronous and asynchronous writes.

2.2 Scheme 2: Gray‑Scale Switch

This method avoids dual writes by using a feature flag to control whether the service accesses the old or new database.

Step 2: New‑Old Data Sync – DBA performs full migration followed by incremental sync.

Step 3: Validate Read New DB – Deploy refactored service, keep incremental sync, enable read‑new‑DB flag for verification, and revert if issues arise.

Step 4: Switch Write Traffic – Cut off writes to the old DB, let incremental sync catch up, verify consistency, then switch writes to the new DB.

Step 5: Code Cleanup – Route all read/write traffic to the new DB.

A short downtime is required during the write‑traffic cut‑over, but the approach avoids cross‑database transactions and reduces code‑change effort.

3. Migration Details

Given the presence of declarative and programmatic transactions, the gray‑scale switch was chosen.

3.1 Business Code Refactor

DAO calls are replaced with a ProxyDAO that checks a feature flag to decide which database to use.

3.2 Data Synchronization

After creating the new database, the DBA performs a full data copy and then uses PingCAP's Syncer for incremental sync, requiring MySQL 5.5‑<8.0, binlog format ROW, and binlog_row_image set to FULL.

3.3 Data Consistency Verification

Consistency checks are performed at both the DBA level and by the service via periodic sampling. Since Syncer masquerades as a MySQL slave, traditional show slave status checks are unsuitable; instead, PingCAP's sync‑diff‑inspector is used.

The inspector works by splitting tables into chunks, computing checksums in parallel, and generating repair SQL for mismatched chunks.

Chunk division using producer‑consumer model.

Consumer threads compute upstream and downstream checksums.

When checksums differ, a binary search isolates divergent rows and produces fix statements.

Full‑table verification is time‑consuming; therefore, after an initial full check of cold data, subsequent checks focus on recently modified rows using an update‑time filter.

Sample SQL for incremental verification:

select * from table where update_time >= X;

Parallel queries reduce latency, and the time difference (N‑S) between the two databases is kept within milliseconds for indexed update_time columns.

4. Summary

The final choice was a conservative approach: switch write traffic to the old DB’s replica, perform incremental consistency checks, then switch writes to the new DB. Read traffic remained uninterrupted, and write failures during the brief cut‑over lasted no more than five seconds.

This solution balances low implementation cost, minimal business impact, and sufficient data safety for the specific business scenario.

5. References

TiDB Syncer overview: https://cn.pingcap.com/blog/tidb-syncer

PingCAP sync‑diff‑inspector documentation: https://docs.pingcap.com/zh/tidb/stable/sync-diff-inspector-overview