tencent cloud

Overview

Many-to-One sync is to sync the content in multiple source databases to one target database. If you use a single database, you may often need to shard the data due to high load or region issues, but storing the databases/tables of the same type in many databases makes data query inconvenient. The many-to-one sync feature can easily solve this problem.

As a many-to-one sync task consists of multiple one-way sync tasks to establish a many-to-one topology, restrictions on one-way sync and relevant operations must be followed. For more information, see the appropriate sync scenario in Data Sync.

Notes

• During full data sync, DTS consumes certain source database 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. Each source database is 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. If there are duplicate primary keys in multiple source databases for business reasons, select an appropriate conflict resolution method as instructed in Recommended Configuration for Typical Use Case to make the sync behavior and data meet the expectations.

Application Restrictions

• DDL statements in the configurations of multiple sync tasks should not form a ring.

DDL Configuration Principles

• DDL statements in the configurations of multiple sync tasks should not form a ring; otherwise, they will loop in the system, causing errors.
• The same table object in the target database cannot receive DDL sync from multiple source databases; otherwise, such DDL statements may conflict with each other in the target database, causing errors.
  • In many-to-one sync that combines multiple tables with the same name into one, you can select DDL in only one sync task.
  • In other types of many-to-one sync tasks (such as a task that combines multiple tables with different names into one database), you can select DDL in each task. In this case, select an appropriate DDL sync policy based on the actual conditions.
• During verification, the sync system will judge whether the sync task being created will cause a DDL loop or conflict based on all your other sync tasks and provide prompts for your reference.

Recommended Configurations for Typical Use Cases

A many-to-one sync task consists of multiple one-way sync tasks to establish a many-to-one 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.

Example: a sync task among databases A, B, and C needs to be created, where databases A and B have tables with the same name that need to be synced to database C, task 1 is sync from A to C, and task 2 is sync from B to C. To sync data from more source databases to the target database, simply add sync tasks by referring to task 2.

Directions

The following uses MySQL two-to-one sync (databases A and B have database/table structures and data, and database C is empty) as an example. The many-to-one sync operations for other databases are similar.

Creating sync task 1 (database A > database C)

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 databases 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 Database Settings	Source Database Type	Select the TencentDB instance type selected during purchase, which cannot be changed once configured.
   	Source Database Region	Select the TencentDB instance A region selected during purchase, which cannot be changed once configured.
   	Service Provider	Others (including TencentDB for MySQL and self-built MySQL), AWS, and Alibaba Cloud are supported.
   	Access Type	If **Other Cloud Vendors** is selected as **Service Provider**, the access type can be public network; if **Others** is selected as **Service Provider**, you need to select an access type according to the database deployment conditions. Public Network: self-built database connected through a public IP. Self-Build on CVM: self-built database on CVM. Direct Connect/VPN Access: self-built database connected through a Direct Connect/VPN gateway. VPC: self-built database connected through a VPC. Database: TencentDB database. CCN: self-built database connected through CCN.
   Target Database Settings	Target Database Type	Select the target database type, which cannot be changed once configured.
   	Target Database Region	Select the target database C region, which cannot be changed once configured.
   	Access Type	Select the access type of the target database C.

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	Structure initialization: table structures in the source database will be initialized into the target database before the sync task runs. Full data initialization: data in the source database will be initialized into the target database before the sync task runs. In this document, select **Structure initialization/Full data initialization**.
   	If Target Already Exists	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 database. In this document, select **Ignore and execute**.
   Data Sync Option	Conflict Resolution Method	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. Select an option as needed.
   	SQL Type	Supported operations: INSERT, UPDATE, DELETE, and DDL. In many-to-one sync, you can select DDL in at most one task. In this document, select DDL in task 1 but not other tasks.
   Sync Object Option	Database and Table Objects of Source Database	Select the objects to be synced. You can select databases, tables, and views.
   	Selected Object	It displays the selected sync objects, and database/table mapping is supported.

6. On the task verification page, the system will check the DDL configuration first and then check the source and target database parameters. After all check items are passed, click Start Task.

   Note：

   • If the verification failed, fix the problem as instructed in Fix for Verification Failure 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 sync task 2 (database B > database C)

Configure sync task 2 after the previous sync task enters the incremental sync phase.
The operations of tasks 1 and 2 are basically the same. The following only describes their differences:

1. Set the sync source and target databases.
   Enter the information of databases A and B in the source and target database settings respectively.
2. Set the sync options and objects.

• Initialization Type: select Full data initialization only but not Structure Initialization.
• If Target Already Exists: select Ignore and execute.
• Conflict Resolution Method: select an option as needed.
• SQL Type: do not select DDL. In many-to-one sync, you can select DDL in at most one task. In this document, select DDL in task 1 but not other tasks.

Stopping sync task

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