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, intbl-name: "t*"
,"t*"
indicates all tables starting witht
. 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 toreplicate-do-db
in MySQL.ignore-dbs
: block lists of the schemas to be migrated, similar toreplicate-ignore-db
in MySQL.do-tables
: allow lists of the tables to be migrated, similar toreplicate-do-table
in MySQL. Bothdb-name
andtbl-name
must be specified.ignore-tables
: block lists of the tables to be migrated, similar toreplicate-ignore-table
in MySQL. Bothdb-name
andtbl-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
andignore-dbs
are similar to the Evaluation of Database-Level Replication and Binary Logging Options in MySQL. - The filtering rules corresponding to
do-tables
andignore-tables
are similar to the Evaluation of Table-Level Replication Options in MySQL.
The filtering process of a test
.t
table is as follows:
Filter at the schema level:
If
do-dbs
is not empty, check whether a matched schema exists indo-dbs
.- If yes, continue to filter at the table level.
- If not, filter
test
.t
.
If
do-dbs
is empty andignore-dbs
is not empty, check whether a matched schema exits inignore-dbs
.- If yes, filter
test
.t
. - If not, continue to filter at the table level.
- If yes, filter
If both
do-dbs
andignore-dbs
are empty, continue to filter at the table level.
Filter at the table level:
If
do-tables
is not empty, check whether a matched table exists indo-tables
.- If yes, migrate
test
.t
. - If not, filter
test
.t
.
- If yes, migrate
If
ignore-tables
is not empty, check whether a matched table exists inignore-tables
.- If yes, filter
test
.t
. - If not, migrate
test
.t
.
- If yes, filter
If both
do-tables
andignore-tables
are empty, migratetest
.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:
Table | Whether to filter | Why filter |
---|---|---|
logs .messages_2016 | Yes | The schema logs fails to match any do-dbs . |
logs .messages_2017 | Yes | The schema logs fails to match any do-dbs . |
logs .messages_2018 | Yes | The schema logs fails to match any do-dbs . |
forum_backup_2016 .messages | Yes | The schema forum_backup_2016 fails to match any do-dbs . |
forum_backup_2017 .messages | Yes | The schema forum_backup_2017 fails to match any do-dbs . |
forum .users | Yes | 1. 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 .messages | No | 1. 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 .messages | No | 1. 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 . |