TiDB Data Migration Binlog Event Filter
TiDB Data Migration (DM) provides the binlog event filter feature to filter out or only receive specified types of binlog events for some schemas or tables. For example, you can filter out all TRUNCATE TABLE
or INSERT
events. The binlog event filter feature is more fine-grained than the block and allow lists feature.
Configure the binlog event filter
In the task configuration file, add the following 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
Starting from DM v2.0.2, you can configure the binlog event filter in the source configuration file. For details, see Upstream Database Configuration File.
When you use the wildcard for matching schemas and tables, note the following:
schema-pattern
andtable-pattern
only support wildcards, including*
,?
, and[]
. There can only be one*
symbol in a wildcard match, and it must be at the end. For example, intable-pattern: "t_*"
,"t_*"
indicates all tables starting witht_
. See wildcard matching#Syntax) for details.sql-pattern
only supports regular expressions.
Parameter descriptions
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 moreEvent
s from the following table:Events Type Description all
Includes all the events below all dml
Includes all DML events below all ddl
Includes all DDL events below none
Includes none of the events below none ddl
Includes none of the DDL events below none dml
Includes none of the DML events below insert
DML The INSERT
DML eventupdate
DML The UPDATE
DML eventdelete
DML The DELETE
DML eventcreate database
DDL The CREATE DATABASE
DDL eventdrop database
DDL The DROP DATABASE
DDL eventcreate table
DDL The CREATE TABLE
DDL eventcreate index
DDL The CREATE INDEX
DDL eventdrop table
DDL The DROP TABLE
DDL eventtruncate table
DDL The TRUNCATE TABLE
DDL eventrename table
DDL The RENAME TABLE
DDL eventdrop index
DDL The DROP INDEX
DDL eventalter table
DDL The ALTER TABLE
DDL eventsql-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.
- The type of the event is not in the
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.
- The type of the event is in the
- When multiple rules match the same table, the rules are applied sequentially. The block list has a higher priority than the allow list. For example, if both the
Ignore
andDo
rules are applied to the same table, theIgnore
rule takes effect.
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 theTRUNCATE TABLE
,DROP TABLE
andDELETE STATEMENT
operations of all tables that match thetest_*
.t_*
pattern.filter-schema-rule
filters out theDROP DATABASE
operation of all schemas that match thetest_*
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 theCREATE TABLE
,INSERT
,UPDATE
andDELETE
statements of all tables that match thetest_*
.t_*
pattern.do-schema-rule
only migrates theCREATE DATABASE
statement of all schemas that match thetest_*
pattern.
Note
The reason why the CREATE DATABASE/TABLE
statement is migrated is that you can migrate DML statements only after the schema and table are created.
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: "*"
.
Note
To avoid filtering out data that need to be migrated, you must configure the global filtering rule as strictly as possible.
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