A method and device for real-time data synchronization of MySQL master-slave clusters

Abstract

The present invention provides a real-time data synchronization method and device for a MySQL master-slave cluster, which create an auxiliary table T1, a main auxiliary table T2, and a backup auxiliary table T3; a source end performs an update operation on the auxiliary table T1 at preset intervals; a master log of a master node is parsed to obtain a main transaction, and the update operation for the auxiliary table T1 parsed from the master log is used as a main auxiliary transaction, and the main auxiliary transaction and the main transaction submitted by the target end are recorded through the main auxiliary table T2; the update operation for the auxiliary table T1 parsed from the backup log of the backup node is used as a backup auxiliary transaction, and the backup auxiliary transaction is recorded through the backup auxiliary table T3; when the master node stops performing the main synchronization service, the transaction progress of the backup node is adjusted according to the main auxiliary table T2 and the backup auxiliary table T3, so that the progress of the main transaction of the backup node is consistent with the transaction progress when the master node is deactivated, and the backup node starts synchronizing the main transaction to the target end at the adjusted transaction progress.

Description

Technical Field

[0001] The present invention relates to the technical field of data synchronization, and in particular to a method and device for real-time data synchronization of a MySQL master-slave cluster. Background Art

[0002] When the source database is a MySQL master-slave cluster and the target database is a homogeneous or heterogeneous database management system, and real-time data synchronization software is used for real-time data synchronization, in order to avoid interruption of the real-time data synchronization service and ensure the continuity of real-time synchronization, when the master node of the source MySQL database cluster fails, if the MySQL database node fails or the master synchronization service fails, failover is required to continue the real-time data synchronization service on the available database node in the MySQL database cluster to ensure high availability of real-time data synchronization from the MySQL database to the target database.

[0003] Currently, for MySQL database master-slave clusters, log analysis-based real-time data synchronization software typically uses MySQL's global transaction ID to locate synchronization transactions in the local binlog files of the MySQL database master and slave nodes during synchronization failover. This method then resumes real-time data synchronization based on the breakpoint, ensuring synchronization continuity and accuracy. This method requires the MySQL master-slave cluster to use the Global Transaction Identifier (GTID) synchronization mode and is not suitable for clusters using the point-based synchronization mode. Furthermore, GTID-based transaction location requires reading from the binlog file header during failover, and cannot directly locate the synchronization breakpoint.

[0004] In view of this, overcoming the defects of the prior art is an urgent problem to be solved in this technical field. Summary of the Invention

[0005] The technical problem to be solved by the present invention is how to directly locate the synchronization breakpoint under the premise of a cluster applicable to the site mode when the main node performing real-time data synchronization needs to switch with the backup node.

[0006] The present invention adopts the following technical solutions:

[0007] In a first aspect, a method for real-time data synchronization of a MySQL master-slave cluster is provided, comprising:

[0008] Create an auxiliary table T1 on the source side, and create a primary auxiliary table T2 and a backup auxiliary table T3 on the target side; wherein the source side updates the auxiliary table T1 at a preset interval;

[0009] Parse the master log of the master node on the source side to obtain the main transaction, use the update operation for the auxiliary table T1 parsed from the master log as the main auxiliary transaction, and record the main auxiliary transaction and the main transaction submitted by the target side in the main auxiliary table T2;

[0010] The update operation in the auxiliary table T1 parsed from the standby log of the standby node on the source side is used as a standby auxiliary transaction, and the standby auxiliary transaction is recorded in the standby auxiliary table T3;

[0011] When the master node stops performing the master synchronization service, the transaction progress of the standby node is adjusted according to the master auxiliary table T2 and the standby auxiliary table T3 so that the progress of the main transaction of the standby node is consistent with the transaction progress when the master node is deactivated;

[0012] The standby node is switched to a new master node, and transaction synchronization is started from the adjusted transaction progress.

[0013] Preferably, recording the primary and secondary transactions and the main transaction submitted by the target end through the primary and secondary table T2 specifically includes:

[0014] When the target end commits the main transaction synchronized from the master node, the starting position, transaction length and transaction number of the main transaction are recorded in the main auxiliary table T2;

[0015] When the target end executes the primary-secondary transaction, the primary-secondary transaction start position, transaction length, and update timestamp are recorded in the primary-secondary table T2.

[0016] Preferably, recording the backup auxiliary transaction through the backup auxiliary table T3 specifically includes:

[0017] The starting position, transaction length and update timestamp corresponding to the standby auxiliary transaction are recorded in the main auxiliary table T3.

[0018] Preferably, when the master node stops performing the master synchronization service, the transaction progress of the standby node is adjusted according to the master auxiliary table T2 and the standby auxiliary table T3 so that the progress of the main transaction of the standby node is consistent with the transaction progress when the master node is deactivated, specifically including:

[0019] When the master node stops performing the master synchronization service, the master node obtains the last recorded master-auxiliary transaction and the last recorded principal transaction according to the master-auxiliary table T2, and obtains the last recorded backup-auxiliary transaction according to the backup-auxiliary table T3;

[0020] Obtaining a magnitude relationship between the last recorded update timestamp tsA of the primary auxiliary transaction and the last recorded update timestamp tsB of the backup auxiliary transaction, and adjusting the transaction progress of the backup node according to the magnitude relationship;

