ApsaraDB for SelectDB:Import data by using DTS

For a self-managed MySQL source database:

• If a primary/secondary switchover occurs in the source database during synchronization, the task fails.

• DTS calculates latency by comparing the timestamp of the last synchronized record with the current time. If no DML operations run for a long time in the source database, latency reporting may become inaccurate. If latency appears too high, run a DML operation in the source database to update the latency.

Note

If you select a full database for synchronization, create a heartbeat table. Update or write to this table every second.

• DTS periodically runs the CREATE DATABASE IF NOT EXISTS `test` command in the source database to advance the binary log offset.

• If your source database is Amazon Aurora MySQL or another clustered MySQL instance, ensure the domain name or IP address used in the task configuration—and its DNS resolution—always points to a read/write (RW) node. Otherwise, synchronization may fail.

For an ApsaraDB RDS for MySQL source database:

• Read-only instances—such as ApsaraDB RDS for MySQL 5.6 read-only instances—that do not record transaction logs cannot serve as source databases.

• DTS periodically runs the CREATE DATABASE IF NOT EXISTS `test` command in the source database to advance the binary log offset.

Permissions

• To view the permissions of the source database account, see MySQL permissions.

• To modify the permissions of the source database account, see Modify account permissions.

Binary logging on the source database

Binary logging is enabled by default for ApsaraDB RDS for MySQL. Verify the following settings before you configure the synchronization task:

• Row image format — Make sure that binlog_row_image is set to full. Otherwise, an error is reported during the precheck and the data synchronization task cannot start. For information about how to configure the parameter, see Set instance parameters.

• Self-managed MySQL databases — If the source instance is a self-managed MySQL database, enable binary logging, set binlog_format to row, and set binlog_row_image to full.

• Dual-primary clusters — If the source self-managed MySQL instance is a dual-primary cluster in which each node is a replica of the other, enable the log_slave_updates parameter so that DTS can obtain all binary logs. For more information, see Create an account for a self-managed MySQL database and configure the binary log.

• Log retention — DTS requires the local binary logs of the source database to be retained for at least seven days. Otherwise, DTS may fail to obtain the binary logs and the task may fail. In extreme cases, data inconsistency or data loss may occur. Issues that are caused by a binary log retention period shorter than the period required by DTS are not covered by the DTS Service Level Agreement (SLA). If the source is an ApsaraDB RDS for MySQL instance, see Manage local logs (binary logs).

Destination tables

If you create the destination tables yourself instead of letting DTS synchronize the schemas, create tables that use the unique data model in the destination ApsaraDB for SelectDB instance before you start the task. For the column types to use, see Data type mappings. For the decision itself, see Configure the synchronization objects and advanced settings.

Configure the source and destination databases

1. Log on to the ApsaraDB for SelectDB console.

2. In the top navigation bar, select the region in which your instance resides.

3. On the Instances page, click the ID of the instance to go to the Instance Details page.

4. On the Instance Details page, click Data Pipeline in the left-side navigation pane. In the top menu bar, select the Data Synchronization tab.

5. Click Create Synchronization Task and configure the source and destination database information as described in the following table.

   Category	Setting	Description
   Task information	Task Name	DTS automatically generates a task name. We recommend that you specify a descriptive name for easy identification. The name does not need to be unique.
   Source database	Database Type	Select MySQL.
   	Access Method	Select Alibaba Cloud Instance.
   	Instance Region	Select the region of the source ApsaraDB RDS for MySQL instance.
   	RDS Instance ID	Select the ID of the source ApsaraDB RDS for MySQL instance.
   	Database Account	Enter the database account of the source ApsaraDB RDS for MySQL instance. For information about the required permissions, see Permissions.
   	Database Password	Enter the password for the specified database account.
   	Encryption	Select Non-encrypted or SSL-encrypted as needed. If you set this to SSL-encrypted, you must enable SSL encryption for the RDS for MySQL instance beforehand. For more information, see Use a cloud certificate to quickly enable SSL link encryption.
   Destination database	Database Type	Select SelectDB.
   	Access Method	Select Alibaba Cloud Instance.
   	Instance Region	Select the region of the destination ApsaraDB for SelectDB instance.
   	Instance ID	Select the ID of the destination ApsaraDB for SelectDB instance.
   	Database Account	Enter the database account of the destination ApsaraDB for SelectDB instance. For information about the required permissions, see Permissions.
   	Database Password	Enter the password for the specified database account.

6. After you complete the configuration, click Test Connection to Proceed at the bottom of the page.

   If the source or destination database is an Alibaba Cloud database instance, such as ApsaraDB for SelectDB, ApsaraDB RDS for MySQL, or ApsaraDB for MongoDB, DTS automatically adds the IP addresses of DTS servers in the corresponding region to the allowlist of the Alibaba Cloud database instance.

Configure the synchronization objects and advanced settings

