CN104951474B

CN104951474B - Method and device for acquiring MySQL binlog incremental log

Info

Publication number: CN104951474B
Application number: CN201410125645.8A
Authority: CN
Inventors: 刘杰
Original assignee: Alibaba Group Holding Ltd
Current assignee: Alibaba Group Holding Ltd
Priority date: 2014-03-31
Filing date: 2014-03-31
Publication date: 2021-10-01
Anticipated expiration: 2034-03-31
Also published as: HK1211110A1; CN104951474A

Abstract

The application discloses a method for acquiring a MySQL binlog incremental log, which comprises the following steps: searching a first record of which the time stamp is not earlier than a pre-specified time stamp in a binary log file set of the current MySQL database; and acquiring the incremental log of the current MySQL database according to the file name of the binary log file where the record is located and the position of the record in the file. The application also provides a device for acquiring the MySQL binlog incremental log. By adopting the method provided by the application, the start point for obtaining the incremental log can be appointed through the timestamp, so that the incremental log data can still be correctly obtained under the condition that the MySQL master database is changed.

Description

Method and device for acquiring MySQL binlog incremental log

Technical Field

The application relates to a MySQL database technology, in particular to a method for acquiring a MySQL binlog incremental log. The application also provides a device for acquiring the MySQL binlog incremental log.

Background

MySQL databases have various different types of log files: the MySQL database management system comprises an error log, a binary log, a query log, a slow query log, a redo log and the like, and can query information such as operation, state and the like executed by the MySQL database according to the logs, and log files are indispensable for the management and maintenance of the MySQL database.

Wherein the binary log (binlog) records the change operation of the MySQL database, such as: a database or table (create), an insert operation (insert), an update operation (update), a delete operation (insert), etc. is created, and each change operation is written to the binary log file as a record, each record including a timestamp of the current record update, the location of the record in the current binary log file (i.e., offset), and other information related to the database change operation. MySQL databases typically have one or more binary log files, the different binary log files being distinguished by file extensions in the form of different numerical numbers, for example: mysql-bin.00001. By using the binary log file, on one hand, the data recovery function of the MySQL database can be realized, and on the other hand, the data consistency of the MySQL master and slave databases can be realized through master and slave copying.

The basic process of MySQL primary and standby replication using binary log files is as follows: establishing connection between the standby database and the main database, and requesting to acquire the log records behind the specified position of the specified binary log file; the main database returns the log records positioned at the rear of the designated position in the designated binary log file to the standby database; after receiving the data returned by the main database, the standby database analyzes the change operations executed on the main database, and executes the change operations on the standby database, that is, the same database operations are executed on the main database and the standby database, so that the data consistency of the main database and the standby database is realized through the main-standby copying process.

Therefore, the MySQL master database supports the incremental mode to provide the binary log data for other standby databases or application programs needing to acquire the binary log data; accordingly, a backup database or other application program that needs to obtain the binary log data from the MySQL master database needs to explicitly specify the file name of the binary log file from which the log data is desired to be obtained and the offset of the log data to be obtained in the binary log file in the request for obtaining the incremental log data, so that the required binary log data can be correctly obtained from the master database.

The mode of acquiring the incremental log by designating the 'file name + offset' is beneficial to reducing the interactive data amount between the main database and the standby database or other application programs for acquiring binary log data and reducing the occupation of bandwidth, but certain defects exist in the specific implementation process. For an application program that needs to fetch incremental binary log data from a master database to meet related business requirements such as incremental subscription or consumption, the incremental binary log data can be fetched from the master database in a manner similar to that of the backup database. However, when the primary database and the secondary database are switched due to a failure of the primary database, binary log files of the primary database and the secondary database are numbered independently, no corresponding relationship exists between the primary database and the secondary database, and start point information (filename + offset) used by an application program for capturing incremental logs is associated with the primary database, so that a start point corresponding to original start point information cannot be found in the binary log files of the secondary database (current primary database), and the application program cannot continuously capture incremental log data or capture wrong incremental log data.

Disclosure of Invention

The application provides a method for acquiring MySQL binlog incremental logs, which aims to solve the problem that incremental logs cannot be acquired correctly in the prior art in a mode of designating sites by 'filenames and offsets' under the condition of changing a MySQL main database. The present application additionally provides an apparatus for obtaining a MySQL binlog delta log.

The application provides a method for obtaining MySQL binlog incremental logs, which comprises the following steps:

searching a first record of which the time stamp is not earlier than a pre-specified time stamp in a binary log file set of the current MySQL database;

and acquiring the incremental log of the current MySQL database according to the file name of the binary log file where the record is located and the position of the record in the file.

Optionally, the searching for the first record of which the timestamp is not earlier than the pre-specified timestamp in the binary log file set of the current MySQL database includes:

searching the last file of which the timestamp of the first record is not later than the pre-specified timestamp in the binary log file set of the current MySQL database;

looking up a first record in the last file having a timestamp not earlier than the pre-specified timestamp.

