Methods of data manipulation and data recovery in distributed databases

Abstract

The present disclosure relates to a method for recovering data in a distributed database, comprising the steps of: determining, through a search server, a data manipulation command to be flashed back, fingerprint information corresponding to the data manipulation command, and routing information for routing to an underlying data storage database involved in the data manipulation command, based on keywords in a data manipulation command statement; determining, based on the fingerprint information corresponding to the data manipulation command to be flashed back, an operation log event containing the fingerprint information in each underlying data storage database pointed to by the routing information; generating, based on a transactional statement contained in the operation log event, a reverse statement of the transactional statement; and executing the reverse statement in the underlying data storage database.

Description

Technical Field

[0001] The present disclosure relates generally to distributed databases, and more particularly to data manipulation and data recovery in distributed databases. Background Art

[0002] In a distributed database, there is a possibility that data in the distributed database needs to be restored. For example, in the case where a data manipulation command is an erroneous operation, the user may want to restore the data in the distributed database to the state before the erroneous operation.

[0003] A distributed database usually contains multiple underlying databases. Most existing recovery tools support data recovery of a single underlying database. For example, binlog2sql, a commonly used data recovery tool in the industry, generates reverse statements by serially parsing the binary log files of the underlying database, and finds matching error statements through manual comparison.

[0004] In a distributed database environment, a database administrator (DBA) cannot quickly locate the specific physical node of the underlying database where an erroneous statement is executed and the specific information of the executed statement.

[0005] In addition, since the search conditions supported by binlog2sql are relatively rough, there are too many interference results, and manual operations are prone to missing or misselecting data, causing secondary data pollution.

[0006] In addition, since the distributed database involves multiple underlying databases, in order to recover the data in the distributed database, corresponding recovery operations need to be performed in each underlying database, which is time-consuming and may result in excessively long failure times of the distributed database.

[0007] The industry needs a technology that can quickly help DBAs recover erroneous data in distributed databases, shorten failure time, and reduce the impact on production applications. Summary of the invention

[0008] A brief overview of the disclosure is given below in order to provide a basic understanding of some aspects of the disclosure. However, it should be understood that this overview is not an exhaustive overview of the disclosure. It is not intended to identify the key or important parts of the disclosure, nor is it intended to limit the scope of the disclosure. Its purpose is simply to give some concepts of the disclosure in a simplified form as a prelude to a more detailed description given later.

[0009] According to one aspect of the present disclosure, there is provided a method for executing data manipulation commands in a distributed database, comprising the steps of: receiving a data manipulation command for a distributed database; executing the data manipulation command in the distributed database; storing in a search server a data manipulation command statement, routing information for routing to an underlying data storage database involved in the data manipulation command, and fingerprint information that globally and uniquely identifies the execution of the data manipulation command; and storing operation log events in the underlying data storage database, the operation log events comprising at least a transactional statement for operating the underlying data storage database and the fingerprint information.

[0010] According to another aspect of the present disclosure, a method for recovering data in a distributed database is provided, comprising the steps of: determining, through a search server, based on keywords in a data manipulation command statement, a data manipulation command to be flashed back, fingerprint information corresponding to the data manipulation command, and routing information for routing to an underlying data storage database involved in the data manipulation command; determining, based on the fingerprint information corresponding to the data manipulation command to be flashed back, an operation log event containing the fingerprint information in each underlying data storage database pointed to by the routing information; generating, based on a transactional statement contained in the operation log event, a reverse statement of the transactional statement; and executing the reverse statement in the underlying data storage database.

[0011] According to another aspect of the present disclosure, a device for executing data manipulation commands in a distributed database is provided, comprising: a memory on which instructions are stored; and a processor configured to execute the instructions stored on the memory to execute a method for executing data manipulation commands in a distributed database.

[0012] According to another aspect of the present disclosure, a device for recovering data in a distributed database is provided, comprising: a memory having instructions stored thereon; and a processor configured to execute the instructions stored on the memory to perform a method for recovering data in a distributed database.

[0013] According to another aspect of the present disclosure, a computer-readable storage medium is provided, comprising computer-executable instructions, which, when executed by one or more processors, enable the one or more processors to perform any of the above methods.

