Sign InTry Free

Key Features of TiDB Data Migration

This document describes the data migration features provided by TiDB Data Migration (DM) and introduces appropriate parameter configurations.

For different DM versions, pay attention to the different match rules of schema or table names in the table routing, block & allow lists, and binlog event filter features:

  • For DM v1.0.5 or later versions, all the above features support the wildcard match. For all versions of DM, note that there can be only one * in the wildcard expression, and * must be placed at the end.
  • For DM versions earlier than v1.0.5, table routing and binlog event filter support the wildcard but do not support the [...] and [!...] expressions. The block & allow lists only supports the regular expression.

It is recommended that you use the wildcard for matching in simple scenarios.

Table routing

The table routing feature enables DM to migrate a certain table of the upstream MySQL or MariaDB instance to the specified table in the downstream.

Parameter configuration

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"

Parameter explanation

  • DM migrates the upstream MySQL or MariaDB instance tables that match the schema-pattern/table-pattern rule provided by Table selector to the downstream target-schema/target-table.
  • For sharded tables that match the schema-pattern/table-pattern rules, DM extracts the table name by using the extract-table.table-regexp regular expression, the schema name by using the extract-schema.schema-regexp regular expression, and source information by using the extract-source.source-regexp regular expression. Then DM writes the extracted information to the corresponding target-column in the merged table in the downstream.

Usage examples

This section shows the usage examples in different scenarios.

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 matches schema-pattern: "test_*" and table-pattern: "t_*" to the downstream test.t.
  • rule-2 is used to migrate DDL statements of the schema that matches schema-pattern: "test_*", such as CREATE/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 matching schema-pattern and table-pattern, DM extracts the sharded table name by using table-regexp and writes the name suffix without the t_ part to target-column of the merged table, that is, the c_table column.
  • extract-schema: For a sharded schema matching schema-pattern and table-pattern, DM extracts the sharded schema name by using schema-regexp and writes the name suffix without the test_ part to target-column of the merged table, that is, the c_schema column.
  • extract-source: For a sharded table matching schema-pattern and table-pattern, DM writes the source instance information to the target-column of the merged table, that is, the c_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"

Block and allow table lists

The block and allow lists filtering rule of the upstream database instance tables is similar to MySQL replication-rules-db/tables, which can be used to filter or only migrate all operations of some databases or some tables.

Parameter configuration

block-allow-list:             # Use black-white-list if the DM version is earlier than or equal to v2.0.0-beta.2.
  rule-1:
    do-dbs: ["test*"]         # Starting with characters other than "~" indicates that it is a wildcard;
                              # v1.0.5 or later versions support the regular expression rules.
    do-tables:
    - db-name: "test[123]"    # Matches test1, test2, and test3.
      tbl-name: "t[1-5]"      # Matches t1, t2, t3, t4, and t5.
    - db-name: "test"
      tbl-name: "t"
  rule-2:
    do-dbs: ["~^test.*"]      # Starting with "~" indicates that it is a regular expression.
    ignore-dbs: ["mysql"]
    do-tables:
    - db-name: "~^test.*"
      tbl-name: "~^t.*"
    - db-name: "test"
      tbl-name: "t"
    ignore-tables:
    - db-name: "test"
      tbl-name: "log"

Parameter explanation

  • do-dbs: allow lists of the schemas to be migrated, similar to replicate-do-db in MySQL
  • ignore-dbs: block lists of the schemas to be migrated, similar to replicate-ignore-db in MySQL
  • do-tables: allow lists of the tables to be migrated, similar to replicate-do-table in MySQL. Both db-name and tbl-name must be specified
  • ignore-tables: block lists of the tables to be migrated, similar to replicate-ignore-table in MySQL. Both db-name and tbl-name must be specified

If a value of the above parameters starts with the ~ character, the subsequent characters of this value are treated as a regular expression. You can use this parameter to match schema or table names.

Filtering process

The filtering rules corresponding to do-dbs and ignore-dbs are similar to the Evaluation of Database-Level Replication and Binary Logging Options in MySQL. The filtering rules corresponding to do-tables and ignore-tables are similar to the Evaluation of Table-Level Replication Options in MySQL.

The filtering process is as follows:

  1. Filter at the schema level:

    • If do-dbs is not empty, judge whether a matched schema exists in do-dbs.

      • If yes, continue to filter at the table level.
      • If not, filter test.t.
    • If do-dbs is empty and ignore-dbs is not empty, judge whether a matched schema exits in ignore-dbs.

      • If yes, filter test.t.
      • If not, continue to filter at the table level.
    • If both do-dbs and ignore-dbs are empty, continue to filter at the table level.

  2. Filter at the table level:

    1. If do-tables is not empty, judge whether a matched table exists in do-tables.

      • If yes, migrate test.t.
      • If not, filter test.t.
    2. If ignore-tables is not empty, judge whether a matched table exists in ignore-tables.

      • If yes, filter test.t.
      • If not, migrate test.t.
    3. If both do-tables and ignore-tables are empty, migrate test.t.

Usage example

Assume that the upstream MySQL instances include the following tables:

`logs`.`messages_2016`
`logs`.`messages_2017`
`logs`.`messages_2018`
`forum`.`users`
`forum`.`messages`
`forum_backup_2016`.`messages`
`forum_backup_2017`.`messages`
`forum_backup_2018`.`messages`

The configuration is as follows:

block-allow-list:  # Use black-white-list if the DM version is earlier than or equal to v2.0.0-beta.2.
  bw-rule:
    do-dbs: ["forum_backup_2018", "forum"]
    ignore-dbs: ["~^forum_backup_"]
    do-tables:
    - db-name: "logs"
      tbl-name: "~_2018$"
    - db-name: "~^forum.*"
​      tbl-name: "messages"
    ignore-tables:
    - db-name: "~.*"
​      tbl-name: "^messages.*"

After using the bw-rule rule:

TableWhether to filterWhy filter
logs.messages_2016YesThe schema logs fails to match any do-dbs.
logs.messages_2017YesThe schema logs fails to match any do-dbs.
logs.messages_2018YesThe schema logs fails to match any do-dbs.
forum_backup_2016.messagesYesThe schema forum_backup_2016 fails to match any do-dbs.
forum_backup_2017.messagesYesThe schema forum_backup_2017 fails to match any do-dbs.
forum.usersYes1. The schema forum matches do-dbs and continues to filter at the table level.
2. The schema and table fail to match any of do-tables and ignore-tables and do-tables is not empty.
forum.messagesNo1. The schema forum matches do-dbs and continues to filter at the table level.
2. The table messages is in the db-name: "~^forum.*",tbl-name: "messages" of do-tables.
forum_backup_2018.messagesNo1. The schema forum_backup_2018 matches do-dbs and continues to filter at the table level.
2. The schema and table match the db-name: "~^forum.*",tbl-name: "messages" of do-tables.

Binlog event filter

Binlog event filter is a more fine-grained filtering rule than the block and allow lists filtering rule. You can use statements like INSERT or TRUNCATE TABLE to specify the binlog events of schema/table that you need to migrate or filter out.

Parameter configuration

filters:
  rule-1:
    schema-pattern: "test_*"
    ​table-pattern: "t_*"
    ​events: ["truncate table", "drop table"]
    sql-pattern: ["^DROP\\s+PROCEDURE", "^CREATE\\s+PROCEDURE"]
    ​action: Ignore

Parameter explanation

  • schema-pattern/table-pattern: the binlog events or DDL SQL statements of upstream MySQL or MariaDB instance tables that match schema-pattern/table-pattern are filtered by the rules below.

  • events: the binlog event array. You can only select one or more Events from the following table:

    EventsTypeDescription
    allIncludes all the events below
    all dmlIncludes all DML events below
    all ddlIncludes all DDL events below
    noneIncludes none of the events below
    none ddlIncludes none of the DDL events below
    none dmlIncludes none of the DML events below
    insertDMLThe INSERT DML event
    updateDMLThe UPDATE DML event
    deleteDMLThe DELETE DML event
    create databaseDDLThe CREATE DATABASE DDL event
    drop databaseDDLThe DROP DATABASE DDL event
    create tableDDLThe CREATE TABLE DDL event
    create indexDDLThe CREATE INDEX DDL event
    drop tableDDLThe DROP TABLE DDL event
    truncate tableDDLThe TRUNCATE TABLE DDL event
    rename tableDDLThe RENAME TABLE DDL event
    drop indexDDLThe DROP INDEX DDL event
    alter tableDDLThe ALTER TABLE DDL event
  • sql-pattern: it is used to filter specified DDL SQL statements. The matching rule supports using a regular expression. For example, "^DROP\\s+PROCEDURE".

  • action: the string (Do/Ignore). Based on the following rules, it judges whether to filter. If either of the two rules is satisfied, the binlog is filtered; otherwise, the binlog is not filtered.

    • Do: the allow list. The binlog is filtered in either of the following two conditions:
      • The type of the event is not in the event list of the rule.
      • The SQL statement of the event cannot be matched by sql-pattern of the rule.
    • Ignore: the block list. The binlog is filtered in either of the following two conditions:
      • The type of the event is in the event list of the rule.
      • The SQL statement of the event can be matched by sql-pattern of the rule.

Usage examples

This section shows the usage examples in the scenario of sharding (sharded schemas and tables).

