This document describes how to use the data sync feature of DTS to sync data from TDSQL for MySQL to TDSQL for MySQL. The supported scenarios are as follows:
Onto Tencent Cloud: Sync from self-built TDSQL for MySQL to Tencent Cloud TDSQL for MySQL
Onto Tencent Cloud: Sync from Tencent Cloud TDSQL for MySQL to Tencent Cloud TDSQL for MySQL
Off Tencent Cloud: Sync from Tencent Cloud TDSQL for MySQL to self-built TDSQL for MySQL.
Note
During full data sync, DTS consumes certain source database resources, which may increase the load and pressure of the source database. If your database configuration is low, we recommend you sync the data during off-peak hours.
To avoid duplicate data, make sure that the tables to be synced have a primary key or non-null unique key.
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.
Prerequisites
The source and target databases must meet the requirements for the sync feature and version as instructed in Databases Supported by Data Sync.
Permissions required of the source database:
GRANT RELOAD,LOCKTABLES,REPLICATION CLIENT,REPLICATION SLAVE,SELECTON*.*TO'migration account'@'%' IDENTIFIED BY'migration password';
Permissions required of the target database: ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER, and UPDATE.
Application Restrictions
Only basic tables can be synced, and objects such as views, functions, triggers, and procedures are not supported.
If the source MySQL database is a non-GTID database, DTS doesn't support HA switch for it. If it is switched, DTS incremental sync may be interrupted.
Only data with the InnoDB database engine can be synced. Tables with other engines will trigger an error during task verification.
Correlated data objects must be synced at the same time; otherwise, sync will fail.
During incremental sync, if the source database has binlog statements in the STATEMENT format, sync will fail.
Level-two partitioned tables as described in Subpartitioning cannot be synced. If they are included in the synced tables, the task will be paused, with an error reported.
If TDSQL for MySQL (MariaDB) is used as the source or target database, two-way sync is not supported.
The TDSQL sync feature adopts a row-level concurrency policy to speed up the incremental sync. Therefore, during the incremental sync, intermediate values of transactions may be observed for a very short period of time in the target database, but the data in the source and target databases will remain consistent eventually.
Currently, the primary key conflict resolution policy can only be Overwrite. For primary key data conflicts in the incremental sync phase, conflict overwrite will be performed directly. However, the task will report an error for conflicts during full data initialization.
Scenarios that contain both DML and DDL statements in the same transaction are not supported. They will trigger errors during task execution.
Geometry data types are not supported. They will trigger errors during task execution.
If the source database is self-built, the scaling of the source database is not supported. When the SET node is configured for the first time, the SET node cannot be added or deleted once the connection is saved. If you need to add or delete the SET node, you need to create the sync task again.
Operation Restrictions
During the sync, do not perform the following operations; otherwise, the sync task will fail:
Do not modify or delete user information (including username, password, and permissions) in the source and target databases and port numbers.
Do not write binlog data in the STATEMENT format into the source database.
Do not clear binlogs in the source database.
Do not delete the system table __tencentdb__ during incremental sync.
Synchronizable SQL Operations
Operation Type
SQL Statement
DML
INSERT, UPDATE, DELETE
DDL
CREATE DATABASE, DROP DATABASE, ALTER DATABASE, CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE, RENAME TABLE, CREATE INDEX, DROP INDEX
Note
CREATE TABLE table name AS SELECT is not supported.
Environment Requirements
Type
Environment Requirements
Requirements for the source database
The source and target databases can be connected.
Requirements for instance parameters:
The server_id parameter in the source database must be set manually and cannot be 0.
row_format for the source databases/tables cannot be set to FIXED.
The values of the lower_case_table_names variable in both the source and target databases must be the same.
The connect_timeout variable in the source database must be greater than or equal to 10.
Requirements for binlog parameters:
The log_bin variable in the source database must be set to ON.
The binlog_format variable in the source database must be set to ROW.
The binlog_row_image variable in the source database must be set to FULL.
On MySQL 5.6 or later, if the gtid_mode variable is not ON, an alarm will be triggered. We recommend you enable gtid_mode.
It is not allowed to set do_db and ignore_db.
If the source instance is a replica database, the log_slave_updates variable must be set to ON.
We recommend that you retain the binlog of the source database for at least three days; otherwise, the task cannot be resumed from the checkpoint and will fail.
Foreign key dependency:
Foreign key dependency can be set to only one of the following two types: NO ACTION and RESTRICT.
During partial table sync, all tables with foreign key dependency must be migrated.
The environment variable innodb_stats_on_metadata must be set to OFF.
Requirements for the target database
If the target database is a distributed database, we recommend you manually create a partitioned table and plan the shardkey in advance; otherwise, DTS will create a table in the target database based on the table style of the source database. If the source database is a standalone instance, the target database will be created as a single table.
The target database version must be later than or equal to the source database version.
The target database must have sufficient storage space. If you select Full data initialization as the initialization type, the target database space must be at least 1.2 times the space of databases/tables to be synced in the source database.
The max_allowed_packet parameter of the target database must be set to 4 MB or above.
Directions
The operations for migrating self-built TDSQL for MySQL to cloud TDSQL for MySQL are basically the same as when going off the cloud. The following is an example of migrating self-built TDSQL MySQL to Tencent Cloud TDSQL MySQL.
Monthly subscription and pay-as-you-go billing are supported.
Source Instance Type
Select TDSQL for MySQL, which cannot be changed after purchase.
TDSQL for MySQL is divided into three kernel versions (MySQL/MariaDB/Percona). Here, select TDSQL for MySQL as there is no need to distinguish the kernel versions.
Source Instance Region
Select the source region of the DTS task. If the source database is TencentDB, select its region here. If the source database is self-built, select the region closest to it, so that DTS can choose the optimal sycn path to reduce the sycn time.
Target Instance Type
Select TDSQL for MySQL, which cannot be changed after purchase.
Target Instance Region
Select the target instance region, which cannot be changed after purchase.
Specification
Currently, only the Standard Edition is supported.
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 task 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 databases and their accounts and passwords, test the connectivity, and click Next.
Setting Items
Parameter
Description
Task Configuration
Task Name
DTS will automatically generate a task name, which is customizable.
Running Mode
Immediate execution and scheduled execution are supported.
Source Instance Settings
Source Instance Type
The source database type selected during purchase, which cannot be changed.
Source Instance Region
The source instance region selected during purchase, which cannot be changed.
Public Network: The source database can be accessed through a public IP.
Self-Build on CVM: The source database is deployed in a CVM instance.
Direct Connect: The source database can be interconnected with VPCs through Direct Connect.
VPN Access: The source database can be interconnected with VPCs through VPN Connections.
Database: The source database is a TencentDB instance.
CCN: The source database can be interconnected with VPCs through CCN.
VPC: The source and target databases are both deployed in Tencent Cloud VPCs. To use the VPC access type, submit a ticket for application.
VPC-based Direct Connect Gateway/VPN Gateway
Only VPC-based Direct Connect gateway is supported. You need to confirm the network type associated with the gateway. For VPN Gateway, select a VPN Gateway instance.
Virtual Private Cloud (VPC)
Select a VPC and subnet associated with the VPC-based Direct Connect Gateway or VPN Gateway.
Host - Proxy:
Enter the proxy address and port.
Node - SET
Enter the TDSQL for MySQL shard node IP. You need to enter a node IP each shard, and separate multiple nodes by line break.
If the source database is self-built, when the SET node is configured for the first time, the SET node cannot be added or deleted once the connection is saved. If you need to add or delete the SET node, you need to create the sync task again.
Account
Account of the source instance, which must have the required permissions.
Password
Password of the source instance account.
Target Instance Settings
Target Instance Type
The target instance type selected during purchase, which cannot be changed.
Target Instance Region
The target instance region selected during purchase, which cannot be changed.
Access Type
Select a type based on your scenario. In this scenario, select Database. If the source instance selects Database for access, the target instance selects "Public Network/Self-Build on CVM/Direct Connect/VPN Access/Database/CCN".
Instance ID
Select the ID of the target instance.
Account
Account of the target instance, which must have the required permissions.
Password
Password of the target instance account.
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 want to use a tool such as gh-ost and pt-osc to perform online DDL operations on a table during sync, you must select the entire database (or entire instance) where the table resides rather than only the table as the sync object; otherwise, the temporary table data generated by online DDL changes cannot be synced to the target database.
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 sycn object; otherwise, after the renaming operation, the data in table B will not be synced to the target database.
Database/Table mapping: Hover over the right side of a selected object, and the Edit icon will be displayed. Click it and then enter a mapping name in the pop-up window.
Setting Items
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 instance before the sync task runs. If you select Full data initialization, you 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 you select Full data initialization only, you need to create the table structures in the target database in advance.
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
Overwrite: If a primary key conflict is found during data sync, the system will use the primary key record in the source database to overwrite that in the target database.
SQL Type
The following operations are supported: INSERT, DELETE, UPDATE, and DDL.
Sync Object Option
Database and Table Objects of Source Instance
Select the objects to be synced. You can select databases and tables.
Selected Object
It displays the selected sync objects, and database/table mapping is supported.
6. On the task verification 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. Before doing so, ensure that data sync has been completed.
8. (Optional) You can click a task ID to enter the task details page and view the task initialization status and monitoring data.
Yes
No
Was this page helpful?