CN104657382A

CN104657382A - Method and device for detecting consistency of data of MySQL master and slave servers

Info

Publication number: CN104657382A
Application number: CN201310593859.3A
Authority: CN
Inventors: 林晓斌
Original assignee: Alibaba Group Holding Ltd
Current assignee: Alibaba Group Holding Ltd
Priority date: 2013-11-21
Filing date: 2013-11-21
Publication date: 2015-05-27
Anticipated expiration: 2033-11-21
Also published as: CN104657382B

Abstract

The invention discloses a method for detecting consistency of data of MySQL master and slave servers. The method comprises the steps of setting default storage engines of the master and slave servers to be InnoDB; performing special database operation in the master sever, wherein the operation is to be written into a binlog file of the master server; determining whether the current database operation to be written into the binlog file is the special database operation by the master server while writing into the binlog file; if so, exporting the master server data; determining whether the currently-performed database operation is the special database operation while reading and implementing content of the binlog file written in the master server through a sever; if so, exporting the slave server; determining whether the exported master server data are the same as the exported master slave data; if so, determining that the exported master server data are consistent to the exported master slave data. The invention further provides a device for detecting consistency of data of the MySQL master and slave servers. With the adoption of the method and device, whether the consistency of the master and slave servers data can be accurately detected at a time.

Description

For the method and apparatus of MySQL principal and subordinate server data consistency detection

Technical field

The application relates to principal and subordinate's server data consistency detection field, is specifically related to a kind of method of the principal and subordinate's server data consistency detection for MySQL; The application provides a kind of device for MySQL principal and subordinate server data consistency detection simultaneously.

Background technology

MySQL is a Relational DBMS, by saving the data in different tables, instead of all data being placed in a large warehouse, accelerating data access speed and improve dirigibility.In order to realize the load balancing of server, and strengthening the ability that Database Systems resist disaster, MySQL database system, the Database Systems popular with other are the same, also use the framework mode of master-slave synchronisation, that is: system comprises two-server: master server and from server.The object of above-mentioned framework mode is adopted to have following two aspects.First, the renewal rewards theory of data only can be realized on master server, and the inquiry request of data is all sent to from server and perform, perform on a different server by Data Update and inquiry are put respectively, the response time to user operation can be shortened, improve the performance of system.Secondly, when master server breaks down, database manipulation request can be directly switch to and continue to provide service from server, the stable operation of customer service can be ensured.Obviously, in order to realize above-mentioned two objects, inevitable requirement is consistent from the data of server and the data of master server.Particularly in financial class business, require that master and slave server data must keep completely the same, otherwise may bring the loss being difficult to make up to user.

For reaching the on all four object of above-mentioned master and slave server data, MySQL utilizes binary log binlog to realize.Its implementation is specific as follows: the Data Update action performed writes in binary log binlog by master server, and binary log binlog is sent to from server, receive binary log binlog from server and apply, that is: by the Data Update action on master server, performing once from server, thus reaching the consistent of master and slave server data; This process is commonly referred to as MySQL leader follower replication process.But in concrete enforcement, because binlog is a kind of logic log, record be the logical operation of database, therefore may exist because the original state of master and slave server is inconsistent, be employed the inconsistent problem of master and slave server data that the reasons such as renewal cause from server data.

For these reasons, in MySQL leader follower replication process, usually need to carry out principal and subordinate's consistency detection to some important database and/or table.The main method that MySQL system carries out principal and subordinate's consistency detection is at present: inquire about on master and slave server respectively, is read out line by line by the record needing to carry out in the database of consistency detection and/or table and compares.The method is simple and easy to do, but due to following reason, whether the data of master and slave server are consistent to cause the method accurately to judge:

First, because the data of master and slave server exist certain delay when copying, data, before copying and terminating completely, are in constantly change and and under incomplete state, the mode therefore directly reading master and slave server data cannot the consistance of strict guarantee acquisition data always.

Secondly, because master and slave server dynamically updates, cannot ensure that the logical time point of initiating the inquiry request of consistency detection from master and slave two servers is strictly identical, that is: it is seen that master and slave server phase view in the same time when cannot ensure to initiate inquiry request on master and slave server.Because the logical time point of initiating inquiry request is different, even may causes state consistent for master and slave server data, be mistaken for inconsistent.

For the defect of above-mentioned principal and subordinate's consistency detecting method, existing improvement project adopts the mode of repeatedly inquiring about, if the data of that is inquiring about the master and slave server obtained for the first time are inconsistent, then again initiate second time inquiry request respectively for master and slave server, and judge that whether Query Result is consistent.Existing improvement project wishes to reduce erroneous judgement by repeatedly retry, but the same existing defects of this way:

The first, the possibility that there is erroneous judgement cannot fundamentally be avoided.Because principal and subordinate is in dynamic updating process, if master and slave server busy (such as: the situation that update request amount is large), second time inquiry is still likely judged by accident, even occurs repeatedly judging by accident.

The second, repeatedly on master and slave server, perform query manipulation, server can be caused to occur meaningless pressure, thus affect the response speed of server to customer service.

Summary of the invention

The application provides a kind of method for MySQL principal and subordinate server data consistency detection, and the method can detect that whether principal and subordinate's server data is consistent disposable, exactly.

The application provides a kind of device for MySQL principal and subordinate server data consistency detection simultaneously.

The application provides a kind of method for MySQL principal and subordinate server data consistency detection, comprises the steps:

Master server is set and is InnoDB storage engines from the default storage engine of server;

Master server performs specific database manipulation, and described specific database manipulation will be written in the binary log binlog file of master server;

When master server performs and writes binary log binlog file, judge whether the current database operation that will write in described binary log binlog file is described specific database manipulation; If so, master server data are derived;

Read the content of the binary log binlog file that above-mentioned master server is write from server, and when performing database manipulation wherein, judge whether the database manipulation of current execution is described specific database manipulation; If so, derive from server data;

Judge the master server data of derivation and the whether identical from server data of derivation; If identical, the data consistent of principal and subordinate's server is described.

Optionally, described specific database manipulation, comprising: creation database or table, Update Table storehouse or table, delete database or table, in table, insert record, record from table in deletion record or updating form.

Optionally, described on master server, perform the step of specific database manipulation before, perform following steps:

The table of a newly-built use Blackhole black hole engine on master server;

Accordingly, describedly on master server, perform specific database manipulation refer to, in the table of above-mentioned use Blackhole black hole engine, insert record.

Master server is specifically shown for newly-built one, and comprising a type in this table is the field of character string;

Accordingly, the described step performing specific database manipulation on master server refers to, in described specific table, insert a record, is that in the field of character string, write operates relevant indication information with derived data in the type of this record;

Accordingly, in described derivation master server data and described derivation from before the step of server data, following step is performed:

Read in the record inserted in described specific table, operate relevant indication information with derived data;

Accordingly, in described derivation master server data and described derivation from the step of server data, operate relevant indication information according to above-mentioned with derived data, perform the operation of derived data in the mode corresponding to this indication information.

Optionally, describedly operate relevant indication information with derived data and refer to destination path and file destination name;

Accordingly, in described derivation master server data and described derivation from the step of server data, the described operation performing derived data in the mode corresponding to this indication information, refers to the write of the data of derivation with in the data file of described file destination name name and under being stored in the catalogue that described destination path specifies.

Optionally, describedly operate relevant indication information with derived data and refer to that database-name and/or table name claim;

Accordingly, in described derivation master server data and described derivation from the step of server data, the described operation performing derived data in the mode corresponding to this indication information, refers to that derivation claims consistent database and/or table with described database-name and/or table name.

Optionally, describedly operate relevant indication information with derived data and refer to that condition describes;

Accordingly, in described derivation master server data and described derivation from the step of server data, the described operation performing derived data in the mode corresponding to this indication information, refers to qualified data in derived data storehouse and/or table.

Optionally, described specific table is the table using Blackhole black hole engine.

Optionally, perform on master server if described in the step of specific database manipulation, extra change is introduced to master and slave server data, in described derivation after the step of server data, master server performs the operation recovering raw data.

Optionally, when performing the step of specific database manipulation described in performing on master server, following step is first performed:

Whether the statistical conversion state judging current master and slave server is " deriving "; If so, the operation of this principal and subordinate's server data consistency detection is not performed; If not, the statistical conversion state of master and slave server is set for " unlatching of statistical conversion function ", and continues the step performing specific database manipulation described in execution on master server;

Accordingly, when performing described derivation master server data and derive the step from server data, following step is first performed: the statistical conversion state of master and slave server is set for " deriving ";

Accordingly, in described derivation master server data with derive from after the step of server data, the statistical conversion state of master and slave server is set immediately for " unlatching of statistical conversion function ".

The application provides a kind of device for MySQL principal and subordinate server data consistency detection simultaneously, comprising:

Setting unit, for arranging master server and being InnoDB storage engines from the default storage engine of server;

Operation execution unit, for performing specific database manipulation on master server, described specific database manipulation will be written in the binary log binlog file of master server;

Master server judging unit, when writing binary log binlog file for performing on master server, judges whether the current database operation that will write in described binary log binlog file is described specific database manipulation;

Master server lead-out unit, when the judged result of described master server judging unit is, the current database operation that write in described binary log binlog file is described specific database manipulation, then this unit starting, for deriving master server data;

From server judging unit, in the content reading the binary log binlog file that above-mentioned master server is write from server, and when performing database manipulation wherein, judge whether the database manipulation of current execution is described specific database manipulation;

From server lead-out unit, when the described judged result from server judging unit is, the database manipulation of current execution is described specific database manipulation, then this unit starting, for deriving from server data;

Data determining unit, whether identical with the data derived from server lead-out unit for judging the data that master server lead-out unit is derived, and export judged result.

Optionally, described device also comprises:

Black hole table creating unit, for the table of a use Blackhole black hole engine newly-built on master server;

Accordingly, described operation execution unit performs specific database manipulation and refers on master server, shows to insert record in the table of the newly-built use Blackhole black hole engine of creating unit to described black hole.

Optionally, described device also comprises:

Table creating unit, for a specific table newly-built on master server, comprising a type in this table is the field of character string;

Accordingly, described operation execution unit performs specific database manipulation and refers on master server, in the specific table that described table creating unit is newly-built, insert a record, be that in the field of character string, write operates relevant indication information with derived data in the type of this record;

Accordingly, described master server lead-out unit comprises:

Master Server Info reads subelement, for reading in the record that inserts in described specific table, operates relevant indication information with derived data;

Master server statistical conversion perform subelement, for receive described Master Server Info read subelement export operate relevant indication information with derived data, perform the operation of deriving master server data in the mode corresponding to this indication information;

Accordingly, describedly to comprise from server lead-out unit:

Reading subelement from server info, for reading in the record that inserts in described specific table, operating relevant indication information with derived data;

Derive from server data and perform subelement, for receive described read that subelement exports from server info operate relevant indication information with derived data, perform the operation of deriving from server data in the mode corresponding to this indication information.

Optionally, described device also comprises:

Date restoring judging unit, for judging whether the specific database manipulation that described operation execution unit performs introduces extra change to master and slave server data;

Data recovery unit, if the judged result of described date restoring judging unit is, the specific database manipulation that described operation execution unit performs introduces extra change to master and slave server data, then this unit starting, this unit for performing the operation recovering raw data on master server.

Optionally, described device also comprises:

Derive condition adjudgement unit, for judging whether the statistical conversion state of current master and slave server is " deriving ";

State set unit, when the judged result of described derivation condition adjudgement unit is, when the current statistical conversion state of master and slave server is not " deriving ", this subelement starts, for arranging the statistical conversion state of master and slave server for " unlatching of statistical conversion function ";

Accordingly, described operation execution unit, when the judged result of described derivation condition adjudgement unit is, when the current statistical conversion state of master and slave server is not " deriving ", this unit starting, for performing specific database manipulation;

Accordingly, described master server lead-out unit also comprises:

Master server first state arranges subelement, before starting to perform the operation of derivation master server data, arranges the statistical conversion state of master server for " deriving " for performing subelement at described master server statistical conversion;

Master server second state arranges subelement, after completing the operation of deriving master server data at described master server statistical conversion execution subelement, arranges the statistical conversion state of master server for " unlatching of statistical conversion function ";

