Data real-time synchronization method and device for MySQL master-slave cluster

Abstract

The invention provides a MySQL master-slave cluster data real-time synchronization method and device. The method comprises the following steps: creating an auxiliary table T1, a master auxiliary table T2 and a standby auxiliary table T3; the source end updates the auxiliary table T1 every preset time; analyzing a main log of the main node to obtain a main transaction, taking an updating operation which is analyzed from the main log and aims at the auxiliary table T1 as a main auxiliary transaction, and recording the main auxiliary transaction and the main transaction submitted by the target end through a main auxiliary table T2; taking an updating operation which is analyzed from a standby log of the standby node and aims at the auxiliary table T1 as a standby auxiliary transaction, and recording the standby auxiliary transaction through a standby auxiliary table T3; and when the main node stops performing the main synchronization service, the transaction progress of the standby node is adjusted according to the main 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 main node is stopped, and the standby node starts to synchronize the main transaction to the target end according to 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, 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 nodes in the MySQL database cluster to ensure high availability of real-time data synchronization from the MySQL database to the target database.

[0003] At present, for the master-slave cluster of MySQL database, the conventional method for real-time data synchronization software based on log analysis during synchronization fault switching is to use MySQL's global transaction ID to locate the synchronization transaction in the local binlog log file of the master and slave nodes of the MySQL database, obtain the synchronization breakpoint when the failure occurs, and then continue the real-time data synchronization based on the breakpoint position to ensure the continuity and correctness of synchronization. This method requires the MySQL master-slave cluster to use the global transaction ID (GTID) synchronization mode, which is not suitable for clusters based on the site mode. On the other hand, transaction positioning based on GTID needs to be read from the binlog file header during fault recovery, 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 the field of this technology. 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 suitable for 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 solution: In a first aspect, a method for real-time data synchronization of a MySQL master-slave cluster is provided, comprising: An auxiliary table T1 is created at the source end, and a main auxiliary table T2 and a backup auxiliary table T3 are created at the target end; wherein the source end updates the auxiliary table T1 at preset intervals; The main log of the master node on the source side is parsed to obtain the main transaction, the update operation for the auxiliary table T1 parsed from the main log is used as the 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 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 through 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.

[0007] Preferably, recording the main auxiliary transaction and the main transaction submitted by the target end through the main auxiliary table T2 specifically includes: 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.

[0008] Preferably, recording the standby auxiliary transaction through 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.

[0009] 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: When the master node stops performing the master synchronization service, the last recorded master auxiliary transaction and the last recorded principal transaction are obtained according to the master auxiliary table T2, and the last recorded backup auxiliary transaction is obtained according to the backup auxiliary table T3; Obtaining 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, and adjusting the transaction progress of the backup node once according to the size 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.

[0010] Preferably, adjusting the transaction progress of the standby node once 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.

[0011] Preferably, the second adjustment of the transaction progress after the first adjustment according to the context relationship specifically includes: When the starting position of the last recorded primary and secondary transaction 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 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.

[0012] Preferably, the switching of 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.

[0013] Preferably, the switching of 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.

[0014] 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.

[0015] 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.

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

[0017] According to a fifth aspect, a computer program product comprising instructions is provided. When the instructions are executed on a computer or a processor, the computer or the processor executes the method according to the first aspect.

[0018] 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.

[0019] 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 standby auxiliary table T3; the source end performs an update operation on the auxiliary table T1 at preset intervals; the main log of the master node is parsed to obtain a main transaction, the update operation for the auxiliary table T1 parsed from the main 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 standby log of the standby node is used as a standby auxiliary transaction, and the standby auxiliary transaction is recorded through the standby auxiliary table T3; when the master node stops performing the main synchronization service, the transaction progress of the standby node is adjusted according to the main 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, and the standby node starts to synchronize the main transaction to the target end with 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 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 starting reading at the beginning of the binlog log. BRIEF DESCRIPTION OF THE DRAWINGS

[0020] In order 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, and for ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work.

[0021] Figure 1 This is a method flow chart of a method for real-time data synchronization of a MySQL master-slave cluster provided by an embodiment of the present invention; Figure 2 It is a flow chart of a method for adjusting the transaction progress of a master node and a standby node in a real-time data synchronization method for a MySQL master-slave cluster provided by an embodiment of the present invention; Figure 3 It is a method flow chart of one-time 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; Figure 4 It 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; Figure 5 The present invention is a schematic diagram of a device for real-time synchronization of data of a MySQL master-slave cluster provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0022] In order to make the purpose, technical solution and advantages of the present invention more clearly understood, the present invention is further described in detail below in conjunction with 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.

