Method and device for recovering MySQL multi-master cluster

Abstract

The invention discloses a MySQL multi-master cluster recovery method and device. The method comprises the following steps: installing a qing-sys service; automatically executing closing of the database container running uniquely on each node; automatically electing a main node in each node; automatically modifying a database data state file of the main node; starting the database container corresponding to the main node; starting the database containers of the nodes except the main node in the cluster in sequence; and restarting the database container of the main node. By means of the method and device, automatic main node election and recovery are achieved, and it is ensured that the cluster recovery process after power failure is efficient and accurate; data consistency is guaranteed, and the problem of data loss or inconsistency among nodes is avoided; gradually recovering and reducing the risk; quickly expanding to a multi-node cluster environment; the recovery time is shortened.

Description

Technical Field

[0001] The present invention belongs to the technical field of databases, and in particular relates to a method and device for restoring a MySQL multi-master cluster. Background Art

[0002] MySQL is an open source relational database management system that is widely used in a variety of database application scenarios, especially distributed systems and database applications that have high requirements for database consistency and persistence. In order to improve the reliability and availability of the system, MySQL supports the deployment mode of multi-master cluster. In the multi-master cluster mode, multiple nodes can perform data read and write operations at the same time, thus providing high availability and high fault tolerance.

[0003] The advantage of a multi-master cluster is that if one node fails, other nodes can still continue to provide services, thus ensuring the normal operation of the system. However, in actual application scenarios, if all master nodes in the entire cluster lose power or stop running at the same time due to unexpected reasons (such as power failure or hardware damage), the database will face serious service interruption problems. After power is restored or the system is restarted, how to ensure the consistency of data in each node of the cluster and avoid data loss or damage has become a key technical problem.

[0004] Defects and shortcomings of the existing technology:

[0005] 1. Data inconsistency problem: In a multi-master cluster, if a power outage occurs when multiple nodes are writing data at the same time, the write status of each node may be out of sync, resulting in data inconsistency.

[0006] 2. Risk of data loss: Current technologies rely on transaction logs (such as binlog) of each node and data in memory cache when processing power outage recovery. However, if the system suddenly loses power and the data in memory has not been persisted to the disk in time, some transaction data may be lost. Even if the power is restored, uncommitted transactions or partially committed transactions in the database may be in an incomplete state, and there is a risk of loss.

[0007] 3. High risk of manual recovery: The current recovery process of MySQL multi-master clusters often requires manual intervention by administrators to ensure that all nodes are in the same state. This manual recovery method not only increases the workload of administrators, but also easily leads to operational errors, which further induces the risk of data loss or cluster crash.

[0008] 4. Long recovery time: After power outages on multiple nodes, it usually takes a long time to resynchronize the data of each node. Especially when the cluster is large, the recovery process is very time-consuming, which directly affects business continuity. Summary of the invention

[0009] The purpose of the present invention is to overcome the above problems and provide a method and device for MySQL multi-master cluster recovery.

[0010] In order to achieve the above purpose, the technical solution adopted by the present invention is:

[0011] A method for restoring a MySQL multi-master cluster includes the following steps:

[0012] Install the qing-sys service on each node of the MySQL multi-master cluster;

[0013] After multiple nodes in the cluster confirm startup and perform network preparation, the qing-sys service automatically shuts down the only database container running on each node;

[0014] Based on the integrated Raft protocol, the qing-sys service automatically elects a master node in each node;

[0015] The qing-sys service automatically modifies the database data status file of the master node; starts the database container corresponding to the master node;

[0016] The qing-sys service starts the database containers of the nodes in the cluster except the master node in sequence;

[0017] Close the database container of the master node, and restart the database container of the master node.

[0018] As a further improvement, the modification of the database data status file of the master node comprises the following steps:

[0019] Change the data state from not allowing booting the cluster to booting the node, allowing it to start as the master node of the cluster.

[0020] As a further improvement, starting the database container corresponding to the master node includes the following steps:

[0021] The qing-sys service modifies the startup parameters of the master node database container and adds an environment variable of -e MySQL_GALERA_CLUSTER_BOOTSTRAP=yes.

[0022] As a further improvement, the qing-sys service starts the database containers of the nodes in the cluster except the master node in sequence, and the data of the database container of each node is synchronized with the data of the database container of the master node.

[0023] As a further improvement, in the database container of the master node that is restarted, the environment variable -eMySQL_GALERA_CLUSTER_BOOTSTRAP=yes is stopped from being used.

[0024] As a further improvement, the multiple nodes in the cluster confirm the startup and perform network preparation, including the following steps:

[0025] Confirm that the operating system of each node has been started and the network status is normal;

[0026] The network heartbeat detection mechanism is used to confirm whether other nodes are started and can communicate with each other.

[0027] As a further improvement, the qing-sys service automatically shuts down the only database container running on each node, including the following steps:

[0028] Use the Docker command to stop the Galera database container running on each node one by one.

[0029] As a further improvement, based on the integrated Raft protocol, the qing-sys service automatically elects a master node in each node, including the following steps:

[0030] The integrated Raft protocol runs between the qing-sys services of each node, and the master node is elected through message communication.

[0031] As a further improvement, the qing-sys service starts the database containers of the nodes in the cluster except the master node in sequence, and synchronizes the data with the data container of the master node through the Galera protocol.

[0032] On the other hand, the present invention also provides a device for recovering a MySQL multi-master cluster, the device comprising at least one processor and a memory storing instructions, and when the instructions are executed by at least one processor, the method in the above technical solution is implemented.

[0033] A computer-readable storage medium stores a computer program, and the computer program is executed by a processor to implement the method described in the steps of the above technical solution.

[0034] A computer program product, comprising a computer program, wherein when the computer program is executed by a processor, the method described in the steps of the above technical solution is implemented.

[0035] The beneficial effects of the present invention are:

[0036] Automated master node election and recovery: Through the Raft election algorithm and automated scripts, there is no need for administrators to intervene manually, ensuring that the cluster recovery process after power outages is efficient and accurate;

[0037] Data consistency guarantee: By shutting down the database containers of all nodes and having the elected master node perform the boot operation, the data consistency and integrity of each node is ensured to avoid data loss or inconsistency between nodes.

[0038] Gradual recovery reduces risk: relying on the master node to bootstrap and the gradual startup of other nodes, avoiding race conditions or data conflicts caused by starting multiple nodes at the same time;

[0039] Scalability: It is suitable for large-scale MySQL multi-master clusters, can be quickly expanded to multi-node cluster environments, and can effectively ensure high availability of the cluster in the event of sudden power outages;

[0040] Reduced recovery time: After multiple nodes lose power, the cluster can be automatically restored without human intervention. BRIEF DESCRIPTION OF THE DRAWINGS

[0041] The present invention has the following accompanying drawings:

[0042] Figure 1 are flow charts of some specific embodiments of the present invention;

[0043] Figure 2 A schematic diagram of a device for restoring a MySQL multi-master cluster according to the present invention. DETAILED DESCRIPTION

[0044] The following examples further illustrate the content of the present invention, but should not be construed as limiting the present invention. Without departing from the spirit and essence of the present invention, modifications or substitutions made to the methods, steps or conditions of the present invention all fall within the scope of the present invention.

[0045] like Figure 1 As shown, the present invention provides a method for restoring a MySQL multi-master cluster, comprising the following steps:

[0046] Step S10: Install the qing-sys service on each node of the MySQL multi-master cluster;

[0047] Step S11: multiple nodes in the cluster confirm startup and perform network preparation, and the qing-sys service automatically closes the only database container running on each node;

[0048] Step S12: Based on the integrated Raft protocol, the qing-sys service automatically elects a master node in each node;

