CN115934428B

CN115934428B - Main disaster recovery and backup switching method and device of MYSQL database and electronic equipment

Info

Publication number: CN115934428B
Application number: CN202310033403.5A
Authority: CN
Inventors: 郑筠; 张春阳; 刘佳; 胡丁
Original assignee: Hunan Sanxiang Bank Co Ltd
Current assignee: Hunan Sanxiang Bank Co Ltd
Priority date: 2023-01-10
Filing date: 2023-01-10
Publication date: 2023-05-23
Anticipated expiration: 2043-01-10
Also published as: CN115934428A

Abstract

The invention relates to the technical field of databases, in particular to a method, a device and electronic equipment for switching a main disaster recovery device of a MYSQL database, which are used for determining a proper main disaster recovery switching mode for a system according to three different scenes when the MYSQL database is in a MYSQL master-slave architecture, an MGR architecture single main mode and an MGR architecture multi-main mode when a main disaster recovery switching instruction of the MYSQL database is monitored, so that the current framework mode of the system with switching requirements can be accurately obtained, personalized and automatic operation of the systems with different frameworks is realized, the technical problems that the timeliness and the accuracy of switching are difficult to ensure when DBA judgment and switching operation are needed for a plurality of systems using the MYSQL database are solved, and the technical effects of reducing the main disaster recovery manual switching cost of the MYSQL database and improving the switching efficiency are achieved.

Description

Main disaster recovery and backup switching method and device of MYSQL database and electronic equipment

Technical Field

The present invention relates to the field of databases, and in particular, to a method and an apparatus for switching between primary and backup of a MYSQL database, and an electronic device.

Background

MySQL is a relational database management system, and because of the advantages of small volume, high speed, low total possession cost, open source code and the like, a plurality of production systems select a MySQL database as a website database to develop, and the system using the MySQL database uses an MGR architecture and supports a MYSQL master-slave architecture. The MySQL database main disaster recovery automatic switching method aiming at various frameworks is not disclosed in the prior art.

Chinese patent publication No. CN114064613a discloses a method and system for preventing loss of mysql master-slave switching data, the method comprising the steps of: step S1, reading and writing separation is carried out on the service of the mysql database, the addition and deletion of data are realized through a master library of the mysql database, and the reading of data is realized through a slave library of the mysql database; s2, enabling a thread to monitor the health condition of a main library of the mysql database; step S3, when the service of the mysql database reads that the database connection string error information of the mysql database main database of the redis memory is equal to 1, temporarily storing data in the redis memory; s4, when the thread monitors that the mysql database main library is recovered to be healthy, the database connection string error information of the mysql database main library is empty; and S5, writing the accumulated data in the redis memory back to the mysql database main library, and recovering the main library of the mysql database.

Therefore, the technical scheme only realizes the primary disaster recovery switching for the master-slave frameworks of the MySQL database, and when the production systems of a plurality of different frameworks simultaneously perform the primary disaster recovery switching, DBA personnel are limited, the dependence on manual operation efficiency is too low, and the switching accuracy is difficult to ensure.

Disclosure of Invention

Therefore, the invention provides a method and a device for switching between main and standby modes of a MySQL database and electronic equipment, which are used for solving the technical problems of poor switching timeliness and low accuracy when a plurality of production systems which use the MySQL database and are different in framework are used for simultaneously switching between the main and standby modes.

In order to achieve the above objective, the present invention provides a method for switching between active and standby of MYSQL database, comprising:

when a master disaster recovery and backup switching instruction of a MYSQL database is monitored, acquiring the current state of the MYSQL database comprises the following steps:

obtaining a current framework of the MYSQL database, the framework comprising at least one of: MYSQL master-slave architecture and MGR architecture;

when the current framework is an MGR framework, acquiring a current mode of the MYSQL database, wherein the mode comprises the following steps: an MGR architecture single master mode and an MGR architecture multi-master mode;

determining a main disaster recovery switching mode according to the current state, including:

When the current state is a MYSQL master slave architecture, performing master disaster recovery and backup switching by adopting a first switching mode;

when the current state is the MGR architecture single main mode, performing main disaster recovery switching by adopting a second switching mode;

and when the current state is the MGR architecture multi-main mode, performing main disaster recovery switching by adopting a third switching mode.

Further, the performing the primary disaster recovery switching by adopting the first switching mode includes:

acquiring a first confirmation parameter M1 and a second confirmation parameter M2 in any slave library register; the initial values of the first confirmation parameter M1 and the second confirmation parameter M2 are 0, when the slave library receives the data pre-writing information sent by the master library, the register sets the first confirmation parameter M1 to 1, and when the slave library receives the data writing success information sent by the master library, the register sets the second confirmation parameter M2 to 1;

