Schema Management
Using Vitess requires you to work with two different types of schemas:
- The MySQL database schema. This is the schema of the individual MySQL instances.
- The VSchema, which describes all the keyspaces and how they're sharded.
The workflow for the VSchema
is as follows:
- Apply the
VSchema
for each keyspace using theApplyVschema
command. This saves the VSchemas in the global topology service. - Execute
RebuildVSchemaGraph
for each cell (or all cells). This command propagates a denormalized version of the combined VSchema to all the specified cells. The main purpose for this propagation is to minimize the dependency of each cell from the global topology. The ability to push a change to only specific cells allows you to canary the change to make sure that it's good before deploying it everywhere.
This document describes the vtctl
commands that you can use to review or update your schema in Vitess.
Note that this functionality is not recommended for long-running schema changes. It is recommended to use a tool such as pt-online-schema-change
or gh-ost
instead.
Reviewing your schema #
This section describes the following vtctl commands, which let you look at the schema and validate its consistency across tablets or shards:
GetSchema #
The GetSchema command displays the full schema for a tablet or a subset of the tablet's tables. When you call GetSchema
, you specify the tablet alias that uniquely identifies the tablet. The <tablet alias>
argument value has the format <cell name>-<uid>
.
Note: You can use the vtctl ListAllTablets
command to retrieve a list of tablets in a cell and their unique IDs.
The following example retrieves the schema for the tablet with the unique ID test-000000100:
GetSchema test-000000100
ValidateSchemaShard #
The ValidateSchemaShard
command confirms that for a given keyspace, all of the replica tablets in a specified shard have the same schema as the primary tablet in that shard. When you call ValidateSchemaShard
, you specify both the keyspace and the shard that you are validating.
The following command confirms that the primary and replica tablets in shard 0
all have the same schema for the user
keyspace:
ValidateSchemaShard user/0
ValidateSchemaKeyspace #
The ValidateSchemaKeyspace
command confirms that all of the tablets in a given keyspace have the the same schema as the primary tablet on shard 0
in that keyspace. Thus, whereas the ValidateSchemaShard
command confirms the consistency of the schema on tablets within a shard for a given keyspace, ValidateSchemaKeyspace
confirms the consistency across all tablets in all shards for that keyspace.
The following command confirms that all tablets in all shards have the same schema as the primary tablet in shard 0 for the user keyspace:
ValidateSchemaKeyspace user
GetVSchema #
The GetVSchema
command displays the global VSchema for the specified keyspace.
GetSrvVSchema #
The GetSrvVSchema
command displays the combined VSchema for a given cell.
Changing your schema #
This section describes the following commands:
ApplySchema #
Vitess offers managed schema migration, and notably supports online schema migrations (aka Online DDL), transparently to the user. Vitess Online DDL offers:
- Non-blocking migrations
- Migrations are asyncronously auto-scheduled, queued and executed by tablets
- Migration state is trackable
- Migrations are cancellable
- Migrations are retry-able
- Lossless, revertible migrations
- Support for declarative migrations
- Support for postponed migrations
- Support for failover agnostic migrations
- Support for concurrent migrations
The ApplySchema command applies a schema change to the specified keyspace on all shards. The command format is: ApplySchema -- {--sql=<sql> || --sql_file=<filename>} <keyspace>
Further reading:
Permitted schema changes #
The ApplySchema
command supports these commands:
CREATE TABLE
,ALTER TABLE
,DROP TABLE
,CREATE VIEW
,ALTER VIEW
,DROP VIEW
in Online DDL- In addition,
CREATE INDEX
,DROP INDEX
,RENAME TABLE
, in non Online DDL
ApplySchema
does not support creation or modifications of stored routines, including functions, procedures, triggers, and events.
ApplyVSchema #
The ApplyVSchema
command applies the specified VSchema to the keyspace. The VSchema can be specified as a string or in a file.
RebuildVSchemaGraph #
The RebuildVSchemaGraph
command propagates the global VSchema to a specific cell or the list of specified cells.