Data Transmission Service:Synchronize data from a self-managed MySQL database to an ApsaraDB RDS for MySQL instance

Source database requirements

• Tables must have a PRIMARY KEY or UNIQUE constraint, with all fields unique. Otherwise, duplicate records may appear in the destination database.

• If you select individual tables as sync objects and rename tables or columns, a single sync task supports up to 1,000 tables. Exceeding this limit causes a request error. In that case, split the tables across multiple tasks, or sync the entire database instead.

• Binary log requirements must be met:

  Note

  If the source is a self-managed MySQL database, you must explicitly enable binary logging and set both parameters. For RDS MySQL, binary logging is enabled by default — verify only that binlog_row_image is set to FULL. For more information, see Create an account for a self-managed MySQL database and configure binary logging and Modify instance parameters.

  Parameter	Required value	Notes
  binlog_format	ROW	Self-managed MySQL only
  binlog_row_image	FULL	Required for both self-managed MySQL and ApsaraDB RDS for MySQL
  log_slave_updates	ON	Self-managed MySQL in a dual-primary cluster only
  Retention period (RDS MySQL)	At least 3 days (7 days recommended)	Shorter retention may cause the task to fail
  Retention period (self-managed MySQL)	At least 7 days	Shorter retention may cause the task to fail because DTS cannot obtain the binary logs; in exceptional circumstances, data inconsistency or loss may occur

• Do not execute DDL statements that change schemas during schema synchronization or full data synchronization. Doing so fails the task.

• Data generated by binary log change operations — such as data restored from a physical backup or data from cascade operations — is not synchronized. If this affects your business, remove the affected databases and tables from the sync objects, then add them back. For more information, see Modify the objects to be synchronized.

• If the source is MySQL 8.0.23 or later and the tables include invisible columns, DTS cannot read those columns and data loss occurs. To fix this, make the columns visible:

  • Run ALTER TABLE <table_name> ALTER COLUMN <column_name> SET VISIBLE;. See Invisible Columns.

  • Tables without primary keys automatically generate invisible primary keys. Make those visible too. See Generated Invisible Primary Keys.

Unsupported operations

The following behaviors are not supported or cause synchronization failures:

• Parser-defined data: DTS does not synchronize data where a parser defined by comments is used.

• pt-online-schema-change: Do not use pt-online-schema-change for online DDL operations on sync objects. Use Data Management (DMS) instead. For more information, see Perform lock-free DDL operations.

• RENAME TABLE on sync objects: Renaming a table that is selected as a sync object stops its data from being synchronized. To avoid this, add the database that contains the table — both before and after the rename — to the list of sync objects.

• Writes to the destination during sync: Writing data from other sources to the destination database during synchronization causes data inconsistency.

• EncDB source: If the source is an ApsaraDB RDS for MySQL instance with the EncDB feature enabled, full data synchronization cannot be performed.

  ApsaraDB RDS for MySQL instances with Transparent Data Encryption (TDE) enabled support schema synchronization, full data synchronization, and incremental data synchronization.

• Primary/secondary switchover: If you perform a primary/secondary switchover on a self-managed MySQL source while the task is running, the task fails.

• Read-only RDS MySQL 5.6: A read-only ApsaraDB RDS for MySQL 5.6 instance (which does not record transaction logs) cannot be used as the source.

Other considerations

• The destination database version must be the same as or later than the source database version.

• If the destination is MySQL 8.0.23 or later with invisible columns in the destination tables, DTS cannot find the target columns and the task fails. Make those columns visible using the ALTER TABLE command above.

• If you are not using DTS schema synchronization, make sure field types are compatible between source and destination. For example, a text field in the source mapped to varchar(255) in the destination can result in data truncation.

• If the data includes rare characters or emojis (4-byte characters), the destination database and tables must use the UTF8mb4 character set. If you are using DTS schema synchronization, set the character_set_server parameter in the destination to UTF8mb4.

• Run the sync during off-peak hours. Initial full data synchronization reads from the source and writes to the destination concurrently, which increases load on both databases.

