ApsaraDB RDS:Sync data from MySQL to DuckDB

Precautions

• Tables in the RDS for MySQL instance must have a primary key to be synchronized.

• When DTS performs initial full data synchronization, it consumes read and write resources on the source and destination databases and can increase their load. We recommend performing data synchronization during off-peak hours, for example, when the CPU load of both databases is below 30%.

• Do not perform an online DDL change on synchronization objects in the source database by using tools such as gh-ost or pt-online-schema-change. Otherwise, the synchronization will fail.

• The cluster of a DuckDB-based analytical primary instance is locked if a node's disk space usage exceeds 80%. Estimate the space required for the objects to be synchronized in advance and ensure that the destination cluster has sufficient storage space.

• The use of prefix indexes is not supported and may cause data synchronization to fail.

• If you back up the destination DuckDB-based analytical primary instance while a DTS task is running, the task will fail.

• You cannot use a DuckDB-based analytical primary instance as a data source for data synchronization.

• If a DML statement contains LIMIT, a non-deterministic function such as UUID(), or an XA transaction, it is an unsafe operation for replication. This may cause data inconsistency. Avoid using these statements when you synchronize data to a DuckDB-based analytical primary instance.

Billing

Source instance types

Data synchronization tasks support the following source MySQL instance types:

• RDS for MySQL

• A self-managed database on an ECS instance

• An on-premises self-managed database

Supported SQL operations

• DDL operations: CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE, ADD COLUMN, DROP COLUMN, and MODIFY COLUMN

• DML operations: INSERT, UPDATE, and DELETE

Database account permissions

Steps

1. Log in to the Data Transmission Service console. From the left navigation pane, choose Data Synchronization. In the top navigation bar, select the target region.

2. On the Data Synchronization page, click Create Task.

3. On the Create a synchronization task > Configurations for Source and Destination Databases page, configure the source and destination databases.

   Source instance

   Parameter	Description
   database type	Relational Database > MySQL
   connection method	cloud instance
   instance region	The region of the source instance. This was selected when you purchased the data synchronization instance and cannot be changed.
   cross-account	Single account
   instance ID	Select the source RDS instance ID.
   database account	Enter the database account for the source RDS instance. For details on required permissions, see Database account permissions.
   database password	Enter the password for the database account.
   connection method	Select Non-encrypted or SSL-encrypted based on your requirements. If you select SSL-encrypted, you must enable SSL encryption for the RDS instance in advance. For more information, see Use a cloud certificate to quickly enable SSL encryption. Important Currently, the Encryption can be set only in the Chinese mainland and China (Hong Kong) regions.

   Destination instance

   Parameter	Description
   database type	Data Warehouse > DuckDB
   connection method	cloud instance
   instance region	The region of the destination instance. This was selected when you purchased the data synchronization instance and cannot be changed.
   cross-account	Single account Note DTS supports cross-region data synchronization. The example in this topic focuses on data synchronization between instances in the same region and VPC to show how to ensure data consistency. For more information about data synchronization, see Overview of data synchronization scenarios.
   RDS instance ID	Select the ID of the destination DuckDB instance.
   database account	Enter the database account for the RDS for DuckDB primary analytical instance. For details on required permissions, see Database account permissions.
   database password	Enter the password for the database account.
   connection method	Select Non-encrypted or SSL-encrypted as needed. If you select SSL-encrypted, you need to enable SSL encryption for the RDS instance in advance. For more information, see Quickly enable SSL link encryption with a cloud certificate. Important Currently, the Encryption can be set only in the Chinese mainland and China (Hong Kong) regions.

4. Click Test Connectivity and Proceed to continue.

5. In the CIDR Blocks of DTS Servers dialog box, click Test connection.

