DocsICP FilingConsole
Documentation
Home Page Data Transmission Service User Guide Data Synchronization (New Console) Synchronize data from a MySQL database Synchronizing data from MySQL

Synchronizing data from MySQL

Updated at:
Copy as MD
Product
My Favorites

Synchronize data from a MySQL database using Alibaba Cloud Data Transmission Service (DTS) by following these seven steps.

Step 1: Prerequisites

Complete these prerequisites before you configure a synchronization task.

  1. (Optional) Connect to Alibaba Cloud: Ensure that your business environment can connect to Alibaba Cloud. You can skip this step if your source environment or Alibaba Cloud instance has public access.

  2. Access authorization:

  3. Network and security settings: Ensure that the DTS server can access the source and destination databases. For the complete list of IP addresses for the destination region, see Add the IP addresses of DTS servers to an IP address whitelist.

  4. Source and destination databases: For configuration requirements and limitations for the source and destination databases, see Data synchronization from a MySQL source: Limitations.

Step 2: Create a sync task

  1. Log in to the Data Transmission Service (DTS) console.

  2. In the left-side navigation pane, click Data Synchronization. In the upper-left corner, select the region of the synchronization instance.

  3. Click Create Task to open the task configuration page.

    1. Task Name: DTS automatically generates a task name. Specify a descriptive name for easy identification. The name does not have to be unique.

    2. Configure the source and target databases:

      Source Database

      Configuration parameters depend on the Access Method. Select the method that suits your scenario.

      Alibaba Cloud Instance

      Parameter

      Description

      Select Existing Connection

      • If you need to use a database instance that has been added to the system (created or saved), select the desired database instance from the drop-down list. The database information below is then automatically configured.

        Note

        In the DMS console, this parameter is labeled Select a DMS database instance..

      • If the instance is not registered, or if you prefer not to use a registered one, manually configure the following parameters.

      Database Type

      Select MySQL.

      Access Method

      Select Alibaba Cloud Instance.

      Instance Region

      Select the region where the source RDS MySQL instance is located.

      Replicate Data Across Alibaba Cloud Accounts

      -

      Specify whether the source database is in a different Alibaba Cloud account. If you select Yes, you must configure the Alibaba Cloud Account and RAM Role Name parameters.

      Alibaba Cloud Account

      Enter the numeric ID of the other Alibaba Cloud Account ID.

      RAM Role Name

      Enter the name of the RAM role.

      RDS Instance ID

      Select the source RDS instance ID.

      Database Account

      Enter the account for the source RDS MySQL instance.

      Database Password

      Enter the password for the database account.

      Encryption

      Select Non-encrypted or SSL-encrypted.

      Express Connect, VPN Gateway, or Smart Access Gateway

      Parameter

      Description

      Select Existing Connection

      • If you need to use a database instance that has been added to the system (created or saved), select the desired database instance from the drop-down list, and the database information below will be automatically configured.

        Note

        In the DMS console, this parameter is labeled Select a DMS database instance..

      • If the instance is not registered, or if you prefer not to use a registered one, manually configure the following parameters.

      Database Type

      Select MySQL.

      Access Method

      Select Express Connect, VPN Gateway, or Smart Access Gateway.

      Instance Region

      Select the region of the VPC connected to the source database.

      Replicate Data Across Alibaba Cloud Accounts

      -

      Specify whether the source database is in a different Alibaba Cloud account. If you select Yes, you must configure the Alibaba Cloud Account and RAM Role Name parameters.

      Alibaba Cloud Account

      Enter the numeric ID of the other Alibaba Cloud Account ID.

      RAM Role Name

      Enter the name of the RAM role.

      Connected VPC

      Select the VPC connected to the source database.

      Domain Name or IP

      Enter the source database endpoint.

      Port Number

      Enter the source database port.

      Database Account

      Enter the account for the source database.

      Database Password

      Enter the password for the database account.

      Encryption

      Select Non-encrypted or SSL-encrypted. If the source database uses an SSL-encrypted, you must also upload the CA Certificate and enter the CA Certificate Password.

      Primary/Secondary vSwitch in VPC Tunnel

      Select the primary vSwitch (Primary) and secondary vSwitch (Secondary) for the VPC data channel.

      Public IP Address

      Parameter

      Description

      Select Existing Connection

      • If you want to use a database instance that has been added to the system (created or saved), select the desired database instance from the drop-down list. The database information below will be automatically configured.

        Note

        In the DMS console, this parameter is labeled Select a DMS database instance..

      • If the instance is not registered, or if you prefer not to use a registered one, manually configure the following parameters.

      Database Type

      Select MySQL.

      Access Method

      Select Public IP Address.

      Instance Region

      Select the region closest to your source database.

      Domain Name or IP

      Enter the source database endpoint.

      Port Number

      Enter the source database port.

      Database Account

      Enter the account for the source database.

      Database Password

      Enter the password for the database account.

      Encryption

      Select Non-encrypted or SSL-encrypted. If the source database uses an SSL-encrypted, you must also upload the CA Certificate and enter the CA Certificate Password.

      Self-managed Database on ECS

      Parameter

      Description

      Select Existing Connection

      • If you need to use a database instance that has been added to the system (created or saved), select the desired database instance from the drop-down list. The database information below will be automatically configured.

        Note

        In the DMS console, this parameter is labeled Select a DMS database instance..

      • If the instance is not registered, or if you prefer not to use a registered one, manually configure the following parameters.

      Database Type

      Select MySQL.

      Access Method

      Select Self-managed Database on ECS.

      Instance Region

      Select the region where the ECS instance is located.

      Replicate Data Across Alibaba Cloud Accounts

      -

      Specify whether the source database is in a different Alibaba Cloud account. If you select Yes, you must configure the Alibaba Cloud Account and RAM Role Name parameters.

      Alibaba Cloud Account

      Enter the numeric ID of the other Alibaba Cloud Account ID.

      RAM Role Name

      Enter the name of the RAM role.

      ECS Instance ID

      Select the ECS instance ID.

      Port Number

      Enter the database port on the ECS instance.

      Database Account

      Enter the account for the source database.

      Database Password

      Enter the password for the database account.

      Encryption

      Select Non-encrypted or SSL-encrypted. If the source database uses an SSL-encrypted, you must also upload the CA Certificate and enter the CA Certificate Password.

      Cloud Enterprise Network (CEN)

      Parameter

      Description

      Select Existing Connection

      • If you need to use a database instance that has been added to the system (newly created or saved), select the required database instance from the drop-down list. The database information below will be automatically configured.

        Note

        In the DMS console, this parameter is labeled Select a DMS database instance..

      • If the instance is not registered, or if you prefer not to use a registered one, manually configure the following parameters.

      Database Type

      Select MySQL.

      Access Method

      Select Cloud Enterprise Network (CEN).

      Instance Region

      Select the region where the source database is located.

      Replicate Data Across Alibaba Cloud Accounts

      -

      Specify whether the source database is in a different Alibaba Cloud account. If you select Yes, you must configure the Alibaba Cloud Account and RAM Role Name parameters.

      Alibaba Cloud Account

      Enter the numeric ID of the other Alibaba Cloud Account ID.

      RAM Role Name

      Enter the name of the RAM role.

      CEN Instance ID

      Select the CEN instance ID.

      Connected VPC

      From the selected CEN instance, select the VPC connected to the source database.

      Domain Name or IP

      Enter the source database endpoint.

      Port Number

      Enter the source database port.

      Database Account

      Enter the account for the source database.

      Database Password

      Enter the password for the database account.

      Encryption

      Select Non-encrypted or SSL-encrypted. If the source database uses an SSL-encrypted, you must also upload the CA Certificate and enter the CA Certificate Password.

      Database Gateway

      Parameter

      Description

      Select Existing Connection

      • If you need to use a database instance that has been registered in the system (created or saved), select the desired database instance from the drop-down list. The database information below will be automatically configured.

        Note

        In the DMS console, this parameter is labeled Select a DMS database instance..

      • If the instance is not registered, or if you prefer not to use a registered one, manually configure the following parameters.

      Database Type

      Select MySQL.

      Access Method

      Select Database Gateway.

      Instance Region

      Select the region where the DG is located.

      Database Gateway ID

      Select the ID of your Database Gateway.

      Database Endpoint

      Select the database address managed by the Database Gateway.

      Database Account

      Enter the account for the source database.

      Database Password

      Enter the password for the database account.

      Encryption

      Select Non-encrypted or SSL-encrypted. If the source database uses an SSL-encrypted, you must also upload the CA Certificate and enter the CA Certificate Password.

      Destination Database

      The Access Method options for the Access Method depend on the connection method of the source database. See the console for available options. The following sections list the required parameters for each Access Method.

      Note

      Connecting to a target database using a Public IP Address address is not supported. Use a data migration task for this purpose instead.

      Alibaba Cloud Instance

      Parameter

      Description

      Example image

      Select Existing Connection

      • To use a database instance that has been registered with the system (newly created or saved), select it from the drop-down list. Its information is automatically populated.

        Note

        If you are on the Data Management (DMS) console, this parameter is named Select a DMS database instance..

      • If the database instance is not registered or you do not want to use a registered one, manually configure the information below.

      To configure the target database information, set Database type to relational database > MySQL and Connection method to Cloud instance. For Region, select the region where the target instance is located, such as China (Beijing). For Cross-account, select No. Then, select the RDS instance ID, enter the database account and database password. For Connection mode, select unencrypted connection or SSL-encrypted connection.

      Database Type

      Select the required database type. This example uses MySQL.

      Access Method

      Select Alibaba Cloud Instance.

      Instance Region

      Select the region where the target database is located.

      Replicate Data Across Alibaba Cloud Accounts

      -

      Indicates whether to connect across different Alibaba Cloud accounts. If you select Yes, you must configure the Alibaba Cloud Account and RAM Role Name parameters.

      Alibaba Cloud Account

      Enter the Alibaba Cloud Account ID.

      RAM Role Name

      Enter the name of the Resource Access Management (RAM) role.

      RDS Instance ID

      Select the instance ID of the target RDS for MySQL instance. If you select a different Database Type, select the corresponding instance ID.

      Database Account

      Enter the account for the target RDS for MySQL instance.

      Database Password

      Enter the password that corresponds to the database account.

      Encryption

      Select either Non-encrypted or SSL-encrypted.

      Express Connect, VPN Gateway, or Smart Access Gateway

      Parameter

      Description

      Example image

      Select Existing Connection

      • To use a database instance that has been registered with the system (newly created or saved), select it from the drop-down list. Its information is automatically populated.

        Note

        If you are on the Data Management (DMS) console, this parameter is named Select a DMS database instance..

      • If the database instance is not registered or you do not want to use a registered one, manually configure the information below.

      In the target database information form, set Database type to MySQL and Connection method to Express Connect, VPN Gateway, or Smart Access Gateway. Then, configure Region, VPC connected to the target database, Domain name or IP address, Port (default is 3306), database account, and database password. For Connection mode, select unencrypted connection or SSL-encrypted connection. In the VPC data channel section, configure the Primary vSwitch and Standby vSwitch.

      Database Type

      Select the required database type. This example uses MySQL.

      Access Method

      Select Express Connect, VPN Gateway, or Smart Access Gateway.

      Instance Region

      Select the region of the VPC that is connected to the target database.

      Connected VPC

      Select the VPC that is connected to the target database.

      Domain Name or IP

      Enter the connection endpoint of the target database.

      Port Number

      Enter the port of the target database.

      Database Account

      Enter the account for the target database.

      Database Password

      Enter the password that corresponds to the database account.

      Encryption

      Select an Non-encrypted or an SSL-encrypted based on your requirements. If an SSL-encrypted is enabled for the destination database, you also need to upload a CA Certificate and enter the CA Certificate Password.

      Primary/Secondary vSwitch in VPC Tunnel

      Select the vSwitch (Primary) and vSwitch (Secondary) that Data Transmission Service (DTS) will use to access the data source through the VPC data channel.

      Self-managed Database on ECS

      Parameter

      Description

      Example image

      Select Existing Connection

      • To use a database instance that has been registered with the system (newly created or saved), select it from the drop-down list. Its information is automatically populated.

        Note

        If you are on the Data Management (DMS) console, this parameter is named Select a DMS database instance..

      • If the database instance is not registered or you do not want to use a registered one, manually configure the information below.

      In the Target database information section, set Database type to relational database > MySQL and Connection method to Self-managed database on an ECS instance. Then, configure Region, ECS instance ID, Port (default is 3306), database account, database password, and Connection mode (unencrypted connection or SSL-encrypted connection).

      Database Type

      Select the required database type. This example uses MySQL.

      Access Method

      Select Self-managed Database on ECS.

      Instance Region

      Select the region where the Elastic Compute Service (ECS) instance is located.

      ECS Instance ID

      Select the ECS instance ID.

      Port Number

      Enter the port of the self-managed MySQL database on the ECS instance.

      Database Account

      Enter the account for the self-managed MySQL database.

      Database Password

      Enter the password that corresponds to the database account.

      Encryption

      Select Non-encrypted or SSL-encrypted based on your requirements. If the destination database has SSL-encrypted enabled, you also need to upload the CA Certificate and enter the CA Certificate Password.

      Cloud Enterprise Network (CEN)

      Parameter

      Description

      Example image

      Select Existing Connection

      • To use a database instance that has been registered with the system (newly created or saved), select it from the drop-down list. Its information is automatically populated.

        Note

        If you are on the Data Management (DMS) console, this parameter is named Select a DMS database instance..

      • If the database instance is not registered or you do not want to use a registered one, manually configure the information below.

      In the target database information section, set Database type to relational database > MySQL and Connection method to Cloud Enterprise Network (CEN). Then, configure Region, CEN instance ID, VPC connected to the database, Domain name or IP address, Port (default is 3306), database account, and database password. For connection mode, select unencrypted connection.

      Database Type

      Select the required database type. This example uses MySQL.

      Access Method

      Select Cloud Enterprise Network (CEN).

      Instance Region

      Select the region where the target database is located.

      CEN Instance ID

      Select the CEN instance ID.

      Connected VPC

      Select the VPC in the CEN instance that is connected to the target database.

      Domain Name or IP

      Enter the connection endpoint of the target database.

      Port Number

      Enter the port of the target database.

      Database Account

      Enter the account for the target database.

      Database Password

      Enter the password that corresponds to the database account.

      Encryption

      Select Non-encrypted or SSL-encrypted based on your actual situation. If the destination database uses an SSL-encrypted, you also need to upload a CA Certificate and enter the CA Certificate Password.

      Database Gateway

      Parameter

      Description

      Example image

      Select Existing Connection

      • To use a database instance that has been registered with the system (newly created or saved), select it from the drop-down list. Its information is automatically populated.

        Note

        If you are on the Data Management (DMS) console, this parameter is named Select a DMS database instance..

      • If the database instance is not registered or you do not want to use a registered one, manually configure the information below.

      If you select Database Gateway (DG) as the connection method, configure Region, Database Gateway ID, Database address, database account, database password, and Connection mode (optional: unencrypted connection or SSL-encrypted connection). If you connect using a public domain name, performance depends on public network conditions. For standard hybrid cloud solutions with an IDC, we recommend using Express Connect.

      Database Type

      Select the required database type. This example uses MySQL.

      Access Method

      Select Database Gateway.

      Instance Region

      Select the region where the Database Gateway (DG) is located.

      Database Gateway ID

      Select the Database Gateway ID.

      Database Endpoint

      Select the database address managed by the DG.

      Database Account

      Enter the account for the target database instance.

      Database Password

      Enter the password that corresponds to the database account.

      Encryption

      Select Non-encrypted or SSL-encrypted as required. If an SSL-encrypted is enabled for the destination database, you also need to upload a CA Certificate and enter the CA Certificate Password.

  4. After completing the configuration, click Test Connectivity and Proceed to verify that DTS can connect to the source and destination databases. If the CIDR Blocks of DTS Servers dialog box appears, add the IP addresses it displays to the security settings of the corresponding source or destination database. Then, click Confirm authorization and test link. If the connection test is successful, you will be automatically redirected to configure the task objects.

