MySQL data detection and synchronization method and electronic equipment

Abstract

The invention discloses a MySQL data detection and synchronization method and electronic equipment, and belongs to the technical field of software development. The method comprises the following steps: starting a global transaction function of MySQL nodes, wherein a transaction corresponding to each MySQL node contains a unique identifier of each transaction; the monitoring period of the MySQL nodes is determined, and the MySQL nodes are monitored in the monitoring period; automatically determining a target monitoring period, and obtaining an update transaction corresponding to the MySQL node in the target monitoring period; determining a master-slave relationship of the MySQL node based on the information related to the update transaction; according to the master-slave relationship, detecting the data synchronization integrity between the MySQL nodes; and executing a data synchronization integrity repairing step. According to the method and the device, the integrity and the data consistency of data synchronization between the MySQL master and slave nodes are ensured, the synchronization efficiency is improved, and the synchronization difficulty is reduced.

Description

Technical Field

[0001] This application belongs to the field of software development technology, and specifically relates to MySQL data detection and synchronization methods and electronic devices. Background Technology

[0002] With the rapid development of information and network technologies, the requirements for high availability, data integrity, and stability of software are becoming increasingly stringent. As business and technology evolve, multi-datacenter / distributed active-active deployments have become an essential deployment model for most software service providers. Data transmission across data centers is subject to various unstable factors, such as network and data center failures. As a widely used open-source relational database, MySQL's asynchronous synchronization mode is widely adopted. However, during cross-datacenter data synchronization, these unstable factors increase the risk of data loss during asynchronous synchronization. Resynchronizing lost data is difficult and inefficient.

[0003] To address the aforementioned issues, this application proposes a MySQL data detection and synchronization method and an electronic device. Summary of the Invention

[0004] To address the shortcomings of the prior art, this application provides a MySQL data detection and synchronization method and electronic device, which solves the problems of high difficulty and low efficiency in resynchronizing lost data after data synchronization between MySQL nodes is lost.

[0005] The technical effect to be achieved in this application is accomplished through the following solution:

[0006] Firstly, this application provides a MySQL data detection and synchronization method, the method comprising:

[0007] Enable global transaction functionality for MySQL nodes, where each transaction for each MySQL node contains a unique identifier.

[0008] Determine the monitoring period for the MySQL node, and monitor the MySQL node within the monitoring period;

[0009] Automatically determine the target monitoring period and obtain the update transactions corresponding to the MySQL nodes within the target monitoring period;

[0010] Based on information related to update transactions, determine the master-slave relationship of MySQL nodes;

[0011] Based on the master-slave relationship, check the data synchronization integrity between MySQL nodes;

[0012] Perform data synchronization integrity repair steps.

[0013] In some embodiments, the MySQL node includes a master node and a slave node.

[0014] In some embodiments, determining the master-slave relationship of MySQL nodes based on information related to update transactions includes: parsing the master node corresponding to the transaction according to the format of the transaction records of MySQL nodes within the target monitoring period, and determining the master-slave relationship of MySQL nodes, wherein the master-slave relationship represents the correspondence between the master node and the slave node.

[0015] In some embodiments, detecting the data synchronization integrity between MySQL nodes based on the master-slave relationship includes:

[0016] The unique identifiers of transactions in the master node's log file and the slave node's log file are obtained respectively within the monitoring period. The master node's log file contains the unique identifiers of all transactions, and the slave node's log file contains the unique identifiers of all synchronized transactions.

[0017] Determine whether transaction loss occurred during the synchronization process. If transaction loss is confirmed, it indicates that data synchronization is incomplete.

[0018] In some embodiments, the data synchronization integrity repair step includes:

[0019] Obtain the unique identifier of the transaction lost during the synchronization process;

[0020] The corresponding update statement is obtained by using the unique identifier of the lost transaction;

[0021] Retrieve the corresponding update data from the master node according to the update statement;

[0022] Update the slave node with the updated data.

[0023] In some embodiments, the operation corresponding to the update statement includes: adding, deleting, and modifying.

[0024] In some embodiments, the determination of whether transaction loss has occurred during the synchronization process is accomplished using operating system comparison commands.

[0025] In some embodiments, before obtaining the unique identifier of a transaction lost during the synchronization process, the method further includes: excluding transactions in the master node that do not need to be synchronized.

[0026] Secondly, this application provides an electronic device, the electronic device comprising: a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the method described in any of the foregoing descriptions.

[0027] Thirdly, this application provides a computer-readable storage medium storing one or more programs that can be executed by one or more processors to implement the method described in any of the foregoing claims.

[0028] The MySQL data detection and synchronization method and electronic device provided in this application automatically detects data loss within the target monitoring period by setting a monitoring period and automatically resolves the master node. Thus, it realizes the detection and synchronization of lost transactions based on the unique identifier of the transaction, reducing the synchronization difficulty and improving the synchronization efficiency of lost data. Attached Figure Description

[0029] To more clearly illustrate the embodiments of this application or the existing technical solutions, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments recorded in this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0030] Figure 1 This is a flowchart of a MySQL data detection and synchronization method according to an embodiment of this application;

[0031] Figure 2 This is a schematic block diagram of an electronic device according to an embodiment of this application. Detailed Implementation

[0032] To make the objectives, technical solutions, and advantages of this application clearer, the technical solutions of this application will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only a part of the embodiments of this application, and not all of them. Based on the embodiments in this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0033] It should be noted that, unless otherwise defined, the technical or scientific terms used in one or more embodiments of this application should have the ordinary meaning understood by one of ordinary skill in the art to which this application pertains. The terms "first," "second," and similar terms used in one or more embodiments of this application do not indicate any order, quantity, or importance, but are merely used to distinguish different components. Terms such as "comprising" or "including" mean that the element or object preceding the word encompasses the elements or objects listed following the word and their equivalents, without excluding other elements or objects. Terms such as "connected" or "linked" are not limited to physical or mechanical connections, but can include electrical connections, whether direct or indirect. Terms such as "upper," "lower," "left," and "right" are used only to indicate relative positional relationships; when the absolute position of the described object changes, the relative positional relationship may also change accordingly.

[0034] The purpose of this application is to use a script to detect inconsistencies between MySQL master and slave nodes caused by unstable data synchronization due to network fluctuations or other reasons, resulting in some update operations not being synchronized to the slave nodes. The script parses MySQL update records to identify the master-slave relationship between the monitored MySQL nodes and retrieves transactions lost by the slave nodes during master-slave synchronization. Finally, it reads the data corresponding to the lost transactions from the master node and synchronizes it to the corresponding slave nodes, thereby achieving data consistency between the MySQL master and slave nodes.

[0035] The various non-limiting embodiments of this application will now be described in detail with reference to the accompanying drawings.

[0036] First, refer to Figure 1 This application provides a detailed description of the MySQL data detection and synchronization method.

[0037] This application provides a MySQL data detection and synchronization method, the method comprising:

[0038] S1: Enable global transaction functionality for MySQL nodes. Each transaction for each MySQL node contains a unique identifier for each transaction.

[0039] S2: Determine the monitoring period for the MySQL node, and monitor the MySQL node within the monitoring period;

[0040] S3: Automatically determine the target monitoring period and obtain the update transactions corresponding to the MySQL nodes within the target monitoring period;

[0041] S4: Determine the master-slave relationship of MySQL nodes based on information related to update transactions;

[0042] S5: Based on the master-slave relationship, check the data synchronization integrity between MySQL nodes;

[0043] S6: Perform data synchronization integrity repair steps.

[0044] The MySQL data detection and synchronization method and electronic device provided in this application automatically detects data loss within a target monitoring period by setting a monitoring cycle and automatically resolves the master node. It then uses the unique identifier of each transaction to detect and synchronize lost transactions, reducing the difficulty of synchronization and improving the efficiency of synchronizing lost data.

[0045] In some embodiments, the MySQL node includes a master node and slave nodes; specifically, the MySQL node may include two or more nodes.

[0046] In some embodiments, determining the master-slave relationship of MySQL nodes based on information related to update transactions includes: parsing the master node corresponding to the transaction according to the format of the transaction records of MySQL nodes within the target monitoring period, and determining the master-slave relationship of MySQL nodes, wherein the master-slave relationship represents the correspondence between the master node and the slave node.

[0047] For example, if a MySQL node includes two nodes, it is necessary to analyze the master-slave relationship for the corresponding time period based on the actual transaction records, that is, which node is the master node and which node is the slave node at the corresponding time. Because the master and slave nodes may change in real time during actual operation, this application fully considers the factors of master and slave node changes, so as to quickly and accurately determine the lost transactions and improve the efficiency of synchronization.

[0048] In some embodiments, detecting the data synchronization integrity between MySQL nodes based on the master-slave relationship includes:

[0049] The unique identifiers of transactions in the master node's log file and the slave node's log file are obtained respectively within the monitoring period. The master node's log file contains the unique identifiers of all transactions, and the slave node's log file contains the unique identifiers of all synchronized transactions.

