Multi-node database synchronization method based on data division

Abstract

The invention relates to the technical field of database synchronization, in particular to a multi-node database synchronization method based on data partitioning, which comprises the following steps of: 1, performing data partitioning and node role allocation; 2, synchronization mechanism configuration, wherein the synchronization mechanism configuration comprises master and slave node copy setting and data initialization and import; 3, incremental data synchronization, asynchronous / semi-synchronous replication and conflict detection; 4, using a protocol to optimize large file and mass data transmission; step 5, performing parallel processing on the fragmented data at a plurality of nodes; and step 6, carrying out redundant backup on the data fragments at a plurality of nodes. According to the invention, data division and node role allocation are carried out; and data distribution: routing the change event to a corresponding node according to a fragmentation rule, or broadcasting a write set through a group communication protocol, carrying out redundant backup on the data fragmentation at a plurality of nodes, and finally checking the consistency of the fragmentation data regularly, so that the method has the advantages of being capable of realizing efficient synchronization of the multi-node database, giving consideration to the performance and the consistency and the like.

Description

Technical Field

[0001] The present invention relates to the technical field of database synchronization, and in particular to a multi-node database synchronization method based on data partitioning. Background Art

[0002] With the increasing pressure of database read and write, single-node databases can no longer bear the business load under large data volumes, and multi-node distributed database designs are becoming increasingly popular. In multi-node database applications, data synchronization between nodes is a key design issue. In the early days of multi-node database design, read-write separation was usually adopted to achieve database synchronization. For database systems, generally speaking, there are fewer write operations and more read operations. Therefore, the design scheme is one write database and multiple read databases, and one-way synchronization is performed from the write database to multiple read databases. However, with the increasing number of write operations, the write database as a single point has become a bottleneck for system performance, thereby reducing the synchronization performance and consistency of the multi-node database. In view of this, it is necessary to propose a multi-node database synchronization method based on data partitioning. Summary of the Invention

[0003] (1) Technical problems solved

[0004] In response to the deficiencies of the existing technology, the present invention provides a multi-node database synchronization method based on data partitioning, which has the advantages of being able to achieve efficient synchronization of multi-node databases, taking into account both performance and consistency, and solving the above-mentioned problems.

[0005] (2) Technical solution

[0006] To achieve the above object, the present invention provides the following technical solutions:

[0007] A multi-node database synchronization method based on data partitioning includes the following steps:

[0008] Step 1: Data partitioning and node role assignment;

[0009] Step 2: Synchronization mechanism configuration, which includes master-slave node replication settings and data initialization and import;

[0010] Step 3: Incremental data synchronization, asynchronous / semi-synchronous replication, and conflict detection;

[0011] Step 4: Use protocols to optimize large file and massive data transfers;

[0012] Step 5: Shard data is processed in parallel on multiple nodes;

[0013] Step 6: Data sharding is redundantly backed up on multiple nodes;

[0014] Step 7: Regularly check the consistency of shard data and fix differences through incremental synchronization.

[0015] Preferably, step one includes data sharding strategies: vertical sharding and horizontal sharding, wherein vertical sharding: dividing by business modules or functions, and deploying tables with strong correlation on the same node; horizontal sharding: distributing data to different nodes based on hash, range or date rules, such as taking the hash modulus of the user ID or dividing by time interval.

[0016] Preferably, in step 1, the node roles are assigned to a designated master node (responsible for write operations) and a slave node (responsible for read operations), or a multi-master mode is used to achieve bidirectional synchronization.

[0017] Preferably, in step 2, the master node: turns on the binary log, configures a unique identifier, creates a dedicated synchronization account and authorizes it; the slave node: configures the master node address, port and account information, sets the relay log, starts the replication process, exports the master node data, and imports it to the slave node to ensure that the initial state is consistent.

[0018] Preferably, the incremental data synchronization in step three includes log parsing: parsing the binlog of the master node through a tool (such as Canal) to capture incremental changes; data distribution: routing the change event to the corresponding node according to the sharding rule, or broadcasting the write set through a group communication protocol, asynchronous / semi-synchronous replication: balancing performance and consistency requirements. In semi-synchronous mode, the master node needs to wait for at least one slave node to confirm the write; conflict detection and resolution: optimistic locking or timestamp mechanism is used in the multi-master architecture to handle concurrent write conflicts.