Optionally, when the step of searching the last file of which the timestamp of the first record is not later than the pre-specified timestamp in the binary log file set of the current MySQL database is executed, a binary search method is adopted.

Optionally, the method includes:

establishing data connection with the current MySQL database;

acquiring a binary log file list of the current MySQL database;

correspondingly, in the binary log file set of the current MySQL database, the first record whose timestamp is not earlier than the pre-specified timestamp is searched, that is, the search operation is performed in the file set included in the obtained binary log file list.

Optionally, the establishing of the data connection with the current MySQL database includes:

acquiring the IP address of a server where the current MySQL database is located and the port number of a database service provided by the current MySQL database;

and establishing data connection with the current MySQL database by using the IP address and the port number.

Optionally, the obtaining of the IP address of the server where the current MySQL database is located and the port number of the database service provided by the current MySQL database is implemented in the following manner:

and reading information related to the current MySQL database in a meta-information base, and extracting the IP address and the port number from the information.

Optionally, the method includes:

and recording the timestamp of the last record of the incremental log acquired from the MySQL master database as the pre-designated timestamp.

Optionally, before the step of recording the timestamp of the last record of the incremental log obtained from the MySQL master database as the pre-specified timestamp, the following steps are performed:

judging whether the MySQL database system executes the main/standby switching operation;

if yes, the MySQL standby database which takes over the MySQL main database and continues to provide database services is used as the current MySQL database.

Optionally, the determining whether the MySQL database system executes the active/standby switching operation is implemented by the following method:

monitoring whether a field which is stored in a meta-information base and used for recording an IP address of a server where a MySQL main database is located changes or not; and if so, judging that the MySQL database system executes the main/standby switching operation.

The present application further provides a device for obtaining MySQL binlog incremental logs, comprising:

the record searching unit is used for searching a first record of which the time stamp is not earlier than a pre-specified time stamp in a binary log file set of the current MySQL database;

and the incremental log obtaining unit is used for obtaining the incremental log of the current MySQL database according to the file name of the binary log file in which the record is found by the record searching unit and the position of the record in the file.

Optionally, the record searching unit includes:

the file searching subunit is used for searching the last file of which the timestamp of the first record is not later than the pre-specified timestamp in the binary log file set of the current MySQL database;

a record lookup performing subunit, configured to lookup, in the last file, a first record whose timestamp is not earlier than the pre-specified timestamp.

Optionally, the file searching subunit is specifically configured to search, by using a binary search method, a last file of which the timestamp of the first record is not later than the pre-specified timestamp in the binary log file set of the current MySQL database.

Optionally, the apparatus includes:

the connection establishing unit is used for establishing data connection with the current MySQL database;

the file list acquisition unit is used for acquiring a binary log file list of the current MySQL database;

correspondingly, the record searching unit executes searching operation in the file set contained in the binary log file list output by the file list acquiring unit.

Optionally, the connection establishing unit includes:

the connection information acquisition subunit is used for acquiring the IP address of the server where the current MySQL database is located and the port number of the database service provided by the current MySQL database;

and the connection establishment execution subunit is used for establishing data connection with the current MySQL database by using the IP address and the port number output by the connection information acquisition subunit.

Optionally, the connection information obtaining subunit is specifically configured to read information in a meta information base, which is related to the current MySQL database, and extract the IP address and the port number from the information.

Optionally, the apparatus includes:

and the time stamp recording unit is used for recording the time stamp of the last record of the increment log acquired from the MySQL master database as the pre-specified time stamp.

Optionally, the apparatus includes:

the master-slave switching judging unit is used for judging whether the MySQL database system executes master-slave switching operation or not;

and the current database selection unit is used for taking over the MySQL standby database which continuously provides database service for the MySQL main database as the current MySQL database when the output of the main/standby switching judgment unit is 'yes'.

Optionally, the master/slave switching determining unit is specifically configured to monitor whether a field stored in the meta information base and used for recording an IP address of a server in which the MySQL master database is located changes; and if so, judging that the MySQL database system executes the main/standby switching operation.

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

the method for acquiring the MySQL binlog incremental log does not adopt a mode of determining the start point according to the file name and the offset in the prior art, but adopts a mode of positioning the start point according to the timestamp, namely: searching a first record with a timestamp not earlier than a pre-specified timestamp in a binary log file set of the current MySQL database, and acquiring incremental log data of the current MySQL database according to the file name of the binary log file where the record is located and the position of the record in the file.

Drawings

FIG. 1 is a flowchart of an embodiment of a method for obtaining a MySQL binlog incremental log of the present application;

FIG. 2 is a flowchart of an embodiment of the present application for finding a last file having a timestamp of a first record that is not later than the pre-specified timestamp using bisection;

fig. 3 is a flowchart of an embodiment of a processing procedure required in the active/standby switching scenario according to the present application;

fig. 4 is a schematic diagram of an embodiment of an apparatus for obtaining a MySQL binlog delta log according to the present application.