[0050] Determine whether transaction loss occurred during the synchronization process. If transaction loss is confirmed, it indicates that the data synchronization is incomplete and the lost transaction data needs to be resynchronized.

[0051] In some embodiments, the data synchronization integrity repair step includes:

[0052] Obtain the unique identifier of the transaction lost during the synchronization process;

[0053] The corresponding update statement is obtained by using the unique identifier of the lost transaction;

[0054] Retrieve the corresponding update data from the master node according to the update statement;

[0055] Update the slave node with the updated data.

[0056] In some embodiments, the operation corresponding to the update statement includes: adding, deleting, and modifying; this is illustrative, and other operations well known to those skilled in the art can also be applied here, without limitation.

[0057] In some embodiments, the determination of whether transaction loss has occurred during the synchronization process is accomplished using operating system comparison commands, which eliminates the need for line-by-line comparison and improves efficiency.

[0058] In some embodiments, before obtaining the unique identifier of a transaction lost during the synchronization process, the method further includes: excluding transactions in the master node that do not need to be synchronized.

[0059] This application proposes a method to periodically check whether the data synchronization between MySQL master and slave nodes is complete by executing a script at regular intervals. When it is detected that some update operations of the master nodes have not been synchronized to the slave nodes, the latest data is automatically obtained from the master nodes and updated to the slave nodes that have lost synchronization, thereby achieving data integrity, improving synchronization efficiency and reducing synchronization difficulty.

[0060] The detection and synchronization methods of this application are illustrated below through examples:

[0061] 1. Detection data synchronization integrity process

[0062] 1) Obtain update operation records of MySQL nodes within the monitoring period: Use commands from MySQL and its built-in toolkit to obtain update transactions of MySQL nodes within the monitoring start and end time range. Enable global transaction functionality for MySQL nodes; each transaction corresponding to a MySQL node contains a unique identifier, and this unique identifier uniquely corresponds to the MySQL node.

[0063] 2) Parsing the Master-Slave Relationship Between MySQL Nodes: Based on the format of MySQL transaction records, the master node corresponding to the transaction can be identified, allowing analysis of the master-slave relationship between MySQL nodes. Furthermore, considering that master-slave nodes can achieve fault recovery in a wired network environment using tools like KeepAlived, there is a possibility of master node switching. During the parsing process, a master-slave switch could occur; therefore, in addition to parsing update transaction records, it is necessary to dynamically parse the master-slave relationship between MySQL nodes using the MySQL node status and operation records obtained through this method.

[0064] 3) Detecting Data Synchronization Integrity: Extracting unique identifiers of transactions from the MySQL node record files within the monitoring period. The master node contains unique identifiers for all transactions, and the slave nodes contain unique identifiers for all synchronized transactions. If transactions are lost during synchronization, the unique identifiers of one or more transactions in the master node will not appear in the synchronization update records of the slave nodes. Therefore, by comparing the records between the master and slave nodes (considering factors such as network latency, the comparison of slave nodes considers files within the next period; to ensure the efficiency of file comparison, operating system comparison commands are used directly, not line-by-line comparison), the lost transactions in the slave nodes during synchronization are obtained. When lost transactions are detected during synchronization, it indicates that data synchronization may be incomplete. After excluding transactions in the master node that do not need to be synchronized, the unique identifiers of transactions lost during data synchronization between the master and slave nodes within the monitoring period can be completely obtained.

[0065] 2. Data Synchronization Integrity Repair Process

[0066] When a data synchronization integrity issue is detected, the unique identifier of the lost transaction during the synchronization process is also obtained. This unique identifier allows us to locate the complete information of the transaction in the record file and further parse out other information within the transaction, such as update statements and transaction operation times. Finally, after transforming the update statements, the data corresponding to the transaction is retrieved from the master node, and the data is updated to the slave nodes. The specific steps are as follows:

[0067] 1) Obtaining Update Statements: The transaction's unique identifier allows you to locate its position in the log file. By analyzing the log format, you can obtain the start and end identifiers for the transaction. For example, the two lines before the unique identifier can be used to indicate the start of the transaction. The transaction commit command after the unique identifier can be used to indicate the end of the transaction. The content between the start and end identifiers contains all the necessary key information. Further, by locating the transaction start and end commands, you can obtain the complete update statements.

[0068] 2) Querying updated data from the master node based on update statements: Update statements include operations such as insert, delete, and modify. The conditions for querying data for each operation can be analyzed from the transaction information, such as the database name, table name, and the unique key inserted. This information can be used to further obtain complete table information, such as primary keys and unique indexes. Combining the data from the update operation, a query statement corresponding to this data can be generated (e.g., the primary key / unique index / index involved in the update statement is converted into query conditions). The query statement is then executed on the master node to retrieve the required data.

