CN109388523A - A method of based on binary log file access pattern MySQL database - Google Patents
A method of based on binary log file access pattern MySQL database Download PDFInfo
- Publication number
- CN109388523A CN109388523A CN201811126400.1A CN201811126400A CN109388523A CN 109388523 A CN109388523 A CN 109388523A CN 201811126400 A CN201811126400 A CN 201811126400A CN 109388523 A CN109388523 A CN 109388523A
- Authority
- CN
- China
- Prior art keywords
- event
- data
- log file
- binary log
- general head
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Granted
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F11/00—Error detection; Error correction; Monitoring
- G06F11/07—Responding to the occurrence of a fault, e.g. fault tolerance
- G06F11/14—Error detection or correction of the data by redundancy in operation
- G06F11/1402—Saving, restoring, recovering or retrying
- G06F11/1471—Saving, restoring, recovering or retrying involving logging of persistent data for recovery
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Quality & Reliability (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a kind of methods based on binary log file access pattern MySQL database, characterized by the following steps: S100: searching the binary log file of MySQL database in disk, obtains the binary log file according to binary log file signature；S200: according to the format of binary log file, the binary log file is parsed, obtains the event statements of table structure and table data in the binary log file；S300: morphological analysis and semantic parsing are carried out to the event statements, obtain the action type of current event, data recovery is carried out according to the action type, restores and saves data result.The beneficial effects of the present invention are: 1. can extract table structure and table data by binary log file under no MySQL database bad border, achieve the effect that restore database table；2. only exist binary log file fragment, as long as this segment includes the required key message for restoring table, the table data can be restored.
Description
Technical field
The invention belongs to data to restore field, be related to a kind of method for restoring MySQL database, and in particular to one kind is based on
The method of binary log file access pattern MySQL database.
Background technique
With the arriving of information age, management and application to data all be unable to do without database.Database Systems are to calculate
One of the important component of machine application system.Database technology is widely used to data processing, information retrieval, artificial intelligence
Etc. all various aspects, database application technology is to be engaged in one of the technology of personnel's indispensability of computer operation.
MySQL is a Relational DBMS, is developed by MySQL AB company, Sweden, belongs to Oracle at present
Under product.MySQL is one of most popular Relational DBMS, and in terms of WEB application, MySQL is best
RDBMS (Relational Database Management System, relational database management system) application software.
MySQL is a kind of relational database management system, and relational database saves the data in different tables, rather than
All data are placed in one big warehouse, which adds speed and improve flexibility.
Sql like language used in MySQL is the most frequently used standardized language for accessing database.MySQL software uses
Double authorization policies, are divided into Community Edition and commercial version, especially open since its is small in size, speed is fast, the total cost of ownership is low
The exploitation of this feature of source code, general middle-size and small-size website all selects MySQL as site databases.
In recent years, the storage and management of information are increased with the use scope and frequency of use of database technology.Correspondingly,
Database recovery technology is also required to reform to adapt to new demand and change.In MySQL database recovery technology, there is one kind
The method for deleting data by the binary log file access pattern of MySQL database, but, current this technology exist with next
A little defects:
1. MySQL service must be relied on, that is, it must do and restore under with MySQL database environment；
2. must have complete journal file；
3. losing table structure file or in the case where table data file, can not restore the table record of MySQL database/
Data.
Summary of the invention
Present invention problem in view of the deficiencies of the prior art proposes a kind of based on binary log file access pattern MySQL number
According to the method in library, by parsing and obtaining binary log file, table structure and table in binary log file are further parsed
The event statements of data, and morphological analysis and semantic parsing are carried out to event statements, the action type of current event is obtained, according to
Action type carries out data recovery, restores and saves data result to achieve the purpose that restore MySQL database, including is following
Step:
S100: searching the binary log file of MySQL database in disk, is obtained according to binary log file signature
The binary log file；
S200: according to the format of binary log file, the binary log file is parsed, obtains the binary system day
The event statements of table structure and table data in will file；
S300: carrying out morphological analysis and semantic parsing to the event statements, obtain the action type of current event, according to
The action type carries out data recovery, restores and saves data result.
Preferably, the step S100 includes step in detail below:
S101: the binary log file signature is searched, wherein the binary log file signature is
0xFE62696E；
S102: searching the general head of the binary log file, reads the text of binary log described in the general head
The general head and corresponding data are arranged and are saved by the sequence of the creation time by the creation time of part；
S103: judge whether to complete the lookup and preservation of the general head of current binary log file, if so, executing step
Otherwise rapid S104 executes step 102, carry out the lookup and preservation of next general head；
S104: judging whether to complete the lookup of binary log file signature in the disk, if so, executing step
Otherwise S200 executes step 101.
Preferably, specific step is as follows by the step S102:
S1021: continuous 19 byte is the binary log text after the binary log file signature 0xFE62696E
The general head of the first of part；
The addressing system of other general heads in addition to the described first general head are as follows: be after the corresponding data of current general head
Next general head, general head have data structure as shown in table 1 below:
Table 1: general head data structure
| Title | Length (byte) | Explanation |
| timestamp | 4 | The log creation time |
| type_code | 1 | Event type |
| serve_id | 4 | Service id |
| event_length | 4 | Event length |
| end_log_pos | 4 | Event end position |
| flags | 2 | Version sign |
S1022: it according to event length and event end position in the general head data structure of the table 1, reads described logical
With head and corresponding data, and is arranged and saved by the sequence of the creation time.
Preferably, specific step is as follows by the step S103:
S1031: the next general head of addressing: being next general head after the corresponding data of current general head；
S1032: according to the general head data structure of the table 1, the event type of next general head is read, judges institute
State whether event type is End Event, if it is, the lookup and preservation of the general head of current binary log file are completed,
Step S104 is executed, otherwise, executes step S1033；Wherein, type_code is equal in the general head data structure of the table 1
0x03 then indicates that the event type is End Event；
S1033: the next general head of addressing simultaneously executes step S102, wherein is institute after the corresponding data of current general head
State next general head.
Preferably, the step S200 includes step in detail below:
S201: according to the general head data structure of the table 1, read the general head acquired in the step S102 and
Corresponding data；
S202: judging whether current event type is query event, if so, otherwise executing step S203 executes step
Rapid S201；Wherein, in the general head data structure of the table 1 type_code be equal to 0x02 then indicate that the event type is
Query event；
S203: according to the data structure of the query event of such as the following table 2, current query event is parsed, acquisition includes
The event statements of CREATE TABLE, UPDATE, INSERT INTO；
The data structure of table 2:query event
S204: judging whether to complete the parsing of the general head and corresponding data, if so, step 300 is executed, otherwise,
Execute step 201.
Preferably, specific step is as follows by the step S300:
S301: event statements acquired in the step S200 are classified, the specific steps are as follows:
3011: including CREATE TABLE in event statements, be then table structure by current event statement classification；
3012: including UPDATE and/or INSERT INTO in event statements, be then table number by current event statement classification
According to；
3013: not including one of CREATE TABLE, UPDATE or INSERT INTO in event statements, then give up；
S302: morphological analysis and semantic parsing are carried out to current event sentence；
S303: data recovery is carried out to the parsing result of the step S302 and saves data result；
S304: the data result of the data result of the table structure with identical table name and table data is merged into identical table name
Table record；
S305: whether interpretation completes the parsing of all event statements, if it is, terminating process, otherwise, executes step
S302。
Preferably, specific step is as follows by the step S302:
S3021: morphological analysis and semantic parsing are carried out to the table structure:
Parse event statements " CREATE TABLE ' tablem ' (' field1 ' XXXX, ' field2 ' of the table structure
YYYY ... ..., ' fieldw'WWWW ... ..., ' fieldn'ZZZZ) ENGINE=InnoDB ROW_FORMAT=COMPACT ",
Wherein, it no more than n, XXXX, YYYY, WWWW, ZZZZ is field type that m, w, n, which are natural number greater than 0 and w,；
Morphological analysis by the event statements be divided into each identifiable word and/or phrase CREATE TABLE,
Table and field respectively indicates to build table, table name and domain name；
In conjunction with morphological analysis, the event statements group is combined into the table structure of the tablem of following table 3 by semanteme parsing；
The table structure of table 3:tablem
| field1 | field2 | …… | fieldw | …… | fieldn |
S3022: morphological analysis and semantic parsing are carried out to the table data:
Parse the table data event statements " insert into tablem value (value1, value2 ... ...,
Valuew ... ..., valuen) ", wherein m, w, n are the natural number greater than 0 and w is not more than n；
The event statements are divided into each recognizable word and/or phrase insert into, table by morphological analysis
And value, respectively indicate insertion, table name and the corresponding value of domain name；
In conjunction with morphological analysis, the event statements group is combined into the table data -1 of the tablem of following table 4-1 by semanteme parsing:
The table data -1 of table 4-1:tablem
| value1 | value2 | …… | valuew | …… | valuen |
Parse event statements " the update tablem set fieldw=valuenew where of the table data
Fieldw=valuew；", wherein m, w, n are the natural number greater than 0 and w is not more than n；
Morphological analysis by the event statements be divided into each recognizable word and/or phrase update, table and
Value respectively indicates update, table name and the corresponding value of domain name；
In conjunction with morphological analysis, the event statements group is combined into the table data -2 of the tablem of following table 4-2 by semanteme parsing.
The table data -2 of table 4-2:tablem
| value1 | value2 | …… | valuenew | …… | valuen |
Preferably, specific step is as follows by the step S303:
S3031: the table structure of tablem is obtained are as follows:
| field1 | field2 | …… | fieldw | …… | fieldn |
S3032: data recovery: determining that current operation behavior is that a data is inserted into the table structure of tablem, obtains
Current operation result are as follows:
| field1 | field2 | …… | fieldw | …… | fieldn |
| value1 | value2 | …… | valuew | …… | valuen |
S3033: data recovery: determine that current operation behavior is a data in the table structure for update tablem, acquisition is worked as
Preceding operating result are as follows:
| field1 | field2 | …… | fieldw | …… | fieldn |
| value1 | value2 | …… | valuenew | …… | valuen |
S3034: repeating said steps S3031 to S3032 until complete the parsing of all event statements, by operating result into
Row saves as the data result of tablem after merging.
The beneficial effects of the present invention are:
1. can extract table structure and table data under no MySQL database bad border by binary log file, reach
To the effect for restoring database table；
2. only exist binary log file fragment, as long as this segment includes the required key message for restoring table, energy
Restore the table data；In some cases, binary log file (for example deleting binary log file) is lost completely,
As long as binary log file is not covered with, it will be able to which it is extensive to recover binary log file progress data for extraction from disk
It is multiple；
It, will not be due to losing table structure and table data to extensive 3. present invention is fully independent of database tables to carry out data recovery
Reproduce raw any influence.
The term that the present invention occurs is made explained below:
File signature: the exclusive value of file identifies the type of file.
Event statements: the performed sentence operated of system is described in MySQL binary log, is divided into not by different operations
Same event category.
General head: identical header structure possessed by all event statements in MySQL binary log file.
Binary log file sequence: the multiple binary log files stored according to chronological order.
Data recovery: parsing data manipulation sentence, in conjunction with the data mode before being operated to data, thus it is speculated that data are held
Row result.
Table structure: table metadata information is the general name to table self attributes.Metadata information includes: literary name number of segment, field
Type, order of the field, field length, field precision, field character code, field NULL attribute, whether there is or not symbol and tables for field
Key information and index information etc..
Table data: according to the data made of table structure tissue with certain structure
Table record: the full database table with table structure and table data.
Detailed description of the invention
Fig. 1 is the general flow chart of method provided by the present invention；
Fig. 2 is the number of the binary log file comprising binary log file signature and general head in the embodiment of the present invention
According to form schematic diagram；
Fig. 3 is the data format schematic diagram of the binary log file in the embodiment of the present invention comprising End Event.
Specific embodiment
The present invention is further elaborated with reference to the accompanying drawings and examples.
Fig. 1 shows the general flow chart of method provided by the present invention, as shown in Figure 1, comprising the following steps:
S100: searching the binary log file of MySQL database in disk, is obtained according to binary log file signature
Binary log file includes step in detail below:
S101: search binary log file signature: the binary log file of MySQL database can be big according to file
Small, Time of Day is divided into multiple files.But no matter it is divided into how many a files, the initial position of binary log file is all solid
Fixed file signature 0xFE62696E.As shown in night dashed part thin in Fig. 2, binary log file signature is found
Mark of the 0xFE62696E as the binary log file；
S102: searching the general head of binary log file, read the creation time of binary log file in general head,
General head and corresponding data are arranged and are saved by the sequence of creation time, step in detail below is included:
S1021: as shown in night dashed part thick in Fig. 2, continuous 19 after binary log file signature 0xFE62696E
Byte is the first general head of binary log file；
General head has data structure as shown in table 1 below:
Table 1: general head data structure
| Title | Length (byte) | Explanation |
| timestamp | 4 | The log creation time (storage of small end format) |
| type_code | 1 | Event type |
| serve_id | 4 | Service id |
| event_length | 4 | Event length (storage of small end format) |
| end_log_pos | 4 | Event end position (storage of small end format) |
| flags | 2 | Version sign |
The addressing system of other general heads in addition to the first general head are as follows: be next after the corresponding data of current general head
General head.Specifically, the first general head and table 1 in conjunction with shown in night dashed part thick in Fig. 2, according to four words of event length
Save the nybble content 0x00000078 of content 0x00000074 and event end position, that is, from the starting point of the first general head
The content for the continuous 0x74 byte that location is first address and end address is 0x00000078 is the first general head and corresponding data, such as
In Fig. 2 shown in rectangle frame part；In this embodiment, next general head is the interior of continuous 19 bytes that 0x682CE95A starts
Hold.
S1022: according to event length and event end position in the general head data structure of table 1, general head and right is read
Data are answered, and is arranged and is saved by the sequence of creation time.Specifically, as shown in night dashed part thick in Fig. 2
One general head and table 1, the content 0C716E95A of 4 bytes is on January 1st, 1970 to current total number of seconds, root before the first general head
According to the nybble content 0x00000074 of the event length and nybble content 0x00000078 of event end position, that is, from
The content for the continuous 0x74 byte that the initial address of one general head is first address and end address is 0x00000078 is first logical
With head and corresponding data, finally, and being arranged by the sequence of creation time and saving current general head and corresponding data.
S103: judge whether to complete the lookup and preservation of the general head of current binary log file, if so, executing step
Otherwise rapid S104 executes step 102, carry out the lookup and preservation of next general head；
Specific step is as follows:
S1031: the next general head of addressing: being next general head, step as above after the corresponding data of current general head
Described in S1021；
S1032: according to the general head data structure of table 1, the event type of next general head is read, judges that event type is
No is End Event, if it is, completing the lookup and preservation of the general head of current binary log file, executes step
Otherwise S104 executes step S1033；As shown in figure 3, thick night dashed part shows another general head and corresponding data, it is ellipse
Circular portion shows the event type 0x03 of next general head, and expression event type is End Event, that is, completes current binary system
The lookup and preservation of the general head of journal file；
S1033: the next general head of addressing simultaneously executes step S102, is next general after the corresponding data of current general head
Head, described in step S1021 as above.
S104: judging whether to complete the lookup of binary log file signature in disk, if so, step S200 is executed,
Otherwise, step 101 is executed.
S200: according to the format of binary log file, binary log file is parsed, is obtained in binary log file
The event statements of table structure and table data include step in detail below:
S201: according to the general head data structure of table 1, general head and corresponding data acquired in read step S102；
S202: judging whether current event type is query event, if so, otherwise executing step S203 executes step
Rapid S201；As shown in Fig. 2, square-shaped frame partially illustrates the event type 0x02 of another general head, then it represents that event type is
Query event；
S203: according to the data structure of the query event of such as the following table 2, current query event is parsed, acquisition includes
The event statements of CREATE TABLE, UPDATE, INSERT INTO；
The data structure of table 2:query event
S204: judging whether to complete the parsing of general head and corresponding data, if so, executing step 300, otherwise, executes
Step 201.
S300: morphological analysis and semantic parsing are carried out to event statements, the action type of current event is obtained, according to operation
Type carries out data recovery, restores and save data result, specific step is as follows by step S300:
S301: event statements acquired in step S200 are classified, the specific steps are as follows:
3011: including CREATE TABLE in event statements, be then table structure by current event statement classification, to store
All binary log sentences relevant to table structure；
3012: including UPDATE and/or INSERT INTO in event statements, be then table number by current event statement classification
According to store all binary log sentences relevant to table data；
3013: not including one of CREATE TABLE, UPDATE or INSERT INTO in event statements, then give up these
Event statements；
S302: morphological analysis and semantic parsing are carried out to current event sentence, specific step is as follows by step S302:
S3021: morphological analysis and semantic parsing are carried out to table structure:
Parse the event statements of table structure, such as " CREATE TABLE ' table1 ' (' field1 ' tinyint NOT
NULL, ' field2 ' varchar (255) NULL) ENGINE=InnoDB ROW_FORMAT=COMPACT "；
Morphological analysis by event statements be divided into each identifiable word and/or phrase CREATE TABLE, table and
Field respectively indicates to build table, table name table1 and domain name field1, field2；
In conjunction with morphological analysis, event statements group is combined into the table structure of the table1 of following table 5 by semanteme parsing；
The table structure of table 5:table1
| field1 | field2 |
S3022: morphological analysis and semantic parsing are carried out to table data:
The event statements of resolution table data such as " insert into table1value (1, repeat (' a ', 255)) "；
Morphological analysis by event statements be divided into each recognizable word and/or phrase insert into, table and
Value respectively indicates insertion, table name table1 and the corresponding value 1 of domain name, 255a；
In conjunction with morphological analysis, event statements group is combined into the table data -1 of the table1 of following table 6-1 by semanteme parsing
(aaaaaa........ indicates 255 characters " a "):
The table data -1 of table 6-1:table1
| 1 | aaaaaa......... |
Event statements " the update table1set field2=b where field2=of resolution table data
aaaaaa........；", wherein aaaaaa........ indicates 255 characters " a "；
Event statements are divided into each recognizable word and/or phrase update, table and value by morphological analysis, respectively
From expression update, table name table1 and the corresponding value b of domain name；
In conjunction with morphological analysis, event statements group is combined into the table data -2 of the table1 of following table 6-2 by semanteme parsing, that is,
The value of former field2 is updated to b by 255 characters " a ".
The table data -2 of table 6-2:table1
| 1 | b |
S303: data recovery is carried out to the parsing result of step S302 and saves data result, the specific step of step S303
It is rapid as follows:
S3031: the table structure of table1 is obtained are as follows:
| field1 | field2 |
S3032: data recovery: determining that current operation behavior is that a data is inserted into the table structure of table1, obtains
Current operation result are as follows:
| field1 | field2 |
| 1 | aaaaaa......... |
S3033: data recovery: determine that current operation behavior is a data in the table structure for update table1, acquisition is worked as
Preceding operating result are as follows:
| field1 | field2 |
| 1 | aaaaaa......... |
In addition, in the embodiment:
If semantic description is table structure event, the structure of frm file can will be defined as in MySQL saving number
According to the standard data structure of result, all table structure information is extracted from semanteme, then according to table structure information creating one
The similar data form such as step 3032；
If semantic description is table data event, data are simple data, can be stored directly in step 3033 creation
The corresponding domain of data form in；
If semantic description be table data category event, data are big data and irregular, then can to data into
Then row compression processing is stored in the corresponding domain of data form of step 3033 creation；
If semantic description is table data event, data are big data and regular, then can save master data and
Changing rule, as the data result in step 3032 can save as (1,255a).
S3034: parsing of the step S3031 to S3033 up to completing all event statements is repeated, operating result is closed
The data result of tablem is saved as after and.
S304: the data result of the data result of the table structure with identical table name and table data is merged into identical table name
Table record；
S305: whether interpretation completes the parsing of all event statements, if it is, terminating process, otherwise, executes step
S302。
The method provided through the invention solves and there is no one kind extensive based on binary log file in the prior art
The technical issues of method of multiple MySQL database.
It should be understood that the present invention is not limited to above-mentioned citings, it for those of ordinary skills, can basis
Above description is improved or converted, and all these modifications and variations all should belong to the protection model of appended claims of the present invention
It encloses.
Claims (8)
1. a kind of method based on binary log file access pattern MySQL database, it is characterised in that the following steps are included:
S100: searching the binary log file of MySQL database in disk, according to the acquisition of binary log file signature
Binary log file；
S200: according to the format of binary log file, parsing the binary log file, obtains the binary log text
The event statements of table structure and table data in part；
S300: morphological analysis and semantic parsing are carried out to the event statements, the action type of current event is obtained, according to described
Action type carries out data recovery, restores and saves data result.
2. a kind of method based on binary log file access pattern MySQL database according to claim 1, feature exist
In the step S100 includes step in detail below:
S101: the binary log file signature is searched, wherein the binary log file signature is 0xFE62696E；
S102: searching the general head of the binary log file, reads binary log file described in the general head
The general head and corresponding data are arranged and are saved by the sequence of the creation time by creation time；
S103: judging whether to complete the lookup and preservation of the general head of current binary log file, if so, executing step
Otherwise S104 executes step 102, carry out the lookup and preservation of next general head；
S104: judging whether to complete the lookup of binary log file signature in the disk, if so, step S200 is executed,
Otherwise, step 101 is executed.
3. a kind of method based on binary log file access pattern MySQL database according to claim 2, feature exist
In specific step is as follows by the step S102:
S1021: continuous 19 byte is the binary log file after the binary log file signature 0xFE62696E
First general head；
The addressing system of other general heads in addition to the described first general head are as follows: be next after the corresponding data of current general head
General head, general head have data structure as shown in table 1 below:
Table 1: general head data structure
S1022: according to event length and event end position in the general head data structure of the table 1, the general head is read
And corresponding data, and arranged and saved by the sequence of the creation time.
4. a kind of method based on binary log file access pattern MySQL database according to claim 2, feature exist
In specific step is as follows by the step S103:
S1031: the next general head of addressing: being next general head after the corresponding data of current general head；
S1032: according to the general head data structure of the table 1, the event type of next general head is read, judges the thing
Whether part type is End Event, if it is, completing the lookup and preservation of the general head of current binary log file, is executed
Otherwise step S104 executes step S1033；Wherein, type_code is equal to 0x03 then in the general head data structure of the table 1
Indicate that the event type is End Event；
S1033: the next general head of addressing simultaneously executes step S102, wherein under being described after the corresponding data of current general head
One general head.
5. a kind of method based on binary log file access pattern MySQL database according to claim 2, feature exist
In the step S200 includes step in detail below:
S201: according to the general head data structure of the table 1, the general head and correspondence acquired in the step S102 are read
Data；
S202: judging whether current event type is query event, if so, executing step S203, otherwise, executes step
S201；Wherein, in the general head data structure of the table 1 type_code be equal to 0x02 then indicate the event type be query
Event；；
S203: according to the data structure of the query event of such as the following table 2, parsing current query event, and obtaining includes CREATE
The event statements of TABLE, UPDATE, INSERT INTO；
The data structure of table 2:query event
S204: judging whether to complete the parsing of the general head and corresponding data, if so, executing step 300, otherwise, executes
Step 201.
6. a kind of method based on binary log file access pattern MySQL database according to claim 1, feature exist
In specific step is as follows by the step S300:
S301: event statements acquired in the step S200 are classified, the specific steps are as follows:
3011: including CREATE TABLE in event statements, be then table structure by current event statement classification；
3012: including UPDATE and/or INSERT INTO in event statements, be then table data by current event statement classification；
3013: not including one of CREATE TABLE, UPDATE or INSERT INTO in event statements, then give up；
S302: morphological analysis and semantic parsing are carried out to current event sentence；
S303: data recovery is carried out to the parsing result of the step S302 and saves data result；
S304: the data result of the data result of the table structure with identical table name and table data is merged into the table of identical table name
Record；
S305: whether interpretation completes the parsing of all event statements, if it is, terminating process, otherwise, executes step S302.
7. a kind of method based on binary log file access pattern MySQL database according to claim 6, feature exist
In specific step is as follows by the step S302:
S3021: morphological analysis and semantic parsing are carried out to the table structure:
Parse event statements " CREATE TABLE ' tablem ' (' field1 ' XXXX, ' field2 ' of the table structure
YYYY ... ..., ' fieldw'WWWW ... ..., ' fieldn'ZZZZ) ENGINE=InnoDB ROW_FORMAT=COMPACT ",
Wherein, it no more than n, XXXX, YYYY, WWWW, ZZZZ is field type that m, w, n, which are natural number greater than 0 and w,；
Morphological analysis by the event statements be divided into each identifiable word and/or phrase CREATE TABLE, table and
Field respectively indicates to build table, table name and domain name；
In conjunction with morphological analysis, the event statements group is combined into the table structure of the tablem of following table 3 by semanteme parsing；
The table structure of table 3:tablem
S3022: morphological analysis and semantic parsing are carried out to the table data:
Parse the table data event statements " insert into tablem value (value1, value2 ... ...,
Valuew ... ..., valuen) ", wherein m, w, n are the natural number greater than 0 and w is not more than n；
Morphological analysis by the event statements be divided into each recognizable word and/or phrase insert into, table and
Value respectively indicates insertion, table name and the corresponding value of domain name；
In conjunction with morphological analysis, the event statements group is combined into the table data -1 of the tablem of following table 4-1 by semanteme parsing:
The table data -1 of table 4-1:tablem
Parse event statements " the update tablemset fieldw=valuenew where fieldw=of the table data
valuew；", wherein m, w, n are the natural number greater than 0 and w is not more than n；
The event statements are divided into each recognizable word and/or phrase update, table and value by morphological analysis, respectively
From expression update, table name and the corresponding value of domain name；
In conjunction with morphological analysis, the event statements group is combined into the table data -2 of the tablem of following table 4-2 by semanteme parsing.
The table data -2 of table 4-2:tablem
8. a kind of method based on binary log file access pattern MySQL database according to claim 6, feature exist
In specific step is as follows by the step S303:
S3031: the table structure of tablem is obtained are as follows:
S3032: data recovery: determining that current operation behavior is that a data is inserted into the table structure of tablem, obtains current
Operating result are as follows:
S3033: it data recovery: determines that current operation behavior is a data in the table structure for update tablem, obtains current behaviour
Make result are as follows:
Operating result is closed in S3034: repeating said steps S3031 to the S3033 parsing up to completing all event statements
The data result of tablem is saved as after and.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201811126400.1A CN109388523B (en) | 2018-09-26 | 2018-09-26 | Method for recovering MySQL database based on binary log file |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201811126400.1A CN109388523B (en) | 2018-09-26 | 2018-09-26 | Method for recovering MySQL database based on binary log file |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN109388523A true CN109388523A (en) | 2019-02-26 |
| CN109388523B CN109388523B (en) | 2021-11-30 |
Family
ID=65418259
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201811126400.1A Active CN109388523B (en) | 2018-09-26 | 2018-09-26 | Method for recovering MySQL database based on binary log file |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN109388523B (en) |
Cited By (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110427282A (en) * | 2019-07-17 | 2019-11-08 | 厦门市美亚柏科信息股份有限公司 | The method, apparatus and computer-readable medium restored for log fragment |
| CN110569223A (en) * | 2019-09-16 | 2019-12-13 | 京东数字科技控股有限公司 | database log processing method and device |
| CN111045868A (en) * | 2019-12-13 | 2020-04-21 | 上海中通吉网络技术有限公司 | Method and device for automatically recovering database data |
| CN111209251A (en) * | 2019-12-27 | 2020-05-29 | 山大地纬软件股份有限公司 | Data increment synchronization system and method for data archiving system |
| CN111488263A (en) * | 2020-04-14 | 2020-08-04 | 北京思特奇信息技术股份有限公司 | Method and device for analyzing logs in MySQ L database |
| CN111563123A (en) * | 2020-05-07 | 2020-08-21 | 北京首汽智行科技有限公司 | Live warehouse metadata real-time synchronization method |
| CN113282592A (en) * | 2021-07-22 | 2021-08-20 | 成都云祺科技有限公司 | Method, system and storage medium for recovering MSSQL database |
Citations (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US8041679B1 (en) * | 2008-06-04 | 2011-10-18 | Symantec Operating Corporation | Synthetic differential backups creation for a database using binary log conversion |
| CN103838780A (en) * | 2012-11-27 | 2014-06-04 | 阿里巴巴集团控股有限公司 | Data recovery method of database and relevant device |
| CN104765659A (en) * | 2015-04-30 | 2015-07-08 | 北京奇虎科技有限公司 | Data recovery method and device applied to database |
| CN104951474A (en) * | 2014-03-31 | 2015-09-30 | 阿里巴巴集团控股有限公司 | Method and device for acquiring MySQL binlog incremental logs |
| CN106897173A (en) * | 2017-03-01 | 2017-06-27 | 四川艾特赢泰智能科技有限责任公司 | It is a kind of to search and recover the method recorded by modification in SQL server databases |
| CN107102934A (en) * | 2016-02-22 | 2017-08-29 | 阿里巴巴集团控股有限公司 | The method and apparatus that a kind of relevant database binary log is reset |
| US20180101589A1 (en) * | 2016-10-11 | 2018-04-12 | VoltDB, Inc. | High-performance database replication systems and methods |
-
2018
- 2018-09-26 CN CN201811126400.1A patent/CN109388523B/en active Active
Patent Citations (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US8041679B1 (en) * | 2008-06-04 | 2011-10-18 | Symantec Operating Corporation | Synthetic differential backups creation for a database using binary log conversion |
| CN103838780A (en) * | 2012-11-27 | 2014-06-04 | 阿里巴巴集团控股有限公司 | Data recovery method of database and relevant device |
| CN104951474A (en) * | 2014-03-31 | 2015-09-30 | 阿里巴巴集团控股有限公司 | Method and device for acquiring MySQL binlog incremental logs |
| CN104765659A (en) * | 2015-04-30 | 2015-07-08 | 北京奇虎科技有限公司 | Data recovery method and device applied to database |
| CN107102934A (en) * | 2016-02-22 | 2017-08-29 | 阿里巴巴集团控股有限公司 | The method and apparatus that a kind of relevant database binary log is reset |
| US20180101589A1 (en) * | 2016-10-11 | 2018-04-12 | VoltDB, Inc. | High-performance database replication systems and methods |
| CN106897173A (en) * | 2017-03-01 | 2017-06-27 | 四川艾特赢泰智能科技有限责任公司 | It is a kind of to search and recover the method recorded by modification in SQL server databases |
Non-Patent Citations (3)
| Title |
|---|
| YUANDLL: "文件签名", 《HTTPS://BLOG.CSDN.NET/FARCE/ARTICLE/DETAILS/5606222》 * |
| 无: "有关binlog的那点事（二）（mysql5.7.13）", 《HTTP://WWW.WEST999.COM/INFO/HTML/WANGLUOBIANCHENG/MYSQL/20180617/4180455.HTML》 * |
| 赵志学: "MySQL 数据库备份与恢复", 《电脑学习》 * |
Cited By (10)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110427282A (en) * | 2019-07-17 | 2019-11-08 | 厦门市美亚柏科信息股份有限公司 | The method, apparatus and computer-readable medium restored for log fragment |
| CN110427282B (en) * | 2019-07-17 | 2022-05-27 | 厦门市美亚柏科信息股份有限公司 | Method, apparatus and computer readable medium for log fragment recovery |
| CN110569223A (en) * | 2019-09-16 | 2019-12-13 | 京东数字科技控股有限公司 | database log processing method and device |
| CN111045868A (en) * | 2019-12-13 | 2020-04-21 | 上海中通吉网络技术有限公司 | Method and device for automatically recovering database data |
| CN111045868B (en) * | 2019-12-13 | 2023-12-05 | 上海中通吉网络技术有限公司 | Method and device for automatically recovering database data |
| CN111209251A (en) * | 2019-12-27 | 2020-05-29 | 山大地纬软件股份有限公司 | Data increment synchronization system and method for data archiving system |
| CN111488263A (en) * | 2020-04-14 | 2020-08-04 | 北京思特奇信息技术股份有限公司 | Method and device for analyzing logs in MySQ L database |
| CN111563123A (en) * | 2020-05-07 | 2020-08-21 | 北京首汽智行科技有限公司 | Live warehouse metadata real-time synchronization method |
| CN111563123B (en) * | 2020-05-07 | 2023-08-22 | 北京首汽智行科技有限公司 | Real-time synchronization method for hive warehouse metadata |
| CN113282592A (en) * | 2021-07-22 | 2021-08-20 | 成都云祺科技有限公司 | Method, system and storage medium for recovering MSSQL database |
Also Published As
| Publication number | Publication date |
|---|---|
| CN109388523B (en) | 2021-11-30 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN109388523A (en) | A method of based on binary log file access pattern MySQL database | |
| US9424294B2 (en) | Method for facet searching and search suggestions | |
| JP5833406B2 (en) | Data management architecture related to generic data items using references | |
| Balmin et al. | Incremental validation of XML documents | |
| US10180992B2 (en) | Atomic updating of graph database index structures | |
| CN108052659A (en) | Searching method, device and electronic equipment based on artificial intelligence | |
| US10671671B2 (en) | Supporting tuples in log-based representations of graph databases | |
| US20030195889A1 (en) | Unified relational database model for data mining | |
| US20170255708A1 (en) | Index structures for graph databases | |
| Sacks-Davis et al. | Atlas: A nested relational database system for text applications | |
| CN105373607B (en) | Method for compressing SQL access log of power business system | |
| CN103440265B (en) | The delta data catching method of MYSQL database based on MapReduce | |
| US10706030B2 (en) | Utilizing artificial intelligence to integrate data from multiple diverse sources into a data structure | |
| CN111190873B (en) | Log mode extraction method and system for log training of cloud native system | |
| US20150006577A1 (en) | Method and system for searching and storing data | |
| US8756246B2 (en) | Method and system for caching lexical mappings for RDF data | |
| EP3635580A1 (en) | Functional equivalence of tuples and edges in graph databases | |
| US20180349443A1 (en) | Edge store compression in graph databases | |
| CN103927373A (en) | Method for building dynamic big data model efficiently based on incremental association rule technology | |
| WO2018080560A1 (en) | Encoding edges in graph databases | |
| US11144580B1 (en) | Columnar storage and processing of unstructured data | |
| US10877998B2 (en) | Highly atomized segmented and interrogatable data systems (HASIDS) | |
| Liu et al. | PAIRPQ: an efficient path index for regular path queries on knowledge graphs | |
| CN113032371A (en) | Database grammar analysis method and device and computer equipment | |
| CN111723162B (en) | Dictionary processing method, processing device, server and voice interaction system |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |