Implement two-way synchronization between ApsaraDB RDS for MySQL instances by using DTS-Data Transmission Service(DTS)-阿里云帮助中心

Data Transmission Service (DTS) supports real-time two-way data synchronization between two MySQL databases, such as ApsaraDB RDS for MySQL and self-managed MySQL. This feature is applicable to scenarios including active geo-redundancy and geo-disaster recovery. This topic uses ApsaraDB RDS for MySQL instances as an example. The procedure for other data sources is similar.

Prerequisites

Source and target RDS MySQL instances are required. Create RDS instances if not already present.

Notes

When DTS performs initial full data synchronization, it consumes read and write resources from the source and destination databases, which may increase the database load. If your database has poor performance, low specifications, or high service traffic, the database load may increase significantly. Examples of such conditions include a source database with many slow SQL statements or tables without primary keys, or a destination database with deadlocks. This can even make the database service unavailable. Therefore, you must evaluate the performance of the source and destination databases before you synchronize data. We recommend that you perform data synchronization during off-peak hours, for example, when the CPU load of both databases 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 data other than the data from DTS is written 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 the destination database is an ApsaraDB RDS for MySQL or Serverless ApsaraDB RDS for MySQL instance (excluding 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 used only by DTS.
A two-way synchronization task includes forward and reverse synchronization tasks. When you configure or reset the task, if the destination object of one task matches the synchronization object of the other task:
- Allow only one task to synchronize full and incremental data. The other task supports only incremental synchronization.
- Data from the source of the current task synchronizes only to the destination of the current task. It does not serve as source data for the other task.

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

DTS supports two-way synchronization only between two MySQL databases. Two-way synchronization among multiple MySQL databases is not supported.

Two-way data synchronization architecture

Supported data sources

Two-way data synchronization between MySQL databases supports the following data sources. This topic uses ApsaraDB RDS for MySQL instances as an example. The procedure for other data sources is similar.

Source Database	Destination Database
ApsaraDB RDS for MySQL instance Self-managed database on an ECS instance Self-managed database connected over a leased line, VPN Gateway, or Smart Access Gateway Self-managed database connected over Database Gateway Self-managed database connected over Cloud Enterprise Network (CEN)	ApsaraDB RDS for MySQL instance Self-managed database on an ECS instance Self-managed database connected over a leased line, VPN Gateway, or Smart Access Gateway Self-managed database connected over Database Gateway Self-managed database connected over Cloud Enterprise Network (CEN)

Supported SQL operations for synchronization

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

Important

DDL statements are synchronized only in forward tasks (source to destination). Reverse tasks automatically filter out DDL statements.
When you execute DDL to modify a table schema during bidirectional synchronization, ensure DML writes on both databases are consistent with the new schema. For example, if you delete a column from the source, the destination may still write data based on the old schema that includes the deleted column, causing failures.

Supported conflict detection

To ensure data consistency, make sure that records with the same primary key, business primary key, or unique key are updated on only one node. If records are updated on both nodes concurrently, DTS handles the conflict based on the resolution policy that you configure.

DTS can detect the following types of conflicts:

Uniqueness conflicts caused by INSERT statements

A uniqueness constraint is violated when an INSERT statement is synchronized. For example, if records with the same primary key value are inserted into both nodes concurrently, the INSERT statement fails on the peer node because a record with the same primary key value already exists.
Mismatched records for UPDATE statements
- If the record to be updated by an UPDATE statement does not exist in the destination instance, DTS automatically converts the statement to an INSERT statement. This may cause a uniqueness conflict for a unique key.
- The record to be updated by an UPDATE statement has a primary key or unique key conflict.
The record to be deleted does not exist

The record to be deleted by a DELETE statement does not exist in the destination instance. If this type of conflict occurs, DTS automatically ignores the DELETE operation, regardless of the configured conflict resolution policy.

Important

Due to factors such as different system times between the two databases and synchronization latency, the DTS conflict detection mechanism cannot guarantee the complete prevention of data conflicts. When you use two-way synchronization, you must modify your application logic to ensure that records with the same primary key, business primary key, or unique key are updated on only one node.
DTS provides resolution policies for the preceding data synchronization conflicts. You can select a policy when you configure two-way synchronization.

Feature limitations

Incompatible with triggers

If the synchronization object is an entire database that contains triggers that update the content of synchronized tables, data inconsistency may occur. For example, a database contains two tables: Table A and Table B. Table A has a trigger that inserts a row of data into Table B after a row of data is inserted into Table A. In this case, if an INSERT operation is performed on Table A in the source instance during synchronization, the data in Table B becomes inconsistent between the source and destination instances.

In such cases, you must delete the corresponding trigger in the destination instance. The data of Table B is synchronized from the source instance. For more information, see How do I configure a synchronization or migration task when the source database contains triggers?.
RENAME TABLE limitations

The RENAME TABLE operation may cause data inconsistency. For example, if the synchronization object is a single table and you rename the table in the source instance during synchronization, the data of the table is not synchronized to the destination database. To prevent this issue, you can select the entire database to which the table belongs as the synchronization object.
DDL synchronization direction limitations

To ensure the stability of the two-way synchronization link, DDL updates can be synchronized in only one direction. This means that if DDL synchronization is configured for one direction, DDL synchronization is not supported in the reverse direction. Only DML synchronization is performed in the reverse direction.

Procedure

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

Important
When you purchase the instance, set both the source and destination instance types to MySQL and the synchronization topology to Two-way Synchronization.
Log on to the Data Transmission Service console.

Note
If you are redirected to the Data Management (DMS) console, you can click the icon in the lower-right corner and then click the icon to return to the previous version of the DTS console.
In the navigation pane on the left, click Data Synchronization.
At the top of the Synchronization Tasks page, select the region where the destination instance resides.
Find the purchased data synchronization instance and click Configure Synchronization Channel for the first synchronization task.

Important
A two-way data synchronization instance consists of two synchronization tasks that must be configured separately.

Configure the source and destination instances. Configure source and destination instances for RDS two-way synchronization

Category	Configuration	Description
N/A	Synchronization Task Name	DTS automatically generates a task name. You can specify a descriptive name for easy identification. The name does not need to be unique.
Source Instance Information	Instance Type	Select RDS Instance.
	Instance Region	The region of the source instance that you selected when you purchased the synchronization instance. This value cannot be changed.
	Instance ID	Select the ID of the RDS instance that you want to use as the data synchronization source.
	Database Account	Enter the database account of the source RDS instance. Important If the database type of the source RDS instance is MySQL 5.5 or MySQL 5.6, the Database Account and Database Password configuration items are not available.
	Database Password	Enter the password that corresponds to the database account.
	Encryption	Select Non-encrypted or SSL-encrypted as needed. If you select SSL-encrypted, you must enable SSL encryption for the RDS instance in advance. For more information, see Configure SSL encryption.
Destination Instance Information	Instance Type	Select RDS Instance.
	Instance Region	The region of the destination instance that you selected when you purchased the synchronization instance. This value cannot be changed.
	Instance ID	Select the ID of the RDS instance that you want to use as the data synchronization destination.
	Database Account	Enter the database account of the destination RDS instance. Important If the database type of the destination RDS instance is MySQL 5.5 or MySQL 5.6, the Database Account and Database Password configuration items are not available.
	Database Password	Enter the password that corresponds to the database account.
	Encryption	Select Non-encrypted or SSL-encrypted as needed. If you select SSL-encrypted, you must enable SSL encryption for the RDS instance in advance. For more information, see Configure SSL encryption.

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 ApsaraDB RDS for MySQL or ApsaraDB for MongoDB), DTS automatically adds the CIDR blocks of the DTS servers in the corresponding region to the whitelist of the Alibaba Cloud database instance. If the source or destination database is a self-managed database that is hosted on an ECS instance, DTS automatically adds the CIDR blocks of the DTS servers in the corresponding region to the security rules of the ECS instance. You must also ensure that the self-managed database allows access from the ECS instance. If the database is deployed in a cluster on multiple ECS instances, you must manually add the CIDR blocks of the DTS servers in the corresponding region to the security rules of each ECS instance. If the source or destination database is a self-managed database in an on-premises data center or a database from another cloud provider, you must manually add the CIDR blocks of the DTS servers in the corresponding region to allow access from the DTS servers. For the CIDR blocks of DTS servers, see CIDR blocks of DTS servers.

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.