Detailed Description

In the following description, numerous specific details are set forth in order to provide a thorough understanding of the present application. This application is capable of implementation in many different ways than those herein set forth and of similar import by those skilled in the art without departing from the spirit of this application and is therefore not limited to the specific implementations disclosed below.

In the application, a method and a device for acquiring the MySQL binlog incremental log are respectively provided. To facilitate understanding of the technical solutions of the present application, before describing the technical solutions of the present application in detail, a brief description is made on the binlog file format.

Binlog is a binary stored log of MySQL with each Binlog file header being a 4-byte flag with a value of 0xfe0x620x690x6e (i.e., 0xfe followed by bin). The header is followed by a series of LOG _ EVENTs, which are the basic units in the binlog file, i.e. the binlog normally grows in LOG _ EVENT by LOG _ EVENT. Each LOG _ EVENT is an independent unit, with no mutual reference relationship. There are many EVENT types of LOG _ EVENT, wherein the EVENT type of LOG _ EVENT used for recording database change operation is a query EVENT (queryevent). The LOG _ EVENT in the above binlog file structure is a record described in this application.

Each LOG _ EVENT has an EVENT header of 19 bytes in length, which contains information such as the length of the EVENT, the timestamp of the EVENT, and the EVENT type. The event header is followed by an event body determined according to the event type,

for the record with the type of query event, the agent thread ID, the error code, the specific database change operation statement, and the like are recorded in the event body. The binlog file format is shown in the following table:

table one: binlog file format (where Nth byte refers to the ordering starting from 0)

The host time of the execution EVENT recorded in bytes 0-3 of the LOG _ EVENT header is the timestamp information described in this application, that is, the time of the current record update, for example: the time stamp 1390467118 corresponds to the time of the Thu Jan2316:51:58CST 2014. Bytes 13-16 of the LOG _ EVENT header is the offset (where the record is located in the binary LOG file) as described herein.

Based on the knowledge of the binlog file format, the following embodiments of the present invention are described in detail. Please refer to fig. 1, which is a flowchart illustrating an embodiment of a method for obtaining a MySQL binlog incremental log according to the present application. The method comprises the following steps:

step 101: and searching a first record with the timestamp not earlier than a pre-specified timestamp in a binary log file set of the current MySQL database.

According to the technical scheme, according to a pre-specified timestamp, in a binary log file set of a current MySQL database of an incremental log to be acquired, a start point corresponding to the timestamp is positioned, namely: and searching a first record of which the time stamp is not earlier than the time stamp, and acquiring incremental log data of the current MySQL database by taking the file name of the file where the record is located and the offset of the record in the file as starting points.

As can be seen from the above description, the core of the technical solution of the present application is: and positioning the starting site through the timestamp information, and acquiring incremental log data of the MySQL database according to the starting site. Therefore, the technical scheme of the application can be deployed at a server side where the MySQL database is located to acquire the incremental log data in a local reading mode, and can also be deployed on other equipment different from the server where the MySQL database is located to acquire the incremental log data in a network connection establishing mode. Neither of these embodiments departs from the core of the present application.

According to the technical scheme, the starting point is positioned according to the timestamp, and the searching process of the binary log file set of the current MySQL database is realized through the step. The following first presents a brief description of the set of binary log files described in this step.

The MySQL database, when initially running, creates two new files with names like xxx-bin.000001 and xxx-bin.index from the root directory, if the relevant options for the configuration file do not give a file name, MySQL will name these two files using the host name, for example: mysql-bin.000001 and mysql-bin.index. MySQL records all modifications to the database contents and structure in the MySQL-bin.000001 file, which contains a list of all currently used binary log files. When the MySQL database is restarted or the size of the log file exceeds the upper limit specified by the system variable max _ binlog _ size, a new log file is generated, and the serial numbers of the file extensions of the new log file are sequentially increased. Therefore, the binary log files of the MySQL database may be one or a group, and the set of binary log files described in this application includes all the binary log files currently used by the MySQL database.

Because the binary log files record the change operations made on the MySQL database according to the time sequence, and the binary log file set is also arranged according to the sequence of the generated binary log files from early to late (the numbers in the file extensions are sequentially accumulated), the search operation in this step refers to performing a search operation on the binary log files contained in the current binary log file set of the MySQL database and ordered according to the time from early to late, and searching for the first record with the timestamp not earlier than the pre-specified timestamp.

As can be seen from the foregoing description of the binlog file format, the binlog file acts like a stream file, and the changes made to the MySQL database are recorded in chronological order. And each record has no obvious start and end mark, and the end position of one record can be judged only by length. Based on the characteristic of the binlog file format, the binary log file set of the current MySQL database is searched for the fact that the time stamp of the binary log file set is not earlier than the pre-specified time stamp, the simplest and easiest method is that each binary log file is analyzed one by one, when each file is analyzed, each record is read from the head of the binary log file, whether the time stamp of the current record is not earlier than the pre-specified time stamp is judged, if yes, the required record is found, and if not, the next record is continuously read. By adopting the method, if the record to be positioned is positioned at the tail part of the last binary log file, the operation is the traversal of all the binary log files, and the execution efficiency of the whole searching process is very low.