[0069] 3) Update the data to the slave node: During the detection period, data may have been deleted. Therefore, if the data obtained from the master node is empty, the deletion operation for this data needs to be performed on the slave node. If the data is obtained from the master node, it needs to be converted into an insert / update statement (in conjunction with the primary key or unique key), and the insert / update operation is performed on the slave node (insert if there is no data, update if there is data). This ensures consistency between the master and slave nodes on this data.

[0070] The MySQL data synchronization integrity and correction scheme described in this application makes full use of the commands of mysql and its auxiliary packages to monitor the integrity of data synchronization between two or more MySQL master and slave nodes, and ultimately ensures the integrity and consistency of data synchronization between MySQL master and slave nodes.

[0071] In practical project applications, the MySQL data synchronization integrity scheme described in this application can efficiently achieve final data consistency between MySQL master and slave nodes without affecting the MySQL master and slave nodes and ensuring its own performance.

[0072] It should be noted that the methods of one or more embodiments of this application can be executed by a single device, such as a computer or server. The methods of this embodiment can also be applied in a distributed scenario, where multiple devices cooperate to complete the process. In such a distributed scenario, one of these devices may execute only one or more steps of the methods of one or more embodiments of this application, and the multiple devices will interact with each other to complete the method described.

[0073] It should be noted that the above description describes specific embodiments of this application. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps described in the claims may be performed in a different order than that shown in the embodiments and still achieve the desired results. Furthermore, the processes depicted in the drawings do not necessarily require the specific or sequential order shown to achieve the desired results. In some embodiments, multitasking and parallel processing are also possible or may be advantageous.

[0074] Based on the same inventive concept, and corresponding to the methods of any of the above embodiments, this application also discloses an electronic device;

[0075] Specifically, Figure 2This diagram illustrates the hardware structure of an electronic device for a MySQL data detection and synchronization method provided in this embodiment. The device may include a processor 410, a memory 420, an input / output interface 430, a communication interface 440, and a bus 450. The processor 410, memory 420, input / output interface 430, and communication interface 440 are interconnected internally via the bus 450.

[0076] The processor 410 can be implemented using a general-purpose CPU (Central Processing Unit), microprocessor, application-specific integrated circuit (ASIC), or one or more integrated circuits, and is used to execute relevant programs to implement the technical solutions provided in the embodiments of this application.

[0077] The memory 420 can be implemented in the form of ROM (Read Only Memory), RAM (Random Access Memory), static storage device, dynamic storage device, etc. The memory 420 can store the operating system and other applications. When the technical solutions provided in the embodiments of this application are implemented by software or firmware, the relevant program code is stored in the memory 420 and is called and executed by the processor 410.

[0078] Input / output interface 430 is used to connect input / output modules to realize information input and output. Input / output modules can be configured as components in the device (not shown in the figure) or externally connected to the device to provide corresponding functions. Input devices may include keyboards, mice, touch screens, microphones, various sensors, etc., and output devices may include displays, speakers, vibrators, indicator lights, etc.

[0079] The communication interface 440 is used to connect a communication module (not shown in the figure) to enable communication between this device and other devices. The communication module can communicate via wired means (e.g., USB, Ethernet cable, etc.) or wireless means (e.g., mobile network, Wi-Fi, Bluetooth, etc.).

[0080] Bus 450 includes a pathway for transmitting information between various components of the device (e.g., processor 410, memory 420, input / output interface 430, and communication interface 440).

[0081] It should be noted that although the above-described device only shows the processor 410, memory 420, input / output interface 430, communication interface 440, and bus 450, in specific implementations, the device may also include other components necessary for normal operation. Furthermore, those skilled in the art will understand that the above-described device may only include the components necessary for implementing the embodiments of this application, and not necessarily all the components shown in the figures.

[0082] The electronic devices described above are used to implement the corresponding MySQL data detection and synchronization methods in any of the foregoing embodiments, and have the beneficial effects of the corresponding method embodiments, which will not be repeated here.

[0083] Based on the same inventive concept, corresponding to the methods of any of the above embodiments, one or more embodiments of this application also provide a non-transitory computer-readable storage medium storing computer instructions for causing the computer to execute the MySQL data detection and synchronization method as described in any of the above embodiments.

[0084] The computer-readable medium of this embodiment includes permanent and non-permanent, removable and non-removable media, and information storage can be implemented by any method or technology. Information can be computer-readable instructions, data structures, program modules, or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other non-transfer medium that can be used to store information accessible by a computing device.

