MoveTables

Move tables between keyspaces without downtime

These workflows can have a significant impact on the source tablets (which are often in production) — especially when a PRIMARY tablet is used as a source. You can limit the impact on the source tablets using the --vreplication_copy_phase_max_* vttablet flags

Description #

MoveTables is used to start and manage workflows to move one or more tables from an external database or an existing Vitess keyspace into a new Vitess keyspace. The target keyspace can be unsharded or sharded.

MoveTables is typically used for migrating data into Vitess or to implement vertical sharding. You might use the former when you first start using Vitess and the latter if you want to distribute your load across servers without sharding tables.

Command #

Please see the MoveTables command reference for a full list of sub-commands and their flags.

The Basic MoveTables Workflow Lifecycle #

  1. Initiate the migration using create
    MoveTables --workflow <workflow> --target-keyspace <target-keyspace> create --source-keyspace <source-keyspace> --tables <tables>
  2. Monitor the workflow using show or status
    MoveTables --workflow <workflow> --target-keyspace <target-keyspace> show
    MoveTables --workflow <workflow> --target-keyspace <target-keyspace> status
  3. Confirm that data has been copied over correctly using VDiff
  4. Cutover to the target keyspace with switchtraffic
    MoveTables --workflow <workflow> --target-keyspace <target-keyspace> switchtraffic
  5. Cleanup vreplication artifacts and source tables with complete
    MoveTables --workflow <workflow> --target-keyspace <target-keyspace> complete

Common Use Cases for MoveTables #

Adopting Vitess #

For those wanting to try out Vitess for the first time, MoveTables provides an easy way to route part of their workload to Vitess with the ability to migrate back at any time without any risk. You point a vttablet to your existing MySQL installation, spin up an unsharded Vitess cluster and use a MoveTables workflow to start serving some tables from Vitess. You can also go further and use a Reshard workflow to experiment with a sharded version of a part of your database.

See this user guide for detailed steps.

Vertical Sharding #

For existing Vitess users you can easily move one or more tables to another keyspace, either for balancing load or as preparation for sharding your tables.

See this user guide which describes how MoveTables works in the local example provided in the Vitess repo.

Parameters #

Action #

MoveTables is an "umbrella" command. The action or sub-command defines the operation on the workflow.

Create #

create sets up and creates a new workflow. The workflow name should not conflict with that of an existing workflow.

Show #

show displays useful information about a workflow – including recent logs.

Status #

status (or progress) reports the progress of a workflow by showing the percentage of data copied across targets, if workflow is in copy state, and the replication lag between the target and the source once the copy phase is completed. It also shows the current state of traffic for the tables involved in the workflow.

It is too expensive to get real-time row counts of tables, using count(*), say. So we use the statistics available in the information_schema to approximate copy progress. This data can be significantly off (up to 50-60%) depending on the utilization of the underlying mysql server resources. You can manually run ANALYZE TABLE to update the statistics if so desired.

MirrorTraffic #

mirrortraffic mirrors a percentage of traffic forward for the tablet-types specified.

mirrortraffic must be run before switchtraffic. switchtraffic will automatically remove any mirror rules that were created by mirrortraffic.

SwitchTraffic #

switchtraffic switches traffic forward for the tablet-types specified. You can switch all traffic with just one command, and this is the default behavior. Note that you can now switch replica, rdonly, and primary traffic in any order.

Note that VTGate can buffer queries when switching traffic which can virtually eliminate any visible impact on application users.

ReverseTraffic #

reversetraffic switches traffic in the reverse direction for the tablet-types specified. The traffic should have been previously switched forward using SwitchTraffic for the cells and tablet-types specified.

Note that VTGate can buffer queries when reversing traffic which can virtually eliminate any visible impact on application users.

Cancel #

cancel can be used if a workflow was created in error or was misconfigured and you prefer to create a new workflow instead of fixing this one. cancel can only be called if no traffic has been switched. It removes vreplication-related artifacts like rows from the vreplication and copy_state tables in the sidecar _vt database along with routing rules and blacklisted tables from the topo and, by default, the target tables on the target keyspace (see --keep-data and --rename-tables).

Complete #

This is a destructive command

complete is used after all traffic has been switched. It removes vreplication-related artifacts like rows from vreplication and copy_state tables in the sidecar _vt database along with routing rules and and blocklisted tables from the topo. By default, the source tables are also dropped on the target keyspace (see --keep-data and --rename-tables).