[0023] In the description of the present invention, it is necessary to understand that the terms "center", "up", "down", "front", "back", "left", "right", "vertical", "horizontal", "top", "bottom", "inside", "outside", etc., indicating orientations or positional relationships, are based on the orientations or positional relationships shown in the accompanying drawings, and are only for the convenience of describing the present disclosure and simplifying the description, and do not indicate or imply that the device or element referred to must have a specific orientation, be constructed and operated in a specific orientation, and therefore should not be understood as a limitation on the present disclosure.

[0024] In the description of the present invention, the terms "first" and "second" are used only for descriptive purposes, and cannot be understood as indicating or implying relative importance or implicitly indicating the number of indicated technical features. 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, the meaning of "multiple" is two or more. In addition, for example, the same type of nouns may be described as two independent individuals by adding "A" and "B" at the end. In this case, the corresponding features defined as "A" and "B" are only used to distinguish the same type of individuals for description purposes, and cannot be understood as indicating or implying relative importance or implicitly indicating the number of indicated technical features.

[0025] 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 the errors associated with the measurement of the particular quantity (i.e., the limitations of the measurement system).

[0026] Unless the context requires otherwise, throughout the specification and claims, the term "including" is to be interpreted as open inclusion, that is, "including, but not limited to". In the description of the specification, the terms "one embodiment", "some embodiments", "exemplary embodiments", "examples", "specific examples" or "some examples" and the like are intended to indicate that 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.

[0027] 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.

[0028] Embodiment 1: This embodiment provides a real-time data synchronization method for a MySQL master-slave cluster. Figure 1 As shown, the method flow includes: In step 101, an auxiliary table T1 is created at the source end, and a main auxiliary table T2 and a backup auxiliary table T3 are created at the target end; wherein the source end updates the auxiliary table T1 at preset intervals.

[0029] In this embodiment, the real-time data synchronization method of the MySQL master-slave cluster is applied to a scenario where a source database synchronizes data with a target database, wherein the source database may be a MySQL database and the target database may be a homogeneous or heterogeneous database management system.

[0030] The source end includes a master node and a backup node. One of the cluster nodes in the source database serves as the master node, and a real-time data synchronization service is deployed on the master node as the master synchronization service. There are also some cluster nodes in the source database 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 the transaction information of the source 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 the transaction information to the target end when the master node cannot synchronize the transaction information, so as to maintain the data synchronization process between the source and target ends.

[0031] The auxiliary table T1 is created in the source database cluster. The auxiliary table T1 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 binlog log file of the master node) and the standby log of the standby node (i.e., the binlog log file of the standby node).

[0032] 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.

[0033] After parsing the main log of the master node to obtain the main transaction, the main 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.

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

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

[0036] After the main synchronization service is started, the main synchronization service at 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 will be recorded in the main log. In this embodiment, the main synchronization service parses the update operation on the auxiliary table T1 in the main log to obtain the main auxiliary transaction, and updates it in the main auxiliary table T2, so as to obtain the transaction progress of the main node through the main auxiliary transaction.

[0037] 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.

[0038] 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 through the standby auxiliary table T3.

[0039] 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.

[0040] The standby log is also used to record the main transaction that needs 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, but in order to keep the progress tracking of the standby node, it is still assumed in the record 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, but 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 transaction filtered by the standby node is in the same order as the main transaction executed by the main node.

[0041] 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. Therefore, 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.

[0042] In this embodiment, since the analysis starting points of the primary synchronization service and the backup synchronization service are likely to be different, the transaction progress of the primary node and the transaction progress of the backup node are mostly different. 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: In step 104, 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.

[0043] In this embodiment, since there are records of main and auxiliary transactions in the main auxiliary table T2 and records of standby auxiliary transactions in the standby auxiliary table T3, the main and auxiliary transactions last recorded when the main node is disabled and the standby auxiliary transactions last recorded can be compared to determine whether the transaction progress of the standby node is ahead or behind the transaction progress of the main node when the main node is disabled, and the transaction progress of the standby node is adjusted accordingly, so that the transaction progress of the standby node is consistent with the transaction progress of the main node, ensuring that the standby node can continue the transaction synchronization operation from the breakpoint when the main node is disabled.

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

[0045] 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 with 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.

