MySQL as a Source: Data Synchronization Limits

Network

Cross-region or cross-country deployments — for example, a source database in Singapore connected to DTS via a VPN in China (Hong Kong) — may cause RTT to exceed this limit, resulting in synchronization latency.

Business load

DTS pulls logs for the entire source database instance, not just the synchronized objects. Frequent writes to unrelated large tables generate logs that can cause synchronization latency, even when you synchronize only a small subset of tables.

Additional load considerations:

• Batch updates: Large-scale updates to CLOB, BLOB, or LONG type columns may cause latency. Perform such operations in batches or schedule them during off-peak hours.

• Tables without primary keys: Frequent DELETE or UPDATE operations on tables without primary keys may cause latency.

• Large transactions: A single transaction with a log volume exceeding 100 GB may cause the task to fail.

Cross-account and cross-border synchronization

Cross-account synchronization: Support depends on the database type and connection type. See Configure Cross-Alibaba Cloud Account Tasks.

Cross-border data synchronization: Not enabled by default. Submit a request for cross-border data synchronization permission and pass the review before use.

Instance recovery

If a DTS instance fails, support will attempt to recover it within 8 hours. During recovery, the instance may be restarted or have its parameters adjusted.

Only DTS instance parameters are modified during recovery — database parameters are not changed. For the parameters that may be modified, see Modify Instance Parameters.

Account permissions

Self-managed MySQL requires the following permissions:

ApsaraDB RDS for MySQL: Provide an account with read and write permission.

To synchronize database accounts from the source, see Migrate Database Accounts for prerequisites and considerations.

Binary logging configuration

To verify the current values, run the following queries on the source database:

-- Check binary logging status (MySQL 8.x)
SELECT variable_value AS "Binary logging status"
FROM performance_schema.global_variables
WHERE variable_name = 'log_bin';

-- Check binlog_row_image and binlog_format
SHOW GLOBAL VARIABLES WHERE variable_name IN ('binlog_row_image', 'binlog_format');

Binary log retention period

Retaining binary logs for less than the required period may cause DTS task failures because logs become unavailable. In extreme cases, this can lead to data inconsistency or data loss. Issues caused by insufficient retention periods are not covered by the DTS Service Level Agreement (SLA).

Tables and primary keys

All tables to be synchronized must have a primary key or a UNIQUE constraint with unique field values. Without these, data duplication may occur in the destination.

To find tables without primary keys in the source database, run:

SELECT
  t.TABLE_SCHEMA AS table_schema,
  t.TABLE_NAME AS table_name
FROM information_schema.TABLES t
LEFT JOIN information_schema.TABLE_CONSTRAINTS tc
  ON t.TABLE_SCHEMA = tc.TABLE_SCHEMA
  AND t.TABLE_NAME = tc.TABLE_NAME
  AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
WHERE t.TABLE_TYPE = 'BASE TABLE'
  AND tc.CONSTRAINT_NAME IS NULL
  AND t.TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME;

For Two-way Synchronization links (such as MySQL to MySQL), enable the Exactly-Once write feature in the task to accurately synchronize data from tables without primary keys or UNIQUE constraints. See Synchronize Tables Without Primary Keys and UNIQUE Constraints.

Table quantity limit: If the synchronization scope is table-level and requires column mapping, a single task supports a maximum of 1,000 tables. If this limit is exceeded, split the tables across multiple tasks or configure a full database synchronization task.

Invisible columns (MySQL 8.0.23 and later)

If the source database is MySQL 8.0.23 or later, check for invisible columns in the objects to be synchronized. Make them visible before starting the task:

ALTER TABLE <table_name> ALTER COLUMN <column_name> SET VISIBLE;

Also set automatically generated invisible primary keys (for tables without primary keys) to visible. Failing to do so may cause data loss because DTS cannot retrieve data for invisible columns.

Version compatibility

Synchronize from a lower version to a higher version, or keep source and destination versions consistent. Synchronizing from a higher version to a lower version may cause compatibility issues.

MySQL compatibility

When DTS synchronizes data from a MySQL-family source database, it relies on the standard MySQL protocol and Binlog format, and behaviors incompatible with standard MySQL are not supported. If your source database claims MySQL compatibility but behaves differently from MySQL (for example, when OceanBase is connected as a source in MySQL mode, the timestamp of the trailing rotate event in the Binlog is 0, which differs from MySQL), the DTS synchronization task may fail.

Unsupported source databases

• ApsaraDB RDS for MySQL 5.6 read-only instances (which do not record transaction logs)

• ApsaraDB RDS for MySQL instances with the always-confidential feature enabled (Full Data Synchronization is not supported; Transparent Data Encryption (TDE) is not affected)

Cluster read/write node requirement

If the source is Amazon Aurora MySQL or another cluster-mode MySQL instance, connect DTS to a read/write (RW) node that is always available. Connecting to a read-only node may cause the synchronization task to run incorrectly.

Performance impact

Full Data Synchronization initialization consumes read and write resources from both the source and destination databases. Perform data synchronization during off-peak hours to minimize impact.

DDL restrictions

Do not perform DDL during schema or full data synchronization: DDL operations that change the database or table schema during Schema Synchronization or Full Data Synchronization will cause the task to fail.

During full synchronization, DTS queries the source database, which creates metadata locks. These locks may block DDL operations on the source database.

Unsupported DDL types: The following DDL operations are not supported and may cause data loss or task failure:

• DDL parsers defined using comment syntax

• Online DDL in temporary table mode (including multi-table merge scenarios)

• Adding function-based indexes to unique key columns

Online DDL tool restriction: If the synchronization scope is a single table or multiple tables (not the entire database), do not use tools such as pt-online-schema-change on the synchronized objects during the task. This causes synchronization failure.

Operations not captured by binary logs

DTS cannot detect or synchronize data changes from operations that are not written to binary logs, including:

• Foreign key cascade updates and deletes

• Data recovery through physical backup

If this affects your data, and if your business allows, remove the affected tables from the synchronization scope using the modify synchronization object feature, then add them back. See Modify Synchronization Objects.

Primary/secondary switchover

If a self-managed MySQL source instance undergoes a primary/secondary switchover, the synchronization task will fail.

Synchronization latency

DTS calculates latency by comparing the timestamp of the last synchronized record with the current time. If no DML operations occur for an extended period, the latency reading may be inaccurate.

To get an accurate latency reading, run a DML operation on the source database.

If the entire database is the synchronization scope, create a heartbeat table that writes data every second.

Anti-looping database

DTS automatically creates a dts database in the destination databases of both the forward and reverse tasks to prevent data looping. While the task runs:

• Do not modify the dts database.

• The task account must have read and write permission on the dts database.

The dts database may affect normal MySQL primary/secondary synchronization. Check whether the dts database and its tables exist in the secondary database. If not, manually create them based on the schema in the primary database.

Unidirectional full initialization

When configuring a two-way synchronization task, if the destination of one task overlaps with the source of another:

• Only one direction can perform Full Data Synchronization and Incremental Data Synchronization.

• The other direction supports only Incremental Data Synchronization to ensure correct data initialization.

• Synchronized data does not cascade: data written to the destination of one task is not picked up as source data by the other task.

log_slave_updates for shared source databases

If a self-managed MySQL instance serves as the source for both a two-way synchronization task and other DTS tasks, set log_slave_updates=1 in its configuration file and restart the database. This ensures that data changes synchronized from the other direction are recorded in binary logs and captured by other tasks.

Message Queue for Apache Kafka