Synchronize data from a MySQL database to Tablestore using DTS

Usage notes

Synchronization types

• Schema synchronization

  DTS synchronizes the schema definitions of objects from the source database to the destination database. These objects can include tables, views, triggers, and stored procedures.

• Full synchronization

  DTS synchronizes all historical data of the specified objects from the source database to the destination database. This data serves as the baseline for subsequent incremental synchronization. To simplify the data synchronization process, we recommend that you select both schema synchronization and full synchronization when you configure the data synchronization task.

• Incremental synchronization

  DTS synchronizes incremental data that is generated in the source database to the destination database in real time.

SQL operations supported for incremental synchronization

Required permissions for database accounts

Procedure

1. Go to the Synchronization Tasks page.

   1. Log on to the Data Management (DMS) console.

   2. In the top menu bar, click Integration and Development.

   3. In the left navigation pane, choose Data Transmission (DTS) > Data Synchronization.

   4. To the right of Synchronization Tasks, select the region where the synchronization instance is located.

   Note

   • The actual operations may vary depending on the mode and layout of the DMS console. For more information, see Simple mode and Customize the layout and style of the DMS console.

   • You can also log on to the new DTS Synchronization Tasks page.

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

   1. Optional: In the upper-right corner, click New Configuration Page.

      Note

      • If you are already on the new configuration page (the Back to Previous Version button is in the upper-right corner), you can skip this step.

      • Some parameters differ between the new and old configuration pages. We recommend that you use the new configuration page.

   2. Configure the source and destination database information.

      Category	Configuration	Description
      None	Task Name	DTS automatically generates a task name. We recommend that you specify a descriptive name for easy identification. The name does not need to be unique.
      Source Database Information	Select DMS Database Instance	Choose whether to use an existing instance as needed. If you use an existing instance, the database information is automatically filled in. You do not need to enter it again. If you do not use an existing instance, configure the database information below. Note In the DMS console, click Add DMS Database Instance to add a database instance. For more information, see Add an ApsaraDB database instance and Add a third-party or self-managed database instance. In the DTS console, you can add a database to DTS on the Database Connections page or the new configuration page. For more information, see Manage data connections.
      	Database Type	Select MySQL.
      	Access Method	Select Alibaba Cloud Instance.
      	Instance Region	Select the region where the source ApsaraDB RDS for MySQL instance resides.
      	Replicate Data Across Alibaba Cloud Accounts	This topic describes synchronization within the same Alibaba Cloud account. Select No. For cross-account scenarios, see Configure a task for cross-account data synchronization.
      	RDS Instance ID	Select the ID of the source ApsaraDB RDS for MySQL instance.
      	Database Account	Enter the database account for the source ApsaraDB RDS for MySQL instance. For permission requirements, see Permissions for database accounts.
      	Database Password	Enter the password for the specified database account.
      	Encryption	Select Non-encrypted or SSL-encrypted based on your database requirements. If you set this parameter to SSL-encrypted, you must enable SSL encryption for the RDS for MySQL instance beforehand. For more information, see Quickly enable SSL encryption using a cloud certificate.
      Destination Database Information	Select DMS Database Instance	Choose whether to use an existing instance as needed. If you use an existing instance, the database information is automatically filled in. You do not need to enter it again. If you do not use an existing instance, configure the database information below. Note In the DMS console, click Add DMS Database Instance to add a database instance. For more information, see Add an ApsaraDB database instance and Add a third-party or self-managed database instance. In the DTS console, you can add a database to DTS on the Database Connections page or the new configuration page. For more information, see Manage data connections.
      	Database Type	Select Tablestore.
      	Access Method	Select Alibaba Cloud Instance.
      	Instance Region	Select the region where the destination Tablestore instance resides.
      	Instance ID	Select the name of the destination Tablestore instance.
      	Alibaba Cloud Account AccessKey ID	Enter the AccessKey ID used to identify the user. For more information about how to obtain an AccessKey ID, see Create an AccessKey pair. Important If you enter the AccessKey ID of a RAM user, you must grant the AliyunOTSFullAccess permission (permission to manage Tablestore) to the RAM user. For more information, see Grant permissions to a RAM user using a RAM policy.
      	Alibaba Cloud Account AccessKey Secret	Enter the AccessKey secret used to authenticate the user. For more information about how to obtain an AccessKey secret, see Create an AccessKey pair.

   3. After completing the configuration, click Test Connection and Proceed at the bottom of the page.

      If the source or destination database is an Alibaba Cloud database instance, such as an ApsaraDB RDS for MySQL instance or an ApsaraDB for MongoDB instance, DTS automatically adds the IP addresses of the DTS servers in the corresponding region to the whitelist of the Alibaba Cloud database instance. If the source or destination database is a self-managed database hosted on an ECS instance, DTS automatically adds the IP addresses of the DTS servers in the corresponding region to the security rules of the ECS instance. You must also ensure that the self-managed database allows access from the ECS instance. If the database is deployed in a cluster on multiple ECS instances, you must manually add the IP addresses of the DTS servers in the corresponding region to the security rules of each ECS instance. If the source or destination database is a self-managed database in an on-premises data center or another cloud database, you must manually add the IP addresses of the DTS servers in the corresponding region to allow access from the DTS servers. For a list of DTS server IP addresses, see IP address blocks of DTS servers.

      Warning

      Adding the public IP address blocks of DTS servers, whether automatically by DTS or manually by you, may pose security risks. Using this product, you acknowledge and accept these potential risks. You must implement basic security measures, such as using strong passwords, restricting open ports for each CIDR block, enabling authentication for internal API communication, and periodically reviewing and restricting unnecessary CIDR blocks. Alternatively, you can use a private network connection, such as a leased line, VPN Gateway, or Smart Access Gateway.