In order to improve the searching efficiency, the technical scheme of the application provides a method for determining the binary log file where the record to be searched is located firstly and then searching the record in the binary log file, so that the traversing operation of the binary log file can be reduced (only one binary log file needs to be traversed). The two steps of locating the file and locating the record are described in detail below.

Step 101-1: and searching the last file of which the timestamp of the first record is not later than the pre-specified timestamp in the binary log file set of the current MySQL database.

The specific searching process in this step is such that the first record in each binary log file is read, whether the timestamp of the first record is earlier than the pre-specified timestamp is judged, if so, the first record of the next binary log file is read, the same judgment is made, until the timestamp of the first record of a certain file is found to be later than the pre-specified timestamp, and the file searched before the current file is the binary log file to be searched in this step. If the timestamp of the first record of the current file is earlier than the pre-specified timestamp and no other binary log file exists in the subsequent file (the current file is the last file in the set), it indicates that the record to be searched is located in the current file, and the current file is the binary log file to be searched in this step.

It should be noted that, in the above process of locating the desired binary log file by searching, if the timestamp of the first record in the currently read binary log file is exactly equal to the pre-specified timestamp, indicating that the desired record has been found, the searching process may be terminated, and the subsequent step 101-2 (further searching for the first record with a timestamp that is not earlier than the pre-specified timestamp in the above found file) is not required to be performed, and step 101 is all completed.

In the above-described embodiment, if the binary log file to be searched is the last file in the binary log file set, the above search and location process needs to read the first record and perform timestamp judgment on each file, and in an application scenario of an actual MySQL database, there may be tens of files, dozens of files, or even more files in the binary log file set. In order to effectively improve this problem, the technical solution of the present application provides a preferred embodiment, that is: and searching the last file of which the timestamp of the first record is not later than the pre-specified timestamp by adopting a binary search method.

The binary search method, also called a binary search method, is a search method with high efficiency, and the basic idea is as follows: assuming that the data sequence is sorted in ascending order (the descending order is similar in reason), for a given value x, comparison is started from the middle position (as the current position) of the sequence, and if the current position value is equal to x, the search is successful; if x is smaller than the current position value, continuing the searching process in the first half section of the data sequence; if x is greater than the current position value, the search process continues in the second half of the data sequence until found. Because the binary search method adopts a half-by-half search mode, half of the data sequence to be searched is excluded every time, and the search times can be reduced. The time complexity of a conventional one-by-one comparison lookup algorithm is O (N) for looking up a number in a data sequence containing N numbers, but if the data sequence is ordered, the time complexity of the binary lookup is only O (log2N) (where 2 is the base of the logarithm). For example: for a sequence containing 7 data, the sequence can be completed only by comparing 7 times at most by adopting a conventional algorithm for comparing one by one, and can be completed only by comparing 3 times at most by adopting a binary search method.

Specifically, according to the technical scheme of the application, because the records in the binary log file are written in according to the time sequence of executing the change operation of the related database, and the binary log file is generated one by one according to the time sequence (the extension names of the binary log files are increased progressively), the time stamps of the first records in all the binary log files arranged according to the time sequence are also arranged according to the time sequence, and the basic requirement that the data sequence is required to be ordered by a binary search method is met, so that the binary log file required by the step can be searched by the binary search method, and the searching efficiency is effectively improved. Referring to fig. 2, which is a schematic diagram of a basic flow of the present application for searching for a last file whose timestamp of a first record is not later than the pre-specified timestamp by using a binary search method, since the binary search method is relatively mature, a brief description will be made below with reference to the drawing on how the binary search method is used in the present technical solution (for convenience of description, a search condition "the timestamp of the first record is not later than the pre-specified timestamp" is simplified and described as "not later than t 1"):

step 101-1-1: and assigning initial values to the algorithm related variables. Currently, there are N binary log files represented by array B, with B0 being the first file and B N-1 being the last file. l represents the starting position of the interval to be searched, h represents the ending position of the interval to be searched, and i represents the middle position of the interval to be searched. The pre-assigned timestamp is t 1.

Step 101-1-2: and positioning the middle position of the currently searched file interval, and acquiring the timestamp t of the first record of the corresponding file.

Step 101-1-3: if t = t1, it is indicated that the desired record (the first record in the B [ i ] file) was found, and the present search process is thus ended.

Step 101-1-4: and judging whether l is less than or equal to h, if so, continuing to execute the subsequent searching operation.

Step 101-1-5: if t < t1, i.e.: the timestamp of the current record is earlier than t1, but the B [ i ] file is not necessarily the last file satisfying the requirement of "not later than t 1", so it is also necessary to continue searching in the second half of the current file interval to see if there is a file whose first record has a timestamp later than the current record timestamp, but earlier than t1, and go to step 101-1-6 to execute.

