Embodiment
It should be appreciated that the specific embodiments described herein are merely illustrative of the present invention, it is not used to limit this
Invention.
As shown in figure 1, first embodiment of the invention proposes a kind of master/slave data storehouse switching method, including:
Step S10, performs connection native database service operations.
The executive agent of the inventive method can be a kind of database management platform or database server, specifically should
It will be specifically described for primary database or standby database in following description, other energy are also not limited to certainly
Enough realize the equipment or server of master/slave data storehouse switching.
Wherein, the present invention is main ensures that database master/slave data is same by using MariaDB and binlog daily records
Step process can be monitored, it is ensured that quick automatic switching when database is abnormal.
Wherein, can be in master/slave data place in its upper side administration database broker software when implementing
DBAgent (or other can realize the software of identical function), DBAgent are a kind of database broker software,
It can be used for monitoring data storehouse service operation situation, parse binlog journal files, can be used when breaking down
In switching control process;Wherein, can be by the relevant action performed in MariaDB database runnings
It recorded in binlog journal files, in case reading synchrodata from database, MariaDB databases were performed
Cheng Zhong, when producing binlog journal files, accordingly providing binlog positions, i.e. current log position, (length is whole
Shape value).
Specifically, the DBAgent timings monitoring native database running situation and current binlog in master/slave data storehouse
Newest GTID in daily record, meanwhile, standby machine database running situation is shared between master/slave data storehouse mutually.
When native database is primary database, when being standby database to client database:
The DBAgent timings of primary database connect primary database by MYSQL agreements with specific user
Serve port, inquires about and obtains native database current location (current gtid_current_pos), and remembered
Record is updated, and the DBAgent for being sent to standby database by heartbeat message interface is recorded.
When native database is for database, when being main database to client database:
The DBAgent timings of standby database connect standby database by MYSQL agreements with specific user
Serve port, inquires about and obtains native database current location (current gtid_current_pos), and remembered
Record is updated, and the DBAgent for being sent to primary database by heartbeat message interface is recorded.
Step S20, is received to client database transmission to client database current location.
Specifically, when native database is primary database, when being standby database to client database:
The DBAgent of primary database receives the heartbeat message that the DBAgent of standby database is sent, the standby number of record
According to the current gtid_current_pos positions in storehouse.
When native database is for database, when being main database to client database:
The DBAgent of standby database receives the heartbeat message that the DBAgent of primary database is sent, and records main number
According to the current gtid_current_pos positions in storehouse.
Step S30, if the frequency of failure of the connection native database service operations exceedes default number of times,
Judge that the native database is abnormal, and perform alarm operation or in the native database and described to end data
Active-standby switch operation is carried out between storehouse, or, if described receive to the current to client database of client database transmission
The frequency of failure of position exceedes default number of times, then performs alarm operation or in the native database and described right
Active-standby switch operation is carried out between client database.
Specifically, when native database is for database, when being main database to client database:
If the DBAgent connection database services of standby database unsuccessfully exceed stipulated number (such as 10 times, this
Number of times can system pre-set, can also be modified by user according to actual use situation), judge
Current primary database is abnormal, continues executing with connecting detection operation.
When native database is primary database, when being standby database to client database:
If the DBAgent connection database services of standby database unsuccessfully exceed stipulated number (such as 10 times, this
Number of times can system pre-set, can also be modified by user according to actual use situation), judge
Current primary database is abnormal, parses the journal file of primary database, obtains the rearmost position of primary database, and root
Active-standby switch operation is performed according to rearmost position, when implementing, following manner can be taken:
1st, by way of parsing database binlog journal files, primary database is obtained last
Gtid_current_pos positions, are recorded as last_pos positions;
2nd, the DBAgent of primary database sends handover request message to the DBAgent of standby database, the message
Include the last_pos positions of primary database;
3rd, whether the DBAgent continuation timing Test database services of primary database are normal, until database clothes
Business recovers;
4th, the last_pos positions of primary databases of the DBAgent of primary database in handover request, are initiated
Active-standby switch flow, wherein:
If the rearmost position of primary database is not sky, rearmost position and the standby database for contrasting primary database are current
Position, wherein, if standby database current location is equal to the rearmost position of primary database, perform behaviour of automatically switching
Make, if standby database current location is less than the rearmost position of primary database, whether held according to business need decision-making
Row automatically switching operation;
If the rearmost position of primary database is sky, the current location of primary database and standby database present bit are contrasted
Put, wherein, if standby database current location is more than or equal to the current location of primary database, execution is cut automatically
Operation is changed, otherwise, then whether automatically switching operation is performed according to business need decision-making.
Above-mentioned automatically switching operation is specially:The standby database of switching is primary database, and extensive in standby database service
After multiple, recover active and standby replication relation again.
Or, when host data base is primary database, when being standby database to client database:
If the DBAgent of primary database is sent beyond the DBAgent that stipulated number does not receive primary database
Heartbeat message, then perform alarm alarm operation.
When host data base is for database, when being main database to client database:
If the DBAgent of standby database is sent beyond the DBAgent that stipulated number does not receive primary database
Heartbeat message, then pass through MYSQL agreements and connect primary database serve port and obtain primary database current location
Gtid_current_pos, if obtaining the DBAgent process operation exceptions for successfully illustrating primary database, database
Service is normal, without active-standby switch is initiated, only need to alert and record correlation log, if obtaining failure, explanation
Primary database DBAgent processes and database service are abnormal, can now initiate active-standby switch request.
In addition, as shown in Fig. 2 for leader follower replication monitoring management paralell composition, being described in detail below:
Primary database perform DDL sentences or comprising update SQL affairs when, for the affairs distribute an overall situation
The GTID numberings of unique order, before affairs are submitted write-in binary system binlog journal files be synchronized to simultaneously from
Database, the binlog log information synchrodatas of primary database are read from database, reply execution corresponding
SQL statement, so as to reach and primary database data syn-chronization;
Database broker software DBAgent periodic monitor native database service operation situations, pass through MYSQL
Whether the operation of agreement connected reference native database monitoring data storehouse is normal, while it is current to obtain native database
The timing of gtid_current_pos positions sends heartbeat message and gives opposite end DBAgent, DBAgent to receive opposite end
The gtid_current_pos positions of new record opposite end database are solved after heartbeat message, native database and opposite end is compared
The gtid_current_pos positions of database, the data syn-chronization situation of master-slave database is judged according to difference, works as master
Exceed setting range from database gtid_current_pos gaps and carry out the related measures such as Real-time Alarm.
Further, DBAgent transmissions in opposite end are not received in database broker software DBAgent certain times
Heartbeat message after, actively by MYSQL agreement peer end of the connection database services, judge to client database take
Whether business is normal, while obtaining the gtid_current_pos positions to client database, judges if connection failure
It is abnormal to client database, whether active-standby switch is initiated according to principal and subordinate role's decision-making of database.
By such scheme, the invention provides a kind of master/slave data storehouse switching method, this method can be supervised in real time
Principal and subordinate's data syn-chronization gap situation is grasped in control, and system is understood indirectly writes transaction load situation of change;Eliminate
Tripartite's management assembly, direct hedge whole system unavailable problem caused by third party's management assembly failure;Letter
Installation and deployment are also simplify while changing processing logic, DBAgent can carry out software binding, each number with DB
It is unified easy according to storehouse node deployment;Quick identification standby machine database data is inconsistent, can be automatic when data are consistent
Active-standby switch is carried out, and builds new leader follower replication relation.Data can automate generation differentiation SQL when inconsistent
Script, is that manual intervention is saved the time, and height ensures data consistency;It is different in DBAgent processes itself
Regular data storehouse is serviced under normal circumstances, it is to avoid unnecessary mistake switching.
As shown in figure 3, second embodiment of the invention proposes a kind of master/slave data storehouse switching method, including:
Step S11, performs connection native database service operations, timing acquisition native database current location.
Specifically, the DBAgent timings monitoring native database running situation and current binlog in master/slave data storehouse
Newest GTID in daily record, meanwhile, standby machine database running situation is shared between master/slave data storehouse mutually.
When native database is primary database, when being standby database to client database:
The DBAgent timings of primary database connect primary database by MYSQL agreements with specific user
Serve port, inquires about and obtains native database current location (current gtid_current_pos), and remembered
Record is updated, and the DBAgent for being sent to standby database by heartbeat message interface is recorded.
When native database is for database, when being main database to client database:
The DBAgent timings of standby database connect standby database by MYSQL agreements with specific user
Serve port, inquires about and obtains native database current location (current gtid_current_pos), and remembered
Record is updated, and the DBAgent for being sent to primary database by heartbeat message interface is recorded.
Step S21, is received to client database transmission to client database current location, by the native database
Client database current location is compared with described for current location.
Specifically, when native database is primary database, when being standby database to client database:
The DBAgent of primary database receives the heartbeat message that the DBAgent of standby database is sent, the standby number of record
According to the current gtid_current_pos positions in storehouse, the current gtid_current_pos positions of primary database are compared and standby
The current gtid_current_pos positions of database.
When native database is for database, when being main database to client database:
The DBAgent of standby database receives the heartbeat message that the DBAgent of primary database is sent, and records main number
According to the current gtid_current_pos positions in storehouse, the current gtid_current_pos positions of primary database are compared and standby
The current gtid_current_pos positions of database.
Step S40, when the native database current location and the difference to client database current location are big
When default threshold value, alarm operation is performed.
Specifically, when the current gtid_current_pos positions of primary database and for the current of database
Gtid_current_pos positions difference be more than configuration threshold value when (threshold value can system pre-set,
Can be modified by user according to actual use situation), corresponding measure is taken, is such as alerted.
By such scheme, the invention provides a kind of master/slave data storehouse switching method, this method can be supervised in real time
Principal and subordinate's data syn-chronization gap situation is grasped in control, and system is understood indirectly writes transaction load situation of change;Eliminate
Tripartite's management assembly, direct hedge whole system unavailable problem caused by third party's management assembly failure;Letter
Installation and deployment are also simplify while changing processing logic, DBAgent can carry out software binding, each number with DB
It is unified easy according to storehouse node deployment;Quick identification standby machine database data is inconsistent, can be automatic when data are consistent
Active-standby switch is carried out, and builds new leader follower replication relation.Data can automate generation differentiation SQL when inconsistent
Script, is that manual intervention is saved the time, and height ensures data consistency;It is different in DBAgent processes itself
Regular data storehouse is serviced under normal circumstances, it is to avoid unnecessary mistake switching.
Further, in order to better ensure that the uniformity of reserve database switching evidence, reference picture 4 is
In the specific embodiment of the invention, if the frequency of failure of the connection native database service operations is more than default
Number of times, then judge that the native database is abnormal, and performs alarm operation or in the native database and described
A kind of schematic flow sheet of the step of to carrying out active-standby switch operation between client database.
As a kind of embodiment, above-mentioned steps S30 includes:
Step S31, if the native database is standby database, described is main database to client database, then
Alarm operation is performed, and continues executing with connecting detection operation.
Specifically, when native database is for database, when being main database to client database:
If the DBAgent connection database services of standby database unsuccessfully exceed stipulated number (such as 10 times, this
Number of times can system pre-set, can also be modified by user according to actual use situation), judge
Current primary database is abnormal, continues executing with connecting detection operation.
Step S32, if the native database is primary database, described is standby database to client database, then
The journal file of the native database is parsed, the rearmost position of the native database is obtained, and according to described
Rearmost position performs active-standby switch operation.
Specifically, when native database is primary database, when being standby database to client database:
If the DBAgent connection database services of standby database unsuccessfully exceed stipulated number (such as 10 times, this
Number of times can system pre-set, can also be modified by user according to actual use situation), judge
Current primary database is abnormal, parses the journal file of primary database, obtains the rearmost position of primary database, and root
Active-standby switch operation is performed according to rearmost position, when implementing, following manner can be taken:
1st, by way of parsing database binlog journal files, primary database is obtained last
Gtid_current_pos positions, are recorded as last_pos positions;
2nd, the DBAgent of primary database sends handover request message to the DBAgent of standby database, the message
Include the last_pos positions of primary database;
3rd, whether the DBAgent continuation timing Test database services of primary database are normal, until database clothes
Business recovers;
4th, the last_pos positions of primary databases of the DBAgent of primary database in handover request, are initiated
Active-standby switch flow, wherein:
If the rearmost position of primary database is not sky, rearmost position and the standby database for contrasting primary database are current
Position, wherein, if standby database current location is equal to the rearmost position of primary database, perform behaviour of automatically switching
Make, if standby database current location is less than the rearmost position of primary database, whether held according to business need decision-making
Row automatically switching operation;
If the rearmost position of primary database is sky, the current location of primary database and standby database present bit are contrasted
Put, wherein, if standby database current location is more than or equal to the current location of primary database, execution is cut automatically
Operation is changed, otherwise, then whether automatically switching operation is performed according to business need decision-making.
By such scheme, the invention provides a kind of master/slave data storehouse switching method, this method passes through in the machine
When database is abnormal, the Different treatments that master/slave data storehouse is performed have been better achieved real-time monitoring and have grasped master
Standby database synchronization gap situation, simplifies operation, it is ensured that data consistency, while avoid switching by mistake.
Further, in order to better ensure that the uniformity of reserve database switching evidence, reference picture 5 is
In the specific embodiment of the invention, a kind of schematic flow sheet for the step of active-standby switch is operated is performed.
As a kind of embodiment, above-mentioned steps S32 includes:
Step S321, if the rearmost position of the primary database is not sky, contrasts the primary database most
Position and the standby database current location afterwards, wherein, if the standby database current location is equal to the main number
According to the rearmost position in storehouse, then automatically switching operation is performed, if the standby database current location is less than the main number
According to the rearmost position in storehouse, then whether the automatically switching operation is performed according to business need decision-making.
Specifically, if the last_pos positions of primary database are not sky, the last GTID of primary database is compared
Whether position last_pos is consistent with the current gtid_current_pos of standby database, if standby database
Gtid_current_pos then carries out automatically switching operation equal to primary database last_pos, if standby database
Gtid_current_pos is less than primary database last_pos and illustrates the data not yet Complete Synchronization of primary database to main number
Whether, according to storehouse, master/slave data storehouse data are inconsistent, at this point it is possible to be needed to automatically switch according to business need decision-making
And record the log information of correlation.
Wherein, according to MariaDB replicating principle, it is impossible to standby database gtid_current_pos occur big
In primary database last_pos situation.
Step S322, if the rearmost position of the primary database is sky, contrasts the current of the primary database
Position and the standby database current location, wherein, if the standby database current location is more than or equal to described
The current location of primary database, then perform the automatically switching operation, otherwise, then be according to business need decision-making
It is no to perform the automatically switching operation.
Specifically, if primary database last_pos positions are sky, the current GTID positions of primary database are compared
Gtid_current_pos is put whether consistent with the current gtid_current_pos of standby database, if standby database
More than or equal to primary database gtid_current_pos positions, then automatically switching operation is carried out, otherwise, then explanation is led
The data of database not yet Complete Synchronization is to standby database, and master/slave data storehouse data are inconsistent, at this point it is possible to root
Whether need to automatically switch and record the log information of correlation according to business need decision-making.
By such scheme, the invention provides a kind of master/slave data storehouse switching method, this method passes through according to not
Corresponding active-standby switch operation is performed with situation, real-time monitoring has been better achieved and has grasped master/slave data storehouse synchronization discrepancy
Away from situation, simplify operation, it is ensured that data consistency, while avoid switching by mistake.
Further, in order to better ensure that the uniformity of reserve database switching evidence, reference picture 6 is
In the specific embodiment of the invention, a kind of schematic flow sheet for the step of performing automatically switching operation.
As a kind of embodiment, above-mentioned steps S32 includes:
Step S323, it is primary database to switch the standby database, and after the standby database service recovers,
Again active and standby replication relation is recovered.
Specifically, the standby database of DBAgent switchings is primary database, and main former database is set into standby database,
After standby database service recovers, DBAgent recovers master/slave data storehouse replication relation again;When master/slave data storehouse
When data are inconsistent, then need to parse the binlog journal files of former primary database, from standby database
The SQL statement that all GTID after being parsed at gtid_current_pos positions are related to, generation forward direction SQL
After sentence, make it that former master/slave data storehouse data recovery is consistent after former standby database supplement is finished.
By such scheme, the invention provides a kind of master/slave data storehouse switching method, this method is by performing certainly
Dynamic handover operation, has been better achieved real-time monitoring and has grasped master/slave data storehouse synchronization discrepancy away from situation, simplified operation,
Ensure data consistency, while avoid switching by mistake.
Further, in order to better ensure that the uniformity of reserve database switching evidence, reference picture 7 is
In the specific embodiment of the invention, a kind of flow signal for the step of obtaining the rearmost position of the native database
Figure.
As a kind of embodiment, above-mentioned steps S32 includes:
Step S324, obtains the current log file list of the native database.
Specifically, when host data base services exception, DBAgent is by calling mysqlbinlog instruments to obtain
Take the GTID positions last_pos that database is last.
Wherein, the current binlog of index file acquisitions under database binlog binary log files is passed through
Listed files.
Step S325, is scanned since last journal file in the current log file list, and
The rearmost position of the native database is obtained from last event of last journal file.
Specifically, scanned since last binlog journal file, this binlog file is found in scanning
Last gtid event (GTID_LOG_EVENT), obtains last gtid as last_pos.
Step S326, if last described event can not be found from last described journal file, sweeps
A upper journal file for last journal file is retouched, until finding last described event.
Specifically, if current binlog does not find gtid events, i.e., no affairs submit record at this
In binlog files, then be scanned for a binlog file, move in circles, until finding last gtid.
Further, the method for specified gtid_current_pos positions is searched with above-mentioned by binlog journal files
Search last_pos methods.
In addition, as shown in figure 8, master/slave data storehouse corresponding binlog front and rear for the operation of MariaDB active-standby switch
Iog location change situation.
Wherein, PosA is the gtid_current_pos in the normal heartbeat message that former primary database last time is reported
Position;Last_pos be former primary database last write affairs and be actually written into binlog binary logs most
Big GTID positions;PosB is last affairs that former standby host database replicates binlog daily records from primary database
Corresponding GTID positions;When both Last_pos, PosB coincidence are equal, master/slave data is completely the same, can
It is automatic to carry out active-standby switch;When Last_pos unknown (physical machine such as power down suspension abnormal conditions), PosB is more than
Whether master/slave data is consistent unknown during equal to PosA;Now carry out after active-standby switch, treat that original host recovers abnormal
Need to reaffirm Last_pos positions and PosB relation afterwards, it is active and standby if Last_pos is more than PosB
Variantization binlog between machine, otherwise standby machine data are consistent;When the unknown (things such as power down suspension of Last_pos
Reason machine abnormal conditions), when PosB is less than PosA, former standby host data substantially fall behind original host, standby machine data
It is inconsistent.
By such scheme, the invention provides a kind of master/slave data storehouse switching method, it has been better achieved in real time
Monitoring grasps master/slave data storehouse synchronization discrepancy away from situation, simplifies operation, it is ensured that data consistency, while avoid cutting by mistake
Change.
Based on the realization of above method embodiment, the present invention also provides corresponding device embodiment.
As shown in figure 9, first embodiment of the invention proposes a kind of master/slave data storehouse switching device, including:
Link block 100, for performing connection native database service operations.
Wherein, the present invention is main ensures that database master/slave data is same by using MariaDB and binlog daily records
Step process can be monitored, it is ensured that quick automatic switching when database is abnormal.
Wherein, can be in master/slave data place in its upper side administration database broker software when implementing
DBAgent (or other can realize the software of identical function), DBAgent are a kind of database broker software,
It can be used for monitoring data storehouse service operation situation, parse binlog journal files, can be used when breaking down
In switching control process;Wherein, can be by the relevant action performed in MariaDB database runnings
It recorded in binlog journal files, in case reading synchrodata from database, MariaDB databases were performed
Cheng Zhong, when producing binlog journal files, accordingly providing binlog positions, i.e. current log position, (length is whole
Shape value).
Specifically, the DBAgent timings monitoring native database running situation and current binlog in master/slave data storehouse
Newest GTID in daily record, meanwhile, standby machine database running situation is shared between master/slave data storehouse mutually.
When native database is primary database, when being standby database to client database:
The DBAgent timings of primary database connect primary database by MYSQL agreements with specific user
Serve port, inquires about and obtains native database current location (current gtid_current_pos), and remembered
Record is updated, and the DBAgent for being sent to standby database by heartbeat message interface is recorded.
When native database is for database, when being main database to client database:
The DBAgent timings of standby database connect standby database by MYSQL agreements with specific user
Serve port, inquires about and obtains native database current location (current gtid_current_pos), and remembered
Record is updated, and the DBAgent for being sent to primary database by heartbeat message interface is recorded.
Position module 200, for receiving to client database transmission to client database current location.
Specifically, when native database is primary database, when being standby database to client database:
The DBAgent of primary database receives the heartbeat message that the DBAgent of standby database is sent, the standby number of record
According to the current gtid_current_pos positions in storehouse.
When native database is for database, when being main database to client database:
The DBAgent of standby database receives the heartbeat message that the DBAgent of primary database is sent, and records main number
According to the current gtid_current_pos positions in storehouse.
Performing module 300, if the frequency of failure for the connection native database service operations is more than default
Number of times, then judge that the native database is abnormal, and performs alarm operation or in the native database and described
To carrying out active-standby switch operation between client database, or, if described receive the opposite end number sent to client database
Exceed default number of times according to the frequency of failure of storehouse current location, then perform alarm operation or in the native database
And it is described to carrying out active-standby switch operation between client database.
Specifically, when native database is for database, when being main database to client database:
If the DBAgent connection database services of standby database unsuccessfully exceed stipulated number (such as 10 times, this
Number of times can system pre-set, can also be modified by user according to actual use situation), judge
Current primary database is abnormal, continues executing with connecting detection operation.
When native database is primary database, when being standby database to client database:
If the DBAgent connection database services of standby database unsuccessfully exceed stipulated number (such as 10 times, this
Number of times can system pre-set, can also be modified by user according to actual use situation), judge
Current primary database is abnormal, parses the journal file of primary database, obtains the rearmost position of primary database, and root
Active-standby switch operation is performed according to rearmost position, when implementing, following manner can be taken:
1st, by way of parsing database binlog journal files, primary database is obtained last
Gtid_current_pos positions, are recorded as last_pos positions;
2nd, the DBAgent of primary database sends handover request message to the DBAgent of standby database, the message
Include the last_pos positions of primary database;
3rd, whether the DBAgent continuation timing Test database services of primary database are normal, until database clothes
Business recovers;
4th, the last_pos positions of primary databases of the DBAgent of primary database in handover request, are initiated
Active-standby switch flow, wherein:
If the rearmost position of primary database is not sky, rearmost position and the standby database for contrasting primary database are current
Position, wherein, if standby database current location is equal to the rearmost position of primary database, perform behaviour of automatically switching
Make, if standby database current location is less than the rearmost position of primary database, whether held according to business need decision-making
Row automatically switching operation;
If the rearmost position of primary database is sky, the current location of primary database and standby database present bit are contrasted
Put, wherein, if standby database current location is more than or equal to the current location of primary database, execution is cut automatically
Operation is changed, otherwise, then whether automatically switching operation is performed according to business need decision-making.
Above-mentioned automatically switching operation is specially:The standby database of switching is primary database, and extensive in standby database service
After multiple, recover active and standby replication relation again.
Or, when host data base is primary database, when being standby database to client database:
If the DBAgent of primary database is sent beyond the DBAgent that stipulated number does not receive primary database
Heartbeat message, then perform alarm alarm operation.
When host data base is for database, when being main database to client database:
If the DBAgent of standby database is sent beyond the DBAgent that stipulated number does not receive primary database
Heartbeat message, then pass through MYSQL agreements and connect primary database serve port and obtain primary database current location
Gtid_current_pos, if obtaining the DBAgent process operation exceptions for successfully illustrating primary database, database
Service is normal, without active-standby switch is initiated, only need to alert and record correlation log, if obtaining failure, explanation
Primary database DBAgent processes and database service are abnormal, can now initiate active-standby switch request.
In addition, as shown in Fig. 2 for leader follower replication monitoring management paralell composition, being described in detail below:
Primary database perform DDL sentences or comprising update SQL affairs when, for the affairs distribute an overall situation
The GTID numberings of unique order, before affairs are submitted write-in binary system binlog journal files be synchronized to simultaneously from
Database, the binlog log information synchrodatas of primary database are read from database, reply execution corresponding
SQL statement, so as to reach and primary database data syn-chronization;
Database broker software DBAgent periodic monitor native database service operation situations, pass through MYSQL
Whether the operation of agreement connected reference native database monitoring data storehouse is normal, while it is current to obtain native database
The timing of gtid_current_pos positions sends heartbeat message and gives opposite end DBAgent, DBAgent to receive opposite end
The gtid_current_pos positions of new record opposite end database are solved after heartbeat message, native database and opposite end is compared
The gtid_current_pos positions of database, the data syn-chronization situation of master-slave database is judged according to difference, works as master
Exceed setting range from database gtid_current_pos gaps and carry out the related measures such as Real-time Alarm.
Further, DBAgent transmissions in opposite end are not received in database broker software DBAgent certain times
Heartbeat message after, actively by MYSQL agreement peer end of the connection database services, judge to client database take
Whether business is normal, while obtaining the gtid_current_pos positions to client database, judges if connection failure
It is abnormal to client database, whether active-standby switch is initiated according to principal and subordinate role's decision-making of database.
By such scheme, the invention provides a kind of master/slave data storehouse switching device, this method can be supervised in real time
Principal and subordinate's data syn-chronization gap situation is grasped in control, and system is understood indirectly writes transaction load situation of change;Eliminate
Tripartite's management assembly, direct hedge whole system unavailable problem caused by third party's management assembly failure;Letter
Installation and deployment are also simplify while changing processing logic, DBAgent can carry out software binding, each number with DB
It is unified easy according to storehouse node deployment;Quick identification standby machine database data is inconsistent, can be automatic when data are consistent
Active-standby switch is carried out, and builds new leader follower replication relation.Data can automate generation differentiation SQL when inconsistent
Script, is that manual intervention is saved the time, and height ensures data consistency;It is different in DBAgent processes itself
Regular data storehouse is serviced under normal circumstances, it is to avoid unnecessary mistake switching.
As shown in Figure 10, second embodiment of the invention proposes a kind of master/slave data storehouse switching device, wherein:
Above-mentioned link block 100, is additionally operable to perform connection native database service operations, timing acquisition the machine number
According to storehouse current location.
Specifically, the DBAgent timings monitoring native database running situation and current binlog in master/slave data storehouse
Newest GTID in daily record, meanwhile, standby machine database running situation is shared between master/slave data storehouse mutually.
When native database is primary database, when being standby database to client database:
The DBAgent timings of primary database connect primary database by MYSQL agreements with specific user
Serve port, inquires about and obtains native database current location (current gtid_current_pos), and remembered
Record is updated, and the DBAgent for being sent to standby database by heartbeat message interface is recorded.
When native database is for database, when being main database to client database:
The DBAgent timings of standby database connect standby database by MYSQL agreements with specific user
Serve port, inquires about and obtains native database current location (current gtid_current_pos), and remembered
Record is updated, and the DBAgent for being sent to primary database by heartbeat message interface is recorded.
Above-mentioned position module 200, is additionally operable to receive to client database transmission to client database current location, will
Client database current location is compared with described for the native database current location.
Specifically, when native database is primary database, when being standby database to client database:
The DBAgent of primary database receives the heartbeat message that the DBAgent of standby database is sent, the standby number of record
According to the current gtid_current_pos positions in storehouse, the current gtid_current_pos positions of primary database are compared and standby
The current gtid_current_pos positions of database.
When native database is for database, when being main database to client database:
The DBAgent of standby database receives the heartbeat message that the DBAgent of primary database is sent, and records main number
According to the current gtid_current_pos positions in storehouse, the current gtid_current_pos positions of primary database are compared and standby
The current gtid_current_pos positions of database.
The device also includes:
Alarm module 400, for when the native database current location with it is described to client database current location
Difference be more than default threshold value when, perform alarm operation.
Specifically, when the current gtid_current_pos positions of primary database and for the current of database
Gtid_current_pos positions difference be more than configuration threshold value when (threshold value can system pre-set,
Can be modified by user according to actual use situation), corresponding measure is taken, is such as alerted.
By such scheme, the invention provides a kind of master/slave data storehouse switching device, this method can be supervised in real time
Principal and subordinate's data syn-chronization gap situation is grasped in control, and system is understood indirectly writes transaction load situation of change;Eliminate
Tripartite's management assembly, direct hedge whole system unavailable problem caused by third party's management assembly failure;Letter
Installation and deployment are also simplify while changing processing logic, DBAgent can carry out software binding, each number with DB
It is unified easy according to storehouse node deployment;Quick identification standby machine database data is inconsistent, can be automatic when data are consistent
Active-standby switch is carried out, and builds new leader follower replication relation.Data can automate generation differentiation SQL when inconsistent
Script, is that manual intervention is saved the time, and height ensures data consistency;It is different in DBAgent processes itself
Regular data storehouse is serviced under normal circumstances, it is to avoid unnecessary mistake switching.
Further, in order to better ensure that reserve database switching evidence uniformity, above-mentioned performing module
300, if it is standby database to be additionally operable to the native database, described is main database to client database, then holds
Row alarm operation, and continue executing with connecting detection operation;If the native database is primary database, described right
Client database is standby database, then parses the journal file of the native database, obtains the native database
Rearmost position, and according to the rearmost position perform active-standby switch operation.
Specifically, when native database is for database, when being main database to client database:
If the DBAgent connection database services of standby database unsuccessfully exceed stipulated number (such as 10 times, this
Number of times can system pre-set, can also be modified by user according to actual use situation), judge
Current primary database is abnormal, continues executing with connecting detection operation.
Then, when native database is primary database, when being standby database to client database:
If the DBAgent connection database services of standby database unsuccessfully exceed stipulated number (such as 10 times, this
Number of times can system pre-set, can also be modified by user according to actual use situation), judge
Current primary database is abnormal, parses the journal file of primary database, obtains the rearmost position of primary database, and root
Active-standby switch operation is performed according to rearmost position, when implementing, following manner can be taken:
1st, by way of parsing database binlog journal files, primary database is obtained last
Gtid_current_pos positions, are recorded as last_pos positions;
2nd, the DBAgent of primary database sends handover request message to the DBAgent of standby database, the message
Include the last_pos positions of primary database;
3rd, whether the DBAgent continuation timing Test database services of primary database are normal, until database clothes
Business recovers;
4th, the last_pos positions of primary databases of the DBAgent of primary database in handover request, are initiated
Active-standby switch flow, wherein:
If the rearmost position of primary database is not sky, rearmost position and the standby database for contrasting primary database are current
Position, wherein, if standby database current location is equal to the rearmost position of primary database, perform behaviour of automatically switching
Make, if standby database current location is less than the rearmost position of primary database, whether held according to business need decision-making
Row automatically switching operation;
If the rearmost position of primary database is sky, the current location of primary database and standby database present bit are contrasted
Put, wherein, if standby database current location is more than or equal to the current location of primary database, execution is cut automatically
Operation is changed, otherwise, then whether automatically switching operation is performed according to business need decision-making.
By such scheme, the invention provides a kind of master/slave data storehouse switching device, this method passes through in the machine
When database is abnormal, the Different treatments that master/slave data storehouse is performed have been better achieved real-time monitoring and have grasped master
Standby database synchronization gap situation, simplifies operation, it is ensured that data consistency, while avoid switching by mistake.
Further, in order to better ensure that reserve database switching evidence uniformity, above-mentioned performing module
300, if the rearmost position for being additionally operable to the primary database is not sky, contrast the last position of the primary database
Put with described for database current location, wherein, if the standby database current location is equal to the primary database
Rearmost position, then automatically switching operation is performed, if the standby database current location is less than the primary database
Rearmost position, then whether the automatically switching operation is performed according to business need decision-making;If the primary database
Rearmost position be sky, then contrast the current location of the primary database and the standby database current location, its
In, if the standby database current location is more than or equal to the current location of the primary database, perform described
Whether automatically switching operation, otherwise, then perform the automatically switching operation according to business need decision-making.
Specifically, if the last_pos positions of primary database are not sky, the last GTID of primary database is compared
Whether position last_pos is consistent with the current gtid_current_pos of standby database, if standby database
Gtid_current_pos then carries out automatically switching operation equal to primary database last_pos, if standby database
Gtid_current_pos is less than primary database last_pos and illustrates the data not yet Complete Synchronization of primary database to main number
Whether, according to storehouse, master/slave data storehouse data are inconsistent, at this point it is possible to be needed to automatically switch according to business need decision-making
And record the log information of correlation.
Wherein, according to MariaDB replicating principle, it is impossible to standby database gtid_current_pos occur big
In primary database last_pos situation.
Then, if primary database last_pos positions are sky, the current GTID positions of primary database are compared
Whether gtid_current_pos is consistent with the current gtid_current_pos of standby database, if standby database is more than
Equal to primary database gtid_current_pos positions, then automatically switching operation is carried out, otherwise, then illustrate master data
The data in storehouse not yet Complete Synchronization is to standby database, and master/slave data storehouse data are inconsistent, at this point it is possible to according to industry
Business requires whether decision-making needs to automatically switch and record the log information of correlation.
By such scheme, the invention provides a kind of master/slave data storehouse switching device, this method passes through according to not
Corresponding active-standby switch operation is performed with situation, real-time monitoring has been better achieved and has grasped master/slave data storehouse synchronization discrepancy
Away from situation, simplify operation, it is ensured that data consistency, while avoid switching by mistake.
Further, in order to better ensure that reserve database switching evidence uniformity, above-mentioned performing module
300, it is primary database to be additionally operable to the switching standby database, and after the standby database service recovers, weight
Newly recover active and standby replication relation.
Specifically, the standby database of DBAgent switchings is primary database, and main former database is set into standby database,
After standby database service recovers, DBAgent recovers master/slave data storehouse replication relation again;When master/slave data storehouse
When data are inconsistent, then need to parse the binlog journal files of former primary database, from standby database
The SQL statement that all GTID after being parsed at gtid_current_pos positions are related to, generation forward direction SQL
After sentence, make it that former master/slave data storehouse data recovery is consistent after former standby database supplement is finished.
By such scheme, the invention provides a kind of master/slave data storehouse switching device, this method is by performing certainly
Dynamic handover operation, has been better achieved real-time monitoring and has grasped master/slave data storehouse synchronization discrepancy away from situation, simplified operation,
Ensure data consistency, while avoid switching by mistake.
Further, in order to better ensure that reserve database switching evidence uniformity, above-mentioned performing module
300, it is additionally operable to obtain the current log file list of the native database;From current log file row
Last journal file in table starts scanning, and from last event of last journal file
The middle rearmost position for obtaining the native database;If described in can not being found from last described journal file most
Latter event, then scan a upper journal file for last journal file, until find it is described most
Latter event.
Specifically, when host data base services exception, DBAgent is by calling mysqlbinlog instruments to obtain
Take the GTID positions last_pos that database is last.
Wherein, the current binlog of index file acquisitions under database binlog binary log files is passed through
Listed files.
Then, scanned since last binlog journal file, scanning finds this binlog file most
Latter gtid event (GTID_LOG_EVENT), obtains last gtid as last_pos.
Then, if current binlog does not find gtid events, i.e., no affairs submit record in this binlog
In file, then be scanned for a binlog file, move in circles, until finding last gtid.
Further, the method for specified gtid_current_pos positions is searched with above-mentioned by binlog journal files
Search last_pos methods.
In addition, as shown in figure 8, master/slave data storehouse corresponding binlog front and rear for the operation of MariaDB active-standby switch
Iog location change situation.
Wherein, PosA is the gtid_current_pos in the normal heartbeat message that former primary database last time is reported
Position;Last_pos be former primary database last write affairs and be actually written into binlog binary logs most
Big GTID positions;PosB is last affairs that former standby host database replicates binlog daily records from primary database
Corresponding GTID positions;When both Last_pos, PosB coincidence are equal, master/slave data is completely the same, can
It is automatic to carry out active-standby switch;When Last_pos unknown (physical machine such as power down suspension abnormal conditions), PosB is more than
Whether master/slave data is consistent unknown during equal to PosA;Now carry out after active-standby switch, treat that original host recovers abnormal
Need to reaffirm Last_pos positions and PosB relation afterwards, it is active and standby if Last_pos is more than PosB
Variantization binlog between machine, otherwise standby machine data are consistent;When the unknown (things such as power down suspension of Last_pos
Reason machine abnormal conditions), when PosB is less than PosA, former standby host data substantially fall behind original host, standby machine data
It is inconsistent.
By such scheme, the invention provides a kind of master/slave data storehouse switching device, it has been better achieved in real time
Monitoring grasps master/slave data storehouse synchronization discrepancy away from situation, simplifies operation, it is ensured that data consistency, while avoid cutting by mistake
Change.
The preferred embodiments of the present invention are these are only, are not intended to limit the scope of the invention, it is every to utilize
Equivalent structure or equivalent flow conversion that description of the invention and accompanying drawing content are made, or be directly or indirectly used in
Other related technical fields, are included within the scope of the present invention.