[0019] Preferably, the sharded data in step five is processed in parallel on multiple nodes, load balancing of read and write requests is achieved in combination with the proxy layer, and the degree of parallelism is controlled by a sliding window algorithm to avoid node overload.

[0020] Preferably, the data sharding in step six is ​​redundantly backed up on multiple nodes, supporting fast switching in case of failure, and combining heartbeat detection and automatic failover.

[0021] (3) Beneficial effects

[0022] Compared with the existing technology, the present invention provides a multi-node database synchronization method based on data partitioning, which has the following beneficial effects:

[0023] 1. The present invention implements bidirectional synchronization by partitioning data and assigning node roles, designating a master node (responsible for write operations) and a slave node (responsible for read operations), or adopting a multi-master mode. The master node: enables binary logging, configures a unique identifier, creates a dedicated synchronization account, and authorizes it. The slave node: configures the master node address, port, and account information, sets a relay log, starts a replication process, exports the master node data, imports it into the slave node, ensures initial state consistency, and uses a tool to parse the master node's binlog to capture incremental changes. Data distribution: routes change events to corresponding nodes according to sharding rules, or broadcasts write sets through a group communication protocol. Asynchronous / semi-synchronous replication: balances performance and consistency requirements. In semi-synchronous mode, the master node must wait for at least one slave node to confirm the write. Conflict detection and resolution: optimistic locking or timestamp mechanisms are used in the multi-master architecture to handle concurrent write conflicts. Large file and massive data transmission is optimized by using protocols. Sharded data is processed in parallel on multiple nodes, and data shards are redundantly backed up on multiple nodes. Finally, shard data consistency is regularly verified, and differences are repaired through incremental synchronization. This method has the advantages of achieving efficient synchronization of multi-node databases and balancing performance and consistency.

[0024] 2. In the present invention, sharded data is processed in parallel on multiple nodes, and load balancing of read and write requests is achieved in combination with the proxy layer. The degree of parallelism is controlled by the sliding window algorithm to avoid node overload. Data shards are redundantly backed up on multiple nodes, supporting fast switching in case of failure, and combining heartbeat detection with automatic failover. DETAILED DESCRIPTION

[0025] The following will be combined with the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the embodiments described are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative efforts are within the scope of protection of the present invention.

[0026] The present invention relates to a multi-node database synchronization method based on data partitioning, comprising the following steps:

[0027] Step 1: Data partitioning and node role assignment;

[0028] Step 1 includes data sharding strategies: vertical sharding and horizontal sharding. Vertical sharding involves sharding by business module or function, with closely related tables deployed on the same node. Horizontal sharding involves distributing data across different nodes based on hash, range, or date rules, such as by user ID hash modulus or time interval. Node roles are assigned to designated master nodes (responsible for write operations) and slave nodes (responsible for read operations), or a multi-master mode is used for two-way synchronization.

[0029] Step 2: Synchronization mechanism configuration, which includes master-slave node replication settings and data initialization and import;

[0030] In step 2, for the master node, enable binary logging, configure a unique identifier, create a dedicated synchronization account, and authorize it. For the slave node, configure the master node address, port, and account information, set up relay logs, start the replication process, export the master node data, and import it to the slave node to ensure consistency in the initial state.

[0031] Step 3: Incremental data synchronization, asynchronous / semi-synchronous replication, and conflict detection. Incremental data synchronization includes log parsing: using tools such as Canal to parse the master node's binlog and capture incremental changes; data distribution: routing change events to corresponding nodes according to sharding rules, or broadcasting write sets through group communication protocols; asynchronous / semi-synchronous replication: balancing performance and consistency requirements. In semi-synchronous mode, the master node must wait for at least one slave node to confirm the write; conflict detection and resolution: optimistic locking or timestamp mechanisms are used in multi-master architectures to handle concurrent write conflicts.

[0032] Step 4: Use protocols to optimize large file and massive data transfers;

[0033] Step 5: Sharded data is processed in parallel on multiple nodes, and the proxy layer is used to achieve load balancing of read and write requests. The sliding window algorithm is used to control the degree of parallelism and avoid node overload.

[0034] Step 6: Data sharding is redundantly backed up on multiple nodes, supporting fast failover in the event of a failure, combining heartbeat detection with automatic failover;

