Filter Binlog Events

This document describes how to filter binlog events when you use DM to perform continuous incremental data replication. For the detailed replication instructions, refer to the following documents by scenarios:

Configuration

To use binlog event filter, add a filter to the task configuration file of DM, as shown below:

  1. filters:
  2. rule-1:
  3. schema-pattern: "test_*"
  4. table-pattern: "t_*"
  5. events: ["truncate table", "drop table"]
  6. sql-pattern: ["^DROP\\s+PROCEDURE", "^CREATE\\s+PROCEDURE"]
  7. action: Ignore
  • schema-pattern/table-pattern: Filters matching schemas or tables

  • events: Filters binlog events. Supported events are listed in the table below:

    EventCategoryDescription
    allIncludes all events
    all dmlIncludes all DML events
    all ddlIncludes all DDL events
    noneIncludes no event
    none ddlExcludes all DDL events
    none dmlExcludes all DML events
    insertDMLInsert DML event
    updateDMLUpdate DML event
    deleteDMLDelete DML event
    create databaseDDLCreate database event
    drop databaseDDLDrop database event
    create tableDDLCreate table event
    create indexDDLCreate index event
    drop tableDDLDrop table event
    truncate tableDDLTruncate table event
    rename tableDDLRename table event
    drop indexDDLDrop index event
    alter tableDDLAlter table event
  • sql-pattern: Filters specified DDL SQL statements. The matching rule supports using a regular expression.

  • action: Do or Ignore

    • Do: the allow list. A binlog event is replicated if meeting either of the following two conditions:

      • The event matches the rule setting.
      • sql-pattern has been specified and the SQL statement of the event matches any of the sql-pattern options.
    • Ignore: the block list. A binlog event is filtered out if meeting either of the following two conditions:

      1. - The event matches the rule setting.
      2. - sql-pattern has been specified and the SQL statement of the event matches any of the sql-pattern options.

      If both Do and Ignore are configured, Ignore has higher priority over Do. That is, an event satisfying both Ignore and Do conditions will be filtered out.

Application scenarios

This section describes the application scenarios of binlog event filter.

Filter out all sharding deletion operations

To filter out all deletion operations, configure a filter-table-rule and a filter-schema-rule, as shown below:

  1. filters:
  2. filter-table-rule:
  3. schema-pattern: "test_*"
  4. table-pattern: "t_*"
  5. events: ["truncate table", "drop table", "delete"]
  6. action: Ignore
  7. filter-schema-rule:
  8. schema-pattern: "test_*"
  9. events: ["drop database"]
  10. action: Ignore

Migrate only DML operations of sharded schemas and tables

To replicate only DML statements, configure two Binlog event filter rule, as shown below:

  1. filters:
  2. do-table-rule:
  3. schema-pattern: "test_*"
  4. table-pattern: "t_*"
  5. events: ["create table", "all dml"]
  6. action: Do
  7. do-schema-rule:
  8. schema-pattern: "test_*"
  9. events: ["create database"]
  10. action: Do

Filter out SQL statements not supported by TiDB

To filter out SQL statements not supported by TiDB, configure a filter-procedure-rule, as shown below:

  1. filters:
  2. filter-procedure-rule:
  3. schema-pattern: "*"
  4. sql-pattern: [".*\\s+DROP\\s+PROCEDURE", ".*\\s+CREATE\\s+PROCEDURE", "ALTER\\s+TABLE[\\s\\S]*ADD\\s+PARTITION", "ALTER\\s+TABLE[\\s\\S]*DROP\\s+PARTITION"]
  5. action: Ignore

Filter Binlog Events - 图1

Warning

To avoid filtering out data that needs to be migrated, configure the global filtering rule as strictly as possible.

See also

Filter Binlog Events Using SQL Expressions