[0021] The context relationship between the starting position of the last recorded primary and secondary transaction and the starting position of the last recorded main transaction is obtained, and the transaction progress after the first adjustment is secondarily adjusted according to the context relationship.

[0022] Preferably, adjusting the transaction progress of the standby node once according to the size relationship specifically includes:

[0023] When the update timestamp tsA is greater than the update timestamp tsB, the transaction progress of the standby node is advanced to the starting position of the standby auxiliary transaction corresponding to the update timestamp tsA;

[0024] When the update timestamp tsA is less than the update timestamp tsB, the transaction progress of the standby node is traced back to the starting position of the standby auxiliary transaction corresponding to the update timestamp tsA;

[0025] When the update timestamp tsA is equal to the update timestamp tsB, the transaction progress of the standby node is kept unchanged.

[0026] Preferably, the second adjustment of the transaction progress after the first adjustment according to the context relationship specifically includes:

[0027] When the starting position of the last recorded primary and secondary transactions is later than the starting position of the last recorded main transaction, the transaction progress after the one adjustment is kept unchanged;

[0028] When the starting position of the last recorded main transaction is earlier than the starting position of the last recorded primary and secondary transactions, the standby node filters the last recorded main transaction based on the transaction progress after the one adjustment, thereby advancing the transaction progress of the standby node to the starting position of the next main transaction of the last recorded main transaction.

[0029] Preferably, the switching of the standby node to a new master node and the starting of transaction synchronization from the adjusted transaction progress specifically include:

[0030] When the original master node fails, the backup node is switched to the new master node, and the original master node is stopped within a set time.

[0031] Preferably, the switching of the standby node to a new master node and the starting of transaction synchronization from the adjusted transaction progress specifically include:

[0032] When the original node and the backup node switch to each other, the backup node switches to the new primary node and performs transaction synchronization. The original primary node switches to the new backup node, filters the main transaction, and parses the update operation in the auxiliary table T1.

[0033] In a second aspect, a real-time data synchronization device for a MySQL master-slave cluster is provided, comprising at least one processor and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the processor to execute the real-time data synchronization method for the MySQL master-slave cluster.

[0034] In a third aspect, the present invention further provides a non-volatile computer storage medium, wherein the computer storage medium stores computer-executable instructions, and the computer-executable instructions are executed by one or more processors to complete the method described in the first aspect.

[0035] In a fourth aspect, a chip is provided, comprising: a processor and an interface, for calling and running a computer program stored in a memory to execute the method of the first aspect.

[0036] In a fifth aspect, a computer program product comprising instructions is provided, which, when executed on a computer or a processor, causes the computer or the processor to execute the method of the first aspect.

[0037] In a sixth aspect, a real-time data synchronization system for a MySQL master-slave cluster is provided, comprising a real-time data synchronization device for a MySQL master-slave cluster as in the second aspect, and using a real-time data synchronization method for a MySQL master-slave cluster as in the first aspect.

[0038] The present invention provides a real-time data synchronization method and device for a MySQL master-slave cluster, which create an auxiliary table T1, a main auxiliary table T2, and a backup auxiliary table T3; a source end performs an update operation on the auxiliary table T1 at preset intervals; a master log of a master node is parsed to obtain a main transaction, and the update operation for the auxiliary table T1 parsed from the master log is used as a main auxiliary transaction, and the main auxiliary transaction and the main transaction submitted by the target end are recorded through the main auxiliary table T2; the update operation for the auxiliary table T1 parsed from the backup log of the backup node is used as a backup auxiliary transaction, and the backup auxiliary transaction is recorded through the backup auxiliary table T3; when the master node stops performing the main synchronization service, the transaction progress of the backup node is adjusted according to the main auxiliary table T2 and the backup auxiliary table T3, so that the progress of the main transaction of the backup node is consistent with the transaction progress when the master node is deactivated, and the backup node starts synchronizing the main transaction to the target end at the adjusted transaction progress. Among them, since the auxiliary table T1 is created separately, the MySQL master-slave cluster does not need to be limited to using the GTID synchronization mode. The MySQL master-slave cluster using the site mode is also applicable. On the other hand, the file offset corresponding to the transaction recorded in the auxiliary table can be used to directly locate the synchronization breakpoint without having to start reading from the beginning of the binlog log. BRIEF DESCRIPTION OF THE DRAWINGS

[0039] To more clearly illustrate the technical solutions of the embodiments of the present invention, the following briefly introduces the drawings required for use in the embodiments of the present invention. Obviously, the drawings described below are only some embodiments of the present invention. Those skilled in the art can also derive other drawings based on these drawings without inventive effort.

[0040] Figure 1 This is a method flow chart of a real-time data synchronization method for a MySQL master-slave cluster provided by an embodiment of the present invention;

[0041] Figure 2 This is a flow chart of a method for adjusting transaction progress between a master node and a backup node in a real-time data synchronization method for a MySQL master-slave cluster provided by an embodiment of the present invention;

[0042] Figure 3 This is a flow chart of a method for adjusting a standby node in a real-time data synchronization method for a MySQL master-slave cluster provided by an embodiment of the present invention;

[0043] Figure 4 This is a flow chart of a method for secondary adjustment of a standby node in a real-time data synchronization method for a MySQL master-slave cluster provided by an embodiment of the present invention;

