A large sharded MySQL fleet needs a tenant moved between shards with no downtime. How should online data movement, dual writes, cutover and rollback be sequenced?

The sequence

1. Copy the tenant's existing data to the destination shard, in the background, throttled so the source's serving traffic is unaffected. This takes as long as it takes and nothing depends on its speed.

2. Stream ongoing changes from source to destination using the replication log, so the destination catches up and then stays continuously in sync. Measure and monitor the lag, because the cutover window is bounded by it.

3. Verify. Row counts, checksums per table, and a sampled row-level comparison. The verification must be strong enough that you would bet the tenant's data on it, because that is what the cutover does.

4. Cutover. With lag near zero:

• Stop writes for that tenant — a brief write freeze, typically sub-second, with writes queued or rejected with a retryable error rather than lost.
• Drain the remaining replication lag to zero.
• Update the routing metadata so the tenant's traffic goes to the destination.
• Resume writes.

Reads may continue throughout; only writes pause, and only for that tenant. Other tenants are unaffected entirely, which is the property that makes this operationally acceptable.

5. Keep the source data intact, with reverse replication running from destination back to source for a period. This is what makes rollback possible — a routing change back, rather than a data restore.

6. Stop reverse replication and remove the source data, deliberately, after a period long enough for problems to surface.

Why dual writes at the application layer are the wrong approach

The instinct is to have the application write to both databases during the migration. This is substantially worse than log-based replication:

• The two writes are not atomic. A failure between them leaves the databases divergent, silently.
• Ordering is not guaranteed across concurrent writers, so the destination's final state can differ from the source's.
• Every write path must be modified, and any path that is missed — a migration script, an admin tool, an overlooked service — writes to only one side.
• Rollback is unclear, since the divergence is unbounded and undetectable without a full comparison.

Log-based replication has none of these problems: it sees every write, in commit order, including the ones nobody remembered.

What the routing layer must provide

• An atomic switch of the tenant's location, so no request can observe a half-updated mapping.
• A brief, bounded write freeze, coordinated with the drain.
• Correct handling of in-flight transactions at the moment of the switch.
• Fast propagation of the metadata change to every client, or requests continue reaching the old shard.

This is why sharding middleware exists and why hand-rolled application-level sharding makes tenant movement so painful — the routing layer is the hard part, and Vitess-style systems exist substantially to provide it.

The property that matters most

Rollback is a routing change, not a data restore. As long as the source retains the data and reverse replication runs, an unexpected problem after cutover is resolved in seconds. A migration whose rollback plan is "restore from backup" is not a zero-downtime migration, regardless of how the cutover itself was performed.