CN115934428A

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

Info

Publication number: CN115934428A
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-04-07
Anticipated expiration: 2043-01-10
Also published as: CN115934428B

Abstract

The invention relates to the technical field of databases, in particular to a main disaster backup switching method, a main disaster backup switching device and electronic equipment for a MYSQL database.

Description

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

Technical Field

The invention relates to the technical field of databases, in particular to a main disaster recovery backup switching method and device of a MYSQL database and electronic equipment.

Background

MySQL is a relational database management system, and due to the advantages of small size, high speed, low overall cost, open source codes and the like, a MySQL database is selected as a website database for development by a plurality of production systems, and the system using the MySQL database not only uses an MGR framework, but also supports a MySQL master-slave framework. The automatic switching method of the MySQL database main disaster recovery aiming at various frameworks is not disclosed in the prior art.

Chinese patent publication No. CN114064613A discloses a method and system for mysql master-slave switching data not to be lost, the method comprising the following steps: s1, reading and writing separation is carried out on the business of the mysql database, the addition and deletion of data are realized through a main database of the mysql database, and the reading of the data is realized through a slave database of the mysql database; s2, starting a thread to monitor the health condition of a main library of the mysql database; s3, when the service of the mysql database reads that the database connection string error information of the mysql database main library of the redis memory is equal to 1, temporarily storing the data in the redis memory; s4, when the thread monitors that the main library of the mysql database is healthy, the error information of the database connection string of the main library of the mysql database 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 for use.

Therefore, according to the technical scheme, the main disaster recovery backup switching is realized only for the main and the slave frameworks of the MySQL database, when the main disaster recovery backup switching is simultaneously performed on the production systems with different frameworks, DBA personnel are limited, the manual operation efficiency is too low, and the switching accuracy is difficult to ensure.

Disclosure of Invention

Therefore, the invention provides a main disaster recovery switching method and device 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 have different structures perform main disaster recovery switching at the same time.

In order to achieve the above object, the present invention provides a main disaster recovery switching method for a MYSQL database, which includes:

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

obtaining a current framework of the MYSQL database, wherein the framework comprises at least one of the following: MYSQL master-slave and MGR architectures;

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

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

when the current state is the MYSQL master-slave architecture, a first switching mode is adopted to carry out master disaster recovery backup switching;

when the current state is the MGR framework single-master mode, a second switching mode is adopted to carry out master disaster recovery switching;

and when the current state is the MGR architecture multi-master mode, a third switching mode is adopted to carry out master disaster recovery switching.

Further, the main disaster recovery switching is performed by adopting a first switching mode, which comprises:

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 data pre-write information sent by a master library, the register sets the first confirmation parameter M1 to 1, and when the slave library receives data write 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, the data of the master library and the slave library are synchronized, correspondingly storing the instruction and 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 IP of the client to the new master library, and performing interaction by the new master library and the client;

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

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

if T is more than 0 and less than or equal to T1, judging whether the different-step data needs to be called in the instruction of the client at present to obtain a judgment result, and analyzing and processing according to the judgment result;

if T1 is larger than T and smaller than or equal to T2, acquiring backup data corresponding to time points T in other slave libraries except the master library, and analyzing and processing according to the relation between the backup data and the data at different steps;

if T2 is less than T, parameter resources in the register are released, instructions and IP of the client which are not processed by the current master library are correspondingly stored, connection between the master library and the client is cut off, the function of the master library is changed into read-only, the slave library is used as a new master library, the function of the new master library is changed into read-write, the instructions and IP of the client are sent to the new master library, and interaction is carried out between the new master library and the client.

Further, performing analysis processing according to the determination result, including:

if yes, the client connected at the time point T is obtained, a data recovery request is sent to the client connected at the time point T, and analysis processing is carried out according to a request result;

if not, carrying out exception marking on the different synchronous data, establishing a data access instruction for storage, wherein the data access instruction is used for accessing data corresponding to a time point T in a master library when the master library is recovered, updating and correcting the different synchronous data according to the data corresponding to the time point T in the master library, then correspondingly storing an instruction and an IP of a client which are not processed by the master library at present, 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 carrying out interaction between the new master library and the client.

Further, the analysis processing is performed according to the request result, and the analysis processing comprises the following steps:

if the data recovery is successful, parameter resources in the register are released, instructions and IP of a client which are not processed by the current master library are correspondingly stored, the connection between the master library and the client is cut off, the function of the master library is changed into read-only, the slave library is used as a new master library, the function of the new master library is changed into read-write, the instructions and IP of the client are sent to the new master library, and the new master library and the client interact;

if the data recovery fails, carrying out exception marking on the different synchronous data, establishing a data access instruction for storage, wherein the data access instruction is used for accessing data corresponding to a time point T in a master library when the master library is recovered, updating and correcting the different synchronous data according to the data corresponding to the time point T in the master library, then correspondingly storing an instruction and an IP of a client which is 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 carrying out interaction between the new master library and the client.

Further, performing analysis processing according to the relationship between the backup data and the different step data, including:

if the backup data is the same as the asynchronous data, establishing a data access instruction for storing, wherein the data access instruction is used for accessing data corresponding to a time point T in a master library when the master library is recovered, updating and correcting the asynchronous data according to the data corresponding to the time point T in the master library, then correspondingly storing an instruction and an IP (Internet protocol) of a client which is not processed by the master library at present, cutting off the connection between the master library and the client, changing the function of the master library into read-only, using 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 carrying out interaction by the new master library and the client;

if the backup data is different from the asynchronous data, updating and correcting the asynchronous data according to the backup data, releasing parameter resources in the register, then correspondingly storing instructions and IP of a client which is 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 instructions and IP of the client to the new master library, and interacting between the new master library and the client.

Further, the performing of the main disaster recovery and backup switching by using the second switching mode includes:

calculating the weight score W of each database node, and taking the database node with the maximum weight score W as a new master database;

the weight fraction W = α d _i ×βf _i ×γe _i Wherein, in the step (A),

d is the data volume, D _i Amount of data for ith database node, e _i To preset the treatment efficiency, f _i =(1-F _i )/D _i ，F _i The error times of the ith database node are alpha, beta and gamma, which are weight coefficients;

the method comprises the steps of correspondingly storing instructions and IP of a client which are not processed by a current master library, cutting off the connection between the master library and the client, changing the function of the master library into read-only, changing the function of a new master library into read-write, sending the instructions and IP of the client to the new master library, and interacting between the new master library and the client.

Further, the main disaster recovery switching is performed by adopting a third switching mode, which includes:

the method comprises the steps of correspondingly storing an instruction and an IP of a client which are not processed by a current database node, cutting off the connection between the database node and the client, changing the function of the database node into read-only, sending the instruction and the IP of the client to other database nodes, and interacting with the client by the other database nodes.

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

the system comprises a state monitoring module, a state judging module and a state judging module, wherein the state monitoring module is used for acquiring the current state of a MYSQL database when a main disaster recovery backup switching instruction of the MYSQL database is monitored;

the state monitoring module comprises a state monitoring unit; the state monitoring unit is used for acquiring a current framework of the MYSQL database, and the framework comprises at least one of the following components: MYSQL master-slave and MGR architectures; when the current framework is an MGR framework, obtaining a current mode of the MYSQL database, wherein the mode comprises the following steps: MGR architecture single master mode and 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 the main disaster recovery backup by adopting a first switching mode when the current state is MYSQL master-slave architecture; the second switching unit is used for performing main disaster recovery backup switching by adopting a second switching mode when the current state is the single main mode of the MGR architecture; and the third switching unit is used for performing main disaster recovery switching by adopting a third switching mode when the current state is the multi-main mode of the MGR architecture.

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 the main disaster recovery switching method for the MYSQL database according to the first aspect.

Compared with the prior art, the method has the advantages that a main disaster backup switching instruction of the MYSQL database is monitored, a framework mode currently applied by a client using the database is obtained in response to the monitored instruction, a personalized main disaster backup switching mode is determined for a system according to three different scenes when the system where the client is located is in a MYSQL master-slave framework, a MGR framework single-master mode and a MGR framework multi-master mode, and when the current state is the MYSQL master-slave framework, the main disaster backup switching is carried out in a first switching mode; when the current state is the single-master mode of the MGR framework, a second switching mode is adopted to carry out master disaster backup switching; when the current state is the MGR architecture multi-master mode, the third switching mode is adopted to switch the master disaster recovery and backup, so that the master disaster recovery and backup switching can be performed in different modes aiming at different architecture mode scenes, the method can be applied to disaster recovery and backup drilling switching of the database, and is also applicable to a scene that the master database is down in the using process of the database, the application range is wide, and the automation degree is high. The method solves the technical problems of low accuracy, low efficiency and the like of manual processing of the main disaster backup switching of the database in the prior art.

Further, the present invention sets a register for storing data synchronization between the master library and the slave library, when receiving data pre-write information sent by the master library from the slave library, the register sets the first confirmation parameter M1 to 1, when receiving data write success information sent by the master library from the slave library, the register sets the second confirmation parameter M2 to 1, 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 are equal to each other, it indicates that the data synchronization between the master library and the slave library is completed, 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 to continue processing, when the first confirmation parameter M1 and the second confirmation parameter M2 are not equal to each other, it indicates that the data synchronization between the slave library and the master library is not completed or the register has a failure, and the time of the un-synchronized data is further analyzed. Whether data synchronization of the master library and the slave library is finished or not is accurately judged by detecting whether data pre-sending information and data writing completion information received by the slave library exist correspondingly or not, the data synchronization current situation of the master library and the slave library is quickly obtained before the master disaster backup switching, data loss in the master disaster backup switching process is effectively avoided, the data integrity of the master disaster backup switching during MYSQL master and slave architecture is guaranteed, and automatic switching is realized.

Further, when the master-slave library is detected to be not synchronized with data, the different-step data are 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, when the unsynchronized data are recent data, the probability that the unsynchronized data are possibly called is high, so that whether the different-step data need to be called in the current client-side instruction 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 data are timely unsynchronized and do not influence the client-side within a period of time, so that the parameter resources in the register are released, the master-slave library is directly switched, different subsequent operation modes are realized according to different time periods where the unsynchronized data are located, normal operation of the client-side is guaranteed, data loss is reduced to the maximum extent, meanwhile, the master disaster backup SQL switching cost of the database is also considered, and automatic master-slave backup switching of the MY framework is realized.

Further, the invention judges whether the current instruction of the client needs to call the asynchronous data when the recent data in the master-slave library has unsynchronized data, if so, the client connected at the time point T is obtained, a data recovery request is initiated to the client connected at the time point T, if not, the asynchronous data is abnormally marked, when the client needs to call the asynchronous data, the probability of using the asynchronous data is high, the data recovery request is initiated to the client according to the record of the client connected by the master library once, so as to quickly carry out data synchronization, if the client does not need to call, the probability of calling the data by the client in the near future is low, so the asynchronous data is abnormally marked, the data synchronization request is carried out again after the breakdown of the master library is repaired, the master-slave disaster switch is firstly carried out to maintain the normal running order of the client, whether the data is urgent or not is judged according to the utilization rate of the unsynchronized data, the time and the mode for carrying out data synchronization repair are determined according to the urgent degree of the unsynchronized data, and the balance between the normal use of the client and the master-slave disaster switch cost is realized in the maintenance of the normal use of the client and the master disaster slave disaster switch.