[0044] Figure 5The present invention provides a device schematic diagram of a real-time data synchronization device for a MySQL master-slave cluster. DETAILED DESCRIPTION

[0045] In order to make the purpose, technical solutions and advantages of the present invention more clearly understood, the present invention will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present invention and are not intended to limit the present invention.

[0046] In the description of the present invention, it should be understood that the terms "center", "up", "down", "front", "back", "left", "right", "vertical", "horizontal", "top", "bottom", "inside", "outside", etc., indicating the orientation or position relationship, are based on the orientation or position relationship shown in the accompanying drawings, and are only for the convenience of describing the present disclosure and simplifying the description, rather than indicating or implying that the device or element referred to must have a specific orientation, be constructed and operated in a specific orientation, and therefore cannot be understood as a limitation on the present disclosure.

[0047] In the description of the present invention, the terms "first" and "second" are used for descriptive purposes only, and cannot be understood as indicating or implying relative importance or implicitly indicating the number of technical features indicated. Thus, the features defined as "first" and "second" may explicitly or implicitly include one or more of the features. In the description of the embodiments of the present disclosure, unless otherwise specified, "multiple" means two or more. In addition, for example, the description may also use the method of adding "A" and "B" at the end to describe the same type of nouns as two independent individuals. In this case, the corresponding features defined as "A" and "B" are only used to distinguish the description purposes of the same type of individuals, and cannot be understood as indicating or implying relative importance or implicitly indicating the number of technical features indicated.

[0048] As used herein, "about," "substantially," or "approximately" includes the stated value and an average value that is within an acceptable range of deviation from the particular value as determined by one of ordinary skill in the art taking into account the measurements in question and errors associated with measurement of the particular quantity (i.e., limitations of the measurement system).

[0049] Unless the context requires otherwise, throughout the specification and claims, the term "including" is to be interpreted as meaning open inclusion, that is, "including, but not limited to". In the description of the specification, the terms "one embodiment", "some embodiments", "exemplary embodiments", "example", "specific example" or "some examples" and the like are intended to indicate that the specific features, structures, materials or characteristics associated with the embodiment or example are included in at least one embodiment or example of the present disclosure. The schematic representation of the above terms does not necessarily refer to the same embodiment or example. In addition, the specific features, structures, materials or characteristics may be included in any one or more embodiments or examples in any appropriate manner, that is, although they may be carried in the embodiments or examples of the above terms due to reasons such as the order and position of appearance, it is not limited to that they can be carried in combination by one embodiment or example.

[0050] In addition, the technical features involved in the various embodiments of the present invention described below can be combined with each other as long as they do not conflict with each other.

[0051] Embodiment 1:

[0052] This embodiment provides a real-time data synchronization method for a MySQL master-slave cluster. Figure 1 As shown, the method flow includes:

[0053] In step 101 , an auxiliary table T1 is created on the source side, and a main auxiliary table T2 and a backup auxiliary table T3 are created on the target side; wherein the source side updates the auxiliary table T1 at preset intervals.

[0054] In this embodiment, the real-time data synchronization method of the MySQL master-slave cluster is applied to the scenario of synchronizing data from the source database to the target database, wherein the source database can be a MySQL database and the target database can be a homogeneous or heterogeneous database management system.

[0055] The source end includes a master node and a backup node. One of the cluster nodes in the source end database serves as the master node, and a real-time data synchronization service is deployed on the master node as the master synchronization service. Another portion of the cluster nodes in the source end database serves as backup nodes, and a real-time data synchronization service is deployed on the backup nodes as the backup synchronization service. The master node is used to synchronize transaction information from the source end database to the target end, and the target end caches, executes, and commits the synchronized transactions. The backup node is used to replace the master node to continue synchronizing transaction information with the target end when the master node is unable to synchronize transaction information, thereby maintaining the data synchronization process between the source and target ends.

[0056] The auxiliary table T1 is created in the source database cluster and contains a timestamp type field. The master synchronization service updates the auxiliary table T1 at a corresponding frequency. The update operation is recorded in the master log of the master node (i.e., the master node's binlog log file) and the standby log of the standby node (i.e., the standby node's binlog log file).

[0057] In step 102, the main log of the master node is parsed to obtain the main transaction, and the update operation for the auxiliary table T1 parsed from the main log is used as the main auxiliary transaction. The main auxiliary transaction and the main transaction submitted by the target end are recorded through the main auxiliary table T2.

[0058] After parsing the master log of the master node to obtain the main transaction, the master synchronization service will synchronize the transaction information of the main transaction to the target end. After receiving the synchronized transaction information, the target end caches, executes and commits the transaction information.

[0059] The primary synchronization service parses the update operation for the auxiliary table T1 in the primary log to obtain the primary auxiliary transaction, and the backup synchronization service parses the update operation for the auxiliary table T1 in the backup log to obtain the backup auxiliary transaction. The primary auxiliary transaction and the backup auxiliary transaction are used to track the progress of the primary node and the backup node, so that when the backup node needs to replace the primary node to perform the synchronization task, the progress between the primary node and the backup node can be synchronized; the primary auxiliary table T2 and the backup auxiliary table T3 are both created in the target database cluster. The primary auxiliary table T2 is used to record data synchronization breakpoints related to the primary node, and the backup auxiliary table T3 is used to record data synchronization breakpoints related to the backup node, wherein the data synchronization breakpoint is the transaction processing node related to the primary node or the backup node. The corresponding progress of the primary node and the backup node can be obtained through the primary auxiliary table T2 and the backup auxiliary table T3.

[0060] In this embodiment, the main transaction is a task operation that needs to be synchronized and executed between the source-end database and the target-end database, and contains specific task operation content.

[0061] After the primary synchronization service is started, the primary synchronization service on the source end updates the auxiliary table T1 at preset intervals, updates the timestamp field value in the auxiliary table T1 to the current system time, and each update operation on the auxiliary table T1 is recorded in the primary log. In this embodiment, the primary synchronization service parses the update operation on the auxiliary table T1 in the primary log to obtain the primary-auxiliary transaction, and updates it in the primary-auxiliary table T2, so as to obtain the transaction progress of the primary node through the primary-auxiliary transaction.

[0062] In this embodiment, the preset time may be 1 second. It should be noted that both the primary auxiliary transaction and the main transaction are executed after the primary synchronization service is started according to the predetermined order in the primary log and the preset transaction offset interval.

[0063] In step 103 , the update operation in the auxiliary table T1 parsed from the standby log of the standby node on the source side is taken as a standby auxiliary transaction, and the standby auxiliary transaction is recorded in the standby auxiliary table T3 .

[0064] In this embodiment, each update operation on the auxiliary table T1 will be recorded in the standby log. The standby synchronization service parses the update operation on the auxiliary table T1 in the standby log to obtain the standby auxiliary transaction, and updates it in the standby auxiliary table T3 to obtain the transaction progress of the standby node through the standby auxiliary transaction.

[0065] The standby log is also used to record the main transactions that need to be filtered by the standby node; wherein, since the standby node is only used to replace the main node when needed, the standby node does not need to perform the synchronization task of the main transaction when the main node is working normally. However, in order to keep the progress of the standby node tracked, it is still assumed in the records of the standby log that the standby node is performing the synchronization work normally, and the main transaction and the standby auxiliary transaction are recorded in the standby log in the corresponding order. However, since the standby node does not need to synchronize the main transaction, after the standby synchronization service is turned on, the standby log is parsed, and the main transaction recorded in the standby log is filtered, that is, the standby node does not perform the synchronization operation of the main transaction, and only parses the update operation for the auxiliary table T1 in the standby log. It should be noted that the main transactions filtered by the standby node are in the same order as the main transactions executed by the main node.

[0066] It should be noted that, in this embodiment, since the order of the main transactions in the primary log and the backup log is consistent, and the primary auxiliary transactions and the backup auxiliary transactions both correspond to update operations in the auxiliary table T1, the adjacent primary auxiliary transactions or adjacent backup auxiliary transactions are separated by the preset time, so the transaction progress of the primary node can be located through the primary auxiliary transaction, and the transaction progress of the backup node can be located through the backup auxiliary transaction.

[0067] In this embodiment, since the analysis starting points of the primary synchronization service and the backup synchronization service are likely to be different, there is likely to be a difference between the transaction progress of the primary node and the transaction progress of the backup node. Before replacing the primary node with the backup node, the transaction progress of the primary node and the transaction progress of the backup node need to be synchronized. Therefore, this embodiment also involves the following steps:

[0068] In step 104, when the master node stops performing the master synchronization service, the transaction progress of the backup node is adjusted according to the master auxiliary table T2 and the backup auxiliary table T3 so that the progress of the main transaction of the backup node is consistent with the transaction progress when the master node is deactivated.

[0069] In this embodiment, since there are records of primary and secondary transactions in the primary and secondary table T2 and records of secondary and secondary transactions in the secondary and secondary table T3, the primary and secondary transactions last recorded when the primary node is deactivated can be compared with the secondary and secondary transactions last recorded when the primary node is deactivated, so as to determine whether the transaction progress of the secondary node is ahead of or behind the transaction progress of the primary node when the primary node is deactivated, and thus adjust the transaction progress of the secondary node so that the transaction progress of the secondary node is consistent with the transaction progress of the primary node, thereby ensuring that the secondary node can continue the transaction synchronization operation from the breakpoint when the primary node is deactivated.

[0070] In step 105, the standby node is switched to a new master node, and transaction synchronization is started from the adjusted transaction progress.

[0071] In this embodiment, one of the situations is: when the original master node fails, the backup node is switched to the new master node, and the original master node is temporarily stopped from use; another situation is: the master node and the backup node are switched to each other, that is, the backup node is switched to the new master node, and transaction synchronization is performed, the original master node is switched to the new backup node, the main transaction is filtered, and only the update operation in the auxiliary table T1 is parsed.

[0072] It should be noted that, in this embodiment, when the backup node switches to the new master node and starts transaction synchronization from the adjusted transaction progress, the original backup synchronization service is converted to the new master synchronization service to update the auxiliary table T1 at preset time intervals, and the backup auxiliary table T3 corresponds to recording the transaction progress of the new master node; when the master node switches to the new backup node, the master auxiliary table T2 corresponds to recording the transaction progress of the new backup node.

[0073] In this embodiment, the primary synchronization service updates the auxiliary table T1 at a certain frequency, and the backup synchronization service parses the update operation to record the transaction progress of the primary node and the backup node. The primary node and the backup node do not need to adopt the GTID synchronization mode. The primary node and the backup node based on the site mode are also applicable to this method; on the other hand, since the transaction progress of the primary node and the backup node is obtained through the primary auxiliary table T2 and the backup auxiliary table T3, when the backup node needs to replace the primary node, the synchronization breakpoint position of the primary node can be directly located.

[0074] In this embodiment, the master-auxiliary table T2 is used to record the main transactions synchronized from the master node to the target end. At the same time, it is also necessary to record the corresponding master-auxiliary transactions to facilitate the acquisition of the overall transaction progress of the master node. In order to ensure the acquisition of information about related transactions in the master-auxiliary table T2, this embodiment also involves the following designs:

[0075] When the target end commits the main transaction synchronized from the master node, the starting position, transaction length and transaction number of the main transaction are recorded in the main auxiliary table T2;

[0076] When the target end executes the primary-secondary transaction, the primary-secondary transaction start position, transaction length, and update timestamp are recorded in the primary-secondary table T2.

[0077] In this embodiment, the main transaction and the main and auxiliary transactions are executed in a corresponding order, and the relevant information of the main transaction and the main and auxiliary transactions includes the corresponding starting position and transaction length. Each main transaction or main and auxiliary transaction starts from the starting position and performs a corresponding file offset to complete the execution of the corresponding transaction. Therefore, when recording the corresponding transaction, the transaction number of the transaction is recorded to correspond to the specific transaction content, and the position of the transaction in the overall transaction progress is obtained based on the starting position and transaction length of the corresponding transaction.

[0078] Specifically, when the master node synchronizes the main transaction to the target end, the master node sends the transaction ID of the main transaction to the target end. The editing method of the transaction ID can be: the file number of the main transaction in the master log is used as the high 4 bytes, and the corresponding transaction length is used as the low 4 bytes, thereby forming an 8-byte integer as the transaction ID; at the same time, a virtual operation log sequence number is constructed, and the transaction ID, operation log sequence number, operation type and operation data of the main transaction are encapsulated as an internal synchronization data message and sent to the target end to complete the synchronization operation of the main transaction. The target end caches and executes the synchronized main transaction, and after receiving the transaction XID commit operation, performs the commit operation, and records the information of the main transaction in the master auxiliary table T2.

[0079] Furthermore, when the primary synchronization service is started, it is necessary to obtain the initial analysis point from the primary auxiliary table T2. The initial analysis point contains two parts of information: the current binlog file number and the file offset. Then, based on the analysis starting point, the binlog log file position of the source MySQL database is located, and log operation reading and parsing begins.

[0080] For the operation of auxiliary table T1, a transaction record of the T update operation is inserted on the target side, where the site_id field is 0, identifying this operation as a transaction operation of auxiliary table T1, and the ts field records the update time value of T1.

[0081] Correspondingly, the standby auxiliary table T3 also needs to record the relevant information of the corresponding standby auxiliary transaction for tracking the transaction progress of the standby node. Therefore, this embodiment also involves the following design:

[0082] The recording of the backup auxiliary transaction through the backup auxiliary table T3 specifically includes: recording the starting position, transaction length and update timestamp corresponding to the backup auxiliary transaction into the main auxiliary table T3.

[0083] In this embodiment, when the standby synchronization service is started, an initial analysis point is obtained from the standby auxiliary table T3. The initial analysis point contains two pieces of information: the current binlog file number (i.e., the starting position corresponding to the standby auxiliary transaction) and the offset within the file (i.e., the transaction length). Based on the analysis starting point, event information in the local binlog file is read and parsed. After the standby synchronization service is started, the standby log is read from the starting analysis point, and only update operations targeting the auxiliary table T1 are read as standby auxiliary transactions. The main transactions in the binlog are filtered (i.e., discarded). When an update operation to the auxiliary table T1 is read, the update operation is parsed as a standby auxiliary transaction, and the transaction information of the standby auxiliary transaction is sent to the target end. Simultaneously, information about the update operation to the auxiliary table T1 (i.e., the starting position, transaction length, and update timestamp corresponding to the standby auxiliary transaction) is sent to the target end and recorded in the standby auxiliary table T3.

[0084] In this embodiment, during the process of the master node and the backup node performing corresponding synchronization services, when the master node fails or needs to be replaced with the backup node, it is necessary to adjust the current transaction progress of the backup node to the synchronization breakpoint position of the master node to ensure that the backup node can continue data synchronization from the interruption position of the master node. Since the master auxiliary table T2 and the backup auxiliary table T3 record the transaction progress of the master node and the backup node respectively, it is necessary to adjust the transaction progress of the backup node according to the master auxiliary table T2 and the backup auxiliary table T3. Figure 2 As shown, this embodiment involves the following designs:

[0085] In step 201, when the master node stops performing the master synchronization service, the last recorded master-slave transaction and the last recorded principal transaction are obtained from the master-slave table T2, and the last recorded backup-slave transaction is obtained from the backup-slave table T3.

[0086] In step 202, the size relationship between the last recorded update timestamp tsA of the primary auxiliary transaction and the last recorded update timestamp tsB of the backup auxiliary transaction is obtained, and the transaction progress of the backup node is adjusted according to the size relationship.