Accordingly, describedly also to comprise from server lead-out unit:

From server first state, subelement is set, for described to derive the operation performing and to perform subelement and derive from server data from server data before, arrange from the statistical conversion state of server be " deriving ";

From server second state, subelement is set, for performing subelement and complete described to derive from server data and derive from after the operation of server data, arrange from the statistical conversion state of server be " unlatching of statistical conversion function ".

Compared with prior art, the application has the following advantages:

Use the application provide for MySQL principal and subordinate server data consistency detecting method, MySQL master server performs specific database manipulation, this specific database manipulation will be written in binary log binlog, master server write binary log binlog and from server application binary daily record binlog time, described specific database manipulation will be detected, as unified logical time point, and perform statistical conversion operation, thus fundamentally solving the problem of consistance view, that is: ensure leading, the inquiry initiated from server is it is seen that identical view.Adopt the method that the application provides, on the master and slave server of MySQL, only perform a statistical conversion operation respectively and the data derived are compared, just accurately can judge that whether principal and subordinate's server data is consistent, there is not the possibility of erroneous judgement, do not need to perform repeatedly retry.

In a kind of preferred implementation of the application, when performing specific database manipulation, have employed to using the mode inserting record in the table of Blackhole black hole storage engines.Due to the characteristic of black hole engine self, all data insertings all can not go on record, but corresponding operation can write in binary log binlog file, therefore in the table using black hole engine, perform the operation of data inserting record, extra data can not be introduced, even if repeat repeatedly, also can not increase the data record in table, the operation of follow-up Resume service device raw data can be simplified.

In the another kind of preferred implementation of the application, when performing specific database manipulation, the write indication information relevant to derived data in the record inserted, comprising the storehouse of: the destination path of specifying derived data to deposit and file destination name, needs derivation and/or the title of table and the restrictive condition of derived data.By insert record in specify derived data to deposit destination path and file destination name, deposit position and the file name of master and slave server derived data can be specified dynamic flexible, not be used on master and slave server and specify respectively, thus the step that simplifies the operation; By specifying the title needing database and/or the table of deriving and the condition of specifying derived data demand fulfillment in the record inserted, realize each part that only derives and need the data of carrying out consistency detection, effectively can reduce statistical conversion and operate the impact that server is brought, reduce the impact on server performance.

Accompanying drawing explanation

The process flow diagram of the method for MySQL principal and subordinate server data consistency detection that Fig. 1 provides for the application first embodiment;

The unit block diagram of the device for MySQL principal and subordinate server data consistency detection that Fig. 2 provides for the application second embodiment.

Embodiment

Set forth a lot of detail in the following description so that fully understand the application.But the application can be much different from alternate manner described here to implement, those skilled in the art can when doing similar popularization without prejudice to when the application's intension, and therefore the application is by the restriction of following public concrete enforcement.

As shown in Figure 1, Fig. 1 is the embodiment process flow diagram of a kind of method for MySQL principal and subordinate server data consistency detection that the application provides, and is described below in conjunction with Fig. 1.

Step S101: master server is set and is InnoDB storage engines from the default storage engine of server.

The various different technology of data in MySQL is stored in file (or internal memory).Each technology in these technology all uses different memory mechanisms, index skill, locking level and finally provide different functions and ability.By selecting different technology, the allomeric function of database application can be improved.These different technology and supporting correlation function are referred to as storage engines in MySQL.MySQL supports several data storage engines, such as: MyISAM, InnoDB, BDB, Memory etc., wherein, some storage engines do not support issued transaction, such as: MyISAM, needing under the application scenario supporting affairs, InnoDB is the database engine of MySQL first-selection.

The greatest feature of InnoDB supports ACID transaction functionality exactly.The logical execution units that affairs are made up of a step or a few step data storehouses sequence of operation, the sequence of operations in this logical execution units or all perform, or all abandon performing.The affairs of database are made up of following statement: one group of DML(data manipulation language (DML)) statement, such as: insert record insert, more new record update, deletion record delete etc.; A DDL(data definition language (DDL)) statement, such as: creation database or table create, amendment table alter, delete database or table drop etc.; A DCL(data control language (DCL)) statement, such as: authorize grant, submit commit, rollback rollback etc. to.The all database comprised when office operates all after successful execution, should submit affairs to, these amendments are forever come into force.

Affairs have four characteristics: atomicity, consistance, isolation and continuation, and these four characteristics are also called for short ACID.Wherein, isolation refers to, the execution of each affairs does not interfere with each other, the affairs that the built-in function of any one affairs is concurrent to other, is all isolation.That is: the intermediateness can not seeing the other side between the affairs of concurrence performance, can not influence each other between the affairs of concurrence performance, it is with being intended to the consistance ensureing data read-write operation.SQL92 defines four kinds of isolation levels: do not submit to read, submit to read, repeatable read (Read repeatable) and serial read.

Most of relational database acquiescence uses the isolation level submitted to and read, MySQL InnoDB gives tacit consent to the isolation level using repeatable read (Read repeatable), terminate to affairs for read operation read lock, the renewal rewards theory of other affairs can only wait until that these affairs are carried out after terminating.Support the isolation level of repeatable read, mean that InnoDB can use its multi version to turn to the snapshot of the database at certain particular point in time place of inquiry displaying, this inquiry can see the change that those offices submitted before particular point in time do, the change can't see affairs thereafter or do not submit to office to make.That is: if adopt the repeatable read isolation level of acquiescence, all read operations then in same affairs, read the snapshot established by first read operation in these affairs, want to obtain fresher snapshot, then need to submit Current transaction to and start a new inquiry transaction realizing.

A kind of method for MySQL principal and subordinate server data consistency detection that the application provides, needs to utilize above-mentioned repeatable read characteristic.But other engines existing do not support repeatable read characteristic in MySQL, first therefore master server will be set and be InnoDB storage engines from the default storage engine of server.In each version of MySQL database system, default storage engine is not identical, and earlier version acquiescence uses MyISAM data storage engines, and after MySQL5.5, version then adopts InnoDB storage engines by default.