[0085] The computer instructions stored in the storage medium of the above embodiments are used to cause the computer to execute the MySQL data detection and synchronization method as described in any of the above embodiments, and have the beneficial effects of the corresponding method embodiments, which will not be repeated here.

[0086] Those skilled in the art should understand that the discussion of any of the above embodiments is merely exemplary and is not intended to imply that the scope of this application (including the claims) is limited to these examples; within the framework of this application, the technical features of the above embodiments or different embodiments can also be combined, the steps can be implemented in any order, and there are many other variations of different aspects of one or more embodiments of this application as described above, which are not provided in detail for the sake of brevity.

[0087] Additionally, to simplify the description and discussion, and to avoid obscuring one or more embodiments of this application, the well-known power / ground connections to integrated circuit (IC) chips and other components may or may not be shown in the provided drawings. Furthermore, the apparatus may be shown in block diagram form to avoid obscuring one or more embodiments of this application, and this also takes into account the fact that the details of implementation of these block diagram apparatuses are highly dependent on the platform on which one or more embodiments of this application will be implemented (i.e., these details should be fully understood by those skilled in the art). While specific details (e.g., circuits) are set forth to describe exemplary embodiments of this application, it will be apparent to those skilled in the art that one or more embodiments of this application may be implemented without these specific details or with variations thereof. Therefore, these descriptions should be considered illustrative rather than restrictive.

[0088] Although this application has been described in conjunction with specific embodiments thereof, many substitutions, modifications, and variations of these embodiments will be apparent to those skilled in the art from the foregoing description. For example, other memory architectures (e.g., dynamic RAM (DRAM)) may be used with the embodiments discussed.

[0089] One or more embodiments of this application are intended to cover all such substitutions, modifications, and variations that fall within the broad scope of the appended claims. Therefore, any omissions, modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of one or more embodiments of this application should be included within the protection scope of this application.

Claims

1. A method for MySQL data detection and synchronization, characterized in that, The method includes: Enable global transaction functionality for MySQL nodes, where each transaction for each MySQL node contains a unique identifier. Determine the monitoring period for the MySQL node, and monitor the MySQL node within the monitoring period; Automatically determine the target monitoring period and obtain the update transactions corresponding to the MySQL nodes within the target monitoring period; Based on information related to update transactions, determine the master-slave relationship of MySQL nodes; Based on the master-slave relationship, check the data synchronization integrity between MySQL nodes; Perform data synchronization integrity repair steps.

2. The MySQL data detection and synchronization method according to claim 1, characterized in that, The MySQL nodes include master nodes and slave nodes.

3. The MySQL data detection and synchronization method according to claim 1 or 2, characterized in that, Determining the master-slave relationship of MySQL nodes based on the relevant information of the update transaction includes: parsing the master node corresponding to the transaction according to the format of the transaction record of the MySQL node within the target monitoring period, and determining the master-slave relationship of the MySQL nodes, wherein the master-slave relationship represents the correspondence between the master node and the slave node.

4. The MySQL data detection and synchronization method according to claim 3, characterized in that, Based on the master-slave relationship, the integrity of data synchronization between MySQL nodes is checked, including: The unique identifiers of transactions in the master node's log file and the slave node's log file are obtained respectively within the monitoring period. The master node's log file contains the unique identifiers of all transactions, and the slave node's log file contains the unique identifiers of all synchronized transactions. Determine whether transaction loss occurred during the synchronization process. If transaction loss is confirmed, it indicates that data synchronization is incomplete.

5. The MySQL data detection and synchronization method according to claim 4, characterized in that, The data synchronization integrity repair step includes: Obtain the unique identifier of the transaction lost during the synchronization process; The corresponding update statement is obtained by using the unique identifier of the lost transaction; Retrieve the corresponding update data from the master node according to the update statement; Update the slave node with the updated data.

6. The MySQL data detection and synchronization method according to claim 5, characterized in that, The operations corresponding to the update statement include: adding, deleting, and modifying.

7. The MySQL data detection and synchronization method according to claim 4, characterized in that, The determination of whether transaction loss has occurred during the synchronization process is accomplished using operating system comparison commands.

8. The MySQL data detection and synchronization method according to claim 5, characterized in that, Before obtaining the unique identifier of a transaction lost during the synchronization process, the process also includes: excluding transactions in the master node that do not need to be synchronized.

9. An electronic device, the electronic device comprising: A memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that the processor, when executing the computer program, implements the method as described in any one of claims 1 to 8.

10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores one or more programs that can be executed by one or more processors to implement the method as described in any one of claims 1 to 8.

Images