[0014] Other features and advantages of the present invention will become apparent from the following detailed description of preferred embodiments of the present invention. BRIEF DESCRIPTION OF THE DRAWINGS

[0015] The accompanying drawings, which constitute a part of the specification, illustrate embodiments of the present disclosure and, together with the description, serve to explain the principles of the present disclosure.

[0016] The present disclosure may be more clearly understood from the following detailed description with reference to the accompanying drawings, in which:

[0017] Figure 1 A structural block diagram of a system for implementing the present invention is shown;

[0018] Figure 2 A flow chart showing execution of data manipulation commands in a distributed database;

[0019] Figure 3 A flow chart for recovering data in a distributed database is shown.

[0020] Figure 4 An exemplary configuration of a computing device capable of implementing an embodiment according to the present disclosure is shown. DETAILED DESCRIPTION

[0021] The following detailed description is made with reference to the accompanying drawings, and the following detailed description is provided to assist in a comprehensive understanding of the various exemplary embodiments of the present disclosure. The following description includes various details to assist in understanding, but these details are considered to be examples only and not to limit the present disclosure, which is defined by the appended claims and their equivalents. The words and phrases used in the following description are only used to enable a clear and consistent understanding of the present disclosure. In addition, for the sake of clarity and brevity, descriptions of well-known structures, functions, and configurations may be omitted. Those of ordinary skill in the art will recognize that various changes and modifications may be made to the examples described herein without departing from the spirit and scope of the present disclosure.

[0022] Figure 1 The structural block diagram of the system for implementing the present invention is shown. In the system for implementing the present invention, the client 101 is connected to the data access agent 102 for communication, and the client 101 is connected to the search server 103 for communication; the data access agent 102 is connected to the distributed database 104 including multiple underlying data storage databases 1041-1043 for communication; and the data access agent 102 is connected to the search server 103 for communication.

[0023] The client 101 is used for users to interact with the system, including inputting commands, displaying data from the system, and so on.

[0024] As a preferred example, the data access agent 102 is implemented as a middleware for accessing the distributed data 104. For example, the middleware for accessing the distributed data 104 can be implemented by Java. The programming language suitable for implementing the middleware for accessing the distributed data 104 is not limited to the Java language, and the middleware for accessing the distributed data 104 can also be implemented by any other programming language, such as Python, C#, C++, C, etc.

[0025] As a preferred example, the search server 103 is implemented as Elasticsearch. Elasticsearch is one of the currently widely used open source search engines, which is developed based on Lucene. Elasticsearch is a distributed search and analysis engine that can be used for full-text retrieval, structured retrieval and analysis, and can combine the three.

[0026] The distributed database 104 may be any distributed database, including but not limited to a MySQL database. For example, the distributed database 104 may also be a distributed database (ms SQL) provided by Microsoft or a distributed database (Oracle database) provided by Oracle.

[0027] Figure 2 A flow chart for executing data manipulation commands in a distributed database is shown.

[0028] The following will be combined Figure 2 How the present invention executes data manipulation commands in a distributed database is described in detail.

[0029] In step S201, a data manipulation command is received from the client 101. For example, the data manipulation command is: insert into schema1.table1(id,name,shardingId)values(1,"test1"),(2,"test2"),(3,"test3").

[0030] In step S202, the data manipulation command is executed in the distributed database. Specifically, according to the library table sharding rule of the distributed database (for example, by taking the modulus of the number of sub-libraries), the routing information for routing to the underlying data storage database (for example, DB1, DB2, DB3) involved in executing the data manipulation command can be obtained. The library table sharding rule of the distributed database can be set differently according to the specific application, and is therefore not limited to taking the modulus of the number of sub-libraries, but may be any other method. Based on the routing information, the corresponding data can be manipulated in the underlying data storage database involved in the data manipulation command. The data manipulation command includes, for example, a command for inserting records in a distributed database, a command for updating records in a distributed database, and a command for deleting records in a distributed database.

[0031] In step S203, the statement of the data manipulation command, the routing information for routing to the underlying data storage database involved in the data manipulation command, and the fingerprint information that globally uniquely identifies the execution of the data manipulation command are stored in the search server. More preferably, the execution start time of the data manipulation command and the execution end time of the data manipulation command can be further stored in the search server. That is, the statement of the data manipulation command, the execution start time of the data manipulation command, the execution end time of the data manipulation command, the routing information for routing to the underlying data storage database involved in the data manipulation command, and the fingerprint information that globally uniquely identifies the execution of the data manipulation command are stored in the search server. The search server is preferably an Elasticsearch search server. When executing the above exemplary data manipulation command, the following information is stored in the search server.

[0032]

[0033] Among them, the statements of data manipulation commands are recorded as follows:

[0034] "sql":insert into schema1.table1(id,name,shardingID)values(1,'test1',1),(2,'test',2),(3,'test3',3)

[0035] The execution start time of the data manipulation command is recorded as: "startTime": "1593319719474".

[0036] The execution end time of the data manipulation command is recorded as: "endTime": "1593319750797".

[0037] The routing information record for routing to the underlying data storage database involved in the data manipulation command is: "routeIds": "DB1, DB2, DB3".

[0038] The fingerprint information record that globally and uniquely identifies the execution of the data manipulation command is: “identity”: “6875f744-2019-44e8-871d-bc62714801bc”.

[0039] As an example, the search server is an Elasticsearch search server.

[0040] In step S204, an operation log event is stored in the underlying data storage database, wherein the operation log event at least includes a transactional statement for operating the underlying data storage database and the fingerprint information. For example, when executing the above exemplary data manipulation command, the following information is stored as an operation log event in the underlying data storage database DB1.

[0041] "Original statement: / *{"identity":"6875f744-2019-44e8-871d-bc62714801bc"}* / insert into schema1.table1(id,name,shardingID)values(1,'test1',1);

[0042] Database: shema1

[0043] Table name: table1

[0044] Field name: id, name, shardingID

[0045] Field type: long, varchar, long

[0046] Modified field value: 1,'test1',1

[0047] Field value before modification: null

[0048] Execution time: 1593319719474

[0049] Total transaction execution time: 1"

[0050] The above operation log event contains transactional statements (insertinto schema1.table1(id,name,shardingID)values(1,'test1',1)) that operate on the underlying data storage database and fingerprint information ("identity":"6875f744-2019-44e8-871d-bc62714801bc").

[0051] As an example, the distributed database of the present invention is a MySQL database, and the operation log events are stored in the form of binary files.

[0052] The present invention uses the above method to execute data manipulation commands in a distributed database, especially storing corresponding fingerprint information in a search server and an underlying data storage database, in order to facilitate the implementation of the method for recovering data in a distributed database as described below.

[0053] The following will be combined Figure 3 A method for recovering data in a distributed database according to the present invention is specifically described.

[0054] In step S301, according to the keywords in the data manipulation command statement, the data manipulation command to be executed for flashback operation, the fingerprint information corresponding to the data manipulation command, and the routing information for routing to the underlying data storage database involved in the data manipulation command are determined by the search server. More preferably, according to the keywords in the data manipulation command statement and the time range for executing the data manipulation command, the data manipulation command to be executed for flashback operation, the fingerprint information corresponding to the data manipulation command, and the routing information for routing to the underlying data storage database involved in the data manipulation command are determined by the search server. The keywords in the data manipulation command statement insert into schema1.table1(id,name,shardingID)values(1,'test1',1),(2,'test',2),(3,'test3',3) may refer to any word appearing in the string. The time range for executing the data manipulation command may refer to the time range into which the execution start time of the data manipulation command and / or the execution end time of the data manipulation command fall.

[0055] For example, users can use Elasticsearch's search device to quickly match the appropriate result set based on user-familiar business search conditions such as the transaction's database name, table name, statement type, statement execution time range, and original statement keywords through Elasticsearch's powerful search capabilities. Users can accurately find erroneous data manipulation command statements based on the original statement information in the result set.

[0056] Compared with the prior art that uses obscure search conditions (such as operation log file name, operation log location information, etc.), the present invention searches based on business keywords familiar to users and the time range for executing data manipulation commands. Therefore, the present invention is easier to use and can improve the efficiency of data recovery.

[0057] As an example, based on the keywords in the data manipulation command statement and the time range of executing the data manipulation command, the following specific information can be determined by searching the server. For example, the data manipulation command to perform the flashback operation is: "insert into schema1.table1(id,name,shardingID)values(1,'test1',1),(2,'test',2),(3,'test3',3); the fingerprint information corresponding to the data manipulation command is "identity":"6875f744-2019-44e8-871d-bc62714801bc"; and the routing information for routing to the underlying data storage database involved in the data manipulation command is "routeIds":"DB1,DB2,DB3".

[0058] In step S302, based on the fingerprint information corresponding to the data manipulation command to perform the flashback operation, the operation log events containing the fingerprint information in each underlying data storage database pointed to by the routing information are determined.

[0059] For example, according to the Elasticsearch result set filtered by the user and the routing information, multiple subtasks are obtained by splitting. The data access agent 102 can execute the multiple subtasks in parallel through multiple threads to improve the efficiency of transaction recovery.

[0060] For example, the data access agent 102 reads the operation log events stored in the form of binary files from the underlying data storage database as a database user with slave permissions.

[0061] As an example, in DB1, which is one of the underlying data storage databases pointed to by the above routing information, the operation log event containing the above fingerprint information "identity": "6875f744-2019-44e8-871d-bc62714801bc" is as follows.

[0062] "Original statement: / *{"identity":"6875f744-2019-44e8-871d-bc62714801bc"}* / insert into schema1.table1(id,name,shardingID)values(1,'test1',1);

[0063] Database: shema1

[0064] Table name: table1

[0065] Field name: id, name, shardingID

[0066] Field type: long, varchar, long

[0067] Modified field value: 1,'test1',1

[0068] Field value before modification: null

[0069] Execution time: 1593319719474

[0070] Total transaction execution time: 1"

[0071] Thus, the operation log events related to the sub-transactions of the misoperated distributed transaction can be obtained. According to the operation log events, the original statement information of the executed statement, the metadata information of the table, the field values ​​before and after the transaction statement is executed, the execution time and other information can be parsed.

[0072] In a distributed database, the execution and / or flashback of a data manipulation command may involve multiple underlying data storage databases. For example, the above data manipulation command "insert into schema1.table1(id,name,shardingID)values(1,'test1',1),(2,'test',2),(3,'test3',3) involves data storage databases DB1, DB2 and DB3. Operation log events containing certain fingerprint information can be determined in parallel in the multiple data storage databases involved through multi-threading. For example, the operation log events containing fingerprint information in the read binary file can be determined based on the time range and the fingerprint information with the help of binlog2sql.

[0073] In step S303, a reverse statement of the transactional statement is generated according to the transactional statement contained in the operation log event. For example, a reverse flashback statement can be generated according to the operation log event information obtained above. For example, according to the transactional statement insertinto schema1.table1(id,name,shardingID)values(1,'test1',1) contained in the above operation log event in the underlying data storage database DB1 as an example, a reverse statement deleteid,name,shardingId from schema1.table1 where id=1 is generated. Similarly, the reverse statement of the transactional statement can be generated in multiple data storage databases involved in parallel through multiple threads. For example, binlog2sql can be used to parse the transactional statement from the operation log event and generate the reverse statement of the transactional statement.

[0074] In step S304, the above reverse statement delete id, name, shardingId from schema1.table1 where id = 1 is executed in the underlying data storage database. The reverse statement can also be executed in multiple data storage databases involved in parallel through multiple threads.

[0075] The operations of steps S302 to S304 are all performed on each underlying data storage database. For example, for a MySQL database, the XA distributed transaction feature of MySQL can be used to ensure the synchronous flashback of multiple underlying data storage databases to avoid partial flashback failure.

[0076] The above specifically describes the steps of performing a flashback operation for a single data manipulation command. When a flashback operation is to be performed for multiple data manipulation commands, the flashback operation is performed sequentially for one of the multiple data manipulation commands in the reverse order of executing the multiple data manipulation commands.