Step 3: Configure synchronization objects and advanced options

  1. On the Configure Objects page, configure settings such as the synchronization type and synchronization objects. The available settings vary by target database type. Select the tab for your target database type to view the corresponding settings.

    MySQL

    Parameter

    Description

    Synchronization Types

    Incremental Data Synchronization is selected by default. You also need to select Schema Synchronization and Full Data Synchronization. After the precheck, Data Transmission Service (DTS) initializes the target cluster with the full data of the synchronization objects from the source instance. This data serves as the baseline for subsequent incremental synchronization.

    Method to Migrate Triggers in Source Database

    Select a method to synchronize triggers based on your requirements. If the objects to be synchronized do not involve triggers, you can skip this setting. For more information, see Configure how to synchronize or migrate triggers.

    Note

    This parameter is available only when you select Synchronization Types for Schema Synchronization.

    Enable Migration Assessment

    This feature assesses if the source and target database schemas meet requirements, checking elements like index length, stored procedures, and dependent tables. You can select Yes or No based on your needs.

    Note

    • This parameter is available only when you select Synchronization Types for Schema Synchronization.

    • If you select Yes, the precheck may take longer. You can view the Assessment Result during the precheck phase. The assessment result does not affect the precheck result.

    Synchronization Topology

    Select One-way Synchronization or Two-way Synchronization based on your business requirements. Two-way Synchronization is supported only for specific source and target instance pairs. For more information, see Data synchronization topologies.

    Enable Exactly-Once Write

    If the synchronization objects include tables without a primary key or unique constraint, select Yes. For more information, see Synchronize tables without primary keys and unique constraints.

    Note

    This parameter is available only when you select Synchronization Topology for Two-way Synchronization.

    Exclude DDL Operations

    • Yes: Does not synchronize DDL operations.

    • No: Synchronizes DDL operations.

    Note

    • This parameter is available only when you select Synchronization Topology for Two-way Synchronization.

    • To ensure stability in bidirectional synchronization, this setting applies only to the forward task (source to target). DDL operations are always filtered for the reverse task (target to source).

    Global Conflict Resolution Policy

    If conflicts occur, select a suitable conflict resolution policy based on your business requirements.

    Supported conflict detection types

    To ensure data consistency, make sure that records with the same primary key, business key, or unique key are updated on only one node in a bidirectional synchronization configuration. If updates occur on both nodes, DTS responds based on the conflict resolution policy that you configure.

    DTS uses conflict detection and resolution to maximize the stability of bidirectional synchronization instances. DTS can detect the following types of conflicts:

    • Unique constraint conflict caused by INSERT

      An INSERT statement violates a unique constraint during synchronization. For example, if both nodes in a bidirectional synchronization configuration INSERT a record with the same primary key value at or near the same time, the INSERT fails on the peer node because a record with that primary key already exists.

    • Incomplete match for UPDATE operation

      • If the record to be updated by an UPDATE statement does not exist in the target instance, DTS automatically converts the operation into an INSERT statement. This may cause a unique key conflict.

      • An UPDATE statement causes a primary key or unique key conflict.

    • Record to be deleted by DELETE does not exist

      The record to be deleted by a DELETE statement does not exist in the target instance. When this type of conflict occurs, DTS automatically ignores the DELETE operation, regardless of the configured conflict resolution policy.

    Important

    • Due to factors like system time differences and synchronization latency, the DTS conflict detection mechanism cannot prevent all data conflicts. When using bidirectional synchronization, you must also modify your application to ensure that records with the same primary key, business key, or unique key are updated on only one of the two nodes.

    • DTS provides resolution policies for the preceding data synchronization conflicts. You can select a policy when you configure bidirectional synchronization.

    • Overwrite (If a conflict occurs, the conflicting record in the destination instance is overwritten): Overwrites the record in the target instance with the conflicting record from the source.

    • TaskFailed (If a conflict occurs, an error is reported and the task is stopped): Reports an error and stops the task. You must then resolve the conflict manually.

    • Ignore (If a conflict occurs, the conflicting record in the destination instance is used): Skips the conflicting operation and retains the existing record in the target instance.

    Note

    • This parameter is available only when you select Synchronization Topology for Two-way Synchronization.

    • If the synchronization task is paused or restarted and latency occurs, these policies do not take effect during the latency period. The data in the target instance is overwritten by default.

    Processing Mode of Conflicting Tables

    • Precheck and Report Errors: Checks whether a table with the same name exists in the target database. If none exist, the precheck passes. If a table with the same name exists, the precheck fails, and the task does not start.

      Note

      If you cannot delete or rename the table with the same name in the target database, you can change the table name in the target database. For more information, see schema, table, and column name mapping.

    • Ignore Errors and Proceed: Skips the check for tables with the same name in the target database.

      Warning

      If you select Ignore Errors and Proceed, data inconsistency may occur and pose risks to your business. For example:

      • If the table schemas are consistent and a record in the target database has the same primary key or unique key value as a record in the source database:

        • During full synchronization, DTS retains the record in the target cluster. The record from the source database is not synchronized to the target database.

        • During incremental synchronization, DTS does not retain the record in the target cluster. The record from the source database overwrites the record in the target database.

      • If the table schemas are inconsistent, the task may fail or only partially synchronize data. Use this option with caution.

    Whether to migrate Event

    Select whether to synchronize events from the source database. If you select Yes, you must meet specific requirements and perform additional steps. For more information, see Synchronize or migrate events.

    Capitalization of Object Names in Destination Instance

    You can configure the case sensitivity policy for the schema, table, and column names of synchronization objects in the target instance. By default, DTS default policy is selected. You can also select Consistent with the source database or Consistent with the default policy of the destination database. For more information, see Case sensitivity of target object names.

    Source Objects

    In the Source Objects box, click the objects that you want to synchronize, and then click 向右 to move them to the Selected Objects box.

    Note

    You can select synchronization objects at the database, table, or column level. If you select tables or columns as synchronization objects, other types of objects such as views, triggers, and stored procedures are not synchronized to the target database.

    Selected Objects
    Note

    • Using the object name mapping feature may cause synchronization to fail for objects that depend on the renamed object.

    • To select the SQL operations to be synchronized at the database or table level, right-click a synchronization object in the Selected Objects box and select the desired SQL operations in the dialog box that appears.

    • To filter data by using a WHERE clause, right-click the table to be synchronized in the Selected Objects box and set the filter condition in the dialog box that appears. For instructions on how to set a filter condition, see Configure filter conditions.

    Kafka

    Parameter

    Description

    Synchronization Types

    Incremental Data Synchronization is selected by default. You also need to select Schema Synchronization and Full Data Synchronization. After the precheck, Data Transmission Service (DTS) initializes the target cluster with the full data of the synchronization objects from the source instance. This data serves as the baseline for subsequent incremental synchronization.

    Note

    If the Access Method of the target Kafka instance is Alibaba Cloud Instance, Schema Synchronization is not supported.

    Processing Mode of Conflicting Tables

    • Precheck and Report Errors: Checks whether a table with the same name exists in the target database. If none exist, the precheck passes. If a table with the same name exists, the precheck fails, and the task does not start.

      Note

      If you cannot delete or rename the table with the same name in the target database, you can change the table name in the target database. For more information, see schema, table, and column name mapping.

    • Ignore Errors and Proceed: Skips the check for tables with the same name in the target database.

      Warning

      If you select Ignore Errors and Proceed, data inconsistency may occur and pose risks to your business. For example:

      • If the table schemas are consistent and a record in the target database has the same primary key or unique key value as a record in the source database:

        • During full synchronization, DTS retains the record in the target cluster. The record from the source database is not synchronized to the target database.

        • During incremental synchronization, DTS does not retain the record in the target cluster. The record from the source database overwrites the record in the target database.

      • If the table schemas are inconsistent, the task may fail or only partially synchronize data. Use this option with caution.

    Data Format in Kafka

    Select the storage format for data that is synchronized to the Kafka instance.

    Synchronize all fields

    Specifies whether to synchronize all fields.

    Kafka Data Compression Format

    Select a compression format for Kafka messages based on your requirements.

    • LZ4 (default): Low compression ratio but very fast.

    • GZip: High compression ratio, but slow and CPU-intensive.

    • Snappy: A balance between compression ratio and speed.

    Policy for Shipping Data to Kafka Partitions

    Select a policy based on your business requirements.

    Message acknowledgement mechanism

    Select a message acknowledgment mechanism based on your business requirements.

    Topic That Stores DDL Information

    From the drop-down list, select the topic that you want to use to store DDL information.

    Note

    If you do not select a topic, DDL information is stored in the topic that receives data by default.

    Capitalization of Object Names in Destination Instance

    You can configure the case sensitivity policy for the schema, table, and column names of synchronization objects in the target instance. By default, DTS default policy is selected. You can also select Consistent with the source database or Consistent with the default policy of the destination database. For more information, see Case sensitivity of target object names.

    Source Objects

    In the Source Objects box, click the objects that you want to synchronize, and then click 向右 to move them to the Selected Objects box.

    Note

    You can select synchronization objects at the database, table, or column level. If you select tables or columns as synchronization objects, other types of objects such as views, triggers, and stored procedures are not synchronized to the target database.

    Selected Objects

    In this example, no additional configuration is required. You can use the mapping feature to specify the topic name for the source table in the target Kafka instance, and select the DML and DDL operations, column names, or other information to be synchronized.

    Note

    • To select the SQL operations to be synchronized at the database or table level, right-click a synchronization object in the Selected Objects box and select the desired SQL operations in the dialog box that appears.

    • Using the object name mapping feature may cause synchronization to fail for objects that depend on the renamed object.

    AnalyticDB for MySQL 3.0

    Parameter

    Description

    Synchronization Types

    Incremental Data Synchronization is selected by default. You also need to select Schema Synchronization and Full Data Synchronization. After the precheck, Data Transmission Service (DTS) initializes the target cluster with the full data of the synchronization objects from the source instance. This data serves as the baseline for subsequent incremental synchronization.

    Note

    If you select Full Data Synchronization, tables to be synchronized for which CREATE TABLE statements are executed (including table schema and data) can be synchronized to the target database.

    DDL and DML Operations to Be Synchronized

    Select the DML and DDL operations to be synchronized at the instance level. All operations are selected by default.

    Note

    To select the SQL operations to be synchronized at the database or table level, right-click a synchronization object in the Selected Objects box and select the desired SQL operations in the dialog box that appears.

    Merge Tables

    Specifies whether to synchronize multiple source tables that have the same schema (sharded tables) to a single table in the target database.

    • Yes: In OLTP scenarios, database sharding and table splitting are often used to improve the response speed of business tables. In OLAP scenarios, such as for this target database, a single data table can store massive amounts of data and single-table queries are more convenient. In such scenarios, you can use the multi-table merging feature of DTS to synchronize multiple source tables that have the same schema (sharded tables) to a single table in the target database. For more information, see Enable multi-table merging.

      Note

      • After you select multiple source tables, you must use the object name mapping feature to map them to the same table name in the target database. For more information about the object name mapping feature, see schema, table, and column name mapping.

      • DTS adds the __dts_data_source column of the TEXT type to the synchronized target table to identify the source of each row. DTS writes values in the format of DTS instance ID:database name:schema name:table name to the column to distinguish the table source. Example: dts********:dtstestdata:testschema:customer1.

      • Multi-table merging is a task-level feature and is not supported at the table level. If you want to enable multi-table merging for some tables but not for others, you must create two data synchronization tasks.

      Warning

      Do not perform DDL operations that change the database or table schema on the source database. Otherwise, data inconsistency or task failures may occur.

    • No: This is the default option.

    Processing Mode of Conflicting Tables

    • Precheck and Report Errors: Checks for tables with the same names in the destination database. If any tables with the same names are found, an error is reported during the precheck and the data synchronization task does not start. Otherwise, the precheck is successful.

      Note

      If you cannot delete or rename the table with the same name in the destination database, you can map it to a different name in the destination. For more information, see Object name mapping.

    • Ignore Errors and Proceed: Skips the check for tables with the same name in the destination database.

      Warning

      Selecting Ignore Errors and Proceed may cause data inconsistency and put your business at risk. For example:

      • If the table schemas are consistent and a record in the destination database has the same primary key or unique key value as a record in the source database:

        • During full data synchronization, DTS retains the destination record and skips the source record.

        • During incremental synchronization, DTS overwrites the destination record with the source record.

      • If the table schemas are inconsistent, data initialization may fail. This can result in only partial data synchronization or a complete synchronization failure. Use with caution.

    Capitalization of Object Names in Destination Instance

    You can configure the case sensitivity policy for the schema, table, and column names of synchronization objects in the target instance. By default, DTS default policy is selected. You can also select Consistent with the source database or Consistent with the default policy of the destination database. For more information, see Case sensitivity of target object names.

    Source Objects

    In the Source Objects box, click the objects that you want to synchronize, and then click 向右 to move them to the Selected Objects box.

    Note

    • You can select synchronization objects at the database, table, or column level. If you select tables or columns as synchronization objects, other types of objects such as views, triggers, and stored procedures are not synchronized to the target database.

    • If you select an entire database as the synchronization object, the following default behavior applies:

      • If a table to be synchronized in the source database has a primary key (single-column or multi-column), the primary key column(s) are used as the distribution key.

    Selected Objects
    Note

    • To select the SQL operations to be synchronized at the database or table level, right-click a synchronization object in the Selected Objects box and select the desired SQL operations in the dialog box that appears.

    • To filter data by using a WHERE clause, right-click the table to be synchronized in the Selected Objects box and set the filter condition in the dialog box that appears. For instructions on how to set a filter condition, see Configure filter conditions.

  2. Click Advanced Settings. In most cases, you can use the default settings.

    Parameters

    Note

    The options shown in the console are the most current.

    Parameter

    Description

    Dedicated Cluster for Task Scheduling

    By default, DTS schedules tasks on a shared cluster, and you do not need to select one. If you want tasks to be more stable, you can purchase a dedicated cluster to run DTS synchronization tasks. For more information, see What is a DTS dedicated cluster?

    Copy the temporary table of the Online DDL tool that is generated in the source table to the destination database.

    If the source database uses Data Management (DMS) or gh-ost to perform online DDL changes, you can choose whether to synchronize the data in temporary tables that are generated by the online DDL change process.

    Important

    • DTS does not support online DDL tools like pt-online-schema-change. Using such tools will cause the synchronization task to fail.

    • The handling method varies based on the phase. The Schema Synchronization and Full Data Synchronization phases do not allow DDL operations that change the database or table schema. Therefore, these phases are not controlled by the online DDL policy.

      • Schema Synchronization: This phase is not controlled by the online DDL policy. The related temporary tables are created.

      • Full Data Synchronization: This phase is not controlled by the online DDL policy. Temporary tables are not synchronized. All tables whose names match the specified regular expressions are filtered.

      • Incremental Data Synchronization: This phase is controlled by the online DDL policy.

        • Yes: Synchronizes data changes in the temporary tables that are generated by online DDL operations, such as _table_name_gho.

        • No, Adapt to DMS Online DDL and No, Adapt to gh-ost: Filter data changes in the temporary tables that are generated by tools such as gh-ost based on regular expression rules. An example of such a table is _table_name_gho.

    • Yes: Synchronizes the data in temporary tables that are generated by online DDL changes.

      Note

      • If the temporary tables generated by online DDL changes contain a large amount of data, synchronization latency may occur.

    • No, Adapt to DMS Online DDL: Does not synchronize the data in temporary tables. DTS synchronizes only the original DDL statements that are executed by using Data Management (DMS) in the source database.

      Note

      This option causes table locks in the target database.

    • No, Adapt to gh-ost: Does not synchronize the data in temporary tables and allows you to customize filtering rules. DTS filters data changes from temporary tables that are generated by tools such as gh-ost based on regular expression rules. An example of such a table is _table_name_gho. You can modify the default regular expressions that are used to match shadow tables and garbage tables based on your requirements:

      • Shadow table: ^_(.+)_(?:gho|new)$

      • Garbage table: ^_(.+)_(?:ghc|del|old)$

      Note

      This option causes table locks in the target database.

    Whether to Migrate Accounts

    Select whether to synchronize account information from the source database based on your requirements. If you select Yes, you must also select the accounts to be synchronized and confirm the account permissions. For more information, see Migrate database accounts.

    Retry Time for Failed Connections

    If the connection to the source or target database fails after a task starts, DTS immediately begins to retry the connection. The default retry duration is 720 minutes. You can also specify a custom value that ranges from 10 to 1,440 minutes. We recommend that you specify a value of 30 minutes or more. If DTS reconnects to the source and target databases within the specified retry duration, the synchronization task automatically resumes. Otherwise, the task fails.

    Note

    • If you have multiple DTS instances that connect to the same source or target, such as instance A and instance B, and you set the retry duration for A to 30 minutes and for B to 60 minutes, the smaller value (30 minutes) takes effect for both.

    • DTS charges fees for task running time during the connection retry period. We recommend that you specify a custom retry duration based on your business requirements, or release the DTS instance as soon as the source and target instances are released.

    Retry Time for Other Issues

    After a synchronization task starts, if a non-connection issue, such as a DDL or DML execution error, occurs on the source or target database, DTS reports an error and immediately retries the operation. The default retry duration is 10 minutes. You can also specify a custom value that ranges from 1 to 1,440 minutes. We recommend that you specify a value of 10 minutes or more. If the operation succeeds within the specified retry duration, the synchronization task automatically resumes. Otherwise, the task fails.

    Important

    The value of Retry Time for Other Issues must be less than the value of Retry Time for Failed Connections.

    Enable Throttling for Full Data Synchronization

    To reduce the load on your source and target databases, you can limit the full synchronization rate by setting Queries per second (QPS) to the source database, RPS of Full Data Migration, and Data migration speed for full migration (MB/s).

    Note

    • This parameter is available only when Synchronization Types is selected for Full Data Synchronization.

    • You can also adjust the rate of full synchronization after the synchronization instance starts.

    Enable Throttling for Incremental Data Synchronization

    Based on your requirements, you can also select whether to limit the rate of the incremental synchronization task by setting RPS of Incremental Data Synchronization and Data synchronization speed for incremental synchronization (MB/s). This reduces the pressure on the target database.

    Whether to delete SQL operations on heartbeat tables of forward and reverse tasks

    Select whether to write heartbeat SQL information to the source database while the DTS instance is running.

    • Yes: Prevents DTS from writing heartbeat data to the source database. This may result in inaccurate latency reporting in the DTS console.

    • No: Writes heartbeat SQL information to the source database. This may affect features of the source database, such as physical backup and cloning.

    Environment Tag

    You can select a tag to identify the instance's environment (e.g., "production" or "testing").

    Scene Label

    You can select a tag to identify the instance's scenario (e.g., "data migration" or "disaster recovery").

    Configure ETL

    Select whether to configure the ETL feature. For more information about ETL, see What is ETL?

    Sensitive Data Scanning and Masking

    Specifies whether to enable sensitive data scanning and desensitization to identify and process sensitive information in the transmitted data. For more information, see Sensitive data scanning and desensitization.

    • Open

    • Close

    Monitoring and Alerting

    Configure alarms to send notifications if the synchronization fails or if latency exceeds a specified threshold.