comparing the first confirmation parameter M1 with the second confirmation parameter M2;

if m1=m2, after the data of the master library and the slave library have been synchronized, correspondingly storing the instruction and the IP of the client which are not processed by the current master library, cutting off the connection between the master library and the client, changing the function of the master library into read-only, taking the slave library as a new master library, changing the function of the new master library into read-write, sending the instruction and the IP of the client to the new master library, and interacting by the new master library and the client;

If M1 is not equal to M2, the data of the master library and the data of the slave library are not completely synchronized, a time point T corresponding to the unsynchronized data is obtained, and the unsynchronized data is analyzed and processed according to the relation between the time point T and a first preset time point T1 and a second preset time point T2, wherein 0 is more than T1 and less than T2.

Further, according to the relationship between the time point T and the first preset time point T1 and the second preset time point T2, the analyzing and processing the unsynchronized data includes:

if T is more than 0 and less than or equal to T1, judging whether the asynchronous data is required to be called in the current instruction of the client, obtaining a judging result, and analyzing and processing according to the judging result;

if T1 is more than or equal to T2, acquiring backup data corresponding to a time point T in other slave libraries except the master library, and analyzing and processing according to the relation between the backup data and the asynchronous data;

and if T2 is less than T, releasing parameter resources in the register, correspondingly storing the instruction and IP of the client which are not processed by the main library currently, cutting off the connection between the main library and the client, changing the function of the main library into read-only, changing the function of the slave library into read-write, changing the function of the new main library into read-write, sending the instruction and IP of the client to the new main library, and interacting with the client by the new main library.

Further, according to the judgment result, performing analysis processing includes:

if yes, acquiring a client connected at a time point T, initiating a data recovery request to the client connected at the time point T, and analyzing and processing according to a request result;

if not, the abnormal marking is carried out on the unsynchronized data, a data access instruction is established for storing, the data access instruction is used for accessing the data corresponding to the time point T in the main library when the main library is recovered, updating and correcting are carried out on the unsynchronized data according to the data corresponding to the time point T in the main library, then the instruction and the IP of the client which is not processed by the main library at present are correspondingly stored, the connection between the main library and the client is cut off, the function of the main library is changed into read-only, the function of the slave library is changed into read-write, the function of the new main library is changed into read-write, the instruction and the IP of the client are sent to the new main library, and interaction is carried out by the new main library and the client.

Further, the analyzing and processing according to the request result includes:

if the data recovery is successful, releasing parameter resources in the register, correspondingly storing the instruction and IP of the client which are not processed by the main library currently, cutting off the connection between the main library and the client, changing the function of the main library into read-only, changing the function of the auxiliary library into read-write, changing the function of the new main library into read-write, sending the instruction and IP of the client to the new main library, and interacting with the client by the new main library;

If the data recovery fails, carrying out abnormal marking on the unsynchronized data, establishing a data access instruction for storing the data corresponding to a time point T in a main library, accessing the data corresponding to the time point T in the main library when the main library is recovered, updating and correcting the unsynchronized data according to the data corresponding to the time point T in the main library, then correspondingly storing the instruction and IP of a client which is not processed by the main library currently, cutting off the connection between the main library and the client, changing the function of the main library into read-only, taking the slave library as a new main library, changing the function of the new main library into read-write, sending the instruction and IP of the client to the new main library, and interacting the new main library and the client.

Further, the analyzing and processing according to the relation between the backup data and the asynchronous data comprises the following steps:

if the backup data and the asynchronous data are the same, a data access instruction is established for storing, the data access instruction is used for accessing data corresponding to a time point T in a main library when the main library is recovered, updating and correcting the asynchronous data according to the data corresponding to the time point T in the main library, then correspondingly storing an instruction of a client which is not processed by the main library and an IP, cutting off the connection between the main library and the client, changing the function of the main library into read-only, taking the slave library as a new main library, changing the function of the new main library into read-write, transmitting the instruction and the IP of the client to the new main library, and interacting by the new main library and the client;

And if the backup data and the asynchronous data are different, updating and correcting the asynchronous data according to the backup data, releasing parameter resources in the register, correspondingly storing an instruction and an IP of a client which are not processed by the current main library, cutting off the connection between the main library and the client, changing the function of the main library into read-only, changing the function of the slave library into read-write, sending the instruction and the IP of the client to the new main library, and interacting by the new main library and the client.

Further, the performing the primary disaster recovery switching by adopting the second switching mode includes:

calculating a weight score W of each database node, and taking the database node with the largest weight score W as a new main library;

the weight fraction w=αd _i ×βf _i ×γe _i Wherein, the method comprises the steps of, wherein,

d is the data amount, D _i E is the data volume of the ith database node _i To preset the processing efficiency, f _i =(1-F _i )/D _i ，F _i Alpha, beta and gamma are weight coefficients for the number of errors of the ith database node;

and correspondingly storing the instruction and the IP of the client which are not processed by the current main library, cutting off the connection between the main library and the client, changing the function of the main library into read-only, changing the function of the new main library into read-write, sending the instruction and the IP of the client to the new main library, and interacting by the new main library and the client.

Further, the performing the primary disaster recovery switching by adopting a third switching mode includes:

and correspondingly storing the instructions and the IP of the clients which are not processed by the current database node, cutting off the connection between the database node and the clients, changing the functions of the database node into read-only, sending the instructions and the IP of the clients to the other database nodes, and interacting with the clients by the other database nodes.

In a second aspect, the present invention provides a primary disaster recovery switching device for a MYSQL database, including:

the state monitoring module is used for acquiring the current state of the MYSQL database when monitoring a main disaster recovery and backup switching instruction of the MYSQL database;

the state monitoring module comprises a state monitoring unit; the state monitoring unit is used for acquiring the current framework of the MYSQL database, and the framework comprises at least one of the following components: MYSQL master-slave architecture and MGR architecture; when the current framework is an MGR framework, acquiring a current mode of the MYSQL database, wherein the mode comprises the following steps: an MGR architecture single master mode and an MGR architecture multi-master mode;

the mode determining module is used for determining a main disaster recovery switching mode according to the current state;

The mode determining module comprises a first switching unit, a second switching unit and a third switching unit; the first switching unit is used for switching between the primary disaster recovery and the backup disaster recovery by adopting a first switching mode when the current state is a MYSQL master-slave architecture; the second switching unit is used for performing primary disaster recovery switching by adopting a second switching mode when the current state is the MGR architecture single primary mode; and the third switching unit is used for switching the primary disaster recovery from the primary disaster recovery by adopting a third switching mode when the current state is the MGR architecture multi-primary mode.

In a third aspect, the present invention provides an electronic device comprising:

one or more processors;

a memory for storing one or more programs;

when the one or more programs are executed by the one or more processors, the one or more processors implement a method for switching between primary and backup of a MYSQL database according to the first aspect.

Compared with the prior art, the method has the beneficial effects that by monitoring the main disaster recovery switching instruction of the MYSQL database, the framework mode currently applied by the client using the database is obtained in response to the monitoring of the instruction, and the personalized main disaster recovery switching mode is determined for the system according to three different scenes when the system where the client is positioned is in a MYSQL master-slave architecture, an MGR single-master mode and an MGR multi-master mode, and when the current state is the MYSQL master-slave architecture, the main disaster recovery switching is performed by adopting a first switching mode; when the current state is the MGR architecture single main mode, performing main disaster recovery switching by adopting a second switching mode; when the current state is the MGR architecture multi-main mode, the main disaster recovery switching is performed by adopting a third switching mode, so that the main disaster recovery switching is performed by using different modes aiming at different architecture mode scenes, the method can be applied to disaster recovery drilling switching of a database, is also suitable for scenes in which the main database is down in the use process of the database, and has wide application range and high automation degree. The method solves the technical problems of low accuracy, low efficiency and the like of the manual processing database main disaster recovery switching in the prior art.

Furthermore, the present invention sets a register for storing the data synchronization situation between the master and slave libraries, when the slave libraries receive the data pre-writing information sent by the master library, the register sets 1 the first confirmation parameter M1, when the slave libraries receive the data writing success information sent by the master library, the register sets 1 the second confirmation parameter M2, at this time, the first confirmation parameter M1 and the second confirmation parameter M2 are compared, when the first confirmation parameter M1 and the second confirmation parameter M2 correspond to each other, it is indicated that the data of the master library and the slave libraries are synchronized, the connection between the old master library and the client is cut off, the functions of the new master library and the old master library are changed, and the unprocessed tasks received by the old master library are delivered to the new master library for further processing, when the first confirmation parameter M1 and the second confirmation parameter M2 correspond to each other, it is indicated that the slave libraries have not completed the data synchronization or the register has a fault, and the time of the unsynchronized data is analyzed further. By detecting whether the data pre-sending information and the data writing completion information received by the slave library exist correspondingly, whether the master-slave library has completed data synchronization or not is accurately judged, the current situation of acquiring the master-slave library data synchronization is quickly acquired before the master-slave switching, the data loss in the master-slave switching process is effectively avoided, the data integrity of the master-slave switching in the MYSQL master-slave architecture is ensured, and the automatic switching is realized.

