Sync Data Across PolarDB-X Instances Using CDC - PolarDB-X

PolarDB-X supports MySQL-compatible primary/secondary replication between two PolarDB-X instances. The Change Data Capture (CDC) feature provides binary logging so a PolarDB-X instance can act as the primary database, and MySQL Replica-equivalent capabilities so another PolarDB-X instance can act as the secondary database.

Users cannot create replication links for PolarDB-X instances purchased in the PolarDB-X console due to network restrictions. Replication links mainly apply to self-managed databases.

How it works

Setting up replication between two PolarDB-X instances follows the same pattern as replicating from PolarDB-X to MySQL. The SQL statements are highly compatible with MySQL. For the full list of supported replication statements, see Replication statements.

Prerequisites

Before you begin, ensure that you have:

Network connectivity between the secondary instance and the primary instance's endpoint and port
A database user on the primary instance with replication privileges
The primary instance's endpoint, port, and binlog position or timestamp to start replication from

Usage notes

RETURNING optimization and data inconsistency risk (version 5.4.12 and later)

Starting from version 5.4.12, PolarDB-X enables the RETURNING optimization for INSERT IGNORE statements on tables with global secondary indexes (GSIs) by default. This optimization can cause data inconsistency when the secondary instance consumes binlog events.

How the issue occurs:

Create a table with a unique global secondary index (UGSI):

CREATE TABLE t(
  pk INT,
  sk INT,
  uk INT,
  PRIMARY KEY(pk),
  UNIQUE GLOBAL INDEX g_i_k(uk) PARTITION BY uk
) PARTITION BY sk;

Insert data:

INSERT INTO t VALUES (1,1,1),(2,2,2);
INSERT IGNORE INTO t VALUES (1,2,2);

With the RETURNING optimization enabled, the binlog records:
```
INSERT INTO t VALUES(1,2,2);
DELETE FROM t WHERE pk = 1 AND sk = 2;
```
When the secondary instance replays these events, the INSERT triggers a unique key conflict on the uk column. The replica converts the INSERT to a REPLACE, which overwrites the original record and causes data inconsistency between the primary and secondary instances.

To mitigate this risk, disable the optimization:

SET GLOBAL DML_USE_RETURNING = FALSE;

Disabling the optimization may reduce INSERT IGNORE performance on tables with GSIs and can affect data import throughput. Evaluate your workload before applying this change.

Set up replication

A complete replication setup follows this sequence:

Create a replication link with CHANGE MASTER TO.
(Optional) Configure replication filters with CHANGE REPLICATION FILTER.
Start replication with START SLAVE.
Stop replication with STOP SLAVE when needed.
Delete the link with RESET SLAVE ALL after stopping replication.

Create a replication link

Use CHANGE MASTER TO to configure the primary instance connection and create a replication link.

Syntax:

CHANGE MASTER TO option [, option] ... [ channel_option ]

option: {
    MASTER_HOST = 'host_name'
  | MASTER_USER = 'user_name'
  | MASTER_PASSWORD = 'password'
  | MASTER_PORT = port_num
  | MASTER_LOG_FILE = 'source_log_name'
  | MASTER_LOG_POS = source_log_pos
  | MASTER_LOG_TIME_SECOND = source_log_time
  | SOURCE_HOST_TYPE = {RDS|POLARDBX|MYSQL}
  | STREAM_GROUP = 'stream_group_name'
  | WRITE_SERVER_ID = write_server_id
  | TRIGGER_AUTO_POSITION = {FALSE|TRUE}
  | WRITE_TYPE = {SPLIT|SERIAL|TRANSACTION}
  | MODE = {INCREMENTAL|IMAGE}
  | CONFLICT_STRATEGY = {OVERWRITE|INTERRUPT|IGNORE|DIRECT_OVERWRITE}
  | IGNORE_SERVER_IDS = (server_id_list)
}

channel_option:
    FOR CHANNEL channel

server_id_list:
    [server_id [, server_id] ... ]

Parameters:

Parameter	Description	Default
`MASTER_HOST`	Endpoint of the primary instance	—
`MASTER_USER`	Username for connecting to the primary instance	—
`MASTER_PASSWORD`	Password for connecting to the primary instance	—
`MASTER_PORT`	Port for connecting to the primary instance	—
`MASTER_LOG_FILE`	Binlog file name on the primary instance	—
`MASTER_LOG_POS`	Offset in the binlog file to start replication from	—
`MASTER_LOG_TIME_SECOND`	UNIX timestamp to start replication from. Ignored if `MASTER_LOG_FILE` and `MASTER_LOG_POS` are set.	—
`SOURCE_HOST_TYPE`	Type of the primary instance: `RDS`, `POLARDBX`, or `MYSQL`. Set to `POLARDBX` when the primary is a PolarDB-X 2.0 instance.	`MYSQL`
`STREAM_GROUP`	Stream group name for the multi-stream binlog service. Required when the primary is a PolarDB-X 2.0 instance and you want to use multi-stream binlog.	—
`WRITE_SERVER_ID`	ID of the write server	—
`TRIGGER_AUTO_POSITION`	Specifies whether to automatically use the latest binlog offset of the primary instance	`FALSE`
`WRITE_TYPE`	Write mode: `SPLIT` (non-transactional parallel replication), `SERIAL` (non-transactional serial replication), or `TRANSACTION` (transactional serial replication)	`SPLIT`
`MODE`	Sync mode: `INCREMENTAL` (incremental data synchronization only) or `IMAGE` (schema synchronization, full data synchronization, and incremental data synchronization)	—
`CONFLICT_STRATEGY`	Conflict policy: `OVERWRITE` (overwrite using `REPLACE INTO`), `INTERRUPT` (stop replication), `IGNORE` (skip the conflict), or `DIRECT_OVERWRITE`	`OVERWRITE`
`IGNORE_SERVER_IDS`	Server IDs to exclude from replication	—
`FOR CHANNEL channel`	Link name. Must be unique across all existing replication links.	—