[0035] Step 7: Regularly check the consistency of shard data, repair differences through incremental synchronization, and use CRC check or hash value comparison to ensure transmission integrity.

[0036] The beneficial effects of the present invention are as follows: the present invention specifies a master node (responsible for write operations) and a slave node (responsible for read operations) through data partitioning and node role allocation, or adopts a multi-master mode to achieve bidirectional synchronization, wherein the master node: starts a binary log, configures a unique identifier, creates a dedicated synchronization account and authorizes it; the slave node: configures the master node address, port and account information, sets a relay log, starts a replication process, exports the master node data, imports it to the slave node, ensures initial state consistency, and uses a tool to parse the master node's binlog to capture incremental changes; data distribution: routes change events to corresponding nodes according to sharding rules, or broadcasts write sets through a group communication protocol; asynchronous / semi-synchronous replication: balances performance and consistency requirements; in semi-synchronous mode, the master node needs to wait for at least one slave node to confirm the write; conflict detection and resolution: optimistic locking or timestamp mechanism is used in the multi-master architecture to handle concurrent write conflicts, and large file and massive data transmission is optimized by using a protocol. Sharded data is processed in parallel on multiple nodes, and data shards are redundantly backed up on multiple nodes. Finally, the shard data consistency is regularly checked, and differences are repaired through incremental synchronization. Therefore, the method has the advantages of being able to achieve efficient synchronization of multi-node databases and taking into account both performance and consistency.

[0037] While embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that various changes, modifications, substitutions, and variations may be made to these embodiments without departing from the principles and spirit of the invention, and that the scope of the invention is defined by the appended claims and their equivalents.

Claims

1. A multi-node database synchronization method based on data partitioning, characterized in that: The following steps are involved: Step 1: Data partitioning and node role assignment; Step 2: Synchronization mechanism configuration, which includes master-slave node replication settings and data initialization and import; Step 3: Incremental data synchronization, asynchronous / semi-synchronous replication, and conflict detection; Step 4: Use protocols to optimize large file and massive data transfers; Step 5: Shard data is processed in parallel on multiple nodes; Step 6: Data sharding is redundantly backed up on multiple nodes; Step 7: Regularly check the consistency of shard data and fix differences through incremental synchronization.

2. A multi-node database synchronization method based on data partitioning according to claim 1, characterized in that: Step 1 includes data sharding strategies: vertical sharding and horizontal sharding, wherein vertical sharding: dividing by business modules or functions, and deploying highly correlated tables on the same node; horizontal sharding: distributing data to different nodes based on hash, range or date rules, such as taking the hash modulus of the user ID or dividing by time interval.

3. The multi-node database synchronization method based on data partitioning according to claim 1, characterized in that: In the step 1, the node roles are assigned to a designated master node (responsible for write operations) and a slave node (responsible for read operations), or a multi-master mode is used to achieve bidirectional synchronization.

4. The multi-node database synchronization method based on data partitioning according to claim 1, characterized in that: In step 2, the master node: turns on the binary log, configures a unique identifier, creates a dedicated synchronization account and authorizes it; the slave node: configures the master node address, port and account information, sets the relay log, and starts the replication process.

5. The multi-node database synchronization method based on data partitioning according to claim 1, characterized in that: The incremental data synchronization in step three includes log parsing: parsing the binlog of the master node through tools (such as Canal) to capture incremental changes; data distribution: routing change events to the corresponding nodes according to the sharding rules, or broadcasting the write set through the group communication protocol, asynchronous / semi-synchronous replication: balancing performance and consistency requirements. In semi-synchronous mode, the master node needs to wait for at least one slave node to confirm the write; conflict detection and resolution: optimistic locking or timestamp mechanism is used in the multi-master architecture to handle concurrent write conflicts.

6. The multi-node database synchronization method based on data partitioning according to claim 1, characterized in that: The sharded data in step five is processed in parallel on multiple nodes, and load balancing of read and write requests is achieved in combination with the proxy layer.

7. The multi-node database synchronization method based on data partitioning according to claim 1, characterized in that: The data shards in step six are redundantly backed up on multiple nodes, supporting fast switching in case of failure, and combining heartbeat detection with automatic failover.