Zero‑Downtime Resharding with Vitess VReplication: A Practical Walkthrough

The Problem: Resharding Without Downtime

When a MySQL‑based application grows, a single shard can become a bottleneck. Traditionally, moving data to a new shard required a maintenance window: stop writes, dump the table, load it elsewhere, and restart traffic. For busy services that window can stretch to hours, which is unacceptable for many SLA‑driven systems.

Thesis: VReplication Keeps Traffic Flowing While Data Moves

Vitess VReplication solves this by continuously streaming row changes from a source tablet to a target tablet using the MySQL binary log. As long as the source and target tables are identical, the workflow can run alongside live traffic, allowing you to cut over when the target is caught up.

How VReplication Works

• Source tablet reads its binlog and sends row events.
• Target tablet applies those events in real time.
• The workflow is controlled via vtctl commands and its progress is stored in the _vt.vreplication table.
• Operators can pause, resume, or throttle the stream at any moment.

Worked Example: Creating a VReplication Workflow

Assume you have a local Vitess cluster started with the docker‑compose.yml from vitess/examples. You already have a keyspace commerce with two tablets per shard and a simple table:

CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

1. Insert test data (run on any client that can reach the Vitess VTGate, e.g., mysql protocol):

   INSERT INTO commerce.user (id, name) VALUES (1, 'alice'), (2, 'bob'), (3, 'carol');
   

2. Create the workflow (execute on the VTAdmin host or any machine with vtctl access; you need cluster admin privileges):

   vtctl CreateVReplicationWorkflow \
     -workflow user_sync \
     -keyspace commerce \
     -table user \
     -source_keyspace commerce \
     -target_keyspace commerce_2 \
     -selector \"\" \
     -filter \"\"
   

   This command starts streaming from commerce to a newly created keyspace commerce_2 (which must already contain an empty user table with the same definition).

3. Monitor progress (run periodically; no special permissions beyond read‑only access to VTGate):

   vtctl ShowWorkflow -workflow user_sync
   

   You can also query the Vitess metadata table directly:

   SELECT workflow, src_keyspace, src_table, copied_rows, lag_seconds, state
   FROM _vt.vreplication
   WHERE workflow = 'user_sync';
   

   Watch copied_rows increase and lag_seconds drop toward zero.

4. Verify real‑time sync while the workflow runs:

   # In a separate session, keep inserting into the source
   INSERT INTO commerce.user (id, name) VALUES (4, 'dave');
   
   # After a few seconds, check the target
   SELECT * FROM commerce_2.user WHERE id = 4;
   

   If the row appears, the stream is applying changes live.

5. Cut‑over traffic (requires admin rights):
   1. Stop the workflow to finalize the cut‑over point:

      vtctl StopWorkflow -workflow user_sync
      

   2. Promote the target tablets to PRIMARY (or the appropriate type) and reparent the application’s VTGate routing:

      vtctl SetTabletType -tablet_type PRIMARY -cells all -keyspace commerce_2
      

   3. Update your application’s connection string or Vitess routing rules to point to commerce_2.
   4. Run a quick consistency check (e.g., row counts, checksums) to confirm no data loss.

Trade‑offs and Limitations

• Write load on the source: Heavy traffic increases binlog parsing and network usage. You can throttle with the vreplication_max_qps flag or adjust vreplication_batch_size to reduce lag.
• Schema identity required: Any difference in columns, indexes, or foreign keys causes the workflow to fail. Ensure both sides are identical before starting.
• Large BLOB/TEXT/JSON columns: These can spike memory usage during row copy. Tuning vreplication_batch_size (e.g., to 1000 rows) helps keep memory bounded.
• Lag monitoring is essential: Unchecked lag can leave the target stale after cut‑over. Use the lag_seconds metric in _vt.vreplication or expose it via Prometheus and set an alert threshold (e.g., >5 seconds).
• Rollback tooling: Versions prior to 20.0 lack automated reverse‑workflow creation. To roll back, you must manually create a reverse VReplication workflow from the target back to the source and verify consistency before switching traffic back.

Practical Way to Check the Result

After cut‑over, run a simple consistency check on both keyspaces:

SELECT COUNT(*) AS src_cnt FROM commerce.user;
SELECT COUNT(*) AS tgt_cnt FROM commerce_2.user;

If the counts match and a sample checksum (e.g., MD5(GROUP_CONCAT(id ORDER BY id))) is identical, the migration succeeded with zero downtime.

Actionable Closing

VReplication turns what used to be a risky, scheduled outage into a routine, observable operation. Start by testing the workflow in a staging environment, monitor lag_seconds and copied_rows, and only cut over when lag is consistently near zero. Keep an eye on write‑intensive periods and adjust throttling flags as needed. With these practices, you can reshard MySQL workloads in Vitess without interrupting your users.