Further, when the fact that the data synchronization is not completed in the master-slave library is detected, the unsynchronized data are analyzed and processed according to the relation between the time point T and the first preset time point T1 and the second preset time point T2, when the unsynchronized data are the recent data, the possibility that the unsynchronized data are called is relatively high, so that whether the unsynchronized data need to be called in an instruction of the current client is further judged, when the unsynchronized data are historical data, the data synchronization condition is judged according to the data of other slave libraries, when the unsynchronized data are long-term data, the unsynchronized phenomenon exists on the surface in time, the influence on the client is avoided in a period of time, therefore, parameter resources in a register are released, the master-slave library is directly switched, different follow-up operation modes are realized according to different time periods where the unsynchronized data are located, normal operation of the client is guaranteed, data loss is reduced to the maximum extent, meanwhile, the master-slave switching cost of the database is considered, and automatic master-slave switching in MYSQL architecture is realized.

Further, in the invention, when the asynchronous data exists in the recent data in the master-slave library, judging whether the asynchronous data is required to be called in the current instruction of the client, if so, acquiring the client connected at the time point T, initiating a data recovery request to the client connected at the time point T, if not, carrying out abnormal marking on the asynchronous data, when the client needs to call the asynchronous data, indicating that the asynchronous data is used in a large probability, initiating a data recovery request to the client according to the record of the client connected at the time of the master library, so as to quickly carry out data synchronization, and if the client does not need to call, indicating that the call probability of the client to the data in the recent time is small, so that the asynchronous data is abnormally marked, and then carrying out data synchronization request again after the master library is down fault recovery, firstly carrying out master disaster backup switching so as to maintain the normal operation order of the client, judging whether the data is urgent according to the utilization rate of the asynchronous data, and determining the time and mode of carrying out data synchronization recovery according to the urgent disaster recovery according to the degree of the asynchronous data, thereby realizing balance between the use of the maintenance client and the master disaster recovery.

Further, the method initiates a data recovery request to the client connected at the time point T, if the client calls data, the data corresponding to the time point T can be sent to the slave library, the slave library data is successfully recovered, parameter resources in the register are released, master-slave switching operation is immediately carried out, if the data resources of the client are released, the data cannot be recovered quickly, firstly, the unsynchronized data are marked abnormally, when the subsequent data of the old master library is recovered, the old master library data are read again to carry out data synchronization on the unsynchronized data, when the client cannot provide the unsynchronized data in real time to complete data synchronization recovery, a data access instruction is established to store, the data access instruction is used for accessing the master library data after the master library is in a down state to realize data synchronization, the time and mode of the data synchronization are determined through the current state of the client, the technical effect of dynamically carrying out data recovery is achieved, different situations of the data synchronization process are fully considered, and the complete data synchronization is realized.

Furthermore, in the invention, when the backup data and the asynchronous data are the same, two situations may exist, the data synchronization is not carried out between the plurality of slave libraries and the master library, or the working process of the register is wrong, at this time, a data access instruction is established for storing the data corresponding to the time point T in the master library, the data access instruction is used for accessing the data corresponding to the time point T in the master library when the master library is recovered, if the backup data and the asynchronous data are different, the data synchronization is not carried out between the plurality of slave libraries and the master library, the asynchronous data are updated and corrected according to the backup data, parameter resources in the register are released, if the backup data and the asynchronous data are the same, the fault of the register is indicated, the register is reported and corrected, if the backup data and the asynchronous data are different, the asynchronous data are updated and corrected according to the backup data, and the data synchronization is carried out between the slave libraries, the data synchronization can also be realized, and the master-slave library switching is carried out after the correction. The data synchronization problem under the downed scene of the main library is solved by calling the backup data corresponding to the other slave library time points T and the unsynchronized data for comparison, the operation process of data synchronization is determined according to the relation between the backup data of the other slave library and the unsynchronized data, the data synchronization is rapidly and skillfully completed, and the safety of the main disaster recovery and the backup switching of the database is ensured.

Further, when the current state is the MGR architecture single main mode, the weight score W of each database node is calculated, the database node with the largest weight score W is used as a new main database, the data quantity, the processing efficiency and the error rate of each database node are considered when the new main database is determined, the corresponding preset weight coefficient is multiplied, related personnel can flexibly set the weight coefficient according to the system type and the use scene of the used database, the determined database node is the best node capable of matching the current client and the system requirement, after the determination of the new main database is completed, the old main database and the new main database are functionally set, the main disaster and backup are switched under the MGR architecture single main mode, the determination of the main database is completed according to the preset algorithm, the technical problem that the adaptation degree of the new main database and the database is not high is solved, the technical effect that the MYSQL database is switched according to the conditions of using the system is prone is realized, and the personalized main disaster and backup switching is realized when the R architecture single main disaster and backup are in the single mode.

