tencent cloud

Feedback

Creating Two-Way Sync Data Structure

Last updated: 2022-05-17 10:30:13

    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 databases at the same time during sync.

    As a two-way sync task consists of two one-way sync tasks to establish a two-way 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. 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. If there are duplicate primary keys in the two source databases for business reasons, select an appropriate conflict resolution method as instructed in Recommended Configurations for Typical Use Cases to make the sync behavior and data meet the expectations.

    Application Restrictions

    • 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).
    • Currently, a two-way 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.

    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. Example: a two-way sync task consisting of two one-way sync tasks (1 and 2) needs to be created between databases A and B: A > B (task 1) and B > A (task 2).
    Scenario Time Requirements Sync TaskInitialization TypeIf Target Already ExistsConflict Resolution MethodSQL Type
    Scenario 1: database A has database/table structures and data, and database B is emptyTask 2 can be created only after task 1 enters the "incremental sync" phaseTask 1: forward syncStructure initialization/full data initializationPrecheck and report errorSelect 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 syncDo not selectIgnore and execute
    Scenario 2: database A has database/table structures and data, and database B has only database/table structures but no dataNoneTask 1: forward syncFull data initializationIgnore and execute
    Task 2: reverse syncDo not selectIgnore and execute
    Scenario 3: both databases A and B have database/table structures and dataNoneTask 1: forward syncFull data initializationIgnore and execute
    Task 2: reverse syncFull data initializationIgnore and execute

    Directions

    This document uses MySQL two-way sync (where database A has database/table structures and data, and database B has only database/table structures but no data) as an example. The two-way sync operations for other databases are similar.

    Creating sync task 1: forward sync (database A > database B)

    1. Log in to the data sync purchase page, select appropriate configuration items, and click Buy Now.
      ParameterDescription
      Billing ModeMonthly 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 TypeSelect MySQL (including TencentDB for MySQL and self-built MySQL).
      Source Database RegionSelect the source database A region.
      Target Database TypeSelect MySQL (including TencentDB for MySQL and self-built MySQL).
      Target Database RegionSelect the target database B region.
      Sync Task SpecificationCurrently, only the Standard Edition is supported
    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.
      CategoryParameterDescription
      Task Configuration Task Name DTS will automatically generate a task name, which is customizable.
      Running ModeImmediate 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 TypeSelect the target database type, which cannot be changed once configured.
      Target Database RegionSelect the target database B region, which cannot be changed once configured.
      Access TypeSelect the access type of the target 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.
      CategoryParameterDescription
      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 **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 TypeSupported operations: INSERT, UPDATE, DELETE, and DDL.
      In two-way sync, you can select DDL in at most one task. In this document, select DDL in task 1 but not task 2.
      Sync Object Option Database and Table Objects of Source DatabaseSelect the objects to be synced. You can select databases, tables, and views.
      Selected ObjectIt 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
    1. Return to the data sync task list, and you can see that the task has entered the Running status.

    Creating sync task 2: reverse sync (database B > database A)

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

    1. Set the sync source and target databases.
      Swap the settings of the source and target databases in task 1.
    2. Set the sync options and objects.
    • Initialization Type: do not select.
    • If Target Already Exists: select Ignore and execute.
    • Conflict Resolution Method: select the same option as selected in task 1.
    • SQL Type: in two-way sync, you can select DDL in at most one task. In this document, select DDL in task 1 but not task 2.

    Stopping sync task

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

    Contact Us

    Contact our sales team or business advisors to help your business.

    Technical Support

    Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

    7x24 Phone Support