Furthermore, the invention initiates a data recovery request to the connected client terminal at the time point T, if the client terminal facilitates data call, the data corresponding to the time point T can be sent to the slave library, the slave library data is successfully recovered, the parameter resource in the register is released, the master-slave switching operation is immediately performed, if the client terminal data resource is released, the data cannot be quickly recovered, the abnormal data is marked, when the subsequent data of the old master library is recovered, the data of the old master library is read again to perform data synchronization on the unsynchronized data, when the client terminal cannot provide the unsynchronized data in real time to complete the data synchronization recovery, a data access instruction is established to be stored, the data access instruction is used for accessing the data of the master library to realize the data synchronization after the shutdown recovery of the master library, the time and the mode of the data synchronization are determined through the current state of the client terminal, the technical effect of dynamically performing the data recovery is achieved, different conditions of the data synchronization process are fully considered, and the complete data synchronization is realized.

Further, when the backup data is the same as the asynchronous data, two situations may exist, none of the plurality of slave libraries performs data synchronization with the master library, or an error exists in the working process of the register, at this time, a data access instruction is established for storing, the data access instruction 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 is different from the asynchronous data, it is indicated that none of the plurality of slave libraries performs data synchronization with the master library, the asynchronous data is updated and corrected according to the backup data, the parameter resource in the register is released, if the backup data is the same as the asynchronous data, it is indicated that the register fails, the register is reported and corrected, if the backup data is different from the asynchronous data, the asynchronous data is updated and corrected according to the backup data, the data is quickly synchronized by performing data synchronization between the slave libraries, and the master library and the slave library are switched after correction. The backup data corresponding to the other slave library time points T are called to be compared with the data at different steps, so that the data synchronization problem in the crash scene of the master library is solved, the data synchronization operation process is determined according to the relation between the backup data of the other slave libraries and the unsynchronized data, the data synchronization is completed quickly and skillfully, and the safety of the master disaster backup switching of the database is ensured.

Further, when the current state is the MGR architecture single master mode, the invention calculates the weight score W of each database node, takes the database node with the largest weight score W as a new master library, considers the data volume, processing efficiency and error rate of each database node when determining the new master library, and multiplies the data volume, processing efficiency and error rate by a corresponding preset weight coefficient, and relevant personnel can flexibly set the weight coefficient according to the system type and use scene of the used database to realize that the determined database node is the best node capable of matching the current client and system requirements, and after the determination of the new master library is completed, the old master library and the new master library are functionally set to complete the master disaster backup switching under the MGR architecture single master mode, and the master library is determined according to a preset algorithm, thereby solving the technical problem of low adaptation degree of the used system of the new master library and the database, realizing the technical effect of carrying out the master disaster backup switching on the MYSQL database according to the conditions of the used system, and realizing the personalized master disaster backup switching when the MGR architecture single master mode is performed.

Further, when the current state is the MGR architecture multi-master mode, the present invention correspondingly stores the instructions and the IP of the client that 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 instructions and the IP of the client to the other database nodes, and interacts with the client by the other database nodes, only changes the function of the downtime node into read-only, and participates in the decision and execution of the transaction by the other master nodes together, thereby realizing the fast switching of the main disaster recovery in the MGR architecture multi-master mode.

Drawings

Fig. 1 is a flowchart of a main disaster recovery switching method for a MYSQL database according to an embodiment of the present invention;

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

Detailed Description

In order that the objects and advantages of the invention will be more clearly understood, the invention is further described below with reference to examples; it should be understood that the specific embodiments described herein are merely illustrative of the invention and are not intended to limit 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 only for explaining the technical principles of the present invention, and do not limit the scope of the present invention.

It should be noted that the terms "comprises" and "comprising," and any variations thereof, in the description and claims of the present invention and the above-described drawings, 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, but may include other steps or elements not expressly listed or inherent to such process, method, article, or apparatus.

Please refer to fig. 1, which is a flowchart of a main disaster recovery switching method of a MYSQL database according to an embodiment of the present invention, the main disaster recovery switching method of the MYSQL database includes:

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

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

s103: when the current state is the MYSQL master-slave architecture, a first switching mode is adopted to carry out master disaster recovery backup switching;

s104: when the current state is the single-master mode of the MGR framework, a second switching mode is adopted to carry out master disaster backup switching;

s105: and when the current state is the MGR architecture multi-master mode, a third switching mode is adopted to carry out master disaster recovery switching.

