This document describes how to use the data sync feature of DTS to sync data from MySQL/MariaDB/Percona to TencentDB for MySQL.
The following deployment modes of the source database are supported:
Self-built MySQL, third-party cloud MySQL, and TencentDB for MySQL.
Self-built MariaDB and TencentDB for MariaDB.
Self-built Percona.
This document describes how to sync data from MySQL to TencentDB for MySQL. The requirements and steps of data sync from MariaDB and Percona to TencentDB for MySQL are basically the same.
Business Impact
1. When DTS performs full data synchronization, it reads all data from the source database once, which increases the load on the source database. If your database has low specifications, it is recommended to schedule synchronization tasks during off-peak hours or reduce the DTS rate before starting the task.
The impact varies with the specifications of the source database. For example, with a source database of 8-core 16G, DTS tasks default to 8-thread concurrency (adjustable). In a network without bottlenecks, the impact on the source database’s performance is as follows:
DTS full export phase: occupies approximately 18%-45% of the source database’s CPU, increases query pressure by about 40-60 MB/s, and occupies roughly 8 active session connections.
DTS incremental export phase: Poses minimal pressure on the source database, with only one connection monitoring the binlog in real-time.
2. Sync is implemented without locks by default, during which no global lock (the FTWRL lock) is added to the source database, and only tables without a primary key are locked.
3. During data sync, DTS will use the account that executes the sync task to write the system database __tencentdb__ in the source database to record the data comparison information during the sync task.
To ensure that subsequent data problems can be located, the __tencentdb__ system database in the source database will not be deleted after the sync task ends.
The __tencentdb__ system database uses a single-threaded connection wait mechanism and occupies a very small space, about 0.01%–0.1% of the storage space of the source database; for example, if the source database is 50 GB, __tencentdb__ will be about 5–50 MB. Therefore, it has almost no impact on the performance of the source database and will not preempt resources.
Preparation
1. Ensure that the access channel between DTS and the database is established based on the access type you intend to use. For details, see Network Preparation Overview.
Self-built IDC databases/Databases from other cloud providers: Access methods include Public Network/Direct Connect/VPN Access/CCN.
Self-built databases on CVM: Select Self-build on CVM as the access method.
Tencent Cloud database instances: Select Database as the access method.
Lightweight databases on Lighthouse: Select Public Network as the access method.
2. Grant the required permissions to the task execution account in the source database, as detailed below:
GRANT RELOAD,LOCKTABLES,REPLICATION CLIENT,REPLICATION SLAVE,SHOWDATABASES,SHOWVIEW,PROCESS,SELECTON*.*TO'account'@'%' IDENTIFIED BY' password ';
//If the Source Database is Alibaba Cloud Database, SHOW DATABASES authorization is not required, but at least one Non-system Database needs to be created. Otherwise, the Pre-validation Task will fail. Authorization is required for other non-Alibaba Cloud Database scenarios. For Alibaba Cloud Database authorization, please refer to https://help.aliyun.com/document_detail/96101.html
1. Only synchronization of basic tables, views, stored procedures, and functions are supported.
2. Related data objects should be synchronized together to avoid synchronization failure. Common relationships include views referencing tables, views referencing other views, primary-foreign key relationships tables, etc.
3. It is recommended to synchronize tables with primary keys or non-null unique keys to avoid data duplication.
4. Synchronizing the entire source instance is not supported. If databases are created on the source during subsequent synchronization tasks, such as through the CREATE DATABASE operation, it is recommended to either create these database objects on the target before starting the synchronization task or add the new database objects by modifying the synchronization configuration after the task has started.
5. If CREATE TABLE or RENAME TABLE operations occur on the source during the synchronization task, it is recommended to select the entire database containing the table during the object configuration phase. Otherwise, the created tables or renamed tables will not be synchronized to the target.
6. When views, stored procedures, and functions are synchronized, DTS checks whether user1, corresponding to the DEFINER ( [DEFINER = user1]) in the source database, matches the synchronization account user2. If they do not match, after synchronization, DTS modifies the SQL SECURITY attribute of user1 in the target database from DEFINER to INVOKER ( [INVOKER = user1]), and sets the DEFINER in the target database to the synchronization account user2 ([DEFINER = synchronization account user2]). If the view definitions in the source database are overly complex, the synchronization task may fail.
7. If the source database is Alibaba Cloud MySQL, tables without primary keys cannot be synchronized in MySQL 5.6, while MySQL 5.7 and later versions have no such restriction. If the source database is AWS MySQL, tables without primary keys cannot be synchronized.
8. Only databases using the InnoDB, MyISAM, or TokuDB engines are supported for synchronization. Tables using other database engines will be skipped by default. If the source database contains compressed TokuDB engine data, the target database should support compression mode for synchronization; otherwise, the task will fail.
9. When the target database is a Tencent Cloud MySQL instance using the RocksDB engine, the source InnoDB/TokuDB engines can be converted to the target RocksDB engine. However, there are specific restrictions for converting from InnoDB to RocksDB:
9.1 If the primary key or part of the primary key in a table to be migrated from the source database is of the types TINYTEXT, TEXT, MEDIUMTEXT, or LONGTEXT, these types do not support value equality on the target side. Therefore, SQL statements relying on value equality (such as DELETE FROM table_name WHERE primary_key_column = 'some text') will not work after synchronization, potentially leading to data inconsistency between the source and target databases.
9.2 During data consistency verification, if the primary key or part of the primary key in a table to be migrated from the source database is of the types TINYTEXT, TEXT, MEDIUMTEXT, or LONGTEXT, and the table contains more than 50,000 rows, the table will be skipped, and no consistency check will be performed.
9.3 Due to differences in the underlying storage of DOUBLE data types between RocksDB and InnoDB engines, if the source DOUBLE value approaches its upper limit (such as -1.7976931348623157E+308), false negatives may occur when creating consistency check tasks. While the actual data may be consistent, the check results might show discrepancies.
9.4 Tables involving partitions are not supported for migration.
Data Types
1. During incremental synchronization, if the source database generates Binlog statements in the STATEMENT format, synchronization will fail.
2. If there are gaps in the GTID of the source database’s Binlog, it may impact synchronization task performance and lead to task failure.
3. Scenarios where DML and DDL statements are included in the same transaction are not supported. In such cases, tasks will fail.
4. Geometry-related data types are not supported. Tasks involving these data types will fail.
5. ALTER VIEW statements are not supported. Such statements will be skipped and not synchronized.
6. JSON data type specifics for synchronization linkages between MySQL and TDSQL-C MySQL.
6.1 For data requiring precision, it is recommended to use types such as DECIMAL for separate recording rather than JSON, because MySQL’s handling of floating-point numbers in JSON can result in a loss of precision for numbers with six or more decimal places.
6.2 DTS handling of floating-point numbers in JSON types.
6.2.1 When MySQL 5.7 or earlier is the target database, due to issues inherent in MySQL 5.7, floating-point numbers x.0 in JSON on the source may be converted to x on the target during synchronization. For example, source data {"a":12.0} would be synchronized to the target as {"a":12}.
6.2.2 When MySQL 5.7 or earlier is the source database and MySQL 8.0 or later is the target database, due to issues inherent in MySQL 5.7, floating-point numbers x.0 in JSON on the source might appear as x when read via SELECT. However, the binlog still records the value as a floating-point type, and DTS processes it accordingly, resulting in x.0 on the target database. For example, source data {"a":12.0} may appear as {"a":12} in SELECT results but will be synchronized as {"a":12.0} to the target.
6.2.3 Regardless of the version, if the floating-point number in the JSON type from the source database has more than one decimal place, such as {"a":12.00} or {"a":12.000}, it will be synchronized to the target database as {"a":12.0}.
HA Switch
If the source is a non-GTID instance, DTS does not support HA switching for the source. Any HA switch in the source MySQL may cause the DTS incremental synchronization to be interrupted.
Primary Key Conflict Policy
If you select Report for the primary key conflict policy during the full synchronization phase, avoid performing dual writes on the target database. Otherwise, in case of a primary key conflict, DTS will not report an error but will overwrite the target database data with the source database data.
Operation Restrictions
Avoid performing the following operations during the synchronization process to prevent synchronization task failures:
1. In scenarios involving schema initialization and full data initialization, DDL operations are not supported during the Full Data Export and Full Data Import steps.
2. Do not modify or delete user information (including usernames, passwords, and permissions) or port numbers in the source or target databases.
3. Avoid executing operations to clear Binlog on the source database.
Synchronizable SQL operations
Operation Type
SQL Statements
DML
INSERT,UPDATE,DELETE
DDL
DROP DATABASE,ALTER DATABASE,CREATE TABLE,ALTER TABLE,DROP TABLE,TRUNCATE TABLE,RENAEM TABLE,CREATE VIEW,DROP VIEW,CREATE INDEX,DROP INDEX
Note：
DDL statements involving partitions cannot be synced.
Directions
1. Log in to the data sync purchase page, select appropriate configuration items, and click Buy Now.
Parameter
Description
Billing Mode
Monthly subscription and pay-as-you-go billing are supported.
Source Instance Type
Select MySQL, which cannot be changed once configured.
Source Instance Region
Select the source instance region, which cannot be changed once configured.
Target Instance Type
Select MySQL, which cannot be changed once configured.
Target Instance Region
Select the target instance region, which cannot be changed once configured.
Specification
Select a specification based on your business needs. The higher the specification, the higher the performance.
2. After successful purchase, return to the data sync list, and you can see the newly created data sync task. You need to configure it before you can use it.
3. In the data sync list, click Configure in the Operation column to enter the sync task configuration page.
4. On the sync task configuration page, configure the source and target instances and their accounts and passwords, test the connectivity, and click Next.
Task Configuration
Parameter
Description
Task Name
DTS automatically generates a task name. It is recommended to change it to a meaningful name for easier task identification.
Running Mode
Immediate execution: The task starts immediately after passing the pre-checks.
Scheduled execution: Set a specific start time for the task. The task does not start immediately after passing the pre-checks but waits until the scheduled time.
Automatic Retry
Once it is enabled, if a synchronization task is temporarily interrupted due to network issues or similar causes, DTS will automatically retry and resume the task within the specified time frame without requiring manual intervention.
The supported range is 5 to 720 minutes.
Source Database Settings
Parameter
Description
Source Instance Type
The type of source instance selected at the time of purchase, which cannot be modified.
Source Instance Region
The region of the source instance selected at the time of purchase, which cannot be modified.
Service Provider
For self-built databases (including those on CVM), Tencent Cloud database instances, and lightweight databases, select Normal.
For third-party cloud vendor databases, select the corresponding service provider.
Access Type
Choose based on the deployment setup of the source database. Different deployment scenarios require specific network configurations. For details, see Network Readiness Overview.
For source databases hosted on self-built IDC environments or other cloud providers, the access types include Public Network / Direct Connect / VPN Access / CCN.
Public Network: The source database can be accessed via a public IP address.
Direct Connect: The source database can connect to Tencent Cloud VPC using DC.
VPN Access: The source database can connect to Tencent Cloud VPC using VPN Connections.
CCN: The source database can connect to Tencent Cloud VPC using CCN.
VPC Access: Both the source data and target database are deployed on Tencent Cloud and within a VPC. If you need to use the VPC access type, submit a ticket for application.
If the source database is self-built on a CVM, select Self-Build on CVM as the access type.
If the source database is a Tencent Cloud database instance, select Database as the access type.
Public Network
When Public Network is selected as the Access Type, configure the following parameters:
Host Address: The IP address or domain name of the source database.
Port: The port used by the source database.
Self-Build on CVM
When Self-Build on CVM is selected as the Access Type, configure the following parameters:
CVM Instance: The instance ID of the CVM.
Port: The port used by the source database.
Direct Connect
When Direct Connect is selected as the Access Type, configure the following parameters:
VPC-Based Direct Connect Gateway: Only VPC Direct Connect Gateway is supported for DC. Confirm the network type associated with the gateway.
VPC: Select the VPC and subnet.
Host Address: The IP address of the source database.
Port: The port used by the source database.
VPN Access
When VPN Access is selected as the Access Type, configure the following parameters:
VPN Gateway: Select the VPN gateway by specifying its ID.
VPC: Select the VPC and subnet associated with the VPN gateway.
Host Address: The IP address of the source database.
Port: The port used by the source database.
Database
When Database is selected as the Access Type, configure the following parameters:
Cross-/Intra-Account
Intra-account: The source and target database instances belong to the same Tencent Cloud root account.
Host Network Environment: Select based on the actual scenario. For example, select Tencent Cloud if the source database is a Tencent Cloud database instance; Self-built IDC if it is a self-built IDC database, or the corresponding network for databases from other cloud providers.
Host Address: The host IP address of the source database.
Port: The port used by the source database.
CCN Instance Account Type
My account: The CCN resources and target database belong to the same Tencent Cloud root account.
Other account: The CCN resources and target database belong to different Tencent Cloud root accounts.
VPC-Based CCN Instance: Name of the CCN instance.
CCN-associated VPC and subnet: The CCN-associated VPC refers to the VPC connected to the synchronization linkage within the CCN. Select a VPC from all the VPCs associated with the CCN, excluding the VPC of the source database.
Region of the VPC: The region of the VPC used for connection should match the region of the source database selected during task purchase. If there is a mismatch, DTS will automatically update the source database region to align with the VPC region.
Account/Password
Account/Password: The account and password of the source database.
Connection Method
Currently, if users want to experience the SSL secure connection feature, Submit a ticket for application.
SSL secure connection refers to encrypting the transmission linkage between DTS and the database using Secure Socket Layer (SSL).
Enabling SSL secure connection may increase the connection response time of the database. Generally, Tencent Cloud’s private network linkage is relatively secure, and enabling SSL secure connection is not necessary. However, for scenarios using public network, DC, or other transmission methods where higher data security is required, enabling SSL secure connection is recommended. Ensure that SSL encryption is enabled in the database before selecting SSL secure connection.
Target Database Settings
The parameter configuration for the target database is similar to that of the source database. Select the access type based on the actual situation; further explanation is omitted here.
5. On the Set sync options and objects page, set the data initialization, data sync, and sync object options and click Save and Go Next.
Note:
If you only select Full data initialization for Initialization Type, the system will assume by default that you have created the table structures in the target database and will neither sync table structures nor check whether the source and target databases have tables with the same name. Therefore, if you select Precheck and report error for If Target Already Exists, the precheck and error reporting feature won't take effect.
If you want to rename a table (for example, rename table A table B) during sync, you must select the entire database (or entire instance) where table A resides rather than only table A as the sync object; otherwise, the system will report an error.
Category
Parameter
Description
Data Initialization Option
Initialization Type
Structure initialization: Table structures in the source instance will be initialized into the target instance before the sync task runs.
Full data initialization: Data in the source instance will be initialized into the target database before the sync task runs. In scenarios where only full data initialization is selected, users need to create the table structure in the target database in advance.
Both options are selected by default, and you can deselect them as needed.
If Target Already Exists
Precheck and report error: If a table with the same name exists in both the source and target databases, an error will be reported, and the task will stop.
Ignore and execute: Full and incremental data will be directly added to tables in the target instance.
Data Sync Option
Conflict Resolution Method
Report: If a primary key conflict is found during data sync, an error will be reported, and the data sync task will be paused.
Ignore: If a primary key conflict is found during data sync, the primary key record in the target database will be retained.
Overwrite: If a primary key conflict is found during data sync, the primary key record in the source database will overwrite that in the target database.
SQL Type
Supported operations include INSERT, UPDATE, DELETE, and DDL. If you select Custom DDL, you can select different DDL statement sync policies as needed.
Sync Object Option
Database and Table Objects of Source Instance
Select the objects to be synced. You can select basic databases, tables, views, procedures, and functions.The sync of advanced objects is a one-time operation: only advanced objects already in the source database before the task start can be synced, while those added to the source database after the task start will not be synced to the target database. For more information, see Syncing Advanced Object.
Selected Object
Database/Table mapping (renaming) is supported. Hover over a database or table name, click the displayed Edit icon, and enter a new name in the pop-up window.
After selecting individual table objects one by one, you can configure Where conditions. For details, see Where Condition Filter.
When advanced objects are selected for sync, we recommend you not rename databases/tables; otherwise, sync of the advanced objects may fail.
Whether to synchronize online DDL temporary tables
If tools such as gh-ost or pt-osc are used to perform Online DDL operations on the source database tables, DTS supports synchronizing the temporary tables generated by these Online DDL changes to the target database.
If gh-ost is selected, DTS will synchronize the temporary table names (_table name_ghc, _table name_gho, and _table name_del) generated by the gh-ost tool to the target database.
If pt-osc is selected, DTS will synchronize the temporary table names (_table name_new and _table name_old) generated by the pt-osc tool to the target database.
This configuration is generally not required. It should only be adjusted when the database's configuration is insufficient to handle the default DTS concurrent thread count and RPS. For more details, see Throttling.
6. On the Verify task page, complete the verification. After all check items are passed, click Start Task.
If the verification fails, fix the problem as instructed in Check Item Overview and initiate the verification again.
Failed: It indicates that a check item fails and the task is blocked. You need to fix the problem and run the verification task again.
Alarm: It indicates that a check item doesn't completely meet the requirements, and the task can be continued, but the business will be affected. You need to assess whether to ignore the alarm or fix the problem and continue the task based on the alarm message.
7. Return to the data sync task list, and you can see that the task has entered the Running status.
Note:
You can click More > Stop in the Operation column to stop a sync task. You need to ensure that data sync has been completed before stopping the task.
8. (Optional) You can click a task name to enter the task details page and view the task initialization status and monitoring data.
