Data Transmission Service (DTS) synchronizes data from a MariaDB database to an ApsaraDB RDS for MySQL instance, including schema synchronization, full data synchronization, and incremental data synchronization. This topic uses an ApsaraDB RDS for MariaDB TX instance as the source database.
Prerequisites
Before you begin, make sure that you have:
An ApsaraDB RDS for MySQL instance as the destination. For more information, see Create an ApsaraDB RDS for MySQL instance
Enough storage on the destination instance to hold all data from the source instance
Billing
| Synchronization type | Fee |
|---|---|
| Schema synchronization and full data synchronization | Free of charge |
| Incremental data synchronization | Charged. For more information, see Billing overview. |
Permissions required for database accounts
| Database | Required permission | References |
|---|---|---|
| Source ApsaraDB RDS for MariaDB TX instance | Read and write permissions on the objects to be synchronized | Create an account and Modify or reset account permissions |
| Destination ApsaraDB RDS for MySQL instance | Read and write permissions on the destination database | Create an account and Modify account permissions |
Supported SQL operations for incremental synchronization
| Operation type | SQL statement |
|---|---|
| DML | INSERT, UPDATE, and DELETE |
| DDL | ALTER TABLE, ALTER VIEW, CREATE FUNCTION, CREATE INDEX, CREATE PROCEDURE, CREATE TABLE, CREATE VIEW, DROP INDEX, DROP TABLE, RENAME TABLE, and TRUNCATE TABLE |
RENAME TABLE may cause data inconsistency between the source and destination databases. If you select a table as the object to be synchronized and rename the table during synchronization, the data of this table is not synchronized to the destination database. To avoid this, select the database to which this table belongs as the object to be synchronized. Make sure that the databases to which the table belongs before and after the RENAME TABLE operation are both added to the objects to be synchronized.
Limitations
Source database requirements
Tables to be synchronized must have PRIMARY KEY or UNIQUE constraints, and all fields must be unique. Otherwise, the destination database may contain duplicate data records.
If you select tables as the objects to be synchronized and need to edit them in the destination database (such as renaming tables or columns), a single synchronization task supports up to 1,000 tables. If you need more than 1,000 tables, configure multiple tasks to synchronize the tables in batches, or synchronize the entire database.
Binary logging is enabled by default for ApsaraDB RDS for MariaDB instances. Make sure that the
binlog_row_imageparameter is set tofull. Otherwise, the precheck fails and the synchronization task cannot start.Binary log retention requirements: If DTS cannot obtain the binary logs, the task may fail, or data inconsistency and data loss may occur. Adhere to these retention requirements; otherwise, the DTS service level agreement (SLA) does not guarantee service reliability or performance.
Incremental-only synchronization: at least 24 hours
Full and incremental synchronization: at least 7 days. After full data synchronization is complete, set the retention period to more than 24 hours.
Foreign key behavior
During schema synchronization, DTS synchronizes foreign keys from the source database to the destination database.
During full data synchronization and incremental data synchronization, DTS temporarily disables constraint checks and cascade operations on foreign keys at the session level.
Cascade update and delete operations on the source database during synchronization may cause data inconsistency.
Runtime restrictions
Performance impact: Full data synchronization uses read and write resources of both the source and destination databases. This may increase the load on database servers. Schedule synchronization during off-peak hours.
Table fragmentation: During initial full data synchronization, concurrent INSERT operations cause fragmentation in the destination tables. After the full synchronization completes, the used tablespace of the destination database is larger than that of the source database.
DDL operations on selected tables: If you select specific tables (not an entire database) as the objects to be synchronized, do not use tools such as pt-online-schema-change for online DDL operations on those tables during synchronization. Use Data Management (DMS) to perform lock-free DDL operations instead. For more information, see Perform lock-free DDL operations.
Writes to the destination database: Use only DTS to write data to the destination database during synchronization. This prevents data inconsistency between the source and destination databases. If other tools write to the destination database, do not use DMS for online DDL operations, because data loss may occur.
Failed DDL in the destination: If DDL statements fail in the destination database, the DTS task continues to run. Check the task logs to view the failed DDL statements. For more information, see View task logs.
Case-insensitive column names: MySQL column names are not case-sensitive. If multiple columns in the source database have names that differ only in capitalization, data from these columns is written to the same column in the destination MySQL database, which can cause unexpected results.
Post-synchronization verification: After the synchronization completes (the Status changes to Completed), run
analyze table <Table name>to check whether data was written to the destination table. If a high-availability (HA) switchover is triggered in the source MySQL database, data may be written only to memory, causing data loss.
Procedure
Step 1: Open the data synchronization page
Use one of the following methods to access the Data Synchronization page and select the region where the data synchronization instance resides.
DTS console
Log on to the DTS console.
In the left-side navigation pane, click Data Synchronization.
In the upper-left corner of the page, select the region where the data synchronization instance resides.
DMS console
The actual operations may vary based 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.
Log on to the DMS console.
In the top navigation bar, move the pointer over Data + AI and choose DTS (DTS) > Data Synchronization.
From the drop-down list to the right of Data Synchronization Tasks, select the region where the data synchronization instance resides.
Step 2: Configure the source and destination databases
Click Create Task.
Configure the source and destination databases with the following parameters.
Task settings
| Parameter | Description |
|---|---|
| Task Name | DTS automatically generates a task name. Specify a descriptive name to make the task easy to identify. The task name does not need to be unique. |
Source database
| Parameter | Description |
|---|---|
| Select Existing Connection | Optional. If you select an existing database, DTS automatically populates the parameters. If you have not registered a DMS database instance, skip this option and configure the settings below. |
| Database Type | Select MariaDB. |
| Access Method | Select Alibaba Cloud Instance. |
| Instance Region | The region where the source ApsaraDB RDS for MariaDB TX instance resides. |
| Replicate Data Across Alibaba Cloud Accounts | Whether to synchronize data across Alibaba Cloud accounts. In this example, select No. |
| Instance ID | The ID of the source ApsaraDB RDS for MariaDB TX instance. |
| Database Account | The database account of the source instance. For required permissions, see Permissions required for database accounts. |
| Database Password | The password for the database account. |
| Encryption | Whether to encrypt the connection to the source database. In this example, select Non-encrypted. |
Destination database
| Parameter | Description |
|---|---|
| Select Existing Connection | Optional. If you select an existing database, DTS automatically populates the parameters. If you have not registered a DMS database instance, skip this option and configure the settings below. |
| Database Type | Select MySQL. |
| Access Method | Select Alibaba Cloud Instance. |
| Instance Region | The region where the destination ApsaraDB RDS for MySQL instance resides. |
| Replicate Data Across Alibaba Cloud Accounts | Whether to synchronize data across Alibaba Cloud accounts. In this example, select No. |
| RDS Instance ID | The ID of the destination ApsaraDB RDS for MySQL instance. |
| Database Account | The database account of the destination instance. For required permissions, see Permissions required for database accounts. |
| Database Password | The password for the database account. |
| Encryption | Whether to encrypt the connection. Select Non-encrypted or SSL-encrypted based on your requirements. To use SSL-encrypted, enable SSL encryption for the ApsaraDB RDS for MySQL instance first. For more information, see Use a cloud certificate to enable SSL encryption. |
Click Test Connectivity and Proceed.
DTS automatically adds the CIDR blocks of DTS servers to the whitelist of Alibaba Cloud database instances (such as ApsaraDB RDS for MySQL or ApsaraDB for MongoDB instances). For self-managed databases hosted on Elastic Compute Service (ECS) instances, DTS automatically adds the CIDR blocks to the security group rules of the ECS instance. Make sure the ECS instance can access the database. If the database is deployed on multiple ECS instances, manually add the CIDR blocks of DTS servers to the security group rules of each ECS instance. For self-managed databases in a data center or provided by a third-party cloud service provider, manually add the CIDR blocks to the database whitelist. For more information, see CIDR blocks of DTS servers. Adding CIDR blocks of DTS servers to the whitelist, instance security group rules, or ECS security group rules introduces security risks. Before using DTS, take preventive measures, including but not limited to: strengthening username and password security, limiting exposed ports, authenticating API calls, regularly reviewing the whitelist or ECS security group rules and removing unauthorized CIDR blocks, or connecting the database to DTS through Express Connect, VPN Gateway, or Smart Access Gateway.
Step 3: Select objects and configure synchronization settings
Configure the objects to be synchronized and advanced settings.
| Parameter | Description |
|---|---|
| Synchronization Types | By default, Incremental Data Synchronization is selected. Also select Schema Synchronization and Full Data Synchronization. After the precheck completes, DTS synchronizes the historical data of the selected objects from the source database to the destination database. The historical data is the basis for subsequent incremental synchronization. |
| Method to Migrate Triggers in Source Database | The method to synchronize triggers from the source database. Select a method based on your requirements. If no triggers need to be synchronized, skip this parameter. For more information, see Synchronize or migrate triggers from the source database. This parameter is available only when Schema Synchronization is selected. |
| Processing Mode of Conflicting Tables | How to handle tables with identical names in the source and destination databases. Precheck and Report Errors: Checks for identical table names and returns an error if any are found. The task cannot start until the conflict is resolved. To resolve conflicts without deleting or renaming destination tables, use the object name mapping feature to rename tables synchronized to the destination database. For more information, see Map object names. Ignore Errors and Proceed: Skips the check for identical table names. If you select this option, be aware that data inconsistency may occur. During full synchronization, data records with the same primary key as existing records in the destination are skipped. During incremental synchronization, such records overwrite existing records. If schemas differ, data initialization may fail, only some columns may be synchronized, or the task may fail. |
| Source Objects | Select one or more objects from the Source Objects section and click the right-arrow icon to add them to the Selected Objects section. Select databases, tables, or columns as the objects to be synchronized. |
| Selected Objects | To rename an object in the destination instance, right-click it in the Selected Objects section. For more information, see Map the name of a single object. To rename multiple objects at a time, click Batch Edit in the upper-right corner of the Selected Objects section. For more information, see Map multiple object names at a time. To select specific SQL operations for a database or table, right-click the object in the Selected Objects section. For more information, see Supported SQL operations for incremental synchronization. To filter data with WHERE conditions, right-click a table in the Selected Objects section. For more information, see Specify filter conditions. If you rename an object using the object name mapping feature, other objects that depend on the renamed object may fail to synchronize. |
Step 4: Configure advanced settings
Click Next: Advanced Settings and configure the following parameters.
| Parameter | Description |
|---|---|
| Dedicated Cluster for Task Scheduling | By default, DTS schedules the task to the shared cluster. To improve stability, purchase a dedicated cluster. For more information, see What is a DTS dedicated cluster. |
| Monitoring and Alerting | Whether to configure alerting for the synchronization task. If the task fails or the synchronization latency exceeds the specified threshold, alert contacts receive notifications. Select No to disable alerting, or Yes to configure alert thresholds and notification settings. For more information, see Configure monitoring and alerting when you create a DTS task. |
| Retry Time for Failed Connections | The retry time range when the source or destination database fails to connect. Valid values: 10 to 1440 minutes. Default value: 720. Set this value to greater than 30. If DTS reconnects within the specified time range, the task resumes. Otherwise, the task fails. If multiple tasks share the same source or destination database but have different retry time ranges, the shortest range takes precedence. DTS charges for the instance during retry. Specify a retry time range based on your requirements and release the instance promptly after the source and destination instances are released. |
| Retry Time for Other Issues | The retry time range when DDL or DML operations fail. Valid values: 1 to 1440 minutes. Default value: 10. Set this value to greater than 10. If the failed operations succeed within the specified range, the task resumes. Otherwise, the task fails. This value must be smaller than Retry Time for Failed Connections. |
| Enable Throttling for Full Data Synchronization | Throttle full data synchronization to reduce the load on database servers. Configure the Queries per second (QPS) to the source database, RPS of Full Data Migration, and Data migration speed for full migration (MB/s) parameters. This parameter is available only when Full Data Synchronization is selected. |
| Enable Throttling for Incremental Data Synchronization | Whether to throttle incremental data synchronization. Configure the RPS of Incremental Data Synchronization and Data synchronization speed for incremental synchronization (MB/s) parameters to reduce the load on the destination database server. |
| Environment Tag | The environment tag to identify the DTS instance. Select a tag based on your requirements. |
| Configure ETL | Whether to enable the extract, transform, and load (ETL) feature. Select Yes to enter data processing statements in the code editor. For more information, see Configure ETL in a data migration or data synchronization task. Select No to skip ETL. For more information about ETL, see What is ETL? |
Step 5: Run the precheck and purchase the instance
Click Next: Save Task Settings and Precheck.
To view the parameters for the relevant API operation, hover over Next: Save Task Settings and Precheck and click Preview OpenAPI parameters.
Wait for the precheck to complete. DTS performs a precheck before the synchronization task can start.
If the precheck fails, click View Details next to each failed item. Analyze the causes based on the check results and fix the issues. Then run the precheck again.
If an alert is triggered for an item:
If the alert cannot be ignored, click View Details and troubleshoot the issue. Then run the precheck again.
If the alert can be ignored, click Confirm Alert Details. In the View Details dialog box, click Ignore, then click OK. Click Precheck Again to run the precheck again. Ignoring alerts may cause data inconsistency and potential risks.
Wait until the Success Rate reaches 100%. Then click Next: Purchase Instance.
On the purchase page, configure the following parameters.
New Instance Class
Parameter Description Billing Method Subscription: Pay upfront when creating the instance. More cost-effective for long-term use. Pay-as-you-go: Billed hourly. Suitable for short-term use. Release the instance when it is no longer needed to reduce costs. Pay-as-you-go Serverless: Billed based on actual usage with automatic scaling. Resource Group Settings The resource group to which the data synchronization instance belongs. Default value: default resource group. For more information, see What is Resource Management? Instance Class DTS provides instance classes with different synchronization speeds. Select an instance class based on your requirements. For more information, see Instance classes of data synchronization instances. Subscription Duration If you select the Subscription billing method, specify the subscription duration: 1 to 9 months, or 1, 2, 3, or 5 years. DTS Insight
Parameter Description DTS Insight Whether to enable DTS Insight for intelligent diagnostics. Select an option based on your requirements. Read and select Data Transmission Service (Pay-as-you-go) Service Terms.
Click Buy and Start to save the task and start it immediately, or click Buy and Save to save the task without starting it. In the dialog box that appears, click OK.
The task appears in the task list where you can monitor its progress.