Synchronize a self-managed MySQL database to an RDS for MySQL instance over a leased line, VPN Gateway, or Smart Access Gateway
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:
A self-managed MySQL database connected to Alibaba Cloud over Express Connect, VPN Gateway, or Smart Access Gateway. For setup instructions, see Connect an on-premises database to DTS by using CEN, Connect a data center to DTS by using VPN Gateway, or Connect an on-premises database or a third-party cloud database to Alibaba Cloud by using Database Gateway
DTS server CIDR blocks added to the source database whitelist. See Add the CIDR blocks of DTS servers
A database account on the source MySQL database with binary logging enabled and configured. See Create an account for a self-managed MySQL database and configure binary logging
An ApsaraDB RDS for MySQL destination instance with storage space larger than the source database. See Create an ApsaraDB RDS for MySQL instance
For supported MySQL versions, see Overview of data synchronization scenarios.
Billing
| Synchronization type | Task configuration fee |
|---|---|
| Schema synchronization and full data synchronization | Free of charge |
| Incremental data synchronization | Charged. See Billing overview |
SQL operations that can be synchronized
| Operation type | SQL 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 |
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
| Database | Schema synchronization | Full data synchronization | Incremental data synchronization |
|---|---|---|---|
| Self-managed MySQL | SELECT | SELECT | SELECT 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 MySQL | Read and write permissions | Read and write permissions | Read and write permissions |
For instructions on creating accounts and granting permissions:
Self-managed MySQL: Create an account for a self-managed MySQL database and configure binary logging
ApsaraDB RDS for MySQL: Create an account and Modify account 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_formattorowandbinlog_row_imagetofull.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_updatestoONso 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:
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 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.
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 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
| Parameter | Description |
|---|---|
| Task Name | DTS 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
| Parameter | Description |
|---|---|
| Select an existing DMS database instance | (Optional) Select an existing registered DMS database instance. If selected, DTS auto-populates the parameters below. |
| Database Type | Select MySQL. |
| Access Method | Select Express Connect, VPN Gateway, or Smart Access Gateway. |
| Instance Region | Select the region where the self-managed MySQL database resides. |
| Replicate Data Across Alibaba Cloud Accounts | Select No for same-account synchronization. |
| Connected VPC | Select the virtual private cloud (VPC) connected to the self-managed MySQL instance. |
| Domain Name or IP | Enter the IP address or domain name of the self-managed MySQL server. |
| Port Number | Enter the service port of the source database. Default: 3306. |
| Database Account | Enter the source database account. See the permissions table in this topic. |
| Database Password | Enter the password for the database account. |
| Encryption | Select Non-encrypted or SSL-encrypted based on your requirements. |
Destination database
| Parameter | Description |
|---|---|
| Select an existing DMS database instance | (Optional) Select an existing registered DMS database instance. If selected, DTS auto-populates the parameters below. |
| Database Type | Select MySQL. |
| Access Method | Select Alibaba Cloud Instance. |
| Instance Region | Select the region where the destination ApsaraDB RDS for MySQL instance resides. |
| Replicate Data Across Alibaba Cloud Accounts | Select No for same-account synchronization. |
| RDS Instance ID | Select the destination ApsaraDB RDS for MySQL instance. |
| Database Account | Enter the destination database account. See the permissions table in this topic. |
| Database Password | Enter the password for the database account. |
| Encryption | Select 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.
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
| Parameter | Description |
|---|---|
| Synchronization Types | By 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 Database | Select how to handle trigger synchronization. Only shown when Schema Synchronization is selected. See Synchronize or migrate triggers from the source database. |
| Enable Migration Assessment | Checks whether source and destination schemas meet migration requirements. Only shown when Schema Synchronization is selected. Assessment results don't affect the precheck. |
| Synchronization Topology | Select One-way Synchronization. |
| Processing Mode of Conflicting Tables | Precheck 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 Instance | Controls 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 Objects | Select objects to sync and click the right-arrow icon to move them to Selected Objects. You can select databases, tables, or columns. |
| Selected Objects | To 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
| Parameter | Description |
|---|---|
| Dedicated Cluster for Task Scheduling | DTS 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 database | Controls 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 Accounts | Select Yes to sync source database account information and then select which accounts to sync. |
| Retry Time for Failed Connections | How 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 Issues | How 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 Migration | Limits 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 Synchronization | Limits write RPS and bandwidth during incremental synchronization. Reduces destination database load. |
| Environment Tag | Tags the DTS instance for environment identification (for example, production or staging). |
| Whether to delete SQL operations on heartbeat tables of forward and reverse tasks | Yes: 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 ETL | Yes: 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 Alerting | Yes: 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:
| Parameter | Description |
|---|---|
| Billing Method | Subscription: 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 Settings | The resource group for the instance. Default: default resource group. See What is Resource Management? |
| Instance Class | Select an instance class based on the required synchronization speed. See Instance classes of data synchronization instances. |
| Subscription Duration | Only 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.