[0049] Step S13: the qing-sys service automatically modifies the database data status file of the master node;

[0050] Step S14: Start the database container corresponding to the master node;

[0051] Step S15: the qing-sys service starts the database containers of the nodes in the cluster except the master node in sequence;

[0052] Step S16: close the database container of the master node, and restart the database container of the master node.

[0053] Modifying the database data status file of the master node includes the following steps:

[0054] Change the data state from not allowing booting the cluster to booting the node, allowing it to start as the master node of the cluster.

[0055] Starting the database container corresponding to the master node includes the following steps:

[0056] The qing-sys service modifies the startup parameters of the master node database container and adds an environment variable of -e MySQL_GALERA_CLUSTER_BOOTSTRAP=yes.

[0057] In some embodiments of the present invention, the qing-sys service starts the database containers of the nodes in the cluster except the master node in sequence, and the data of the database container of each node is synchronized with the data of the database container of the master node.

[0058] In some embodiments of the present invention, in the database container of the master node that is restarted, the environment variable -e MySQL_GALERA_CLUSTER_BOOTSTRAP=yes is stopped from being used.

[0059] Confirm startup and network preparation on multiple nodes in the cluster, including the following steps:

[0060] Confirm that the operating system of each node has been started and the network status is normal;

[0061] The network heartbeat detection mechanism is used to confirm whether other nodes are started and can communicate with each other.

[0062] The qing-sys service automatically shuts down the only database container running on each node, including the following steps:

[0063] Use the Docker command to stop the Galera database container running on each node one by one.

[0064] Based on the integrated Raft protocol, the qing-sys service automatically elects a master node in each node, including the following steps:

[0065] The integrated Raft protocol runs between the qing-sys services of each node, and the master node is elected through message communication.

[0066] In some embodiments of the present invention, the qing-sys service starts the database containers of the nodes in the cluster except the master node in sequence, and synchronizes the data with the data container of the master node through the Galera protocol.

[0067] The present invention realizes automatic coordination and master node election after power failure recovery by installing a specific service (qing-sys service) on each cluster node, ensures the consistency of cluster data, and avoids human intervention and data loss problems.

[0068] The specific steps are as follows:

[0069] 1. Node environment preparation: Install the qing-sys service on each node of the MySQL multi-master cluster. The qing-sys service is responsible for ensuring the startup status and network connection status of each node after the node is powered off and restored, and ensuring that all nodes are ready and can communicate with each other.

[0070] 2. Shut down the database container: After multiple nodes in the cluster are confirmed to be started and the network is ready, the qing-sys service automatically shuts down the Galera database container running on each node. This ensures that the database container will not start automatically during the subsequent recovery process, thus avoiding confusion when the data is inconsistent or the network is not fully prepared.

[0071] 3. Raft election of master node: By integrating the Raft protocol, the qing-sys service automatically elects the master node in each node. Raft is a distributed consensus algorithm that ensures that one of the multiple nodes is selected as the leader (master node) to lead the cluster recovery process.

[0072] 4. Modify the master node configuration file: On the elected master node, the qing-sys service will automatically modify the data status file of the master node database, that is, the safe_to_bootstrap field in the / var / lib / docker / volumes / galera / _data / data / grastate.dat file. The initial value of this field is 0, indicating that booting the cluster is not allowed. In this step, the qing-sys service changes the value to 1, indicating that this node is selected as the boot node and is allowed to start as the master node of the cluster.

[0073] 5. Start the master node database container: The qing-sys service further modifies the startup parameters of the master node database container and adds

[0074] -e MySQL_GALERA_CLUSTER_BOOTSTRAP=yes environment variable. This variable instructs the master node to perform cluster bootstrap operations when it starts. After that, the qing-sys service will start the Galera database container of the master node, and the master node database will start running and become the boot node of the cluster.