Optionally, the obtaining of the current state of the MYSQL database may be performed in the following manner: the main disaster recovery switching instruction sent by the client to the MYSQL database may include two parts, namely an address code and an operation code, wherein the address code stores an IP address of the client sending the instruction, and the operation code may include the main disaster recovery switching instruction and a framework mode of a 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 may be obtained by sending a main disaster recovery switching instruction to the MYSQL database by the client, where the instruction may include an address code for storing an IP address of the client and an operation code for conveying the main disaster recovery switching instruction, sending a framework mode query message to the client after receiving the main disaster recovery switching instruction, reading a framework mode message sent again by the client, and obtaining the current state of the database through two interactions with the client.

The method comprises the steps of monitoring a main disaster backup switching instruction of a MYSQL database, responding to the monitored instruction, obtaining a framework mode currently applied by a client using the database, determining a personalized main disaster backup switching mode for a system according to three different scenes when the system where the client is located is in a MYSQL main-slave architecture, a MGR architecture single-main mode and a MGR architecture multi-main mode, and performing main disaster backup switching by adopting a first switching mode when the current state is the MYSQL main-slave architecture; when the current state is the single-master mode of the MGR framework, a second switching mode is adopted to carry out master disaster backup switching; when the current state is the multi-main mode of the MGR framework, the main disaster recovery and backup switching is carried out by adopting the third switching mode, so that the main disaster recovery and backup switching is carried out by adopting different modes aiming at different framework mode scenes, the method can be applied to disaster recovery drilling and switching of the database, is also suitable for the scene that the main database is down in the using 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 manual processing of the main disaster backup switching of the database in the prior art.

Specifically, the main disaster recovery switching is performed by a first switching mode, which includes:

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

if M1= M2, the data of the master library and the slave library are synchronized, correspondingly storing the instruction and 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 IP of the client to the new master library, and interacting by the new master library and the client;

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 the register may be preferentially subjected to fault notification and data recovery is attempted.

Specifically, a register is provided in each slave bank for storing parameters related to data synchronization information. When the data synchronization is carried out between the master library and the slave library or at least two times of handshaking is carried out, before the client sends data to the master library, the client 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 a receiving approval instruction to the client, at the moment, a register in the slave library sets a first confirmation parameter M1 from 0 to 1, the client receives the receiving approval instruction and then carries out data transmission to the master library, after the data reception of the master library is finished, a receipt receiving completion instruction is replied to the client, at the moment, the slave library starts data synchronization operation with the master library, and when the data synchronization between the slave library and the master library is finished, a register in the slave library sets a second confirmation parameter M2 from 0 to 1. Optionally, the register stores the first validation parameter M1 and the second validation parameter M2 in correspondence according to a time sequence.

The invention sets a register for storing the data synchronization condition between the master library and the slave library, when the slave library receives the data pre-write information sent by the master library, the register sets the first confirmation parameter M1 to 1, when the slave library receives the data write success information sent by the master library, the register sets the second confirmation parameter M2 to 1, 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 are correspondingly equal, the data of the master library and the slave library 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, the unprocessed tasks received by the old master library are delivered to the new master library for continuous processing, when the first confirmation parameter M1 and the second confirmation parameter M2 are correspondingly unequal, the slave library and the master library data are synchronized or the register have a fault, and the time of the unsynchronized data is further analyzed. Whether the data pre-sending information and the data writing completion information received by the slave library exist correspondingly or not is detected, whether data synchronization of the master library and the slave library is completed or not is accurately judged, the data synchronization current situation of the master library and the slave library is rapidly obtained before the master disaster backup switching, data loss in the master disaster backup switching process is effectively avoided, the data integrity of the master disaster backup switching during the MYSQL master-slave architecture is guaranteed, and automatic switching is achieved.

Specifically, according to the relationship between the time point T and a first preset time point T1 and a second preset time point T2, analyzing and processing the data at different steps includes:

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

