Synchronize a self-managed MySQL database to an RDS for MySQL instance over a leased line, VPN Gateway, or Smart Access Gateway

Updated at:

Data Transmission Service (DTS) lets you run a continuous data synchronization task from a self-managed MySQL database—connected to Alibaba Cloud over Express Connect, VPN Gateway, or Smart Access Gateway—to an ApsaraDB RDS for MySQL instance.

Prerequisites

Before you begin, make sure that you have:

For supported MySQL versions, see Overview of data synchronization scenarios.

Billing

Synchronization typeTask configuration fee
Schema synchronization and full data synchronizationFree of charge
Incremental data synchronizationCharged. See Billing overview

SQL operations that can be synchronized

Operation typeSQL statements
DMLINSERT, UPDATE, DELETE
DDLALTER TABLE, ALTER VIEW; CREATE FUNCTION, CREATE INDEX, CREATE PROCEDURE, CREATE TABLE, CREATE VIEW; DROP INDEX, DROP TABLE; RENAME TABLE; TRUNCATE TABLE
Important

The RENAME TABLE statement can cause data inconsistency between the source and destination databases. If you select a table as the sync object and rename it during synchronization, that table's data stops syncing to the destination. To prevent this, add the database containing the table to the sync objects—include both the database before and after the rename.

Permissions required for database accounts

DatabaseSchema synchronizationFull data synchronizationIncremental data synchronization
Self-managed MySQLSELECTSELECTSELECT on objects to sync; REPLICATION CLIENT; REPLICATION SLAVE; SHOW VIEW; permissions to create databases and tables (DTS creates a dts database to record heartbeat data)
ApsaraDB RDS for MySQLRead and write permissionsRead and write permissionsRead and write permissions

For instructions on creating accounts and granting permissions:

To synchronize account information from the source database, additional permissions are required. See Migrate database accounts.

Limitations

Source database requirements

  • Tables to be synchronized must have PRIMARY KEY or UNIQUE constraints with all fields unique. Without this, the destination may contain duplicate records.

  • If you select tables (not an entire database) as sync objects and need to rename tables or columns, a single task supports up to 1,000 tables. For more than 1,000 tables, use multiple tasks or sync the entire database instead.

  • Binary logging must meet these requirements:

    • Enable binary logging and set binlog_format to row and binlog_row_image to full.

    • Retain binary logs for at least 7 days. If DTS cannot access the logs, the task fails and data inconsistency or loss may occur.

    • For a dual-primary cluster, set log_slave_updates to ON so DTS can read all binary logs.

  • Do not run DDL statements to change database or table schemas during schema synchronization or full data synchronization. The task will fail.

  • Data generated by cascade operations or physical backup restores is not synchronized to the destination while the task is running.

    If unsynchronized data affects your business, remove the affected databases and tables from the sync objects and then add them back. See Modify the objects to be synchronized.
  • MySQL 8.0.23 and later: if the data to be synchronized includes invisible columns, DTS cannot read those columns and data loss occurs.

    • To make a column visible: ALTER TABLE <table_name> ALTER COLUMN <column_name> SET VISIBLE;. See Invisible Columns.

    • Tables without primary keys automatically generate invisible primary keys—make them visible before syncing. See Generated Invisible Primary Keys.

Foreign key behavior

  • During schema synchronization, DTS synchronizes foreign keys to the destination.

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

Other limitations

  • If a primary/secondary switchover occurs on the source while the task is running, the task fails.

  • DTS calculates synchronization latency based on the timestamp of the latest synchronized record and the current source timestamp. If no DML operations run on the source for a long time, the latency reading may be inaccurate. Run a DML operation on the source to refresh the latency. If you sync an entire database, create a heartbeat table that receives updates every second.

  • DTS periodically runs CREATE DATABASE IF NOT EXISTS 'test' on the source to advance the binary log position.

  • The destination database version must be the same as or later than the source. Earlier destination versions can cause compatibility issues.

  • Run the synchronization during off-peak hours. Full data synchronization uses read and write resources on both databases, increasing server load.

  • Full data synchronization causes fragmentation in destination tables due to concurrent INSERT operations. After full synchronization, the destination tablespace is larger than the source.

  • If you select individual tables (not an entire database) as sync objects, do not use pt-online-schema-change for online DDL operations. Use Data Management (DMS) instead. See Perform lock-free DDL operations.

  • Do not write data from other sources to the destination during synchronization. Doing so can cause data inconsistency or loss, including when using DMS to run online DDL statements.

  • If a DDL statement fails in the destination, the DTS task continues running. View failed DDL statements in task logs. See View task logs.

  • MySQL column names are case-insensitive. If the source database has columns whose names differ only in capitalization, their data all writes to the same destination column, causing unexpected results.

  • After the task status changes to Completed, run ANALYZE TABLE <table_name> to verify that data was written to the destination table. An HA switchover on the source may result in data being written only to memory, causing data loss.

Create the synchronization task

Step 1: Open the Data Synchronization page

Use either the DTS console or the DMS console.

DTS console:

  1. Log on to the DTS console.

  2. In the left-side navigation pane, click Data Synchronization.

  3. In the upper-left corner, select the region where the synchronization instance will reside.

DMS console:

The exact steps may vary based on your DMS console mode and layout. See Simple mode and Customize the layout and style of the DMS console.
  1. Log on to the DMS console.

  2. In the top navigation bar, move the pointer over Data + AI and choose DTS (DTS) > Data Synchronization.

  3. From the drop-down list next to Data Synchronization Tasks, select the region where the synchronization instance will reside.

Step 2: Configure the source and destination databases

Click Create Task. On the Create Task page, configure the parameters below.

Click New Configuration Page or Back to Previous Version in the upper-right corner to switch between the new and previous configuration page versions. Some parameter names differ between versions.

Task name

ParameterDescription
Task NameDTS generates a name automatically. Specify a descriptive name to make the task easy to identify. The name does not need to be unique.

Source database

ParameterDescription
Select an existing DMS database instance(Optional) Select an existing registered DMS database instance. If selected, DTS auto-populates the parameters below.
Database TypeSelect MySQL.
Access MethodSelect Express Connect, VPN Gateway, or Smart Access Gateway.
Instance RegionSelect the region where the self-managed MySQL database resides.
Replicate Data Across Alibaba Cloud AccountsSelect No for same-account synchronization.
Connected VPCSelect the virtual private cloud (VPC) connected to the self-managed MySQL instance.
Domain Name or IPEnter the IP address or domain name of the self-managed MySQL server.
Port NumberEnter the service port of the source database. Default: 3306.
Database AccountEnter the source database account. See the permissions table in this topic.
Database PasswordEnter the password for the database account.
EncryptionSelect Non-encrypted or SSL-encrypted based on your requirements.

Destination database

ParameterDescription
Select an existing DMS database instance(Optional) Select an existing registered DMS database instance. If selected, DTS auto-populates the parameters below.
Database TypeSelect MySQL.
Access MethodSelect Alibaba Cloud Instance.
Instance RegionSelect the region where the destination ApsaraDB RDS for MySQL instance resides.
Replicate Data Across Alibaba Cloud AccountsSelect No for same-account synchronization.
RDS Instance IDSelect the destination ApsaraDB RDS for MySQL instance.
Database AccountEnter the destination database account. See the permissions table in this topic.
Database PasswordEnter the password for the database account.
EncryptionSelect Non-encrypted or SSL-encrypted. To use SSL encryption, enable SSL on the RDS instance first. See Use a cloud certificate to enable SSL encryption.

Step 3: Test connectivity and proceed

Click Test Connectivity and Proceed at the bottom of the page.

DTS automatically adds its CIDR blocks to the whitelist of Alibaba Cloud database instances and to security group rules of Elastic Compute Service (ECS) instances. For self-managed databases in data centers or on third-party clouds, add DTS CIDR blocks manually. See Add the CIDR blocks of DTS servers.

Warning

Adding DTS CIDR blocks to a database whitelist or security group introduces security exposure. Before proceeding, take preventive measures such as: using strong credentials, restricting exposed ports, validating API calls, auditing whitelist rules regularly, and removing unauthorized CIDR blocks.

Step 4: Configure sync objects and settings

