tencent cloud

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.

Notes

• During full data sync, DTS consumes certain source instance 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.
• 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.
• 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,LOCK TABLES,REPLICATION CLIENT,REPLICATION SLAVE,SHOW VIEW,PROCESS,SELECT ON *.* TO 'account'@'%' IDENTIFIED BY 'password';
  GRANT ALL PRIVILEGES ON `__tencentdb__`.* TO 'account'@'%'; 
  FLUSH PRIVILEGES;
  

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

Use limits

• Only basic tables, views, procedures, and functions can be synced.
• When views, procedures, and functions are synced, DTS will check whether user1 corresponding to DEFINER ([DEFINER = user1]) in the source database is the same as the sync account user2, and if not, DTS will change the SQL SECURITY attribute of user1 in the target database from DEFINER to INVOKER ([INVOKER = user1]) after the sync, and set the DEFINER in the target database to the sync account user2 ([DEFINER = sync account user2]). If the view definition in the source database is too complex, the task may fail.
• If the source MySQL database is a non-GTID instance, DTS doesn't support HA switch for it. If it is switched, DTS incremental sync may be interrupted.
• Only data with the following three database engines can be synced: InnoDB, MyISAM, and TokuDB. Tables with other engines will be skipped during sync by default.
• Correlated data objects must be synced together; otherwise, sync will fail. Common correlations include table reference by views, view reference by views, and tables correlated through primary/foreign keys.
• During incremental sync, if the source database has distributed transactions or generates binlog statements in the STATEMENT format, sync will fail.
• If the source database is Alibaba Cloud MySQL, then the tables to be synced on v5.6 must have a primary key, while tables on v5.7 and later are unrestricted. If the source database is AWS MySQL, then the tables to be synced must have a primary key.
• If the binlog of the source database has a GTID hole, it may compromise the performance of the sync task and cause the task to fail.
• Scenarios that contain both DML and DDL statements in the same transaction are not supported and will trigger errors during task execution.
• Geometry data types are not supported and will trigger errors during task execution.
• The ALTER VIEW statement is not supported and will be skipped during sync.

Operation limits

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 run distributed transactions in the source database.
• 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

Environment requirements

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.
   As there are many cross-scenarios of source database deployment modes and access types, the sync steps for different scenarios are similar. The following only provides configuration examples for typical scenarios. For other scenarios, configure by referring to the examples.
   Example 1: Syncing data from a local self-built database to TencentDB through Direct Connect/VPN

   Category	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	Select the source instance type selected during purchase, which cannot be changed once configured.
   	Source Instance Region	Select the source instance region selected during purchase, which cannot be changed once configured.
   	Service Provider	For a self-built database (such as a CVM-based one) or TencentDB database, select Others. For a third-party cloud database, select the corresponding service provider. In this scenario, select Others.
   	Access Type	Select a type based on your scenario. In this scenario, select Direct Connect or VPN Access, and you need to configure VPN-IDC interconnection as instructed in Direct Connect or VPN Access: Configuring VPN-IDC Interconnection. For the preparations for different access types, see Overview. 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. VPN Gateway: Select a VPN Gateway instance.
   	VPC	Select a VPC and subnet associated with the VPC-based Direct Connect Gateway or VPN Gateway.
   	Host Address	IP address or domain name for accessing the source MySQL instance.
   	Port	Port for accessing the source MySQL instance.
   	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.
   	Instance ID	Target instance ID.
   	Account	Account of the target instance, which must have the required permissions.
   	Password	Password of the target instance account.

   Example 2: Syncing data between two TencentDB instances

   Category	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	Select the source instance type selected during purchase, which cannot be changed once configured.
   	Source Instance Region	Select the source instance region selected during purchase, which cannot be changed once configured.
   	Service Provider	For a self-built database (such as a CVM-based one) or TencentDB database, select Others. For a third-party cloud database, select the corresponding service provider. In this scenario, select Others.
   	Access Type	Select a type based on your scenario. In this scenario, select Database. For the preparations for different access types, see Overview. 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.
   	Instance ID	Source instance ID. You can view the source instance information in the instance list.
   	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.
   	Instance ID	Target instance ID.
   	Account	Account of the target instance, which must have the required permissions.
   	Password	Password of the target instance account.

   Example 3: Syncing data from Alibaba Cloud RDS to TencentDB over the public network

   Category	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	Select the source instance type selected during purchase, which cannot be changed once configured.
   	Source Instance Region	Select the source instance region selected during purchase, which cannot be changed once configured.
   	Service Provider	For a self-built database (such as a CVM-based one) or TencentDB database, select Others. For a third-party cloud database, select the corresponding service provider. In this scenario, select Alibaba Cloud.
   	Access Type	For a third-party cloud database, you can select ublic Network generally or select VPN Access, Direct Connect, or CCN based on your actual network conditions. In this scenario, select Public Network. For the preparations for different access types, see Overview. 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.
   	Host Address	IP address or domain name for accessing the source instance.
   	Port	Source instance port.
   	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.
   	Instance ID	Target instance ID.
   	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 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 select Full data initialization only, you need to create the table structures in the target database in advance.
   • 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. 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. For more information, see Setting SQL Filter Policy.
   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. When advanced objects are selected for sync, we recommend you not rename databases/tables; otherwise, sync of the advanced objects may fail. Online DDL temp tables can be synced (through tools such as gh-ost or pt-online-schema-change). Click Edit of a table and select a temp table name in the pop-up window. For more information, see Syncing Online DDL Temp Table.

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.