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:

• For DM v1.0.5 or later versions, the block and allow lists support 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 block and allow lists only support regular expression matching.

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 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 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: