Cluster synchronization method based on MySQL

Abstract

The present invention provides a MYSQL-based cluster synchronization method, which is characterized by comprising: a step of determining whether the GTID in an update notification is native to MYSQL; if so, executing steps A1 to A2; otherwise, executing step B: step A1: replacing part of the characters of source_id in the GTID with custom semantic information; step A2: pushing the update notification injected with the custom semantic information; step B1: parsing the GTID, determining whether the target to be pushed of the update notification is the updated source cluster; if so, ignoring the update notification; if not, pushing the update notification; the custom semantic information includes the source cluster identifier generated by the update notification.

Description

Technical Field

[0001] The method of the present invention relates to a data center synchronization system, and in particular to a cluster synchronization method based on MYSQL. Background Art

[0002] Building a database system based on MYSQL is a common solution in current data centers, but for active-active or even multi-active clusters, data loopback is an unavoidable problem. Figure 1 For example, consider a dual-active cluster in cluster A: A piece of data X is updated first. Cluster B needs to synchronize with cluster A, so it subscribes to each other's data changes. The update to X is pushed to cluster B, which then pushes the update to cluster A... and so on indefinitely. The term "update" is used broadly to include operations that require inter-cluster synchronization, such as changes and additions to data. Unless otherwise specified, this definition applies to updates mentioned below.

[0003] Currently, solutions to this data loop problem fall into two categories: one is invasive, such as exploiting the fact that MySQL's master database writes binary logs when data changes occur. This approach rewrites the MySQL binlog generation logic and adds custom identifiers to record synchronization information. The other is non-invasive, such as maintaining an additional table specifically for recording synchronization information or adding a hidden column to the table requiring data synchronization.

[0004] However, both of these solutions have drawbacks: Intrusive implementations place high demands on developers and are prone to bugs and other development risks. Relying on recording additional Binlog Query Log events wastes significant storage resources. Adding hidden columns can impact related businesses and introduce systemic risks. Dedicated maintenance of tables introduces additional network interactions, significantly increasing network latency.

[0005] Building a database system based on MySQL is also often faced with idempotence issues. An idempotence issue is that, under the same conditions, operations on a business should produce the same results no matter how many times they are performed. However, in complex systems, because a single business may involve multiple systems, and these systems may be independently deployed, network issues and other factors can cause inconsistent results after multiple operations on the same business. For example, order and inventory services may be deployed in two clusters and synchronized with each other. After a Binlog event for a product deduction is consumed again, due to synchronization link failures or network retransmissions, the same -1 Binlog event may be repeatedly pulled and consumed, resulting in excessive inventory deductions.

[0006] MySQL itself provides the GTID (Global Transaction Identifier) ​​mechanism to address idempotence issues. For example, encapsulating the order and inventory operations in a single transaction, sharing a single GTID, logically avoids idempotence issues. However, in practice, for large transactions, simply encapsulating all related operations in a single transaction consumes significant memory and can severely block synchronization, leading to system availability issues. Summary of the Invention

[0007] In view of the shortcomings of the prior art described above, the present invention provides a cluster synchronization method based on MYSQL, which is characterized in that it includes: a step of determining whether the GTID in the update notification is native to MYSQL. If so, execute steps A1 to A2, otherwise execute step B: Step A1: replace part of the characters of source_id in the GTID with custom semantic information; Step A2: push the update notification injected with the custom semantic information; Step B1: parse the GTID and determine whether the target to be pushed of the update notification is the updated source cluster. If so, ignore the update notification; if not, push the update notification; the custom semantic information includes the source cluster identifier generated by the update notification.

[0008] Preferably, in the above-mentioned MYSQL-based cluster synchronization method, the method for determining whether the GTID in the update notification is MYSQL is to determine the Version bit in the GTID. If the value is 1, it indicates a MYSQL native GTID.

[0009] Preferably, in the above-mentioned MYSQL-based cluster synchronization method, step A1 further includes a step of inverting the Version bit in the GTID.

[0010] Preferably, in the above-mentioned MYSQL-based cluster synchronization method, the custom semantic information also includes a sub-transaction ID.

[0011] Preferably, in the above-mentioned MYSQL-based cluster synchronization method, the step of pushing the update notification further includes: recording the updated row count, and if the row count exceeds a preset threshold, executing: generating an XIDEvent; mocking a new GTID Event; and incrementing the sub-transaction ID by 1.

[0012] Preferably, in the above-mentioned MYSQL-based cluster synchronization method, one character in the source_id of the GTID is replaced with the cluster ID; and five characters in the source_id of the GTID are replaced with the sub-transaction ID.

[0013] Preferably, the above-mentioned MYSQL-based cluster synchronization method further includes a step of compressing the GTID.

[0014] Preferably, in the above-mentioned MYSQL-based cluster synchronization method, the compression method includes: a step of converting the sub-transaction ID into a binary sequence; and a step of mapping other characters in the GTID into a unified code.

[0015] Preferably, in the above-mentioned MYSQL-based cluster synchronization method, the sub-transaction ID is converted into a 20-bit binary sequence.

[0016] Preferably, in the above-mentioned MYSQL-based cluster synchronization method, other characters in the GTID are mapped into a unified 12-bit binary code. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] Figure 1 This is a schematic diagram of an active-active cluster;

[0018] Figure 2 This is a schematic diagram of the structure of MYSQL native GTID;

[0019] Figure 3 It is a flow chart of an embodiment of the present invention. DETAILED DESCRIPTION

[0020] The following describes the embodiments of the present invention through specific examples. Those skilled in the art will readily understand the other advantages and benefits of the present invention from the disclosure herein. The present invention may also be implemented or applied through various other specific embodiments, and the details in this specification may be modified or altered based on different viewpoints and applications without departing from the spirit of the present invention.

[0021] GTID is a native feature of YSQL. Its full name is Global Transaction Identifier, which is used to uniquely identify a transaction in the binlog. Before committing a transaction, you can explicitly specify the GTID of the current transaction. If not, MySQL will generate a globally unique GTID. When a transaction is committed, a special Binlog Event of type GTIDS_Event will be written at the beginning of the Binlog Event of a transaction to identify the start of the current transaction and the GTID of this transaction, and then the Binlog of the transaction will be written. When synchronizing across clusters, Figure 1In the dual-cluster synchronization scenario, a common synchronization implementation is for clusters A and B to subscribe to each other's Binlogs. When data in cluster A is updated, the replicator deployed in cluster A pushes the corresponding Binlog to cluster B, which then updates its own data based on the Binlog.

[0022] The GTID structure is: "source_id:transaction_id", where "source_id" is the server that generates the GTID, and "transaction_id" is a serial number. It is a self-increasing series starting from 1 on each MySQL server and is a globally unique identifier for a transaction. Together, they ensure the global uniformity of MySQL transactions, which is also the solution to the MySQL idempotence problem mentioned above.

[0023] The "source_id" in GTID is a format like Figure 2 In the string shown, each placeholder "X" represents a hexadecimal number. Substrings 1 through 3 are related to the timestamp. Substring 4 is a pseudorandom number whose random factor is related to the wall clock at the time of random number generation, the current mysqld PID, and the memory address of the current mysqld temporary THD. Substring 5 is related to the MYSQL server's MAC address.

[0024] As can be seen from the source_id format, GTID uses three factors to ensure the uniqueness of source_id: time, random number, and MAC address. The default timestamp length used by MySQL allows for time accuracy of 100ns. This means that as long as the initialization interval between two MySQL instances with the same MAC address on the same physical machine is greater than 100ns, source_id will be unique even without relying on random numbers. Similarly, reducing the timestamp length will reduce time accuracy. However, increasing the initialization interval between two MySQL instances can still ensure source_id uniqueness without relying on random numbers.

[0025] The present invention utilizes the GTID generation mechanism of MYSQL, edits it, adds custom semantic information, and achieves the purpose of the present invention based on this custom semantic information. Figure 3 , Figure 3 This is a flow chart of an embodiment of the present invention.

[0026] Step S1 determines whether the GTID in the update notification is a native MySQL GTID. If so, it does not contain custom semantic information, and the process proceeds to step S2. If not, the process proceeds to step S3. One method for determining this is to examine the version bit in the MySQL GTID, which is represented by the first character of the substring 3 of source_id. Alternatively, the determination can be made based on the encoding rules used in step S2.

[0027] Step S2 represents injecting custom semantic information into the native GTID. The custom semantic information here should include the cluster identifier of the update source. Figure 1 In the example, the native GTID generated by update number 1 is: 6ebc9f7e-20e2-11ec-b861-525400fb0df3:19. In the first implementation, the eighth character (i.e., "e") of substring 1 "6ebc9f7e" is truncated and replaced with custom semantic information. In this implementation, it is replaced with the identifier (ID) of cluster A. As mentioned above, source_id is represented in hexadecimal, so the actual length of one character is 4 bits. 4 bits can represent 16 different encodings, which means that by truncating 1 bit, the synchronization of up to 16 clusters can be supported. If you need to support the synchronization of larger clusters, you can consider truncating more bits of the timestamp and replacing them with the cluster identifier. As mentioned above, shortening the length of the timestamp will lead to a decrease in time accuracy. If the last 6 characters in substring 1 are all intercepted, the time accuracy will drop to 2s. That is, it is necessary to ensure that the initialization time interval between any two MYSQL instances using the same MAC address on the same physical machine is greater than 2s in order to ensure the uniqueness of source_id without relying on random number factors. This condition is not harsh in practice, so the decrease in time accuracy caused by replacing the characters in substring 1 with custom semantic information will not affect the practicality of the present invention. In order to improve the judgment efficiency of step S1, the custom semantic information can also include adding a modification mark to source_id. Specifically, in this example, the Version bit of substring 3 of source_id is inverted, that is, substring 3 is changed to "01ec".

