Data replication amongst 3 or more data sources

The NineData data replication function supports full data synchronization and incremental data synchronization amongst 3 or more data sources.

Function Description

This article uses the Bi-directional replication function of NineData to build a data synchronization task between three data sources to ensure that changes made in each data source will be synchronized to the other two data sources.

This example is used to demonstrate the scenario. There are three data sources A, B, and C, and data source A is the master, and Bi-directional replication tasks between A and B, A and C are created respectively for real-time data synchronization of the three data sources, which is commonly for remote storage. disaster/recovery, global availability and other scenarios.

tip

This method is for real-time synchronization between three or more data sources.

Prerequisites

• All data sources participating in data replication have been added to NineData. For how to add, see Add Data Source.

• The type and version of the source and target data sources are MySQL 8.0, 5.7, 5.6, 5.5, 5.1, and it is recommended that the source and target data source versions match each other.

• In scenarios that do not include schema replication, the target data source must contain the same schema definition of replication objects as the source data source. For example, to copy the incremental data of Table_A in data source A to data source B, data source B must have Table_A created, and have the same schema as table_A in data source A.

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

  • binlog_format=ROW

  • binlog_row_image=FULL

    tip

    If the source data source is the standby database, in order to ensure that the complete Binlog is obtained, the log_slave_updates parameter also needs to be enabled.

Usage Restrictions

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

Step 1: Add multi-active tag for all data sources participating in replication

To prevent circular replication of data, an multi-active tag need to be added to all data sources participating in the replication task.

1. Log in to NineData Console.

2. In the navigation pane on the left, click Datasource>Datasource.

3. Click the target data source ID to open the Details page.

4. In the data source details area (that is, the area containing information such as the data source name, ID, creator, and creation time), click Show Details.

5. Find Multi-Active Tag, click the icon on the right side of it.

6. Enter the multi-active tag, and click OK.

   tip

   • Multi-active tag can contain 1~64 characters.
   • The multi-active tag must be globally unique and cannot be duplicated with other multi-active tag.

Step 2: Create a Bi-directional replication task between data source A and data source B

1. Log in to NineData Console.

2. Click Replication > in the left navigation bar.

3. On the Replication page, click Create Replication in the upper right corner.

4. On the Source & Target tab, configure according to the table below, and click Next.

   Parameter	Description
   Task Name	Enter the name of the data synchronization task. To facilitate subsequent search and management, please use a meaningful name. Up to 64 characters are supported.
   Source data source	Select data source A, which will serve as the central data node and be responsible for the following operations: Synchronize DDL, DML, etc. changes to other data sources. Consolidates DML changes from all other nodes into itself.
   Target data source	Select data source B, which will perform Bi-directional data replication with data source A.
   Replication Mode	Select Bi-directional.
   Forward Type	Select the type of replication from Data Source A to Data Source B.
   Forward Incremental Start Time	To be selected when selecting Incremental only. From Started: Based on the start time of the current copy task, perform incremental copy. Customized Time: Select the time point when the incremental replication starts. Please select the time zone accordingly. If the time point is configured to be before the start of the current replication task, the replication task will fail if there is a DDL operation within that time period.
   Reverse Type	The type of replication from data source B to data source A.
   Support DDL Replication	Whether to replicate DDL operations during incremental replication. Forward replication supported, reverse not supported: Incremental DDL operations in the source data source will be synchronized to the target data source. In this scenario, please do not perform DDL operations in the reverse task (target data source), otherwise the task may report an error. NOT Supported: All incremental DDL operations will not be synchronized. In this scenario, please do not perform DDL operations in the source or target data source, otherwise the task may report an error.
   Target table preparation configuration	If target table already exist (need to select when Schema is selected) Abort after error: The same name is detected in the pre-check stage table, stop the task. Skip and continue the task: When a table with the same name is detected in the pre-check phase, send a prompt and continue the task. When the schema is copied, the table with the same name is ignored. If copy data at the same time, the data will be appended to the table with the same name, and the original data will not be overwritten. Delete object and rewrite: When a table with the same name is detected in the pre-check phase, send a prompt and continue the task. When copying the schema, delete the table with the same name in the target database, and re-copy the table schema from the source database. If copy data at the same time, the data will be written after the table schema is copied. Preserve the schema and clean data before write (optional when replicating the schema and data at the same time): When a table with the same name is detected in the pre-check stage, send a prompt and continue the task. When the schema is copied, the table schema is retained in the target database, and the data in the table with the same name is cleared when the data copy starts, and then copied from the original table again. Target table data processing policy (need to select when Schema is not selected) Pre-check for errors and Exit: Stop the task when the precheck phase detects that data exists in the target table. Ignore the existing data and append: When the pre-check stage detects that there is data in the target table, ignore this part of the data and write additional data. Clear the existing data before write: When the pre-check stage detects that there is data in the target table, delete this part of the data and rewrite it.

5. On the Objects tab, configure the following parameters, and then click Next.

   Parameter	Description
   Replication Objects	Select the content to be replicated, you can choose All Objects to replicate all the contents of the source library, or you can choose Customized Object, select it in the Source list For the content to be replicated, click > to add to the right Target list.
   Blocklist (optional)	Click Add to add a blocklist record, Select libraries or objects that need to be added to the blocklist, these contents will not be replicated. Used to exclude certain libraries or objects from All Objects replication or Customized Object. The drop-down box on the left: select the name of the library that needs to be added to the blocklist. Right drop-down box: select the object in the corresponding library, you can click multiple objects to make multiple selections, and leave it blank to add the entire database to the blocklist. If you want to add multiple libraries to the blocklist, you can click the Add button below to add a row.

