MySQL Migration to MySQL

NineData replication supports full data migration and incremental data migration between self-built MySQL to self-built MySQL data sources. This document provides best practices for using NineData to perform incremental data migration between MySQL data sources.

Feature Introduction

NineData data replication supports high-performance replication of structure, full data, and incremental data between data sources. For MySQL data sources, it also provides bidirectional replication, enabling the quick construction of geo-distributed active-active business architectures.

• Structure: Supports the replication of object structures between homogeneous and heterogeneous data sources, greatly reducing the barrier to data replication between two sources.
• Full Data: Achieves row-level concurrent batch replication through intelligent data sharding, effectively ensuring replication performance. The independently developed novel breakpoint resume technology ensures the accuracy of data in tables without primary keys.
• Incremental Data: Supports incremental data replication of full object types DML|DDL, combined with transaction-level concurrency, hotspot merging, and other technologies to provide robust replication performance while ensuring transaction consistency.
• Bidirectional Real-time Data Replication (only between MySQL instances): Direct bidirectional replication of data between multiple nodes ensures that all node data remains up-to-date.

With the above features, it is easy and efficient to achieve scenarios such as full|incremental data replication, full|incremental data migration, full|incremental data synchronization, data integration, and seamless migration without downtime, providing enterprises with flexible and reliable data replication solutions.

Prerequisites

• The source and target data sources have been added to NineData. For instructions on how to add them, see Adding Data Sources.

• The versions of the source and target data sources are shown in the table below. Note that the version of the source data source must be less than or equal to the version of the target data source.

  Source Data Source	Target Data Source
  MySQL 8.0	MySQL 8.0
  MySQL 5.7	MySQL 8.0, 5.7
  MySQL 5.6	MySQL 8.0, 5.7, 5.6
  MySQL 5.5	MySQL 8.0, 5.7, 5.6, 5.5
  MySQL 5.1	MySQL 8.0, 5.7, 5.6, 5.5, 5.1

• The source data source must have Binlog enabled, and the Binlog-related parameters are set as follows:

  • binlog_format=ROW
  • binlog_row_image=FULL
  tip

  If the source data source is a replica, to ensure complete Binlog logs are obtained, the log_slave_updates parameter must also be enabled.

Usage Limitations

• The data replication feature only targets user databases within the data source; system databases will not be replicated. For example, libraries such as information_schema, mysql, performance_schema, and sys in MySQL-type data sources will not be replicated.
• The account of the source data source must have the SELECT permission for the replication object (structural replication, full replication), SHOW VIEW permission (view replication), and REPLICATION CLIENT, REPLICATION SLAVE permissions (incremental replication). The account of the target data source must have DML, DDL permissions.
• Before performing data synchronization, it is necessary to assess the performance of the source and target data sources, and it is recommended to perform data synchronization during off-peak business hours. Otherwise, the full data initialization will occupy a certain amount of read and write resources of the source and target data sources, leading to increased database load.
• During synchronization, if the source data source contains views (VIEW), functions (FUNCTION), stored procedures (PROCEDURE), triggers (TRIGGER), and events (EVENT), the definitions of these objects (DEFINER) will be modified to the account accessing the target data source in the current synchronization task after synchronization to the target data source.
• It is necessary to ensure that each table in the synchronization object has a primary key or unique constraint, and the column names are unique, otherwise, the same data may be synchronized repeatedly.
• During synchronization, if the source data source has triggers, the system will synchronize the triggers after the incremental synchronization is completed.

Operation Steps

1. Log in to the NineData Console.

2. Click on in the left navigation bar.

3. On the page, click on in the upper right corner.

4. On the tab, configure according to the table below and click .

   Parameter	Description
   	Enter the name of the data synchronization task. To facilitate subsequent search and management, please use meaningful names as much as possible. Up to 64 characters are supported.
   	The data source where the synchronization object is located.
   	The data source that receives the synchronization object.
   	Select .
   	Select the content to be copied to the target data source, check , , and . The icon is the configuration for incremental operation types. You can uncheck some operation types according to your needs. After unchecking, these operations will be ignored in the incremental synchronization.
   （Required when is selected）	Choose the strategy to execute when an object name conflict occurs according to your needs. : Stop the task when a table with the same name is detected during the pre-inspection phase. : Send a prompt and continue the task when a table with the same name is detected during the pre-inspection phase. During structural replication, ignore the table with the same name. If you also perform data replication, the data will be appended to the table with the same name without overwriting the existing data. : Send a prompt and continue the task when a table with the same name is detected during the pre-inspection phase. During structural replication, delete the table with the same name in the target database and re-copy the table structure based on the source database. If you also perform data replication, the data will be written after the table structure is copied. （Optional when both structure and data replication are performed）: Send a prompt and continue the task when a table with the same name is detected during the pre-inspection phase. During structural replication, retain the structure of the table in the target database, and clear the data in the table with the same name at the beginning of data replication, then re-copy from the original table.

5. On the tab, configure the following parameters, then click .

   Parameter	Description
   	Select the content to be copied. You can choose to copy all content from the source database, or you can choose , select the content to be copied in the list, and click > to add to the right list.
   （Optional）	Click to add a blacklist record, select the database or object to be added to the blacklist, and these contents will not be copied. Used to exclude certain databases or objects in full database replication or replication. Left dropdown box: Select the database name to be added to the blacklist. Right dropdown box: Select the objects in the corresponding database. You can click on multiple objects for multi-selection, leave blank to add the entire database to the blacklist. If you want to add multiple databases to the blacklist, you can click the Add button below to add a row.

6. On the tab, configure the table name after the table is synchronized to the target data source, then click . If there are updates in the source and target data sources during the configuration mapping phase, you can click the button in the upper right corner of the page to refresh the information of the source and target data sources.

   tip

   You can click on the on the right side of the page to customize the name of the column name after synchronization to the target data source. In addition, you can set , configure filtering conditions through comparison expressions, and only data that meets the filtering conditions will be synchronized to the target data source. For example, if the filtering condition is set to emp_no>=10005, data in the emp_no column less than 10005 will not be synchronized to the target data source.

7. On the tab, wait for the system to complete the pre-inspection. After the pre-inspection is passed, click .

8. On the page, prompt , the synchronization task starts running. At this time, you can perform the following operations:

   • Click to view the execution of each stage of the synchronization task.
   • Click to return to the task list page.