[0075] 6. Start other node database containers step by step: When the Galera database container of the master node is confirmed to be running normally, the qing-sys service will start the Galera database containers of other nodes in the cluster in sequence. The database containers of each node will be synchronized with the master node to ensure data consistency and correct cluster connection.

[0076] 7. Restart the master node database container: When the database containers of all other nodes are running normally and synchronized with the master node, the qing-sys service will shut down the Galera database container of the master node and restart it. When restarting, the -eMySQL_GALERA_CLUSTER_BOOTSTRAP=yes environment variable is no longer used to ensure that the master node returns to the normal node state and maintain the consistency and normal operation of the cluster.

[0077] 8. Cluster recovery is complete: When all nodes are running normally, the cluster enters a stable state, and all data and services are restored to normal, the recovery process of the MySQL multi-master cluster after a power outage is completed. This method ensures reliable recovery of the cluster, avoids data loss and data inconsistency, and reduces the process of manual intervention.

[0078] The present invention mainly performs automatic election of master nodes and control of database containers through the qing-sys service, ensuring rapid recovery of the cluster after power failure.

[0079] The following are the detailed implementation steps of the present invention:

[0080] 1. Environment initialization

[0081] Pre-deploy the qing-sys service on each node of the cluster. As a system-level daemon, the qing-sys service is responsible for monitoring the node status, network connectivity, and performing automated cluster recovery operations.

[0082] The cluster consists of multiple MySQL database nodes, each of which runs a Galera database container.

[0083] The node uses Docker container technology to manage database instances, and the database data is stored in the / var / lib / docker / volumes / galera / _data / directory.

[0084] The qing-sys service is installed on each node, can monitor the status of other nodes in the cluster, and has the ability to execute commands.

[0085] 2. Confirmation of multi-node startup status

[0086] When power is restored, the qing-sys service is first started on each node and performs the following steps:

[0087] Confirm that the operating system of each node has been started and the network status is normal.

[0088] Use a network heartbeat detection mechanism (such as ping or RPC communication within the cluster) to confirm that other nodes have also started and can communicate with each other.

[0089] When all nodes in the cluster are in a communicative state, the qing-sys service will continue to perform recovery operations.

[0090] 3. Close all Galera database containers

[0091] After all nodes are confirmed to be ready for network connection, the qing-sys service automatically shuts down the Galera database container running on each node. The purpose of shutting down the container is to ensure that during the restart process after a power outage, each node will not immediately start the database on its own to avoid data inconsistency.

[0092] Through Docker commands, such as docker stop <galera-container-id>, stop the Galera database container running on each node one by one.

[0093] 4. Raft Masternode Election

[0094] After each node is started, the qing-sys service uses the Raft protocol to elect a master node in the cluster. The Raft algorithm ensures that a master node is selected from multiple nodes through a voting mechanism to guide cluster recovery.

[0095] The Raft protocol runs between the qing-sys services of each node and elects a leader node (i.e., the master node) through message communication.

[0096] After the master node election is completed, the master node will assume the leading role of cluster data recovery.

[0097] 5. Modify the master node configuration file

[0098] After the master node is elected, the qing-sys service automatically modifies the Galera database configuration file of the master node to allow the node to bootstrap the cluster.

[0099] Specific operations include:

[0100] Modify the master node file

[0101] / var / lib / docker / volumes / galera / _data / data / grastate.dat, change the safe_to_bootstrap field in the file from 0 to 1. This change indicates that the node will start the cluster as a boot node.

[0102] Example modification command: sed -i 's / safe_to_bootstrap:0 / safe_to_bootstrap:1 / ' / var / lib / docker / volumes / galera / _data / data / grastate.dat

[0103] 6. Start the Galera database container on the master node

[0104] The qing-sys service starts the Galera database container on the master node and boots the cluster by setting the startup environment variable -eMySQL_GALERA_CLUSTER_BOOTSTRAP=yes. This environment variable instructs the MySQL Galera container to perform cluster bootstrapping operations when it starts.

[0105] Use the Docker command to start the master node container:

[0106] After startup, the master node will load the previously saved data and prepare to accept synchronization from other nodes.

[0107] 7. Start the database containers of other nodes in sequence

[0108] After the database container of the master node runs normally, the qing-sys service will start the Galera database container on other nodes one by one. Each node will automatically synchronize data with the master node when it starts to ensure data consistency.

[0109] Use the Docker command to start the container on other nodes:

[0110] When these nodes are started, they will synchronize data with the master node through the Galera protocol to ensure the consistency and integrity of the data in the cluster.

[0111] 8. Restart the master node database container

[0112] When all node database containers are started normally and synchronization is complete, the qing-sys service will shut down the Galera database container on the master node and restart it to ensure that the master node does not continue to run in cluster boot mode.

[0113] First, shut down the master node container using the following command: docker stop galera

[0114] Then, restart the master node container without the MySQL_GALERA_CLUSTER_BOOTSTRAP=yes parameter:

[0115] At this point, the master node joins the cluster as a normal node, completing the recovery process of the entire MySQL multi-master cluster.

[0116] 9. Cluster recovery completed

[0117] After all nodes are started and synchronized normally, the MySQL multi-master cluster is restored, the database services of all nodes are running normally, and the entire cluster is in a healthy state. The qing-sys service will continue to monitor the operation status of the cluster and provide further maintenance and troubleshooting support when necessary.

[0118] 1. Automated master node election and recovery: Through the Raft election algorithm and automated scripts, there is no need for administrators to intervene manually, ensuring that the cluster recovery process after power outages is efficient and accurate.

[0119] 2. Data consistency guarantee: By shutting down the database containers of all nodes and having the elected master node perform boot operations, the data consistency and integrity of each node is ensured to avoid data loss or inconsistency between nodes.

[0120] 3. Gradual recovery to reduce risks: This method relies on the bootstrapping of the master node and the gradual startup of other nodes, avoiding race conditions or data conflicts caused by starting multiple nodes at the same time.

[0121] 4. Scalability: This method is suitable for large-scale MySQL multi-master clusters. It can be quickly expanded to a multi-node cluster environment and effectively ensure the high availability of the cluster in the event of a sudden power outage.

[0122] 5. Reduce recovery time: This method can automatically restore the cluster after multiple nodes lose power without human intervention. After actual verification in production systems, the cluster recovery time can be reduced from an average of 10-30 minutes to 5-10 seconds.

[0123] In some specific embodiments, Figure 2 As shown, a device for recovering a MySQL multi-master cluster includes at least one processor and a memory storing instructions to implement any one of the methods for recovering a MySQL multi-master cluster in the above technical solutions.

[0124] In some specific embodiments, a computer-readable storage medium stores a computer program, and the computer program is executed by a processor to implement the steps in the above technical solution.

[0125] In some specific embodiments, a computer program product is provided, the computer program product includes a computer program, and when the computer program is executed by a processor, the method of implementing the steps in the above technical solution is implemented.

[0126] The embodiments and functional operations of the subject matter described in this specification may be implemented in: digital electronic circuitry, tangibly implemented computer software or firmware, computer hardware, including the structures disclosed in this specification and their structural equivalents, or a combination of more than one of the above. The embodiments of the subject matter described in this specification may be implemented as one or more computer programs, i.e., one or more modules of computer program instructions encoded on one or more tangible non-transitory program carriers, for execution by a data processing device or to control the operation of a data processing device.

[0127] Alternatively or additionally, the program instructions may be encoded on an artificially generated propagation signal, such as a machine-generated electrical, optical, or electromagnetic signal, which is generated to encode information for transmission to an appropriate receiver device for execution by a data processing device. The computer storage medium may be a machine-readable storage device, a machine-readable storage substrate, a random or serial access memory device, or a combination of one or more of the above devices.