When the master-slave library is detected to be not synchronized, the different-step 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, when the unsynchronized data is recent data, the probability that the unsynchronized data can be called is high, whether the different-step data needs to be called in the instruction of the current client is further judged, when the unsynchronized data is historical data, the data synchronization condition is judged according to the data of other slave libraries, when the unsynchronized data is long-term data, the data is subjected to an unsynchronized phenomenon in time and does not influence the client within a period of time, so that the parameter resources in the register are released, the master-slave library is directly switched, different subsequent operation modes are realized according to different time periods where the unsynchronized data are located, the normal operation of the client is ensured, the data loss is reduced to the maximum extent, and the master-slave stock switching cost of the database is also considered, so that the automatic master-slave stock switching of the MYSQL architecture is realized.

Specifically, the analysis processing according to the judgment result includes:

The method comprises the steps of judging whether asynchronous data need to be called in a current instruction of a client side when the recent data in a master-slave library has unsynchronized data, if so, acquiring the client side connected at a time point T, initiating a data recovery request to the client side connected at the time point T, if not, carrying out abnormal marking on the asynchronous data, if the client side needs to call the asynchronous data, indicating that the asynchronous data has higher probability of being used recently, initiating a data recovery request to the client side according to the record of the client side connected with the master library once so as to carry out data synchronization rapidly, if not, indicating that the probability of calling the data recently by the client side is lower, therefore, carrying out abnormal marking on the asynchronous data, carrying out data synchronization request again until the host library crashes and repairs down, carrying out master disaster backup switching to maintain the sequence of normal operation of the client side, judging whether the data is urgent according to the utilization rate of the unsynchronized data, determining the time and the mode for carrying out data synchronization repairing according to the urgent degree of the unsynchronized data, and realizing balance in the normal use of the client side and the master disaster backup switching cost.

Specifically, the analysis processing is performed according to the request result, and the analysis processing includes:

if the data recovery is successful, parameter resources in the register are released, instructions and IP of the client which are not processed by the current master library are correspondingly stored, the connection between the master library and the client is cut off, the function of the master library is changed into read-only, the slave library is used as a new master library, the function of the new master library is changed into read-write, the instructions and IP of the client are sent to the new master library, and the new master library and the client interact;

if the data recovery fails, carrying out exception marking on the different synchronous data, establishing a data access instruction for storage, wherein the data access instruction is used for accessing data corresponding to a time point T in a master library when the master library is recovered, updating and correcting the different synchronous data according to the data corresponding to the time point T in the master library, then correspondingly storing an instruction and an IP of a client which is not processed by the master library at present, 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 carrying out interaction between the new master library and the client.

Furthermore, the invention initiates a data recovery request to the connected client at the time point T, if the client facilitates data call, the data corresponding to the time point T can be sent to the slave library, the slave library data is successfully recovered, the parameter resource in the register is released, the master-slave switching operation is immediately carried out, if the client data resource is released, the data cannot be rapidly recovered, the data out of synchronization is marked abnormally, when the subsequent data of the old master library is recovered, the data of the old master library is read again to carry out data synchronization on the data out of synchronization, when the client cannot provide the data out of synchronization in real time to complete data synchronization repair, a data access instruction is established to be stored, the data access instruction is used for accessing the data of the master library to realize data synchronization after the shutdown recovery of the master library, the time and the mode of data synchronization are determined through the current state of the client, the technical effect of dynamically carrying out data recovery is achieved, different conditions of the data synchronization process are fully considered, and complete data synchronization is realized.

Specifically, the analyzing and processing according to the relationship between the backup data and the different-step data includes:

if the backup data is the same as the asynchronous data, establishing a data access instruction for storage, wherein the data access instruction is used for accessing data corresponding to a time point T in a master library when the master library is recovered, updating and correcting the different asynchronous data according to the data corresponding to the time point T in the master library, then correspondingly storing an instruction and an IP (Internet protocol) of a client which is not processed by the master library at present, 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 carrying out interaction by the new master library and the client;

if the backup data is different from the asynchronous data, updating and correcting the asynchronous data according to the backup data, releasing parameter resources in a register, correspondingly storing instructions and IP (Internet protocol) of a client which is 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 instructions and IP of the client to the new master library, and interacting the new master library and the client.

