MySQL database rapid recovery method based on Binlog

Abstract

The invention provides a MySQL database rapid recovery method based on a Bin log, and relates to the technical field of database management and data recovery, and the method comprises the following steps: screening and extracting log content in a corresponding time period from the Bin log of a MySQL database according to a time interval of occurrence of misoperation specified by a user; the extracted log content is converted into a text format through an analysis tool, and the log content recorded in the ROW format is selected as a processing object; processing a DELETE operation record in the log content, converting a DELETE statement into an INSERT statement, and generating an SQL script used for recovering the deleted data; processing the UPDATE operation record in the log content, adjusting the sequence of a WHERE clause and an SET clause of a UPDATE statement, and generating an SQL script used for recovering updated data; and importing the generated SQL script for recovering the deleted data and the SQL script for recovering the updated data into a target database, and executing the scripts through a database client tool to complete data recovery operation.

Description

TECHNICAL FIELD

[0001] The application belongs to the technical field of database management and data recovery, and particularly relates to a MySQL database rapid recovery method based on Binlog logs. BACKGROUND

[0002] In modern enterprise-level applications, the stability and data integrity of the database as the core data storage and management tool are crucial. MySQL, as a widely used open-source relational database management system, is adopted by many enterprises due to its flexibility, ease of use and high performance. However, in the process of daily operation, data loss problems caused by human errors (such as mistakenly deleting or modifying key data) occur from time to time. Although MySQL provides multiple backup and recovery mechanisms, traditional backup methods (such as mysqldump) have problems such as table locking, high resource consumption and slow recovery speed, which are more obvious in large data scenarios.

[0003] The binlog logs of MySQL record all the operations (excluding queries) that modify the database, and these logs can be used for data recovery. However, the process of directly extracting and converting binlog logs into executable SQL scripts for data recovery is complex, and requires solving technical problems such as how to efficiently and accurately filter the log content of the target time period, how to parse the logs into a readable format, and how to handle DELETE and UPDATE operations. SUMMARY

[0004] The application provides a MySQL database rapid recovery method based on Binlog logs to solve one of the above technical problems.

[0005] The technical solution adopted by the application is:

[0006] The application embodiment provides a MySQL database rapid recovery method based on Binlog logs, which includes:

[0007] According to the time interval of the user-specified error operation, the log content in the corresponding time period is filtered and extracted from the binlog logs of the MySQL database;

[0008] The extracted log content is converted to text format by a parsing tool, and the log content recorded in ROW format is selected as the processing object;

[0009] The DELETE operation record in the log content is processed, the DELETE statement is converted into an INSERT statement, and an SQL script for recovering the deleted data is generated;

[0010] Processing the UPDATE operation record in the log content, adjusting the order of the WHERE clause and the SET clause of the UPDATE statement, and generating an SQL script for restoring the updated data;

[0011] Importing the generated SQL script for restoring the deleted data and the SQL script for restoring the updated data into the target database, and executing the script through a database client tool to complete the data recovery operation.

[0012] According to an embodiment of the present application, the log content in the corresponding time period is filtered and extracted from the binlog log of the MySQL database according to the time interval of the user-specified misoperation occurrence, specifically:

[0013] The user inputs the start time and end time of the misoperation occurrence;

[0014] Through the MySQL command line tool or a third-party log management tool, the binlog log file is matched based on the timestamp, and the log content corresponding to the target time period is filtered out.

[0015] According to an embodiment of the present application, the analysis tool includes the mysqlbinlog command, and the analysis process includes:

[0016] The start-datetime and stop-datetime parameters are specified through the mysqlbinlog command to limit the analysis time range;

[0017] The base64-output=DECODE-ROWS parameter is set during analysis to ensure that the ROW format log content is completely parsed into text.

[0018] According to an embodiment of the present application, the DELETE operation record in the log content is processed, the DELETE statement is converted into an INSERT statement, and an SQL script for restoring the deleted data is generated, specifically:

[0019] The DELETE statement and its associated primary key field value are extracted from the parsed log;

[0020] The primary key field value in the DELETE statement is used as the field value of the INSERT statement, and the WHERE clause in the DELETE statement is removed;

[0021] Invalid characters in the statement are removed through a text replacement tool, and a semicolon is added at the end of the statement to form a standardized INSERT statement.

[0022] According to one embodiment of the present application, the processing of the UPDATE operation record in the log content, the adjustment of the order of the WHERE clause and the SET clause of the UPDATE statement, and the generation of the SQL script for restoring the updated data are specifically as follows:

[0023] extracting the WHERE clause and the SET clause of the UPDATE statement from the parsed log;

[0024] taking the content of the WHERE clause of the UPDATE statement as the SET clause of a new statement, and taking the content of the original SET clause as the WHERE clause of the new statement;

[0025] removing invalid characters in the statement by using a text replacement tool, and adding a semicolon at the end of the statement to form a standardized UPDATE statement.

[0026] According to one embodiment of the present application, the generated SQL script for restoring the deleted data and the SQL script for restoring the updated data are imported into a target database, and the scripts are executed by using a database client tool to complete the data restoration operation, specifically as follows:

