Configuration process for one-way synchronization between ApsaraDB RDS for MySQL instances - Data Transmission Service

Data Transmission Service (DTS) supports data synchronization between two MySQL databases. This topic describes how to configure one-way synchronization between two ApsaraDB RDS for MySQL instances.

Prerequisites

You have created the source and destination ApsaraDB RDS for MySQL instances. For more information, see Create an ApsaraDB RDS instance.
The database engine for both the source and destination instances is MySQL.

Precautions

During initial full data synchronization, DTS consumes read and write resources from the source and destination databases, which increases the database load. If database performance is poor, instance specifications are low, or business traffic is heavy (for example, the source database has many slow SQL queries or tables without primary keys, or the destination database experiences deadlocks), the database load increases and may even cause the service to become unavailable. Before you synchronize data, evaluate the performance of your source and destination instances. We recommend performing data synchronization during off-peak hours, for example, when the CPU utilization of both instances is below 30%.
During data synchronization, do not use tools such as gh-ost or pt-online-schema-change to perform online DDL operations on the synchronization objects in the source database. Otherwise, the synchronization task fails.
If no other sources are writing data to the destination database, you can use Data Management (DMS) to perform online DDL operations. For more information, see Change table schemas without locking tables.
If a source table lacks a primary key or a unique constraint and contains non-unique data, duplicate data may be created in the destination database.
During initial full data synchronization, concurrent INSERT operations cause table fragmentation in the destination instance. After the initial full data synchronization is complete, the destination instance will use more table space than the source instance.
If the destination database is an ApsaraDB RDS for MySQL instance (except for versions 5.7 and 8.0), DTS creates a data synchronization account named dtssyncwriter in the destination database. This account has write permissions and is for internal use by DTS only.

Billing

Synchronization type	Pricing
Schema synchronization and full data synchronization	Free of charge.
Incremental data synchronization	Charged. For more information, see Billing overview.

Supported synchronization topologies

One-to-one one-way synchronization
One-to-many one-way synchronization
Cascading one-way synchronization
Many-to-one one-way synchronization

For more information about each synchronization topology and its precautions, see Data synchronization topologies.

Supported SQL operations

Operation type	SQL statement
DML	INSERT, UPDATE, DELETE, and REPLACE
DDL	ALTER TABLE and ALTER VIEW CREATE FUNCTION, CREATE INDEX, CREATE PROCEDURE, CREATE TABLE, and CREATE VIEW DROP INDEX and DROP TABLE RENAME TABLE Important RENAME TABLE operations may cause data inconsistency between the source and destination databases. For example, if you select a table as the object to be synchronized and rename the table during data synchronization, the data of this table is not synchronized to the destination database. To prevent this situation, you can select the database to which this table belongs as the object to be synchronized when you configure the data synchronization task. Make sure that the databases to which the table belongs before and after the RENAME TABLE operation are added to the objects to be synchronized. TRUNCATE TABLE

Limitations

Incompatible with triggers

If you synchronize an entire database and a trigger within that database updates another table in the same database, data inconsistency may occur between the source and destination databases. For a solution, see Configure a data synchronization or migration task for a source database that contains a trigger.
RENAME TABLE limitations

The RENAME TABLE operation can cause data inconsistency. For example, if you select only Table A for synchronization and it is renamed to Table B in the source instance, Table B is not synchronized to the destination instance. To prevent this issue, select the entire database that contains both Table A and Table B as the synchronization object when you configure the data synchronization task.

Procedure

Purchase a data synchronization instance. For more information, see Purchase a data synchronization task.

Note
When you purchase the instance, select MySQL as the database engine for both the source and destination instances, and select One-way Synchronization as the synchronization topology.
Log on to the Data Transmission Service (DTS) console.
In the left-side navigation pane, click Data Synchronization.
At the top of the Data Synchronization page, select the region where the data synchronization instance resides.
Find the purchased data synchronization instance and click Configure Synchronization Task.

Configure the source and destination instance information.

Section	Parameter	Description
N/A	Task Name	DTS automatically generates a task name. We recommend using a descriptive name for easier identification.
Source Instance Information	Instance Type	Select RDS Instance.
	Instance Region	The region of the source instance that you selected when you purchased the data synchronization instance. This cannot be changed.
	Instance ID	Select the ID of the source RDS instance.
	Database Account	Enter the database account of the source RDS instance. Note If the source RDS instance runs MySQL 5.5 or MySQL 5.6, you do not need to configure the Database Account and Database Password parameters.
	Database Password	Enter the password for the database account.
	Connection Method	Select Non-encrypted or SSL-encrypted as needed. If you select SSL-encrypted, you must first enable SSL encryption for the RDS instance. For more information, see Configure SSL encryption. Important Currently, the Encryption parameter is available only for regions in the Chinese mainland and the China (Hong Kong) region.
