This topic describes how to synchronize data between ApsaraDB RDS for MySQL instances that belong to different Alibaba Cloud accounts by using Data Transmission Service (DTS).

Prerequisites

  • The source and destination ApsaraDB RDS for MySQL instances are created. For more information, see Create an ApsaraDB RDS for MySQL instance.
  • The database type of the source and destination ApsaraDB RDS instances is MySQL.

Usage notes

  • DTS uses read and write resources of the source and destination databases during initial full data synchronization. This may increase the loads of the database servers. If the database performance or specifications are unfavorable, or the data volume is large, database services may become unavailable. For example, DTS occupies a large amount of read and write resources in the following cases: a large number of slow SQL queries are performed on the source database, the tables have no primary keys, or a deadlock occurs in the destination database. Before you synchronize data, evaluate the impact of data synchronization on the performance of the source and destination databases. We recommend that you synchronize data during off-peak hours. For example, you can synchronize data when the CPU utilization of the source and destination databases is less than 30%.
  • The tables to be migrated in the source database must have PRIMARY KEY or UNIQUE constraints and all fields must be unique. Otherwise, the destination database may contain duplicate data records.
  • During initial full data synchronization, concurrent INSERT operations cause fragmentation in the tables of the destination instance. After initial full data synchronization, the tablespace of the destination instance is larger than that of the source instance.
  • Two-way synchronization across Alibaba Cloud accounts is not supported.

Supported synchronization topologies

  • One-way one-to-one synchronization
  • One-way one-to-many synchronization
  • One-way cascade synchronization
  • One-way many-to-one synchronization

For more information about the synchronization topologies that are supported by DTS, see Synchronization topologies.

SQL operations that can be synchronized

Operation type SQL statement
DML INSERT, UPDATE, DELETE, and REPLACE
DDL
  • ALTER TABLE and ALTER VIEW
  • CREATE FUNCTION, CREATE INDEX, CREATE PROCEDURE, CREATE TABLE, and CREATE VIEW
  • DROP INDEX and DROP TABLE
  • RENAME TABLE
  • TRUNCATE TABLE

Limits

  • Incompatibility with triggers

    If you select a database as the object to be synchronized and the database contains a trigger that updates a table, data inconsistency may occur. For more information about how to solve this issue, see Configure a data synchronization task for a source database that contains a trigger.

  • Limits on RENAME TABLE operations

    RENAME TABLE operations may cause data inconsistency between the source and destination databases. For example, if only Table A is selected as the object to be synchronized and is renamed Table B, Table B cannot be synchronized to the destination database. To prevent this situation, you can select the entire database where Table A is located as the object to be synchronized when you configure the data synchronization task.

Billing

Synchronization type Task configuration fee
Schema synchronization and full data synchronization Free of charge.
Incremental data synchronization Charged. For more information, see Billing overview.

Preparations

  1. Optional:Obtain the IDs of the Alibaba Cloud accounts to which the source and destination instances belong.
    Note Skip the step if you have obtained the IDs of the Alibaba Cloud accounts to which the source and destination instances belong.
    1. Log on to the Account Management console by using the Alibaba Cloud account to which the source or destination instance belongs.
    2. Optional:In the left-side navigation pane, click Basic Information.
    3. View and record the value of the Account ID parameter.
  2. Create a RAM role.
    1. Log on to the RAM console by using the Alibaba Cloud account to which the source instance belongs.
    2. In the left-side navigation pane, choose Identities > Roles.
    3. On the Roles page, click Create Role.
    4. In the Create Role panel, select Alibaba Cloud Account for the Select Trusted Entity parameter and click Next.
    5. In the Configure Role step, set parameters for the RAM role.
      Parameter Description
      RAM Role Name The name of the RAM role. In this example, ram-for-dts is used.
      Note The role name must be 1 to 64 characters in length and can contain letters, digits, and hyphens (-).
      Note Optional. The description for the RAM role.
      Select Trusted Alibaba Cloud Account Select Other Alibaba Cloud Account and enter the ID of the Alibaba Cloud account to which the destination instance belongs. For information about how to obtain the ID of an Alibaba Cloud account, see Preparations.
    6. Click OK.
  3. Click Input and Attach to grant permissions to the created RAM role.
    1. In the Add Permissions panel, select System Policy as Type.
    2. In the Policy Name field, enter AliyunDTSRolePolicy.
    3. Click OK.
    4. After you grant the permissions, click Close.
  4. Modify the trust policy.
    1. On the Roles page, find the RAM role that you created and click the role name to view details.
    2. On the Basic Information page of the RAM role, click the Trust Policy Management tab.
    3. On the Trust Policy Management tab, click Edit Trust Policy.
    4. Copy the following code to the code editor: 
      {
    "Statement": [
        {
            "Action": "sts:AssumeRole",
            "Effect": "Allow",
            "Principal": {
                "RAM": [
                    "acs:ram::<ID of the Alibaba Cloud account to which the destination instance belongs>:root"
                ],
                "Service": [
                    "<ID of the Alibaba Cloud account to which the destination instance belongs>@dts.aliyuncs.com"
                ]
            }
        }
    ],
    "Version": "1"
}
      Note In the preceding code, replace ID of the Alibaba Cloud account to which the destination instance belongs with the Alibaba Cloud account ID that you specified when you configure the RAM role information.
    5. Click OK.