[0027] saving the generated SQL script as a text file;

[0028] loading the script file by using a source command through a MySQL client tool, or executing the SQL statements one by one through a graphical tool;

[0029] ensuring that the database is in an unlocked state and that the target table is not occupied by other transactions during the execution.

[0030] According to one embodiment of the present application, the method further includes:

[0031] logging in to the business system, checking the data state of the target module, and re-adjusting the SQL script and repeating the restoration operation if data abnormality is found.

[0032] According to one embodiment of the present application, the method further includes logging in to a management interface of the business system and checking the data integrity of the target module.

[0033] If data loss or abnormality is found, the field values or statement logic in the SQL script are adjusted, and the restoration operation is re-executed.

[0034] The second aspect embodiment of the present application provides a computer readable storage medium having a program stored thereon, and the program is executed by a processor to implement the steps in the method.

[0035] A third aspect of this application provides an electronic device including a memory, a processor, and a program stored in the memory and executable on the processor, wherein the processor executes the program to implement the steps of the method as described.

[0036] Due to the adoption of the above technical solution, the beneficial effects achieved by this application are as follows:

[0037] This application allows users to accurately filter relevant log content from a large number of binlog logs based on specific time intervals of erroneous operations, greatly reducing unnecessary log analysis workload.

[0038] This application utilizes ROW format binlog logs, which can record the changes of each row of data in detail. This provides accurate data support for the reverse processing of subsequent DELETE and UPDATE operations, ensuring a high degree of accuracy in the recovery process.

[0039] This application achieves perfect data recovery even under complex business logic by converting DELETE statements to INSERT statements and adjusting the positions of the WHERE and SET clauses in UPDATE statements. This method is not only suitable for simple single-table operations but can also handle complex scenarios involving multi-table joins.

[0040] The entire data recovery process in this application does not rely on full backup files; it can be completed using only standard MySQL command-line tools and text processing tools, reducing environmental requirements and system resource consumption.

[0041] This application is specifically designed for the InnoDB storage engine in MySQL databases and can work well with other types of storage engines, demonstrating good versatility and adaptability. Attached Figure Description

[0042] The accompanying drawings, which are included to provide a further understanding of this application and form part of this application, illustrate exemplary embodiments and are used to explain this application, but do not constitute an undue limitation of this application. In the drawings:

[0043] Figure 1 A flowchart illustrating a fast recovery method for a MySQL database based on Binlog logs, provided in an embodiment of this application;

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

[0045] Figure label:

[0046] 810, processor; 820, communication interface; 830, memory; 840, communication bus. DETAILED DESCRIPTION

[0047] In order to more clearly illustrate the overall concept of the present application, the following will be described in detail with reference to the accompanying drawings.

[0048] In the following description, numerous specific details are set forth in order to provide a thorough understanding of the present application. However, it will be apparent to one skilled in the art that the present application can be practiced without the specific details and other implementations can be employed. In other instances, well-known methods, procedures, components, and circuits have not been described in detail as not to unnecessarily obscure aspects of the present application. Embodiments of the present application can be implemented in various ways without departing from the spirit or scope of the present application. Embodiments of the present application can be implemented in a variety of ways without departing from the spirit or scope of the present application. It should be noted that the embodiments of the present application and the features in each embodiment can be combined with each other under the condition of no conflict.

[0049] In the present application, unless specifically defined and limited otherwise, the first feature is "on" or "under" the second feature can be that the first and second features are in direct contact, or the first and second features are indirectly in contact through an intermediate medium. In the description of the present application, the description of the terms "one embodiment", "some embodiments", "an example", "a specific example", or "some examples" means that the specific features, structures, materials or characteristics described in connection with the embodiment or example are included in at least one embodiment or example of the present application. In the present application, the illustrative description of the above terms does not necessarily refer to the same embodiment or example. Moreover, the specific features, structures, materials or characteristics described can be combined in any appropriate manner in any one or more embodiments or examples.

[0050] Embodiment 1

[0051] As shown in Figure 1 A MySQL database fast recovery method based on Binlog log, comprising:

[0052] S100, according to the time interval of the user specified misoperation, the log content in the corresponding time period is filtered and extracted from the binlog log of the MySQL database.

[0053] As mentioned above, filtering and extracting log content from the binlog logs of the MySQL database according to the time interval when the user specifies the occurrence of the misoperation refers to first determining a specific time period as a filtering condition in the data recovery process. This time period is usually set based on the specific time point that the user can identify the data loss or damage. By using the mysql binlog tool and combining specific time parameters (such as --start-datetime and --stop-datetime), the relevant log records of this time period can be accurately filtered from a large number of binlog log files. The purpose of this is to focus on logs that may contain misoperation information, thereby reducing the amount of data for subsequent processing and improving recovery efficiency.

[0054] For example, assume that an administrator discovers that the employee salary data in the company's human resource management system was mistakenly modified between 14:00 and 15:00 on July 31, 2025. In order to recover these data, the administrator needs to first locate all relevant operations within this time period. By executing the following command:

[0055] mysqlbinlog --no-defaults --base64-output=decode-rows-v-v --start-datetime='2025-07-31 14:00:00' --stop-datetime='2025-07-31 15:00:00' binlog.000001 > filtered_logs.sql