[0128] A computer program (which may also be referred to or described as a program, software, software application, module, software module, script or code) may be written in any form of programming language, including compiled or interpreted languages ​​or declarative or procedural languages, and the computer program may be deployed in any form, including as a stand-alone program or as a module, component, subroutine or other unit suitable for use in a computing environment. A computer program may, but need not, correspond to a file in a file system. A program may be stored in a portion of a file that stores other programs or data, for example, one or more scripts stored in the following: in a markup language document; in a single file dedicated to a related program; or in multiple collaborative files, for example, files storing one or more modules, subroutines or code portions. A computer program may be deployed to execute on one or more computers, the computers being located in one place or distributed to multiple locations and interconnected by a communication network.

[0129] The contents not described in detail in this specification belong to the prior art known to professional and technical personnel in this field.

[0130] Although the present invention has been described in detail above by general description, specific implementation methods and experiments, it is obvious to those skilled in the art that some modifications or improvements can be made to the present invention. Therefore, these modifications or improvements made without departing from the spirit of the present invention all belong to the scope of protection claimed by the present invention.

Claims

1. A method for restoring a MySQL multi-master cluster, characterized in that: The following steps are involved: Install the qing-sys service on each node of the MySQL multi-master cluster; After multiple nodes in the cluster confirm startup and perform network preparation, the qing-sys service automatically shuts down the only database container running on each node; Based on the integrated Raft protocol, the qing-sys service automatically elects a master node in each node; The qing-sys service automatically modifies the database data status file of the master node; Start the database container corresponding to the master node; The qing-sys service starts the database containers of the nodes in the cluster except the master node in sequence; Close the database container of the master node, and restart the database container of the master node.

2. A method for restoring a MySQL multi-master cluster according to claim 1, characterized in that: The step of modifying the database data status file of the master node comprises the following steps: Change the data state from not allowing booting the cluster to booting the node, allowing it to start as the master node of the cluster.

3. A method for restoring a MySQL multi-master cluster according to claim 1, characterized in that: The step of starting the database container corresponding to the master node comprises the following steps: The qing-sys service modifies the startup parameters of the master node database container and adds an environment variable of -e MySQL_GALERA_CLUSTER_BOOTSTRAP=yes.

4. A method for restoring a MySQL multi-master cluster according to claim 1, characterized in that: The qing-sys service starts the database containers of the nodes in the cluster except the master node in sequence, and the data of the database container of each node is synchronized with the data of the database container of the master node.

5. A method for restoring a MySQL multi-master cluster according to claim 3, characterized in that: In the database container of the master node that is restarted, stop using the environment variable -e MySQL_GALERA_CLUSTER_BOOTSTRAP=yes.

6. A method for restoring a MySQL multi-master cluster according to claim 1, characterized in that: The multiple nodes in the cluster confirm the startup and perform network preparation, including the following steps: Confirm that the operating system of each node has been started and the network status is normal; The network heartbeat detection mechanism is used to confirm whether other nodes are started and can communicate with each other.

7. A method for restoring a MySQL multi-master cluster according to claim 1, characterized in that: The qing-sys service automatically shuts down the only database container running on each node, including the following steps: Use the Docker command to stop the Galera database container running on each node one by one.

8. A method for restoring a MySQL multi-master cluster according to claim 1, characterized in that: Based on the integrated Raft protocol, the qing-sys service automatically elects a master node in each node, including the following steps: The integrated Raft protocol runs between the qing-sys services of each node, and the master node is elected through message communication.

9. A method for restoring a MySQL multi-master cluster according to claim 1, characterized in that: The qing-sys service starts the database containers of the nodes in the cluster except the master node in sequence, and synchronizes the data in the data container of the master node with the data through the Galera protocol.

10. A device for recovering a MySQL multi-master cluster, the device comprising at least one processor and a memory storing instructions, and when the instructions are executed by at least one processor, a method for recovering a MySQL multi-master cluster as described in any one of claims 1 to 9 is implemented.

Images