[0028] Step S3 indicates determining whether the cluster identifier in the update notification to be pushed is the same as the identifier of the push target. If they are not identical, the update notification is pushed; if they are identical, the update push is abandoned, thereby preventing data loops.

[0029] The above steps enable non-invasive data loop avoidance. However, to address the issues caused by large transactions, the present invention also provides a more preferred implementation method, which involves splitting a large transaction into several smaller subtransactions by adding subtransaction identifiers to custom semantic information. Specifically, in step S2, more characters from substring 1 are truncated and replaced with the subtransaction ID. For example, characters from bits 3 to 8 of the substring are truncated, with the third bit replaced with the cluster identifier; bits 4 to 8 are reset to zero, resulting in substring 1 becoming "6e000000." Update notifications are then pushed, and the row count of the update operation is recorded. If the row count exceeds a preset threshold, the transaction is considered too large and needs to be split. In this case, an XID event is explicitly generated, followed by a mock GTID event, and the subtransaction ID portion is incremented by 1 (equivalent to splitting a subtransaction). Finally, the custom semantic information is replaced with the source_id according to the above steps. However, during the replacement, the subtransaction identifier portion is also incremented by 1, resulting in substring 1 becoming "6e000001." In this way, the cluster that receives the update notification can restore the large transaction through the sub-transaction identifier.

[0030] According to the implementation method of the custom semantic information in the above embodiment, for the updates involved in the same native GTID, only the 4th to 8th bits in substring 1 of source_id will change (the part reserved for subtransaction extension), and the rest of the positions will remain unchanged. Taking this feature into consideration, a more preferred method includes the step of compressing source_id. An optional compression method is to map the unchanged part of source_id into a fixed code and convert the subtransaction extension part into a binary string. Continuing with the above example, the source_id with custom semantic information becomes: 6e0 00000 -20e2-01ec-b861-525400fb0df3, where only the double-underlined portion is variable, totaling five characters. As mentioned earlier, the fifth character is actually a hexadecimal number, so each character only requires 4 bits to represent. Therefore, the five double-underlined characters require only a 20-bit binary sequence to represent. The remaining portion, since it does not change, can be encoded arbitrarily. Given that the cluster ID in the above example uses 4 bits, the encoding length of the remaining portion should be greater than 4 bits. Considering the data processing characteristics of computers, 32-bit encoding is more efficient, so the encoding of the unchanging portion in the above example uses 12 bits. Thus, the original GTID transmission occupies 37 bytes, but after compression, it is reduced to 4 bytes, greatly improving transmission efficiency.

[0031] The above embodiments are merely illustrative of the principles and effectiveness of the present invention and are not intended to limit the present invention. Anyone skilled in the art may modify or alter the above embodiments without departing from the spirit and scope of the present invention. Furthermore, the slaves, nodes, systems, and databases of the present invention are all logical entities. In practice, they can be physically separate computers equipped with software and hardware, or simply software modules with the same functionality on a general-purpose computer.

Claims

1. A cluster synchronization method based on MYSQL, characterized in that: include: Determine whether the GTID in the update notification is MYSQL native. If so, execute steps A1 and A2. Otherwise, execute step B1. ： Step A1: Replace some characters of source_id in the GTID with custom semantic information, including: replacing one character in source_id in the GTID with the cluster ID, and replacing five characters in source_id in the GTID with the subtransaction ID; Step A2: Push an update notification injected with custom semantic information and record the updated row count. If the updated row count exceeds the preset threshold, execute the following: generate an XID event; mock a new GTID event; and increment the sub-transaction ID by 1. Step B1: Parse the GTID and determine whether the target of the update notification is the source cluster of the update. If yes, ignore the update notification; if not, push the update notification. The custom semantic information includes the source cluster identifier generated by the update notification.

2. The MYSQL-based cluster synchronization method according to claim 1, characterized in that: The method for determining whether the GTID in the update notification is MYSQL is to determine the Version bit in the GTID. If the value is 1, it indicates a MYSQL native GTID.

3. The MYSQL-based cluster synchronization method according to claim 1, characterized in that: The step A1 also includes the step of inverting the Version bit in the GTID.

4. The MYSQL-based cluster synchronization method according to claim 1, characterized in that: The method also includes a step of compressing the GTID.

5. The MYSQL-based cluster synchronization method according to claim 4, characterized in that: The compression method comprises: The step of converting the sub-transaction ID into a binary sequence; The step of mapping other characters in the GTID into a unified code.

6. The MYSQL-based cluster synchronization method according to claim 5, characterized in that: The sub-transaction ID is converted into a 20-bit binary sequence.

7. The MYSQL-based cluster synchronization method according to claim 5, characterized in that: Map the other characters in the GTID into a unified 12-bit binary code.

Images