Configure one-way data synchronization between ApsaraDB RDS for MySQL instances

Usage notes

• During full data initialization, DTS consumes read and write resources of the source and destination databases, which may increase the database load. When the databases have low performance, low specifications, or heavy workloads (for example, a large number of slow SQL statements, tables without primary keys, or deadlocks on the destination database), the database pressure may be further increased and services may become unavailable. Therefore, you must evaluate the performance of the source and destination databases before starting data synchronization, and it is recommended that you perform data synchronization during off-peak hours (for example, when the CPU load of both the source and destination databases is below 30%).

• During data synchronization, do not use tools such as gh-ost or pt-online-schema-change to perform online DDL operations on the synchronization objects in the source database. Otherwise, the synchronization task fails.

• If no data other than the data from DTS is written to the destination database, you can use Data Management (DMS) to perform online DDL operations. For more information, see Change table schemas without locking tables.

• If a source table lacks a primary key or a unique constraint and contains non-unique data, duplicate data may be created in the destination database.

• During full data initialization, concurrent INSERT operations cause table fragmentation in the destination instance. After full data initialization is complete, the table storage space in the destination instance is larger than that in the source instance.

• When the destination database is an RDS MySQLor RDS MySQL Serverless instance (except versions 5.7 and 8.0), DTS creates a data synchronization account named dtssyncwriter in the destination database that has write permissions and is used only for DTS internal purposes.

Billing

Supported synchronization topologies

• One-way one-to-one synchronization

• One-way one-to-many synchronization

• One-way cascade synchronization

• One-way many-to-one synchronization

For descriptions and considerations of each synchronization topology, see Synchronization topologies.

SQL operations that can be synchronized

Limitations

• Trigger incompatibility

  If the sync object is an entire database and a trigger in that database updates a table within the database, data inconsistency between the source and destination databases may occur. For information about the workaround, see Configure tasks for source databases with triggers.

• RENAME TABLE limitation

  RENAME TABLE operations may cause data inconsistency during synchronization. For example, if the sync object contains only table A and the source instance renames table A to table B during synchronization, table B will not be synchronized to the destination database. To avoid this issue, you can select the entire database in which table A and table B reside as the sync object when you configure the data synchronization task.

Procedure

1. Purchase a data synchronization instance. For more information, see Purchase a data synchronization task.

   Note

   During purchase, set both the source instance and destination instance to MySQL, and set the synchronization topology to One-way Synchronization.

2. Log on to the Data Transmission Service console.

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

4. At the top of the Data Synchronization page, select the region of the data synchronization instance.

5. Find the purchased data synchronization instance, and click Configure Synchronization Channel.

6. Configure the source and destination instance information for the synchronization channel.

   Category	Parameter	Description
   None	Synchronization task name	DTS automatically generates a synchronization task name. We recommend that you specify a meaningful name for easy identification. There is no uniqueness requirement.
   Source instance information	Instance type	Select RDS Instance.
   	Instance region	Displays the source instance region selected when you purchased the data synchronization instance. This value cannot be changed.
   	Instance ID	Select the ID of the RDS instance to use as the source of data synchronization.
   	Database account	Enter the database account of the source RDS. Note When the database type of the source RDS instance is MySQL 5.5 or MySQL 5.6, you do not need to configure Database Account and Database Password.
   	Database password	Enter the password of the database account.
   	Connection method	Select Non-encrypted or SSL-encrypted as needed. If you set it to SSL-encrypted, you must enable the SSL encryption feature of the RDS instance in advance. For more information, see Configure SSL encryption.
   Destination instance information	Instance type	Select RDS Instance.
   	Instance region	Displays the destination instance region selected when you purchased the data synchronization instance. This value cannot be changed.
   	Instance ID	Select the ID of the RDS instance to use as the destination of data synchronization.
   	Database account	Enter the database account of the destination RDS. Note When the database type of the destination RDS instance is MySQL 5.5 or MySQL 5.6, you do not need to configure Database Account and Database Password.
   	Database password	Enter the password of the database account.
   	Connection method	Select Non-encrypted or SSL-encrypted as needed. If you set it to SSL-encrypted, you must enable the SSL encryption feature of the RDS instance in advance. For more information, see Configure SSL encryption.