6. Configure the task objects.

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

      Parameter	Description
      Synchronization Types	For full synchronization only, select both Schema Synchronization and Full Data Synchronization. For synchronization without downtime, select Schema Synchronization, Full Data Synchronization, and Incremental Data Synchronization. Note If you do not select Schema Synchronization, make sure that the destination database contains the required databases and tables. You can then use the table and column name mapping feature in the Selected Objects box. If you do not select Incremental Data Synchronization, do not write new data to the source instance during synchronization to ensure data consistency.
      Method to Migrate Triggers in Source Database	Select a method to migrate triggers. If the objects to migrate do not have triggers, you can skip this parameter. For more information, see Configure a method to synchronize or migrate triggers. Note You can configure this parameter only when you select Schema Synchronization for Synchronization Types.
      Enable Migration Assessment	This feature evaluates whether the schemas of the source and destination databases, such as index length, stored procedures, and dependent tables, meet the requirements. Select Yes or No. Note You can configure this parameter only when you select Schema Synchronization for Synchronization Types. If you select Yes, the precheck may take longer. You can view the Assessment Result during the precheck phase. The evaluation result does not affect the precheck result.
      Synchronization Topology	Select One-way Synchronization.
      Processing Mode of Conflicting Tables	Precheck and Report Errors: Checks whether a table with the same name exists in the destination database. If no table with the same name exists, this check item passes. If a table with the same name exists, an error is reported during the precheck, and the data synchronization task does not start. Note If you cannot delete or rename the table with the same name in the destination database, you can change the name of the table in the destination database. For more information, see Map table and column names. Ignore Errors and Proceed: Skips the check for tables with the same name in the destination database. Warning If you select Ignore Errors and Proceed, data inconsistency may occur, which may pose risks to your business. For example: If the table schemas are consistent and a record in the destination database has the same primary key value as a record in the source database: During full synchronization, DTS retains the record in the destination cluster. The record from the source database is not migrated. During incremental synchronization, DTS does not retain the record in the destination database. The record from the source database overwrites the record in the destination database. If the table schemas are inconsistent, only data from some columns may be synchronized, or the task may fail. Proceed with caution.
      Whether to migrate Event	Select whether to migrate events from the source database based on your business requirements. If you select Yes, you must also follow the relevant requirements and perform subsequent operations. For more information, see Synchronize or migrate events.
      Capitalization of Object Names in Destination Instance	You can configure the case sensitivity policy for the names of synchronized objects, including databases, tables, and columns, in the destination instance. By default, DTS default policy is selected. You can also choose to align the policy with the default policy of the source or destination database. For more information, see Case Sensitivity for Destination Object Names.
      Source Objects	In the Source Objects box, select the objects to be synchronized and click to move them to the Selected Objects box. Note You can select objects to synchronize at the database, table, or column level. If you select only tables or columns, other object types such as views, triggers, and stored procedures are not synchronized to the destination database.
      Selected Objects	To change the name of an individual object in the destination instance, right-click the object in the Selected Objects box. For instructions on setting the name, see Map table and column names. To batch edit the names of multiple objects in the destination instance, click Batch Edit in the upper-right corner of the Selected Objects box. For instructions on setting the names, see Map table and column names. Note To select the SQL operations to synchronize for a specific database or table, right-click the 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 in the Selected Objects box and set the filter condition in the dialog box that appears. For more information, see Set a filter condition.

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

      This example uses the default settings. You can customize them as needed.

      Parameter description

      Parameter	Description
      Dedicated Cluster for Task Scheduling	By default, DTS schedules tasks on a shared cluster, so no action is required. If you require higher task stability, 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 you use Data Management Service (DMS) or gh-ost to perform online DDL changes on the source database, you can select whether to synchronize the temporary table data generated by these changes. Important DTS tasks do not support online DDL changes made by tools such as pt-online-schema-change. Using such tools will cause the DTS task to fail. Yes: Synchronizes the temporary table data generated by online DDL changes. Note If the temporary table data generated by online DDL changes is excessively large, synchronization latency may occur. No, Adapt to DMS Online DDL: Does not synchronize the temporary table data generated by online DDL changes. DTS synchronizes only the original DDL statements that Data Management Service (DMS) executes on the source database. Note This option causes table locks on the destination database. No, Adapt to gh-ost: Does not synchronize the temporary table data generated by online DDL changes. DTS synchronizes only the original DDL statements that gh-ost executes on the source database. You can use the default regular expressions for gh-ost shadow tables and garbage tables, or configure custom regular expressions. Note This option causes table locks on the destination database.
      Whether to Migrate Accounts	Select whether to synchronize account information from the source database. If you select Yes, you must also select the accounts to synchronize and confirm their permissions. For more information about authorization, see Migrate database accounts.
      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 retries the connection. The default retry duration is 720 minutes. You can specify a custom duration from 10 to 1,440 minutes. We recommend that you set the duration to at least 30 minutes. If DTS reconnects to the database within the specified retry duration, the synchronization task automatically resumes. Otherwise, the task fails. Note If multiple DTS instances share the same source or destination database, for example, DTS instance A and DTS instance B, and you set the network retry duration to 30 minutes for A and 60 minutes for B, the shorter duration of 30 minutes applies to both. DTS instances are billed based on their runtime, including the connection retry period. To avoid unnecessary charges, specify a custom retry duration or release the DTS instance when you release the database instances.
      Retry Time for Other Issues	If a non-connection issue, such as a DDL or DML execution error, occurs in the source or destination database after the task starts, DTS reports an error and immediately retries the operation. The default retry duration is 10 minutes. You can specify a custom duration from 1 to 1,440 minutes. We recommend that you set the duration to at least 10 minutes. 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 Migration	You can limit the full synchronization rate to reduce the load on the source and destination databases. You can set Queries per Second (QPS) on the Source Database, RPS of Full Data Migration, and Full Data Migration Speed (MB/s). Note You can configure this parameter only when you select Full Data Synchronization for Synchronization Types.
      Enable Throttling for Incremental Data Synchronization	You can also limit the incremental synchronization rate to reduce the load on the destination database. You can set RPS of Incremental Data Synchronization and Data synchronization speed for incremental synchronization (MB/s).
      Whether to delete SQL operations on heartbeat tables of forward and reverse tasks	Select whether to write heartbeat SQL statements to the source database while the DTS instance is running. Yes: Does not write heartbeat SQL statements to the source database. The DTS instance may show a delay. No: Writes heartbeat SQL statements to the source database. This may affect features such as physical backup and cloning of the source database.
      Environment Tag	You can select an environment tag to identify the instance based on your business requirements.
      Scene Label	You can select a scenario tag to identify the use case of the instance.
      Configure ETL	Select whether to configure the extract, transform, and load (ETL) feature. For more information about ETL, see What is ETL?. Yes: Configures the ETL feature. You must enter the data processing statements in the text box. For more information, see Configure ETL in a DTS migration or synchronization task. No: Does not configure the ETL feature.
      Sensitive Data Scanning and Masking	Enable this feature to scan for and process sensitive information in the transmitted data.

   3. Click Next: Data Validation to configure a data validation task.

      To use the data validation feature, select one or more data validation methods as needed. You can retain the default values for other parameters or customize them based on your business requirements. For instructions on how to configure the parameters, see Configure data validation.

      Note

      If you select schema validation, the validation process may report a "data inconsistency" error due to historical compatibility issues, even if the schema has already been synchronized. This is a non-critical alert and does not affect the actual synchronization result. To avoid false positives, we recommend that you do not enable schema validation during data validation.

      Parameter description

      Parameter	Description
      Full Data Verification	Full field validation by row sampling: Configures the sampling percentage for full-field validation on the sampled data. Valid values: integers from 10 to 100. Verify based on the number of table rows: Validates the number of rows in the full data task. The data content is not validated. Note The Verify based on the number of table rows mode is free of charge. The Full field validation by row sampling mode is charged based on the amount of data that is validated.
      Full Data Verification Time Rule	Only Start Now is supported.
      Timeout Settings for Full Data Verification	No: The full validation task is not forcibly ended if it times out. Settings: Specifies a timeout period for the full validation task. The timer starts after the full validation task begins. If the validation task is not completed within the specified period, it is forcibly ended. Valid values: integers from 1 to 72.
      Full calibration reference	Default: Uses the union of the source and destination databases as the baseline to validate data consistency. Source Database: Uses the source database as the baseline to validate its data consistency against the destination database. Data that exists only in the destination database is not validated. Destination Database: Uses the destination database as the baseline to validate its data consistency against the source database. Data that exists only in the source database is not validated.
      Maximum number of rows of data read per second by full verification (RPS)	Full data validation consumes read resources from the database. You can limit the rate of the full validation task by specifying the number of rows read per second and the volume of data read per second to reduce the pressure 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 read BPS for full validation (bytes/s) are set to 0, no rate limit is applied.
      Maximum read BPS for full validation (bytes/s)
      Incremental Verification Benchmark	You can filter the DML operations to be validated based on your business requirements.
      Validation objects	In the Selected Objects box, select the objects that you do not want to validate and click to remove them. Note By default, DTS moves the objects that you specify to synchronize or migrate to the Selected Objects box.
      Full Data Verification Alert	No: No alert is configured. Settings: An alert is configured. You must also select and configure alert rules. The alert rules are as follows: Trigger an alert when the full validation task fails. Set a threshold for data inconsistency. An alert is triggered when the number of inconsistent data records in the full validation task is greater than or equal to the threshold.
      Incremental Data Verification Alert	No: No alert is configured. Settings: An alert is configured. You must also select and configure alert rules. The alert rules are as follows: Trigger an alert when the incremental validation task fails. Specify the number of check cycles, the duration of each cycle, and an inconsistency threshold. An alert is triggered if the number of inconsistent data records is greater than or equal to the threshold for the specified number of consecutive cycles. Specify the number of check cycles, the duration of each cycle, and a latency threshold. An alert is triggered if the latency of the incremental validation task is greater than or equal to the threshold for the specified number of consecutive cycles.

   4. Click Next: Save Task Settings and Precheck to precheck the synchronization task.

7. When the Success Rate reaches 100%, click Next: Purchase Instance.

   Note

   • A synchronization job must pass a precheck before it can start.

   • If the precheck fails, click the icon next to the failed check item for details.

     • Fix the issue as prompted, then rerun the precheck.

     • Alternatively, select Ignore or Ignore Alert Items and Rerun Precheck.

8. On the buy page, select a billing method and link specification for the data synchronization instance. The following table describes each parameter.

   Category	Parameter	Description
   New Instance Class	Billing method	Subscription: You pay when you create the instance. This billing method is ideal for long-term use and is more cost-effective than Pay-As-You-Go. Pay-As-You-Go: You are billed by the hour. This billing method is ideal for short-term use. To save costs, you can release the instance when it is no longer needed.
   	Resource group	The resource group that contains the instance. The default value is default resource group. For more information, see What is Resource Management?.
   	Link specification	The link specification affects the synchronization speed. Select a specification with a performance level that meets your business needs. For more information, see Specifications of data synchronization links.
   	Subscription duration	If you select the Subscription billing method, specify the instance quantity and subscription duration. You can subscribe for 1 to 9 months, or for 1, 2, 3, or 5 years. Note This parameter is only available for the Subscription billing method.

9. Once the configuration is complete, select the Data Transmission Service (Pay-as-you-go) Service Terms, click Buy and Start, and in the OK dialog box, click Yes.

10. After completing the purchase, you will be returned to the Data Synchronization list page. Click the ID of the task you just created to open its details page. In the left navigation pane, click Task Management to view the progress. The screen below indicates that the historical data migration is complete and incremental data is being synchronized in real time.

Fix synchronization failures from field type changes

If you change the field type of a source table during data synchronization, the synchronization job reports an error and stops. To resolve this issue, follow these steps.

1. In the destination instance, create a new table B (for example, customer_new) based on the schema of table A (for example, customer), which failed to synchronize. Adjust the field types of table B as needed.

2. Run the INSERT INTO SELECT command to copy the data from table A to the new table B. Then, verify that the data in both tables is consistent.

3. Rename or delete the original table A, and then rename table B to customer.

4. In the DTS console, restart the data synchronization job.

Related topics

• DuckDB-based analytical primary instances

• Create and connect to a DuckDB-based analytical primary instance

• Change the configuration of a DuckDB-based analytical primary instance

• Add a DuckDB-based analytical read-only instance to an RDS MySQL High-availability Edition