Step 101-1-6: setting l = i +1, and in order to continue to search the starting position of the setting interval in the second half of the current file interval, going to step 101-1-2 to execute.

Step 101-1-7: if t > t1, namely: the timestamp of the current record is later than t1, the B [ i ] file does not meet the requirement of 'not later than t 1', the search should be continued in the first half of the current file interval, and the step 101-1-8 is carried out.

Step 101-1-8: setting h = i-1, and in order to continue to search the end position of the set interval in the first half of the current file interval, turning to step 101-1-2 to execute.

The above-described search process is always performed in a loop, and the process terminates in two cases: in the first case, when it is determined in step 101-1-3 that t = t1, B [ i ] is the last file meeting the requirement, and the first record in B [ i ] is the record to be searched in the technical solution of the present application, in this case, the subsequent step 101-2 may be skipped, and step 102 may be directly executed; in the second case, it is determined in step 101-1-4 that l > h (the starting position is already greater than the ending position), indicating that the binary search process is ended and that the current B [ i ] file is the last file satisfying the requirement of "no later than t 1".

Step 101-2: looking up a first record in the last file having a timestamp not earlier than the pre-specified timestamp.

Executing the step to show that a last file with a timestamp of a first record not later than the pre-specified timestamp is found in the binary log file set of the current MySQL database, and in the step, all records in the file need to be traversed from the first record of the last file to find the first record with the timestamp not earlier than the pre-specified timestamp.

It should be noted here that, in general, the first record with the timestamp equal to the pre-specified timestamp can be found in the last file, and the expression "not earlier than" (including both the case equal to and the case greater than) is adopted in this step, considering the special cases that may occur in the specific implementation process: in the binary log file set of the current MySQL database, a record whose timestamp is identical to the pre-specified timestamp cannot be found (for a detailed description, see the following description related to active/standby switching). In this case, in order to enable the technical solution of the present application to still find the starting point as accurate as possible and ensure that the subsequent step 102 of obtaining the incremental log can be smoothly executed, a manner of searching for the first record whose timestamp is not earlier than the pre-specified timestamp is adopted in this step.

For example, in a specific example of the present embodiment, the following is implemented: if the time stamp of the record is less than the pre-specified time stamp, the next record is continuously read, and the same judgment is made until the time stamp of the current record is greater than or equal to the pre-specified time stamp, the searching process is ended, and the current record is the first record of the last file, the time stamp of which is not earlier than the pre-specified time stamp.

So far, a corresponding record is found in the binary log file set of the current MySQL database according to the pre-specified timestamp, and then the function of obtaining the incremental log can be realized in the subsequent step 102 according to the found record.

In consideration of the fact that in practical application, an application program or system which needs to capture incremental binary log data from a MySQL database to meet related business requirements such as incremental subscription or consumption is usually installed on other equipment different from a server where the MySQL database is located, the incremental log data is captured from the MySQL main database through network connection under the conventional condition, and the method provided by the application is adopted to continue to capture the incremental log data from a standby database (namely, the current MySQL database described in the application) under the condition that main/standby switching occurs. In this case, the timestamp of the last record of the incremental log obtained from the MySQL master database is the pre-specified timestamp in this step. In order to implement the technical solution of the present application in the application scenario, four steps of determining the active/standby switch, recording the timestamp, establishing the data connection, and obtaining the binary log file list need to be executed before executing step 101, please refer to fig. 3, which is a flowchart of an embodiment of the foregoing processing procedure, and the following further describes with reference to fig. 3. For convenience of the following description, an application program or a system, in which the technical scheme of the present application is implemented in the process of grabbing the incremental log, is referred to as an incremental log grabbing task.

Step 100-1: and judging whether the MySQL database system executes the main/standby switching operation.

And the incremental log grabbing task adopts a mode similar to that of the MySQL standby database, and sends a data packet which encapsulates the COM _ BINLOG _ DUMP command to the MySQL main database according to the requirements of a MySQL network communication protocol, the data packet also encapsulates the file name and the offset of a binary log file from which incremental log data are expected to be acquired, and the MySQL main database returns the incremental log which meets the requirements to the incremental log grabbing task after receiving the data packet.

Considering that the MySQL master database may have a fault, in order to switch the operation of capturing the incremental log to the standby database (i.e. the switched master database) in time, the incremental log capturing task needs to monitor whether the MySQL master database system is switched or not. In a specific example of this embodiment, a specific character string "GroupKey" is used to represent a current primary and secondary databases in a meta information base of a database, and by detecting the character string, relevant information of the current primary and secondary databases may be obtained, and if it is found that an IP address of the primary database changes, it may be determined that the primary and secondary databases are switched, and the incremental log capture task needs to perform subsequent steps to switch the operation of capturing the incremental log to the secondary database.