Auto Increment Handling #

When migrating tables from an unsharded keyspace to a sharded one using the the MoveTables command, options are provided for how to handle auto incrementing values for the table(s) being moved. This is important as now that these tables will be sharded in the target keyspace you cannot rely on the MySQL auto_increment clauses — as the values will need to be unique across all shards in the sharded target keyspace — and you would instead need to rely on Vitess Sequences to achieve the equivalent behavior. Vitess supports fully managing these auto incrementing values when moving tables with:

  1. A combination of these MoveTables create flags: --sharded-auto-increment-handling=replace --global-keyspace=foo where foo is an unsharded keyspace that can be used for sequences, reference tables, and other "global" resources. That keyspace is where the sequence tables will be created if needed.
  2. Then, when switching the application traffic to the target keyspace you would specify the --initialize-target-sequences flag for the SwitchTraffic sub-command. It's at this point where the sequence tables will be created, if needed, and the starting value used will be initialized based on the current maximum value that has been used for the table.

This combination allows Vitess to replace the MySQL feature with the equivalent Vitess one in a way that is entirely transparent to the application and its users — they can continue to elide values for the column where auto incrementing values are desired on INSERT, and there will be no visible difference in the behavior resulting from the traffic switch.

Please see additional information about these flags below.

--sharded-auto-increment-handling #

optional
default REMOVE

This is a flag for the Create sub-command and it takes a string where the valid values are LEAVE (leave the MySQL auto_increment clauses in place), REMOVE (remove the clauses), and REPLACE (replace them with Vitess sequences). When REPLACE is specifed then not only are the MySQL auto_increment clauses removed when copying the table schemas from the source keyspace to the target, but the target keyspace's VSchema is also updated so that auto increment values will be retrieved from the sequence table (which can automatically be created using the two flags covered below).

--global-keyspace #

optional
default ""

This is also a flag for the Create sub-command and it takes a string where the value must be an existing unsharded keyspace. This keyspace will then be used to create the backing sequence tables if they do not already exist, provided you also request that the target sequences be setup and initialized using the next flag below.

--initialize-target-sequences #

optional
default false

If specified for the SwitchTraffic sub-command, then when switching write (primary tablet) traffic for tables that are being moved from an unsharded keyspace to a sharded one, initialize any sequences being used by those tables on the target. They are initialized using the current maximum value for the column across all shards on the target.

If this option is not specifed then when it comes to switching the write traffic you will need to manually ensure that you create and initialize the sequence tables so that the next values they provide are higher than any already used (with ample buffer in between to avoid potential identifier reuse and duplicate key errors during and immediately following the cutover).

Additional Key Options #

Each action or sub-command has additional options/parameters that can be used to modify its behavior. Please see the command's reference docs for the full list of command options or flags. Below we will add additional information for a subset of key options.

--auto-start #

optional
default true

Normally the workflow starts immediately after it is created. If this flag is set to false then the workflow is in a Stopped state until you explicitly start it.

Uses #
  • Allows updating the rows in _vt.vreplication after MoveTables has setup the streams. For example, you can add some filters to specific tables or change the projection clause to modify the values on the target. This provides an easier way to create simpler Materialize workflows by first using MoveTables with auto_start false, updating the BinlogSource as required by your Materialize and then start the workflow.
  • Changing the copy_state and/or pos values to restart a broken MoveTables workflow from a specific point of time

--cells #

optional
default local cell (of source tablet)
string

Comma seperated list of Cell(s) and/or CellAlias(es) to replicate from.

You can alternatively specify --all-cells if you want to replicate from source tablets in any existing cell (the local cell of the target tablet will be preferred).
Uses #
  • Improve performance by picking a tablet in cells in network proximity with the target
  • Reduce bandwidth costs by skipping cells that are in different availability zones
  • Select cells where replica lags are lower

--defer-secondary-keys #

optional
default false

If true, any secondary keys are dropped from the table definitions on the target shard(s) as we first initialize the tables for the copy phase. The exact same key definitions are then re-added when the copy phase completes for each table.

With this method all secondary index records for the table are generated in one bulk operation. This should significantly improve the overall copy phase execution time on large tables with many secondary keys — especially with MySQL 8.0.31 and later due to InnoDB's support for parallel index builds. This is logically similar to the mysqldump --disable-keys option.

--max-replication-lag-allowed #

optional
default the value used for --timeout

