PolarDB:Synchronize data from a self-managed MySQL database on an ECS instance to PolarDB for MySQL

Notes

• During initial full data synchronization, DTS consumes read and write resources on both the source and destination databases. This can increase the database server load. If your databases have low specifications, poor performance, or heavy service traffic, the impact can be significant. For example, issues like numerous slow SQL queries, tables without primary keys on the source instance, or deadlocks on the destination instance can increase database pressure and may even cause service interruptions. Before you start the data synchronization task, evaluate the performance of your source and destination instances. Run the task during off-peak hours, such as when the CPU utilization of both instances is below 30%.

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

• During initial full data synchronization, concurrent INSERT operations cause table fragmentation in the destination cluster. After the initial full data synchronization is complete, the tablespace of the destination cluster is larger than that of the source database.
• If a table in the source database has no primary key or UNIQUE constraint, and its records are not unique across all fields, duplicate data may occur.

Billing

Supported SQL operations

Limitations

• DTS has limited compatibility with triggers.

  If you synchronize an entire database and a trigger in that database updates a table within the same database, data inconsistency may occur between the source and destination databases. For a solution, see How do I configure a synchronization or migration task if the source database contains triggers?.

• The RENAME TABLE operation has limitations.

  The RENAME TABLE operation may cause data inconsistency. For example, if you synchronize only a specific table and that table is renamed in the source instance during synchronization, data from that table is not synchronized to the destination database. To prevent this issue, select the entire database to which the table belongs as the synchronization object during configuration.

Preparations

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 more information about each topology and related usage notes, see Synchronization topologies.

Procedure

1. Purchase a data synchronization instance. For more information, see Purchase a DTS instance.
   Note When purchasing, select MySQL for the source instance, Apsara PolarDB for the target instance, and One-way Synchronization for the synchronization topology.
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 Return to old version to return to the classic DTS console.

3. In the navigation pane on the left, click Data Synchronization.
4. In the top navigation bar of the Data Synchronization page, select the region where the destination instance is located.
5. Find the purchased data synchronization instance and click Configure Synchronization Channel.
6. Configure the source and destination instances for the synchronization channel.
   

   Category	Configuration	Description
   N/A	Synchronization Job Name	DTS automatically generates a name for the synchronization job. Specify a descriptive name for easy identification. The name does not have to be unique.
   Source Instance Information	Instance Type	Select Self-managed Database on ECS.
   	Instance Region	The region of the source instance that you selected when you purchased the data synchronization instance. This parameter cannot be changed.
   	ECS Instance ID	Select the ID of the ECS instance where the self-managed MySQL database is located.
   	Database Type	Fixed as MySQL. This parameter cannot be changed.
   	Port	Enter the service port of the self-managed MySQL database.
   	Database Account	Enter the database account for connecting to the self-managed MySQL database. Note The database account used for data synchronization must have the REPLICATION CLIENT, REPLICATION SLAVE, SHOW VIEW, and SELECT permissions on all synchronization objects.
   	Database Password	Enter the password for the database account.
   Destination Instance Information	Instance Type	Fixed as PolarDB Instance. This parameter cannot be changed.
   	Instance Region	The region of the destination instance that you selected when you purchased the data synchronization instance. This parameter cannot be changed.
   	PolarDB Instance ID	Select the ID of the destination PolarDB for MySQL cluster.
   	Database Account	Enter the database account for connecting to the PolarDB for MySQL cluster. Note The database account used for data synchronization must have the ALL permissions on the destination synchronization objects.
   	Database Password	Enter the password for the database account.

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 DTS server IP whitelist.

   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 handling mode for existing tables in the destination database and select the synchronization objects.
   

   Configuration Item	Description
   Handling Mode for Existing Tables	Precheck and Report an Error: DTS checks whether a table with the same name exists in the destination database. If no table with the same name exists, the check passes. If a table with the same name exists, the precheck fails, and the data synchronization task does not start. Note If you cannot delete or rename the table in the destination database, you can use the object name mapping feature to avoid name conflicts. For more information, see Map object names in the destination instance. Ignore Errors and Proceed: DTS skips the check for tables with the same name in the destination database. Warning If you select Ignore Errors and Proceed, data inconsistency may occur and pose risks to your business. For example: If the table schemas are the same, records in the destination table with the same primary key value as in the source table are retained during the initial synchronization phase. During the incremental synchronization phase, these records are overwritten. If the table schemas are different, data may fail to be initialized, only some columns may be synchronized, or the synchronization may fail.
   Select Synchronization Objects	In the Source Objects box, click the object that you want to synchronize, and then click the icon to move it to the Selected Objects box. You can select databases and tables as synchronization objects. Note If you select an entire database as a synchronization object, the schema changes of all objects in that database are synchronized to the destination database. By default, the names of the synchronization objects are not changed. To change the name of an object in the destination cluster, use the object name mapping feature. For more information, see Map object names in the destination instance.
   Change Mapped Name	To rename synchronized objects in the destination instance, use the object name mapping feature. For more information, see database, table, and column mapping.
   Replicate Temporary Tables to Destination Database During Online DDL in DMS	If you use Data Management Service (DMS) to perform online DDL changes on the source database, you can specify whether to synchronize the temporary tables generated by these changes. Yes: Synchronize data from temporary tables generated by online DDL changes. Note If online DDL changes generate a large volume of data in temporary tables, data synchronization may be delayed. No: Do not synchronize data from temporary tables. Only the original DDL data from the source database is synchronized. Note This option causes table locking in the destination database.
   Retry Duration for Unstable Connections	By default, if DTS fails to connect to the source or destination database, it retries for 720 minutes (12 hours). You can also specify a custom retry duration. The synchronization task automatically resumes if DTS reconnects within the specified duration; otherwise, the task fails. Note Because DTS instances are charged during the connection retry period, set a custom retry duration based on your business requirements, or release the DTS instance promptly after 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 initial synchronization.
    

    Note There are two types of initial synchronization: initial schema synchronization and initial full data synchronization. If you select both Initial Schema Synchronization and Initial Full Data Synchronization, DTS synchronizes the schema and historical data of the objects from the source database to the target database before the incremental data synchronization starts.

11. In the lower-right corner of the page, click Precheck.

    Note

    • Before the task starts, DTS runs a precheck. The task starts only after it passes the precheck.

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

      • You can fix the issues based on the details and then run the precheck again.

      • If you do not need to fix a warning item, you can click Acknowledge and Ignore or Ignore and Precheck Again to skip the item and run the precheck again.

12. After Precheck Passed is displayed in the Precheck dialog box, close the dialog box. The synchronization task starts automatically.
13. Wait until the synchronization channel is initialized and the task enters the Synchronizing state.
    The Data Synchronization page displays the status of data synchronization jobs.