7. In the lower-right corner of the page, click Set Whitelist and Next.

   If the source or destination database is an Alibaba Cloud database instance, such as an ApsaraDB RDS for MySQL or ApsaraDB for MongoDB instance, DTS automatically adds the CIDR blocks of DTS servers to the IP address whitelist of the instance. If the source or destination database is a self-managed database hosted on an Elastic Compute Service (ECS) instance, DTS automatically adds the CIDR blocks of DTS servers to the security group rules of the ECS instance, and you must make sure that the ECS instance can access the database. If the self-managed database is hosted on multiple ECS instances, you must manually add the CIDR blocks of DTS servers to the security group rules of each ECS instance. If the source or destination database is a self-managed database that is deployed in a data center or provided by a third-party cloud service provider, you must manually add the CIDR blocks of DTS servers to the IP address whitelist of the database to allow DTS to access the database. For more information, see Whitelist DTS server IP addresses.

   Warning

   Adding the public IP address blocks of the DTS service, either automatically or manually, may pose security risks. Using this product, you acknowledge that you understand and accept the potential security risks and that you must implement basic security measures. These measures include, but are not limited to, strengthening password security, limiting the ports open to each CIDR block, using authentication for internal API calls, and regularly checking and restricting unnecessary CIDR blocks. Alternatively, you can connect through a private network using a leased line, VPN Gateway, or Smart Access Gateway.

8. Configure the synchronization policy and object information.

   Parameter	Description
   Select sync objects	In the Source Objects pane, click the objects to synchronize, and then click the icon to move them to the Selected Objects pane. Sync objects can be selected at the database or table level. Note If you select an entire database as a sync object, all schema changes to objects in the database are synchronized to the destination database. By default, the name of a sync object in the destination database is the same as that in the source database. If you want to change the name of a sync object in the destination database, use the object name mapping feature. For more information, see Rename synchronized objects in the destination instance.
   Library, table, and column mapping	Change the names of synchronized objects in the destination instance. For more information, see Map databases, tables, and columns.
   Replicate temporary tables during DMS online DDL	If you use Data Management (DMS) to perform online DDL changes on the source database, you can choose whether to synchronize the temporary tables generated by the DDL changes. Yes: Synchronizes the temporary tables generated by online DDL changes. Note If a large amount of temporary table data is generated by online DDL changes, the data synchronization task may be delayed. No: Does not synchronize the temporary tables generated by online DDL changes. Only the original DDL operations from the source database are synchronized. Note This option causes tables in the destination database to be locked.
   Connection retry duration	If DTS cannot connect to the source or destination instance, it retries for 720 minutes (12 hours) by default. You can also specify a custom retry duration. If DTS reconnects to the source or destination instance within the specified duration, the synchronization task automatically resumes. Otherwise, the task fails. Note You are billed for task run time during connection retries. Customize the retry duration based on your business needs, or release the DTS instance as soon as the source and destination instances are released.

9. After you complete the preceding configurations, click Next in the lower-right corner of the page.

10. Configure advanced settings for synchronization initialization.

    Note

    • This step initializes the schemas and data of the objects that already exist in the source instance in the destination instance, as the baseline data for subsequent incremental synchronization.

    • Synchronization initialization types include schema initialization and full data initialization. By default, you must select Schema Initialization and Full Data Initialization.

11. After you complete the preceding configurations, click Precheck and Start in the lower-right corner of the page.

    Note

    • Before the synchronization task is officially started, a precheck is performed. The synchronization task can only be started after the precheck passes.

    • If the precheck fails, click the icon next to a specific check item to view the failure details.

      • Follow the prompt to fix the issue and run the precheck again.

      • If you do not need to fix the warning check items, you can also select Ignore, Ignore Warning Items and Rerun Precheck to skip the warning check items and run the precheck again.

12. When Precheck Passed is displayed in the Precheck dialog box, close the Precheck dialog box. The synchronization task starts.

13. Wait until link initialization is complete and the task enters the Synchronizing state.

    You can go to the Data Synchronization page to view the status of the synchronization task.

    The synchronization task list on this page includes columns for Instance ID/Task Name, Status, Sync Overview (latency and speed), Billing Method, Synchronization Topology, and Actions. From the Actions column, you can perform operations such as Pause Synchronization, Switch to Subscription, and Upgrade. The page header supports searching by task name and filtering or sorting by status.