• After initial full data synchronization, the destination tablespace is typically larger than the source due to fragmentation from concurrent INSERT operations.

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

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

• If a DDL statement fails in the destination, the DTS task continues running. View failed DDL statements in the task logs. For more information, see View task logs.

• If you want to sync account information from the source, additional permissions are required. For more information, see Migrate database accounts.

• If a DTS task fails, DTS technical support attempts to restore it within 8 hours. The task may be restarted, and task-level parameters (not database parameters) may be modified.

Synchronization latency for self-managed MySQL sources

DTS calculates synchronization latency based on the timestamp of the latest synchronized data in the destination and the current timestamp in the source. If no DML operations are performed on the source for an extended period, the displayed latency may be inaccurate. To refresh the latency, run a DML operation on the source. If you are syncing an entire database, create a heartbeat table that receives data every second.

DTS also executes CREATE DATABASE IF NOT EXISTS 'test' in the source periodically to advance the binary log file position. This applies to both self-managed MySQL sources and ApsaraDB RDS for MySQL sources.

Step 1: Go to the data synchronization page

Use one of the following methods:

Step 2: Configure source and destination databases

1. Click Create Task.

2. Configure the source database parameters:

   Parameter	Description
   Task Name	A name for the DTS task. DTS generates one automatically. Use a descriptive name for easy identification. The name does not need to be unique.
   Select Existing Connection	If the source is registered with DTS, select it from the drop-down list — DTS populates the remaining parameters automatically. Otherwise, fill in the parameters below. For more information, see Manage database connections.
   Database Type	Select MySQL.
   Access Method	Select Self-managed Database on ECS. For environment preparation, see Preparation overview.
   Instance Region	The region where the source MySQL database resides.
   Replicate Data Across Alibaba Cloud Accounts	Select No for this example.
   ECS Instance ID	The ID of the ECS instance hosting the source MySQL database.
   Port Number	The service port of the source MySQL database. The port must be accessible over the Internet. Default: 3306.
   Database Account	The account for the source MySQL database. See the Permissions required for database accounts section.
   Database Password	The password for the database account.
   Encryption	Select Non-encrypted or SSL-encrypted based on your requirements. If you select SSL-encrypted, upload a CA Certificate and configure the CA Key parameter.

3. Configure the destination database parameters:

   Parameter	Description
   Select Existing Connection	If the destination is registered with DTS, select it from the drop-down list. Otherwise, fill in the parameters below. For more information, see Manage database connections.
   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	Select No for this example.
   RDS Instance ID	The ID of the destination ApsaraDB RDS for MySQL instance.
   Database Account	The account for the destination database. See the Permissions required for database accounts section.
   Database Password	The password for the database account.
   Encryption	Select Non-encrypted or SSL-encrypted. To use SSL encryption, enable it on the RDS instance first. For more information, see Use a cloud certificate to enable SSL encryption.

4. Click Test Connectivity and Proceed. In the CIDR Blocks of DTS Servers dialog box, click Test Connectivity.

   Make sure the CIDR blocks of DTS servers can be added — automatically or manually — to the security settings of the source and destination databases. For more information, see Add the CIDR blocks of DTS servers.

Step 3: Configure sync objects

