Two-way synchronization between MySQL instances

Prerequisites

You must have a source and a destination ApsaraDB RDS for MySQL instance. If you need to create one, see create an ApsaraDB RDS for MySQL instance.

Notes

• When DTS performs an 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 a heavy workload (for example, the source database has a large number of slow SQL queries or tables without primary keys, or the destination database has deadlocks), the database load may increase significantly. This can even cause the database service to become unavailable. Before synchronizing data, evaluate the performance of your source and destination databases. Perform data synchronization during off-peak hours, for example, when the CPU utilization 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 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 the destination database is an ApsaraDB RDS for MySQL or ApsaraDB RDS for MySQL Serverless 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

Supported synchronization topologies

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

Supported data sources

Two-way data synchronization is supported for the following MySQL data sources. This topic uses ApsaraDB RDS for MySQL instances as the example.

Supported SQL operations

Supported conflict detection

To ensure data consistency, update records with the same primary or unique key on only one of the two synchronized nodes. If records are updated on both nodes concurrently, DTS handles the conflict based on the conflict resolution policy that you configure for the data synchronization task.

DTS uses conflict detection and resolution to help ensure the stability of two-way synchronization instances. DTS can detect the following types of conflicts:

• Uniqueness conflicts caused by INSERT operations

  A uniqueness constraint is violated when an INSERT operation is synchronized. For example, if records with the same primary key value are inserted into both nodes at or near the same time, the INSERT operation fails on the peer node because a record with the same primary key value already exists.

• Conflicts related to UPDATE operations

  • If the record to be updated by an UPDATE operation does not exist in the destination instance, DTS automatically converts the operation to an INSERT operation. This may cause a uniqueness conflict for a unique key.

  • The record to be updated by an UPDATE operation has a primary key or unique key conflict.

• Record to be deleted does not exist

  A DELETE operation targets a record that 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.

Limitations

• Incompatible with triggers

  If the synchronization object is an entire database that contains a trigger that updates 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 you insert a row into Table A on the source instance, the data in Table B will become inconsistent between the source and destination.

  In such cases, you must delete the corresponding trigger in the destination instance. Instead, data for 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 table's data will no longer be synchronized to the destination database. To prevent this, select the entire database that contains the table 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. The reverse synchronization task supports only DML operations.

Procedure

1. 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.

2. Log on to the Data Transmission Service console.

   Note

   If you are redirected to the Data Management (DMS) console, click the icon in the lower-right corner and then click the icon to return to the previous version of the Data Transmission Service (DTS) console.

3. In the left-side navigation pane, click Data Synchronization.

4. At the top of the Synchronization Tasks page, select the region where the destination instance is located.

5. Locate 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.

6. Configure the source and destination instances for the synchronization channel.

   Category	Parameter	Description
   N/A	Synchronization task name	DTS automatically generates a name for the synchronization task. For easy identification, specify a descriptive name. The name does not need to be unique.
   Source instance information	Instance type	Select RDS Instance.
   	Instance region	The region you selected for the source instance during purchase. This parameter 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 parameters are not available.
   	Database password	Enter the password that corresponds to the database account.
   	Connection method	Select Non-encrypted or SSL-encrypted. 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 you selected for the destination instance during purchase. This parameter 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 parameters are not available.
   	Database password	Enter the password that corresponds to the database account.
   	Connection method	Select Non-encrypted or SSL-encrypted. If you select SSL-encrypted, you must enable SSL encryption for the RDS instance in advance. For more information, see Configure SSL encryption.

7. Click Set Whitelist and Next at the lower-right corner of the page.

   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 IP addresses of the DTS servers in the corresponding region to the whitelist of the instance. If the source or destination database is a self-managed database on an ECS instance, DTS automatically adds the IP addresses of the DTS servers in the corresponding region to the security group rules of the ECS instance. You must also ensure that the database is configured to allow access from the DTS servers. If the database is deployed in a cluster across multiple ECS instances, you must manually add the IP addresses of the DTS servers in the corresponding region to the security group rules of each of the other ECS instances. If the source or destination database is a self-managed database in an on-premises data center (IDC) or a database on another cloud platform, you must manually add the IP addresses of the DTS servers in the corresponding region to allow access from the DTS servers. For the IP addresses of the DTS servers, see IP address ranges 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.

8. Configure the synchronization policy and objects.

   Category	Parameter	Description
   Synchronization policy	Exclude DDL Statements	Yes: Does not synchronize DDL operations. No: Synchronizes DDL operations. Important DDL synchronization has 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 operations to be synchronized. By default, Insert, Update, and Delete are selected. You can change the selection based on your business requirements.
   	Conflict resolution policy	Select a policy for handling synchronization conflicts. The default policy is TaskFailed. You can select a policy based on your business requirements. TaskFailed (If a conflict occurs, the task reports an error and stops) This is the default conflict resolution policy. When a data synchronization task encounters a conflict, the task reports an error and stops. The task enters the Failed state and requires user intervention. Ignore (If a conflict occurs, use the record from the destination instance) When a data synchronization task encounters a conflict, it skips the current SQL operation and continues. The conflicting record in the destination database is used. Overwrite (If a conflict occurs, overwrite the record in the destination instance) When a data synchronization task encounters a conflict, it overwrites the conflicting record in the destination database.
   Select synchronization objects	N/A	In the Source Objects box, select the databases or tables that you want to synchronize. Then, click the icon to move them to the Selected Objects box. Important If you select an entire database as a 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. If you need to change the name of a synchronization object in the destination database, you must 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.
   Object name mapping	N/A	Change the names of synchronized objects in the destination instance. For more information, see Map databases, tables, and columns.
   Synchronize temporary tables for DMS online DDL	N/A	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.
   Connection retry duration	N/A	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.

9. After you complete the preceding configurations, click Next at the lower-right corner of the page.

10. Configure advanced settings for initial synchronization.

    This step initializes the destination instance with the schema and data of the synchronization objects from the source instance. This serves as the baseline data for subsequent incremental data synchronization. The initial synchronization types are initial schema synchronization and initial full data synchronization. By default, both initial schema synchronization and initial full data synchronization are selected.

    Important

    If some tables are included in the synchronization objects of both the forward and reverse tasks, those tables are not initialized.

11. After you complete the preceding configurations, click Precheck and Start at the lower-right corner of the page.

    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 a failed 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 Ignore and then Ignore Alarm and Re-precheck to run the precheck again.

12. In the Precheck dialog box, after Precheck Passed is displayed, close the Precheck dialog box, and the synchronization task will start.

13. Wait for the task to initialize until its status changes to Synchronizing.

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

14. Locate the second synchronization task and click Configure Synchronization Channel. Repeat steps 6 through 13 to configure the task.

15. After you configure the second synchronization task, wait until the status of both tasks changes to Synchronizing. This completes the two-way data synchronization setup.