[0056] The above command will extract all operation records between 2:00 pm and 3:00 pm on July 31, 2025 from the binlog log file named binlog.000001 and save them to the filtered_logs.sql file. Next, the administrator can perform further data analysis and recovery work based on this narrowed-down log file.

[0057] It should be noted that in specific implementation scenarios, on the basis of the above scheme, this step can not only be limited to simple time period screening, but also can be extended to a more flexible and powerful log screening mechanism. For example, in addition to being based on a time range, it can also be screened according to operation types (such as only screening DELETE or UPDATE operations), table names or database names involved, or even operations of specific users, and the like. This multi-dimensional screening method makes the present scheme not only suitable for simple misoperation recovery scenarios, but also applicable to more complex environments, such as tracing the operation history of a specific business process or auditing the behavior of a specific user. In addition, considering that different-sized enterprises can have different needs, this method can also be integrated with other automated scripts or monitoring systems to realize real-time monitoring of key operations and an automated data recovery process, further enhancing the robustness and response speed of the system.

[0058] S200, converting the extracted log content into a text format through a parsing tool, and selecting log content recorded in a ROW format as a processing object.

[0059] As described above, converting the extracted log content into a text format through a parsing tool, and selecting log content recorded in a ROW format as a processing object, refers to, after completing log time range screening, further structurally analyzing the screened binlog log, so that it is converted from a database-specific binary log format into a human-readable, easy-to-process text form. This process relies on a dedicated parsing tool and can accurately restore the data change operation details recorded in the log. Among them, the ROW format is a log mode of MySQL binlog, and its characteristic is that it explicitly records the complete field values of a certain row of data before and after modification in each log, rather than only recording the SQL statement itself. Therefore, selecting the log in the ROW format as the processing object can ensure that there is sufficient data basis for subsequent reverse conversion of DELETE and UPDATE operations, thereby realizing high-precision data recovery.

[0060] For example, assume that the salary information of an employee is mistakenly modified in a human resource system, and the administrator has screened out the relevant log segment through a time condition. At this time, if the binlog of the database is configured in ROW format, the log will explicitly record the salary value of the employee before modification (such as "old value: salary = 8000") and the value after modification (such as "new value: salary = 6000"). After the log is converted by the analysis tool, a clear text description can be generated, for example: "in the table employee, the record with the primary key 1001, the field salary is changed from 8000 to 6000". This structured text information provides a direct basis for subsequently reversing the UPDATE operation to "restore the original value" operation. If the log is in STATEMENT format, only a statement similar to "UPDATE employee SET salary = 6000 WHERE id = 1001" will be recorded, and the original value cannot be obtained, resulting in an inability to safely restore. Therefore, selecting ROW format logs as the processing object is a key prerequisite for realizing accurate recovery.

[0061] It should be noted that in a specific implementation scenario, the automatic identification and compatible processing mechanism of multiple log formats can also be supported on the basis of the above scheme. For example, in the analysis process, the system can automatically detect the format type (ROW, STATEMENT or MIXED) of the log, and preferentially select the ROW format log containing complete row change information for processing; if part of the log is in STATEMENT format, it can be supplemented by other auxiliary information (such as database audit logs, application layer operation logs) to improve the completeness of the recovery. In addition, the analysis process can introduce structured labeling and classification of log content, such as labeling different operation types (insert, delete, update), different table objects or different transaction identifiers, to facilitate the generation of recovery scripts as needed. The analysis result can also be used as a basic data source for data change tracing, operation auditing or compliance checking.

[0062] S300, the DELETE operation record in the log content is processed, and the DELETE statement is converted into an INSERT statement to generate an SQL script for restoring the deleted data.

[0063] As mentioned above, processing the DELETE operation records in the log content, converting the DELETE statement into an INSERT statement, and generating an SQL script for restoring the deleted data refers to further processing the log after extracting and parsing the log information containing the DELETE operation from the binlog log to achieve data recovery. Specifically, this process involves identifying the table name, primary key, and all field values of the deleted data rows corresponding to each DELETE operation. Then, based on this information, the corresponding INSERT statement is constructed, i.e., the originally deleted data is re-inserted into the database, thereby achieving the purpose of restoring data. The key to this step is to accurately capture the data rows affected by the DELETE operation and convert them into SQL statements that can be directly executed.

[0064] For example, assume that in a human resource management system, a certain employee record is deleted due to a misoperation. By parsing the binlog log, it is found that the operation is DELETE FROM employees WHERE id=12345;. This DELETE statement actually implies all detailed information about the employee with id 12345 (such as name, position, date of entry, etc.). The parsing tool extracts the complete data row information corresponding to this DELETE operation, including all fields and their values. Then, the system converts this information into an INSERT statement, for example: "re-insert an employee named Zhang San, position software engineer, date of entry June 2020 into the employees table". In this way, by executing the generated INSERT statement, the mis-deleted employee record can be effectively restored, ensuring data integrity is not affected.

[0065] It should be noted that in specific implementation scenarios, in addition to processing DELETE operations in individual tables, the above-mentioned scheme can also consider cross-table association. For example, in some cases, deleting a record may affect other associated tables. In this case, by analyzing the transaction boundaries and association information in the log, INSERT statements for multiple tables can be generated simultaneously to ensure the consistency of the entire business logic.

[0066] In specific implementation scenarios, in addition to the above-mentioned scheme, users can selectively restore data according to specific conditions. For example, only when certain predefined conditions are met (such as a certain field value meets a certain standard), the INSERT operation is executed. This provides users with a more flexible data recovery option, allowing them to adjust the recovery strategy according to actual needs.

[0067] In a specific implementation scenario, on the basis of the above scheme, a timestamp or version number mechanism can be combined, so that each INSERT statement has a creation time or version identifier. This not only helps to track the history of data changes, but also provides a basis for future audits. In addition, if it is necessary to roll back to an earlier state, the corresponding version of the data can be easily found for recovery.

[0068] S400, processing the UPDATE operation record in the log content, adjusting the order of the WHERE clause and the SET clause of the UPDATE statement, and generating an SQL script for restoring the updated data.

[0069] As described above, processing the UPDATE operation record in the log content, adjusting the order of the WHERE clause and the SET clause of the UPDATE statement, and generating an SQL script for restoring the updated data means that after extracting the UPDATE operation information from the binlog log, the operation is reconstructed through reverse logic to implement the process of restoring the data to the state before the update. In the ROW format binlog, the UPDATE operation is recorded as two parts of "old value" and "new value", where the "old value" corresponds to the condition of the WHERE clause (i.e. the data before the update), and the "new value" corresponds to the content of the SET clause (i.e. the data after the update). By role-swapping these two parts, i.e. taking the "old value" in the original WHERE clause as the new SET target and the "new value" in the original SET clause as the new WHERE condition, an UPDATE statement is constructed that can restore the data from the current state to the previous state. The reconstructed statement is integrated into the recovery script for subsequent execution of data rollback.

[0070] For example, assume that in a salary management system, an employee's salary was mistakenly modified, with the original salary being 8000 yuan and the error update being 6000 yuan. In the binlog log, the operation is recorded as: updating the record with employee number 1001, with the salary field being updated from 8000 to 6000. After parsing, the WHERE condition is "id=1001 AND salary=6000" (current error state), and the SET content is "salary=8000" (target value to be restored). By adjusting the order of the clauses, the corresponding statement in the recovery script becomes: taking "salary=8000" as the update target and "id=1001 AND salary=6000" as the matching condition to form a new UPDATE statement. After executing the statement, the system will identify the record in the error state (salary of 6000) and restore it to the original correct value (salary of 8000), thus completing the data repair.

[0071] It should be noted that in a specific implementation scenario, the recovery logic can be extended to the complete mapping of the "before image" and "after image" of the entire row for the operation involving simultaneous update of multiple fields on the basis of the above scheme. For example, when an UPDATE operation modifies the name, position and salary fields, the recovery script can construct a reverse UPDATE statement containing multiple field assignments based on the complete old value and new value recorded in the log, ensuring complete rollback of multi-field changes.

[0072] In a specific implementation scenario, data state pre-check logic can be added before generating the recovery script on the basis of the above scheme. For example, before executing "change salary from 6000 back to 8000", first determine whether the record is currently still 6000. If it has been modified again by other operations, prompt the user to confirm whether to continue recovery to avoid overwriting subsequent legal changes, improving the safety and controllability of the recovery operation.

[0073] In a specific implementation scenario, for large-scale batch update scenarios, recovery granularity options can be provided according to transactions, time windows or primary key ranges on the basis of the above scheme. Users can choose to restore only part of the records to achieve fine-grained data rollback without having to execute the entire reverse script of the UPDATE operation.

[0074] In a specific implementation scenario, if the UPDATE operation is part of a transaction, the recovery process can associate other operations (such as DELETE or INSERT) within the transaction to generate a recovery script with transaction integrity, ensuring that the database remains consistent in business logic after recovery.

[0075] S500, import the generated SQL script for restoring deleted data and the SQL script for restoring updated data into the target database, and execute the script through a database client tool to complete the data recovery operation.

[0076] As described above, importing the generated SQL script for restoring deleted data and the SQL script for restoring updated data into the target database and executing the script through a database client tool to complete the data recovery operation means that after log analysis and recovery statement generation are completed, the standardized SQL instruction set formed is loaded into the target database environment, and these statements are executed through the standard interaction interface provided by the database, thereby realizing accurate restoration of the misoperation data. This step is the execution phase of the entire recovery process, and its core is to ensure that the recovery script can be correctly loaded, executed in order, and complete the state rollback of local data without affecting the overall operation stability of the database. During execution, the stability of the database connection, the integrity of the transaction and the compliance of the operation authority need to be ensured to avoid introducing new data anomalies.

[0077] For example, assume that in a human resource system, an administrator has generated two recovery scripts through the foregoing steps: one contains INSERT statements that will reinsert the mistakenly deleted employee, and the other contains UPDATE statements that will restore the salary of the employee who was mistakenly modified to its original value. Next, the administrator imports the two script files into the database session environment in sequence through the MySQL command line client or a graphical management tool (such as MySQL Workbench). After the system receives these SQL instructions, it executes the statements in the script in sequence. For example, first, the employee record with the number 1001 is reinserted into the employee table, and then the salary of the employee with the number 1002 is updated from 6000 yuan to 8000 yuan. During the entire execution process, the database transaction mechanism ensures the atomicity of each statement, and if a statement fails to execute, the rollback mechanism is triggered to prevent partial updates from causing data inconsistency. After the execution is complete, the administrator can verify through a query interface whether the relevant data has been restored to the expected state, thereby completing the entire recovery process.

[0078] It should be noted that in a specific implementation scenario, on the basis of the foregoing scheme, for a large-scale data recovery scenario, the recovery script can be divided into multiple logical batches (such as according to a time interval, according to a table partition, or according to a primary key range), and support for phased import and execution can be provided to avoid high database resource occupation or response delay caused by the execution of a large number of SQL statements at one time, and the controllability and system stability of the recovery process can be improved.

[0079] In a specific implementation scenario, on the basis of the foregoing scheme, before formal execution, the system can provide a “simulation execution” function, that is, syntax checking, impact range analysis, and conflict detection are performed on the recovery script, the number of records that can be affected and the potential data coverage risk are predicted, and real execution is performed after the user confirms, thereby enhancing the security of the operation.

[0080] In a specific implementation scenario, on the basis of the foregoing scheme, the entire recovery process can be encapsulated in a database transaction, so that all recovery operations are either all successful or all canceled. If an exception occurs (such as a network interruption or insufficient permissions) during execution, the system can automatically roll back the executed partial changes, thereby maintaining the consistency of the database state.

[0081] In a specific implementation scenario, on the basis of the foregoing scheme, during script execution, the execution time, the number of affected rows, the execution result (success or failure), and other information of each SQL statement are automatically recorded, forming a traceable recovery operation log, thereby facilitating subsequent auditing, problem troubleshooting, or compliance review.

[0082] In a specific implementation scenario, on the basis of the above scheme, in combination with a database management platform or an operation and maintenance automation tool, the timing execution, remote calling or linkage with other monitoring and alarm systems of the recovery script can be realized, forming a closed-loop data protection mechanism, which is suitable for unattended or high-availability production environments.

[0083] According to an embodiment of the present application, the log content in the corresponding time period is filtered and extracted from the binlog log of the MySQL database according to the time interval of the user-specified misoperation occurrence, specifically:

[0084] The user inputs the start time and end time of the misoperation occurrence;

[0085] The binlog log file is matched based on the timestamp through the MySQL command line tool or the third-party log management tool, and the log content corresponding to the target time period is filtered out.

[0086] As described above, the user first determines the approximate time range of the data misoperation occurrence according to the actual business situation or system monitoring record, and inputs the start time and end time of the time range. The time information is usually accurate to seconds, which is used to define the time window involved in the required recovery operation. Then, the input time range is used as a filtering condition to scan and match multiple binlog log files stored on the database server by using the command line tool provided by MySQL or the third-party log management tool integrated in the database operation and maintenance platform. Each binlog log file contains continuous timestamp information, which records the occurrence time of each operation event therein. The system identifies which log files and which log fragments in the files fall within the user-specified time interval according to these timestamps, and extracts the corresponding log content therefrom. The extraction result is the original log data containing all data change operations in the time period, which is used as the basic input for subsequent parsing and recovery processing. This process ensures that only the logs related to the misoperation are processed, avoiding redundant analysis of irrelevant logs, and improving the efficiency and accuracy of data recovery.

[0087] According to an embodiment of the present application, the parsing tool includes the mysqlbinlog command, and the parsing process includes:

[0088] The start-datetime and stop-datetime parameters are specified through the mysqlbinlog command to limit the parsing time range;

[0089] The base64-output=DECODE-ROWS parameter is set during parsing to ensure that the ROW format log content is completely parsed into text.

[0090] As described above, when parsing the extracted binlog log content using the mysqlbinlog command, first, the time range to be parsed is set by specifying the start-datetime and stop-datetime parameters. These two parameters correspond to the user inputted misoperation start time and end time, respectively, and are used to filter out the operation records within the time interval during the parsing process, avoiding loading irrelevant logs and improving parsing efficiency. At the same time, the base64-output=DECODE-ROWS parameter is set when executing the parsing command. The function of this parameter is to instruct the parsing tool to decode the binary log content recorded in ROW format and convert it into readable text form. Since the ROW format log is usually written in Base64 encoding when stored, if not decoded, the specific row data change information cannot be directly viewed or processed. By enabling this parameter, the table name, field name, and new and old value information involved in each data insertion, deletion, or update operation can be completely restored to plaintext text, facilitating the subsequent reverse processing of DELETE and UPDATE operations and generating SQL statements that can be used for data recovery. The entire parsing process is executed in a command line environment, and the output result is a text file containing readable operation records, which serves as the basis data for generating recovery scripts subsequently.

[0091] According to an embodiment of the present application, the DELETE operation record in the log content is processed, the DELETE statement is converted into an INSERT statement, and an SQL script for recovering deleted data is generated, specifically:

[0092] Extract the DELETE statement and its associated primary key field value from the parsed log;

[0093] Use the primary key field value in the DELETE statement as the field value of the INSERT statement, and remove the WHERE clause in the DELETE statement;

[0094] Remove invalid characters in the statement through a text replacement tool, and add a semicolon at the end of the statement to form a standardized INSERT statement.

[0095] As described above, when processing the DELETE operation record in the parsed log content, first, all the relevant information of the DELETE operation is identified and extracted from the binlog log converted into text format, including the table name involved, the field structure, and the complete content of the deleted data row, among which the primary key field and its value corresponding to the record are particularly extracted. The primary key field value is used to uniquely identify the deleted data row and is the key basis for subsequent recovery operations. Then, based on the extracted information, the original DELETE statement is converted into a corresponding INSERT statement: the values of each field of the deleted data row in the original DELETE operation, including the primary key field value, are used as the field values to be inserted in the INSERT statement, and a complete INSERT statement is constructed. In this process, the WHERE clause in the original DELETE statement is only used to locate the deleted record and is no longer needed during recovery, so it is removed. Subsequently, the generated statement is standardized using a text processing tool to remove redundant characters or format symbols that may be introduced during log parsing, ensure the correct syntax of the statement, and uniformly add a semicolon at the end of each INSERT statement to comply with the standard writing specification of the SQL statement. Finally, a set of SQL statements with uniform format, which can be directly recognized and executed by the database, is formed to constitute a script file for restoring the deleted data.

[0096] According to an embodiment of the present application, the processing of the UPDATE operation record in the log content, adjusting the order of the WHERE clause and the SET clause of the UPDATE statement, and generating an SQL script for restoring the updated data are as follows:

[0097] extracting the WHERE clause and the SET clause of the UPDATE statement from the parsed log;

[0098] taking the content of the WHERE clause of the UPDATE statement as the SET clause of the new statement, and taking the content of the original SET clause as the WHERE clause of the new statement;

[0099] removing invalid characters in the statement by a text replacement tool, and adding a semicolon at the end of the statement to form a standardized UPDATE statement.

[0100] As described above, when processing the UPDATE operation records in the parsed log content, first, the two key components of each UPDATE operation are identified and extracted from the binlog log that has been converted into a text format: the WHERE clause and the SET clause in the original statement. The WHERE clause records the original value before data modification or the condition for locating the record, and the SET clause records the new value after modification. In the recovery process, in order to restore the data from the current state to the state before modification, the two clauses need to be logically reversed. The specific method is: the original field value contained in the original WHERE clause is used as the SET clause content of the new UPDATE statement, i.e., the target value to be restored is specified; at the same time, the new value contained in the original SET clause is used as the WHERE clause content of the new UPDATE statement, i.e., as the matching condition for finding the current erroneous data record. In this way, the reverse restoration of the data update operation is achieved. Subsequently, the generated new statement is standardized using a text processing tool to remove invalid characters, unnecessary spaces or special symbols that may be introduced during log parsing, to ensure that the statement structure is clear, the syntax is correct, and a semicolon is uniformly added at the end of each UPDATE statement to make it meet the format requirements of a standard SQL statement. Finally, a set of complete structure, directly executable SQL statements are formed, which constitute a script file for restoring the erroneously updated data.

[0101] According to an embodiment of the present application, the generated SQL script for restoring deleted data and the SQL script for restoring updated data are imported into the target database, and the script is executed through a database client tool to complete the data recovery operation, specifically:

[0102] The generated SQL script is saved as a text file;

[0103] The script file is loaded through the MySQL client tool using the source command, or the SQL statements are executed one by one through a graphical tool;

[0104] During execution, it is ensured that the database is in an unlocked state and the target table is not occupied by other transactions.

[0105] As described above, the generated SQL script for restoring deleted data and the SQL script for restoring updated data are imported into the target database and executed, first, the two types of recovery statements generated in the foregoing steps are sorted and saved as standard text files respectively, each SQL statement in the file is arranged in order, and the format conforms to the database syntax specification. The text file can be stored in the local database server or in the specified path accessible as an instruction set for performing the recovery operation. Subsequently, the client tool provided by MySQL is connected to the target database, and in the established database session, the script file is loaded and executed using the source command, which reads the file content line by line and submits it to the database for processing. Alternatively, the SQL statements in the script file can be imported into the execution interface in batches or one by one with the help of a graphical database management tool, and the execution process is triggered manually or automatically. During the execution process, it is necessary to ensure that the target database as a whole is in a normal running state, is not set to read-only or maintenance mode, and confirms that the data table to be restored is not locked or long occupied by other ongoing transactions, to avoid execution failure or blocking due to resource conflicts. Only in the case where the database environment allows write operation, the table structure is stable and there is no concurrent modification interference, can the recovery script be correctly applied to complete the complete restoration of the mistakenly deleted and updated data.

[0106] According to one embodiment of the present application, further comprising:

[0107] Logging into the business system, checking the data state of the target module, and if data anomalies are found, readjusting the SQL script and repeating the recovery operation.

[0108] As described above, after the execution of the SQL script is completed, the results of data recovery need to be verified. Specifically, by logging into the business application system associated with the target database through legal identity authentication, entering the affected business module, and viewing the current state of the relevant data. The inspection content includes but is not limited to whether the data records exist, whether the field values are correct, whether the association between the data is complete, and whether the business function is restored to normal. The process uses the user interface of the business system to intuitively reflect the actual data situation in the database, so as to determine whether the recovery operation achieves the expected effect.

[0109] If the data still has missing, errors, duplicates, or logical inconsistencies during the inspection, it indicates that the current recovery script has not accurately restored the data. At this point, the generated SQL script needs to be re-analyzed and adjusted. The adjustment may include correcting field values in the statements, supplementing missing operation records, modifying condition matching logic, or optimizing execution order. After the script is adjusted, it is imported into the database and executed again, and then the business system is logged in again for review. This "execution-verification-correction-re-execution" process can be repeated as needed until the data state of the target module is completely restored to normal. This step ensures closed-loop management of the data recovery process and the reliability of the results.

[0110] According to one embodiment of the present application, it further comprises logging into the management interface of the business system to check the data integrity of the target module.

[0111] If data is missing or abnormal, adjust the field values or statement logic in the SQL script and re-execute the recovery operation.

[0112] As mentioned above, after executing the recovery script, in order to ensure that the data has been accurately restored, it is necessary to further log into the management interface of the business system for detailed inspection. The specific steps are as follows:

[0113] First, log into the management interface of the business system using an account with appropriate permissions. This interface usually provides functions for viewing and managing the data of various modules in the system. Enter the business module related to the target database and carefully check the data integrity therein. This includes but is not limited to confirming whether all expected data records exist, whether the values of each field correctly reflect the actual business situation, and whether the relationship between data remains consistent, etc.

[0114] If during the inspection process, it is found that the data is missing or abnormal, for example, some records have not been restored, field values are incorrect, or the relationship between data is chaotic, etc., the previously generated SQL script needs to be adjusted. The adjustment may involve modifying the field values in the SQL script to correct incorrect information, optimizing the statement logic to more accurately match the data state to be restored, or adding missing operation steps to supplement necessary data repair work.

[0115] After completing the above adjustment, the updated SQL script is imported into the database again and the recovery operation is executed again. Then, repeat the previous data integrity checking steps until all data is correctly restored and there are no abnormalities through the management interface of the business system. This process allows continuous iterative optimization based on actual recovery results to ensure complete and accurate restoration of data. This verification and correction cycle is a key link to ensure the quality of data recovery.

[0116] It should be noted that, in specific implementation scenarios, further solutions can be developed based on the above.

[0117] A second aspect of this application provides 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 program to implement the method described in any of the embodiments of the first aspect above.

[0118] Figure 2 An example is a schematic diagram of the physical structure of an electronic device, such as... Figure 2 As shown, the electronic device may include: a processor 810, a communication interface 820, a memory 830, and a communication bus 840, wherein the processor 810, the communication interface 820, and the memory 830 communicate with each other via the communication bus 840. The processor 810 may call logical instructions in the memory 830 to execute the method in any of the embodiments of the first aspect described above, the method including:

[0119] Based on the time interval of the erroneous operation specified by the user, filter and extract the log content within the corresponding time period from the binlog log of the MySQL database;

[0120] The extracted log content is converted into text format using a parsing tool, and log content recorded in ROW format is selected as the processing object.

[0121] Process the DELETE operation records in the log content, convert the DELETE statements into INSERT statements, and generate an SQL script for recovering deleted data;

[0122] Process the UPDATE operation records in the log content, adjust the order of the WHERE clause and SET clause of the UPDATE statement, and generate an SQL script for restoring the updated data;

[0123] Import the generated SQL scripts for recovering deleted data and for recovering updated data into the target database, and execute the scripts using a database client tool to complete the data recovery operation.

[0124] Further, the logic instructions in the memory 830 described above can be implemented in the form of software functional units and sold or used as independent products, and can be stored in a computer readable storage medium. Based on such understanding, the technical solutions of the present application essentially or the parts that contribute to the prior art or parts of the technical solutions can be embodied in the form of a software product. The computer software product is stored in a storage medium, and includes a plurality of instructions for causing a computer device (which can be a personal computer, a server, or a network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of the present application. The aforementioned storage medium includes: a U disk, a mobile hard disk, a read-only memory, a random access memory, a magnetic disk or an optical disk, and various media that can store program codes.

[0125] In another aspect, the present application also provides a computer program product, which comprises a computer program, the computer program can be stored on a non-transitory computer readable storage medium, and the computer program can be executed by a processor to enable a computer to execute the method provided by the above-mentioned methods, and the method comprises:

[0126] According to the time interval of the user specified misoperation, the log content in the corresponding time period is filtered and extracted from the binlog log of the MySQL database;

[0127] The extracted log content is converted into a text format by a parsing tool, and the log content recorded in a ROW format is selected as a processing object;

[0128] The DELETE operation record in the log content is processed, the DELETE statement is converted into an INSERT statement, and an SQL script for restoring the deleted data is generated;

[0129] The UPDATE operation record in the log content is processed, the order of the WHERE clause and the SET clause of the UPDATE statement is adjusted, and an SQL script for restoring the updated data is generated;

[0130] The generated SQL script for restoring the deleted data and the SQL script for restoring the updated data are imported into a target database, and the scripts are executed by a database client tool to complete the data recovery operation.

[0131] In another aspect, the present application also provides a non-transitory computer readable storage medium, which stores a computer program, and the computer program is executed by a processor to implement the cigarette case image recognition method provided by the above-mentioned methods, and the method comprises:

[0132] According to the time interval of the user specified misoperation, the log content in the corresponding time period is filtered and extracted from the binlog log of the MySQL database;

[0133] The extracted log content is converted into a text format by a parsing tool, and log content recorded in a ROW format is selected as a processing object;

[0134] DELETE operation records in the log content are processed, a DELETE statement is converted into an INSERT statement, and an SQL script for restoring deleted data is generated;

[0135] UPDATE operation records in the log content are processed, the order of a WHERE clause and a SET clause of an UPDATE statement is adjusted, and an SQL script for restoring updated data is generated;

[0136] The generated SQL script for restoring deleted data and the SQL script for restoring updated data are imported into a target database, and the scripts are executed by a database client tool to complete a data restoration operation.

[0137] Any matter not described in the present application can be implemented by using or referring to existing technology.

[0138] Each of the embodiments in the present specification is described in a progressive manner, and the same or similar parts between the embodiments can be mutually referred to. Each of the embodiments focuses on the difference from other embodiments.

[0139] The above only describes the embodiments of the present application and is not intended to limit the present application. The present application can have various modifications and changes for those skilled in the art. Any modification, equivalent replacement, improvement, etc. within the spirit and principle of the present application shall be included in the scope of the claims of the present application.

Claims

1. A method for fast recovery of a MySQL database based on Binlog logs, characterized in that, include: Based on the time interval of the erroneous operation specified by the user, filter and extract the log content within the corresponding time period from the binlog log of the MySQL database; The extracted log content is converted into text format using a parsing tool, and log content recorded in ROW format is selected as the processing object. Process the DELETE operation records in the log content, convert the DELETE statements into INSERT statements, and generate an SQL script for recovering deleted data; Process the UPDATE operation records in the log content, adjust the order of the WHERE clause and SET clause of the UPDATE statement, and generate an SQL script for restoring the updated data; Import the generated SQL scripts for recovering deleted data and for recovering updated data into the target database, and execute the scripts using a database client tool to complete the data recovery operation.

2. The method according to claim 1, characterized in that, The step of filtering and extracting log content within the corresponding time period from the binlog log of the MySQL database based on the user-specified time interval of the erroneous operation is as follows: The start and end times of user input errors; Using the MySQL command-line tool or a third-party log management tool, match binlog log files based on timestamps to filter out log content corresponding to the target time period.

3. The method according to claim 1, characterized in that, The parsing tool includes the mysqlbinlog command, and the parsing process includes: The `mysqlbinlog` command can be used to specify the `start-datetime` and `stop-datetime` parameters to limit the time range for parsing. Set the base64-output=DECODE-ROWS parameter during parsing to ensure that the log content in ROW format is completely parsed into text.

4. The method according to claim 1, characterized in that, The process of processing the DELETE operation records in the log content, converting the DELETE statements into INSERT statements, and generating an SQL script for recovering deleted data is as follows: Extract the DELETE statement and its associated primary key field values ​​from the parsed log; Use the primary key field value from the DELETE statement as the field value from the INSERT statement, and remove the WHERE clause from the DELETE statement; Use a text replacement tool to remove invalid characters from the statement and add a semicolon at the end of the statement to form a standardized INSERT statement.

5. The method according to claim 1, characterized in that, The process involves processing the UPDATE operation records in the log content, adjusting the order of the WHERE and SET clauses in the UPDATE statement, and generating an SQL script to restore the updated data. Specifically: Extract the WHERE and SET clauses of the UPDATE statement from the parsed log; Use the contents of the WHERE clause of the UPDATE statement as the SET clause of the new statement, and use the contents of the original SET clause as the WHERE clause of the new statement; Use a text replacement tool to remove invalid characters from the statement and add a semicolon at the end of the statement to form a standardized UPDATE statement.

6. The method according to claim 1, characterized in that, The process involves importing the generated SQL scripts for recovering deleted data and for recovering updated data into the target database, and then executing the scripts using a database client tool to complete the data recovery operation. Specifically: Save the generated SQL script as a text file; The script file can be loaded using the source command in a MySQL client tool, or the SQL statements can be executed one by one using a graphical tool. During execution, ensure that the database is unlocked and that the target table is not occupied by other transactions.

7. The method according to claim 1, characterized in that, Also includes: Log in to the business system, check the data status of the target module, and if any data anomalies are found, readjust the SQL script and repeat the recovery operation.

8. The method according to claim 7, characterized in that, Also includes: Log in to the management interface of the business system and check the data integrity of the target module; If data is found to be missing or abnormal, adjust the field values ​​or statement logic in the SQL script and re-execute the recovery operation.

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

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

Images