Therefore, the default storage engine that the MySQL database system first should inquiring about current use adopts.In the present embodiment, in the order line of master and slave server, input " show engines respectively; " order, by the list of database engine all available in display current system, the respective value that wherein " Support " arranges is the database engine of " Default " is exactly current default storage engine.If default storage engine is InnoDB, then need not perform extra setting operation, otherwise performing the default storage engine that step as follows arranges current system is InnoDB engine:

1) MySQL service is closed;

2) my.ini file is revised;

Enable default-storage-engine=INNODB statement;

Shielding skip-innodb statement, that is: add line annotation symbol (#) before this statement;

3) MySQL service is restarted after preserving my.ini file.

Step S102: the table of a newly-built use Blackhole black hole engine on master server, and this table comprises the field of character string type.

Blackhole black hole engine is the one in numerous data storage engines of MySQL support, the characteristic of Blackhole storage engines, just as its name WD, it is a data " black hole ", all can disappear to using any data inserted in the table of Blackhole engine, can not be stored, if but enable binary log binlog, update so recited above can be written in binary log binlog.Based on these characteristics of Blackhole storage engines, this storage engines usually makees for recording binlog the relaying copied.

In the present embodiment, make use of the above-mentioned characteristic of Blackhole, the table (table also referred to as Blackhole type) of a first newly-built use Blackhole black hole engine on master server, then record is inserted wherein as specific database manipulation, even if perform repeatedly update like this, also can not introduce extra data, the step of follow-up restore database raw data can be simplified.Adopt following steps, the table of a newly-built use Blackhole storage engines on master server.

First, guarantee that server supports Blackhole black hole storage engines.

" show engines is inputted in the order line of server; " order, system can export a form, and this form shows the podilgree of available database engine and whether support these engines in current database server.If show current database server in form do not support Blackhole storage engines, usually Blackhole storage engines can be found in the server scale-of-two version of MySQL supply, and make current database server support Blackhole storage engines by the manual MySQL of recompility source code, mode as follows to be adopted when compiling to specify and to support Blackhole storage engines :-DWITH_ARCHIVE_STORAGE_ENGINE=1.

Then, master server is set up the table that uses Blackhole black hole engine, and this table comprises the field of character string type.In the present embodiment, adopt statement as follows to create a table name to be the _ table of the use Blackhole black hole engine of ms_consistent, and in this table, only have the field of the character string type of a variable-length:

create table_ms_consistent(VARCHAR(80))ENGINE=BLACKHOLE;

Then " show tables is used; " order, just can see that having established a name is called _ table of ms_consistent;

Also " show create table_ms_consistent can be used; " order, confirm that institute builds new table and uses Blackhole storage engines.

In this step, why require, in newly-built table, the field of character string type be comprised, be because follow-up in this table insert record time, need write in the field of character string type to operate relevant indication information to derived data, illustrate the explanation referred in step S103.

Step S103: inserting a record to using in the table of Blackhole black hole engine, comprising in this record and operating relevant indication information with derived data.

In the table of the use Blackhole black hole engine set up in step s 102, insert a record, this record can not be stored in the table using black hole engine, but this database manipulation will be written in the binary log binlog file of master server.Binary log binlog file is the most important daily record in MySQL, plays an important role: leader follower replication and date restoring in two of MySQL system.

First, the consistance of the master and slave server data of MySQL, by MySQL leader follower replication process implementation, and MySQL reproduction process records all renewal rewards theory to database (inserting record, more new record, deletion record etc.) based on master server in binary log binlog to realize, therefore, copy, binary log binlog function must be enabled on master server.MySQL master server is by all write of the renewal rewards theory to database binary log binlog files, the update content be recorded to its binary log binlog of master server transmission is received from server, and these update content are copied to from the local file (that is: relaying daily record) of server, and applying these renewal rewards theory from server, that is: perform identical renewal rewards theory from server, thus realize the consistance of master and slave server data.

Secondly, if enable binary log binlog function, when database meets accident, the operation can done within the time period of this file record by this Fileview user, again with DB Backup with the use of, can user operation be reproduced, database is recovered.Specifically can use mysqlbinlog instrument, according to the information recorded in binary log binlog, perform complete recovery, based on the recovery of time point and location-based recovery.

In order to realize the function of above-mentioned two aspects, require the data change event (except data query statement) that in binary log binlog, necessary database of record is all, described all data change event, comprise following two classes: DDL(data definition language (DDL)) statement event, such as: creation database or table, amendment table, delete database or table etc.; With DML(data manipulation language (DML)) statement event, such as: deletion record, more new record, insertion record etc., it should be noted that, the default data query statement do not recorded in DML in binary log binlog, if require that binary log binlog records data query statement, need to carry out extra setting.

Binary log binlog is not physics scene, it is logic log, its record be concrete operations content about each affairs, such as: on database test, a newly-built name is called the table of t1, show binlogevents is used to check, can see and have recorded content as follows in binlog, wherein contain the SQL statement of performing database operation:

1.Log_name:mysql-bin.000002

2.Pos:107

3.Event_type:Query

4.Server_id:1

5.End_log_pos:193

6.Info:use`test`;create table t1(id int)

A kind of method for MySQL principal and subordinate server data consistency detection that the application provides, its core is, master server performs specific database manipulation, and write binlog and from when server application binlog at master server, detect described specific database manipulation, and will detect that described specific database manipulation is as unified logical time point, perform statistical conversion operation.Therefore described specific database manipulation must be the database manipulation that can be recorded by binlog, namely above-mentioned DDL and DML operation (not comprising query statement).

In the present embodiment, the specific database manipulation that master server performs is, to use blackhole black hole engine _ ms_consistent table in insert a record, and the operation of inserting record is the one in DML, and be not data query statement, therefore can write in binary log binlog.In the present embodiment, specifically have employed sql command as described below and perform the operation of inserting record:

insert into_ms_consistent

values(“/tmp/dump_data;1.txt;db1;table1;where field1=1”);

In the above-mentioned record inserted in _ ms_consistent table, comprise the field of a character string type, be written with in this field and operate relevant indication information with derived data, specifically comprise three class indication informations:

/ tmp/dump_data; 1.txt: be used to specify destination path and file destination name that derived data deposits;

Db1; Table1: be used to specify the title needing database and the table of deriving;

Where field1=1: the condition being used to specify demand fulfillment during derived data.

