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.
- 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.
- DDL statements in the configurations of multiple sync tasks should not form a ring.
- Currently, a many-to-one sync task can be created between two MySQL databases, two TDSQL-C for MySQL databases, or one MySQL database and one TDSQL-C for MySQL database.
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.
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.
|Scenario ||Time Requirements ||Sync Task||Initialization Type||If Target Already Exists||Conflict Resolution Method||SQL Type|
|Scenario 1: databases A and B have database/table structures and data, and database C is empty||Task 2 can be started only after task 1 enters the "incremental sync" phase||Task 1||Structure initialization/full data initialization||Ignore and execute||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 other multiple tasks.|
|Task 2||Full data initialization||Ignore and execute|
|Scenario 2: databases A and B have database/table structures and data, and database C has only database/table structures but no data||None||Task 1||Full data initialization||Ignore and execute|
|Task 2||Same as task 1||Same as task 1|
|Scenario 3: databases A, B, and C all have database/table structures and data||None||Task 1||Full data initialization||Ignore and execute|
|Task 2||Same as task 1||Same as task 1|
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)
- Log in to the data sync purchase page, select appropriate configuration items, and click Buy Now.
|Billing Mode||Monthly subscription and pay-as-you-go billing modes are supported. Currently, the data sync feature is free of charge, and you will receive notifications by email and Message Center one month before the billing officially starts.|
|Source Database Type||Select MySQL (including TencentDB for MySQL and self-built MySQL).|
|Source Database Region||Select the source database A region.|
|Target Database Type||Select MySQL (including TencentDB for MySQL and self-built MySQL).|
|Target Database Region||Select the target database C region.|
|Sync Task Specification||Currently, only the Standard Edition is supported|
- 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.
- In the data sync list, click Configure in the Operation column to enter the sync task configuration page.
- On the sync task configuration page, configure the source and target databases and their accounts and passwords, test the connectivity, and click Next.
||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.|
||Others (including TencentDB for MySQL and self-built MySQL), AWS, and Alibaba Cloud are supported.|
||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.|
- On the Set sync options and objects page, set the data initialization, data sync, and sync object options and click Save and Go Next.
|Data Initialization Option
In this document, select **Structure initialization/Full data initialization**.
- 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.
|If Target Already Exists
In this document, select **Ignore and execute**.
- 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.
|Data Sync Option
||Conflict Resolution Method
Select an option as needed.
- 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: 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.|
- 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.
- 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
- 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:
- Set the sync source and target databases.
Enter the information of databases A and B in the source and target database settings respectively.
- 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.