Synchronize self-managed MySQL on ECS to RDS MySQL

Prerequisites

• The self-managed MySQL database is version 5.1, 5.5, 5.6, 5.7, or 8.0.

• A destination RDS MySQL instance is created. The destination instance must have more storage space than the source database. For more information, see Create an RDS MySQL instance.

Usage notes

• During initial full data synchronization, DTS consumes read and write resources from the source and destination databases, which increases the database load. If database performance is poor, instance specifications are low, or business traffic is heavy (for example, the source database has many slow SQL queries or tables without primary keys, or the destination database experiences deadlocks), the database load increases and may even cause the service to become unavailable. Before you synchronize data, evaluate the performance of your source and destination instances. We recommend performing data synchronization during off-peak hours, for example, when the CPU utilization of both instances 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 other sources are writing data 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 initial full data synchronization, concurrent INSERT operations cause table fragmentation in the destination instance. After this process completes, the tablespace of the destination instance will be larger than that of the source instance.

• If the destination database is an RDS MySQL or RDS MySQL Serverless instance (other than version 5.7 or 8.0), DTS creates a data synchronization account named dtssyncwriter in the destination database. This account is granted write permissions and is used only by DTS.

Billing

Supported synchronization topologies

• One-to-one one-way synchronization

• One-to-many one-way synchronization

• Many-to-one one-way synchronization

• Cascade one-way synchronization

• One-to-one two-way synchronization

  Note

  To implement two-way synchronization, see Two-way synchronization between MySQL instances.

Supported SQL operations

Limitations

• Incompatible with triggers

  If you synchronize an entire database and a trigger within that database updates another table in the same database, data inconsistency may occur between the source and destination databases. For a solution, see Configure a data synchronization or migration task for a source database that contains a trigger.

• RENAME TABLE limitations

  The RENAME TABLE operation can cause data inconsistency. For example, if you select only Table A for synchronization and it is renamed to Table B in the source instance, Table B is not synchronized to the destination instance. To prevent this issue, select the entire database that contains both Table A and Table B as the synchronization object when you configure the data synchronization task.

Before you begin

Before you configure the synchronization task, you must create an account for a self-managed MySQL database and configure binary logging.

Procedure

1. Purchase a synchronization instance. For more information, see Purchase procedure.

   Note

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

2. Log on to the Data Transmission Service console.

   Note

   If you are redirected to the Data Management (DMS) console, click the icon in the lower-right corner and then click the icon to return to the previous version of the Data Transmission Service (DTS) console.

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

4. At the top of the Synchronization Tasks page, select the region where the destination instance is located.

5. Find the purchased synchronization instance and click Configure Task.

6. Configure the source and destination instances.

   Section	Parameter	Description
   N/A	Synchronization Task Name	DTS automatically generates a task name. We recommend giving the task a descriptive name for easier identification.
   Source Instance Details	Instance Type	Select User-Created Database in ECS Instance.
   	Instance Region	The region of the source instance that you selected when you purchased the synchronization instance. This parameter cannot be changed.
   	ECS Instance ID	Select the ID of the ECS instance that serves as the data source.
   	Database Type	MySQL. This is the database type you selected during purchase and cannot be changed.
   	Port Number	Enter the service port of the self-managed MySQL database. The default value is 3306.
   	Database Account	Enter the account for the self-managed MySQL database. The account must have the REPLICATION CLIENT, REPLICATION SLAVE, SHOW VIEW, and SELECT permissions on all objects to be synchronized.
   	Database Password	Enter the password for the database account.
   Destination Instance Details	Instance Type	Select RDS Instance.
   	Instance Region	The region of the destination instance that you selected when you purchased the synchronization instance. This parameter cannot be changed.
   	Instance ID	Select the ID of the destination RDS instance.
   	Database Account	Enter the account for the destination RDS instance. Note If the destination RDS instance runs MySQL 5.5 or MySQL 5.6, you do not need to configure the Database Account and Database Password parameters.
   	Database Password	Enter the password for the database account.
   	Connection Method	Select Non-encrypted or SSL-encrypted based on your requirements. If you select SSL-encrypted, you must enable SSL encryption for 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 is an Alibaba Cloud database instance (such as RDS MySQL or ApsaraDB for MongoDB), DTS automatically adds the CIDR blocks of the DTS servers in the corresponding region to the whitelist of the instance. If the source or destination is a self-managed database on an ECS instance, DTS automatically adds the CIDR blocks of the DTS servers to the security rules of the ECS instance. You must also ensure that the self-managed database allows access from the ECS instance. If the database is a cluster deployed on multiple ECS instances, you must manually add the CIDR blocks of the DTS servers to the security rules of each ECS instance. If the source or destination is a self-managed database in an on-premises data center or a third-party cloud database, you must manually add the CIDR blocks of the DTS servers to grant them access. For a list of DTS server CIDR blocks, see CIDR blocks of DTS servers.

   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 objects.

   Parameter	Description
   Select Synchronization Objects	In the Source Objects box, select the objects to synchronize and click the icon to move them to the Selected Objects box. You can select objects at the database and table levels. Note If you select an entire database as a synchronization object, all schema change operations on objects within that database are synchronized to the destination database. By default, object names in the destination database are the same as in the source database. To rename an object in the destination database, use the object name mapping feature. For more information, see Set object names in the destination instance.
   Object Name Mapping	Change the names of synchronized objects in the destination instance. For more information, see Map databases, tables, and columns.
   Replicate temporary tables to the destination database during online DDL operations on source tables in DMS	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.
   Retry duration for connection failure to source or destination database	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.

10. Configure advanced settings for initial synchronization.

    • This step copies the schema and data of the selected objects from the source to the destination, creating a baseline for incremental data synchronization.

    • Initial synchronization includes two types: initial schema synchronization and initial full data synchronization. By default, you must select both Initial Schema Synchronization and Initial Full Data Synchronization.

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

    Important

    • Before the data synchronization task starts, a precheck is performed. The task can start only after all check items pass.

    • If the precheck fails, click the icon next to a failed item to view the details.

      • You can fix the issue based on the cause and run the precheck again.

      • If you want to ignore an alert, click Ignore and then Ignore Alarm and Re-precheck to run the precheck again.

12. In the Precheck dialog box, after Precheck Passed is displayed, close the Precheck dialog box, and the synchronization task will start.

13. Wait for the synchronization task to initialize until its status changes to Synchronizing.

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