Step 4: (Optional) Configure data verification

If the destination database type supports data verification, click **Next** to configure the Data Verification task. If data verification is not supported, you are automatically redirected to Step 5: Pre-check and Purchase.

  1. Select a Data Verification Mode:

    Note

    • Full Data Verification: Verifies the data synchronized by the full task. If the instance does not include an incremental task, verification starts after the instance is created and the full data write is complete. If the instance includes an incremental task, verification starts after the instance is created and when the incremental task first achieves zero latency.

    • Incremental Data Verification: Verifies the data from the incremental task. Verification starts after the instance is created and when the incremental task first achieves zero latency.

    • Schema Verification: Verifies the schema of the selected objects. If the instance does not include an incremental task, verification starts after the instance is created and after schema synchronization and the full data write are complete. If the instance includes an incremental task, verification starts after the instance is created and when the incremental task first achieves zero latency.

    Full verification

    If you select Full Data Verification, you also need to configure the parameters described in the following table.

    Parameter

    Description

    Full Data Verification

    • Full field validation by row sampling: Specify a sampling percentage. DTS verifies all fields in the sampled data. The value must be an integer from 10 to 100.

    • Verify based on the number of table rows: Verifies only the row count for the full task. This mode does not verify row-level data.

    Note

    The Verify based on the number of table rows mode is free of charge. The Full field validation by row sampling mode is billed based on the amount of data verified.

    Timeout Settings for Full Data Verification

    • No: The full verification task is not forcibly terminated if it times out.

    • Yes: Specify a timeout period for the full verification task. The timer starts when the task begins. If the verification task does not complete within the specified period, DTS forcibly terminates it. The value must be an integer from 1 to 72.

    Full calibration reference

    • Default: Uses the union of the source and destination databases as the baseline to verify data consistency.

    • Source Database: Uses the source database as the baseline to verify that the destination database is consistent with it. Data that exists only in the destination database is not verified.

    • Destination Database: Uses the destination database as the baseline to verify that the source database is consistent with it. Data that exists only in the source database is not verified.

    Maximum number of rows of data read per second by full verification (RPS)

    Full data verification consumes database read resources. You can limit the read rate of the full verification task to reduce the load on the database.

    Note

    A value of 0 indicates no limit. If both Maximum number of rows of data read per second by full verification (RPS) and Maximum amount of data read per second by full verification (MBps) are set to 0, no throttling is applied.

    Maximum amount of data read per second by full verification (MBps)

    Incremental verification

    If you select Incremental Data Verification, you also need to configure the following parameters.

    Parameter

    Description

    Incremental Verification Benchmark

    Select the DML operations to verify. Supported operations include Source INSERT Operation, Source UPDATE Operation, and Source DELETE Operation.

  2. Configure Verification Objects: All objects are selected by default. You can remove the objects on the right that do not need to be verified..

  3. Configure verification alarms: Configure the parameters in the following table.

    Note

    You can also set or modify data verification alarms after the DTS instance has started.

    Parameter

    Description

    Full Data Verification Alert

    • No: No alarm is configured.

    • Yes: Configure alarms. You must also select and configure alarm rules. The following alarm rules are supported:

      • An alarm is triggered if the full verification task fails.

      • Set a threshold for data inconsistency. An alarm is triggered if the number of inconsistent records in the full verification task is greater than or equal to the specified threshold.

    Incremental Data Verification Alert

    • No: No alarm is configured.

    • Yes: Configure alarms. You must also select and configure alarm rules. The following alarm rules are supported:

      • An alarm is triggered if the incremental verification task fails.

      • Set the number of evaluation cycles, the duration of each cycle, and the inconsistency threshold. An alarm is triggered if the number of inconsistent records is greater than or equal to the threshold for the specified number of consecutive cycles.

      • Set the number of evaluation cycles, the duration of each cycle, and the latency threshold. An alarm is triggered if the verification latency is greater than or equal to the threshold for the specified number of consecutive cycles.
    Note

    If you configure verification alarms, you must also enter the mobile phone number of the Alert Contact. When a verification task triggers an alarm, an SMS notification is sent to this contact.