Step 100-2: and recording the timestamp of the last record of the incremental log acquired from the MySQL master database as the pre-designated timestamp.

In order to switch the operation of obtaining the binlog incremental log from the MySQL master database to the MySQL standby database (i.e. the current MySQL database), the timestamp of the last record of the incremental log obtained from the MySQL master database needs to be recorded, so that the subsequent operation of searching for the start point in the binary log file set of the standby database according to the timestamp can be performed.

In this step, the timestamp of the last record of the incremental log obtained from the MySQL master database is recorded, that is, the timestamp information of the 0-3 byte of the head of the last record is recorded as the basis for positioning the start point in the binary log file set of the backup database, so that the incremental log obtained from the backup database and the incremental log obtained from the master database are ensured to be continuous as much as possible, and the phenomenon of data loss is avoided as much as possible. The timestamp obtained in this step is the pre-specified timestamp described in this application.

Step 100-3: and establishing data connection with the current MySQL database.

The incremental log grabbing task follows an interaction protocol between the MySQL master database and the MySQL slave database, and establishes data connection with the current MySQL database (after master-slave switching occurs, the original slave database becomes the current MySQL database of the application), that is, the incremental log grabbing task requests incremental log data from the current MySQL database by using the identity of the slave database.

In the step of establishing data connection with the current MySQL database, a relatively wide expression mode is adopted, in specific implementation, the step not only includes a connection establishment process, but also generally includes processes such as authentication, and the basic flow is as follows: firstly, an IP address of a server where the current MySQL database is located and a port number of the current MySQL database for providing database service need to be obtained, then a socket (socket) is created to establish data connection with the current MySQL database, and interactive processes such as handshaking, verification and the like are executed according to an interactive protocol between a MySQL master database and a MySQL slave database.

In order to complete the above connection establishment process, the IP address of the current MySQL database and the port number providing the database service must be acquired. In a specific example of this embodiment, a specific character string "GroupKey" is used to represent a current primary and secondary databases in a meta information base of a database, and by detecting the character string, relevant information of the current primary database and the secondary database can be obtained, and after primary and secondary switches occur, the original secondary database becomes the current primary database (the current MySQL database in this application), so that information related to the current MySQL database can be obtained from the character string, and the IP address and the port number can be extracted from the information. In other embodiments, other acquisition modes may be employed, such as: the data connection can be established directly using MySQL default port number 3306 without modifying the port settings in the MySQL configuration file.

Step 100-4: and acquiring a binary log file list of the current MySQL database.

In order to implement the technical solution of the present application, according to the timestamp information obtained in step 100-2, a location corresponding to the timestamp is searched in the binary log file set of the current MySQL database, and a binary log file list of the current MySQL database needs to be obtained, that is: determining the search range of step 101 in the technical solution of the present application.

In a specific example of this embodiment, the binary log file list is obtained as follows: the incremental log capture task sends a "show master logs" command to the current MySQL database based on the data connection established in step 100-3, and the current MySQL database returns its local binary log file list to the incremental log capture task after receiving the command, for example, in this example, the binary log file list received by the incremental log capture task includes 7 files, MySQL-bin.000001, MySQL-bin.000002, and.

In other embodiments, the binary log file list of the current MySQL database may also be obtained in other manners different from the specific example, for example, the binary log index file (e.g., MySQL-bin index) of the current MySQL database may be accessed in a file sharing manner, and the required file list information is read from the binary log index file.

So far, the incremental log crawling task may continue to execute step 101, and according to the obtained timestamp, a record corresponding to the timestamp is searched in the file set included in the obtained binary log file list, that is: the first record is looked up whose timestamp is no earlier than the pre-specified timestamp.

It was mentioned above in step 101 that finding the first record with a timestamp not earlier than the pre-specified timestamp, rather than directly finding the first record with a timestamp equal to the pre-specified timestamp, takes into account the special circumstances in the implementation that are associated with the master-slave switching scenario described above, as described in further detail below.

When a MySQL master database fails (for example, crashes), because a certain delay exists in the MySQL master-slave copying process, the situation that the latest incremental log before the main database crashes is not pulled to the local by the slave database exists, at this moment, master-slave switching occurs, the slave database becomes the master database, the database service is continuously provided, and new records are continuously written into the binary log files. In this case, the backup database loses a portion of the incremental log records that were not pending in the main database, and the incremental log crawling task may have successfully crawled the portion of the records. Therefore, when the incremental log grabbing task searches in the binary log file set of the standby database (i.e. the current MySQL database described in this application) according to the timestamp of the last record in the part of records, it is impossible to find a record whose timestamp is identical to the timestamp. In order to enable the technical solution of the present application to still find a location as accurate as possible under such a condition and ensure that the task of capturing the incremental log can be smoothly executed, a first record with a timestamp not earlier than the pre-specified timestamp is searched in step 101.

