PolarDB:Replicate data from PolarDB-X to MySQL

RETURNING optimization and data consistency

Starting from PolarDB-X version 5.4.12, the RETURNING optimization is enabled by default for INSERT IGNORE statements on tables with global secondary indexes (GSIs). In scenarios where PolarDB-X is the primary database and MySQL is the secondary database, this optimization can cause data inconsistency when MySQL consumes binlogs.

Mitigation

Disable the optimization before starting replication:

SET GLOBAL DML_USE_RETURNING = FALSE;

Root cause

With RETURNING optimization enabled, INSERT IGNORE inserts data without checking for uniqueness, then deletes duplicate rows. This produces the following binlog entries:

INSERT INTO t VALUES(1,2,2);
DELETE FROM t WHERE pk = 1 AND sk = 2;

When MySQL consumes these entries, the INSERT statement triggers a unique key conflict on the uk column.

Reproduce the issue

1. 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;

2. Insert data:

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

3. Observe the binlog entries generated (shown above). MySQL reports a unique key conflict on uk when consuming these entries.

Multi-stream binary logging

When multi-stream binary logging is enabled on PolarDB-X, create a separate replication link for each binary log stream. You can either:

• Configure multiple replication links on a single MySQL instance, or

• Dedicate a separate MySQL secondary instance to each binary log stream

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

Connection

Replication position

Behavior

Channel

Examples

Example 1: Start from a specific timestamp

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';

This starts replication from 2024-04-15 13:45:38 UTC (UNIX timestamp 1713159938).

Example 2: Start from a specific binlog file and offset

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';

This starts replication from offset 4 in binlog.000001.

Syntax

START SLAVE [ channel_option ]

channel_option:
    FOR CHANNEL channel

Examples

Start all replication links:

START SLAVE;

Start a specific replication link:

START SLAVE FOR CHANNEL 'TEST';

Syntax

STOP SLAVE [ channel_option ]

channel_option:
    FOR CHANNEL channel

Examples

Stop all replication links:

STOP SLAVE;

Stop a specific replication link:

STOP SLAVE FOR CHANNEL 'TEST';

Syntax

RESET SLAVE ALL [ channel_option ]

channel_option:
    FOR CHANNEL channel

Examples

Delete all replication links:

RESET SLAVE ALL;

Delete a specific replication link:

RESET SLAVE ALL FOR CHANNEL 'TEST';