Procedure

  1. Go to the Data Synchronization Tasks page in the Data Management (DMS) console.
    1. Log on to the DMS console by using the Alibaba Cloud account to which the destination instance belongs.
    2. In the top navigation bar, click DTS.
    3. In the left-side navigation pane, choose DTS (DTS) > Data Synchronization.
    Note You can also log on to the new DTS console and go to the Data Synchronization Tasks page.
  2. From the drop-down list next to Data Synchronization Tasks, select the region in which the data synchronization instance resides.
    Note If you use the new DTS console, you must select the region in which the data synchronization instance resides in the top navigation bar.
  3. Click Create Task. On the page that appears, configure the source and destination databases.
    Warning After you select the source and destination instances, we recommend that you read the limits displayed at the top of the page. This helps you create and run the data synchronization task.
    Section Parameter Description
    N/A Task Name

    The name of the task. DTS automatically generates a task name. We recommend that you specify an informative name to identify the task. You do not need to specify a unique task name.

    Source Database Database Type The type of the source instance. Select MySQL.
    Access Method The access method of the source instance. Select Alibaba Cloud Instance.
    Instance Region The region where the source ApsaraDB RDS for MySQL instance resides.
    Note You can select different regions for the source and destination ApsaraDB RDS for MySQL instances.
    Replicate Data Across Alibaba Cloud Accounts Specifies whether data is replicated across Alibaba Cloud accounts. Select Yes.
    Alibaba Cloud Account The ID of the Alibaba Cloud account to which the source ApsaraDB RDS for MySQL instance belongs. For more information about how to obtain the ID of an Alibaba Cloud account, see Preparations.
    RAM Role Name The name of the role that you configured by using the Alibaba Cloud account to which the source instance belongs. In this example, the ram-for-dts role that you created in Preparations is used.
    RDS Instance ID The ID of the source ApsaraDB RDS for MySQL instance.
    Note If an alert message appears when you select the ID of an ApsaraDB RDS for MySQL instance, modify the parameter values as prompted. For more information, see Troubleshooting.
    Database Account The database account of the source ApsaraDB RDS for MySQL instance.
    Database Password

    The password of the database account.

    Encryption Specifies whether to encrypt the connection to the source instance. Select Non-encrypted or SSL-encrypted based on your business requirements. In this example, Non-encrypted is selected.
    Note If you want to select SSL-encrypted, you must enable SSL encryption for the ApsaraDB RDS for MySQL instance before you configure the data synchronization task. For more information, see Configure SSL encryption for an ApsaraDB RDS for MySQL instance.
    Destination Database Database Type The type of the destination instance. Select MySQL.
    Access Method The access method of the destination instance. Select Alibaba Cloud Instance.
    Instance Region The region where the destination ApsaraDB RDS for MySQL instance resides.
    Note You can select different regions for the source and destination ApsaraDB RDS for MySQL instances.
    RDS Instance ID The ID of the destination ApsaraDB RDS for MySQL instance.
    Database Account The database account of the destination ApsaraDB RDS for MySQL instance.
    Database Password

    The password of the database account.

    Encryption Specifies whether encrypt the connection to the destination instance. Select Non-encrypted or SSL-encrypted based on your business requirements. In this example, Non-encrypted is selected.
    Note If you want to select SSL-encrypted, you must enable SSL encryption for the ApsaraDB RDS instance before you configure the data migration task. For more information, see Configure SSL encryption for an ApsaraDB RDS for MySQL instance.
  4. In the lower part of the page, click Test Connectivity and Proceed.
    Note
    • You do not need to modify the security settings for ApsaraDB instances, such as ApsaraDB RDS for MySQL and ApsaraDB for MongoDB, or self-managed databases that are hosted on Elastic Compute Service (ECS) instances. DTS automatically adds the CIDR blocks of DTS servers to the whitelists of ApsaraDB instances or the security group rules of ECS instances. For more information, see Add the CIDR blocks of DTS servers to the security settings of on-premises databases.
    • After data synchronization is complete, we recommend that you remove the CIDR blocks of DTS servers from the whitelists or security group rules.
  5. Select objects for the task and configure advanced settings.
    Parameter Description
    Task Stages

    By default, Incremental Data Synchronization is selected. You must also select Schema Synchronization and Full Data Synchronization. After the precheck is complete, DTS synchronizes the historical data of selected objects from the source instance to the destination cluster. The historical data is the basis for subsequent incremental synchronization.

    Processing Mode of Conflicting Tables

    • Precheck and Report Errors: checks whether the destination database contains tables that have the same names as tables in the source database. If the source and destination databases do not contain tables that have identical table names, the precheck is passed. Otherwise, an error is returned during the precheck and the data synchronization task cannot be started.

      Note You can use the object name mapping feature to rename the tables that are synchronized to the destination database. You can use this feature if the source and destination databases contain identical table names and the tables in the destination database cannot be deleted or renamed. For more information, see Map object names .
    • Ignore Errors and Proceed: skips the precheck for identical table names in the source and destination databases.
      Warning If you select Ignore Errors and Proceed, data consistency is not ensured and your business may be exposed to potential risks.
      • If the source and destination databases have the same schema, and a data record has the same primary key as an existing data record in the destination database:
        • During full data synchronization, DTS does not synchronize the data record to the destination database. The existing data record in the destination database is retained.
        • During incremental data synchronization, DTS synchronizes the data record to the destination database. The existing data record in the destination database is overwritten.
      • If the source and destination databases have different schemas, data may fail to be initialized. In this case, only some columns are synchronized or the data synchronization task fails.
    Source Objects

    Select one or more objects from the Source Objects section and click the Rightwards arrow icon to add the objects to the Selected Objects section.

    Note You can select columns, tables, or databases as the objects to be synchronized. If you select tables or columns as the objects to be synchronized, DTS does not synchronize other objects such as views, triggers, or stored procedures to the destination database.
    Selected Objects
    • To rename an object that you want to synchronize to the destination instance, right-click the object in the Selected Objects section. For more information, see Map the name of a single object.
    • To rename multiple objects at a time, click Batch Edit in the upper-right corner of the Selected Objects section. For more information, see Map multiple object names at a time.
    Note
    • To select the SQL operations performed on a specific database or table, right-click an object in the Selected Objects section. In the dialog box that appears, select the SQL operations that you want to synchronize. For more information about the SQL operations that can be synchronized, see SQL operations that can be synchronized.
    • To specify WHERE conditions to filter data, right-click an object in the Selected Objects section. In the dialog box that appears, specify the conditions. For more information about how to specify the conditions, see Use SQL conditions to filter data.
    Filter data

    You can specify WHERE conditions to filter data. For more information, see Use SQL conditions to filter data.

    Select the SQL operations to be synchronized In the Selected Objects section, right-click an object. In the dialog box that appears, select the DML and DDL operations that you want to synchronize. For more information, see SQL operations that can be synchronized.
  6. Click Next: Advanced Settings to configure advanced settings.
    Parameter Description
    Set Alerts
    Specifies whether to set alerts for the data synchronization task. If the task fails or the synchronization latency exceeds the threshold, the alert contacts will receive notifications. Valid values:
    • No: does not set alerts.
    • Yes: sets alerts. If you select Yes, you must also set the alert threshold and alert contacts.
    Capitalization of Object Names in Destination Instance

    The capitalization of database names, table names, and column names in the destination instance. By default, DTS default policy is selected. You can select other options to ensure that the capitalization of object names is consistent with that of the source or destination database. For more information, see Specify the capitalization of object names in the destination instance.

    Replicate Temporary Tables When DMS Performs DDL Operations
    If you use DMS to perform online DDL operations on the source database, you can specify whether to synchronize temporary tables generated by online DDL operations. Valid values:
    • Yes: DTS synchronizes the data of temporary tables generated by online DDL operations.
      Note If online DDL operations generate a large amount of data, the data synchronization task may take an extended period of time to complete.
    • No: DTS does not synchronize the data of temporary tables generated by online DDL operations. Only the original DDL data of the source database is synchronized.
      Note If you select No, the tables in the destination database may be locked.
    Retry Time for Failed Connections
    The retry time range for failed connections. If the source or destination database fails to be connected after the data synchronization task is started, DTS immediately retries a connection within the time range. Valid values: 10 to 1440. Unit: minutes. Default value: 120. We recommend that you set the parameter to a value greater than 30. If DTS reconnects to the source and destination databases within the specified time range, DTS resumes the data synchronization task. Otherwise, the data synchronization task fails.
    Note
    • If you set different retry time ranges for multiple DTS tasks that have the same source or destination database, the shortest retry time range that is set takes precedence.
    • If DTS retries a connection, you are charged for the DTS instance. We recommend that you specify the retry time range based on your business requirements, or release the DTS instance at the earliest opportunity after the source and destination instances are released.
    Configure ETL
    Specify whether to configure the extract, transform, and load (ETL) feature. For more information, see What is ETL?.
  7. In the lower part of the page, click Next: Save Task Settings and Precheck.
    Note
    • Before you can start the data synchronization task, DTS performs a precheck. You can start the data synchronization task only after the task passes the precheck.
    • If the task fails to pass the precheck, you can click the Info icon icon next to each failed item to view details.
      • You can troubleshoot the issues based on the causes and run a precheck again.
      • If you do not need to troubleshoot the issues, you can ignore failed items and run a precheck again.
  8. Wait until the Success Rate becomes 100%. Then, click Next: Purchase Instance.
  9. On the Purchase Instance page, specify the Billing Method and Instance Class parameters for the data synchronization instance. The following table describes the parameters.
    Section Parameter Description
    Parameters Billing Method
    • Subscription: You pay for the instance when you create an instance. The subscription billing method is more cost-effective than the pay-as-you-go billing method for long-term use.
    • Pay-as-you-go: A pay-as-you-go instance is billed on an hourly basis. The pay-as-you-go billing method is suitable for short-term use. If you no longer require a pay-as-you-go instance, you can release the pay-as-you-go instance to reduce costs.
    Instance Class DTS provides several instance classes that have different performance in synchronization speed. You can select an instance class based on your business scenario. For more information, see Specifications of data synchronization instances.
    Subscription Length If you select the subscription billing method, set the subscription duration and the number of instances that you want to create. The subscription duration can be one to nine months or one to three years.
    Note This parameter is available only if you select the subscription billing method.
  10. Read and select the check box for Data Transmission Service (Pay-as-you-go) Service Terms.
  11. Click Buy and Start to start the data synchronization task. You can view the progress of the task in the task list.

Troubleshooting

The following table describes the common alert messages and the corresponding solutions when you configure the source instance.

Alert message Solution
UID Check whether you specify a valid value for the Alibaba Cloud Account or RAM Role Name parameter when you configure the source instance.
Note To obtain the ID of the Alibaba Cloud account to which the source instance belongs, you can log on to the Account Management console by using the account. The account ID is displayed on the Basic Information page.
ROLE