It should be clear that the technical solution of the present application is not limited to the above application scenario in which the active/standby switching occurs, but may also be applied to other application scenarios, and the above-mentioned steps are not all necessary. For example: the application program or the system implementing the technical scheme of the application is located locally (on the same server) in the current MySQL database, and an incremental log needs to be acquired according to specified time information, so that the substeps (step 100-1 to step 100-4) related to the master-slave switching and the data connection establishment are not required to be executed, and the record is searched in a local binary log file set directly in a traversal mode or in a mode of firstly positioning a file (step 101-1) and then positioning a record (step 101-2) in the embodiment. No matter what way to implement this step, as long as the first record with the timestamp not earlier than the pre-specified timestamp can be found, and variations of the above-described embodiments do not depart from the core of the technical solution of the present application and are within the scope of the present application.

Step 102: and acquiring the incremental log of the current MySQL database according to the file name of the binary log file where the record is located and the position of the record in the file.

Through step 101, a record corresponding to a pre-specified timestamp is found in the binary log file set of the current MySQL database, according to the format of the binlog file introduced above, reading the 13 th byte to the 16 th byte of the record head obtains the offset of the record in the current binary log file, according to the file name of the binary log file where the record is located and the offset, the site information of the pre-specified timestamp in the MySQL binary log file set is obtained, and according to the site information, the incremental log data of the current MySQL database located behind the site information can be further obtained.

In a specific implementation process, the incremental log data acquisition can be realized by sending a command for acquiring incremental log data to the current MySQL database. Taking the incremental log capture task described in this embodiment as an example, after the primary/standby switching occurs in the MySQL database system, the location where the record corresponding to the pre-specified timestamp is located in the MySQL-bin.000003 file and the offset is 126 bytes is found in step 101. The incremental log grabbing task sends a MySQL native command COM _ BINLOG _ DUMP to the current MySQL database through the data connection established with the current MySQL database, and carries the site information of the incremental log data to be acquired according to the format requirement of the command, namely: the file name is mysql-bin.000003 and the offset is 126 bytes. And after receiving the COM _ BINLOG _ DUMP command, the current MySQL server pushes incremental log data behind the location information to an incremental log grabbing task according to the location information carried by the command. Therefore, after the MySQL database system is subjected to main-standby switching, the incremental log capturing task also realizes the switching process from the main database to the standby database, so that the continuity and the correctness of capturing incremental log data are ensured to the maximum extent. And the incremental log grabbing task can further analyze the acquired incremental log data and perform necessary subsequent processing according to related service requirements.

The above-described manner of obtaining the incremental log is based on the task of capturing the incremental log, and needs to obtain the incremental log data of the current MySQL database by means of network communication (connection with the database). However, in other embodiments, the technical solution of the present application may also directly execute the operation of acquiring the incremental log data in the local of the MySQL database without the aid of a network. For example, the function of obtaining incremental log data of the current MySQL database in this step may also be implemented by opening a binary log file from which incremental log data is desired to be obtained locally using fopen () function or similar function, positioning a file read-write pointer to an offset position from which incremental log data is desired to be obtained through seek () function or similar function, and then calling fread () function or similar function to read the required data from the specified offset position according to the format of the binary log file.

In summary, whether a local manner or a network manner is adopted, and whether the triggering event of the technical scheme of the present application is the active/standby switching event or other events described in this embodiment, as long as a site (file name + offset) needs to be determined in the binary log file set of MySQL according to the time information, and the MySQL incremental log data is acquired based on the site, the technical scheme of the present application may be adopted.

In the foregoing embodiment, a method for obtaining a MySQL binlog incremental log is provided, and correspondingly, the present application also provides a device for obtaining a MySQL binlog incremental log.

Please refer to fig. 4, which is a schematic diagram of an embodiment of an apparatus for acquiring a MySQL binlog delta log provided by the present application. Since the device embodiment is basically similar to the method embodiment, the description is relatively simple, and the relevant portions only need to refer to the corresponding description of the method embodiment. The device embodiments described below are merely illustrative.

An apparatus for obtaining MySQL binlog incremental logs of this embodiment includes: a record searching unit 401, configured to search, in a binary log file set of a current MySQL database, a first record whose timestamp is not earlier than a pre-specified timestamp; an incremental log obtaining unit 402, configured to obtain an incremental log of the current MySQL database according to the file name of the binary log file in which the record is located and the position of the record in the file, which are found by the record searching unit.

Optionally, the record searching unit includes:

Optionally, the apparatus includes:

Optionally, the connection establishing unit includes:

Optionally, the apparatus includes:

Although the present application has been described with reference to the preferred embodiments, it is not intended to limit the present application, and those skilled in the art can make variations and modifications without departing from the spirit and scope of the present application, therefore, the scope of the present application should be determined by the claims that follow.

In a typical configuration, a computing device includes one or more processors (CPUs), input/output interfaces, network interfaces, and memory.

The memory may include forms of volatile memory in a computer readable medium, Random Access Memory (RAM) and/or non-volatile memory, such as Read Only Memory (ROM) or flash memory (flash RAM). Memory is an example of a computer-readable medium.

