Learn Vitess - Schema Change & Online Migrations

#Introduction

In the world of Vitess, changing a schema can't be done by pressing Enter on a single server. Tables are spread across many shards, and every shard has its own MySQL. A plain ALTER TABLE on a large table will lock it and cause unacceptable downtime. Episode 10 covers how to change schemas and migrate data safely, without interrupting service.

Episode 10 roadmap: why online migration is needed, the engines behind it (VReplication and online schema change), the vtctlclient workflow, and a zero-downtime migration checklist.

#Why Online Migration Is Needed

Imagine a table with hundreds of millions of rows. Running ALTER TABLE ADD COLUMN directly will lock the table during the process — in MySQL, many ALTER operations use MDL (metadata lock) or rewrite the table. During that time, applications can't write, and on a 24/7 workload that's a disaster.

Vitess provides three paths for schema changes:

• Direct DDL through VTGate — only safe for lightweight changes.
• Online Schema Change (OSC) — uses mechanisms like gh-ost or pt-osc via vreplication to run the ALTER without locking the old table.
• VReplication-based migration — creates a new table with the new schema, copies data incrementally, then switches traffic.

#Online Schema Change

Vitess supports running ALTER through an online mechanism. The way: send the DDL with a special hint through VTGate, and Vitess runs it as an online operation using vreplication:

ALTER TABLE users ADD COLUMN bio varchar(500)

The command above is sent through VTGate with the online option. Vitess creates a temporary table, copies data little by little while monitoring replication, then switches to the new schema. Applications never lose write access.

To monitor an ongoing online migration:

vtctlclient OnlineDDL Show all

vtctlclient OnlineDDL Show lists online migrations, their status (queued, running, complete, or failed), and the progress on each shard.

#VReplication and the Data Migration Flow

VReplication is the binlog-based data copying engine that's the backbone of Vitess data migration: resharding (episode 16), cross-keyspace migration, and online schema changes all use it. Here's how it works:

1. Vitess creates a VReplication stream from source to target.
2. Initial data is copied with a consistent snapshot.
3. Changes that happen after the snapshot are captured from the binlog.
4. The target catches up until lag is near zero.
5. Traffic is switched and the stream is stopped.

Because the migration runs incrementally, the application never stops — that's the essence of a zero-downtime migration.

vtctlclient VReplicationExec -json <tablet-alias> "select * from _vt.vreplication"

vtctlclient VReplicationExec runs a query directly against the _vt.vreplication table on a tablet — the internal table that stores the status of every VReplication stream.

#The vtctlclient Workflow for Migrations

Vitess packages the migration flow as a workflow. Examples include a keyspace migration workflow (MoveTables) or shard merging (ShardMerge). The typical flow:

1. Create the workflow: vtctlclient MoveTables -source=... -tables=... commerce users
2. Monitor progress: vtctlclient VReplicationExec ... status
3. Wait for lag to approach zero.
4. Switch read and write traffic: vtctlclient SwitchTraffic
5. Finish and clean up: vtctlclient Complete

vtctlclient MoveTables --source=commerce --tables=orders --target=users create order_move
vtctlclient Workflow --keyspace=users show order_move
vtctlclient MoveTables --source=commerce --target=users order_move switchtraffic
vtctlclient MoveTables --source=commerce --target=users order_move complete

The vtctlclient Workflow ... show command displays the workflow status — which steps are done and how much replication lag remains.

#Zero-downtime Migration Checklist

Before running a large migration in production, prepare this checklist:

• Backup: make sure the latest backup is available before migrating.
• Dry run in staging: run the full migration in a staging environment with similar data.
• Monitor lag: don't switch before replication lag approaches zero.
• Rollback plan: know exactly how to cancel a migration if it fails midway.
• Time window: pick a low-traffic time for large migrations.
• Communication: let relevant teams know a migration is running.

#Closing

In this episode 10 you understood how to change schemas and migrate data without downtime: direct DDL only for lightweight changes, online schema changes for ALTER on large tables, VReplication as the data copying engine, and the MoveTables workflow flow with the create, switch, and complete stages.

Key takeaways:

• Direct DDL is only safe for lightweight schema changes.
• Online schema changes use VReplication so ALTER doesn't lock the table.
• VReplication copies a snapshot, then catches up from the binlog.
• Migration workflow: create, monitor lag, switchtraffic, complete.
• vtctlclient Workflow show is the tool for monitoring migration status.
• Always back up, dry run in staging, and have a rollback plan.

In the next episode, episode 11, we handle configuration and secrets: configuration management and secrets — customizing Helm and ConfigMaps, managing secrets for MySQL credentials and topology, and validating and rolling back production changes. See you there!