Why in the record of insertion _ ms_consistent table, writing the character string of above-mentioned form, is to obtain more excellent implementation result; By writing the destination path and file destination name that derived data deposits in the record inserted, deposit position and the file name of master and slave server upper derivate certificate can be specified dynamic flexible, not be used on master and slave server and specify respectively, thus the step that simplifies the operation; By writing the database and the title of table and the condition of demand fulfillment that need to derive in the record inserted, realize each part that only derives and need the data of carrying out consistency detection, effectively can reduce statistical conversion and operate the impact that server is brought, thus reduce the impact on server performance.

Step S104: when master server performs and writes binary log binlog file, judges whether the current database operation that will write in described binary log binlog file is to using the operation of inserting record in the table of black hole engine; If so, operate relevant indication information according to above-mentioned with derived data, perform the operation of deriving master server data.

The method for MySQL principal and subordinate server data consistency detection that the application provides, why selecting to carry out when master server writes binlog judging and determine whether perform derivation operation, is the Data Update flow process based on MySQL and the sequential nature writing binlog.In MySQL system, a typical Data Update flow process is such:

1) an affairs A is started;

2) operation of a series of Data Update is performed, such as: insert record, more new record, deletion record etc.;

3) InnoDB prepares presentation stage, writes the redo log of InnoDB;

4) binlog is write, by information write binlog file relevant for above-mentioned Data Update operation;

5) submit to affairs A, InnoDB in redo log, write commit mark, complete real submission.

Complete the above-mentioned 5th) step, whole Data Update affairs A just calculates submission, and the Data Update operation done just really comes into force.That is, in the moment of write binlog, this affairs A does not also submit to.

In step S101 above, describe MySQL InnoDB and give tacit consent to the isolation level supporting repeatable read, mean that InnoDB can use its multi version to turn to the snapshot of the database at certain particular point in time place of inquiry displaying, this inquiry can see the change that those offices submitted before particular point in time do, the change can't see affairs thereafter or do not submit to office to make, also namely: if when affairs A does not also submit to, start an affairs B, the data that all inquiries then in affairs B are seen, it will be all the state before A submits to, even if after A submission too.

Based on above-described MySQL Data Update flow process, and the repeatable read characteristic that InnoDB supports, can find out: in the method for MySQL principal and subordinate server data consistency detection that the application provides, after affairs A starts, execute a series of renewal rewards theory comprising specific database manipulation, when master server writes binlog, if detect that the current database manipulation that will write binlog is described specific database manipulation, the derivation affairs that then startup one is new, for performing the operation of deriving master server data, the data that so all inquiries are seen in derivation affairs are all the states before affairs A submits to, also namely derive affairs it is seen that affairs A submit to before primary server database view, therefore the master server data derived also are the data before affairs A submits to.

Same reason, receiving the binlog content of master server from server and when applying, also adopting and the similar flow process of above-mentioned Data Update flow process, that is: first perform the Data Update recorded in binlog and operate, namely the renewal rewards theory in affairs A, finally just submits affairs A to.So, in the process performing the Data Update operation of recording in binlog, if the Data Update operation current execution being detected is described specific database manipulation, the derivation affairs that then startup one is new, in these derivation affairs it is seen that affairs A submit to before from server database view, therefore derive from server data be also affairs A submit to before data.

The method for MySQL principal and subordinate server data consistency detection that the application provides, make use of the sequential nature that MySQL writes binlog, and repeatable read characteristic, at master server with from server, have found unified logical time point, on this logical time point, main, from server see be all affairs A submit to before data base view, therefore on this unified logical time point, master server and the data from server are derived respectively, and compare, just can judge that whether the data of principal and subordinate's server are consistent exactly, there will not be the erroneous judgement caused because of the difference of the time point of initiation inquiry.

In the present embodiment; in step S103 to use Blackhole black hole engine _ ms_consistent table in insert a record; when binlog file is write in master server execution; judge one by one, if detect the current database manipulation that will write be insert into__ms_consistentvalues ("/tmp/dump_data; 1.txt; Db1; Table1; Where field1=1 "); SQL statement, this SQL statement of master server identification is called the _ update that performs of ms_consistent table for table name, and so master server initiates the operation of derived data.

In the present embodiment, in order to specify master flexibly, from deposit position and the filename of server upper derivate certificate, and minimizing statistical conversion operates the impact brought server, reduce the impact on server performance, when inserting record in step s 103 in _ ms_consistent table, be written with and a stringly operate relevant character string to derived data, therefore after master server detects specific database manipulation, before execution derived data, should first read current to write provide in the specific database manipulation of binlog operate relevant character string to derived data, namely the content of the current String field that will write in the above-mentioned SQL insert statement of binlog is read.

Then the character string of above-mentioned reading is resolved, therefrom extract the indication information relevant to derived data, as the title " table1 " of the table in destination path " tmp/dump_data ", file destination name " 1.txt ", the database-name " db1 " that derive, the database db1 that will derive, and the condition defined on table1 " where field1=1 ".

Finally just according to the above-mentioned indication information relevant to derived data, the operation of deriving master server data can be performed.In the present embodiment, the mode of self-defined derivation code is adopted to process, namely in a program according to the above-mentioned indication information read from character string, adopt the mode of string-concatenation, structure meets the select statement of deriving and requiring, such as, is constructed as follows shown SQL statement, by in the table table1 in the database db1 on master server, the record of satisfy condition " where field1=1 " all outputs in the file tmp/dump_data/1.txt specified:

use db1;

select*from table1 into outfile″tmp/dump_data/1.txt″where field1=1;

Step S105: the content reading the binary log binlog file that above-mentioned master server is write from server, and when performing database manipulation wherein, judge whether the database manipulation of current execution is to using the operation of inserting record in the table of black hole engine; If so, operate relevant indication information according to above-mentioned with derived data, perform the operation of deriving from server data.

MySQL uses 2 threads to carry out the task of application binlog from server.One is I/O thread, for connect master server and read master server send the binlog content containing master server latest update, and by this data Replica to from the relaying daily record of server this locality, 2nd thread is SQL thread, for reading relaying daily record and the renewal comprised in execution journal.From on server, read and perform renewal statement and be divided into two independently tasks.Although this design largely solves performance issue, shorten master and slave time delay of copying, but the master and slave this asynchronous system copied, and it is inevitable for determining delay, therefore, even if the query manipulation putting initiation at one time also may see different views.

