ApsaraDB RDS:Two-way synchronization between MySQL databases

Binary log requirements

• Binary logging is enabled by default on ApsaraDB RDS for MySQL. Make sure binlog_row_image is set to full. If not, the precheck fails and the task cannot start. To change this parameter, see Modify instance parameters.

• Binary logs must be retained for at least 7 days. If DTS cannot access the required binary logs, the task fails and data inconsistency or loss may occur. For information on managing binary log files, see Manage binary log files.

For self-managed MySQL:

• Set binlog_format to row and binlog_row_image to full.

• In a dual-primary cluster, set log_slave_updates to ON so DTS can access all binary logs. See Create an account for a self-managed MySQL database and configure binary logging.

• If the self-managed MySQL source is also the source for other DTS tasks, add log_slave_updates=1 to the my.cnf file in /etc and restart the database. Otherwise, changes from the reverse task are not recorded in binary logs, and other DTS tasks may encounter data inconsistency.

Two-way synchronization limits

The following limits apply specifically to two-way synchronization:

• DDL operations sync only in the forward direction. DDL in the reverse direction is ignored. To exclude DDL from synchronization, set Exclude DDL Operations to Yes on the forward task.

• Do not select the same objects in both the forward and reverse tasks.

• Avoid using object name mapping in the reverse task — it may cause data inconsistency.

• For the reverse task, set Processing Mode of Conflicting Tables to Ignore Errors and Proceed for tables already synchronized by the forward task.

Table and schema limits

• Tables without primary keys or UNIQUE constraints may result in duplicate records in the destination. Enable the Exactly-Once write feature when configuring the task. See Synchronize tables without primary keys or UNIQUE constraints.

• If you select individual tables as synchronization objects and need to rename tables or columns, a single task supports up to 1,000 tables. For more than 1,000 tables, configure multiple tasks or synchronize the entire database instead.

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

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

• Make sure all columns to be synchronized are visible. To make a column visible, run:

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

  Tables without explicit primary keys may have auto-generated invisible primary keys — make those visible too. See Invisible Columns and Generated Invisible Primary Keys.

Other limits

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

• DTS does not synchronize data that uses parser-defined comments.

• If you do not use DTS schema synchronization, make sure field types are compatible between source and destination. For example, if a source field is text and the destination field is varchar(255), large values may be truncated.

• To store four-byte characters such as rare characters or emojis, the destination database and tables must use the UTF8mb4 character set. If you use DTS schema synchronization, set the character_set_server parameter to UTF8mb4 in the destination instance.

• Schedule synchronization during off-peak hours. During full data synchronization, DTS uses read and write resources on both instances, which may increase server load. Concurrent INSERT operations during full data synchronization also cause table fragmentation, so the destination tablespace may be larger than the source after full synchronization completes.

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

• DTS disables FOREIGN KEY constraints in the destination by default. Cascade and delete operations from the source are not synchronized.

• During schema synchronization, DTS synchronizes foreign keys from source to destination. During full and incremental synchronization, DTS temporarily disables foreign key constraint checks and cascade operations at the session level. If you perform cascade updates or deletes on the source during synchronization, data inconsistency may occur.

• Data generated by binary log change operations — such as data restored from a physical backup or data from cascade operations — is not recorded or synchronized while the task is running.

  If unrecorded data affects your business, remove the affected databases and tables from the synchronization objects, then re-add them after confirming your business is not impacted. See Modify the objects to be synchronized.

• If DDL statements fail to execute in the destination, the task continues running. Check task logs to view failed DDL statements. See View task logs.

• After the task status changes to Completed, run the following command to verify that data was written to the destination table (for example, an HA switchover may cause data to reside only in memory):

  ANALYZE TABLE <table_name>;

• ApsaraDB RDS for MySQL instances with the EncDB feature enabled cannot be used for full data synchronization. Instances with Transparent Data Encryption (TDE) enabled support schema, full, and incremental data synchronization.

• If a DTS task fails, DTS support attempts to restore it within 8 hours. During restoration, the task may be restarted and task parameters (not database parameters) may be adjusted.

• To synchronize database accounts, review the requirements in Migrate database accounts.

ApsaraDB RDS for MySQL source limits

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

• DTS periodically executes CREATE DATABASE IF NOT EXISTS 'test' in the source to advance the binary log file position.

Self-managed MySQL source limits

• If you perform a primary/secondary switchover on the source database when the data synchronization task is running, the task fails.

• DTS calculates synchronization latency based on the timestamp of the latest synchronized data in the destination database and the current timestamp in the source database. If no DML operation is performed on the source database for a long time, the synchronization latency may be inaccurate. If the latency is too high, perform a DML operation on the source database to update the latency. If you select an entire database as the synchronization object, you can create a heartbeat table. The heartbeat table is updated or receives data every second.

• DTS periodically executes CREATE DATABASE IF NOT EXISTS 'test' in the source to advance the binary log file position.

Step 1: Go to the Data Synchronization Tasks page

1. Log on to the Data Management (DMS) console.DTS consoleDMS console

2. In the top navigation bar, click Data + AI.

3. In the left-side navigation pane, choose DTS (DTS) > Data Synchronization.

   Steps may vary depending on the DMS console layout. See Simple mode and Customize the layout and style of the DMS console. Alternatively, go directly to the Data Synchronization Tasks page.

Step 2: Select the region

On the right side of Data Synchronization Tasks, select the region where the synchronization instance resides.

In the new DTS console, select the region from the top navigation bar.

Step 3: Create a task

Click Create Task.

If the page shows a New Configuration Page button in the upper-right corner, click it to use the new configuration UI. If Back to Previous Version is shown instead, you are already on the new version.

Some parameters differ between old and new versions. The new version is recommended.

Step 4: Configure source and destination databases

Step 5: Test connectivity

Click Test Connectivity and Proceed.

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

Step 6: Configure synchronization objects

In the Configure Objects step, set the following parameters:

Parameter	Description
Synchronization Types	Select Schema Synchronization, Full Data Synchronization, and Incremental Data Synchronization. Schema and full data synchronization establish the baseline; incremental synchronization keeps the instances in sync going forward.
Method to Migrate Triggers in Source Database	Select a method to synchronize triggers based on your requirements. If no triggers need to be synchronized, leave this blank. See Synchronize or migrate triggers from the source database. This parameter appears only when Schema Synchronization is selected.
Enable Migration Assessment	Select Yes to check whether source and destination schemas (index lengths, stored procedures, dependent tables) meet synchronization requirements. Assessment results are visible during the precheck but do not affect the precheck outcome. This parameter appears only when Schema Synchronization is selected.
Synchronization Topology	Select Two-way Synchronization.
Enable Exactly-Once Write	Select Yes if any tables to synchronize lack primary keys or UNIQUE constraints. See Synchronize tables without primary keys or UNIQUE constraints.
Exclude DDL Operations	Select Yes to exclude DDL synchronization, or No to include it. DDL operations sync only in the forward direction. This parameter applies only to the forward synchronization task.
Global Conflict Resolution Policy	Select the policy to apply when conflicts occur. See Conflict detection for policy descriptions. If a task is paused or restarted, data is overwritten during the latency period regardless of this setting.
Processing Mode of Conflicting Tables	Precheck and Report Errors: the precheck fails if identical table names exist in both instances. Ignore Errors and Proceed: skips the check. If schemas match and a record with the same primary or unique key exists in both, full synchronization keeps the destination record; incremental synchronization overwrites it. If schemas differ, synchronization may fail or produce partial results.
Capitalization of Object Names in Destination Instance	Specifies how database, table, and column names are capitalized in the destination. The default is DTS default policy. See Specify the capitalization of object names in the destination instance.
Source Objects	Select the databases, tables, or columns to synchronize and click to add them to Selected Objects. If you select tables or columns, views, triggers, and stored procedures are not synchronized.
Selected Objects	Right-click an object to rename it (object name mapping) or to add SQL operation filters or WHERE conditions. Click Batch Edit to rename multiple objects at once. Note: renaming objects may break synchronization for dependent objects.

Click Next: Advanced Settings and configure the following:

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 API parameters before saving, hover over the button and click Preview OpenAPI parameters.

The task cannot start until it passes the precheck. If the precheck fails, click View Details next to each failed item, fix the issues, and run the precheck again. For alert items that can be ignored, click Confirm Alert Details > Ignore > OK, then click Precheck Again.

Step 8: Purchase the synchronization instance

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

On the purchase page, configure the following:

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

The forward synchronization task (instance A → instance B) is now created and visible in the task list.

Step 9: Configure the reverse synchronization task

Once the forward task reaches Running status, configure the reverse task (instance B → instance A):

1. In the task list, find the reverse synchronization task and click Configure Task.

2. Configure the source and destination databases:

   • Source = the destination instance from the forward task (instance B)

   • Destination = the source instance from the forward task (instance A)

   Important

   The Instance Region values cannot be modified for the reverse task. The reverse task has fewer parameters than the forward task — configure only what the console displays.

3. Configure synchronization objects. Apply these constraints for the reverse task:

   • Under Processing Mode of Conflicting Tables, select Ignore Errors and Proceed for tables already synchronized by the forward task.

   • Do not select objects that are already in the forward task's Selected Objects list.

   • Avoid using object name mapping for the reverse task — it may cause data inconsistency.

4. Run the precheck and wait until Success Rate reaches 100%. Click Back.

Step 10: Verify the configuration

Wait until the Status of both the forward and reverse tasks changes to Running. Two-way data synchronization is now active.

Database-level policy

1. In the Selected Objects section, right-click a database and open the conflict resolution settings.

2. Set Enable Independent Conflict Resolution Policy to Yes.

3. Select a Conflict Resolution Policy:

   Policy	Behavior
   TaskFailed	Task exits on conflict. Manually resolve to resume.
   Ignore	Skip the conflicting statement. The destination record is kept.
   Overwrite	Overwrite the destination record.
   UseMax	Write the larger value to the destination. If the record is missing or the field type is unsupported, falls back to Overwrite.
   UseMin	Write the smaller value to the destination. If the record is missing or the field type is unsupported, falls back to Ignore.

4. In Column Name, enter the column to which the policy applies. Click Add Column to add more columns.

Table-level policy

1. In the Selected Objects section, right-click a table and open the column settings.

2. In Columns, select Independent Conflict Resolution Policy.

3. In Columns for Conflict Detection, select the columns to apply the policy to. By default, columns with primary keys and UNIQUE constraints are pre-selected. The global policy does not apply to columns with an independent policy.

   If columns are auto-selected by the system, UseMax and UseMin are not available.

4. Select a Conflict Resolution Policy from the options described in the table above.

Click OK to save.