Changefeed Log Filters

TiCDC supports filtering data by tables and events. This document introduces how to use the two types of filters.

Table filter

Table filter is a feature that allows you to keep or filter out specific databases and tables by specifying the following configurations:

[filter] # Filter rules rules = ['*.*', '!test.*']

Common filter rules:

  • rules = ['*.*']
    • Replicate all tables (not including system tables)
  • rules = ['test1.*']
    • Replicate all tables in the test1 database
  • rules = ['*.*', '!scm1.tbl2']
    • Replicate all tables except for the scm1.tbl2 table
  • rules = ['scm1.tbl2', 'scm1.tbl3']
    • Only replicate tables scm1.tbl2 and scm1.tbl3
  • rules = ['scm1.tidb_*']
    • Replicate all tables in the scm1 database whose names start with tidb_

For more information, see Table filter syntax.

Event filter rules

Starting in v6.2.0, TiCDC supports event filter. You can configure event filter rules to filter out the DML and DDL events that meet the specified conditions.

The following is an example of event filter rules:

[filter] # The event filter rules must be under the `[filter]` configuration. You can configure multiple event filters at the same time. [[filter.event-filters]] matcher = ["test.worker"] # matcher is an allow list, which means this rule only applies to the worker table in the test database. ignore-event = ["insert"] # Ignore insert events. ignore-sql = ["^drop", "add column"] # Ignore DDLs that start with "drop" or contain "add column". ignore-delete-value-expr = "name = 'john'" # Ignore delete DMLs that contain the condition "name = 'john'". ignore-insert-value-expr = "id >= 100" # Ignore insert DMLs that contain the condition "id >= 100". ignore-update-old-value-expr = "age < 18 or name = 'lili'" # Ignore update DMLs whose old value contains "age < 18" or "name = 'lili'". ignore-update-new-value-expr = "gender = 'male' and age > 18" # Ignore update DMLs whose new value contains "gender = 'male'" and "age > 18".

Description of configuration parameters:

  • matcher: the database and table that this event filter rule applies to. The syntax is the same as table filter.

  • ignore-event: the event type to be ignored. This parameter accepts an array of strings. You can configure multiple event types. Currently, the following event types are supported:

    EventTypeAliasDescription
    all dmlMatches all DML events
    all ddlMatches all DDL events
    insertDMLMatches insert DML event
    updateDMLMatches update DML event
    deleteDMLMatches delete DML event
    create schemaDDLcreate databaseMatches create database event
    drop schemaDDLdrop databaseMatches drop database event
    create tableDDLMatches create table event
    drop tableDDLMatches drop table event
    rename tableDDLMatches rename table event
    truncate tableDDLMatches truncate table event
    alter tableDDLMatches alter table event, including all clauses of alter table, create index and drop index
    add table partitionDDLMatches add table partition event
    drop table partitionDDLMatches drop table partition event
    truncate table partitionDDLMatches truncate table partition event
    create viewDDLMatches create viewevent
    drop viewDDLMatches drop view event
    modify schema charset and collateDDLMatches modify schema charset and collate event
    recover tableDDLMatches recover table event
    rebase auto idDDLMatches rebase auto id event
    modify table commentDDLMatches modify table comment event
    modify table charset and collateDDLMatches modify table charset and collate event
    exchange table partitionDDLMatches exchange table partition event
    reorganize table partitionDDLMatches reorganize table partition event
    alter table partitioningDDLMatches alter table partitioning event
    remove table partitioningDDLMatches remove table partitioning event
    add columnDDLMatches add column event
    drop columnDDLMatches drop column event
    modify columnDDLMatches modify column event
    set default valueDDLMatches set default value event
    add primary keyDDLMatches add primary key event
    drop primary keyDDLMatches drop primary key event
    rename indexDDLMatches rename index event
    alter index visibilityDDLMatches alter index visibility event
    alter ttl infoDDLMatches alter ttl info event
    alter ttl removeDDLMatches DDL events that remove all TTL attributes of a table
    multi schema changeDDLMatches DDL events that change multiple attributes of a table within the same DDL statement
  • ignore-sql: the regular expressions of the DDL statements to be filtered out. This parameter accepts an array of strings, in which you can configure multiple regular expressions. This configuration only applies to DDL events.

  • ignore-delete-value-expr: this parameter accepts a SQL expression that follows the default SQL mode, used to filter out the DELETE type of DML events with a specified value.

  • ignore-insert-value-expr: this parameter accepts a SQL expression that follows the default SQL mode, used to filter out the INSERT type of DML events with a specified value.

  • ignore-update-old-value-expr: this parameter accepts a SQL expression that follows the default SQL mode, used to filter out the UPDATE type of DML events with a specified old value.

  • ignore-update-new-value-expr: this parameter accepts a SQL expression that follows the default SQL mode, used to filter out the UPDATE DML events with a specified new value.