PolarDB:Migrate data between PolarDB for MySQL clusters

Foreign key behavior

• During schema migration, DTS migrates foreign keys from the source to the destination database.

• During full and incremental data migration, DTS temporarily disables foreign key constraint checks and cascade operations at the session level. If you run cascade update or delete operations on the source during migration, data inconsistency may occur.

Source database requirements

• Bandwidth: The source database server must have enough outbound bandwidth. Insufficient bandwidth reduces migration speed.

• Primary keys: Tables being migrated must have PRIMARY KEY or UNIQUE constraints with all unique fields. Tables without these constraints may result in duplicate records on the destination.

• Table count limit: If you select tables as migration objects and need to rename tables or columns in the destination, a single task supports up to 1,000 tables. If you run a task to migrate more than 1,000 tables, a request error occurs. In this case, either split the work across multiple tasks or migrate the entire database.

• Binary logging for incremental migration: Binary logging must be enabled and the loose_polar_log_bin parameter must be set to on. Without this, the precheck fails and the task cannot start. See Enable binary logging and Modify parameters. Binary log retention requirements: After full migration completes, you can set the retention period to more than 24 hours. Make sure that you set the retention period of binary logs in accordance with the preceding requirements. Otherwise, the Service Level Agreement (SLA) of DTS does not guarantee service reliability or performance. Enabling binary logging incurs storage charges.

  • Incremental migration only: retain binary logs for more than 24 hours.

  • Full + incremental migration: retain binary logs for at least 7 days.

• Schema and DDL changes: Do not run DDL statements that modify database or table schemas during schema migration or full data migration. This causes the task to fail.

• Writes during full-only migration: If you run only full data migration (without incremental), do not write to the source database during migration. Writes cause data inconsistency between source and destination.

Other limits

• Cluster versions: Source and destination clusters must be the same version.

• Read-only nodes: DTS does not migrate read-only nodes from the source cluster.

• OSS external tables: DTS does not migrate Object Storage Service (OSS) external tables from the source cluster.

• Two-way synchronization: You cannot migrate data from a cluster that has DTS two-way synchronization configured.

• FLOAT and DOUBLE precision: DTS uses the ROUND(COLUMN,PRECISION) function to retrieve values from FLOAT and DOUBLE columns. Default precision: 38 digits for FLOAT, 308 digits for DOUBLE. Verify these settings meet your requirements before starting the migration.

• Task auto-resume: DTS tries to resume failed tasks for up to 7 days. Before switching workloads to the destination cluster, stop or release the migration task, or run the revoke command to remove write permissions from the DTS accounts on the destination cluster. Otherwise, a resumed task may overwrite destination data with source data.

• Failed DDL statements: If a DDL statement fails to execute on the destination, the task continues running. View failed DDL statements in the task logs. See View task logs.

• DTS support intervention: If a DTS task fails, DTS technical support attempts to restore it within 8 hours. The task may be restarted and task-level parameters may be modified. Database parameters are not modified. For the parameters that may be modified, see the Modify instance parameters section of the Modify the parameters of a DTS instance topic.

• Performance impact: Full data migration uses read and write resources on both source and destination databases, which increases load. Run migrations during off-peak hours.

Special cases

DTS executes the CREATE DATABASE IF NOT EXISTS 'test' statement on the source database periodically to advance the binary log file position.

Step 1: Go to the Data Migration Tasks page

1. Log on to the Data Management (DMS) console.Data Migration page of the new DTS console

2. In the top navigation bar, hover over DTS and choose DTS (DTS) > Data Migration.

   Note

   Depending on the DMS console mode and layout, the steps may differ. See Simple mode and Customize the layout and style of the DMS console. Alternatively, go directly to the Data Migration page of the new DTS console.

3. From the drop-down list on the right side of Data Migration Tasks, select the region where your migration instance resides.

   Note

   In the new DTS console, select the region in the upper-left corner.

Step 2: Create a task

Click Create Task.

Step 3: Switch to the new configuration page (optional)

In the upper-right corner, click New Configuration Page if it is available.

Step 4: Configure the source and destination databases

Configure the parameters as described in the following table.

Task information

Source Database

Destination Database

Step 5: Test connectivity

Click Test Connectivity and Proceed.

DTS automatically adds its server CIDR blocks to the IP address whitelist of Alibaba Cloud database instances. For self-managed databases on Elastic Compute Service (ECS), DTS adds its CIDR blocks to the ECS security group. If the self-managed database spans multiple ECS instances, manually add the CIDR blocks to each instance's security group. For databases hosted in a data center or on a third-party cloud, manually add the DTS server CIDR blocks to the database whitelist. See CIDR blocks of DTS servers.

Step 6: Configure migration objects

On the Configure Objects page, set the following parameters.

Migration types and objects

Parameter	Description
Migration Types	Select the migration types for your scenario. See the Choose a migration type section.
Method to Migrate Triggers in Source Database	The method for migrating triggers. Configure based on your requirements. Only available when Migration Types includes Schema Migration. See Synchronize or migrate triggers from the source database.
Processing Mode of Conflicting Tables	How DTS handles tables in the destination that share names with source tables: Precheck and Report Errors — the precheck fails if conflicting table names exist. To proceed, rename the conflicting tables using the object name mapping feature. See Map object names. Ignore Errors and Proceed — skips the precheck. If schemas match, full migration skips conflicting records; incremental migration overwrites them. If schemas differ, only specific columns are migrated or the task fails. Use with caution.
Source Objects	Select objects from the Source Objects list and click to add them to Selected Objects. You can select columns, tables, or schemas. Selecting tables or columns excludes views, triggers, and stored procedures.
Selected Objects	Right-click an object to rename it (single object) or click Batch Edit to rename multiple objects. Object name mapping may cause dependent objects to fail migration. Right-click a table to add WHERE filter conditions (see Specify filter conditions) or to select specific SQL operations to migrate.

Advanced settings

Click Next: Advanced Settings to configure the following parameters.

Data verification

Click Next Step: Data Verification to configure data verification. See Configure a data verification task.

Step 7: Run the precheck

Click Next: Save Task Settings and Precheck.

To preview the API parameters for this task, hover over the button and click Preview OpenAPI parameters before clicking through.

The task cannot start until it passes the precheck.

• If a precheck item fails, click View Details next to the item, fix the issue, and click Precheck Again.

• If a precheck item triggers an alert:

  • If the alert cannot be ignored, fix the issue and rerun the precheck.

  • If the alert can be ignored, click Confirm Alert Details, then click Ignore > OK > Precheck Again. Ignoring alerts may result in data inconsistency.

Step 8: Purchase a DTS instance

Wait until Success Rate reaches 100%, then click Next: Purchase Instance.

On the Purchase Instance page, configure the following parameters:

Read and accept the Data Transmission Service (Pay-as-you-go) Service Terms, then click Buy and Start > OK.

Track migration progress on the Data Migration page.