Step 5: Pre-check and purchase

  1. Save the task and run a pre-check. DTS verifies that the configurations, permissions, and network settings of the source and target databases meet synchronization requirements. After the pre-check passes, click Next: Purchase Instance.

    • To view the OpenAPI parameters for configuring this instance, hover over the Next: Save Task Settings and Precheck button and click Preview OpenAPI parameters in the tooltip.

    • If you do not need to view the API parameters or have finished viewing them, click Next: Save Task Settings and Precheck at the bottom of the page.

    Note

    • A synchronization job can start only after it successfully passes a pre-check.

    • If the pre-check fails, click View Details next to the failed item, resolve the issue as instructed, and then run the pre-check again.

    • If the pre-check returns a warning:

      • For items that cannot be ignored, click View Details next to the item, resolve the issue as instructed, and then run the pre-check again.

      • For items that can be ignored, click Confirm Alert Details, Ignore, OK, and then Precheck Again to ignore the warning and rerun the pre-check. Note that ignoring warnings can cause data inconsistencies and other business risks.

    After the pre-check is complete, the page displays all check items and their results. In this example, there are 19 check items and the pass rate is 100%. The Target SUPER Privilege Check returns a warning, while the other 18 items are successful. The successful items include checks for source and target database connectivity, permissions, binlog-related settings, storage engines, MySQL password format, and objects with the same name.

  2. On the purchase page, configure the billing method and link specification.

    • Billing method: You can select Subscription, Pay-as-you-go, or Pay-as-you-go Serverless. For more information, see Billing Method.

    • Instance Class: Determines the maximum performance (RPS) for incremental synchronization. Select a specification, such as small or medium, based on the data change frequency in your source database. For more information, see Specifications.

    • After completing the configuration, read and select the Data Transmission Service (Pay-as-you-go) Service Terms checkbox.

    • Click Buy and Start. In the displayed OK dialog box, click OK to complete the purchase. The task then starts automatically.