[0077] Compared with the prior art in which a user directly performs data recovery in each underlying data storage database, the present invention accesses the underlying data storage database through a data access agent, and accurately locates the distributed sub-transaction in the underlying data storage database that is to perform a flashback operation by relying on fingerprint information that globally and uniquely identifies the execution of a data manipulation command, so that the present invention can perform data recovery operations more accurately and avoid data omission or misselection that may be caused by manual operation of the underlying data storage database. Moreover, compared with the prior art in which a user directly performs data recovery in each underlying data storage database, the present invention can perform data recovery operations more efficiently, thereby shortening the time required for data recovery.

[0078] Figure 4 An exemplary configuration of a computing device 400 capable of implementing embodiments according to the present disclosure is shown.

[0079] The computing device 400 is an example of a hardware device to which the above aspects of the present disclosure can be applied. The computing device 400 can be any machine configured to perform processing and / or computing. The computing device 400 can be, but is not limited to, a workstation, a server, a desktop computer, a laptop computer, a tablet computer, a personal data assistant (PDA), a smart phone, a vehicle-mounted computer, or a combination thereof.

[0080] like Figure 4 As shown, computing device 400 may include one or more components that may be connected or communicated with bus 402 via one or more interfaces. Bus 402 may include, but is not limited to, Industry Standard Architecture (ISA) bus, Micro Channel Architecture (MCA) bus, Enhanced ISA (EISA) bus, Video Electronics Standards Association (VESA) local bus, and Peripheral Component Interconnect (PCI) bus, etc. Computing device 400 may include, for example, one or more processors 404, one or more input devices 406, and one or more output devices 408. One or more processors 404 may be any kind of processor, and may include, but is not limited to, one or more general-purpose processors or special-purpose processors (such as special-purpose processing chips). Processor 404 may be configured to execute, for example, Figure 2 or Figure 3 The method shown. Input device 406 can be any type of input device capable of inputting information to a computing device, and can include, but is not limited to, a mouse, a keyboard, a touch screen, a microphone, and / or a remote controller. Output device 408 can be any type of device capable of presenting information, and can include, but is not limited to, a display, a speaker, a video / audio output terminal, a vibrator, and / or a printer.

[0081] The computing device 400 may also include or be connected to a non-transitory storage device 414, which may be any non-transitory storage device that can implement data storage, and may include, but is not limited to, a disk drive, an optical storage device, a solid-state memory, a floppy disk, a flexible disk, a hard disk, a tape, or any other magnetic medium, a compressed disk or any other optical medium, a cache memory, and / or any other storage chip or module, and / or any other medium from which a computer can read data, instructions, and / or code. The computing device 400 may also include a random access memory (RAM) 410 and a read-only memory (ROM) 412. The ROM 412 may store programs, utilities, or processes to be executed in a non-volatile manner. The RAM 410 may provide volatile data storage and store instructions related to the operation of the computing device 400. The computing device 400 may also include a network / bus interface 416 coupled to a data link 418. The network / bus interface 416 may be any type of device or system capable of enabling communication with an external device and / or a network, and may include but is not limited to a modem, a network card, an infrared communication device, a wireless communication device, and / or a chipset (such as a Bluetooth TM equipment, 802.11 equipment, WiFi equipment, WiMax equipment, cellular communication facilities, etc.).

[0082] The present disclosure may be implemented as any combination of an apparatus, a system, an integrated circuit, and a computer program on a non-transitory computer-readable medium. One or more processors may be implemented as an integrated circuit (IC), an application specific integrated circuit (ASIC), or a large scale integrated circuit (LSI), a system LSI, a super LSI, or a super LSI component that performs some or all of the functions described in the present disclosure.

[0083] The present disclosure includes the use of software, applications, computer programs, or algorithms. The software, applications, computer programs, or algorithms may be stored on a non-transitory computer-readable medium to enable a computer such as one or more processors to perform the above steps and the steps described in the accompanying drawings. For example, one or more memories store the software or algorithm in executable instructions, and one or more processors may associate a set of instructions to execute the software or algorithm to provide various functions according to the embodiments described in the present disclosure.