1. Configure the task objects and advanced settings. The following table describes the settings.

   Setting	Description
   Synchronization Types	DTS always selects Incremental Data Synchronization. By default, you must also select Schema Synchronization and Full Data Synchronization. After the precheck, DTS initializes the destination cluster with the full data of the selected source objects, which serves as the baseline for subsequent incremental synchronization. Whether you select Schema Synchronization determines what you must prepare in the destination instance. For the two branches, see the description that follows this table.
   Processing Mode of Conflicting Tables	Precheck and Report Errors: Checks for tables with the same names in the destination database. If any tables with the same names are found, an error is reported during the precheck and the data synchronization task does not start. Otherwise, the precheck is successful. Note If you cannot delete or rename the table with the same name in the destination database, you can map it to a different name in the destination. For more information, see Object name mapping. Ignore Errors and Proceed: Skips the check for tables with the same name in the destination database. Warning Selecting Ignore Errors and Proceed may cause data inconsistency and put your business at risk. For example: If the table schemas are consistent and a record in the destination database has the same primary key or unique key value as a record in the source database: During full data synchronization, DTS retains the destination record and skips the source record. During incremental synchronization, DTS overwrites the destination record with the source record. If the table schemas are inconsistent, data initialization may fail. This can result in only partial data synchronization or a complete synchronization failure. Use with caution.
   Capitalization of Object Names in Destination Instance	Configure the capitalization policy for the database names, table names, and column names of the synchronization objects in the destination instance. By default, DTS Default Policy is used: database names and table names are mapped to lowercase, and the capitalization of column names is the same as in the source database. You can also select another policy, such as Same as Source or Same as Target Default. For more information, see Case policy for target database object names.
   Source Objects	In the Source Objects box, click the objects that you want to synchronize, and then click to move them to the Selected Objects box. You can select synchronization objects at the database, table, or column level. If you select objects at the database level, data synchronization for tables that are added later is not supported. If you may add tables while you use DTS, select synchronization objects at the table level. You can modify them later by using Modify Synchronization Objects in the Actions column of the task list. If you select objects at the table level and need to edit them, such as mapping column names, a single synchronization task supports up to 1,000 tables. If you exceed this limit, an error is returned after you submit the task. In this case, split the tables and configure multiple tasks in batches, or configure a task that synchronizes the entire database.
   Selected Objects	To rename a single object in the destination instance, right-click the object in the Selected Objects box. For more information, see Map a single object name. To rename multiple objects in bulk, click Batch Edit in the upper-right corner of the Selected Objects box. For more information, see Map multiple object names in bulk. Note: To select the SQL operations to synchronize at the database or table level, right-click the object that you want to synchronize in Selected Objects, and then select the required SQL operations in the dialog box that appears. For the supported operations, see SQL operations supported for incremental synchronization. To filter data by using a WHERE condition, right-click the table that you want to synchronize in Selected Objects, and then set the filter condition in the dialog box that appears. For more information, see Configure filter conditions. If you use the object name mapping feature, the synchronization of other objects that depend on the mapped object may fail.

   Your selection for Synchronization Types determines who creates the destination tables:

   • If you select Schema Synchronization — You can define the destination table structure in the next stage, Configure the destination table schemas and fields. If a source table has no primary key or unique constraint, configure the table to use the unique data model in that stage, and manually specify the primary key column.

   • If you do not select Schema Synchronization — Create tables that use the unique data model with the required schema in the destination ApsaraDB for SelectDB instance in advance. The stage for configuring the destination table schemas and fields is not available in this case. For more information, see Data type mappings and Data Models.

2. At the bottom of the page, you can expand Advanced (optional) to configure advanced settings. The following table describes the advanced settings.

Configure the destination table schemas and fields

1. (Optional) After you complete the preceding configurations, click Next: Configure Database and Table Fields to set the Primary Key Column, Distribution Key, and Engine settings of the tables that you want to synchronize in the destination instance.

   • This step is available only if Synchronization Types includes Schema Synchronization when you configure the task objects. You can set Definition Status to All and then make modifications.

   • For Primary Key Column, you can select multiple columns to form a composite primary key. You must also select one or more columns from Primary Key Column as the Distribution Key. For Engine, you can select only unique.

Save the task settings and run a precheck

DTS runs a precheck before the synchronization task starts. The task can start only after the precheck is passed.

1. Save the task and run a precheck.

   • To view the parameters that are used when you call an API operation to configure this instance, move the pointer over the Next: Save Task Settings and Precheck button. Then, click Preview OpenAPI parameters in the message that appears.

   • If you do not need to view the API parameters or have finished viewing them, click Next: Save Task Settings and Precheck at the bottom of the page.

2. Handle the precheck result as follows:

   • Failed check items — Click View Details next to the failed check item, fix the issue as prompted, and then run the precheck again.

   • Warning check items that cannot be ignored — Click View Details next to the failed check item, fix the issue as prompted, and then run the precheck again.

   • Warning check items that you can ignore without fixing them — Click View Alert Details, Confirm Ignore, OK, and Run Pre-check Again in sequence to skip the warning check items and run the precheck again. If you skip warning check items, issues such as data inconsistency may occur and put your business at risk.

3. When Precheck Pass Rate shows 100%, click Next: Purchase.

Purchase the instance and start the task

1. On the Purchase Instance page, select the billing method and the synchronization instance class of the data synchronization instance. For more information, see the following table.

   Category	Parameter	Description
   New Instance Class	Billing method	Subscription: You pay for the instance when you create it. This method is suitable for long-term use. It is more cost-effective than pay-as-you-go, and the longer the subscription period, the larger the discount. Pay-as-you-go: You are billed by hour. This method is suitable for short-term use. You can release the instance immediately after use to reduce costs.
   	Resource group settings	The resource group to which the instance belongs. The default value is default resource group. For more information, see What is Resource Management.
   	Synchronization instance class	DTS provides synchronization instance classes with different performance levels. The instance class affects the synchronization speed. Select an instance class based on your business scenario. For more information, see Specifications of data synchronization instances.
   	Subscription duration	If you use the subscription billing method, select the duration and the number of instances. You can select 1 to 9 months for a monthly subscription, or 1, 2, 3, or 5 years for a yearly subscription. Note: This option is displayed only when the billing method is Subscription.

2. After you complete the configuration, read and select Data Transmission (Pay-As-You-Go) Terms of Service.

3. Click Buy and Start.

   The synchronization task starts. You can view the task progress on the Data Synchronization page.