[0087] Therefore, in this embodiment, when tsA>tsB, it means that the standby node resolution is delayed, and the standby node needs to be further resolved backward until the update timestamp of the standby node on the standby auxiliary table T3 reaches tsB; when tsA<tsB, it means that the standby node resolution is ahead, and the standby node needs to be traced back until the update timestamp of the standby node on the standby auxiliary table T3 is traced back to tsB; through the above adjustment, the main node and the standby node are first aligned in the progress of the update operation on the auxiliary table T1.

[0088] Correspondingly, such as Figure 3 As shown, the method flow includes:

[0089] In step 301, the transaction progress of the standby node is adjusted according to the size relationship.

[0090] In step 302, when the update timestamp tsA is greater than the update timestamp tsB, the transaction progress of the standby node is advanced to the starting position of the standby auxiliary transaction corresponding to the update timestamp tsA.

[0091] In step 303, when the update timestamp tsA is less than the update timestamp tsB, the transaction progress of the standby node is traced back to the starting position of the standby auxiliary transaction corresponding to the update timestamp tsA.

[0092] In step 304, when the update timestamp tsA is equal to the update timestamp tsB, the transaction progress of the standby node is kept unchanged.

[0093] The following table shows the main log for a specific instance:

[0094]

[0095] Among them, the above-mentioned left table is the main log. Between the update operations of adjacent primary and secondary transactions, there are also a corresponding number of main transaction execution processes. When the primary node fails or needs to be replaced by the backup node, the synchronization breakpoint of the primary node may be located in one of the main transactions. Therefore, it is not enough to simply adjust the transaction progress of the backup node to the primary and secondary table T2 to obtain the last recorded primary and secondary transaction. A corresponding secondary adjustment is also required to adjust the transaction progress of the backup node to before the specific main transaction where the interruption occurred. Therefore, Figure 2 As shown, this embodiment also involves the following steps:

[0096] In step 203, the context relationship between the start position of the last recorded primary and secondary transaction and the start position of the last recorded main transaction is obtained, and the transaction progress after the first adjustment is secondarily adjusted according to the context relationship.

[0097] In this embodiment, by judging the relationship between the last recorded primary and secondary transactions and the last recorded main transaction, it is judged whether the synchronization breakpoint of the primary node is located on the last recorded primary and secondary transactions or the last recorded main transaction. If the last recorded primary and secondary transactions are earlier, it means that the synchronization breakpoint of the primary node is located on the last recorded main transaction, and the transaction progress of the backup node needs to be adjusted to the corresponding main transaction for the second time; if the last recorded main transaction is earlier, it means that the synchronization breakpoint of the primary node is located on the last recorded primary and secondary transactions, and no secondary adjustment is required, because the previous adjustment has already adjusted the transaction progress of the backup node to the synchronization breakpoint.

[0098] Specifically, such as Figure 4 As shown, the method flow is as follows:

[0099] In step 401, a second adjustment is made to the transaction progress after the first adjustment according to the context relationship.

[0100] In step 402, when the starting position of the last recorded primary-secondary transaction is earlier than the starting position of the last recorded principal transaction, the transaction progress after the one adjustment is kept unchanged.

[0101] In step 403, when the starting position of the last recorded main transaction is earlier than the starting position of the last recorded primary and secondary transactions, the standby node filters the last recorded main transaction based on the transaction progress after the adjustment, and advances the transaction progress of the standby node to the starting position of the next main transaction of the last recorded main transaction.

[0102] Example 2:

[0103] In this embodiment, in order to more clearly demonstrate the method provided by this embodiment, the corresponding method flow is demonstrated using the following example.

[0104] Create an auxiliary table T1 in the source MySQL database cluster, containing a timestamp column, as shown in the following example:

[0105] create table T1(id int, current_ts timestamp(0));

[0106] Initially, a time record is inserted.

[0107] insert into t1 values(1, current_timestamp());

[0108] commit;

[0109] On the target side, create a primary auxiliary table T2 and a backup auxiliary table T3 to record the data synchronization breakpoints of the primary and backup nodes at the current source side, respectively. These tables contain the site ID, transaction ID, commit LSN, and the timestamp value of the auxiliary table T1. The structures of the primary and backup auxiliary tables T2 and T3 are as follows:

[0110]

[0111] The master and slave binlog operation logs of the source MySQL database are as follows:

[0112]

[0113] The corresponding process of the main synchronization service is as follows:

[0114] The master log is read and parsed, and sent to the target end. After receiving the transaction operation, the target end caches and executes it according to the transaction ID. When the XID commit operation of the transaction is received, the commit operation is performed and the transaction information of the currently committed main transaction is recorded in the master auxiliary table T2.

[0115] For the operation of auxiliary table T1, a transaction record of the T update operation is inserted on the target side, where the site_id field is 0, identifying this operation as a transaction operation of auxiliary table T1, and the ts field records the update time value of T1.

[0116] Standby synchronization service workflow: Only update operations on auxiliary table T1 in the standby log are analyzed as standby auxiliary transactions, and other operations are filtered out.

[0117] Take the following scenario as an example:

[0118] When the primary synchronization service fails or the current MySQL database fails, the backup synchronization service needs to take over the synchronization link, that is, to locate the transaction progress of the backup node to the synchronization breakpoint of the primary node, and replace the primary node to continue synchronizing data to the target end.

[0119] Assume that the primary synchronization service fails while analyzing transaction 5. The transaction progress of the primary node, that is, the primary-auxiliary table T2, is as follows:

[0120]