Further, when the current state is the MGR architecture multi-main mode, the invention correspondingly stores the instruction and IP of the client which are not processed by the current database node, cuts off the connection between the database node and the client, changes the function of the database node into read-only, sends the instruction and IP of the client to other database nodes, and interacts with the client by the other database nodes, and only changes the function of the downtime node into read-only by considering the node operation mode of the MGR architecture multi-main mode, and the decision and execution of the transaction jointly participated by the other main nodes, thereby realizing the quick switching of the main disaster recovery when the MGR architecture multi-main mode.

Drawings

FIG. 1 is a flowchart of a method for switching between active and standby of a MYSQL database according to an embodiment of the present invention;

fig. 2 is a schematic structural diagram of a primary disaster recovery switching device for MYSQL database according to an embodiment of the present invention.

Detailed Description

In order that the objects and advantages of the invention will become more apparent, the invention will be further described with reference to the following examples; it should be understood that the specific embodiments described herein are for purposes of illustration only and are not intended to limit the scope of the invention.

Preferred embodiments of the present invention are described below with reference to the accompanying drawings. It should be understood by those skilled in the art that these embodiments are merely for explaining the technical principles of the present invention, and are not intended to limit the scope of the present invention.

It is noted that the terms "comprises" and "comprising," and any variations thereof, in the description and claims of the present invention and in the foregoing figures, are intended to cover a non-exclusive inclusion, such that a process, method, system, article, or apparatus that comprises a list of steps or elements is not necessarily limited to those steps or elements expressly listed or inherent to such process, method, article, or apparatus.

Fig. 1 is a flowchart of a method for switching between primary and backup of a MYSQL database according to an embodiment of the present invention, where the method includes:

s101: when a master disaster recovery switching instruction of a MYSQL database is monitored, acquiring a current framework of the MYSQL database, wherein the framework comprises at least one of the following components: MYSQL master-slave architecture and MGR architecture;

s102: when the current framework is an MGR framework, acquiring a current mode of the MYSQL database, wherein the mode comprises the following steps: an MGR architecture single master mode and an MGR architecture multi-master mode;

s103: when the current state is a MYSQL master slave architecture, performing master disaster recovery and backup switching by adopting a first switching mode;

s104: when the current state is the MGR architecture single main mode, performing main disaster recovery switching by adopting a second switching mode;

s105: and when the current state is the MGR architecture multi-main mode, performing main disaster recovery switching by adopting a third switching mode.

Alternatively, the current state of the MYSQL database may be obtained by: the main disaster recovery switching instruction sent by the client to the MYSQL database can comprise an address code and an operation code, wherein the address code stores the IP address of the client sending the instruction, and the operation code can contain the main disaster recovery switching instruction and the architecture mode of the server currently used by the client sending the instruction, so that the current state of the MYSQL database is directly obtained from the main disaster recovery switching instruction. Optionally, the current state of the MYSQL database can be obtained by sending a main disaster recovery switching instruction to the MYSQL database by the client, wherein the instruction can comprise an address code for storing the IP address of the client and an operation code for conveying the main disaster recovery switching instruction, sending a framework mode inquiry message to the client after receiving the main disaster recovery switching instruction, reading the framework mode message sent again by the client, and obtaining the current state of the database through two interactions between the client and the client.

According to the method, a master disaster recovery switching mode of individuation is determined for a system according to three different scenes when a system in which a client is positioned is in a MYSQL master-slave architecture, an MGR architecture single master mode and an MGR architecture multi-master mode, and when the current state is the MYSQL master-slave architecture, the master disaster recovery switching mode is performed by adopting a first switching mode; when the current state is the MGR architecture single main mode, performing main disaster recovery switching by adopting a second switching mode; when the current state is the MGR architecture multi-main mode, the main disaster recovery switching is performed by adopting a third switching mode, so that the main disaster recovery switching is performed by using different modes aiming at different architecture mode scenes, the method can be applied to disaster recovery drilling switching of a database, is also suitable for scenes in which the main database is down in the use process of the database, and has wide application range and high automation degree. The method solves the technical problems of low accuracy, low efficiency and the like of the manual processing database main disaster recovery switching in the prior art.

Specifically, the performing the primary disaster recovery switching by adopting the first switching mode includes:

Optionally, if m1+.m2, it may be detected whether the first preset time point T1 is 0, and if the first preset time point T1 is 0, it represents that the register has a fault, and it may preferentially perform fault reminding on the register and attempt data recovery.

Specifically, a register is set in each slave library for storing the relevant parameters of the data synchronization information. When the data synchronization is carried out between the master library and the slave library or the data synchronization is carried out by the client side, before the client side sends data to the master library, the client side sends a data sending request to the master library, the master library sends a message about to receive the data to the slave library and simultaneously sends an approval receiving instruction to the client side, at the moment, a register in the slave library sets a first confirmation parameter M1 from 0 to 1, the client side receives the approval receiving instruction and then carries out data transmission to the master library, after the data reception is finished by the master library, the client side replies a receipt receiving finishing instruction, at the moment, the slave library starts to carry out the data synchronization operation with the master library, and when the data synchronization between the slave library and the master library is finished, the register in the slave library sets a second confirmation parameter M2 from 0 to 1. Optionally, the register stores the first confirmation parameter M1 and the second confirmation parameter M2 in a corresponding manner according to time sequence.

The invention sets a register for storing the data synchronization condition between a master library and a slave library, when a slave library receives the data pre-writing information sent by a master library, the register sets 1 the first confirmation parameter M1, when the slave library receives the data writing success information sent by the master library, the register sets 1 the second confirmation parameter M2, at the moment, the first confirmation parameter M1 and the second confirmation parameter M2 are compared, when the first confirmation parameter M1 and the second confirmation parameter M2 are corresponding to each other, the data of the master library and the slave library are synchronous, the connection between an old master library and a client is cut off, the functions of a new master library and the old master library are changed, the unprocessed tasks received by the old master library are handed to the new master library for continuous processing, when the first confirmation parameter M1 and the second confirmation parameter M2 are not corresponding to each other, the data synchronization between the slave library and the master library or the register has faults, and the time of the unsynchronized data is analyzed further at the moment. By detecting whether the data pre-sending information and the data writing completion information received by the slave library exist correspondingly, whether the master-slave library has completed data synchronization or not is accurately judged, the current situation of acquiring the master-slave library data synchronization is quickly acquired before the master-slave switching, the data loss in the master-slave switching process is effectively avoided, the data integrity of the master-slave switching in the MYSQL master-slave architecture is ensured, and the automatic switching is realized.

Specifically, according to the relationship between the time point T and the first preset time point T1 and the second preset time point T2, the analysis and the processing of the unsynchronized data include:

According to the method and the device, when the fact that the master-slave library does not complete data synchronization is detected, the unsynchronized data are analyzed and processed according to the relation between the time point T and the first preset time point T1 and the second preset time point T2, when the unsynchronized data are recent data, the possibility that the unsynchronized data are called is relatively high, so that whether the unsynchronized data need to be called in a current instruction of the client is further judged, when the unsynchronized data are historical data, the data synchronization situation is judged according to the data of other slave libraries, when the unsynchronized data are long-term data, the unsynchronized phenomenon exists on the surface of the data in time, the influence on the client is avoided in a period of time, therefore, parameter resources in a register are released, the master-slave library is directly switched, different follow-up operation modes are realized according to different time periods where the unsynchronized data are located, normal operation of the client is guaranteed, data loss is reduced to the maximum extent, meanwhile, the disaster-free switching cost of the database is also considered, and automatic switching of the master and slave is realized when the master-slave is in a master-slave SQL architecture.

Specifically, the analyzing process is performed according to the judging result, including:

When the asynchronous data exists in the recent data in the master-slave library, judging whether the asynchronous data is required to be called in the current instruction of the client, if so, acquiring the client connected at the time point T, initiating a data recovery request to the client connected at the time point T, if not, carrying out abnormal marking on the asynchronous data, when the client needs to call the asynchronous data, initiating a data recovery request to the client according to the record of the client connected at one time by the master library to quickly carry out data synchronization, if not, indicating that the call probability of the client to the data is small in the recent time, carrying out abnormal marking on the asynchronous data, and then carrying out data synchronization request again until the master library is down, firstly carrying out primary disaster backup switching to maintain the order of normal operation of the client, judging whether the data is urgent according to the utilization rate of the asynchronous data, determining the time and mode of carrying out data synchronization restoration according to the urgent degree of the asynchronous data, and realizing balanced disaster recovery in the normal use of the maintenance client and primary disaster backup switching.

Specifically, the analyzing and processing are performed according to the request result, including:

Specifically, the analyzing and processing according to the relation between the backup data and the asynchronous data comprises the following steps:

When the backup data and the asynchronous data are the same, the data synchronization is possibly carried out between a plurality of slave libraries and the master library, or errors exist in the working process of the register, a data access instruction is established at the moment and is used for accessing data corresponding to a time point T in the master library when the master library is recovered, if the backup data and the asynchronous data are different, the data synchronization is carried out between the plurality of slave libraries and the master library, the asynchronous data are updated and corrected according to the backup data, parameter resources in the register are released, if the backup data and the asynchronous data are the same, the register faults are indicated, the register is reported and corrected, if the backup data and the asynchronous data are different, the asynchronous data are updated and corrected according to the backup data, and the data synchronization can be realized through the data synchronization between the slave libraries, and the switching between the master library and the slave library is carried out after the correction. The data synchronization problem under the downed scene of the main library is solved by calling the backup data corresponding to the other slave library time points T and the unsynchronized data for comparison, the operation process of data synchronization is determined according to the relation between the backup data of the other slave library and the unsynchronized data, the data synchronization is rapidly and skillfully completed, and the safety of the main disaster recovery and the backup switching of the database is ensured.

Specifically, the performing the primary disaster recovery switching by adopting the second switching mode includes:

Optionally, the initial values of the weight coefficients α, β and γ may be set to α=0.5, β=0.3 and γ=0.2, and the subsequent working process may be modified by the relevant personnel according to the specific scenario, which is set by the specific system.

When the current state is the MGR architecture single main mode, the weight score W of each database node is calculated, the database node with the largest weight score W is used as a new main database, the data quantity, the processing efficiency and the error rate of each database node are considered when the new main database is determined, the corresponding preset weight coefficient is multiplied, related personnel can flexibly set the weight coefficient according to the system type and the use scene of the used database, the determined database node is the best node capable of matching the current client and the system requirement, after the determination of the new main database is completed, the old main database and the new main database are functionally set, the main disaster backup switching under the MGR architecture single main mode is completed, the determination of the main database is carried out according to the preset algorithm, the technical problem that the adaptation degree of the new main database and the database using a system is not high is solved, the technical effect that the MY type database is subjected to main disaster backup switching according to the condition of the use system is realized, and the main disaster backup individuation switching when the MGR architecture single main mode is realized.

Specifically, the performing the primary disaster recovery switching by adopting the third switching mode includes:

When the current state is the MGR architecture multi-main mode, the invention correspondingly stores the instruction and IP of the client which are not processed by the current database node, cuts off the connection between the database node and the client, changes the function of the database node into read-only, sends the instruction and IP of the client to other database nodes, and interacts with the client by the other database nodes, and only changes the function of the downtime node into read-only by considering the node operation mode of the MGR architecture multi-main mode, and the decision and execution of the transaction are jointly participated by the other main nodes, thereby realizing the quick switching of the main disaster equipment when the MGR architecture multi-main mode.

Referring to fig. 2, a schematic structural diagram of a device for switching between primary and backup of a MYSQL database according to an embodiment of the present invention is shown, where the device for switching between primary and backup of a MYSQL database includes a status monitoring module 201 and a status monitoring module 201;

The state monitoring module 201 is configured to obtain a current state of a MYSQL database when a master disaster recovery backup switching instruction of the MYSQL database is monitored;

the state monitoring module 201 includes a state monitoring unit; the state monitoring unit is used for acquiring the current framework of the MYSQL database, and the framework comprises at least one of the following components: MYSQL master-slave architecture and MGR architecture; when the current framework is an MGR framework, acquiring a current mode of the MYSQL database, wherein the mode comprises the following steps: an MGR architecture single master mode and an MGR architecture multi-master mode;

the mode determining module 202 is configured to determine a primary disaster recovery switching mode according to the current state;

the mode determining module 202 includes a first switching unit, a second switching unit, and a third switching unit; the first switching unit is used for switching between the primary disaster recovery and the backup disaster recovery by adopting a first switching mode when the current state is a MYSQL master-slave architecture; the second switching unit is used for performing primary disaster recovery switching by adopting a second switching mode when the current state is the MGR architecture single primary mode; the third switching unit is used for performing main disaster recovery switching by adopting a third switching mode when the current state is the MGR architecture multi-main mode;

The first switching unit comprises a data synchronization component; the data synchronization component is used for acquiring a first confirmation parameter M1 and a second confirmation parameter M2 in any slave library register; the initial values of the first confirmation parameter M1 and the second confirmation parameter M2 are 0, when the slave library receives the data pre-writing information sent by the master library, the register sets the first confirmation parameter M1 to 1, and when the slave library receives the data writing success information sent by the master library, the register sets the second confirmation parameter M2 to 1;

Analyzing and processing the unsynchronized data according to the relation between the time point T and the first preset time point T1 and the second preset time point T2, wherein the method comprises the following steps:

And analyzing according to the judging result, wherein the analyzing comprises the following steps:

Analyzing and processing according to the request result, including:

Analyzing and processing according to the relation between the backup data and the asynchronous data, wherein the analyzing and processing comprises the following steps:

The second switching unit comprises a main library determining component; the main library determining component is used for performing main disaster recovery switching by adopting a second switching mode and comprises the following steps:

The third switching unit comprises a database switching component, and the database switching component is used for performing primary disaster recovery and backup switching by adopting a second switching mode and comprises the following components:

The embodiment of the invention also provides electronic equipment, which comprises:

one or more processors;

a memory for storing one or more programs;

when the one or more programs are executed by the one or more processors, the one or more processors are caused to execute various functional applications and data processing, for example, to implement a method for switching between primary and backup of a MYSQL database provided by the foregoing embodiments of the present invention.

From the above description of embodiments, it will be clear to a person skilled in the art that the present invention may be implemented by means of software and necessary general purpose hardware, but of course also by means of hardware, although in many cases the former is a preferred embodiment. Based on such understanding, the technical solution of the present invention may be embodied essentially or in a part contributing to the prior art in the form of a software product, which may be stored in a computer readable storage medium, such as a floppy disk, a Read-Only Memory (ROM), a random access Memory (RandomAccess Memory, RAM), a FLASH Memory (FLASH), a hard disk or an optical disk of a computer, etc., and include several instructions for causing a computer device (which may be a personal computer, a server, or a network device, etc.) to execute the method according to the embodiments of the present invention.

It should be noted that, in the embodiment of the foregoing primary disaster recovery switching device for MYSQL database, each unit and module included are only divided according to the functional logic, but are not limited to the above-described division, so long as the corresponding functions can be implemented; in addition, the specific names of the functional units are also only for distinguishing from each other, and are not used to limit the protection scope of the present invention.

Note that the above is only a preferred embodiment of the present invention and the technical principle applied. It will be understood by those skilled in the art that the present invention is not limited to the particular embodiments described herein, but is capable of various obvious changes, rearrangements and substitutions as will now become apparent to those skilled in the art without departing from the scope of the invention. Therefore, while the invention has been described in connection with the above embodiments, the invention is not limited to the embodiments, but may be embodied in many other equivalent forms without departing from the spirit or scope of the invention, which is set forth in the following claims.

The foregoing description is only of the preferred embodiments of the invention and is not intended to limit the invention; various modifications and variations of the present invention will be apparent to those skilled in the art. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present invention should be included in the protection scope of the present invention.

Claims

1. A method for switching between primary and backup of MYSQL database is characterized by comprising the following steps:

when the current state is the MGR architecture multi-main mode, performing main disaster recovery switching by adopting a third switching mode;

the switching of the primary disaster recovery device by adopting the first switching mode comprises the following steps:

if M1 is not equal to M2, the data of the master library and the data of the slave library are not completely synchronized, a time point T corresponding to the unsynchronized data is obtained, and the unsynchronized data is analyzed and processed according to the relation between the time point T and a first preset time point T1 and a second preset time point T2, wherein 0 is more than T1 and less than T2;

the method for switching the main disaster recovery from the disaster by adopting the second switching mode comprises the following steps:

correspondingly storing an instruction and an IP of a client which are not processed by a current main library, cutting off the connection between the main library and the client, changing the function of the main library into read-only, changing the function of the new main library into read-write, sending the instruction and the IP of the client to the new main library, and interacting by the new main library and the client;

the method for switching the main disaster recovery device by adopting the third switching mode comprises the following steps:

and correspondingly storing the instructions and the IP of the clients which are not processed by the current database node, cutting off the connection between the database node and the clients, changing the functions of the database node into read-only, sending the instructions and the IP of the clients to other database nodes, and interacting with the clients by the other database nodes.

2. The method for switching between primary and backup of MYSQL database according to claim 1, wherein the root

3. The method for switching between primary and backup of MYSQL database according to claim 2, wherein the analyzing according to the determination result comprises:

4. The method for switching between primary and backup of MYSQL database according to claim 3, wherein the analyzing according to the request result comprises:

5. The method for switching between primary and backup of MYSQL database according to claim 2, wherein the analyzing according to the relationship between the backup data and the unsynchronized data comprises:

6. A primary disaster recovery switching device of a MYSQL database is characterized by comprising:

the mode determining module comprises a first switching unit, a second switching unit and a third switching unit; the first switching unit is used for switching between the primary disaster recovery and the backup disaster recovery by adopting a first switching mode when the current state is a MYSQL master-slave architecture; the second switching unit is used for performing primary disaster recovery switching by adopting a second switching mode when the current state is the MGR architecture single primary mode; the third switching unit is used for performing main disaster recovery switching by adopting a third switching mode when the current state is the MGR architecture multi-main mode;

7. An electronic device, comprising:

one or more processors;

a memory for storing one or more programs;

when the one or more programs are executed by the one or more processors, the one or more processors implement the method for switching between primary and backup of MYSQL databases according to any of claims 1-5.