ParameterDescription
Synchronization TypesBy default, Incremental Data Synchronization is selected. You must also select Schema Synchronization and Full Data Synchronization. DTS synchronizes historical data first, then uses it as the basis for incremental synchronization.
Method to Migrate Triggers in Source DatabaseSelect how to handle trigger synchronization. Only shown when Schema Synchronization is selected. See Synchronize or migrate triggers from the source database.
Enable Migration AssessmentChecks whether source and destination schemas meet migration requirements. Only shown when Schema Synchronization is selected. Assessment results don't affect the precheck.
Synchronization TopologySelect One-way Synchronization.
Processing Mode of Conflicting TablesPrecheck and Report Errors (default): the precheck fails if the source and destination have tables with identical names. Use object name mapping to rename destination tables if needed. See Map object names. Ignore Errors and Proceed: skips the identical-name check. During full synchronization, existing destination records with matching primary or unique keys are retained. During incremental synchronization, they are overwritten. If schemas differ, data initialization may partially fail.
Capitalization of Object Names in Destination InstanceControls capitalization of database, table, and column names in the destination. Default: DTS default policy. See Specify the capitalization of object names in the destination instance.
Source ObjectsSelect objects to sync and click the right-arrow icon to move them to Selected Objects. You can select databases, tables, or columns.
Selected ObjectsTo rename a single object, right-click it. To rename multiple objects, click Batch Edit. See Map object names. To filter by SQL operation type or add WHERE conditions, right-click a table and configure in the dialog box. See Specify filter conditions.

Step 5: Configure advanced settings

Click Next: Advanced Settings.

Data verification settings

See Configure a data verification task.

Advanced settings

ParameterDescription
Dedicated Cluster for Task SchedulingDTS uses the shared cluster by default. Purchase a dedicated cluster for higher task stability. 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 databaseControls how DTS handles temporary tables from online DDL tools (DMS or gh-ost). Yes: syncs temporary table data (may significantly extend sync time for large schemas). No, Adapt to DMS Online DDL: skips temporary table data, syncs only the original DDL from DMS (destination tables may be locked). No, Adapt to gh-ost: skips temporary table data, syncs only the original DDL from gh-ost; use default or custom regular expressions to filter shadow tables (destination tables may be locked). Do not use pt-online-schema-change—the DTS task will fail.
Whether to Migrate AccountsSelect Yes to sync source database account information and then select which accounts to sync.
Retry Time for Failed ConnectionsHow long DTS retries after a connection failure. Valid values: 10–1440 minutes. Default: 720 minutes. Set to at least 30 minutes. If multiple tasks share the same source or destination, the shortest retry time applies. Charges continue during retries.
Retry Time for Other IssuesHow long DTS retries after DDL or DML operation failures. Valid values: 1–1440 minutes. Default: 10 minutes. Set to at least 10 minutes. Must be less than the Retry Time for Failed Connections value.
Enable Throttling for Full Data MigrationLimits read QPS to the source, write RPS to the destination, and migration bandwidth (MB/s) during full data synchronization. Reduces database load. Only shown when Full Data Synchronization is selected.
Enable Throttling for Incremental Data SynchronizationLimits write RPS and bandwidth during incremental synchronization. Reduces destination database load.
Environment TagTags the DTS instance for environment identification (for example, production or staging).
Whether to delete SQL operations on heartbeat tables of forward and reverse tasksYes: DTS does not write heartbeat SQL to the source; a latency value is still displayed. No: DTS writes heartbeat SQL to the source; physical backup and cloning of the source may be affected.
Configure ETLYes: enables the extract, transform, and load (ETL) feature; enter data processing statements in the code editor. See Configure ETL in a data migration or data synchronization task. No: disables ETL.
Monitoring and AlertingYes: configures alerts for task failures or high synchronization latency; requires alert threshold and notification settings. See Configure monitoring and alerting. No: disables alerting.

Step 6: Run the precheck

Click Next: Save Task Settings and Precheck.

To preview the OpenAPI parameters for this task, move the pointer over the button and click Preview OpenAPI parameters.

DTS runs a precheck before starting the task. The task only starts after the precheck passes.

  • If a check item fails, click View Details, resolve the issue, and rerun the precheck.

  • If a check item triggers an alert that can be ignored, click Confirm Alert Details > Ignore > OK, then click Precheck Again. Ignoring an alert may result in data inconsistency.

Step 7: Purchase the synchronization instance

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

On the purchase page, configure the following:

ParameterDescription
Billing MethodSubscription: pay upfront; more cost-effective for long-term use. Pay-as-you-go: billed hourly; release the instance when no longer needed to stop charges.
Resource Group SettingsThe resource group for the instance. Default: default resource group. See What is Resource Management?
Instance ClassSelect an instance class based on the required synchronization speed. See Instance classes of data synchronization instances.
Subscription DurationOnly applies to the Subscription billing method. Options: 1–9 months, 1 year, 2 years, 3 years, or 5 years.

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

The task appears in the task list. Monitor its progress there.

What's next

After the task status changes to Completed, run the following command to verify that data was written correctly to the destination:

ANALYZE TABLE <table_name>;

An HA switchover on the source MySQL database may result in data being written only to memory. Running ANALYZE TABLE helps detect missing data.