[0121] From the primary auxiliary table T2, we can see that the update timestamp of the last record is ts2.

[0122] At this time, the transaction progress of the standby node, that is, the standby auxiliary table T3, has the following three situations:

[0123] (1) The standby synchronization service only resolves to ts1, and the standby auxiliary table T3 is as follows:

[0124]

[0125] According to the auxiliary table T3, the update timestamp of the last record is ts1.

[0126] Since ts2 is greater than ts1, it is determined that there is a parsing delay on the current standby node. According to the binlog operation log, the standby node needs to continue parsing to position 1325 to reach the corresponding update timestamp ts2.

[0127] Comparing the primary-auxiliary table T2 and the primary binlog operation log, we can see that since the primary node failed at transaction 5, the transaction progress of the primary node also includes transaction 4. Therefore, when synchronizing the transaction progress of the standby node, the standby synchronization service also needs to filter out transaction 4.

[0128] After filtering out transaction 4, the standby synchronization service locates position 1417 and starts transaction synchronization.

[0129] (2) The backup synchronization service is parsed to ts2, and the backup auxiliary table T3 is as follows:

[0130]

[0131] According to the backup auxiliary table T3, the last recorded update timestamp is ts2; therefore, the transaction progress of the backup node is not much different from that of the primary node.

[0132] Comparing the primary-auxiliary table T2 and the primary binlog operation log, we can see that since the primary node failed at transaction 5, the transaction progress of the primary node also includes transaction 4. Therefore, when synchronizing the transaction progress of the standby node, the standby synchronization service also needs to filter out transaction 4.

[0133] After filtering out transaction 4, the standby synchronization service locates position 1417 and starts transaction synchronization.

[0134] (3) The backup synchronization service is parsed to ts3, and the backup auxiliary table T3 is as follows:

[0135]

[0136] According to the auxiliary table T3, the update timestamp of the last record is ts3.

[0137] Since ts3 is greater than ts2, it is determined that the current standby node is parsing ahead of time. According to the binlog operation log, the standby node needs to backtrack and parse to position 1376.

[0138] Comparing the primary-auxiliary table T2 and the primary binlog operation log, we can see that since the primary node failed at transaction 5, the transaction progress of the primary node also includes transaction 4. Therefore, when synchronizing the transaction progress of the standby node, the standby synchronization service also needs to filter out transaction 4.

[0139] After filtering out transaction 4, the standby synchronization service locates position 1417 and starts transaction synchronization.

[0140] Another scenario is that when the primary and backup nodes of the MySQL database need to switch, the backup synchronization service needs to take over the synchronization link and become the new primary synchronization service.

[0141] It should be noted that, in this embodiment, when the standby node switches to the new master node and starts transaction synchronization from the adjusted transaction progress, the standby auxiliary table T3 records the transaction progress of the new master node; when the master node switches to the new standby node, the master auxiliary table T2 records the transaction progress of the new standby node.

[0142] In this scenario, the standby node locates the new starting analysis point in the same manner as in the case of a primary node failure. However, after the switchover between the primary and standby nodes, the original primary synchronization service does not fail and continues to parse and read subsequent logs. In this case, when the original primary synchronization service switches to the new standby synchronization service, it only parses operations on the auxiliary table T1, filtering out other operations and switching to the standby synchronization process. This process is consistent with the handling of the primary node failure described above and will not be repeated here.

[0143] In summary, the above two situations are: in one situation, when the original master node fails, the backup node is switched to the new master node, and the original master node is temporarily stopped from use; in the other situation, the master node and the backup node are switched to each other, that is, the backup node is switched to the new master node, and transaction synchronization is performed, and the original master node is switched to the new backup node, and the main transaction is filtered and processed, and only the update operation in the auxiliary table T1 is parsed.

[0144] Example 3:

[0145] like Figure 5 FIG2 is a schematic diagram of a device for real-time data synchronization of a MySQL master-slave cluster according to an embodiment of the present invention. The device for real-time data synchronization of a MySQL master-slave cluster according to this embodiment includes one or more processors 41 and a memory 42 .

[0146] The processor 41 and the memory 42 may be connected via a bus or other means. Figure 5 The bus connection is taken as an example.

[0147] Memory 42, as a nonvolatile computer-readable storage medium, can be used to store nonvolatile software programs and nonvolatile computer executable programs, such as the real-time data synchronization method for a MySQL master-slave cluster in the above-described embodiment. Processor 41 executes the real-time data synchronization method for a MySQL master-slave cluster by running the nonvolatile software programs and instructions stored in memory 42.

[0148] The memory 42 may include high-speed random access memory and non-volatile memory, such as at least one disk storage device, flash memory device, or other non-volatile solid-state memory device. In some embodiments, the memory 42 may optionally include a memory remotely located relative to the processor 41, and such remote memory may be connected to the processor 41 via a network. Examples of such networks include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0149] The program instructions / modules are stored in the memory 42, and when executed by the one or more processors 41, the real-time data synchronization method of the MySQL master-slave cluster in the above embodiment is executed, for example, the real-time data synchronization method of the MySQL master-slave cluster described above is executed. Figures 1-4 The steps shown.

[0150] An embodiment of the present invention further provides a computer storage medium having computer program instructions stored thereon; when the computer program instructions are executed by a processor, the real-time data synchronization method for a MySQL master-slave cluster provided by an embodiment of the present invention is implemented.