1. Computer-readable media, including both non-transitory and non-transitory, removable and non-removable media, may implement information storage by any method or technology. The information may be computer readable instructions, data structures, modules of a program, or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), Static Random Access Memory (SRAM), Dynamic Random Access Memory (DRAM), other types of Random Access Memory (RAM), Read Only Memory (ROM), Electrically Erasable Programmable Read Only Memory (EEPROM), flash memory or other memory technology, compact disc read only memory (CD-ROM), Digital Versatile Discs (DVD) or other optical storage, magnetic cassettes, magnetic tape magnetic disk storage or other magnetic storage devices, or any other non-transmission medium that can be used to store information that can be accessed by a computing device. As defined herein, computer readable media does not include non-transitory computer readable media (transient media), such as modulated data signals and carrier waves.

2. As will be appreciated by one skilled in the art, embodiments of the present application may be provided as a method, system, or computer program product. Accordingly, the present application may take the form of an entirely hardware embodiment, an entirely software embodiment or an embodiment combining software and hardware aspects. Furthermore, the present application may take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, and the like) having computer-usable program code embodied therein.

Claims

1. A method for obtaining MySQL binlog incremental logs, comprising:

when the MySQL master database fails, searching a second record with a timestamp equal to a pre-specified timestamp in a binary log file set of the current MySQL database;

when the MySQL master database fails, taking a MySQL standby database which takes over the MySQL master database and continues to provide database services as a current MySQL database; in a binary log file set of a current MySQL database, searching a first record of which the timestamp is not earlier than a pre-specified timestamp, wherein the first record comprises: searching the last file of which the timestamp of the first record is not later than the pre-specified timestamp in the binary log file set of the current MySQL database; searching for a first record in the last file whose timestamp is not earlier than the pre-specified timestamp;

and acquiring the incremental log of the current MySQL database according to the file name of the binary log file where the first record or the second record is located and the position of the first record or the second record in the file.

2. The method of claim 1, wherein a binary search method is used when searching for a last file with a timestamp of a first record that is not later than the pre-specified timestamp in the binary log file set of the current MySQL database.

3. The method for obtaining MySQL binlog delta logs according to any of claims 1-2, comprising:

establishing data connection with the current MySQL database;

acquiring a binary log file list of the current MySQL database;

4. The method for obtaining a MySQL binlog delta log according to claim 3, wherein the establishing a data connection with the current MySQL database comprises:

5. The method according to claim 4, wherein the obtaining of the IP address of the server where the current MySQL database is located and the port number of the database service provided by the current MySQL database is implemented as follows:

6. The method of claim 3 for obtaining a MySQL binlog delta log, comprising:

7. The method of claim 6, wherein the following steps are performed before the step of recording the timestamp of the last record of the incremental log obtained from the MySQL master database as the pre-specified timestamp:

8. The method according to claim 7, wherein the determining whether the MySQL binlog incremental log executes the active/standby switching operation is implemented as follows:

9. An apparatus for obtaining a MySQL binlog delta log, comprising:

the record searching unit is used for taking over the MySQL standby database which continuously provides database service for the MySQL main database as the current MySQL database when the MySQL main database fails; searching a first record of which the time stamp is not earlier than a pre-specified time stamp in a binary log file set of the current MySQL database;

an incremental log obtaining unit, configured to obtain an incremental log of the current MySQL database according to the file name of the binary log file in which the record is located and the position of the record in the file, which are found by the record searching unit; the file searching subunit is used for searching the last file of which the timestamp of the first record is not later than the pre-specified timestamp in the binary log file set of the current MySQL database;

a record search execution subunit, configured to search, in the last file, a first record whose timestamp is not earlier than the pre-specified timestamp;

and when the MySQL master database does not fail, searching a second record with the timestamp equal to a pre-specified timestamp in a binary log file set of the current MySQL database.

10. The apparatus according to claim 9, wherein the file searching subunit is configured to search, by using a binary search method, a last file of the current MySQL database, for which a timestamp of a first record is not later than the pre-specified timestamp.

11. The apparatus for obtaining MySQL binlog delta log according to any of claims 9-10, comprising:

12. The apparatus for obtaining MySQL binlog delta log according to claim 11, wherein the connection establishing unit comprises:

13. The apparatus according to claim 12, wherein the connection information obtaining subunit is configured to read information in a meta information base related to the current MySQL database, and extract the IP address and the port number from the information.

14. The apparatus for obtaining a MySQL binlog delta log according to claim 11, comprising:

15. The apparatus for obtaining a MySQL binlog delta log according to claim 14, comprising:

16. The apparatus according to claim 15, wherein the active/standby switching determining unit is specifically configured to monitor whether a field stored in the meta information base and used for recording an IP address of a server in which the MySQL master database is located changes; and if so, judging that the MySQL database system executes the main/standby switching operation.