While executing SwitchTraffic we ensure that the VReplication lag for the workflow is less than this duration, otherwise report an error and don't attempt the switch. The calculated VReplication lag is the estimated maximum lag across workflow streams between the last event seen at the source and the last event processed by the target (which would be a heartbeat event if we're fully caught up). Usually, when VReplication has caught up, this lag should be very small (under a second).

While switching write traffic, we temporarily make the source databases read-only, and wait for the targets to catchup. This means that the application can effectively be partially down for this cutover period as writes will pause or error out. While switching write traffic this flag can ensure that you only switch traffic if the current lag is low, thus limiting this period of write-unavailability and avoiding it entirely if we're not likely to catch up within the --timeout) window.

While switching read traffic this can also be used to set an approximate upper bound on how stale reads will be against the replica tablets when using @replica shard targeting.

--on-ddl #

optional
default IGNORE

This flag allows you to specify what to do with DDL SQL statements when they are encountered in the replication stream from the source. The values can be as follows:

  • IGNORE: Ignore all DDLs (this is also the default, if a value for on-ddl is not provided).
  • STOP: Stop when DDL is encountered. This allows you to make any necessary changes to the target. Once changes are made, updating the workflow state to Running will cause VReplication to continue from just after the point where it encountered the DDL. Alternatively you may want to Cancel the workflow and create a new one to fully resync with the source.
  • EXEC: Apply the DDL, but stop if an error is encountered while applying it.
  • EXEC_IGNORE: Apply the DDL, but ignore any errors and continue replicating.

We caution against against using EXEC or EXEC_IGNORE for the following reasons:

  • You may want a different schema on the target
  • You may want to apply the DDL in a different way on the target
  • The DDL may take a long time to apply on the target and may disrupt replication, performance, and query execution while it is being applied (if serving traffic from the target)

--no-routing-rules #

optional
default false

Do not create routing rules for the tables being moved when the workflow is created. This implies that you should not use global routing or send traffic to the target keyspace through a vtgate. See https://github.com/vitessio/vitess/pull/13895 and https://github.com/vitessio/vitess/issues/13851 for a use-case and more details.

--rename-tables #

optional
default false

During Complete or Cancel operations, the tables are renamed instead of being deleted. Currently the new name is _<table_name>_old.

We use the same renaming logic used by pt-online-schema-change. Such tables are automatically skipped by VReplication if they exist on the source.

--enable-reverse-replication #

optional
default true

SwitchTraffic for primary tablet types, by default, starts a reverse replication stream with the current target as the source, replicating back to the original source. This enables a quick and simple rollback mechanism using ReverseTraffic. This reverse workflow name is that of the original workflow concatenated with _reverse.

If set to false these reverse replication streams will not be created and you will not be able to rollback once you have switched write traffic over to the target.

--source-time-zone #

optional
default ""

Specifying this flag causes all DATETIME fields to be converted from the given time zone into UTC. It is expected that the application has stored all DATETIME fields, in all tables being moved, in the specified time zone. On the target these DATETIME values will be stored in UTC.

As a best practice, Vitess expects users to run their MySQL servers in UTC. So we do not specify a target time zone for the conversion. It is expected that the time zone tables have been pre-populated on the target mysql servers.

Any reverse replication streams running after a SwitchWrites will do the reverse date conversion on the source.

Note that selecting the DATETIME columns from the target will now give the times in UTC. It is expected that the application will perform any conversions using, for example, SET GLOBAL time_zone = 'US/Pacific'or convert_tz().

Also note that only columns of DATETIME data types are converted. If you store DATETIME values as VARCHAR or VARBINARY strings, setting this flag will not convert them.

--stop-after-copy #

optional default false

If set, the workflow will stop once the Copy phase has been completed i.e. once all tables have been copied and VReplication decides that the lag is small enough to start replicating, the workflow state will be set to Stopped.

Uses #
  • If you just want a consistent snapshot of all the tables you can set this flag. The workflow will stop once the copy is done and you can then mark the workflow as Complete.

--tablet-types #

optional
default "in_order:REPLICA,PRIMARY"
string

Source tablet types to replicate from (e.g. PRIMARY, REPLICA, RDONLY). The value specified impacts tablet selection for the workflow.

--timeout #

optional
default 30s

For primary tablets, SwitchTraffic first stops writes on the source primary and waits for the replication to the target to catchup with the point where the writes were stopped. If the wait time is longer than timeout the command will error out. For setups with high write qps you may need to increase this value.

More Reading #