Data Transmission Service:One-way synchronization between RDS MySQL instances

Table structure

• Tables to sync must have a PRIMARY KEY or UNIQUE constraint with all unique fields. Without this, the destination may contain duplicate records.

• If you select tables (not databases) as sync objects and plan to rename tables or columns, a single task supports up to 1,000 tables. For more than 1,000 tables, configure multiple tasks or sync at the database level.

Binary log

The following binary log settings must be in place before DTS can read changes from the source:

• binlog_row_image must be set to full. Binary logging is enabled by default on ApsaraDB RDS for MySQL instances, but verify this parameter. See Modify instance parameters.

  • If the source is a self-managed MySQL database: you must enable binary logging and set binlog_format to row and binlog_row_image to full.

  • If the source is a self-managed MySQL database deployed in a dual-primary cluster: you must also set log_slave_updates to ON to ensure DTS can obtain all binary logs. See Create an account for a self-managed MySQL database and configure binary logging.

• Binary log retention must be at least 7 days. If DTS cannot access the required binary logs, the task fails and data loss may occur. See Manage binary log files.

Do not execute DDL statements on the source database during schema synchronization or full data synchronization. This causes the task to fail.

Version and character set compatibility

• The destination database version must be the same as or later than the source. An older destination version may cause compatibility issues.

• If the source database is MySQL 8.0.23 or later and includes invisible columns, DTS cannot obtain the data of those columns — data loss occurs.

• If the destination database is MySQL 8.0.23 or later and includes invisible columns, the destination columns to receive data cannot be found, causing the DTS instance to fail and data loss to occur.

• In both cases, run ALTER TABLE <table_name> ALTER COLUMN <column_name> SET VISIBLE; to make invisible columns visible before starting the task. Tables without primary keys auto-generate invisible primary keys; make those visible too. See Invisible Columns and Generated Invisible Primary Keys.

• If you are not using schema synchronization, make sure field type compatibility is maintained. For example, a text field in the source mapped to a varchar(255) field in the destination may cause data truncation.

• If the data contains 4-byte characters (such as rare characters or emojis), destination tables must use the UTF8mb4 character set. If using schema synchronization, set the character_set_server parameter to UTF8mb4 in the destination database.

• Column names in MySQL are not case-sensitive. If the source has multiple columns whose names differ only in capitalization, they are written to the same column in the destination, causing unexpected results.

Online DDL

• Do not use pt-online-schema-change for online DDL on tables selected as sync objects. Use Data Management (DMS) instead. See Perform lock-free DDL operations.

• If DDL operations fail in the destination, the DTS task continues running. Check task logs for failed DDL statements. See View task logs.

• DTS does not synchronize data that uses parsers defined by comments.

Data consistency

• Do not write data from other sources to the destination during synchronization. This causes data inconsistency.

• Data generated by binary log change operations (such as data restored from a physical backup or cascade operations) is not recorded or synchronized to the destination while the task is running.

Foreign key behavior

• During schema synchronization, DTS synchronizes foreign keys from the source to the destination.

• During full and incremental data synchronization, DTS temporarily disables foreign key constraint checks and cascade operations at the session level. Cascade update or delete operations on the source during this time may cause data inconsistency.

Performance impact

Full data synchronization reads all data from the source database once, which increases load on both databases. To reduce risk:

• Run synchronization tasks during off-peak hours.

• Expect the destination tablespace to be larger than the source after full synchronization, due to concurrent INSERT fragmentation.

• After the task status changes to Completed, run analyze table <table_name> to verify that data was written correctly to the destination. (For example, a high availability (HA) switchover in the source may cause some data to be written only to memory.)

Special cases

For self-managed MySQL sources:

• If a primary/secondary switchover occurs on the source database while the task is running, the task fails.

• DTS calculates synchronization latency based on the timestamp of the latest synchronized data in the destination and the current timestamp in the source. If no DML operations are performed on the source for a long time, the latency may be inaccurate. If latency is too high, perform a DML operation on the source to update it. If you select an entire database as the sync object, you can create a heartbeat table that is updated every second to keep the latency accurate.

• DTS executes CREATE DATABASE IF NOT EXISTS 'test' in the source on a schedule to advance the binary log position.

For ApsaraDB RDS for MySQL sources:

• Read-only ApsaraDB RDS for MySQL 5.6 instances (which do not record transaction logs) cannot be used as a source.

• DTS executes CREATE DATABASE IF NOT EXISTS 'test' in the source on a schedule to advance the binary log position.

• ApsaraDB RDS for MySQL instances with the EncDB feature enabled do not support full data synchronization. Instances with Transparent Data Encryption (TDE) enabled support schema synchronization, full data synchronization, and incremental data synchronization.

If DTS technical support restores a failed task:

• Support attempts restoration within 8 hours. The task may be restarted and certain task parameters may be modified. Database parameters are not modified.

Step 1: Go to the Data Synchronization Tasks page

1. Log on to the Data Management (DMS) console.

2. In the top navigation bar, click Data + AI.

3. In the left-side navigation pane, choose DTS (DTS) > Data Synchronization.

Navigation may differ based on your DMS console mode and layout. See Simple mode and Customize the layout and style of the DMS console. Alternatively, go directly to the Data Synchronization Tasks page of the new DTS console.

Step 2: Create the task

1. Select the region where your data synchronization instance resides.

   In the new DTS console, select the region in the top navigation bar instead.

2. Click Create Task.

3. (Optional) Click New Configuration Page in the upper-right corner of the page.

   Skip this step if the Back to Previous Version button is displayed. The new configuration page is recommended.

Step 3: Configure source and destination databases

Fill in the following parameters for both the source and destination:

Step 4: Test connectivity

Click Test Connectivity and Proceed.

DTS automatically adds its server CIDR blocks to the whitelist of Alibaba Cloud database instances. For ECS-hosted databases, DTS adds the CIDR blocks to the ECS security group rules and you must make sure that the ECS instance can access the database — if the database spans multiple ECS instances, add the CIDR blocks to each instance's security group manually. For on-premises databases or databases hosted by third-party cloud providers, add the DTS server CIDR blocks to the database whitelist manually. See CIDR blocks of DTS servers.

Step 5: Configure sync objects

In the Configure Objects step, fill in the following parameters:

Renaming an object with object name mapping may cause other objects that depend on it to fail syncing.

Step 6: Configure advanced settings

Click Next: Advanced Settings and fill in the following parameters:

Step 7: Configure data verification (optional)

Click Next Step: Data Verification to set up data verification for the task. See Configure a data verification task.

Step 8: Run the precheck

Click Next: Save Task Settings and Precheck.

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

DTS runs a precheck before the task can start. Wait for the precheck to complete:

• If an item fails, click View Details to see the cause and fix the issue, then click Precheck Again.

• If an item shows an alert, either fix the issue or click Confirm Alert Details > Ignore > OK > Precheck Again to proceed. Ignoring alerts may lead to data inconsistency.

Step 9: Purchase the instance

After Success Rate reaches 100%, click Next: Purchase Instance.

On the buy page, configure the following:

Read and select Data Transmission Service (Pay-as-you-go) Service Terms, then click Buy and Start and confirm.

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