Skip to content

Latest commit

 

History

History
150 lines (114 loc) · 7.16 KB

dm-binlog-event-filter.md

File metadata and controls

150 lines (114 loc) · 7.16 KB
Raw
title summary
TiDB Data Migration Binlog Event Filter
Learn how to use the binlog event filter feature of DM.

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.

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 binlog event filter supports 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 binlog event filter supports the wildcard but does not support the [...] and [!...] 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 more Events 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 event
    update DML The UPDATE DML event
    delete DML The DELETE DML event
    create database DDL The CREATE DATABASE DDL event
    drop database DDL The DROP DATABASE DDL event
    create table DDL The CREATE TABLE DDL event
    create index DDL The CREATE INDEX DDL event
    drop table DDL The DROP TABLE DDL event
    truncate table DDL The TRUNCATE TABLE DDL event
    rename table DDL The RENAME TABLE DDL event
    drop index DDL The DROP INDEX DDL event
    alter table DDL The 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.
    • 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 and Do rules are applied to the same table, the Ignore 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 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.

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