[0084] Software and computer programs (which may also be referred to as programs, software applications, applications, components or code) include machine instructions for a programmable processor and may be implemented in a high-level procedural language, an object-oriented programming language, a functional programming language, a logic programming language, or an assembly language or a machine language. The term "computer-readable medium" refers to any computer program product, apparatus or device for providing machine instructions or data to a programmable data processor, such as magnetic disks, optical disks, solid-state storage devices, memories and programmable logic devices (PLDs), including computer-readable media that receive machine instructions as computer-readable signals.

[0085] For example, computer readable media may include dynamic random access memory (DRAM), random access memory (RAM), read-only memory (ROM), electrically erasable read-only memory (EEPROM), compact disk read-only memory (CD-ROM) or other optical disk storage devices, magnetic disk storage devices or other magnetic storage devices, or any other medium that can be used to carry or store the desired computer readable program code in the form of instructions or data structures and can be accessed by a general or special purpose computer or a general or special purpose processor. As used herein, disk or disc includes compact disc (CD), laser disc, optical disc, digital versatile disc (DVD), floppy disk and Blu-ray disc, where disks usually reproduce data magnetically, while discs reproduce data optically by laser. Combinations of the above are also included in the scope of computer readable media.

[0086] The subject matter of the present disclosure is provided as an example of devices, systems, methods and programs for performing the features described in the present disclosure. However, in addition to the above-mentioned features, other features or variations may also be expected. It is expected that the implementation of the components and functions of the present disclosure may be completed with any emerging technology that may replace any of the above-mentioned implementation technologies.

[0087] In addition, the above description provides examples without limiting the scope, applicability or configuration set forth in the claims. Without departing from the spirit and scope of the present disclosure, the functions and arrangements of the elements discussed may be changed. Various embodiments may appropriately omit, replace or add various processes or components. For example, features described with respect to certain embodiments may be combined in other embodiments.

[0088] Similarly, while operations are depicted in a particular order in the drawings, this should not be understood as requiring that such operations be performed in the particular order shown or in sequential order, or that all illustrated operations be performed to achieve desired results. In certain circumstances, multitasking and parallel processing can be advantageous.

Claims

1. A method for recovering data in a distributed database, comprising the steps of: receiving data manipulation commands for a distributed database; Execute the data manipulation command in the distributed database; storing in the search server a data manipulation command statement, routing information for routing to an underlying data storage database involved in the data manipulation command, and fingerprint information that globally and uniquely identifies the execution of the data manipulation command; Furthermore, storing operation log events in the underlying data storage database, wherein the operation log events at least include transactional statements for operating the underlying data storage database and the fingerprint information; According to the keywords in the data manipulation command statement and the time range for executing the data manipulation command, the data manipulation command to be flashed back, the fingerprint information corresponding to the data manipulation command, and the routing information for routing to the underlying data storage database involved in the data manipulation command are determined by the search server; According to the fingerprint information corresponding to the data manipulation command to be flashed back, multiple threads are used to determine in parallel the operation log events containing the fingerprint information in each underlying data storage database pointed to by the routing information; Generate a reverse statement of the transactional statement in parallel based on the transactional statement included in the operation log event through multiple threads; as well as Multiple threads execute the reverse statement in parallel in the underlying data storage database.

2. The method according to claim 1, wherein: The operation log events stored in the form of binary files are read from the underlying data storage database by a database user with slave permissions.

3. The method according to claim 1, wherein: According to the time range and the fingerprint information, the operation log event containing the fingerprint information in the read binary file is determined by means of binlog2sql.

4. The method according to claim 1, wherein: By means of binlog2sql, a transactional statement is parsed from the operation log event and a reverse statement of the transactional statement is generated.

5. The method according to claim 1, further comprising: The execution start time of the data manipulation command and the execution end time of the data manipulation command are stored in the search server.

6. The method according to claim 1, wherein: The distributed database is a MySQL database, and the operation log events are stored in the form of binary files.

7. The method according to claim 1, wherein: The search server is an Elasticsearch search server.

8. A device for recovering data in a distributed database, comprising: a memory having instructions stored therein; as well as A processor configured to execute instructions stored on the memory to perform the method according to any one of claims 1 to 7.

9. A computer-readable storage medium comprising computer-executable instructions, which, when executed by one or more processors, cause the one or more processors to perform the method according to any one of claims 1 to 7.

Images