[0046] It should be noted that, in the present embodiment, when the backup node is switched to the new primary node and transaction synchronization is started from the adjusted transaction progress, the original backup synchronization service is switched to the new primary 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 primary node; when the primary node is switched to the new backup node, the primary auxiliary table T2 corresponds to recording the transaction progress of the new backup node.

[0047] In this embodiment, the main synchronization service updates the auxiliary table T1 at a certain frequency, and the standby synchronization service parses the update operation to record the transaction progress of the primary node and the standby node. The primary node and the standby node do not need to adopt the GTID synchronization mode, and the primary node and the standby 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 standby node is obtained through the primary auxiliary table T2 and the standby auxiliary table T3, when the standby node needs to replace the primary node, the synchronization breakpoint of the primary node can be directly located.

[0048] In this embodiment, the master auxiliary table T2 is used to record the main transaction synchronized from the master node to the target end. At the same time, the corresponding master auxiliary transaction needs to be recorded 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: 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.

[0049] In this embodiment, the main transaction and the main auxiliary transaction are executed in a corresponding order, and the relevant information of the main transaction and the main auxiliary transaction includes a corresponding starting position and a transaction length. Each main transaction or main 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 according to the starting position and transaction length of the corresponding transaction.

[0050] 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. After receiving the XID commit operation of the transaction, the commit operation is performed, and the information of the main transaction is recorded in the master auxiliary table T2.

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

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

[0053] 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: The recording of the standby auxiliary transaction through the standby auxiliary table T3 specifically includes: recording the starting position, transaction length and update timestamp corresponding to the standby auxiliary transaction into the main auxiliary table T3.

[0054] In this embodiment, when the standby synchronization service is started, the initial analysis position is obtained from the standby auxiliary table T3, and the initial analysis position includes two parts of information: the current binlog file number (i.e., the starting position corresponding to the standby auxiliary transaction) and the offset in the file (i.e., the transaction length). Then, according to the analysis starting point, the 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 the update operation for the auxiliary table T1 is read as the standby auxiliary transaction, and the main transaction in the binlog log is filtered (i.e., discarded); when the update operation of the auxiliary table T1 is read, the update operation of the auxiliary table T1 is parsed as the standby auxiliary transaction, and the transaction information of the standby auxiliary transaction is sent to the target end, and the information of the update operation of 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 by the standby auxiliary table T3.

[0055] 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: In step 201, when the master node stops performing the master synchronization service, the last recorded master auxiliary transaction and the last recorded principal transaction are obtained according to the master auxiliary table T2, and the last recorded backup auxiliary transaction is obtained according to the backup auxiliary table T3.

[0056] 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 once according to the size relationship.

[0057] Therefore, in this embodiment, when tsA>tsB, it means that the standby node resolution is delayed, and the standby node needs to continue to be 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 of schedule, 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.

[0058] Correspondingly, such as Figure 3As shown, the method flow includes: In step 301, the transaction progress of the standby node is adjusted according to the size relationship.

[0059] 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.

[0060] 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.

[0061] 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.

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

[0063] Among them, the above-mentioned left table is the main log. Between the update operations of adjacent main and auxiliary transactions, there are also a corresponding number of main transaction executions. When the main node fails or needs to be replaced with the standby node, the synchronization breakpoint of the main node may be located in one of the main transactions. Therefore, it is not enough to only adjust the transaction progress of the standby node to the main auxiliary table T2 to obtain the last recorded main and auxiliary transaction. It is also necessary to make a corresponding secondary adjustment to adjust the transaction progress of the standby node to before the main transaction where the interruption occurs. Therefore, Figure 2 As shown, this embodiment also involves the following steps: 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.

[0064] In this embodiment, by judging the front-to-back 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 on 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 it is necessary to adjust the transaction progress of the backup node 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 there is no need to make a second adjustment, because the previous adjustment has adjusted the transaction progress of the backup node to the synchronization breakpoint.

[0065] Specifically, Figure 4As shown, the method flow is as follows: In step 401, a second adjustment is made to the transaction progress after the first adjustment according to the context relationship.

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

[0067] In step 403, when the starting position of the last recorded main transaction is earlier than the starting position of the last recorded main auxiliary transaction, 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.

[0068] Embodiment 2: In this embodiment, in order to more clearly demonstrate the method provided by this embodiment, the corresponding method flow is demonstrated with the following example.

