How to Migrate from MySQL to Cloud Spanner with Zero Downtime

MySQL's AUTO_INCREMENT primary key is the first thing that must not survive the migration unchanged. Spanner distributes rows across servers by splitting the keyspace into contiguous ranges — a monotonically increasing id means every new row lands in the same range, on the same small set of servers, at exactly the rate new rows are created. That's the opposite of what Spanner is built for: it scales by spreading write load across ranges, and a hot, ever-growing tail range cannot be split fast enough to keep up, so write latency degrades under load in a way that never shows up in a low-traffic staging test.

The fix is a key that distributes writes: a UUID (random, so writes spread uniformly), or a bit-reversed sequence (reverses the bits of an incrementing counter so sequential source values become effectively random in Spanner's keyspace, while an application join can still recover ordering from the original counter stored as a regular column). Interleaved tables are the second real schema change — Spanner lets a child table (order_items) be physically co-located with its parent row (orders) by declaring INTERLEAVE IN PARENT, which turns what would be a join in MySQL into a colocated read, but only if the child's primary key is prefixed with the parent's key — a schema decision that has no MySQL equivalent to translate from.

A big-bang cutover (stop the app, dump MySQL, load Spanner, restart pointing at Spanner) is simple but forces real downtime proportional to data size — hours for a large orders table. The zero-downtime pattern instead runs both databases simultaneously for a transition period: backfill copies existing MySQL rows into Spanner (via Datastream's MySQL-to-Spanner replication, or a custom batch job for full control over the key transformation), while the application dual-writes every new write to both databases starting from a captured point, so no write during the transition is missed by either side.

Reads stay on MySQL throughout the transition — Spanner is a write-only shadow copy until verification passes. This ordering matters: dual-writing before backfill completes means Spanner has some rows from the backfill and some from live dual-writes with no gap, but you only find out if that's actually true by running a verification pass, not by assuming the two writers agree.

Before cutting reads over to Spanner, run a row-count and checksum comparison for every table, and a sampled row-by-row diff on a meaningful subset (not just counts — two tables can have matching row counts and still disagree on content if a transformation bug silently corrupted a field). A common bug at this stage is a type-precision mismatch: MySQL's DECIMAL(10,2) and Spanner's NUMERIC don't always round identically at the edges, and a checksum comparison catches that in minutes where a production billing discrepancy would take weeks to notice and trace back to the migration.

Once verification passes, flip reads to Spanner behind a feature flag scoped to a small percentage of traffic first, watching error rates and p99 latency before expanding to 100% — the same staged-rollout discipline as any production database cutover. Keep MySQL dual-writing (as a rollback target) for a defined window after full cutover, not indefinitely — the goal is a bounded rollback window, not a permanent second database to maintain.

The realistic failure: a team migrates the orders table keeping the original auto-increment integer as the Spanner primary key (it's the path of least resistance — no application-code change needed) and everything passes staging tests, since staging traffic is too low to trigger the problem. In production, write volume climbs, and one Spanner split (the physical unit Spanner uses to distribute a key range across servers) absorbs 100% of insert traffic because every new order's key is numerically larger than all previous ones — write latency climbs, Spanner's split-management tries to react by splitting the hot range further, but a range that's hot because of the write pattern itself keeps re-concentrating on its newest edge no matter how many times it's split.

The diagnostic evidence is visible in Spanner's monitoring: a single split showing disproportionate CPU and write throughput compared to its siblings, correlated exactly with insert rate on the affected table. The recovery requires a real migration, not a config change — re-key the table with a UUID or bit-reversed id, backfill under the same dual-write discipline as the original migration, and cut over again. Prevention is cheaper than recovery here: choose the distributed-friendly key before the first row is written, not after the incident.