tencent cloud

Overview

DTS supports two-way data sync between two databases, which can be applied to multi-site active-active scenarios. In a two-way sync task, two one-way sync tasks are created to establish a two-way topology, and data can be written into both database instances at the same time during sync.

Two-way data sync must follow restrictions on one-way sync and relevant operations. For more information, see the appropriate sync scenario in Databases Supported by Data Sync.

Notes

• During full data sync, DTS consumes certain source instance resources, which may increase the load and pressure of the source database. If your database configuration is low, we recommend you sync the data during off-peak hours.
• To avoid duplicate data, make sure that the tables to be synced have a primary key or non-null unique key.
• You should plan the data in advance. The two source databases are responsible for updating (adding, deleting, and modifying) data with different primary keys so as to avoid problems such as primary key conflict and mutual overwriting of data with the same primary key (for example, data records with primary keys 1, 3, and 5 are updated in database A, while data records with primary keys 2, 4, and 6 are updated in database B). If there are duplicate primary keys in the two source databases for business reasons, select an appropriate conflict resolution policy as instructed in Recommended Configurations for Typical Use Cases to make the sync behavior and data meet the expectations.
• Prepare the target database and grant the account executing the sync task the permissions of the source and target databases.

Use Limits

• DDL statements can be executed in at most one direction during two-way sync, as the sync linkage should not form a ring (you can run DDL statements in either the forward or reverse direction).
• All sync links between MySQL, TDSQL-C for MySQL, MariaDB, Percona, and TDSQL for MySQL support two-way sync except when a TDSQL for MySQL instance with the MariaDB kernel is used as the source or target database.

Recommended Configurations for Typical Use Cases

A two-way sync task consists of two one-way sync tasks to establish a two-way topology. The creation steps for each one-way sync task are similar to those for a general one-way sync task. They differ only in the following sync option settings:

The following configurations are recommended for typical use cases for your reference.

Scenario	Time Requirements	Sync Task	Initialization Type	If Target Already Exists	Conflict Resolution Method	SQL Type
Scenario 1: Instance A has database/table structures and data, and instance B is empty	Task 2 can be created only after task 1 enters the "incremental sync" phase	Task 1: Forward sync (A < B)	Structure initialization/full data initialization	Precheck and report error	Select an option as needed. Example: If a primary key conflict occurs, and you want the content of database A to prevail, you need to select **Overwrite** for task 1 and **Ignore** or **Report** for task 2. The conflict resolution method takes effect only for the data with primary key conflict.	Select DDL in at most one task. For operation types other than DDL, keep them consistent between the two tasks.
		Task 2: Reverse sync (B > A)	Do not select	Ignore and execute
Scenario 2: Instance A has database/table structures and data, and instance B has only database/table structures but no data	None	Task 1: Forward sync (A > B)	Full data initialization	Ignore and execute
		Task 2: Reverse sync (B > A)	Do not select	Ignore and execute
Scenario 3: Both instances A and B have database/table structures and data	None	Task 1: Forward sync (A > B)	Full data initialization	Ignore and execute
		Task 2: Reverse sync (B > A)	Full data initialization	Ignore and execute

Directions

This document takes creating two-way sync between self-built MySQL database A in Shanghai region and TencentDB for MySQL database B in Beijing region as an example. Initially, A has database/table structures and data, while B is empty. When a primary key conflict occurs, data updates in A shall prevail. For A > B sync, the primary key conflict resolution policy is Overwrite, and DDL and DML statements are synced. For B > A sync, the policy is Ignore, and only DML statements are synced.

Creating a sync task 1: Reverse sync (A > B)

1. Log in to the data sync purchase page, select appropriate configuration items, and click Buy Now.
2. After successful purchase, return to the data sync list, and you can see the newly created data sync task. You need to configure it before you can use it.
3. In the data sync list, click Configure in the Operation column to enter the sync task configuration page.
4. On the sync task configuration page, configure the source and target instances and their accounts and passwords, test the connectivity, and click Next.

   Category	Parameter	Description
   Task Configuration	Task Name	DTS will automatically generate a task name, which is customizable.
   	Running Mode	Immediate execution and scheduled execution are supported.
   Source Instance Settings	Source Instance Type	The database A type selected during purchase, which cannot be changed.
   	Source Instance Region	The database A region selected during purchase, which cannot be changed.
   	Service Provider	Select **Others**.
   	Access Type	For a third-party cloud database, you can select **Public Network** generally or select **VPN Access**, **Direct Connect**, or **CCN** based on your actual network conditions. In this scenario, **Public Network** is selected as an example. For the preparations for different access types, see Overview.
   Target Instance Settings	Target Instance Type	The target database B type selected during purchase, which cannot be changed.
   	Target Instance Region	The target database B region selected during purchase, which cannot be changed.
   	Access Type	In this scenario, select **Database**.
   	Instance ID	Instance ID of database B.
   	Account	Account of database B, which must have the required permissions.
   	Password	Password of database B.

5. On the Set sync options and objects page, set the data initialization, data sync, and sync object options and click Save and Go Next.
   

   Category	Parameter	Description
   Data Initialization Option	Initialization Type	In this scenario, select **Structure initialization/Full data initialization**. Structure initialization: Table structures in the source instance will be initialized into the target instance before the sync task runs. Full data initialization: Data in the source instance will be initialized into the target database before the sync task runs.
   	If Target Already Exists	In this scenario, select **Precheck and report error**. Precheck and report error: If a table with the same name exists in both the source and target databases, an error will be reported, and the task will stop. Ignore and execute: Full and incremental data will be directly added to tables in the target instance.
   Data Sync Option	Conflict Resolution Method	Select a conflict resolution policy based on the business conditions. In this scenario, select **Overwrite**. Report: If a primary key conflict is found during data sync, an error will be reported, and the data sync task will be paused. Ignore: If a primary key conflict is found during data sync, the primary key record in the target database will be retained. Overwrite: If a primary key conflict is found during data sync, the primary key record in the source database will overwrite that in the target database.
   	SQL Type	Supported operations include INSERT, UPDATE, DELETE, and DDL. If you select **Custom DDL**, you can select different DDL statement sync policies as needed. For more information, see Setting SQL Filter Policy. In two-way sync, you can select **DDL** in at most one task. In this scenario, select **DDL** in task 1 but not task 2.
   Sync Object Option	Database and Table Objects of Source Instance	Select the objects to be synced.
   	Selected Object	Database/Table mapping (renaming) is supported. Hover over a database or table name, click the displayed **Edit** icon, and enter a new name in the pop-up window.

6. In an A > B forward sync task, DTS will check the source and target database parameters. After all check items are passed, click Start Task. In a B > A reverse sync task, DTS will also check the DDL configuration.

   Note：

   • If the verification failed, fix the problem as instructed in Database Connection Check and initiate the verification task again.
   • If an alarm is displayed in the verification result, it will not affect the task start, but we recommend you click View Details to get the suggestions for adjustment.

• DDL check
• Source and target database parameter check
  

7. Return to the data sync task list, and you can see that the task has entered the Running status.

Creating a sync task 2: Reverse sync (B > A)

The operations of forward and reverse sync tasks are basically the same. The following only describes their differences:

1. Confirm the status of task 1. When task 1 enters the "incremental sync" phase, start configuring task 2.
   This task configuration timing is required only when database B is empty. In other scenarios, there is no need to wait.
2. Set source and target databases.
   Swap the data in source and target databases in task 1.
3. Set sync options and objects.

• Initialization Type: Do not select.
• If Target Already Exists: Select Ignore and execute.
• Primary Key Conflict Resolution: Select an option based on your business conditions. In this scenario, select Ignore.
• SQL Type: In two-way sync, you can select DDL in at most one task. In this scenario, select DDL in task 1 but not task 2.
  

4. On the Verify task page, check the DDL configuration.

Stopping a sync task

If you no longer need a sync task, you can select More > Stop in the Operation column to stop it.