CN109388523B - Method for recovering MySQL database based on binary log file - Google Patents
Method for recovering MySQL database based on binary log file Download PDFInfo
- Publication number
- CN109388523B CN109388523B CN201811126400.1A CN201811126400A CN109388523B CN 109388523 B CN109388523 B CN 109388523B CN 201811126400 A CN201811126400 A CN 201811126400A CN 109388523 B CN109388523 B CN 109388523B
- Authority
- CN
- China
- Prior art keywords
- event
- data
- log file
- binary log
- header
- 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.)
- Active
Links
Images
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 method for recovering a MySQL database based on a binary log file, which is characterized by comprising the following steps of S100: searching a binary log file of a MySQL database in a disk, and acquiring the binary log file according to a binary log file signature; s200: analyzing the binary log file according to the format of the binary log file to obtain a table structure and an event statement of table data in the binary log file; s300: and performing lexical analysis and semantic analysis on the event statement to acquire the operation type of the current event, performing data restoration according to the operation type, and recovering and storing a data result. The invention has the beneficial effects that 1, under the circumstance without MySQL database, the table structure and the table data can be extracted through the binary log file, thereby achieving the effect of recovering the database table; 2. when only a binary log file fragment exists, the table data can be recovered as long as the fragment contains the key information of the required recovery table.
Description
Technical Field
The invention belongs to the field of data recovery, relates to a method for recovering a MySQL database, and particularly relates to a method for recovering the MySQL database based on a binary log file.
Background
With the advent of the information age, databases are kept open for data management and application. Database systems are one of the important components of computer application systems. Database technology has been widely applied to data processing, information retrieval, artificial intelligence and other aspects, and is one of the technologies essential for computer workers.
MySQL is a relational database management system developed by MySQL AB, Sweden, and currently belongs to the product under Oracle flag. MySQL is one of the most popular Relational Database Management systems, and is the best RDBMS (Relational Database Management System) application software in terms of WEB applications.
MySQL is a relational database management system that keeps data in different tables instead of putting all the data in one large repository, which increases speed and flexibility.
The SQL language used by MySQL is the most common standardized language for accessing databases. MySQL software adopts a double-authorization policy and is divided into a community version and a business version, and generally MySQL is selected as a website database for development of small and medium-sized websites due to the characteristics of small volume, high speed, low total ownership cost and particularly open source codes.
In recent years, the storage and management of information has increased the range and frequency of use of database technologies. Accordingly, database recovery techniques are also required to be innovative to accommodate new needs and changes. In the MySQL database recovery technology, there is a method for recovering deleted data through a binary log file of the MySQL database, however, the current technology has the following defects:
1. MySQL service must be relied on, that is, recovery must be done in the environment with MySQL database;
2. must have a complete log file;
3. in the event of a loss of a table structure file or a table data file, the table records/data of the MySQL database cannot be recovered.
Disclosure of Invention
The invention provides a method for recovering a MySQL database based on a binary log file, which aims at the defects of the prior art and comprises the following steps of analyzing and obtaining the binary log file, further analyzing an event statement of a table structure and table data in the binary log file, performing lexical analysis and semantic analysis on the event statement, obtaining the operation type of the current event, recovering the data according to the operation type, and recovering and storing the data result, wherein the method comprises the following steps:
s100: searching a binary log file of a MySQL database in a disk, and acquiring the binary log file according to a binary log file signature;
s200: analyzing the binary log file according to the format of the binary log file to obtain a table structure and an event statement of table data in the binary log file;
s300: and performing lexical analysis and semantic analysis on the event statement to acquire the operation type of the current event, performing data restoration according to the operation type, and recovering and storing a data result.
Preferably, the step S100 includes the following specific steps:
s101: searching the binary log file signature, wherein the binary log file signature is 0xFE 62696E;
s102: searching a universal header of the binary log file, reading the creation time of the binary log file in the universal header, and arranging and storing the universal header and corresponding data according to the sequence of the creation time;
s103: judging whether the search and the storage of the universal header of the current binary log file are finished, if so, executing the step S104, otherwise, executing the step 102, and searching and storing the next universal header;
s104: and judging whether the search of the binary log file signature in the disk is finished, if so, executing the step S200, otherwise, executing the step 101.
Preferably, the specific steps of step S102 are as follows:
s1021: 19 continuous bytes after the binary log file is signed by 0xFE62696E are a first universal header of the binary log file;
the addressing modes of other universal heads except the first universal head are as follows: the corresponding data of the current generic header is followed by the next generic header, which has the data structure shown in table1 below:
table 1: universal header data structure
| Name (R) | Length (byte) | Description of the |
| timestamp | ||
| 4 | Log | |
| type_code | ||
| 1 | Event type | |
| serve_id | 4 | |
| event_length | ||
| 4 | | |
| end_log_pos | ||
| 4 | Event | |
| flags | ||
| 2 | Version mark |
S1022: and reading the universal header and corresponding data according to the event length and the event end position in the universal header data structure of the table1, and arranging and storing the universal header and the corresponding data according to the sequence of the creation time.
Preferably, the specific steps of step S103 are as follows:
s1031: the next generic head is addressed: the next universal head is behind the corresponding data of the current universal head;
s1032: reading the event type of the next universal header according to the universal header data structure of table1, judging whether the event type is an end event, if so, finishing the search and storage of the universal header of the current binary log file, and executing step S104, otherwise, executing step S1033; wherein, if the type _ code in the common header data structure of table1 is equal to 0x03, it indicates that the event type is an end event;
s1033: the next generic header is addressed and step S102 is performed, wherein the corresponding data of the current generic header is followed by the next generic header.
Preferably, the step S200 includes the following specific steps:
s201: reading the universal header and the corresponding data obtained in the step S102 according to the universal header data structure in table 1;
s202: judging whether the current event type is a query event, if so, executing a step S203, otherwise, executing a step S201; wherein, if the type _ code in the common header data structure of table1 is equal to 0x02, it indicates that the event type is a query event;
s203: analyzing the current query event according to the data structure of the query event in the following TABLE 2, and acquiring event statements including CREATE TABLE, UPDATE and INSERT INTO;
table 2: data structure of query event
S204: and judging whether the analysis of the universal header and the corresponding data is finished, if so, executing the step 300, otherwise, executing the step 201.
Preferably, the specific steps of step S300 are as follows:
s301: classifying the event statements acquired in step S200, specifically including the following steps:
3011: if the event statement contains CREATE TABLE, classifying the current event statement into a TABLE structure;
3012: if the event statement comprises UPDATE and/or INSERT INTO, classifying the current event statement INTO table data;
3013: if the event statement does not contain one of CREATE TABLE, UPDATE or INSERT INTO, discarding;
s302: performing lexical analysis and semantic analysis on the current event statement;
s303: performing data restoration on the analysis result of the step S302 and storing the data result;
s304: merging the data result of the table structure with the same table name and the data result of the table data into a table record with the same table name;
s305: judging whether the analysis of all event statements is finished, if so, ending the process, otherwise, executing the step S302.
Preferably, the specific steps of step S302 are as follows:
s3021: performing lexical analysis and semantic analysis on the table structure:
analyzing an event statement "CREATE TABLE ' (' field1 ' XXXX, ' field2 ' YYYY, … …, ' field w ' wwwwww, … …, ' field n ' ZZZZ) ENGINE ═ InnoDB ROW _ FORMAT ═ COMPACT", wherein m, w, and n are natural numbers greater than 0 and w is not greater than n, and XXXX, YYYY, WWWW, and ZZZZ are field types;
the lexical analysis divides the event sentence into recognizable words and/or phrases CREATE TABLE, TABLE and field, which respectively represent TABLE building, TABLE name and domain name;
combining the event sentences into a table structure of tablem shown in the following table 3 by combining lexical analysis and semantic analysis;
table 3: tablem structure
| field1 | field2 | …… | fieldw | …… | fieldn |
S3022: performing lexical analysis and semantic analysis on the table data:
analyzing an event statement "insert into table value (value1, value2, … …, value w, … …, value n)" of the table data, wherein m, w and n are natural numbers greater than 0 and w is not greater than n;
the lexical analysis divides the event statement into identifiable words and/or phrases insert, table and value, which respectively represent values corresponding to insertion, table name and domain name;
in conjunction with lexical analysis, semantic parsing combines the event statements into table data-1 of tablems as in Table 4-1 below:
table 4-1: tablem data-1
| value1 | value2 | …… | valuew | …… | valuen |
Parsing an event statement "update table set field ═ value new where field ═ value of the table data; ", where m, w, n are all natural numbers greater than 0 and w is not greater than n;
the lexical analysis divides the event sentence into identifiable words and/or phrases update, table and value, which respectively represent values corresponding to update, table name and domain name;
in conjunction with lexical analysis, semantic parsing combines the event statements into table data-2 of tablems as in Table 4-2 below.
Tables 4-2: tablem data-2
| value1 | value2 | …… | valuenew | …… | valuen |
Preferably, the specific steps of step S303 are as follows:
s3031: the table structure for obtaining the tablem is as follows:
| field1 | field2 | …… | fieldw | …… | fieldn |
s3032: and (3) data recovery: determining that the current operation behavior is to insert a piece of data into the table structure of the tablem, and obtaining a current operation result as follows:
| field1 | field2 | …… | fieldw | …… | fieldn |
| value1 | value2 | …… | valuew | …… | valuen |
s3033: and (3) data recovery: determining that the current operation behavior is to update a piece of data in a table structure of the tablem, and obtaining a current operation result as follows:
| field1 | field2 | …… | fieldw | …… | fieldn |
| value1 | value2 | …… | valuenew | …… | valuen |
s3034: and repeating the steps S3031 to S3032 until the analysis of all the event statements is completed, merging the operation results and storing the operation results as the data result of the tablem.
The invention has the beneficial effects that:
1. under the circumstance of no MySQL database, the table structure and the table data can be extracted through the binary log file, and the effect of recovering the database table is achieved;
2. when only a binary log file fragment exists, the data of the table can be recovered as long as the fragment contains key information of the table to be recovered; in some cases, the binary log file is completely lost (for example, the binary log file is deleted), and as long as the binary log file is not covered, the recovered binary log file can be extracted from the disk for data recovery;
3. the invention is completely independent of the database table to recover the data, and the recovery is not influenced by the loss of the table structure and the table data.
The following explanations are made for terms appearing in the present invention:
file signing: a value unique to the file, identifying the type of file.
Event statement: statements describing operations executed by the system in the MySQL binary log are divided into different event categories according to different operations.
A universal head: all event statements in the MySQL binary log file have the same header structure.
Binary log file sequence: and storing a plurality of binary log files according to the time sequence.
And (3) data recovery: and analyzing the data operation statement, and conjecturing the data execution result by combining the data state before the data operation.
Table structure: the table metadata information is a general term for the attributes of the table itself. The metadata information includes: the number of fields of the table, the type of the fields, the sequence of the fields, the length of the fields, the precision of the fields, the character codes of the fields, the NULL attribute of the fields, the existence or non-existence of symbols of the fields, the key information and the index information of the table and the like.
Table data: structured data organized according to a table structure
Recording in a table: a complete database table having a table structure and table data.
Drawings
FIG. 1 is a general flow diagram of a method provided by the present invention;
FIG. 2 is a diagram illustrating a data format of a binary log file including a binary log file signature and a generic header according to an embodiment of the present invention;
FIG. 3 is a diagram illustrating a data format of a binary log file containing an end event according to an embodiment of the present invention.
Detailed Description
The invention is further illustrated with reference to the figures and examples.
Fig. 1 shows a general flow chart of the method provided by the present invention, as shown in fig. 1, comprising the following steps:
s100: searching a binary log file of a MySQL database in a disk, and acquiring the binary log file according to a signature of the binary log file, wherein the method comprises the following specific steps:
s101: and (4) searching the binary log file signature, wherein the binary log file of the MySQL database is divided into a plurality of files according to the file size, time and date and the like. However, no matter how many files are divided, the start position of the binary log file is a fixed file signature 0xFE 62696E. As shown by the thin black underline portion in fig. 2, the binary log file signature 0xFE62696E is found as the identifier of the binary log file;
s102: searching a universal head of the binary log file, reading the creation time of the binary log file in the universal head, and arranging and storing the universal head and corresponding data according to the sequence of the creation time, wherein the method comprises the following specific steps:
s1021: as shown by the bold dashed-black line portion in fig. 2, 19 consecutive bytes after the binary log file signature 0xFE62696E is the first universal header of the binary log file;
the generic header has a data structure as shown in table1 below:
table 1: universal header data structure
| Name (R) | Length (byte) | Description of the |
| timestamp | ||
| 4 | Log creation time (Small-end format storage) | |
| type_code | 1 | |
| serve_id | ||
| 4 | | |
| event_length | ||
| 4 | Event Length (Small-end format storage) | |
| end_log_pos | 4 | Event end position (Small format storage) |
| |
2 | Version mark |
The addressing modes of other universal heads except the first universal head are as follows: the corresponding data of the current generic header is followed by the next generic header. Specifically, in conjunction with the first universal header and table1 shown by the bold and black underline portion in fig. 2, according to the four-byte content 0x00000074 of the event length and the four-byte content 0x00000078 of the event end position, that is, the continuous 0x74 bytes of content from the start address of the first universal header as the head address and the end address as 0x00000078 are the first universal header and the corresponding data, as shown by the rectangular frame portion in fig. 2; in this embodiment, the next universal header is a continuous 19 bytes of content starting at 0x682CE 95A.
S1022: and reading the universal header and corresponding data according to the event length and the event end position in the universal header data structure of the table1, and arranging and storing the universal header and the corresponding data according to the sequence of the creation time. Specifically, as shown in the first universal header and table1 in fig. 2, the content 0C716E95A of the first 4 bytes of the first universal header is from 1 month and 1 day in 1970 to the current total seconds, the content 0x00000074 of the four bytes according to the event length and the content 0x00000078 of the four bytes of the event end position, that is, the content of the consecutive 0x74 bytes from the beginning address of the first universal header as the first address and the end address as 0x00000078 is the first universal header and the corresponding data, and finally, the current universal header and the corresponding data are arranged and stored in the order of creation time.
S103: judging whether the search and the storage of the universal header of the current binary log file are finished, if so, executing the step S104, otherwise, executing the step 102, and searching and storing the next universal header;
the method comprises the following specific steps:
s1031: the next generic head is addressed: the next universal header is followed by the corresponding data of the current universal header, as described in step S1021 above;
s1032: reading the event type of the next universal header according to the universal header data structure of table1, judging whether the event type is an end event, if so, finishing the search and storage of the universal header of the current binary log file, and executing step S104, otherwise, executing step S1033; as shown in fig. 3, the thick black underline part shows another generic header and the corresponding data, and the oval part shows an event type 0x03 of the next generic header, which indicates that the event type is an end event, i.e., the search and save of the generic header of the current binary log file are completed;
s1033: the next generic header is addressed and step S102 is performed, followed by the corresponding data of the current generic header being the next generic header, as described above in step S1021.
S104: and judging whether the search of the binary log file signature in the disk is finished, if so, executing the step S200, otherwise, executing the step 101.
S200: analyzing the binary log file according to the format of the binary log file to obtain an event statement of a table structure and table data in the binary log file, and the method comprises the following specific steps:
s201: reading the universal header and the corresponding data obtained in the step S102 according to the universal header data structure in table 1;
s202: judging whether the current event type is a query event, if so, executing a step S203, otherwise, executing a step S201; as shown in fig. 2, the square box part shows another event type 0x02 of the generic header, which indicates that the event type is a query event;
s203: analyzing the current query event according to the data structure of the query event in the following TABLE 2, and acquiring event statements including CREATE TABLE, UPDATE and INSERT INTO;
table 2: data structure of query event
S204: and judging whether the analysis of the universal header and the corresponding data is finished, if so, executing the step 300, otherwise, executing the step 201.
S300: performing lexical analysis and semantic analysis on the event statement to obtain the operation type of the current event, performing data restoration according to the operation type, and recovering and storing a data result, wherein the step S300 specifically comprises the following steps:
s301: classifying the event sentences acquired in step S200, specifically including the following steps:
3011: if the event statement contains CREATE TABLE, classifying the current event statement into a TABLE structure for storing all binary log statements related to the TABLE structure;
3012: if the event statements comprise UPDATE and/or INSERT INTO, classifying the current event statements INTO table data for storing all binary log statements related to the table data;
3013: if the event statement does not contain one of CREATE TABLE, UPDATE or INSERT INTO, discarding the event statements;
s302: performing lexical analysis and semantic analysis on the current event statement, wherein the step S302 specifically comprises the following steps:
s3021: performing lexical analysis and semantic analysis on the table structure:
event statements that resolve a TABLE structure, such as "CREATE TABLE 'TABLE 1' ('field 1' tinyint NOT NULL, 'field 2' varchar (255) NULL) ENGINE ═ InnoDB ROW _ FORMAT ═ COMPACT";
the lexical analysis divides the event sentence into recognizable words and/or phrases CREATE TABLE, TABLE and field, which respectively represent a TABLE, a TABLE name TABLE1 and domain names of field1 and field 2;
combining lexical analysis, semantic analysis combines event statements into a table structure of table1 as shown in table 5 below;
table 5: table structure of table1
| field1 | field2 |
S3022: performing lexical analysis and semantic analysis on the table data:
an event statement that parses table data, such as "insert table1value (1, repeat ('a', 255))";
the lexical analysis divides the event sentence into recognizable words and/or phrases insert, table and value, which respectively represent the values 1 and 255a corresponding to the insertion, table name 1 and domain name;
in conjunction with lexical analysis, semantic parsing combines event statements into table data-1 (aaaaaaaa.......... denotes 255 characters "a") of table1 as in table 6-1 below:
table 6-1: table data-1 of table1
| 1 | aaaaaa......... |
An event statement "update table1set field2 ═ b where field2 ═ aaaaaa.. said.; ", wherein, aaaaaa.... denotes 255 characters" a ";
the lexical analysis divides the event sentence into identifiable words and/or phrases update, table and value, which respectively represent updating, table name table1 and the value b corresponding to the domain name;
in conjunction with lexical analysis, semantic parsing combines event statements into table data-2 of table1 as in Table 6-2 below, i.e., updates the value of the original field2 from 255 characters "a" to b.
Table 6-2: table data-2 of table1
| 1 | b |
S303: restoring the data of the analysis result in the step S302 and storing the data result, wherein the specific steps in the step S303 are as follows:
s3031: the table structure of the acquisition table1 is:
| field1 | field2 |
s3032: and (3) data recovery: determining that the current operation behavior is to insert a piece of data into the table structure of table1, and obtaining the current operation result as:
| field1 | field2 |
| 1 | aaaaaa......... |
s3033: and (3) data recovery: determining that the current operation behavior is to update a piece of data in the table structure of table1, and obtaining the current operation result as:
| field1 | field2 |
| 1 | aaaaaa......... |
further, in this embodiment:
if the semantics describes a table structure event, the structure definition of the frm file in MySQL can be used as a standard data structure for saving data results, all table structure information is extracted from the semantics, and then a data table similar to the step 3032 is created according to the table structure information;
if the semantic description is a table data event, the data is simple data, and can be directly stored in the domain corresponding to the data table created in the step 3033;
if the semanteme describes an event of a table data category, and the data is big data and irregular, the data can be compressed and then stored in a domain corresponding to the data table created in the step 3033;
if the semantics describes a table data event, the data is big data and regular, the basic data and the change rule can be saved, and the data result in step 3032 can be saved as (1,255 a).
S3034: and repeating the steps S3031 to S3033 until the analysis of all the event statements is completed, merging the operation results and storing the operation results as the data result of the tablem.
S304: merging the data result of the table structure with the same table name and the data result of the table data into a table record with the same table name;
s305: judging whether the analysis of all event statements is finished, if so, ending the process, otherwise, executing the step S302.
The method solves the technical problem that no method for recovering the MySQL database based on the binary log file exists in the prior art.
It is to be understood that the invention is not limited to the examples described above, but that modifications and variations are possible to those skilled in the art in light of the above teachings, and that all such modifications and variations are intended to be included within the scope of the invention as defined in the appended claims.
Claims (5)
1. A method for recovering a MySQL database based on a binary log file is characterized by comprising the following steps:
s100: searching a binary log file of a MySQL database in a disk, and acquiring the binary log file according to a signature of the binary log file, wherein the step S100 comprises the following specific steps:
s101: searching the binary log file signature, wherein the binary log file signature is 0xFE 62696E;
s102: searching a universal header of the binary log file, reading the creation time of the binary log file in the universal header, and arranging and storing the universal header and corresponding data according to the sequence of the creation time, wherein the specific steps of the step S102 are as follows:
s1021: 19 continuous bytes after the binary log file is signed by 0xFE62696E are a first universal header of the binary log file;
the addressing modes of other universal heads except the first universal head are as follows: the corresponding data of the current generic header is followed by the next generic header, which has the data structure shown in table1 below:
table 1: universal header data structure
S1022: reading the universal header and corresponding data according to the event length and the event end position in the universal header data structure of the table1, and arranging and storing the universal header and the corresponding data according to the sequence of the creation time;
s103: judging whether the search and the storage of the universal header of the current binary log file are finished, if so, executing the step S104, otherwise, executing the step 102, and searching and storing the next universal header;
s104: judging whether the search of the binary log file signature in the disk is finished, if so, executing the step S200, otherwise, executing the step 101;
s200: analyzing the binary log file according to the format of the binary log file to obtain an event statement of a table structure and table data in the binary log file, wherein the step S200 comprises the following specific steps:
s201: reading the universal header and the corresponding data obtained in the step S102 according to the universal header data structure in table 1;
s202: judging whether the current event type is a query event, if so, executing a step S203, otherwise, executing a step S201; wherein, if the type _ code in the common header data structure of table1 is equal to 0x02, it indicates that the event type is a query event;
s203: analyzing the current query event according to the data structure of the query event in the following TABLE 2, and acquiring event statements including CREATE TABLE, UPDATE and INSERT INTO;
table 2: data structure of query event
S204: judging whether the analysis of the universal header and the corresponding data is finished, if so, executing step 300, otherwise, executing step 201;
s300: and performing lexical analysis and semantic analysis on the event statement to acquire the operation type of the current event, performing data restoration according to the operation type, and recovering and storing a data result.
2. The method for recovering MySQL database based on binary log file according to claim 1, wherein the specific steps of the step S103 are as follows:
s1031: the next generic head is addressed: the next universal head is behind the corresponding data of the current universal head;
s1032: reading the event type of the next universal header according to the universal header data structure of table1, judging whether the event type is an end event, if so, finishing the search and storage of the universal header of the current binary log file, and executing step S104, otherwise, executing step S1033; wherein, if the type _ code in the common header data structure of table1 is equal to 0x03, it indicates that the event type is an end event;
s1033: the next generic header is addressed and step S102 is performed, wherein the corresponding data of the current generic header is followed by the next generic header.
3. The method for recovering MySQL database based on binary log files according to claim 1, wherein the specific steps of the step S300 are as follows:
s301: classifying the event statements acquired in step S200, specifically including the following steps:
3011: if the event statement contains CREATE TABLE, classifying the current event statement into a TABLE structure;
3012: if the event statement comprises UPDATE and/or INSERT INTO, classifying the current event statement INTO table data;
3013: if the event statement does not contain one of CREATE TABLE, UPDATE or INSERT INTO, discarding;
s302: performing lexical analysis and semantic analysis on the current event statement;
s303: performing data restoration on the analysis result of the step S302 and storing the data result;
s304: merging the data result of the table structure with the same table name and the data result of the table data into a table record with the same table name;
s305: judging whether the analysis of all event statements is finished, if so, ending the process, otherwise, executing the step S302.
4. The method for recovering MySQL database based on binary log files according to claim 3, wherein the specific steps of the step S302 are as follows:
s3021: performing lexical analysis and semantic analysis on the table structure:
analyzing an event statement "CREATE TABLE ' (' field1 ' XXXX, ' field2 ' YYYY, … …, ' field w ' wwwwww, … …, ' field n ' ZZZZ) ENGINE ═ InnoDB ROW _ FORMAT ═ COMPACT", wherein m, w, and n are natural numbers greater than 0 and w is not greater than n, and XXXX, YYYY, WWWW, and ZZZZ are field types;
the lexical analysis divides the event sentence into recognizable words and/or phrases CREATE TABLE, TABLE and field, which respectively represent TABLE building, TABLE name and domain name;
combining the event sentences into a table structure of tablem shown in the following table 3 by combining lexical analysis and semantic analysis;
table 3: tablem structure
S3022: performing lexical analysis and semantic analysis on the table data:
analyzing an event statement "insert into table value (value1, value2, … …, value w, … …, value n)" of the table data, wherein m, w and n are natural numbers greater than 0 and w is not greater than n;
the lexical analysis divides the event statement into identifiable words and/or phrases insert, table and value, which respectively represent values corresponding to insertion, table name and domain name;
in conjunction with lexical analysis, semantic parsing combines the event statements into table data-1 of tablems as in Table 4-1 below:
table 4-1: tablem data-1
Parsing an event statement "update table set field ═ value new where field ═ value of the table data; ", where m, w, n are all natural numbers greater than 0 and w is not greater than n;
the lexical analysis divides the event sentence into identifiable words and/or phrases update, table and value, which respectively represent values corresponding to update, table name and domain name;
in conjunction with lexical analysis, semantic parsing combines the event statements into table data-2 of tablems as in Table 4-2 below.
Tables 4-2: tablem data-2
5. The method for recovering MySQL database based on binary log files according to claim 3, wherein the specific steps of the step S303 are as follows:
s3031: the table structure for obtaining the tablem is as follows:
s3032: and (3) data recovery: determining that the current operation behavior is to insert a piece of data into the table structure of the tablem, and obtaining a current operation result as follows:
s3033: and (3) data recovery: determining that the current operation behavior is to update a piece of data in a table structure of the tablem, and obtaining a current operation result as follows:
s3034: and repeating the steps S3031 to S3033 until the analysis of all the event statements is completed, merging the operation results and storing the operation results as the data result of the tablem.
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 CN109388523A (en) | 2019-02-26 |
| CN109388523B true 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) |
Families Citing this family (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| 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 |
| 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 |
| CN111488263B (en) * | 2020-04-14 | 2023-05-09 | 北京思特奇信息技术股份有限公司 | Method and device for analyzing journals in MySQL database |
| CN111563123B (en) * | 2020-05-07 | 2023-08-22 | 北京首汽智行科技有限公司 | Real-time synchronization method for hive warehouse metadata |
| CN113282592B (en) * | 2021-07-22 | 2021-09-24 | 成都云祺科技有限公司 | Method, system and storage medium for recovering MSSQL database |
Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104951474A (en) * | 2014-03-31 | 2015-09-30 | 阿里巴巴集团控股有限公司 | Method and device for acquiring MySQL binlog incremental logs |
| CN107102934A (en) * | 2016-02-22 | 2017-08-29 | 阿里巴巴集团控股有限公司 | The method and apparatus that a kind of relevant database binary log is reset |
Family Cites Families (5)
| 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 |
| US10067999B2 (en) * | 2016-10-11 | 2018-09-04 | 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 |
-
2018
- 2018-09-26 CN CN201811126400.1A patent/CN109388523B/en active Active
Patent Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104951474A (en) * | 2014-03-31 | 2015-09-30 | 阿里巴巴集团控股有限公司 | Method and device for acquiring MySQL binlog incremental logs |
| CN107102934A (en) * | 2016-02-22 | 2017-08-29 | 阿里巴巴集团控股有限公司 | The method and apparatus that a kind of relevant database binary log is reset |
Also Published As
| Publication number | Publication date |
|---|---|
| CN109388523A (en) | 2019-02-26 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN109388523B (en) | Method for recovering MySQL database based on binary log file | |
| CN111522816B (en) | Data processing method, device, terminal and medium based on database engine | |
| CN1297936C (en) | Method and system for comparing files of two computers | |
| US7324992B2 (en) | Database processing method and system | |
| CN109840256B (en) | Query realization method based on business entity | |
| US9400733B2 (en) | Pattern matching framework for log analysis | |
| EP3751426A1 (en) | System and method for migration of a legacy datastore | |
| US20060106849A1 (en) | Idle CPU indexing systems and methods | |
| US20010032199A1 (en) | Method for optimizing the performance of a database | |
| US8200702B2 (en) | Independently variably scoped content rule application in a content management system | |
| US11449564B2 (en) | System and method for searching based on text blocks and associated search operators | |
| CN111506559A (en) | Data storage method and device, electronic equipment and storage medium | |
| CN115543402B (en) | Software knowledge graph increment updating method based on code submission | |
| US9053207B2 (en) | Adaptive query expression builder for an on-demand data service | |
| US6401089B2 (en) | Method for maintaining exception tables for a check utility | |
| EP3635580A1 (en) | Functional equivalence of tuples and edges in graph databases | |
| CN110019306B (en) | SQL statement searching method and system based on XML format file | |
| US20120303608A1 (en) | Method and system for caching lexical mappings for rdf data | |
| US20050137856A1 (en) | Full-text index module consistency checking | |
| CN107633094B (en) | Method and device for data retrieval in cluster environment | |
| US10877998B2 (en) | Highly atomized segmented and interrogatable data systems (HASIDS) | |
| CN115344603A (en) | Freight rate data storage method and device, electronic equipment and computer storage medium | |
| CN112817931A (en) | Method and device for generating incremental version file | |
| CN114089976B (en) | Method, apparatus, and medium for generating database operation statements | |
| CN111221846B (en) | Automatic translation method and device for SQL sentences |
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 |