But use the method that the application provides, as the description in step S104, when the update content of the binary log binlog from server reception master server, and when applying renewal wherein, specific database manipulation detected, because current renewal affairs are not also submitted to, therefore the derived data operation now initiated it is seen that above-mentioned renewal rewards theory do not submit to before view, therefore perform when writing corresponding binlog and specific database manipulation being detected with master server that to derive the view that affairs see identical.

In the present embodiment; the binary log binlog of master server transmission is received from server; and be stored in local relaying daily record; then the renewal rewards theory in relaying daily record is applied one by one; and judge whether the current renewal that will apply is specific database manipulation; that is: in _ ms_consistent table, insert the operation of record, when application process perform insert into__ms_consistent values ("/tmp/dump_data; Db1; Table1; Wherefield1=1 "); During statement, judge that the current renewal that will apply is the operation of inserting record in _ ms_consistent table, start to perform the operation of deriving from server data.

In derivation from before server data, also need first to read and operate relevant character string information with deriving, then according to described character string information, perform to derive and operate.Specific implementation process, identical with step S104, do not repeat them here, refer to the description in step S104.

Step S106: judge the master server data of derivation and the whether identical from server data of derivation; If identical, the data consistent of principal and subordinate's server is described.

After completing above-mentioned steps, master and slave server is derived respective database data at unified logical time point respectively, and is stored in local file.These two files that master and slave server is derived are compared, thus judges that whether the data of principal and subordinate's server are consistent.If export is not very large, can simply whether comparison document size be consistent, whether the number recorded in file is consistent, and whether the content of every bar record is identical.If export is larger, the comparing function that conventional text editor can be adopted to carry is compared, such as: the application programs such as word word-processing application or UltraEdit have Documents Comparison function.In addition, also can select special Documents Comparison instrument, such as: ding-dong Documents Comparison device or UltraCompare document management software.

Because master and slave server is the data derived at unified logical time point, do not exist because delayed from the update action of server, and cause the possibility of erroneous judgement, therefore above-mentioned comparison result just can draw the correct result whether principal and subordinate's data are consistent, if comparison result is consistent, just illustrate that master and slave server data is consistent, if comparison result is inconsistent, just illustrate that master and slave server data is inconsistent.

Step S107: the raw data recovering master and slave server.

The method for MySQL principal and subordinate server data consistency detection that the application provides, need on master server, perform specific database manipulation, based on the master and slave replicanism of MySQL, this operation also can perform from server, and therefore described specific operation may all introduce extra data to master and slave server.For the ease of maintenance customer's data, and reduce unnecessary storage space expense, after should detecting completing data consistency, recovering the raw data on master and slave server, that is: cancelling the data change introduced because carrying out data consistency detection.Such as: delete and detect and the record inserted, newly-built table etc. to carry out.

In the present embodiment, because have employed the table of Blackhole type, and record will be inserted as specific database manipulation in this table, due to the characteristic of Blackhole engine, the record that inserts in this table oriented be not all stored, therefore the operation of deletion record need not be performed, only needing to perform the such SQL statement of droptable_ms_consistent on master server, to delete newly-built _ ms_consistent table just passable, this operation also can copy to from server by binary log binlog, thus also delete from server _ ms_consistent table.

Above embodiment is only a preferred embodiment, and in fact, some steps wherein can adopt other implementation method.

Such as, in other implementations, if there is no the table of a newly-built use Blackhole engine on master server, but utilize the table of existing Blackhole type on master server, and record will be inserted in the table to this Blackhole type as specific database manipulation, so in the step of the raw data of the master and slave server of described recovery, do not need to perform any recovery operation on master server, because described specific database manipulation, does not introduce extra data to master and slave server.

Adopt the method for MySQL principal and subordinate server data consistency detection that the application provides, make use of the repeatable read characteristic of InnoDB storage engines, and it is machine-processed based on the leader follower replication of binlog, by performing specific database manipulation on MySQL master server, and master server write binary log binlog and from server application binary daily record binlog time, described specific database manipulation will be detected, as unified logical time point, and perform statistical conversion operation, thus fundamentally solving the problem of consistance view, that is: ensure leading, it is seen that identical view when performing derivation operation from server.Adopt the method that the application provides, on the master and slave server of MySQL, only perform a statistical conversion operation respectively and the data derived are compared, just accurately can judge that whether principal and subordinate's server data is consistent, there is not the possibility of erroneous judgement, do not need to perform repeatedly retry.

In the method for MySQL principal and subordinate server data consistency detection that the application provides, can a statistical conversion Status Flag be set on master and slave server respectively.Implementing before the method that provides of the application carries out MySQL principal and subordinate server data consistency detection, to arrange this Status Flag of master and slave server for " unlatching of statistical conversion function "; When performing the step deriving master server data and derive from server data, this Status Flag of master and slave server is first set for " deriving ", then performs follow-up derivation operation; In derivation master server data with derive from after the step of server data completes, this Status Flag of master and slave server of reducing immediately is " unlatching of statistical conversion function ".In specific implementation process, to the operation of this Status Flag, the self-defined mode that function interface and query function interface are set can be taked to realize.Adopt above-mentioned statistical conversion Status Flag, be convenient to be derived Cheng Jinhang monitoring to data, the master and slave server state relevant with derived data can be obtained at any time.First, before the method that enforcement the application provides, by inquiring about this Status Flag, accurately can know that master and slave server is current and whether perform statistical conversion operation, avoid at the end of a statistical conversion operation not, initiate again new statistical conversion operation, bring unnecessary pressure to master and slave server; Secondly, by inquiring about this Status Flag, can also know whether current statistical conversion operation completes in time, thus starting the operation of follow-up comparison derived data as early as possible.

In the present embodiment, adopt and insert record as described specific database manipulation to using in the table of black hole engine, be to obtain more excellent implementation result: reduce the excessive data introduced to master and slave server, and can specify dynamically, neatly and operate relevant indication information to derived data.In other embodiments, other can be adopted can to write database manipulation in master server binlog as specific database manipulation, such as: creation database or table, Update Table storehouse or table, delete database or table, in table, insert record, record etc. from table in deletion record or updating form; Also can not adopt the table of described use black hole engine, and adopt original table on master server, or adopt the table using other storage engines; Operate relevant indication information to derived data, can not provide in the record in insertion table, but fixing write performs in the code of deriving operation, also can the total data on master and slave server all be exported in data file by without stint; The change of these embodiments above-mentioned, do not affect the core for MySQL principal and subordinate server data consistency detecting method that the application provides, therefore still can realize the method that the application provides, and obtain same beneficial effect: can detect that whether MySQL principal and subordinate server data is consistent disposable, exactly.

In the above-described embodiment, provide a kind of method for MySQL principal and subordinate server data consistency detection, correspond, the application also provides a kind of device for MySQL principal and subordinate server data consistency detection.Because device embodiment is substantially similar to embodiment of the method, so describe fairly simple, relevant part is see the explanation of the corresponding part of embodiment of the method.The device embodiment of following description is only schematic.

Please refer to Fig. 2, this figure is the unit block diagram of the device for MySQL principal and subordinate server data consistency detection that the application second embodiment provides.

As shown in Figure 2, the device for MySQL principal and subordinate server data consistency detection that the present embodiment provides comprises: setting unit 201, black hole table creating unit 202, operation execution unit 203, master server judging unit 204, master server lead-out unit 205, from server judging unit 206, from server lead-out unit 207, data determining unit 208, date restoring judging unit 209 and data recovery unit 210.

Described setting unit 201, for arranging master server and being InnoDB storage engines from the default storage engine of server.

Described black hole table creating unit 202, for the table of a use Blackhole black hole engine newly-built on master server.In the present embodiment, in the table of this use Blackhole black hole engine, comprising a type is the field of character string.

Described operation execution unit 203, for performing specific database manipulation on master server, described specific database manipulation will be written in the binary log binlog file of master server; In the present embodiment, described operation execution unit 203, show to described black hole to insert a record in the table of the newly-built use Blackhole black hole engine of creating unit 202, and in the field of the character string type of this record, write operates relevant indication information with derived data.

Described master server judging unit 204, when writing binary log binlog file for performing on master server, judge whether the current database operation that will write in described binary log binlog file is the database manipulation inserting a record in the table of Blackhole type that described operation execution unit 203 performs.

Described master server lead-out unit 205, when the judged result of described master server judging unit 204 is, the current database operation write in described binary log binlog file is the database manipulation inserting a record in the table of Blackhole type that described operation execution unit 203 performs, then this unit starting, for deriving master server data.

Described master server lead-out unit 205, comprising: Master Server Info reads subelement 205-1 and master server statistical conversion performs subelement 205-2.

Described Master Server Info reads subelement 205-1, for reading in the record that inserts in the table of described Blackhole type, operates relevant indication information with derived data.

Described master server statistical conversion performs subelement 205-2, operate relevant indication information for what receive that described Master Server Info reads that subelement 205-1 exports with derived data, perform the operation of deriving master server data in the mode corresponding to this indication information.

In the present embodiment, in the record that described operation execution unit 203 is inserted in the table of Blackhole type, what write operates relevant indication information with derivation, comprising: the destination path of storage derived data, file destination name, the database-name that derive, table name claim the condition with demand fulfillment; Described Master Server Info reads subelement 205-1, read and above-mentionedly operate relevant indication information with derivation, and output to described master server statistical conversion execution subelement 205-2, described master server statistical conversion performs subelement 205-2, relevant indication information is operated with derivation according to above-mentioned, derive the data in qualified database and table on master server, and under being stored in described file destination name the catalogue that described destination path specifies.

Described from server judging unit 206, for in the content reading the binary log binlog file that above-mentioned master server is write from server, and when performing database manipulation wherein, judge whether the database manipulation of current execution is the database manipulation inserting a record in the table of Blackhole type that described operation execution unit 203 performs.

Described from server lead-out unit 207, when the described judged result from server judging unit 206 is, the database manipulation of current execution is the database manipulation inserting a record in the table of Blackhole type that described operation execution unit 203 performs, then this unit starting, for deriving from server data.

Described from server lead-out unit 207, comprising: read subelement 207-1 from server info and derive execution subelement 207-2 from server data.

Describedly reading subelement 207-1 from server info, for reading in the record that inserts in the table of described Blackhole type, operating relevant indication information with derived data.

Described derivation from server data performs subelement 207-2, for receive described read that subelement 207-1 exports from server info operate relevant indication information with derived data, perform the operation of deriving from server data in the mode corresponding to this indication information.

In the present embodiment, in the record that described operation execution unit 203 is inserted in the table of Blackhole type, what write operates relevant indication information with derivation, comprising: the destination path of storage derived data, file destination name, the database-name that derive, table name claim the condition with demand fulfillment; Described from server info reading subelement 207-1, read and above-mentionedly operate relevant indication information with derivation, and output to described from server data derivation execution subelement 207-2, described derivation from server data performs subelement 207-2, relevant indication information is operated with derivation according to above-mentioned, derive the data in qualified database and table from server, and under being stored in described file destination name the catalogue that described destination path specifies.

Described data determining unit 208, whether identical with the data derived from server lead-out unit 207 for judging the data that master server lead-out unit 205 is derived, and export judged result.

Described date restoring judging unit 209, for judging whether the specific database manipulation that described operation execution unit 203 performs introduces extra change to master and slave server data.

Described data recovery unit 210, if the judged result of described date restoring judging unit 209 is, the specific database manipulation that described operation execution unit 203 performs introduces extra change to master and slave server data, then this unit starting, this unit for performing the operation recovering raw data on master server.

In the present embodiment, the specific database manipulation that described operation execution unit 203 performs, insert a record in the table of Blackhole type, because the characteristic of Blackhole engine, this record can not be stored in the table of described Blackhole type, therefore the operation performing deletion record is not needed, but in order to perform described specific database manipulation, the newly-built table of a described Blackhole type, therefore described date restoring judging unit 209, judge that specific database manipulation that described operation execution unit 203 performs is to master, extra change is introduced from server data, described data recovery unit 210, derive described after server data from server lead-out unit 207, master server performs the operation of the table deleting described Blackhole type, this operation can be synchronized to from server by binlog, thus delete the table of Blackhole type newly-built from server, thus recovered master, from the raw data server.

Optionally, the device for MySQL principal and subordinate server data consistency detection that the present embodiment provides, also comprises: derive condition adjudgement unit, for judging whether the statistical conversion state of current master and slave server is " deriving "; State set unit, when the judged result of described derivation condition adjudgement unit is, when the current statistical conversion state of master and slave server is not " deriving ", this subelement starts, for arranging the statistical conversion state of master and slave master server for " unlatching of statistical conversion function ".

Accordingly, described operation execution unit, when the judged result of described derivation condition adjudgement unit is, when the current statistical conversion state of master and slave server is not " deriving ", this unit starting, for performing specific database manipulation.

Optionally, described master server lead-out unit also comprises: master server first state arranges subelement, start to perform the operation of derivation master server data for performing subelement at described master server statistical conversion before, the statistical conversion state of master server is set for " deriving "; Master server second state arranges subelement, after completing the operation of deriving master server data at described master server statistical conversion execution subelement, arranges the statistical conversion state of master server for " unlatching of statistical conversion function ".

Optionally, describedly also to comprise from server lead-out unit: from server first state, subelement is set, for described to derive the operation performing and to perform subelement and derive from server data from server data before, arrange from the statistical conversion state of server be " deriving "; From server second state, subelement is set, for performing subelement and complete described to derive from server data and derive from after the operation of server data, arrange from the statistical conversion state of server be " unlatching of statistical conversion function ".

Although the application with preferred embodiment openly as above; but it is not for limiting the application; any those skilled in the art are not departing from the spirit and scope of the application; can make possible variation and amendment, the scope that therefore protection domain of the application should define with the application's claim is as the criterion.

In one typically configuration, computing equipment comprises one or more processor (CPU), input/output interface, network interface and internal memory.

Internal memory may comprise the volatile memory in computer-readable medium, and the forms such as random access memory (RAM) and/or Nonvolatile memory, as ROM (read-only memory) (ROM) or flash memory (flash RAM).Internal memory is the example of computer-readable medium.

1, computer-readable medium comprises permanent and impermanency, removable and non-removable media can be stored to realize information by any method or technology.Information can be computer-readable instruction, data structure, the module of program or other data.The example of the storage medium of computing machine comprises, but be not limited to phase transition internal memory (PRAM), static RAM (SRAM), dynamic RAM (DRAM), the random access memory (RAM) of other types, ROM (read-only memory) (ROM), Electrically Erasable Read Only Memory (EEPROM), fast flash memory bank or other memory techniques, read-only optical disc ROM (read-only memory) (CD-ROM), digital versatile disc (DVD) or other optical memory, magnetic magnetic tape cassette, tape magnetic rigid disk stores or other magnetic storage apparatus or any other non-transmitting medium, can be used for storing the information can accessed by computing equipment.According to defining herein, computer-readable medium does not comprise non-temporary computer readable media (transitory media), as data-signal and the carrier wave of modulation.

2, it will be understood by those skilled in the art that the embodiment of the application can be provided as method, system or computer program.Therefore, the application can adopt the form of complete hardware embodiment, completely software implementation or the embodiment in conjunction with software and hardware aspect.And the application can adopt in one or more form wherein including the upper computer program implemented of computer-usable storage medium (including but not limited to magnetic disk memory, CD-ROM, optical memory etc.) of computer usable program code.

Claims

1., for a method for MySQL principal and subordinate server data consistency detection, it is characterized in that, comprising:

2. the method for MySQL principal and subordinate server data consistency detection according to claim 1, it is characterized in that, described specific database manipulation, comprising: creation database or table, Update Table storehouse or table, delete database or table, in table, insert record, record from table in deletion record or updating form.

3. the method for MySQL principal and subordinate server data consistency detection according to claim 1, is characterized in that, described on master server, perform the step of specific database manipulation before, perform following steps:

The table of a newly-built use Blackhole black hole engine on master server;

Accordingly, describedly on master server, perform specific database manipulation refer to:

Record is inserted in the table of above-mentioned use Blackhole black hole engine.

4. the method for MySQL principal and subordinate server data consistency detection according to claim 1, is characterized in that, described on master server, perform the step of specific database manipulation before, perform following steps:

Accordingly, the described step performing specific database manipulation on master server refers to:

In described specific table, insert a record, be that in the field of character string, write operates relevant indication information with derived data in the type of this record;

5. the method for MySQL principal and subordinate server data consistency detection according to claim 4, is characterized in that, describedly operates relevant indication information with derived data and refers to destination path and file destination name;

6. the method for MySQL principal and subordinate server data consistency detection according to claim 4, is characterized in that, describedly operates relevant indication information with derived data and refers to that database-name and/or table name claim;

7. the method for MySQL principal and subordinate server data consistency detection according to claim 4, is characterized in that, describedly operates relevant indication information with derived data and refers to that condition describes;

8. the method for MySQL principal and subordinate server data consistency detection according to claim 4-7 any one, is characterized in that, described specific table is the table using Blackhole black hole engine.

9. the method for MySQL principal and subordinate server data consistency detection according to claim 1, it is characterized in that, perform on master server if described in the step of specific database manipulation, extra change is introduced to master and slave server data, in described derivation after the step of server data, master server performs the operation recovering raw data.

10. the method for MySQL principal and subordinate server data consistency detection according to claim 1, is characterized in that, when performing the step of specific database manipulation described in performing on master server, first performs following step:

11. 1 kinds, for the device of MySQL principal and subordinate server data consistency detection, is characterized in that, comprising:

12. devices for MySQL principal and subordinate server data consistency detection according to claim 11, is characterized in that, also comprise:

13. devices for MySQL principal and subordinate server data consistency detection according to claim 11, is characterized in that, also comprise:

Accordingly, described master server lead-out unit comprises:

Accordingly, describedly to comprise from server lead-out unit:

14. devices for MySQL principal and subordinate server data consistency detection according to claim 11, is characterized in that, also comprise:

15. devices for MySQL principal and subordinate server data consistency detection according to claim 11, is characterized in that, also comprise:

Accordingly, described master server lead-out unit also comprises:

Accordingly, describedly also to comprise from server lead-out unit: