Online Resharding MySQL with Vitess VReplication: A Step‑by‑Step Guide

Why you need zero‑downtime resharding

As a single MySQL instance handles more traffic, read and write latency climb and hardware limits are reached. The usual fix—splitting the data across multiple shards—requires taking the application offline if you use traditional dump‑and‑load techniques. Vitess solves this with VReplication, which creates a copy of a table on new shards while continuously streaming changes from the source, allowing reads and writes to keep running.

How VReplication works

VTGate, the Vitess query router, launches a VReplication workflow. The workflow first materializes a target table as a snapshot of the source (the Copying phase). Then it tails the source’s binary log and applies those changes to the target (the Streaming phase). When the target is caught up, the workflow moves to Ready, at which point you can cut over traffic to the new sharding layout with a single command.

Worked example: resharding a commerce keyspace

Assume you start with a keyspace commerce split into two shards: 0 (‑80) and 80 (‑). You want to reshard into four shards: 0, 40, 80, C0.

1. Create the workflow

   Run the following command on a machine that can reach the Vitess cluster (you need the VTGate admin credentials, typically the vitess user):

   vtctl WorkflowCreate \
     -type commerce_reshard \
     -keyspace commerce \
     -source-shard 0 \
     -target-shards 0,40,80,C0 \
     -table user_order \
     -workflow commerce_reshard

   This tells Vitess to copy user_order from shard 0 into the four target shards.

2. Monitor progress

   Check the workflow state repeatedly:

   vtctl WorkflowShow -workflow commerce_reshard

   You will see the state field move from Copying to Streaming and finally to Ready. No output is invented here; the actual values depend on your cluster’s load.

3. Verify before cutover

   When the workflow reports Ready, run a consistency check:

   vtctl VerifyShard -keyspace commerce -shard 0

   This compares source and target row counts and reports any drift. If the check passes, you can proceed.

4. Cut over traffic

   Switch the routing to the new sharding layout:

   vtctl WorkflowComplete -workflow commerce_reshard

   VTGate will start directing new queries to the appropriate target shard while ongoing transactions finish on the source. After a short grace period you can drop the source shard if desired.

Trade‑offs and limitations

• Resource usage: During Copying and Streaming the source shard experiences extra CPU, network, and disk I/O as it serves reads/writes and streams its binary log. Monitor cpu_utilization, network_throughput, and disk_iops to ensure foreground traffic isn’t degraded.
• Disk space: Each target shard must have enough free space for a full copy of the source data plus indexes and temporary files. A quick check is to run df -h on the target nodes and compare the size of the source data directory (SHOW TABLE STATUS sum).
• Replication lag: Heavy write bursts can cause the Streaming phase to lag. You can observe lag via the VReplication state table:

  SELECT * FROM _vt.vreplication WHERE workflow='commerce_reshard';

  The lag_seconds column shows how far behind the target is. If lag grows consistently, consider throttling writes or increasing target resources.

• Operational complexity: Engineers must manage workflow states, handle failures (e.g., network partitions), and plan cutover windows. Automating alerts on workflow state changes helps catch issues early.

Actionable closing

Before running a reshard in production:

1. Test the exact workflow in a staging cluster that mirrors your production hardware.
2. Record baseline metrics (CPU, network, disk) on the source shard.
3. During the Copying and Streaming phases, watch those metrics and the lag_seconds column.
4. Only execute WorkflowComplete after VerifyShard
   reports zero drift and the workflow state is Ready.
5. After cutover, keep the source shard read‑only for a short validation window before dropping it.

By following these steps you can reshard MySQL tables online, keeping your application available while Vitess handles the heavy lifting of data movement and synchronization.