Category	Configuration	Description
Set the synchronization policy	Exclude DDL Statements	Select Yes to not synchronize DDL operations. Select No to synchronize DDL operations. Important DDL syntax synchronization direction limitations. To ensure the stability of the two-way synchronization link, DDL synchronization is supported only in the forward direction, not in the reverse direction.
	DML Statements for Synchronization	Select the DML types to be synchronized. By default, Insert, Update, and Delete are selected. You can adjust the selection as needed.
	Conflict Resolution Policy	Select a resolution policy for synchronization conflicts. The default policy is TaskFailed. TaskFailed (If a conflict occurs, the task reports an error and exits) When the synchronization task encounters a conflict, it reports an error, enters the Failed state, and requires manual intervention. Ignore (If a conflict occurs, use the conflicting record in the destination instance) The synchronization task skips the conflicting statement and retains the existing record in the destination database. Overwrite (If a conflict occurs, overwrite the conflicting record in the destination instance) The synchronization task overwrites the conflicting record in the destination database with the source record.
Select the objects to be synchronized	N/A	In the Available box, click the objects that you want to synchronize (at the database or table level), and then click the icon to move them to the Selected box. Important If you select an entire database as the synchronization object, all schema changes to the objects in that database are synchronized to the destination database. By default, the names of the synchronization objects remain unchanged. To change the name of a synchronization object in the destination database, use the object name mapping feature. For more information, see Map the name of a synchronization object to a new name in the destination instance.
Rename Databases and Tables	N/A	You can use the object name mapping feature to rename the objects that are synchronized to the destination instance. For more information, see Object name mapping.
Source table DMS_ ONLINE_ Do you want to copy the temporary table to the target database during DDL	N/A	If you use DMS to perform online DDL operations on the source database, you can specify whether to synchronize temporary tables generated by online DDL operations. Yes: DTS synchronizes the data of temporary tables generated by online DDL operations. Note If online DDL operations generate a large amount of data, the data synchronization task may be delayed. No: DTS does not synchronize the data of temporary tables generated by online DDL operations. Only the original DDL data of the source database is synchronized. Note If you select No, the tables in the destination database may be locked.
Retry Time for Failed Connection	N/A	By default, if DTS fails to connect to the source or destination database, DTS retries within the next 720 minutes (12 hours). You can specify the retry time based on your needs. If DTS reconnects to the source and destination databases within the specified time, DTS resumes the data synchronization task. Otherwise, the data synchronization task fails. Note When DTS retries a connection, you are charged for the DTS instance. We recommend that you specify the retry time based on your business needs. You can also release the DTS instance at your earliest opportunity after the source and destination instances are released.

Click Next in the lower-right corner.
Configure advanced settings for initial synchronization.

In this step, the schema and data of the synchronization objects in the source instance are initialized in the destination instance as the baseline for subsequent incremental synchronization. The initial synchronization includes Initial Schema Synchronization and Initial Full Data Synchronization. Both are selected by default.

Important
Tables that are also synchronization objects in the reverse direction are not initialized.
Click Precheck in the lower-right corner.
Important
- Before the data synchronization task starts, a precheck is performed. The task can start only after all check items pass.
- If the precheck fails, click the icon next to the failed check item to view the details.
  - You can fix the issue based on the cause and run the precheck again.
  - If you want to ignore an alert, click Confirm shielding and then click Ignore the alarm and recheck to skip the alert item and run the precheck again.
When the Precheck dialog box shows The precheck is passed, close the Precheck dialog box to start the synchronization task.
Wait for the synchronization task to initialize. When the task status changes to Synchronizing, the initialization is complete.

You can view the data synchronization status on the Synchronization Tasks page.
Find the second synchronization task and click Configure Task. Repeat Step 5 to Step 12 to configure the task.
After the second synchronization task is configured, wait until the status of both synchronization tasks changes to Synchronizing. The configuration of two-way data synchronization is complete.