TiDB Data Migration Table Routing
When you migrate data using TiDB Data Migration (DM), you can configure the table routing to migrate a certain table of the upstream MySQL or MariaDB instance to the specified table in the downstream.
Configure table routing
routes:
rule-1:
schema-pattern: "test_*"
table-pattern: "t_*"
target-schema: "test"
target-table: "t"
# extract-table, extract-schema, and extract-source are optional and
# are required only when you need to extract information about sharded
# tables, sharded schemas, and source datatabase information.
extract-table:
table-regexp: "t_(.*)"
target-column: "c_table"
extract-schema:
schema-regexp: "test_(.*)"
target-column: "c_schema"
extract-source:
source-regexp: "(.*)"
target-column: "c_source"
rule-2:
schema-pattern: "test_*"
target-schema: "test"
In simple scenarios, it is recommended that you use the wildcard for matching schemas and tables. However, note the following version differences:
For DM v1.0.5 or later versions, the table routing supports the wildcard match, but there can be only one
*
in the wildcard expression, and*
must be placed at the end.For DM versions earlier than v1.0.5, the table routing supports the wildcard but does not support the
[...]
and[!...]
expressions.
Parameter descriptions
- DM migrates the upstream MySQL or MariaDB instance tables that match the
schema-pattern
/table-pattern
rule provided by Table selector to the downstreamtarget-schema
/target-table
. - For sharded tables that match the
schema-pattern
/table-pattern
rules, DM extracts the table name by using theextract-table
.table-regexp
regular expression, the schema name by using theextract-schema
.schema-regexp
regular expression, and source information by using theextract-source
.source-regexp
regular expression. Then DM writes the extracted information to the correspondingtarget-column
in the merged table in the downstream.
Usage examples
This section shows the usage examples in four different scenarios.
If you need to migrate and merge MySQL shards of small datasets to TiDB, refer to this tutorial.
Merge sharded schemas and tables
Assuming in the scenario of sharded schemas and tables, you want to migrate the test_{1,2,3...}
.t_{1,2,3...}
tables in two upstream MySQL instances to the test
.t
table in the downstream TiDB instance.
To migrate the upstream instances to the downstream test
.t
, you must create the following routing rules:
rule-1
is used to migrate DML or DDL statements of the table that matchesschema-pattern: "test_*"
andtable-pattern: "t_*"
to the downstreamtest
.t
.rule-2
is used to migrate DDL statements of the schema that matchesschema-pattern: "test_*"
, such asCREATE/DROP SCHEMA xx
.
rule-1:
schema-pattern: "test_*"
table-pattern: "t_*"
target-schema: "test"
target-table: "t"
rule-2:
schema-pattern: "test_*"
target-schema: "test"
Extract table, schema, and source information and write into the merged table
Assuming in the scenario of sharded schemas and tables, you want to migrate the test_{1,2,3...}
.t_{1,2,3...}
tables in two upstream MySQL instances to the test
.t
table in the downstream TiDB instance. At the same time, you want to extract the source information of the sharded tables and write it to the downstream merged table.
To migrate the upstream instances to the downstream test
.t
, you must create routing rules similar to the previous section Merge sharded schemas and tables. In addtion, you need to add the extract-table
, extract-schema
, and extract-source
configurations:
extract-table
: For a sharded table matchingschema-pattern
andtable-pattern
, DM extracts the sharded table name by usingtable-regexp
and writes the name suffix without thet_
part totarget-column
of the merged table, that is, thec_table
column.extract-schema
: For a sharded schema matchingschema-pattern
andtable-pattern
, DM extracts the sharded schema name by usingschema-regexp
and writes the name suffix without thetest_
part totarget-column
of the merged table, that is, thec_schema
column.extract-source
: For a sharded table matchingschema-pattern
andtable-pattern
, DM writes the source instance information to thetarget-column
of the merged table, that is, thec_source
column.
rule-1:
schema-pattern: "test_*"
table-pattern: "t_*"
target-schema: "test"
target-table: "t"
extract-table:
table-regexp: "t_(.*)"
target-column: "c_table"
extract-schema:
schema-regexp: "test_(.*)"
target-column: "c_schema"
extract-source:
source-regexp: "(.*)"
target-column: "c_source"
rule-2:
schema-pattern: "test_*"
target-schema: "test"
To extract the source information of upstream sharded tables to the merged table in the downstream, you must manually create a merged table in the downstream before starting the migration. The merged table must contain the three target-columns
(c_table
, c_schema
, and c_source
) used for specifying the source information. In addition, these columns must be the last columns and be string types.
CREATE TABLE `test`.`t` (
a int(11) PRIMARY KEY,
c_table varchar(10) DEFAULT NULL,
c_schema varchar(10) DEFAULT NULL,
c_source varchar(10) DEFAULT NULL
);
Assume that the upstream has the following two data sources:
Data source mysql-01
:
mysql> select * from test_11.t_1;
+---+
| a |
+---+
| 1 |
+---+
mysql> select * from test_11.t_2;
+---+
| a |
+---+
| 2 |
+---+
mysql> select * from test_12.t_1;
+---+
| a |
+---+
| 3 |
+---+
Data source mysql-02
:
mysql> select * from test_13.t_3;
+---+
| a |
+---+
| 4 |
+---+
After migration using DM, data in the merged table will be as follows:
mysql> select * from test.t;
+---+---------+----------+----------+
| a | c_table | c_schema | c_source |
+---+---------+----------+----------+
| 1 | 1 | 11 | mysql-01 |
| 2 | 2 | 11 | mysql-01 |
| 3 | 1 | 12 | mysql-01 |
| 4 | 3 | 13 | mysql-02 |
+---+---------+----------+----------+
Incorrect examples of creating merged tables
c-table
is not in the last three columns:
CREATE TABLE `test`.`t` (
c_table varchar(10) DEFAULT NULL,
a int(11) PRIMARY KEY,
c_schema varchar(10) DEFAULT NULL,
c_source varchar(10) DEFAULT NULL
);
c-source
is absent:
CREATE TABLE `test`.`t` (
a int(11) PRIMARY KEY,
c_table varchar(10) DEFAULT NULL,
c_schema varchar(10) DEFAULT NULL,
);
c_schema
is not a string type:
CREATE TABLE `test`.`t` (
a int(11) PRIMARY KEY,
c_table varchar(10) DEFAULT NULL,
c_schema int(11) DEFAULT NULL,
c_source varchar(10) DEFAULT NULL,
);
Merge sharded schemas
Assuming in the scenario of sharded schemas, you want to migrate the test_{1,2,3...}
.t_{1,2,3...}
tables in the two upstream MySQL instances to the test
.t_{1,2,3...}
tables in the downstream TiDB instance.
To migrate the upstream schemas to the downstream test
.t_[1,2,3]
, you only need to create one routing rule.
rule-1:
schema-pattern: "test_*"
target-schema: "test"
Incorrect table routing
Assuming that the following two routing rules are configured and test_1_bak
.t_1_bak
matches both rule-1
and rule-2
, an error is reported because the table routing configuration violates the number limitation.
rule-1:
schema-pattern: "test_*"
table-pattern: "t_*"
target-schema: "test"
target-table: "t"
rule-2:
schema-pattern: "test_1_bak"
table-pattern: "t_1_bak"
target-schema: "test"
target-table: "t_bak"