Checkpoint‑Based Resumption for Billions‑Row Full Migration

Full migration of more than ten billion rows cannot run uninterrupted; bugs, restarts, or database pressure inevitably interrupt the process. Restarting from the beginning would make the migration infeasible, so a checkpoint‑resume mechanism is essential.

The solution uses a dedicated migration‑progress table that stores the maximum primary‑key ID already scanned. After each batch of rows is processed, the program updates this last_id value. On startup the program reads last_id and continues scanning from that point, ensuring that any number of restarts or redeployments pick up exactly where the previous run left off without manual intervention.

This design makes the migration program stateless; the only state resides in the database tables, not in memory. Consequently, crashes, scaling the number of instances, or redeployments never affect overall progress.

Data is scanned by incrementally increasing primary‑key ranges. The SQL query is straightforward:

SELECT * FROM source_table WHERE id > last_id ORDER BY id ASC LIMIT 2000

. This avoids OFFSET pagination, which would become slower as the offset grows because MySQL must skip all preceding rows. Scanning by primary‑key range always uses the clustered index, keeping query performance stable even after billions of rows and tolerating non‑contiguous IDs caused by deletions or archival.

Each batch processes 2,000 rows and then sleeps for 100 ms. Both the batch size and sleep interval are configurable via Nacos, allowing operators to shrink batches and increase sleep time when the write service is under pressure, or enlarge batches when resources are abundant. Adjustments take effect immediately without code changes or service restarts.

A separate migration‑failure log table records failed records, including source table, source primary key, error type, error message, and a JSON snapshot of the original data. Common failures such as RPC timeouts, dirty historical data, or field‑conversion errors are captured here. This log enables rapid troubleshooting and, when a particular error spikes, quick identification of whether the issue lies in the write service or migration logic.

After fixing the root cause, only the failed records need to be retried; the entire migration does not have to be rerun. In practice, the whole migration system relies on just two tables: one for progress tracking and one for failure logging. For migrations involving tens of billions of rows, the article recommends creating both tables to ensure resumability and traceability of any anomalies.