MoveTables
Move tables between keyspaces without downtime
Command #
MoveTables -v1 [-cells=<cells>] [-tablet_types=<source_tablet_types>] -workflow=<workflow>
[-all] [-exclude=<tables>] [-auto_start] [-stop_after_copy]
<source_keyspace> <target_keyspace> <table_specs>
Description #
MoveTables is used to start a workflow 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 used typically 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.
Parameters #
-cells #
optional
default local cell
A comma-separated list of cell names or cell aliases. This list is used by VReplication to determine which
cells should be used to pick a tablet for selecting data from the source keyspace.
Uses #
- Improve performance by using picking a tablet in cells in network proximity with the target
- To reduce bandwidth costs by skipping cells that are in different availability zones
- Select cells where replica lags are lower
-tablet_types #
optional
default replica
A comma-separated list of tablet types that are used while picking a tablet for sourcing data.
One or more from PRIMARY, REPLICA, RDONLY.
Uses #
- To reduce the load on PRIMARY tablets by using REPLICAs or RDONLYs
- Reducing lags by pointing to PRIMARY
workflow #
mandatory
Unique name for the MoveTables-initiated workflow, used in later commands to refer back to this workflow
source_keyspace #
mandatory
Name of existing keyspace that contains the tables to be moved
target_keyspace #
mandatory
Name of existing keyspace to which the tables will be moved
table_specs #
optional one of table_specs
or -all
needs to be specified
Either
a comma-separated list of tables
- if target keyspace is unsharded OR
- if target keyspace is sharded AND the tables being moved are already defined in the target's vschema
Example:
MoveTables -workflow=commerce2customer commerce customer customer,corder
Or
the JSON table section of the vschema for associated tables
- if target keyspace is sharded AND
- tables being moved are not yet present in the target's vschema
Example:
MoveTables -workflow=commerce2customer commerce customer '{"t1":{"column_vindexes": [{"column": "id", "name": "hash"}]}}}'
-all #
optional cannot specify table_specs
if -all
is specified
Move all tables from the source keyspace.
-exclude #
optional only applies if -all
is specified
If moving all tables, specifies tables to be skipped.
-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/orpos
values to restart a broken MoveTables workflow from a specific point of time.
-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
d
A MoveTables Workflow #
Once you select the set of tables to move from one keyspace to another you need to initiate a VReplication workflow as follows:
- Initiate the migration using MoveTables
- Monitor the workflow using Workflow
- Confirm that data has been copied over correctly using VDiff
- Start the cutover by routing all reads from your application to those tables using SwitchReads
- Complete the cutover by routing all writes using SwitchWrites
- Optionally clean up the source tables using DropSources
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 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 user guide which describes how MoveTables works in the local example provided in the Vitess repo.