CHANGE REPLICATION FILTER-PolarDB(PolarDB)-阿里云帮助中心

Syntax

CHANGE REPLICATION FILTER option [, option] ... [ channel_option ]
option: {
    REPLICATE_DO_DB = (do_db_list)
  | REPLICATE_IGNORE_DB = (ignore_db_list)
  | REPLICATE_DO_TABLE = (do_table_list)
  | REPLICATE_IGNORE_TABLE = (ignore_table_list)
  | REPLICATE_WILD_DO_TABLE = (wild_do_table_list)
  | REPLICATE_WILD_IGNORE_TABLE = (wild_ignore_table_list)
  | REPLICATE_SKIP_TSO = 'tso_num'
  | REPLICATE_SKIP_UNTIL_TSO = 'tso_num'
  | REPLICATE_ENABLE_DDL = {TRUE|FALSE}
}
channel_option:
    FOR CHANNEL channel

Usage notes

This SQL statement is supported on PolarDB-X instances running V5.4.19 or later.
To run the CHANGE REPLICATION FILTER statement, you must use a privileged account for the current instance.
If you include the channel_option, the filter applies only to the specified replication link. If you omit this option, the filter applies to all replication links on the instance.
Before you run this statement, the target replication link must be stopped. Use the STOP SLAVE statement to stop the link.

Parameters

REPLICATE_SKIP_TSO: Skips the binlog event for the specified TSO. For more information about TSO, see distributed transaction.
REPLICATE_SKIP_UNTIL_TSO: Skips all binlog events that occur before the specified TSO. The event at the specified TSO is not skipped.
REPLICATE_ENABLE_DDL: Specifies whether to replicate DDL statements. The default value is TRUE.
For descriptions of the following parameters, see CHANGE REPLICATION FILTER.
- REPLICATE_DO_DB
- REPLICATE_IGNORE_DB
- REPLICATE_DO_TABLE
- REPLICATE_IGNORE_TABLE
- REPLICATE_WILD_DO_TABLE
- REPLICATE_WILD_IGNORE_TABLE

Example 1

Run the following statement to modify the replication link to replicate changes only from the TEST database.

CHANGE REPLICATION FILTER REPLICATE_DO_DB=(TEST);

Example 2

Obtain the TSO

Run the SHOW BINLOGS EVENTS statement to obtain the TSO of a binlog event. The following is an example of the output:

mysql> show binlog events limit 5;
+----------------+-----+-------------+------------+-------------+----------------------------------------------------------------------+
| LOG_NAME       | POS | EVENT_TYPE  | SERVER_ID  | END_LOG_POS | INFO                                                                 |
+----------------+-----+-------------+------------+-------------+----------------------------------------------------------------------+
| binlog.000001  |   4 | Format_desc | 2304845447 |         123 | Server ver: 5.6.29-TDDL-5.4.19-17085013, Binlog ver: 4              |
| binlog.000001  | 123 | Rows_query  | 2304845447 |         206 | CTS::71666906440547697281697755859590852608000000000000000000          |
| binlog.000001  | 206 | Rows_query  | 2304845447 |         289 | CTS::71666906449691280016977558605052190370300000000000000000          |
| binlog.000001  | 289 | Rows_query  | 2304845447 |         372 | CTS::71666906459004457792169775586142376552000000000000000000          |
| binlog.000001  | 372 | Rows_query  | 2304845447 |         455 | CTS::71666906468339758416977558623632834560000000000000000000          |
+----------------+-----+-------------+------------+-------------+----------------------------------------------------------------------+
5 rows in set (0.03 sec)

In the INFO column, the numeric string prefixed with CTS:: is the TSO of the event.

Skip an event by its TSO

Run the following statement to modify the replication link and skip the binlog event with the TSO 716669064683978758416977558623632834560000000000000000. This binlog event is at POS=372 in the preceding example output.

CHANGE REPLICATION FILTER REPLICATE_SKIP_TSO='716669064683978758416977558623632834560000000000000000';

For more information, see CHANGE REPLICATION FILTER.