Data Transmission Service (DTS) provides the data synchronization feature to help you synchronize data between two data sources in real time. This feature can be applied to a variety of scenarios such as active geo-redundancy, geo-disaster recovery, zone-disaster recovery, cross-border data synchronization, separating data query from data reporting, cloud BI systems, and real-time data warehousing. This topic describes how to use DTS to synchronize data from ApsaraDB RDS for MySQL to AnalyticDB for MySQL in real time.

Background information

You can use this feature to synchronize data from databases such as MySQL, Distributed Relational Database Service (DRDS), or Apsara PolarDB to AnalyticDB for MySQL databases. MySQL databases can be ApsaraDB RDS for MySQL databases, MySQL databases provided by other cloud service providers, user-created MySQL databases built in on-premises data centers, or user-created MySQL databases deployed on Elastic Compute Service (ECS) instances.

The object of data synchronization can be a column, a table, or a database. You can choose to synchronize the data of a few tables or a few columns.

The data synchronization feature supports the mapping of database names, table names, and column names. You can synchronize data between different databases or tables whose names are different.

Prerequisites

Precautions

  • When you configure a synchronization channel, if the column information of the source table is different from that of the destination table, you can map columns through the column name mapping feature of DTS. For more information, see Object name mapping.
  • If the number of ApsaraDB RDS for MySQL tables to be synchronized is small and the AnalyticDB for MySQL table schema is significantly different from that of the ApsaraDB RDS for MySQL table, you can create a table in AnalyticDB for MySQL in advance. Do not select Initial Schema Synchronization in Step 6 when you configure the synchronization channel.
  • If a date field in the ApsaraDB RDS for MySQL table contains a date value that is incompatible with AnalyticDB for MySQL such as 1900-01-00 and 2015-02-00, you can submit a ticket to apply for non-strict time validation.
    Note After non-strict time validation is enabled, the invalid date value written into the AnalyticDB for MySQL table changes to NULL.

Step 1: Create a DTS synchronization task

You must pay for DTS synchronization tasks. DTS supports two billing methods: subscription and pay-as-you-go. For more information, see DTS pricing.

The pay-as-you-go billing method is used in this example.

  1. Log on to the DTS console.
  2. In the left-side navigation pane, click Data Synchronization.
  3. Select the region of the synchronization task at the top of the page.
  4. Click Create Data Synchronization Task. On the buy page that appears, select a billing method and configure the following parameters.
    Note In this example, select Pay-As-You-Go.
    Synchronize dataConfigure parameters
    Parameter Description
    Function Select Data Synchronization.
    Source Instance Select MySQL from the drop-down list.
    Source Region The region where the source ApsaraDB RDS for MySQL instance resides.
    Target Instance Select AnalyticDB for MySQL from the drop-down list.
    Destination Region The region where the destination AnalyticDB for MySQL cluster resides.
    Synchronization Topology Select One-Way Synchronization.
    Specification Select large from the drop-down list.
    DTS supports four specifications for data synchronization channels based on their maximum performance of transactions per second (TPS):
    • micro: less than 200.
    • small: 200 to 2,000.
    • medium: 2,000 to 5,000.
    • large: unlimited. Actual performance will depend on the network environment and the performance of the source and destination instances.
    Number Select 1.
  5. After you configure the preceding parameters, click Buy Now.
  6. On the Confirm Order page, read and select Data Transmission Service-Pay-as-you-go Agreement of Service, click Activate, and pay for the order.

Step 2: Configure the synchronization channel

  1. Log on to the DTS console.
  2. In the left-side navigation pane, click Data Synchronization.
  3. Select the region of the synchronization task at the top of the page.
  4. On the Synchronization Tasks page, find the target synchronization task and click Configure Synchronization Channel in the Actions column. In the Select Source and Destination Instances for Synchronization Channel step, configure the following parameters.
    Section Parameter Description
    - Synchronization Task Name The name of the synchronization task. We recommend that you specify an identifiable name to facilitate subsequent management.
    Source Instance Details Instance Type The type of the source instance. Select RDS Instance.
    Instance Region The region where the source ApsaraDB RDS for MySQL instance resides.
    Instance ID The ID of the source ApsaraDB RDS for MySQL instance.
    Database Account The database account used to connect to the source ApsaraDB RDS for MySQL instance.
    Database Password The password of the database account used to connect to the source ApsaraDB RDS for MySQL instance.
    Encryption The encryption method for connecting to the source instance. Select Non-encrypted.
    Destination Instance Details Instance Type The type of the destination instance. Select AnalyticDB for MySQL from the drop-down list.
    Instance Region The region where the destination AnalyticDB for MySQL cluster resides.
    Version The version of AnalyticDB for MySQL. Select 3.0.
    Database The destination AnalyticDB for MySQL database.
    Database Account The database account used to connect to the destination AnalyticDB for MySQL database.
    Note The account must have full permissions on the destination AnalyticDB for MySQL database.

    For more information about how to grant permissions to an account, see GRANT.

    Database Password The password of the database account used to connect to the destination AnalyticDB for MySQL database.
    Configure the synchronization channel 1Configure the synchronization channel 2
  5. After you configure the preceding parameters, click Set Whitelist and Next.
  6. In the Select Objects to Synchronize step, configure the parameters and click Next.
    Select the objects to be synchronized
    1. Initial Synchronization: Select Initial Schema Synchronization and Initial Full Data Synchronization.
    2. Processing Mode In Existed Target Table: Select Pre-check and Intercept.
    3. Merge Multi Tables: Select No.
    4. Synchronization Type: Select all operations.
    5. In the Available section, click the table to be synchronized to add it to the Selected section.
    6. Specify whether to modify multiple database names and table names at a time based on your business requirements.
  7. On the table information configuration page, configure the following parameters.
    Table information
    Parameter Description
    Type Select Partitioned Table or Dimension Table.
    Primary Key Column The primary key of the table. AnalyticDB for MySQL allows you to select multiple columns as the primary key. You can use the primary key to remove duplicate data.
    Distribution Column The partition key of the partition table.
  8. After you configure the preceding parameters, click Precheck. The Precheck dialog box appears.
    1. If Error is displayed for a check item, troubleshoot faults based on the error message and Source database connectivity.
    2. If Success is displayed for all check items, click Close to synchronize data.

Step 3: View synchronized data

  1. Log on to the DTS console.
  2. In the left-side navigation pane, click Data Synchronization.
  3. Select the region of the synchronization task at the top of the page.
  4. On the Synchronization Tasks page, find the target synchronization task and view Delay and Speed in the Synchronization Details column.
  5. Log on to the AnalyticDB for MySQL console.
  6. Connect to the cluster and view the data synchronized from ApsaraDB RDS for MySQL. For more information, see Connect to a cluster.