Data Transmission Service:Synchronize data from an ApsaraDB RDS for MySQL instance to a PolarDB-X 1.0 instance

ApsaraDB RDS for MySQL

Binary logging is enabled by default on ApsaraDB RDS for MySQL instances. Make sure that:

• The binlog_row_image parameter is set to full. If it is not, a precheck error occurs and the task cannot start. See Set instance parameters.

• Local binary logs are retained for at least 3 days (7 days recommended). If logs are purged before DTS can read them, the task fails. In extreme cases, data loss or inconsistency may occur. Tasks that fail due to insufficient log retention are not covered by the DTS Service-level agreement (SLA). To set the Retention Period for local binary logs, see Automatically delete local logs.

ApsaraDB RDS for MySQL 5.6 read-only instances do not record transaction logs and cannot be used as a source database.

Self-managed MySQL

For a self-managed MySQL database, configure the following parameters:

Retain binary logs for at least 7 days. If logs are purged before DTS can read them, the task fails, and issues caused by insufficient log retention are not covered by the DTS SLA.

Additional requirements:

• Primary/primary architecture: Enable the log_slave_updates parameter so that DTS can obtain all binary logs from both nodes. See Create a database account for a self-managed MySQL database and configure binary logging.

• Amazon Aurora MySQL or cluster-mode instances: Make sure the domain name or IP address configured for the task always points to the read/write (RW) node. Otherwise, the task may not run properly.

Source database limits

• The server on which the source database is deployed must have sufficient outbound bandwidth. Insufficient bandwidth reduces synchronization speed.

• Tables to be synchronized must have a primary key or a UNIQUE constraint with unique field values. Tables that lack these constraints may produce duplicate data in the destination.

• Only tables can be selected as synchronization objects.

• When using object name mapping (for example, mapping table or column names), a single task supports a maximum of 1,000 tables. To synchronize more than 1,000 tables with name mapping, split them into multiple tasks.

• The PolarDB-X 1.0 instance storage class must be RDS MySQL (a private custom RDS). PolarDB MySQL is not supported.

• Do not perform DDL operations during full data synchronization — this causes the task to fail.

  During the full synchronization phase, DTS queries the source database and may generate metadata locks, which can block DDL operations on the source.

• Data changes from operations not recorded in binary logs are not synchronized. This includes data recovery using physical backups and cascade operations.

  If this occurs, remove the affected database or table from the synchronization objects and then add it back, if your business permits. See Modify synchronization objects.

• Invisible columns (MySQL 8.0.23 and later): If the data to be synchronized contains invisible columns, DTS cannot obtain the data in those columns, which may cause data loss. Run the following command to make invisible columns visible:

  ALTER TABLE <table_name> ALTER COLUMN <column_name> SET VISIBLE;

  Tables without a primary key automatically generate an invisible primary key, which must also be made visible. See Invisible Columns and Generated Invisible Primary Keys.

Other limits

• EncDB (always-confidential): Full data synchronization is not supported when the always-confidential (EncDB) feature is enabled on the source RDS MySQL instance.

  Instances with Transparent Data Encryption (TDE) enabled support schema synchronization, full data synchronization, and incremental data synchronization.

• If you perform online DDL operations that use temporary tables in the source database, such as merging multiple tables, data may be lost in the destination database or the DTS instance may fail.

• Do not use tools such as pt-online-schema-change to perform online DDL operations on objects being synchronized — this causes the task to fail.

• Writing data from other sources to the destination database during synchronization may cause data inconsistency.

• Evaluate the performance impact on source and destination databases before starting synchronization. Run synchronization tasks during off-peak hours when possible. Full data synchronization uses read and write resources on both databases and may increase server load.

• After full data synchronization completes, concurrent INSERT operations may cause fragmentation in the destination tables. The used tablespace of the destination database may be larger than that of the source database.

• If a DTS instance fails, the DTS support team will attempt to recover it within 8 hours. Recovery operations may include restarting the instance or adjusting DTS instance parameters (not database parameters). See Modify instance parameters.

Special cases for self-managed MySQL sources

• If a primary/secondary failover occurs in the source database during synchronization, the task fails.

• Synchronization latency is calculated from the difference between the current timestamp and the timestamp of the last synchronized data record. If no DML operations are performed on the source database for a long time, the displayed latency may be inaccurate. Perform a DML operation on the source to update the latency reading.

  If you choose to synchronize the entire database, create a heartbeat table. DTS updates or writes to the heartbeat table every second to keep the latency reading accurate.

• DTS periodically runs CREATE DATABASE IF NOT EXISTS \test\`` on the source database to advance the binary log offset.

Special cases for ApsaraDB RDS for MySQL sources

• ApsaraDB RDS for MySQL instances that do not record transaction logs, such as read-only instances of ApsaraDB RDS for MySQL 5.6, cannot be used as a source database.

• DTS periodically runs CREATE DATABASE IF NOT EXISTS \test\`` on the source database to advance the binary log offset.

Step 1: Go to the Data Synchronization page

Use either the DTS console or the DMS console.

Step 2: Configure source and destination databases

1. Click Create Task.

2. On the task configuration page, set the following parameters.

General

Source database

Destination database

1. Click Test Connectivity and Proceed.

Make sure that DTS server CIDR blocks are added to the security settings of both the source and destination databases. See Add DTS server IP addresses to a whitelist. If the source or destination database is a self-managed database not accessed through Alibaba Cloud Instance, click Test Connectivity in the CIDR Blocks of DTS Servers dialog box.

Step 3: Configure synchronization objects

In the Configure Objects step, set the following parameters.

Click Next: Advanced Settings.

Step 4: Configure advanced settings

Step 5: Run the precheck

Click Next: Save Task Settings and Precheck.

To view the API parameters for this task configuration, hover over Next: Save Task Settings and Precheck and click Preview OpenAPI parameters.

The task runs a precheck before starting. The task can only start after the precheck passes.

• If any item fails, click View Details next to the failed item, fix the issue, and click Precheck Again.

• If an item triggers an alert that can be ignored, click Confirm Alert Details, then click Ignore > OK > Precheck Again. Ignoring alert items may cause data inconsistency.

Step 6: Purchase the instance

1. Wait until the Success Rate reaches 100%, then click Next: Purchase Instance.

2. On the purchase page, configure the following parameters.

1. Read and select Data Transmission Service (Pay-as-you-go) Service Terms.

2. Click Buy and Start, then click OK to confirm.

The task appears in the task list. Monitor its progress there.