Destination Instance Information	Instance Type	Select RDS Instance.
	Instance Region	The region of the destination instance that you selected when you purchased the data synchronization instance. This cannot be changed.
	Instance ID	Select the ID of the destination RDS instance.
	Database Account	Enter the database account of the destination RDS instance. Note If the destination RDS instance runs MySQL 5.5 or MySQL 5.6, you do not need to configure the Database Account and Database Password parameters.
	Database Password	Enter the password for the database account.
	Connection Method	Select Non-encrypted or SSL-encrypted as needed. If you select SSL-encrypted, you must first enable SSL encryption for the RDS instance. For more information, see Configure SSL encryption. Important Currently, the Encryption parameter is available only for regions in the Chinese mainland and the China (Hong Kong) region.

In the lower-right corner of the page, click Set Whitelist and Next.

If the source or destination database is an Alibaba Cloud database instance, such as an ApsaraDB RDS for MySQL or ApsaraDB for MongoDB instance, DTS automatically adds the CIDR blocks of DTS servers to the IP address whitelist of the instance. If the source or destination database is a self-managed database hosted on an Elastic Compute Service (ECS) instance, DTS automatically adds the CIDR blocks of DTS servers to the security group rules of the ECS instance, and you must make sure that the ECS instance can access the database. If the self-managed database is hosted on multiple ECS instances, you must manually add the CIDR blocks of DTS servers to the security group rules of each ECS instance. If the source or destination database is a self-managed database that is deployed in a data center or provided by a third-party cloud service provider, you must manually add the CIDR blocks of DTS servers to the IP address whitelist of the database to allow DTS to access the database. For more information, see Whitelist DTS server IP addresses.

Warning
Adding the public IP address blocks of the DTS service, either automatically or manually, may pose security risks. Using this product, you acknowledge that you understand and accept the potential security risks and that you must implement basic security measures. These measures include, but are not limited to, strengthening password security, limiting the ports open to each CIDR block, using authentication for internal API calls, and regularly checking and restricting unnecessary CIDR blocks. Alternatively, you can connect through a private network using a leased line, VPN Gateway, or Smart Access Gateway.

Configure the synchronization policy and objects.

Parameter	Description
Select Synchronization Objects	In the Source Objects box, select the objects to synchronize and click the icon to move them to the Selected Objects box. You can select objects at the database and table levels. Note If you select an entire database as a synchronization object, all schema change operations on objects within that database are synchronized to the destination database. By default, object names in the destination database are the same as in the source database. To rename an object in the destination database, use the object name mapping feature. For more information, see Set object names in the destination instance.
Object Name Mapping	Change the names of synchronized objects in the destination instance. For more information, see Map databases, tables, and columns.
Replicate temporary tables to the destination database during online DDL operations on source tables in DMS	If you use Data Management (DMS) to perform online DDL changes on the source database, you can choose whether to synchronize the temporary tables generated by the DDL changes. Yes: Synchronizes the temporary tables generated by online DDL changes. Note If a large amount of temporary table data is generated by online DDL changes, the data synchronization task may be delayed. No: Does not synchronize the temporary tables generated by online DDL changes. Only the original DDL operations from the source database are synchronized. Note This option causes tables in the destination database to be locked.
Retry duration for connection failure to source or destination database	If DTS cannot connect to the source or destination instance, it retries for 720 minutes (12 hours) by default. You can also specify a custom retry duration. If DTS reconnects to the source or destination instance within the specified duration, the synchronization task automatically resumes. Otherwise, the task fails. Note You are billed for task run time during connection retries. Customize the retry duration based on your business needs, or release the DTS instance as soon as the source and destination instances are released.

After completing the preceding configurations, click Next in the lower-right corner of the page.
Configure advanced settings for initial synchronization.
Note
- This step initializes the schema and data of the selected objects from the source instance to the destination instance to establish a baseline for subsequent incremental data synchronization.
- The initial synchronization types include initial schema synchronization and initial full data synchronization. By default, you must select both Initial Schema Synchronization and Initial Full Data Synchronization.
After completing the preceding configurations, click Precheck and Start in the lower-right corner of the page.
Note
- A precheck runs before the synchronization task starts, and you can only start the task after it passes.
- If the precheck fails, click the icon next to the failed item to view the details.
  
  You can fix the issues based on the cause and run the precheck again.
  
  If you do not need to fix the items that triggered warnings, you can click Ignore or Ignore Warnings and Rerun Precheck to skip the warnings and run the precheck again.
After the Precheck dialog box shows Precheck Passed, close the Precheck dialog box. The synchronization task starts.
Wait for the task to complete link initialization and enter the Synchronizing state.

You can view the status of the data synchronization task on the Data Synchronization page.

The task list includes columns for Instance ID/Task Name, Status, Synchronization Overview (latency and speed), Billing Method, Synchronization Topology, and Actions. The Actions column provides options such as Suspend Synchronization, Change to Subscription, and Upgrade. At the top of the page, you can search for tasks by name and filter or sort them by status.