[0151] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions and improvements made within the spirit and principles of the present invention should be included in the scope of protection of the present invention.

Claims

1. A real-time data synchronization method for a MySQL master-slave cluster, characterized in that: include: Create an auxiliary table T1 on the source side, and create a primary auxiliary table T2 and a backup auxiliary table T3 on the target side; wherein the source side updates the auxiliary table T1 at a preset interval; Parse the master log of the master node on the source side to obtain the main transaction, use the update operation for the auxiliary table T1 parsed from the master log as the main auxiliary transaction, and record the main auxiliary transaction and the main transaction submitted by the target side in the main auxiliary table T2; The update operation in the auxiliary table T1 parsed from the standby log of the standby node on the source side is used as a standby auxiliary transaction, and the standby auxiliary transaction is recorded in the standby auxiliary table T3; When the master node stops performing the master synchronization service, the transaction progress of the standby node is adjusted according to the master auxiliary table T2 and the standby auxiliary table T3 so that the progress of the main transaction of the standby node is consistent with the transaction progress when the master node is deactivated; The standby node is switched to a new master node, and transaction synchronization is started from the adjusted transaction progress.

2. The real-time data synchronization method of the MySQL master-slave cluster according to claim 1, characterized in that: The main auxiliary transaction and the main transaction submitted by the target end are recorded through the main auxiliary table T2, specifically including: When the target end commits the main transaction synchronized from the master node, the starting position, transaction length and transaction number of the main transaction are recorded in the main auxiliary table T2; When the target end executes the primary-secondary transaction, the primary-secondary transaction start position, transaction length, and update timestamp are recorded in the primary-secondary table T2.

3. The real-time data synchronization method of the MySQL master-slave cluster according to claim 1, characterized in that: The recording of the standby auxiliary transaction by the standby auxiliary table T3 specifically includes: The starting position, transaction length and update timestamp corresponding to the standby auxiliary transaction are recorded in the main auxiliary table T3.

4. The real-time data synchronization method of the MySQL master-slave cluster according to claim 1, characterized in that: When the master node stops performing the master synchronization service, adjusting the transaction progress of the standby node according to the master auxiliary table T2 and the standby auxiliary table T3 so that the progress of the main transaction of the standby node is consistent with the transaction progress when the master node is deactivated, specifically includes: When the master node stops performing the master synchronization service, the master node obtains the last recorded master-auxiliary transaction and the last recorded principal transaction according to the master-auxiliary table T2, and obtains the last recorded backup-auxiliary transaction according to the backup-auxiliary table T3; Obtaining a magnitude relationship between the last recorded update timestamp tsA of the primary auxiliary transaction and the last recorded update timestamp tsB of the backup auxiliary transaction, and adjusting the transaction progress of the backup node according to the magnitude relationship; The context relationship between the starting position of the last recorded primary and secondary transaction and the starting position of the last recorded main transaction is obtained, and the transaction progress after the first adjustment is secondarily adjusted according to the context relationship.

5. The real-time data synchronization method of the MySQL master-slave cluster according to claim 4, characterized in that: The adjusting the transaction progress of the standby node according to the size relationship specifically includes: When the update timestamp tsA is greater than the update timestamp tsB, the transaction progress of the standby node is advanced to the starting position of the standby auxiliary transaction corresponding to the update timestamp tsA; When the update timestamp tsA is less than the update timestamp tsB, the transaction progress of the standby node is traced back to the starting position of the standby auxiliary transaction corresponding to the update timestamp tsA; When the update timestamp tsA is equal to the update timestamp tsB, the transaction progress of the standby node is kept unchanged.

6. The real-time data synchronization method of the MySQL master-slave cluster according to claim 4, characterized in that: The second adjustment of the transaction progress after the first adjustment according to the contextual relationship specifically includes: When the starting position of the last recorded primary and secondary transactions is later than the starting position of the last recorded main transaction, the transaction progress after the one adjustment is kept unchanged; When the starting position of the last recorded main transaction is earlier than the starting position of the last recorded primary and secondary transactions, the standby node filters the last recorded main transaction based on the transaction progress after the one adjustment, thereby advancing the transaction progress of the standby node to the starting position of the next main transaction of the last recorded main transaction.

7. The real-time data synchronization method of the MySQL master-slave cluster according to claim 1, characterized in that: Switching the standby node to a new master node and starting transaction synchronization from the adjusted transaction progress specifically includes: When the original master node fails, the backup node is switched to the new master node, and the original master node is stopped within a set time.

8. The real-time data synchronization method of the MySQL master-slave cluster according to claim 1, characterized in that: Switching the standby node to a new master node and starting transaction synchronization from the adjusted transaction progress specifically includes: When the original node and the backup node switch to each other, the backup node switches to the new primary node and performs transaction synchronization. The original primary node switches to the new backup node, filters the main transaction, and parses the update operation in the auxiliary table T1.

9. A real-time data synchronization device for a MySQL master-slave cluster, characterized in that: The method comprises at least one processor and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the processor to execute the real-time data synchronization method for a MySQL master-slave cluster according to any one of claims 1 to 8.

10. A non-volatile computer storage medium, characterized in that The computer storage medium stores computer program instructions, which, when executed by one or more processors, implement the real-time data synchronization method for a MySQL master-slave cluster according to any one of claims 1 to 8.

Images