On the purchase page, configure the resource group and synchronization topology. This example uses one-way synchronization.

Step 6: (Optional) Configure reverse synchronization

  1. Wait until the Status of the forward synchronization task is Running.

  2. Navigate to the reverse synchronization task and click Configure Task.

  3. Configure the reverse synchronization task by following the steps above.

    Important

    • For a reverse synchronization task, the roles of the instances are swapped: the source instance is the original target, and the target instance is the original source. Ensure that all connection details, such as the database name, account, and password, are correct for each instance.

    • The Instance Region of the source and target databases cannot be changed in a reverse synchronization task. This task has fewer parameters to configure than a forward synchronization task. Refer to the console for the specific parameters.

    • For a reverse synchronization task, the Processing Mode of Conflicting Tables setting ignores tables synchronized by the forward synchronization task.

    • A reverse synchronization task does not synchronize the objects specified in the Selected Objects list of the forward synchronization task.

    • To prevent potential data inconsistency, we recommend not using object mapping when you configure a reverse synchronization task.

  4. When the Success Rate reaches 100%, click Back.

Step 7: Monitor and validate

After the task starts, it proceeds through the configured phases: Incremental Data Collection, Schema Migration, Full Data Synchronization, Incremental Write, and Full Data Verification.

  • View status: View the task's current phase and running status in the synchronization task list. When incremental synchronization begins and latency stabilizes within seconds, the full load is complete, and DTS is synchronizing incremental data in real time.

  • Data validation: Check for data consistency in the Check Details section of the Full Data Verification or Schema Verification tabs.

Previous：MySQL as a Source: Data Synchronization LimitsNext：Synchronize data from a self-managed MySQL database to an RDS for MySQL instance