1. In the Configure Objects step, configure the following parameters:

   Renaming an object with the object name mapping feature may cause dependent objects to fail synchronization.

   Parameter	Description
   Synchronization Types	Incremental Data Synchronization is selected by default. Also select Schema Synchronization and Full Data Synchronization to migrate historical data before starting incremental sync.
   Method to Migrate Triggers in Source Database	Available only when Schema Synchronization is selected. For more information, see Synchronize or migrate triggers from the source database.
   Enable Migration Assessment	Available only when Schema Synchronization is selected. If set to Yesalert notification settings, the precheck takes longer, and assessment results appear as Assessment Result during the precheck. Assessment results do not affect precheck pass/fail outcomes.
   Synchronization Topology	Select One-way Synchronization.
   Processing Mode of Conflicting Tables	Precheck and Report Errors: the precheck fails if the destination has tables with the same names as the source. To resolve naming conflicts, use the object name mapping feature. For more information, see Database, table, and column name mapping. Ignore Errors and Proceed: skips the name conflict check. During full sync, existing destination records with matching primary keys are retained; during incremental sync, they are overwritten. If schemas differ, the task may fail.
   Whether to migrate Event	Specify whether to sync events from the source. For more information, see Synchronize or migrate events.
   Capitalization of Object Names in Destination Instance	Controls the capitalization of database, table, and column names in the destination. Default: DTS default policy. For more information, see Specify the capitalization of object names in the destination instance.
   Source Objects	Select objects from the Source Objects section and click the right arrow icon to add them to Selected Objects. Supported granularity: columns, tables, or databases. If you select tables or columns, DTS does not sync views, triggers, or stored procedures.
   Selected Objects	Right-click an object to rename it individually. Click Batch Edit to rename multiple objects at once. For more information, see Map object names. Right-click an object to select specific SQL operations or set WHERE filter conditions. For more information, see Set filter conditions.

2. Click Next: Advanced Settings and configure the following:

   Parameter	Description
   Dedicated Cluster for Task Scheduling	DTS uses the shared cluster by default. Purchase a dedicated cluster for improved task stability. For more information, 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.	Applies when you use DMS or gh-ost for online DDL operations. Yes: syncs temporary table data (may extend sync time). No, Adapt to DMS Online DDL: syncs only the final DDL from DMS; destination tables may be locked. No, Adapt to gh-ost: syncs only the final DDL from gh-ost; destination tables may be locked. Do not use pt-online-schema-change — it will cause the task to fail.
   Whether to Migrate Accounts	Specify whether to sync account information from the source. If set to Yes, select the accounts and verify their permissions. For more information, see Migrate database accounts.
   Retry Time for Failed Connections	How long DTS retries failed connections after the task starts. Range: 10–1440 minutes. Default: 720. Set to at least 30 minutes. If multiple tasks share the same source or destination, the shortest retry time applies to all of them. DTS charges for the instance during retry periods.
   Retry Time for Other Issues	How long DTS retries failed DML or DDL operations. Range: 1–1440 minutes. Default: 10. Set to at least 10 minutes. This value must be less than Retry Time for Failed Connections.
   Enable Throttling for Full Data Synchronization	Available only when Full Data Synchronization is selected. Configure QPS to the source, RPS, and data speed (MB/s) to reduce load on the destination.
   Enable Throttling for Incremental Data Synchronization	Configure RPS and data speed (MB/s) to reduce load on the destination.
   Environment Tag	An optional tag for identifying the DTS instance.
   Whether to delete SQL operations on heartbeat tables of forward and reverse tasks	Yes: DTS does not write heartbeat table operations to the source; synchronization latency may appear elevated. No: DTS writes heartbeat table operations; 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 latency threshold breaches; configure alert thresholds and contacts. See Configure monitoring and alerting. No: disables alerting.

3. Click Next Step: Data Verification to configure data verification. For more information, see Configure a data verification task.

Step 4: Run the precheck

Click Next: Save Task Settings and Precheck.

To preview the API parameters for the current task configuration, hover over the button and click Preview OpenAPI parameters before proceeding.

DTS runs a precheck before the task can start. If the precheck fails:

• Click View Details next to each failed item, resolve the issue, and run the precheck again.

• If an alert item can be ignored, click Confirm Alert Details > Ignore > OK, then click Precheck Again. Ignoring alerts may lead to data inconsistency.

Step 5: Purchase an instance

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

2. On the buy page, configure the following parameters:

   Parameter	Description
   Billing Method	Subscription: pay upfront for a fixed term; 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 data synchronization instance. Default: default resource group. For more information, see What is Resource Management?
   Instance Class	The synchronization speed varies by instance class. For more information, see Instance classes of data synchronization instances.
   Subscription Duration	Available only for the Subscription billing method. Options: 1–9 months, 1 year, 2 years, 3 years, or 5 years.

3. Read and select Data Transmission Service (Pay-as-you-go) Service Terms.

4. Click Buy and Start, then click OK in the dialog box.

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