[0069] Create an auxiliary table T1 in the source MySQL database cluster, which contains a timestamp column, as shown in the following example: create table T1(id int, current_ts timestamp(0)); Initially, a time record is inserted.

[0070] insert into t1 values(1, current_timestamp()); commit; On the target side, create the primary auxiliary table T2 and the backup auxiliary table T3, which are used to record the data synchronization breakpoints of the primary node and the backup node at the current source side, respectively, including the site ID, transaction ID, commit LSN, and the timestamp value of the auxiliary table T1. The structure examples of the primary auxiliary table T2 and the backup auxiliary table T3 are as follows:

[0071] The main binlog operation log and the secondary binlog operation log of the source MySQL database are as follows:

[0072] Among them, the corresponding process of the main synchronization service is as follows: Read and parse the main log and send it to the target end. After receiving the transaction operation, the target end caches and executes it according to the transaction ID. After receiving the transaction XID commit operation, the commit operation is performed and the transaction information of the currently committed main transaction is recorded in the main auxiliary table T2.

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

[0074] 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.

[0075] Take the following scenario as an example: 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 to synchronize data to the target end.

[0076] Assume that the primary synchronization service fails when analyzing transaction 5. At this time, the transaction progress of the primary node, that is, the primary auxiliary table T2, is as follows:

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

[0078] At this time, the transaction progress of the standby node, that is, the standby auxiliary table T3, has the following three situations: (1) The standby synchronization service only resolves to ts1, and the standby auxiliary table T3 is as follows:

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

[0080] Since ts2 is greater than ts1, it is determined that there is a parsing delay in 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.

[0081] By 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.

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

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

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

[0085] By 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.

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

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

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

[0089] 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.

[0090] By 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.

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

[0092] Another scenario is that when the primary node and the backup node of the MySQL database need to be switched, the backup synchronization service needs to take over the synchronization link and become the new primary synchronization service.

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

[0094] For this scenario, the location of the new starting analysis point for the standby node is the same as the above-mentioned case of the master node failure. The difference is that after the master node and the standby node switch, the original master synchronization service does not fail, but continues to perform subsequent log parsing and reading. At this time, when the original master node synchronization service switches to the new standby synchronization service, only the operation of the auxiliary table T1 is parsed, and other operations are filtered out, and the synchronization process of the standby synchronization service is switched. This process is consistent with the above-mentioned processing process of the master node failure, and will not be repeated here.

[0095] In summary, the above two situations are: in one situation, when the original master node fails, the standby 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 standby node are switched with each other, that is, the standby node is switched to the new master node, and transaction synchronization is performed, the original master node is switched to the new standby node, the main transaction is filtered, and only the update operation in the auxiliary table T1 is parsed.

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

[0097] The processor 41 and the memory 42 may be connected via a bus or other means. Figure 5 The example of connecting through bus is taken in the following.

[0098] The memory 42 is a non-volatile computer-readable storage medium that can be used to store non-volatile software programs and non-volatile computer executable programs, such as the real-time data synchronization method of the MySQL master-slave cluster in the above embodiment. The processor 41 executes the real-time data synchronization method of the MySQL master-slave cluster by running the non-volatile software programs and instructions stored in the memory 42.

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

[0100] 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 above described Figure 1-Figure 4The steps shown.

[0101] An embodiment of the present invention further provides a computer storage medium on which computer program instructions are stored; 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.

[0102] 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 protection scope of the present invention.

Claims

1. A real-time data synchronization method for a MySQL master-slave cluster, characterized in that: include: An auxiliary table T1 is created at the source end, and a main auxiliary table T2 and a backup auxiliary table T3 are created at the target end; wherein the source end updates the auxiliary table T1 at preset intervals; The main log of the master node on the source side is parsed to obtain the main transaction, the update operation for the auxiliary table T1 parsed from the main log is used as the 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 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 through 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 recording of the main auxiliary transaction and the main transaction submitted by the target end through the main auxiliary table T2 specifically includes: 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, 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: When the master node stops performing the master synchronization service, the last recorded master auxiliary transaction and the last recorded principal transaction are obtained according to the master auxiliary table T2, and the last recorded backup auxiliary transaction is obtained according to the backup auxiliary table T3; Obtaining 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, and adjusting the transaction progress of the backup node once according to the size 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 step of adjusting the transaction progress of the standby node once 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 context relationship specifically includes: When the starting position of the last recorded primary and secondary transaction 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 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: The step of 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: The step of 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: It includes 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 of 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 as described in any one of claims 1 to 8.

Images