Sign InTry Free

TiDB Data Migration Block and Allow Lists

When you migrate data using TiDB Data Migration (DM), you can configure the block and allow lists to filter or only migrate all operations of some databases or some tables.

Configure the block and allow lists

In the task configuration file, add the following 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"

In simple scenarios, it is recommended that you use the wildcard for matching schemas and tables. However, note the following version differences:

  • Wildcards including *, ?, and [] are supported. There can only be one * symbol in a wildcard match, and it must be at the end. For example, in tbl-name: "t*", "t*" indicates all tables starting with t. See wildcard matching for details.

  • A regular expression must begin with the ~ character.

Parameter descriptions

  • 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 process of a test.t table is as follows:

  1. Filter at the schema level:

    • If do-dbs is not empty, check 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, check 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, check 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, check 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 examples

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 applying 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.
Download PDFRequest docs changesAsk questions on Discord
Playground
New
One-stop & interactive experience of TiDB's capabilities WITHOUT registration.
Was this page helpful?
Products
TiDB
TiDB Dedicated
TiDB Serverless
Pricing
Get Demo
Get Started
© 2023 PingCAP. All Rights Reserved.