3. You can configure the task object.

   1. On the Configure Objects page, you can configure the objects to synchronize.

      Configuration	Description
      Synchronization Types	DTS always selects Incremental Data Synchronization. By default, you must also select Schema Synchronization and Full Data Synchronization. After the precheck, DTS initializes the destination cluster with the full data of the selected source objects, which serves as the baseline for subsequent incremental synchronization.
      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.
      Synchronization Operation Types	Select the operation types to synchronize as needed. By default, all operation types are selected.
      Dirty Data Processing Policy	Select a policy for handling data write errors. The options are: Skip Block
      Data Write Mode	Select a data write mode. The options are: Row Update: Uses PutRowChange to perform row-level updates. Row Overwrite: Uses UpdateRowChange to perform row-level overwrites.
      Batch Write Method	The API operation that is called for batch writes. The options are: BulkImportRequest: Offline write. BatchWriteRowRequest: Batch write. Note We recommend using BulkImportRequest for improved read/write efficiency and lower billing costs for your Tablestore instance.
      More Settings	Based on your business requirements, you can click More Settings to configure the following parameters: Queue Size: The queue length of the data write process for the Tablestore instance. Thread Count: The number of callback processing threads for the data write process of the Tablestore instance. Concurrency: The concurrent request limit for the Tablestore instance. Bucket Count: The number of concurrent buckets for sequential writing of incremental data. Increasing this value can improve concurrent write performance. Note The value of Buckets must be less than or equal to the value of Concurrency.
      Case Sensitivity Policy for Destination Object Names	Configure the case-sensitivity policy for database, table, and column names in the destination instance. By default, the DTS default policy is selected. You can also choose to use the default policy of the source or destination database. For more information, see Case policy for destination object names.
      Source Objects	In the Source Objects box, click the objects, and then click to move them to the Selected Objects box. Note You can select objects at the database or table level.
      Selected Objects	To rename a single object in the destination instance, right-click the object in the Selected Objects box. For more information, see Map a single object name. To rename multiple objects in bulk, click Batch Edit in the upper-right corner of the Selected Objects box. For more information, see Map multiple object names in bulk. Note Database name mapping is not supported. You can map table and column names only when you select objects at the table level. If you use the mapping feature, other objects that depend on the mapped object may fail to synchronize. When you use the column name mapping feature, you can modify the type of the corresponding column in the destination Tablestore instance. To filter data using a WHERE clause, right-click the table to synchronize in the Selected Objects box and set the filter condition in the dialog box that appears. For more information, see Set filter conditions.

   2. Click Next: Advanced Settings to configure advanced settings.

      Configuration	Description
      Dedicated Cluster for Task Scheduling	By default, DTS uses a shared cluster for tasks, so you do not need to make a selection. For greater task stability, you can purchase a dedicated cluster to run the DTS synchronization task. For more information, see What is a DTS dedicated cluster?.
      Retry Time for Failed Connections	If the connection to the source or destination database fails after the synchronization task starts, DTS reports an error and immediately begins to retry the connection. The default retry duration is 720 minutes. You can customize the retry time to a value from 10 to 1,440 minutes. We recommend a duration of 30 minutes or more. If the connection is restored within this period, the task resumes automatically. Otherwise, the task fails. Note If multiple DTS instances (e.g., Instance A and B) share a source or destination, DTS uses the shortest configured retry duration (e.g., 30 minutes for A, 60 for B, so 30 minutes is used) for all instances. DTS charges for task runtime during connection retries. Set a custom duration based on your business needs, or release the DTS instance promptly after you release the source/destination instances.
      Retry Time for Other Issues	If a non-connection issue (e.g., a DDL or DML execution error) occurs, DTS reports an error and immediately retries the operation. The default retry duration is 10 minutes. You can also customize the retry time to a value from 1 to 1,440 minutes. We recommend a duration of 10 minutes or more. If the related operations succeed within the set retry time, the synchronization task automatically resumes. Otherwise, the task fails. Important The value of Retry Time for Other Issues must be less than that of Retry Time for Failed Connections.
      Enable Throttling for Full Data Synchronization	During full data synchronization, DTS consumes read and write resources from the source and destination databases, which can increase their load. To mitigate pressure on the destination database, you can limit the migration 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 if Synchronization Types is set to Full Data Synchronization. You can also adjust the rate of full data synchronization when the synchronization instance is running.
      Enable Throttling for Incremental Data Synchronization	You can also limit the incremental synchronization rate to reduce pressure on the destination database by setting RPS of Incremental Data Synchronization and Data synchronization speed for incremental synchronization (MB/s).
      Environment Tag	You can select an environment tag to identify the instance based on your requirements. For this example, you do not need to select a tag.
      Whether to delete SQL operations on heartbeat tables of forward and reverse tasks	Choose whether DTS writes heartbeat SQL information to the source database while the instance is running. Yes: Does not write heartbeat SQL information to the source database. The DTS instance may display latency. No: Writes heartbeat SQL information to the source database. This may interfere with source database operations like physical backups and cloning.
      Configure ETL	Choose whether to enable the extract, transform, and load (ETL) feature. For more information, see What is ETL? Valid values: Yes: Enables the ETL feature. Enter data processing statements in the code editor. For more information, see Configure ETL in a data migration or data synchronization task. No: Disables the ETL feature.
      Monitoring and Alerting	Choose whether to set up alerts. If the synchronization fails or the latency exceeds the specified threshold, DTS sends a notification to the alert contacts. No: No alerts are configured. Yes: Configures alerts. You must also set the alert threshold and alert contact. For more information, see Configure monitoring and alerting during task configuration.

   3. Click Next: Configure Table and Field and then click OK in the Note dialog box.

      By default, DTS uses the primary key from the original table as the Primary Key Column. You can change this by setting Definition Status to All.

      Note

      You can select multiple columns under Primary Key Column to form a composite primary key.