6. On the Mapping tab, select different operations based on the selected replication Type.

   • Contains Schema: configure the table name after the target table is synchronized to the target data source, and click Save and Pre-check.

     tip

     User can click Mapping & Filtering on the right side of the page to customize the column names after they are synchronized to the target data source.

   • Does not contains Schema: The system selects the database with the same name in the target data source by default. If it does not exist, user need to manually select the target database. The table name and column name in the target library must be consistent with the synchronization object. If not, user can also manually map table names and column names.

7. On the Pre-check tab, wait for the system to complete the pre-check. After the pre-check passes, click Launch.

   tip

   • User can click to Enable data consistency comparison. After the synchronization task is completed, the data consistency comparison based on the source data is automatically enabled to ensure that the data at both ends are consistent. Depending on the replication Type, the data consistency comparison starts as follows:

     • Schema : Starts after the schema replication is complete.

     • Schema + Full : start after full replication is complete.

     • Schema + Full + Incremental (SQL Server is not currently supported): Start when the incremental data is consistent with the source data for the first time and the Delay is 0 seconds. You can click View Details to view the synchronization delay in the Details page.

       

   • If the pre-check fails, user need to click the Details in the Actions column to the right of the target item to check the cause of the failure. After correction, click Check Again to re-execute the pre-check.

   • For Warnings, user can be correct or ignore according to specific circumstances.

8. On the Launch page, a message is displayed indicating that Launch Successfully , and the replication task starts to run. At this point user can do the following:

   • Click View Details to view the execution of each phase of the synchronization task.
   • Click Back to list to return to the Replication list.

Step 3: Create a Bi-directional replication task between data source A and data source C

Please refer to Step 2, the only difference is that data source C needs to be selected as the Target.

Operation result

So far, with the deployment of the three-node data replication task, the data change operations on Data Source A will be synchronized to the other two data source B and C in real time. User can also continue to add other MySQL data sources according to Step 2 to achieve multiple data synchronization. Although no limitation enforced by NineData, network bandwith and hardware resource should be considered during achitecture design.

View sync results

1. Log in to the NineData Console.

2. Click on > in the left navigation bar.

3. On the page, click on for the target synchronization task. The page details are as follows.

   

   Serial Number	Function	Description
   1	Synchronization Delay	The data synchronization delay between the source data source and the target data source. 0 seconds indicates no delay between the two ends. At this point, you can choose to switch your 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. For more information, please refer to Operations and Monitoring Introduction.
   3	More	Pause: Pause the task. Only tasks in the Running state can be selected. Terminate: End tasks that are incomplete or in listening mode (i.e., in incremental synchronization). After terminating the task, it cannot be restarted. Please proceed with caution. If the synchronization objects include triggers, trigger replication options will pop up; choose as needed. Delete: Delete the task. Once a task is deleted, it cannot be recovered. Please proceed with caution.
   4	Structural Replication (Displayed for scenarios that include structural replication)	Display the progress and detailed information of structural replication. Click Logs on the right side of the page: View the execution logs of structural replication. Click on the right side of the page: View the latest information. Click View DDL in the Actions column on the right of the target object in the list: View SQL playback.
   5	Full Copy (Displayed for scenarios that include full copy)	Display the progress and detailed information of full copy. Click Monitoring on the right side of the page: View various monitoring indicators during the full copy process. During full copy, you can also click Rate Limit Settings on the right of the monitoring indicator page to limit the rate of writing to the target data source in rows per second. Click Logs on the right side of the page: View the execution logs of the full copy. Click on the right side of the page: View the latest information.
   6	Incremental Copy (Displayed for scenarios that include incremental copy)	Display various monitoring indicators for incremental copy. Click on the right of the page: View the operations currently being executed by the current copy task, including: : The thread number currently in progress for the copy task. : Details of the SQL statement currently being executed by the thread. : The response time of the current thread. An increase in this value may indicate that the current thread may be stuck for some reason. : The timestamp when the current thread started. : The status of the current thread. Click Rate Limit Settings on the right of the page: Limit the rate of writing to the target data source in rows per second. Click Logs on the right side of the page: View the execution logs of the incremental copy. Click on the right side of the page: View the latest information.
   7	Modify Object	Display the modification records of synchronized objects. Click Modify Synchronized Object on the right of the page to configure synchronized objects. Click on the right side of the page: View the latest information.
   8	Data Comparison	Display the results of data comparison between the source data source and the target data source, including Structure Comparison and Data Comparison. If you have not enabled data comparison, click Enable Data Comparison in the page. Click Re-compare on the right side of the page: Re-initiate the comparison of data between the current source and target ends. Click Logs on the right side of the page: View the execution logs of consistency comparison. Click Monitoring (displayed for data comparison only): View the trend chart of RPS (records compared per second) during the comparison. Click Details to view earlier records. Click in the Actions column on the right of the comparison list (displayed on the Data Comparison page only in case of inconsistency): View detailed comparison results between the source and target ends. Click in the Actions column on the right (displayed in case of inconsistency): Generate change SQL. You can copy this SQL directly to the target data source for execution to modify inconsistent content.
   9	View Reverse	Displayed for bidirectional replication tasks only. Click to view the replication details from the target data source to the source data source.
   10	Expand	Display detailed information about the current replication task, including Replication Type, Replication Object, Start Time, and more.

Related documents

Introduction to Data Replication