For full syntax details, see CHANGE MASTER TO.

Example 1: Start replication from a timestamp

The following statement creates a replication link and starts replication from 2024-04-15 13:45:38 UTC (UNIX timestamp 1713159938):

CHANGE MASTER TO
MASTER_HOST='pxc-YOURENDPOINT.com',
MASTER_PORT=3306,
MASTER_USER='polardbx',
MASTER_PASSWORD='123456',
SOURCE_HOST_TYPE=polardbx,
MASTER_LOG_TIME_SECOND=1713159938,
write_server_id=1944903859
FOR CHANNEL 'pxc-bjrcsnbyagcdxh';

Example 2: Start replication from a binlog offset

The following statement creates a replication link and starts replication from offset 4 in binlog.000001:

CHANGE MASTER TO
MASTER_HOST='pxc-YOURENDPOINT.com',
MASTER_PORT=3306,
MASTER_USER='polardbx',
MASTER_PASSWORD='123456',
SOURCE_HOST_TYPE=polardbx,
MASTER_LOG_FILE='binlog.000001',
MASTER_LOG_POS=4,
write_server_id=1944903859
FOR CHANNEL 'pxc-bjrcsnbyagcdxh';

Configure replication filters

Use CHANGE REPLICATION FILTER to control which databases, tables, or binlog events are replicated.

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 = (wile_ignore_table_list)
  | REPLICATE_SKIP_TSO = 'tso_num'
  | REPLICATE_SKIP_UNTIL_TSO = 'tso_num'
  | REPLICATE_ENABLE_DDL = {TRUE|FALSE}
}

channel_option:
    FOR CHANNEL channel

PolarDB-X-specific parameters:

Parameter	Description	Default
`REPLICATE_SKIP_TSO`	Timestamp Oracle (TSO) of the binlog event to skip. For more information about TSO, see Distributed transactions.	—
`REPLICATE_SKIP_UNTIL_TSO`	TSO before which all binlog events are skipped	—
`REPLICATE_ENABLE_DDL`	Specifies whether to replicate DDL statements	`TRUE`

For the remaining parameters (REPLICATE_DO_DB, REPLICATE_IGNORE_DB, REPLICATE_DO_TABLE, REPLICATE_IGNORE_TABLE, REPLICATE_WILD_DO_TABLE, REPLICATE_WILD_IGNORE_TABLE), see CHANGE REPLICATION FILTER in the MySQL documentation and CHANGE REPLICATION FILTER in the PolarDB-X documentation.

Example 1: Replicate a specific database

The following statement configures replication to include only changes from the TEST database:

CHANGE REPLICATION FILTER REPLICATE_DO_DB=(TEST);

Example 2: Skip a specific binlog event by TSO

Run SHOW BINLOGS EVENTS to retrieve TSO values. The TSO appears as the numeric string after CTS:: in the INFO column.

The following statement skips the binlog event with TSO 716669064683978758416977558623632834560000000000000000 (the event at POS 372):

CHANGE REPLICATION FILTER REPLICATE_SKIP_TSO='716669064683978758416977558623632834560000000000000000';

Start replication

Use START SLAVE to start one or all replication links.

Syntax:

START SLAVE [ channel_option ]

channel_option:
    FOR CHANNEL channel

Start all replication links:

START SLAVE;

Start a specific replication link:

START SLAVE FOR CHANNEL 'TEST';

For full syntax details, see START SLAVE.

Stop replication

Use STOP SLAVE to stop one or all replication links.

Syntax:

STOP SLAVE [ channel_option ]

channel_option:
    FOR CHANNEL channel

Stop all replication links:

STOP SLAVE;

Stop a specific replication link:

STOP SLAVE FOR CHANNEL 'TEST';

For full syntax details, see STOP SLAVE.

Delete a replication link

Important

Stop replication before deleting a replication link.

Use RESET SLAVE ALL to remove one or all replication links.

Syntax:

RESET SLAVE ALL [ channel_option ]

channel_option:
    FOR CHANNEL channel

Remove all replication links:

RESET SLAVE ALL;

Remove a specific replication link:

RESET SLAVE ALL FOR CHANNEL 'TEST';

For full syntax details, see RESET SLAVE ALL.

PolarDB:Replicate data between PolarDB-X instances

How it works

Prerequisites

Usage notes

Set up replication

Create a replication link

Configure replication filters

Start replication

Stop replication

Delete a replication link

What's next