4. At the bottom of the page, click Next: Save Task and Precheck.

   • To view the API operation parameters for configuring this instance, move the pointer over the Next: Save Task Settings and Precheck button, and then click Preview OpenAPI parameters in the bubble.

   • If you have finished viewing the API parameters, click Next: Save Task Settings and Precheck at the bottom of the page.

   Note

   • Before the synchronization job starts, DTS runs a precheck. The synchronization job can start only after the task passes the precheck.

   • If the precheck fails, click View Details next to the failed check item. Fix the issue as prompted, and then run the precheck again.

   • If the precheck generates a warning:

     • For failed check items that cannot be ignored, click View Details next to the item. Fix the issue as prompted, and then run the precheck again.

     • If a check item can be ignored, you can skip its warning and rerun the precheck by clicking Click to Confirm Alert Details, Confirm Mute, OK, and then Rerun Precheck. However, muting a warning may cause issues such as data inconsistency and pose risks to your business.

5. When the Success Rate is 100%, click Next: Purchase Instance.

6. Purchase the instance.

   1. On the Purchase page, select the billing method and configure the data synchronization instance. The following table describes the parameters.

      Category	Parameter	Description
      New Instance Class	Billing Method	Subscription: Pay when you create the instance. This is suitable for long-term needs and is more cost-effective than pay-as-you-go. The longer the subscription duration, the greater the discount. Pay-as-you-go: Billed on an hourly basis. This is suitable for short-term needs. You can release the instance immediately after use to save costs. Pay-as-you-go Serverless: Billed on an hourly basis. Resources are dynamically adjusted at the minute level based on the instance load, with real-time billing (a billing order is generated every hour). You only pay for what you use, saving significant costs.
      	Resource Group Configuration	The resource group to which the instance belongs. The default is default resource group. For more information, see What is Resource Management?.
      	Link Specification	DTS provides synchronization specifications with different performance levels. The synchronization link specification affects the synchronization rate. You can choose a specification based on your business scenario. For more information, see Data synchronization link specifications. Note This option is available only when the billing method is Subscription or Pay-as-you-go.
      	Serverless Scaling Configuration (DU)	Serverless DTS uses DTS Units (DUs) for minute-level billing and scaling. The number of DUs is dynamically adjusted within a specified range based on the business load. For more information, see What is a DTS Serverless instance?. Note This option is available only when the billing method is Pay-as-you-go Serverless.
      	Subscription Duration	In subscription mode, select the duration and quantity for the subscription instance. You can choose a monthly subscription for 1 to 9 months, or a yearly subscription for 1, 2, 3, or 5 years. Note This option is available only when the billing method is Subscription.

   2. After you complete the configuration, read and select Data Transmission Service (Pay-As-You-Go) Terms of Service.

   3. Click Purchase and Start. In the Confirm dialog box, click OK.

      You can view the task progress on the Data Synchronization page.

Billing