TDSQL MySQL Synchronization to MySQL

NineData data replication supports schema, full, and incremental data synchronization from Tencent Cloud TDSQL MySQL to MySQL data sources.

Prerequisites

• The source and target data sources have been added to NineData. For details, see Add Data Source.

• The source data source is Tencent Cloud TDSQL MySQL.

• The target data source is MySQL.

  The supported MySQL source versions range from 5.1 to 9.0.

• According to Tencent Cloud documentation, if you connect to TDSQL MySQL across VPCs or over the public network, first enable public access for the instance and allow the database port and NineData service addresses in the security group. For details, see the Tencent Cloud documents Enable Public Access and Configure Cloud Database Security Groups.

• You must have the following permissions on the source and target data sources:

  Replication Type	TDSQL MySQL (Source)	MySQL (Target)
  Schema Replication	SELECT	SELECT, INSERT, UPDATE, DELETE, CREATE
  Full Replication	SELECT	SELECT, INSERT, UPDATE, DELETE, CREATE
  Incremental Replication	SELECT, REPLICATION CLIENT, REPLICATION SLAVE	SELECT, INSERT, UPDATE, DELETE, CREATE

• For incremental replication, confirm in the Tencent Cloud console that Binlog is enabled on the source and that the following parameters are configured:

  • binlog_format=ROW
  • binlog_row_image=FULL
  tip

  If the source is a replica, enable log_slave_updates so that NineData can read the complete Binlog stream.

Usage Limitations

• The data replication function is only for the user databases in the data source, and the system databases will not be replicated. For example: information_schema, mysql, performance_schema, sys databases in MySQL type data sources will not be replicated.
• The account for source data must have SELECT (for replicate database structure and full data), SHOW VIEW (for replicate views), and REPLICATION CLIENT, REPLICATION SLAVE (for replicate incremental data) privileges on the objects to be replicated. The account for target must have DML and DDL privileges.
• Before performing data synchronization, user need to evaluate the performance of the source data source and the target data source, and it is recommended to perform data synchronization during off-peak time. Otherwise, the full data initialization will occupy a certain amount of read and write resources of the source data source and the target data source, increasing database load.
• During the synchronization process, if the source data contains views, functions, stored procedures, triggers, and events, after synchronizing to the target data source, the definer of the above objects information will be modified in the target data source to the account that accesses the target data source in the current synchronization task.
• It is necessary to ensure that each table in the synchronization object has a primary key or unique constraint, and the column name is unique, otherwise the same data may be synchronized repeatedly.
• During the synchronization process, if there are triggers in the source, the system will not synchronize the triggers until the incremental synchronization ends.

Operation Steps

View Synchronization Results

1. Log in to the NineData Console.

2. Click on Replication > Data Replication in the left navigation bar.

3. On the Replication page, click on the Task ID of the target synchronization task, the page details are as follows.

   

   Number	Function	Description
   1	Sync Delay	The data synchronization delay between the source data source and the target data source. 0 seconds means there is no delay between the two ends, at which point you can choose to switch the business to the target data source for smooth migration.
   2	Configure Alerts	After configuring alerts, the system will notify you in the way you choose when the task fails.
   3	More	Pause: Pause the task, only tasks with Running status are selectable. Duplicate: Create a new replication task with the same configuration as the current task. Terminate: End tasks that are unfinished or listening (i.e., in incremental synchronization). Once the task is terminated, it cannot be restarted, so please proceed with caution. If the synchronization objects contain triggers, a trigger replication option will pop up, please select as needed. Delete: Delete the task. Once the task is deleted, it cannot be recovered, so please proceed with caution.
   4	Structure Replication (displayed in scenarios involving structure replication)	Displays the progress and detailed information of structure replication. Click on Log on the right side of the page: View the execution logs of structure replication. Click on on the right side of the page: View the latest information. Click on View DDL in the Actions column on the right side of the target object in the list: View SQL playback.
   5	Full Replication (displayed in scenarios involving full replication)	Displays the progress and detailed information of full replication. Click on Monitor on the right side of the page: View various monitoring indicators during full replication. During full replication, you can also click on Flow Control Settings on the right side of the monitoring indicator page to limit the rate of writing to the target data source per second. The unit is rows/second. Click on Log on the right side of the page: View the execution logs of full replication. Click on on the right side of the page: View the latest information.
   6	Incremental Replication (displayed in scenarios involving incremental replication)	Displays various monitoring indicators of incremental replication. Click on View Threads on the right side of the page: View the operations currently being executed by the current replication task, including: Thread ID: Replication tasks are executed in multiple threads, displaying the current thread number in progress. Execute SQL: Details of the SQL statement currently being executed by the current thread. Response Time: The response time of the current thread. If this value increases, it indicates that the current thread may be stuck for some reason. Event Time: The timestamp when the current thread was started. Status: The status of the current thread. Click on Flow Control Settings on the right side of the page: Limit the rate of writing to the target data source per second. The unit is rows/second. Click on Log on the right side of the page: View the execution logs of incremental replication. Click on on the right side of the page: View the latest information.
   7	Modify Object	Displays the modification records of synchronization objects. Click on Modify Objects on the right side of the page to configure the synchronization objects. Click on on the right side of the page: View the latest information.
   8	Data Comparison	Displays the comparison results between the source data source and the target data source. If you have not enabled data comparison, please click on Enable Comparison on the page. Click on Re-compare on the right side of the page: Re-initiate the comparison between the current source and target data sources. Click on Stop on the right side of the page: After the comparison task starts, you can click this button to stop the comparison task immediately. Click on Log on the right side of the page: View the execution logs of consistency comparison. Click on Monitor (displayed only in data comparison): View the trend chart of RPS (records per second) comparison. Click on Details to view earlier records. Click on in the Actions column on the right side of the comparison list (displayed under the Data tab only in the case of inconsistency): View details of the comparison between the source and target sides. Click on in the Actions column on the right side of the comparison list (displayed only in the case of inconsistency): Generate change SQL. You can directly copy this SQL to the target data source to execute and modify the inconsistent content.
   9	Expand	Display detailed information of the current replication task. Common Options: Export table configuration: Export the current task's database and table configuration, allowing for quick import when creating a new replication task. This helps rapidly establish multiple replication links with the same replication objects. Alert Rules: Configure the alarm strategy for the current task.

Appendix: Pre-Check Item List

Check Item	Check Content
Target database data existence check	Check whether the object to be replicated not empty in the target database
Objects with the same name in the target database existence check	Check whether the object to be replicated already exists in the target database
Existence check for the Definer of non-table objects	Check whether the definer of view, trigger, procedure, function, event exists in the target database
Source datasource connection check	Check the status of the gateway of the source datasource, database connectable, and verify the username and password
Target datasource connection check	Check the status of the gateway of the target datasource, database connectable, and verify the username and password
Target databse privilege check	Check whether the account privileges of the target database meet the requirements
Source database privilege check	Check whether the account privileges of the source database meet the requirements
Source and target datasource collation check	Check whether the collations of the source and target databases are compatible
Target datasource trigger check	Check the existence of triggers on the target database
Source database log_slave_updates check	When the source database is a slave, check whether log_slave_updates is ON
Source and target datasource version check	Check whether the source and target database versions are compatible
Check if the source database has Binlog enabled	Check if the source database has Binlog enabled
Source database binlog format check	Check whether the binlog format of the source database is 'ROW'
Source database binlog_row_image check	Check whether the binlog_row_image of the source database is 'FULL'
Source and target datasource time zone check	Check whether the timezones of the source and target databases match
Source and target datasource character set check	Check whether the character set of the source and target databases are compatible
Source and target datasource SQL_MODE check	Check whether the SQL_MODE of the source and target databases are compatible
Source and target datasource case-sensitive check	Check whether the case-sensitive configurations of the source database and the target database are compatible
Source datasource and target datasource explicit_defaults_for_timestamp check	Check whether the explicit_defaults_for_timestamp of the source database and the target database are compatible
Source database procedure and function check	Check that the source database has been granted privileges to obtain the definitions of procedures and functions

Related Documentation

Introduction to Data Replication