Data Replication Across Three or More Data Sources

NineData data replication supports full and incremental synchronization across three or more data sources.

Overview

This guide uses bidirectional replication to build a synchronization workflow across three data sources, so changes made in one data source can be synchronized to the other two data sources.

The example uses data sources A, B, and C. Data source A acts as the central data node. You create bidirectional replication tasks between A and B, and between A and C. This pattern supports real-time synchronization scenarios such as disaster recovery and global availability.

tip

Use this method only for real-time synchronization across three or more data sources.

Prerequisites

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

• The source and target data sources are MySQL 8.0, 5.7, 5.6, 5.5, or 5.1. We recommend using matching source and target versions.

• In scenarios that do not include schema replication, the target data source must already contain the same schema definitions as the source replication objects. For example, to replicate incremental data of Table_A from data source A to data source B, data source B must already contain Table_A with the same schema as Table_A in data source A.

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

• binlog_format=ROW

• binlog_row_image=FULL

  tip

  If the source data source is a standby database, enable log_slave_updates so NineData can obtain the complete Binlog.

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 a Multi-Active Tag to Each Data Source

To prevent circular replication, add a multi-active tag to every data source that participates in the replication tasks.

1. Log in to the 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, then click the icon on the right.

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

   :::tip

• A multi-active tag can contain 1 to 64 characters.

• The multi-active tag must be globally unique and cannot duplicate another multi-active tag.

  :::

Step 2: Create a Bidirectional Replication Task Between Data Source A and Data Source B

1. Log in to the NineData Console.

2. Click Replication > Data Replication from the navigation menu.

3. On the Replication page, click Create Replication.

4. On the Source & Target tab, configure using this table, and click Next.

   Parameter	Description
   Task Name	Enter the name of the data synchronization task. To facilitate subsequent search and management, 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 these operations: Synchronize DDL and DML changes to other data sources. Consolidate DML changes from all other nodes into itself.
   Target data source	Select data source B, which will run bidirectional 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	Required only when Incremental is selected. From Started: Start incremental replication from the start time of the current replication task. Customized Time: Select the time point when incremental replication starts, and select the corresponding time zone. If the selected time point is earlier than the start time of the current replication task, the replication task will fail if a DDL operation occurred during that time period.
   Reverse Type	Select 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, 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, 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 (required when Schema is selected) Abort after error: Stop the task if the pre-check detects a table with the same name. Skip and continue the task: If the pre-check detects a table with the same name, show a prompt and continue the task. During schema replication, NineData skips the table with the same name. If data is replicated at the same time, NineData appends data to the existing table and does not overwrite the original data. Delete object and rewrite: If the pre-check detects a table with the same name, show a prompt and continue the task. During schema replication, NineData deletes the table with the same name in the target database, and then copies the table schema from the source database again. If data is replicated at the same time, NineData writes the data after the table schema is copied. Preserve the schema and clean data before write (available when schema and data are replicated together): If the pre-check detects a table with the same name, show a prompt and continue the task. During schema replication, NineData keeps the table schema in the target database. When data replication starts, NineData clears the data in the table with the same name, and then copies the data from the source table again. Target table data processing policy (required when Schema is not selected) Pre-check for errors and Exit: Stop the task if the pre-check detects existing data in the target table. Ignore the existing data and append: If the pre-check detects existing data in the target table, ignore the existing data and append new data. Clear the existing data before write: If the pre-check detects existing data in the target table, delete the existing data before writing new data.

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

   Parameter	Description
   Replication Objects	Select the objects to replicate. You can select All Objects to replicate all objects in the source database, or select Customized Object, choose the objects in the Source list, and click > to move them to the Target list.
   Blocklist (optional)	Click Add to add a blocklist rule. Select the databases or objects to exclude from replication. Blocklists are used to exclude specific databases or objects when All Objects or Customized Object replication is selected. Left drop-down list: Select the database to add to the blocklist. Right drop-down list: Select one or more objects in the selected database. Leave this field empty to add the entire database to the blocklist. To exclude multiple databases, click Add again to add more rows.

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

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

  tip

  Click Mapping & Filtering to customize target column names after synchronization.

• Does not include Schema: The system selects the database with the same name in the target data source by default. If no database with the same name exists, manually select the target database. The table and column names in the target database must match the replication objects. If they do not match, manually map the table 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

• Select Enable data consistency comparison to automatically compare the target data with the source data after the synchronization task is complete. The comparison start time depends on the replication Type:

• Schema : Starts after the schema replication is complete.

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

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

  

• If the pre-check fails, click Details in the Actions column for the failed item to view the cause. After fixing the issue, click Check Again to run the pre-check again.

• For warnings, fix the issue or ignore the warning as needed.

  :::

8. On the Launch page, the Launch Successfully message is displayed, and the replication task starts running. You can then 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 Bidirectional Replication Task Between Data Source A and Data Source C

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

Result

After the three-node replication workflow is deployed, data changes on data source A are synchronized to data sources B and C in real time. You can add more MySQL data sources by repeating Step 2. Although NineData does not enforce a fixed node limit in this guide, consider network bandwidth and hardware resources during architecture design.

View Synchronization Results

1. Log in to the NineData Console.

2. Click Replication > Data Replication from the navigation menu.

3. On the Replication page, click Task ID of the target synchronization task, the task details page displays the following information.

   

   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 proceed with caution. If the synchronization objects contain triggers, a trigger replication option will pop up, select as needed. Delete: Delete the task. Once the task is deleted, it cannot be recovered, so proceed with caution.
   4	Structure Replication (displayed in scenarios involving structure replication)	Displays the progress and detailed information of structure replication. Click Log on the right side of the page: View the execution logs of structure replication. Click the on the right side of the page: View the latest information. Click 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 Monitor on the right side of the page: View various monitoring indicators during full replication. During full replication, you can also click 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 Log on the right side of the page: View the execution logs of full replication. Click the 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 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 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 Log on the right side of the page: View the execution logs of incremental replication. Click the on the right side of the page: View the latest information.
   7	Modify Object	Displays the modification records of synchronization objects. Click Modify Objects on the right side of the page to configure the synchronization objects. Click the 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, click Enable Comparison on the page. Click Re-compare on the right side of the page: Re-initiate the comparison between the current source and target data sources. Click 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 Log on the right side of the page: View the execution logs of consistency comparison. Click Monitor (displayed only in data comparison): View the trend chart of RPS (records per second) comparison. Click Details to view earlier records. Click the 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 the 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.

Related Documents

Introduction to Data Replication