Filter all sharding deletion operations

To filter out all deletion operations, configure the following two filtering rules:

  • filter-table-rule filters out the truncate table, drop table and delete statement operations of all tables that match the test_*.t_* pattern.
  • filter-schema-rule filters out the drop database operation of all schemas that match the test_* pattern.
filters:
  filter-table-rule:
    schema-pattern: "test_*"
    table-pattern: "t_*"
    events: ["truncate table", "drop table", "delete"]
    action: Ignore
  filter-schema-rule:
    schema-pattern: "test_*"
    events: ["drop database"]
    action: Ignore

Only migrate sharding DML statements

To only migrate sharding DML statements, configure the following two filtering rules:

  • do-table-rule only migrates the create table, insert, update and delete statements of all tables that match the test_*.t_* pattern.
  • do-schema-rule only migrates the create database statement of all schemas that match the test_* pattern.
filters:
  do-table-rule:
    schema-pattern: "test_*"
    table-pattern: "t_*"
    events: ["create table", "all dml"]
    action: Do
  do-schema-rule:
    schema-pattern: "test_*"
    events: ["create database"]
    action: Do

Filter out the SQL statements that TiDB does not support

To filter out the PROCEDURE statements that TiDB does not support, configure the following filter-procedure-rule:

filters:
  filter-procedure-rule:
    schema-pattern: "test_*"
    table-pattern: "t_*"
    sql-pattern: ["^DROP\\s+PROCEDURE", "^CREATE\\s+PROCEDURE"]
    action: Ignore

filter-procedure-rule filters out the ^CREATE\\s+PROCEDURE and ^DROP\\s+PROCEDURE statements of all tables that match the test_*.t_* pattern.

Filter out the SQL statements that the TiDB parser does not support

For the SQL statements that the TiDB parser does not support, DM cannot parse them and get the schema/table information. So you must use the global filtering rule: schema-pattern: "*".

To filter out the PARTITION statements that the TiDB parser (of some version) does not support, configure the following filtering rule:

filters:
  filter-partition-rule:
    schema-pattern: "*"
    sql-pattern: ["ALTER\\s+TABLE[\\s\\S]*ADD\\s+PARTITION", "ALTER\\s+TABLE[\\s\\S]*DROP\\s+PARTITION"]
    action: Ignore

Online DDL tools

In the MySQL ecosystem, tools such as gh-ost and pt-osc are widely used. DM provides supports for these tools to avoid migrating unnecessary intermediate data.

Restrictions

  • DM only supports gh-ost and pt-osc.
  • When online-ddl is enabled, the checkpoint corresponding to incremental replication should not be in the process of online DDL execution. For example, if an upstream online DDL operation starts at position-A and ends at position-B of the binlog, the starting point of incremental replication should be earlier than position-A or later than position-B; otherwise, an error occurs. For details, refer to FAQ.

Parameter configuration

  • v2.0.5 and later
  • earlier than v2.0.5

In v2.0.5 and later versions, you need to use the online-ddl configuration item in the task configuration file.

  • If the upstream MySQL/MariaDB (at the same time) uses the gh-ost or pt-osc tool, set online-ddl to true in the task configuration file:
online-ddl: true

Before v2.0.5 (not including v2.0.5), you need to use the online-ddl-scheme configuration item in the task configuration file.

  • If the upstream MySQL/MariaDB uses the gh-ost tool, set it in the task configuration file:
online-ddl-scheme: "gh-ost"
  • If the upstream MySQL/MariaDB uses the pt tool, set it in the task configuration file:
online-ddl-scheme: "pt"

Shard merge

DM supports merging the DML and DDL data in the upstream MySQL/MariaDB sharded tables and migrating the merged data to the downstream TiDB tables.

Restrictions

Currently, the shard merge feature is supported only in limited scenarios. For details, refer to Sharding DDL usage Restrictions in the pessimistic mode and Sharding DDL usage Restrictions in the optimistic mode.

Parameter configuration

Set shard-mode to pessimistic in the task configuration file:

shard-mode: "pessimistic" # The shard merge mode. Optional modes are ""/"pessimistic"/"optimistic". The "" mode is used by default which means sharding DDL merge is disabled. If the task is a shard merge task, set it to the "pessimistic" mode. After getting a deep understanding of the principles and restrictions of the "optimistic" mode, you can set it to the "optimistic" mode.

Handle sharding DDL locks manually

In some abnormal scenarios, you need to handle sharding DDL Locks manually.

Download PDFRequest docs changesEdit this page
Was this page helpful?
Open Source Ecosystem
TiDB
TiKV
TiSpark
Chaos Mesh
© 2022 PingCAP. All Rights Reserved.