When the backup data is the same as the asynchronous data, two conditions may exist, a plurality of slave libraries are not synchronized with the master library, or an error exists in the working process of the register, a data access instruction is established for storing, the data access instruction 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 is different from the asynchronous data, the plurality of slave libraries are not synchronized with the master library, the asynchronous data is updated and corrected according to the backup data, parameter resources in the register are released, if the backup data is the same as the asynchronous data, the register is failed, the register is repaired, if the backup data is different from the asynchronous data, the asynchronous data is updated and corrected according to the backup data, the data synchronization between the slave libraries is performed, the data can be quickly synchronized, and the master library and the slave library are switched after the data is corrected. The backup data corresponding to the other slave library time points T are called to be compared with the data at different steps, so that the data synchronization problem in the crash scene of the master library is solved, the data synchronization operation process is determined according to the relation between the backup data of the other slave libraries and the unsynchronized data, the data synchronization is completed quickly and skillfully, and the safety of the master disaster backup switching of the database is ensured.

Specifically, the main disaster recovery switching is performed by adopting a second switching mode, which includes:

the weight fraction W = α d _i ×βf _i ×γe _i Wherein, in the step (A),

d is the data volume, D _i Amount of data for ith database node, e _i To preset the treatment efficiency, f _i =(1-F _i )/D _i ，F _i The number of errors of the ith database node is alpha, beta and gamma are weight coefficients;

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

When the current state is the MGR framework single-master mode, the weight fraction W of each database node is calculated, the database node with the largest weight fraction W is used as a new master library, the data volume, the processing efficiency and the error rate of each database node are considered when the new master library is determined, and the data volume, the processing efficiency and the error rate are multiplied by corresponding preset weight coefficients, related personnel can flexibly set the weight coefficients according to the system type and the use scene of the used database, so that the determined database node is the best node capable of matching the current client and the system requirements, after the determination of the new master library is completed, the old master library and the new master library are subjected to function setting, the master disaster backup switching under the MGR framework single-master mode is completed, the master library is determined according to a preset algorithm, the technical problem that the use system adaptability of the new master library and the database is not high is solved, the technical effect of carrying out master disaster backup switching on the MYSQL database according to the conditions inclined to use the system is achieved, and the master disaster backup switching is individualized switching under the MGR framework single-master disaster backup switching.

Specifically, the performing of the main disaster recovery handover by using the third handover method includes:

the method comprises the steps of correspondingly storing an instruction and an IP of a client which are not processed by a current database node, cutting off the connection between the database node and the client, changing the function of the database node into read-only, sending the instruction and the IP of the client to other database nodes, and carrying out interaction between the other database nodes and the client.

When the current state is the MGR architecture multi-master 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.

Fig. 2 is a schematic structural diagram of a main disaster recovery switching device of a MYSQL database according to an embodiment of the present invention, where the main disaster recovery switching device of the MYSQL database includes a state monitoring module 201 and a state monitoring module 201;

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

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

the mode determining module 202 is configured to determine a main 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 the main disaster recovery backup by adopting a first switching mode when the current state is MYSQL master-slave architecture; the second switching unit is used for performing main disaster recovery and backup switching by adopting a second switching mode when the current state is the single main mode of the MGR framework; the third switching unit is used for performing main disaster recovery and backup switching by adopting a third switching mode when the current state is in a multi-main mode of the MGR architecture;

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 acknowledgement parameter M1 and the second acknowledgement parameter M2 are 0, when the slave bank receives data pre-write information sent by a master bank, the register sets the first acknowledgement parameter M1 to 1, and when the slave bank receives data write success information sent by the master bank, the register sets the second acknowledgement parameter M2 to 1;

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

Analyzing and processing the data at different steps according to the relation between the time point T and a first preset time point T1 and a second preset time point T2, wherein the steps comprise:

And analyzing and processing according to the judgment result, comprising:

And performing analysis processing according to the request result, wherein the analysis processing comprises the following steps:

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

The second switching unit comprises a master library determination component; the master library determining component is used for performing master disaster recovery switching by adopting a second switching mode, and comprises:

the weight fraction W = α d _i ×βf _i ×γe _i Wherein, in the step (A),

the method comprises the steps of correspondingly storing instructions and IP of a client which is not processed by a current master library, cutting off the connection between the master library and the client, changing the function of the master library into read-only, changing the function of a new master library into read-write, sending the instructions and IP of the client to the new master library, and interacting between the new master library and the client.

The third switching unit includes a database switching component, and the database switching component is configured to perform main disaster recovery and backup switching by using a second switching manner, and includes:

An embodiment of the present invention further provides an electronic device, including:

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 enabled to execute various functional applications and data processing, for example, the method for master disaster recovery switching of a MYSQL database provided by the above embodiment of the present invention is implemented.

From the above description of the embodiments, it is obvious for those skilled in the art that the present invention can be implemented by software and necessary general hardware, and certainly, can also be implemented by hardware, but the former is a better embodiment in many cases. Based on such understanding, the technical solutions of the present invention may be embodied 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 (RAM), a FLASH Memory (FLASH), a hard disk or an optical disk of a computer, and includes several instructions for enabling a computer device (which may be a personal computer, a server, or a network device) to execute the methods according to the embodiments of the present invention.

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

It is to be noted that the foregoing is only illustrative of the preferred embodiments of the present invention and the technical principles employed. 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, although the present invention has been described in greater detail by the above embodiments, the present invention is not limited to the above embodiments, and may include other equivalent embodiments without departing from the spirit of the present invention, and the scope of the present invention is determined by the scope of the appended claims.

The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention; various modifications and alterations to this invention will become apparent to those skilled in the art. Any modification, equivalent replacement, or improvement made within the spirit and principle of the present invention should be included in the protection scope of the present invention.

Claims

1. A main disaster recovery switching method of a MYSQL database is characterized by comprising the following steps:

and when the current state is the multi-master mode of the MGR framework, a third switching mode is adopted to carry out master disaster recovery and backup switching.

2. The main disaster recovery switching method of the MYSQL database according to claim 1, wherein the performing main disaster recovery switching by using the first switching method comprises:

3. The major disaster recovery switching method of MYSQL database according to claim 2, characterized in that a root

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

4. The main disaster recovery switching method of the MYSQL database according to claim 3, wherein performing analysis processing according to the determination result comprises:

5. The major disaster recovery switching method of MYSQL database according to claim 4, wherein the analysis processing according to the request result includes:

6. The main disaster recovery switching method for the MYSQL database according to claim 3, wherein performing analysis processing according to the relationship between the backup data and the different step data comprises:

7. The main disaster recovery switching method of the MYSQL database according to claim 1, wherein the main disaster recovery switching is performed by a second switching method, which includes:

the weight fraction W = α d _i ×βf _i ×γe _i Wherein, in the step (A),

d is the data volume, D _i Amount of data for ith database node, e _i To preset the treatment efficiency, f _i =(1-F _i )/D _i ，F _i The error times of the ith database node are alpha, beta and gamma, which are weight coefficients; />

8. The main disaster recovery switching method of the MYSQL database according to claim 1, wherein the main disaster recovery switching is performed by a third switching method, which includes:

9. A main disaster recovery switching device of MYSQL database is characterized by comprising:

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

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 the main disaster recovery backup by adopting a first switching mode when the current state is MYSQL master-slave architecture; the second switching unit is used for performing main disaster recovery and backup switching by adopting a second switching mode when the current state is the single main mode of the MGR framework; and the third switching unit is used for performing main disaster recovery switching by adopting a third switching mode when the current state is the multi-main mode of the MGR architecture.

10. An electronic device, comprising:

one or more processors;

a memory for storing one or more programs;

when executed by the one or more processors, the one or more programs cause the one or more processors to implement the main disaster recovery switching method of MYSQL database according to any of claims 1-8.