Replicate data from PolarDB-X to MySQL - PolarDB - Alibaba Cloud Documentation Center

This topic describes how to replicate data from PolarDB-X to MySQL.

Overview

PolarDB-X is compatible with the MySQL replication protocol. Therefore, you can replicate data from PolarDB-X to MySQL. You can execute the following SQL statements on MySQL to manage the replication link between PolarDB-X and MySQL. For more information about the SQL statements for data replication, see the official documentation of MySQL.
If your PolarDB-X instance is compatible with MySQL 5.7, use a MySQL 5.7 database as the secondary database of PolarDB-X. If your PolarDB-X instance is compatible with MySQL 8.0, use a MySQL 8.0 database as the secondary database of PolarDB-X. We recommend that you use MySQL 8.0.27 or later.
If you enable the multi-stream binary logging feature, you must create a replication link for each binary log stream when you configure a MySQL database as the secondary database of PolarDB-X. You can configure multiple replication links on a single MySQL instance or configure a separate MySQL secondary instance for each binary log stream. If you want to configure multiple replication links on a single MySQL instance, the broadcast replication by using DDL statements is difficult.

Usage notes

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. However, in PolarDB-X to MySQL database replication scenarios, where PolarDB-X is the primary database and MySQL is the secondary database, this optimization may cause data inconsistency issues when MySQL consumes binlogs. To mitigate this risk, you can disable the optimization by executing the SET GLOBAL DML_USE_RETURNING = FALSE statement. Disabling the optimization may reduce the performance of INSERT IGNORE statements on tables with GSIs, potentially affecting data import tasks. Evaluate your business requirements and workloads before deciding whether to disable the optimization.

Reproduce the issue

Create a table named t 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 into the table by using INSERT INTO and INSERT IGNORE.

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

The INSERT IGNORE execution process for which RETURNING optimization is enabled first inserts data into the table without checking for uniqueness. Upon detecting duplicates, it performs a DELETE operation for the duplicate row. This results in the following binlog entries:
```
INSERT INTO t VALUES(1,2,2);
DELETE FROM t WHERE pk = 1 AND sk = 2;
```
Note
When the MySQL database consumes these binlog entries, it executes the INSERT statement, which causes a unique key conflict on the uk column.

Procedure

Create a primary/secondary 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
- MASTER_HOST: the address of the primary instance.
- MASTER_USER: the username that is used to connect to the primary instance.
- MASTER_PASSWORD: the password that is used to connect to the primary instance.
- MASTER_PORT: the port number that is used to connect to the primary instance.
- MASTER_LOG_FILE: the name of the log file on the primary instance.
- MASTER_LOG_POS: the offset from which the binlog replication starts in the log file on the primary instance.
- MASTER_LOG_TIME_SECOND: the time offset of the primary instance. The value is a UNIX timestamp representing the number of seconds that have elapsed since January 1, 1970, 00:00:00 UTC. The MASTER_LOG_TIME_SECOND parameter is ignored if the MASTER_LOG_FILE and MASTER_LOG_POS parameters are configured to specify an exact offset.
- SOURCE_HOST_TYPE: the type of the primary instance. Default value: MYSQL. If the primary instance is a PolarDB-X 2.0 instance, you must explicitly set this parameter to POLARDBX.
- STREAM_GROUP: the name of the stream group if a multi-stream group is created. If the primary instance is a PolarDB-X 2.0 instance and you want to use the multi-stream binlog service of the primary instance, you must specify this parameter.
- WRITE_SERVER_ID: the ID of the write server.
- TRIGGER_AUTO_POSITION: specifies whether to automatically use the latest offset of the primary instance. Default value: FALSE.
- WRITE_TYPE: the write mode. The default value is SPLIT, which indicates non-transactional parallel replication. A value of SERIAL indicates non-transactional serial replication, and a value of TRANSACTION indicates transactional serial replication.
- MODE: the synchronization mode. A value of INCREMENTAL indicates incremental data synchronization, and a value of IMAGE indicates schema synchronization, full data synchronization, and incremental data synchronization.
- CONFLICT_STRATEGY: the conflict policy. The default value is OVERWRITE, which indicates that the data with constraint violation is overwritten by executing the REPLACE INTO statement. A value of INTERRUPT indicates that the replication is interrupted, and a value of IGNORE indicates that the conflict is ignored.
- IGNORE_SERVER_IDS: the ID of the server to ignore.
- channel_option: the name of the link, which must be different from existing link names.

Example 1

Execute the following statement to create a replication link and start synchronization from 1713159938 (2024-04-15 13:45:38) in the log file:

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

Execute the following statement to create a replication link and start synchronization from Offset 4 in the binlog.000001 file on the primary instance:

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

Note

For more information, see CHANGE MASTER TO.

Start a primary/secondary replication link

Syntax

START SLAVE [ channel_option ]

channel_option:
    FOR CHANNEL channel

Example 1
Execute the following statement to enable all the primary/secondary replication links that are created by executing the CHANGE MASTER TO statement:
```
START SLAVE;
```
Example 2
Execute the following statement to enable the primary/secondary replication link whose name is TEST:
```
START SLAVE for channel 'TEST';
```

Note

For more information, see START SLAVE.

Stop a primary/secondary replication link

Syntax

STOP SLAVE [ channel_option ]

channel_option:
    FOR CHANNEL channel

Example 1
Execute the following statement to disable all the primary/secondary replication links that are created by executing the CHANGE MASTER TO statement:
```
STOP SLAVE;
```
Example 2
Execute the following statement to disable the primary/secondary replication link whose name is TEST:
```
STOP SLAVE for channel 'TEST';
```

Note

For more information, see STOP SLAVE.

Delete a primary/secondary replication link

Important

Stop the data replication process before you delete a primary/secondary replication link.

Syntax

RESET SLAVE ALL [ channel_option ]

channel_option:
    FOR CHANNEL channel

Example 1
Execute the following statement to remove all primary/secondary replication links established by executing the CHANGE MASTER TO statement.
```
RESET SLAVE ALL;
```
Example 2
Execute the following statement to remove the primary/secondary replication link with the channel named TEST.
```
RESET SLAVE ALL for channel 'TEST';
```

Note

For more information, see RESET SLAVE ALL.