Database processing method, device, and storage medium

Abstract

This application discloses a database processing method, device, and storage medium. The method includes: in a first server where the database resides, in response to a received command to generate a rollback statement for at least one operation, obtaining logs of the at least one operation from the database's operation logs, and generating a rollback statement for the at least one operation based on the logs of the at least one operation, wherein the operation statement corresponding to the operation is an operation statement executed after review; and outputting the rollback statement for the at least one operation in the first server. Thus, compared to related technologies where the rollback statement is generated by an review platform and returned for page display, directly generating and outputting the rollback statement on the first server side takes less time, allows for faster data recovery, and is more efficient.

Description

Technical Field

[0001] This application relates to the field of computers, and more particularly to a database processing method, device, and storage medium. Background Technology

[0002] In related technologies, database operation statements can be submitted to auditing platforms such as Yearing for review and execution. If the operation is erroneous, the auditing platform can generate a rollback statement and return it for display on the page. Then, the user can select and execute the displayed rollback statement to restore the data. However, displaying the generated rollback statement on the page takes time; if it cannot be displayed promptly, data recovery efficiency will be low. Summary of the Invention

[0003] The purpose of this application is to provide a database processing method, device, and storage medium to solve the problem of low data recovery efficiency in related technologies.

[0004] The objective of this application is achieved through the following technical solution:

[0005] In a first aspect, embodiments of this application provide a database processing method, including:

[0006] In the first server where the database is located, in response to the received command to generate a rollback statement for at least one operation, the log of the at least one operation is obtained from the operation log of the database, and a rollback statement for the at least one operation is generated based on the log of the at least one operation, wherein the operation statement corresponding to the operation is an operation statement that has been reviewed and executed.

[0007] In the first server, a rollback statement for the at least one operation is output.

[0008] In one possible implementation, before retrieving the logs of the at least one operation from the operation logs of the database in response to a received command to generate a rollback statement for at least one operation in the first server where the database resides, and before generating the rollback statement for the at least one operation based on the logs of the at least one operation, the method further includes:

[0009] In the first server, the operation statement corresponding to the operation is reviewed and executed.

[0010] In one possible implementation, before retrieving the logs of the at least one operation from the operation logs of the database in response to a received command to generate a rollback statement for at least one operation in the first server where the database resides, and before generating the rollback statement for the at least one operation based on the logs of the at least one operation, the method further includes:

[0011] The operation statement corresponding to the at least one operation is sent to the operation statement review platform provided by the second server, so that the operation statement review platform reviews and executes the operation statement corresponding to the operation.

[0012] In one possible implementation, the step of retrieving logs of the at least one operation from the operation logs of the database in response to a received command to generate a rollback statement for at least one operation in the first server where the database resides, and generating a rollback statement for the at least one operation based on the logs of the at least one operation, includes:

[0013] When the number of operations reaches a preset number, in the first server where the database is located, in response to the received command to generate a rollback statement for at least one operation, the log of the at least one operation is obtained from the operation log of the database, and a rollback statement for the at least one operation is generated based on the log of the at least one operation.

[0014] In one possible implementation, sending the operation statement corresponding to the at least one operation to the operation statement review platform provided by the second server includes:

[0015] The operation statements corresponding to the at least one operation are divided into multiple work orders and sent to the operation statement review platform at different times. Each work order contains a portion of the operation statements corresponding to the operation, so that the operation statement review platform reviews the portion of the operation statements corresponding to the operation contained in the work order according to the time of receipt of the work order.

[0016] The step of receiving a rollback statement generation command for at least one operation of the database in the first server where the database resides includes:

[0017] When the operation statement corresponding to the at least one operation is divided into multiple work orders and sent to the operation statement review platform at different times, in the first server where the database is located, in response to the received command to generate the rollback statement of the at least one operation, the log of the at least one operation is obtained from the operation log of the database, and the rollback statement of the at least one operation is generated based on the log of the at least one operation.

[0018] In one possible implementation, the generation command carries the location and execution time period of the log for the at least one operation;

[0019] The step of obtaining the logs of the at least one operation from the operation logs of the database includes:

[0020] Based on the location and execution time period of the log of the at least one operation carried in the generation command, the log of the at least one operation is obtained from the operation log of the database.

[0021] In one possible implementation, it also includes:

[0022] Delete the rollback statements in the output of at least one operation that do not conform to the rollback requirements.

[0023] In one possible implementation, the operation log is a binary log.

[0024] In a second aspect, embodiments of this application provide an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement any of the methods described in the first aspect.

[0025] Thirdly, embodiments of this application provide a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements any of the methods described in the first aspect.

[0026] The advantages or beneficial effects of the above technical solutions include at least the following:

[0027] The system receives a command to generate a rollback statement for at least one operation from the first server where the database is located. Based on the logs of at least one operation obtained from the database's operation logs, the system generates a rollback statement and outputs it directly on the first server. Compared with related technologies where the auditing platform generates the rollback statement and returns it for page display, generating and outputting the rollback statement directly on the first server takes less time, allows for faster data recovery, and is more efficient.

[0028] The above overview is for illustrative purposes only and is not intended to be limiting in any way. In addition to the illustrative aspects, embodiments, and features described above, further aspects, embodiments, and features of this application will become readily apparent from the accompanying drawings and the following detailed description. Attached Figure Description

[0029] To more clearly illustrate the technical solutions of the embodiments of this application, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0030] Figure 1 This is a flowchart illustrating an exemplary database processing method provided in an embodiment of this application;

[0031] Figure 2 This is a schematic diagram of the structure of an exemplary electronic device provided in an embodiment of this application. Detailed Implementation

[0032] The technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0033] Figure 1 This is a flowchart illustrating an exemplary database processing method provided in an embodiment of this application. For example... Figure 1 As shown, this database processing method is executed on the first server side and includes at least the following steps:

[0034] Step 101: In the first server where the database is located, in response to the received command to generate a rollback statement for at least one operation, the log of the at least one operation is obtained from the operation log of the database, and a rollback statement for the at least one operation is generated based on the log of the at least one operation, wherein the operation statement corresponding to the operation is an operation statement that has been reviewed and executed.

[0035] The operations involved are database operations, such as adding, deleting, and modifying data. In practical applications, after the corresponding operation statement is verified to be correct, it can be executed to perform the operation on the database. At this time, the database operation log records the operations performed on the database. Therefore, on the first server where the database resides, when a command to generate a rollback statement for at least one operation is received, the logs of at least one operation can be obtained from the database operation log. Then, based on the logs of at least one operation, a rollback statement for that operation is generated. When an operation is erroneous, the corresponding rollback statement can be executed to restore the data to its state before the erroneous operation.

[0036] Step 102: In the first server, output the rollback statement for the at least one operation.

[0037] In this step, after generating at least one rollback statement for an operation on the first server, it is output directly to the first server. Specifically, the rollback statement can be output via command line.

[0038] In this embodiment, the first server where the database is located receives a command to generate a rollback statement for at least one operation. Based on the logs of at least one operation obtained from the database's operation logs, the rollback statement is generated and directly output on the first server. Compared with the related technology where the rollback statement is generated by the auditing platform and returned for page display, generating and outputting the rollback statement directly on the first server takes less time, can restore data more quickly, and is more efficient.

[0039] In an exemplary embodiment, before obtaining the logs of the at least one operation from the operation logs of the database in response to the received command to generate a rollback statement for at least one operation in the first server where the database is located, and before generating the rollback statement for the at least one operation based on the logs of the at least one operation, the above method may further include: sending the operation statement corresponding to the at least one operation to an operation statement review platform provided by a second server, so that the operation statement review platform reviews and executes the operation statement corresponding to the operation.

[0040] The operation statement review platform here is used to review and execute the operation statements corresponding to the operation.

[0041] For example, the database here is a MySQL database. MySQL is a relational database management system that can store data in different tables, increasing speed and improving flexibility. For example, the statement auditing platform could be Yearning MySQL, an SQL statement auditing platform that provides various functions such as query auditing and SQL auditing. It can, to some extent, bridge the gap between operations and development, offering a rich set of features. Examples include SQL querying, query exporting, query auto-completion, SQL auditing, workflow-based work orders, SQL statement detection, SQL statement execution, SQL rollback, historical audit records, query auditing, and more.

[0042] In practical applications, operation statements can be reviewed and executed through an operation statement review platform. If a rollback statement needs to be generated, it must be manually selected during the review submission process. In some application scenarios, the selection may be forgotten, in which case a rollback statement will not be generated. In this case, a rollback statement can be generated based on the aforementioned operation log to compensate for the inconvenience caused by forgetting to select the option. Furthermore, in other application scenarios, rollback statements can also be generated based on the aforementioned operation log, which will be explained in detail in the following examples.

[0043] In an exemplary embodiment, the step of retrieving logs of the at least one operation from the operation logs of the database in response to a received command to generate a rollback statement for at least one operation in the first server where the database resides, and generating a rollback statement for the at least one operation based on the logs of the at least one operation, can be specifically implemented as follows: when the number of operations reaches a preset number, in response to a received command to generate a rollback statement for at least one operation in the first server where the database resides, retrieving logs of the at least one operation from the operation logs of the database, and generating a rollback statement for the at least one operation based on the logs of the at least one operation.

[0044] In practical applications, because the operation statement review platform is on a second server, different from the primary server where the database resides, operation statements need to be submitted to the review platform via the network for review. If rollback statements are also required, the review platform needs to return the rollback statements via the network and display them on the page, where users can then select the rollback statement to execute to restore data. In scenarios with a large number of operation statements, there are also many corresponding rollback statements, and the time required for full display is also significant, greatly impacting data recovery efficiency. Therefore, when the number of operations reaches a preset limit—that is, in scenarios with a large number of operations—rollback statements can be generated directly on the primary server where the database resides, based on the operation logs, without going through the operation statement review platform, and then quickly generated and output, thereby improving data recovery efficiency.

[0045] The specific value of the preset quantity can be set according to the actual situation, and no specific limit is made here.

[0046] In an exemplary embodiment, the specific implementation of sending the operation statement corresponding to the at least one operation to the operation statement review platform provided by the second server may include: dividing the operation statement corresponding to the at least one operation into multiple work orders and sending them to the operation statement review platform at different times, with each work order containing a portion of the operation statement corresponding to the operation, so that the operation statement review platform reviews the portion of the operation statement corresponding to the operation contained in the work order according to the time of receipt of the work order.

[0047] Therefore, the specific implementation of receiving the rollback statement generation command for at least one operation of the database in the first server where the database is located may include: when the operation statement corresponding to the at least one operation is divided into multiple work orders and sent to the operation statement review platform at different times, in response to the received rollback statement generation command for at least one operation in the first server where the database is located, the log of the at least one operation is obtained from the operation log of the database, and the rollback statement of the at least one operation is generated based on the log of the at least one operation.

[0048] In practical applications, the operation statement review platform has implemented workflow-based work orders. This means that operation statements requiring review are submitted to the platform in the form of work orders, each containing a certain number of operation statements. For a project, multiple work orders may be submitted at different times, such as once in the morning and once in the afternoon, and different submitters may also be submitting work orders. The platform may receive multiple work orders in the morning, and reviews them according to the submission time order. When operation statements are reviewed across multiple work orders, the large time span means that if a problem is discovered requiring a rollback, a considerable amount of time is required until all work orders are processed, resulting in low data recovery efficiency. Alternatively, instead of generating rollback statements through the operation statement review platform, the rollback statements can be quickly generated and output directly on the primary server where the database resides, based on the operation logs. This speeds up recovery time, improves data recovery efficiency, can handle various emergencies, ensures normal business operations, and reduces business losses.

[0049] In an exemplary embodiment, before obtaining the log of the at least one operation from the operation log of the database in response to the received command to generate a rollback statement for at least one operation in the first server where the database is located, and generating the rollback statement for the at least one operation based on the log of the at least one operation, the above method may further include: reviewing and executing the operation statement corresponding to the operation in the first server.

[0050] In practical applications, in some special cases, it may be inconvenient to review operation statements through an auditing platform. Instead, it may be necessary to review and execute them directly on the primary server where the database resides. In such cases, rollback statements can be generated based on the operation logs to restore the data, which is very convenient. Here, direct review on the primary server where the database resides can be done manually. Once the manual review is confirmed to be successful, the operation statements can be executed.

[0051] In an exemplary embodiment, the generation command carries the location and execution time period of the logs for the at least one operation. Correspondingly, the specific implementation of obtaining the logs for the at least one operation from the database's operation logs may include: obtaining the logs for the at least one operation from the database's operation logs based on the location and execution time period of the logs for the at least one operation carried in the generation command.

[0052] In practical applications, the above operation logs are binary logs, for example, binlog logs. A binlog is a binary file used to record SQL statements used by users to update the database.

[0053] Correspondingly, a rollback statement for the at least one operation can be generated using binlog2sql based on the logs of the at least one operation.

[0054] binlog2sql is an open-source MySQL binlog parsing tool developed in Python. It can parse binlog into raw SQL statements and also supports parsing binlog into rollback SQL statements.

[0055] For example, the following generation command can be executed using binlog2sql to generate rollback statements:

[0056] python binlog2sql.py-h127.0.0.1-P3306-uadmin-p'admin'-dtest-t test3--start-file='mysql-bin.000002'--start-datetime--stop-datetime.

[0057] The "--start-datetime--stop-datetime" represents the execution time period, and "-h127.0.0.1-P3306-uadmin-p'admin'-dtest-t test3--start-file='mysql-bin.000002'" represents the location of the log for at least one operation.

[0058] In this embodiment, the log is located based on the position and time of the log in the generation command, thereby accurately generating rollback statements.

[0059] In an exemplary embodiment, the above method may further include: deleting rollback statements from the output rollback statements of the at least one operation that do not conform to the rollback requirements.

[0060] In practical applications, because logs are located based on time, it's possible that rollback statements might be generated for operations with similar timestamps. This doesn't meet rollback requirements and needs to be deleted to prevent accidental recovery. In implementation, user deletion operations can be detected, thereby removing rollback statements that don't conform to rollback needs.

[0061] In this embodiment, by deleting rollback statements that do not conform to the rollback requirements, erroneous recovery is avoided, thereby improving the accuracy of recovery.

[0062] This application also provides an electronic device, which includes a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, it implements the database processing method as described in any of the above embodiments.

[0063] Figure 2 This is a schematic diagram of the structure of an exemplary electronic device provided in an embodiment of this application. For example... Figure 2 As shown, the electronic device may include a processor 201, a communication interface 202, a memory 203, and a communication bus 204, wherein the processor 201, the communication interface 202, and the memory 203 communicate with each other via the communication bus 204. The processor 201 may call a computer program in the memory 203 to execute the database processing method in any of the above embodiments.

[0064] This application also provides a computer-readable storage medium storing a computer program thereon, which, when executed by a processor, implements the database processing method as described in any of the above embodiments.

[0065] In the database processing method, device, and storage medium provided in this application embodiment, the first server where the database is located receives a command to generate a rollback statement for at least one operation. Based on the logs of at least one operation obtained from the database's operation log, the rollback statement is generated and directly output on the first server. Compared with the related technology where the rollback statement is generated by the auditing platform and returned for page display, generating and outputting the rollback statement directly on the first server takes less time, can restore data more quickly, and is more efficient.

[0066] In the above embodiments, implementation can be achieved, in whole or in part, through software, hardware, firmware, or any combination thereof. When implemented in software, it can be implemented, in whole or in part, as a computer program product. A computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, all or part of the flow or function according to this application is generated. The computer can be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions can be stored in a computer-readable storage medium or transferred from one computer-readable storage medium to another.

[0067] In the description of this specification, the references to terms such as "one embodiment," "some embodiments," "example," "specific example," or "some examples," etc., indicate that a specific feature, structure, material, or characteristic described in connection with that embodiment or example is included in at least one embodiment or example of this application. Furthermore, the specific features, structures, materials, or characteristics described may be combined in any suitable manner in one or more embodiments or examples. Moreover, without contradiction, those skilled in the art can combine and integrate the different embodiments or examples described in this specification, as well as the features of those different embodiments or examples.

[0068] Furthermore, the terms "first" and "second" are used for descriptive purposes only and should not be construed as indicating or implying relative importance or implicitly specifying the number of technical features indicated. Thus, a feature defined as "first" or "second" may explicitly or implicitly include at least one of that feature. In the description of this application, "a plurality of" means two or more, unless otherwise explicitly specified.

[0069] Any process or method description in the flowchart or otherwise herein can be understood as representing a module, segment, or portion of code comprising one or more executable instructions for implementing a particular logical function or process. Furthermore, the scope of the preferred embodiments of this application includes additional implementations in which functions may be performed not in the order shown or discussed, including substantially simultaneously or in reverse order depending on the functionality involved.

[0070] The logic and / or steps represented in the flowchart or otherwise described herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can be embodied in any computer-readable medium for use by, or in conjunction with, an instruction execution system, apparatus or device (such as a computer-based system, a processor-included system or other system that can fetch and execute instructions from, an instruction execution system, apparatus or device).

[0071] It should be understood that various parts of this application can be implemented using hardware, software, firmware, or a combination thereof. In the above embodiments, multiple steps or methods can be implemented using software or firmware stored in memory and executed by a suitable instruction execution system. All or part of the steps of the methods in the above embodiments can be implemented by a program instructing related hardware, the program being stored in a computer-readable storage medium, which, when executed, includes one or a combination of the steps of the method embodiments.

[0072] Furthermore, the functional units in the various embodiments of this application can be integrated into a processing module, or each unit can exist physically separately, or two or more units can be integrated into a module. The integrated module can be implemented in hardware or as a software functional module. If the integrated module is implemented as a software functional module and sold or used as an independent product, it can also be stored in a computer-readable storage medium. This storage medium can be a read-only memory, a disk, or an optical disk, etc.

[0073] The above description is merely a specific embodiment of this application, but the scope of protection of this application is not limited thereto. Any person skilled in the art can easily conceive of various variations or substitutions within the technical scope disclosed in this application, and these should all be included within the scope of protection of this application. Therefore, the scope of protection of this application should be determined by the scope of the claims.

Claims

1. A database processing method, characterized in that, include: At least one operation statement is sent to the operation statement review platform provided by the second server, so that the operation statement review platform reviews and executes the operation statement corresponding to the operation. In the first server where the database is located, in response to the received command to generate a rollback statement for at least one operation, the log of the at least one operation is obtained from the operation log of the database, and a rollback statement for the at least one operation is generated based on the log of the at least one operation, wherein the operation statement corresponding to the operation is an operation statement that has been reviewed and executed. In the first server, a rollback statement for the at least one operation is output; The step of sending the operation statement corresponding to at least one operation to the operation statement review platform provided by the second server includes: The operation statements corresponding to the at least one operation are divided into multiple work orders and sent to the operation statement review platform at different times. Each work order contains a portion of the operation statements corresponding to the operation, so that the operation statement review platform reviews the portion of the operation statements corresponding to the operation contained in the work order according to the time of receipt of the work order. The step of receiving a rollback statement generation command for at least one operation of the database in the first server where the database resides includes: When the operation statement corresponding to the at least one operation is divided into multiple work orders and sent to the operation statement review platform at different times, in the first server where the database is located, in response to the received command to generate the rollback statement of the at least one operation, the log of the at least one operation is obtained from the operation log of the database, and the rollback statement of the at least one operation is generated based on the log of the at least one operation.

2. The method according to claim 1, wherein, Before, in the first server where the database resides, in response to a received command to generate a rollback statement for at least one operation, the process includes retrieving logs of the at least one operation from the database's operation logs, and generating a rollback statement for the at least one operation based on the logs of the at least one operation, the process further includes: When it is inconvenient to review the operation statement through the review platform, the operation statement corresponding to the operation is reviewed and executed in the first server.

3. The method according to claim 1, wherein, In the first server where the database resides, in response to a received command to generate a rollback statement for at least one operation, the process involves retrieving logs of the at least one operation from the database's operation logs, and generating rollback statements for the at least one operation based on these logs, including: When the number of operations reaches a preset number, in the first server where the database is located, in response to the received command to generate a rollback statement for at least one operation, the log of the at least one operation is obtained from the operation log of the database, and a rollback statement for the at least one operation is generated based on the log of the at least one operation.

4. The method according to claim 1, wherein, The generation command carries the location and execution time period of the log for at least one of the operations; The step of obtaining the logs of the at least one operation from the operation logs of the database includes: Based on the location and execution time period of the log of the at least one operation carried in the generation command, the log of the at least one operation is obtained from the operation log of the database.

5. The method according to claim 1, wherein, Also includes: Delete the rollback statements in the output of at least one operation that do not conform to the rollback requirements.

6. The method according to claim 1, wherein, The operation log is a binary log.

7. An electronic device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the method as described in any one of claims 1-6.

8. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the program is executed by the processor, it implements the method as described in any one of claims 1-6.

Images