Use Data Transmission Service (DTS) to synchronize data from a PolarDB for MySQL cluster to a PolarDB-X 2.0 instance. DTS supports schema synchronization, full data synchronization, and incremental data synchronization, so you can migrate data while keeping the source database online.
Prerequisites
Before you begin, make sure that:
A destination PolarDB-X 2.0 instance is created
The available storage space of the PolarDB-X 2.0 instance is larger than the total data size in the source PolarDB for MySQL cluster
Binary logging is enabled on the source PolarDB for MySQL cluster (see Binary log requirements below)
Billing
| Synchronization type | Fee |
|---|---|
| Schema synchronization and full data synchronization | Free |
| Incremental data synchronization | Charged. For details, see Billing overview. |
Supported synchronization topologies
One-way one-to-one synchronization
One-way many-to-one synchronization
SQL operations that can be synchronized
| Operation type | Supported statements |
|---|---|
| DML | INSERT, UPDATE, DELETE |
| DDL | ALTER TABLE, ALTER VIEW, CREATE FUNCTION, CREATE INDEX, CREATE PROCEDURE, CREATE TABLE, CREATE VIEW, DROP INDEX, DROP TABLE, RENAME TABLE, TRUNCATE TABLE |
Limitations
Source database requirements
Tables to be synchronized must have a PRIMARY KEY or UNIQUE constraint, with all fields unique. Otherwise, the destination database may contain duplicate records.
If you select individual tables as the synchronization objects and need to rename tables or columns in the destination database, a single task supports up to 1,000 tables. Tasks that exceed this limit return a request error. To work around this, split the work into multiple tasks or synchronize the entire database instead of individual tables.
Binary log requirements
Binary logging must be correctly configured on the source PolarDB for MySQL cluster before the task starts.
| Requirement | Setting | Why |
|---|---|---|
| Enable binary logging | Set the loose_polar_log_bin parameter to ON. | If not enabled, the precheck returns errors and the DTS task cannot start. For setup instructions, see Enable binary logging and Modify parameters. |
| Log retention period | Set to at least 3 days; 7 days recommended. | A retention period that is too short may cause data inconsistency or loss and is not covered by the DTS SLA. For instructions, see Modify the retention period. |
Enabling binary logging incurs storage charges for the log files.
Restrictions by synchronization phase
| Phase | Restrictions |
|---|---|
| Schema synchronization and full data synchronization | Do not run DDL statements that change database or table schemas. The task fails if DDL statements are executed during this phase. |
| Full data synchronization and incremental data synchronization | DTS temporarily disables foreign key constraint checks and cascade operations at the session level during these phases. Cascade update and delete operations on the source may cause data inconsistency. |
Objects and data types not supported
Data types not synchronized: BIT, VARBIT, GEOMETRY, ARRAY, UUID, TSQUERY, TSVECTOR, TXID_SNAPSHOT
Prefix indexes cannot be synchronized. If the source contains prefix indexes, the task may fail.
Read-only nodes of the source PolarDB for MySQL cluster are not synchronized.
Object Storage Service (OSS) external tables from the source PolarDB for MySQL cluster are not synchronized.
Usage notes
Schema synchronization behavior: During schema synchronization, DTS synchronizes foreign keys from the source database to the destination database.
Performance impact: Initial full data synchronization uses read and write resources on both databases and may increase server load. Run synchronization tasks during off-peak hours.
Tablespace size after full sync: Concurrent INSERT operations during initial full data synchronization cause table fragmentation in the destination. After full synchronization completes, the destination tablespace may be larger than the source.
DDL tools on source tables: Do not use tools such as pt-online-schema-change to perform DDL operations on source tables during synchronization. The task will fail. If no other sources are writing to the destination, you can use Data Management (DMS) to perform lock-free DDL operations on source tables.
Writing to the destination: Use only DTS to write data to the destination during synchronization. Writing from other sources may cause data inconsistency, and data loss may occur when DMS performs online DDL operations.
Task failure recovery: If a DTS task fails, DTS technical support attempts to restore it within 8 hours. The task may be restarted and task parameters (not database parameters) may be modified during recovery. For the parameters that may be modified, see Modify instance parameters.
Heartbeat table: DTS periodically runs
CREATE DATABASE IF NOT EXISTS \test\`` on the source database to advance the binary log position.
Create a synchronization task
Step 1: Go to the Data Synchronization page
Use one of the following methods:
DTS console
Log on to the DTS console.
In the left-side navigation pane, click Data Synchronization.
In the upper-left corner, select the region where the synchronization instance resides.
DMS console
The exact steps may vary depending on your DMS console mode and layout. For details, 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 synchronization instance resides.
Step 2: Configure source and destination databases
Click Create Task.
Configure the source and destination databases using the following parameters.
WarningAfter configuring the source and destination databases, review the Limits displayed on the page. Skipping this step may cause the task to fail or result in data inconsistency.
Section Parameter Description N/A Task Name A name for the DTS task. DTS auto-generates a name. Specify a descriptive name to make the task easy to identify. The name does not need to be unique. Source Database Select Existing Connection If you have a database instance registered with DTS, select it from the drop-down list—DTS automatically populates the remaining parameters. For details, see Manage database connections. Otherwise, configure the parameters below manually. In the DMS console, select from the Select a DMS database instance list. Database Type Select PolarDB for MySQL. Access Method Select Alibaba Cloud Instance. Instance Region The region where the source PolarDB for MySQL cluster resides. PolarDB Cluster ID The ID of the source PolarDB for MySQL cluster. Database Account The database account for the source cluster. The account must have read permissions on the objects to be synchronized. Database Password The password for the database account. Encryption Whether to encrypt the connection to the source cluster using SSL. For details, see Configure SSL encryption. Destination Database Select Existing Connection If you have a database instance registered with DTS, select it from the drop-down list—DTS automatically populates the remaining parameters. For details, see Manage database connections. Otherwise, configure the parameters below manually. In the DMS console, select from the Select a DMS database instance list. Database Type Select PolarDB-X 2.0. Access Method Select Alibaba Cloud Instance. Instance Region The region where the destination PolarDB-X 2.0 instance resides. Instance ID The ID of the destination PolarDB-X 2.0 instance. Database Account The database account for the destination instance. The account must have read and write permissions on the destination database. Database Password The password for the database account. Click Test Connectivity and Proceed.
Make sure that DTS server CIDR blocks are added to the security settings of both databases, either automatically or manually. For details, see Add the CIDR blocks of DTS servers.
Step 3: Configure synchronization objects
In the Configure Objects step, set the following parameters:
Parameter Description Synchronization Types By default, Incremental Data Synchronization is selected. You must also select Schema Synchronization and Full Data Synchronization. After the precheck is complete, DTS synchronizes the historical data of the selected objects from the source database to the destination cluster. The historical data is the basis for subsequent incremental synchronization. Processing Mode of Conflicting Tables Precheck and Report Errors: checks whether the destination has tables with the same names as the source. If identical names exist, the precheck fails and the task cannot start. To resolve naming conflicts, use the object name mapping feature to rename tables in the destination. Ignore Errors and Proceed: skips the name-conflict check. If schemas match and a record in the destination has the same primary key or unique key as the source: during full data synchronization, the destination record is kept; during incremental data synchronization, the destination record is overwritten. If schemas differ, initialization may fail or only some columns may be synchronized. Use this option with caution. Source Objects Select objects from the Source Objects section and click the right-arrow icon to move them to Selected Objects. You can select columns, tables, or databases. Selecting tables or columns excludes other objects such as views, triggers, and stored procedures. Selected Objects To rename a single object in the destination, right-click it in Selected Objects. For details, see Map the name of a single object. To rename multiple objects at once, click Batch Edit in the upper-right corner. For details, see Map multiple object names at a time. To filter which SQL operations are synchronized for a specific object, right-click it and select the operations. To filter data by row, right-click the object and specify WHERE conditions. For details, see Specify filter conditions. Click Next: Advanced Settings and configure the following:
Parameter Description Dedicated Cluster for Task Scheduling By default, DTS uses the shared cluster. For improved stability, purchase a dedicated cluster. For details, see What is a DTS dedicated cluster. Retry Time for Failed Connections How long DTS retries if the source or destination database becomes unreachable after the task starts. Valid values: 10–1440 minutes. Default: 720 minutes. Set to a value greater than 30. If different tasks share the same source or destination database, the shortest retry time applies across all of them. While DTS retries, you are still charged for the instance. We recommend that you release the DTS instance at your earliest opportunity after the source and destination instances are released. Retry Time for Other Issues How long DTS retries if DDL or DML operations fail after the task starts. Valid values: 1–1440 minutes. Default: 10 minutes. Set to a value greater than 10. This value must be less than Retry Time for Failed Connections. Enable Throttling for Full Data Synchronization Throttles DTS resource usage during full data synchronization to reduce load on source and destination servers. Configure Queries per second (QPS) to the source database, RPS of Full Data Migration, and Data migration speed for full migration (MB/s). Available only when Full Data Synchronization is selected. Enable Throttling for Incremental Data Synchronization Throttles DTS resource usage during incremental synchronization. Configure RPS of Incremental Data Synchronization and Data synchronization speed for incremental synchronization (MB/s). Environment Tag The environment tag that is used to identify the DTS instance. You can select an environment tag based on your business requirements. Whether to delete SQL operations on heartbeat tables of forward and reverse tasks Whether DTS writes heartbeat table operations to the source database. Yes: DTS does not write heartbeat operations, but the instance may show a latency. No: DTS writes heartbeat operations, which may affect physical backup and cloning of the source database. Configure ETL Whether to enable extract, transform, and load (ETL). Yes: configure data processing statements in the code editor. For details, see Configure ETL in a data migration or data synchronization task. No: skip ETL. For an overview, see What is ETL? Monitoring and Alerting Whether to configure alerts. Yes: set an alert threshold and notification contacts—alerts fire when the task fails or synchronization latency exceeds the threshold. For details, see Configure monitoring and alerting when you create a DTS task. No: no alerts. Click Next Step: Data Verification to configure data verification. For details, see Configure a data verification task.
Step 4: Run the precheck
To preview the API parameters for this task, hover over Next: Save Task Settings and Precheck and click Preview OpenAPI parameters.
Click Next: Save Task Settings and Precheck.
DTS runs a precheck before starting the task. The task can start only after passing the precheck.
If an item fails, click View Details, troubleshoot based on the results, and rerun the precheck.
If an alert fires for an item that can be ignored: click Confirm Alert Details, then click Ignore in the dialog box, click OK, and then click Precheck Again. Ignoring alerts may cause data inconsistency.
If an alert fires for an item that cannot be ignored, resolve the issue before proceeding.
Step 5: Purchase an instance
Wait until the Success Rate reaches 100%, then click Next: Purchase Instance.
On the buy page, configure the following parameters:
Section Parameter Description New Instance Class Billing Method Subscription: pay upfront for a fixed duration—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 stop charges. Resource Group Settings The resource group for the synchronization instance. Default: default resource group. For details, see What is Resource Management? Instance Class The synchronization throughput class. Select based on your data volume and latency requirements. For details, see Instance classes of data synchronization instances. Subscription Duration The subscription term (1–9 months, or 1, 2, 3, or 5 years). Available only for the Subscription billing method. Read and accept the Data Transmission Service (Pay-as-you-go) Service Terms.
Click Buy and Start, then click OK in the confirmation dialog.
After the task starts, you can monitor its progress in the task list.