You can use Data Transmission Service (DTS) to synchronize data between data sources. This feature applies to various scenarios such as active geo-redundancy, geo-disaster recovery, zone-disaster recovery, cross-border data synchronization, query load balancing, cloud business intelligence (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

DTS allows you to synchronize data from databases such as MySQL, Distributed Relational Database Service (DRDS), or 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

Considerations

  • When you configure the synchronization channel, you can use the column name mapping feature of DTS to map columns if the column information of the source table is different from that of the destination table. 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 tables, you can create a table in the AnalyticDB for MySQL cluster in advance. When you configure the synchronization channel, do not select Initial Schema Synchronization in Step 6.
  • 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 or 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. At the top of the page, select the region of the synchronization task.
  4. Click Create Data Synchronization Task. On the buy page that appears, select a billing method and configure the parameters as prompted.
    Note In this example, select Pay-As-You-Go.
    International site 1International site 2
    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.
    Target 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 the maximum number of transactions per second (TPS):
    • micro: less than 200.
    • small: 200 (inclusive) to 2,000 (exclusive).
    • medium: 2,000 (inclusive) to 5,000 (exclusive).
    • large: unlimited. The actual performance depends on the network environment and the performance of the source and destination instances.
    Quantity purchased Set the value to 1.
  5. After you configure the preceding parameters, click Buy Now.
  6. On the Confirm Order page, select the Data Transmission Service-Pay-as-you-go Agreement of Service check box, click Activate, and then pay for the order as prompted.

Step 2: Configure the synchronization channel

  1. Log on to the DTS console.
  2. In the left-side navigation pane, click Data Synchronization.
  3. At the top of the page, select the region of the synchronization task.
  4. On the Synchronization Tasks page, find the 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
    N/A Synchronization Task Name The name of the synchronization task. We recommend that you specify a name that can describe your business to facilitate subsequent management.
    Source Instance Details Instance Type The type of the source instance. In this example, select RDS Instance from the drop-down list.
    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. In this example, 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. In this example, 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 Be Synchronized 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 change 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 distribution key of the partitioned 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 the fault 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. At the top of the page, select the region of the synchronization task.
  4. On the Synchronization Tasks page, find the 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 that is synchronized from the ApsaraDB RDS for MySQL instance. For more information about how to connect to an AnalyticDB for MySQL cluster, see Connect to a cluster.