Http request to database data change backtracking query system

Abstract

The application provides a system for tracing query of data change of http request to database, comprising a record storage module, a query change module and a data change information searching module; the record storage module, i.e. a user sends an http request, enters a server to send an sql statement, a database receives the sql statement, pushes to a parsing server after execution, sends the received change record and a hash value of the sql statement to a message queue server and pushes to a document database for storage; the query change module, i.e. according to business conditions, searches historical records in the database, extracts and uses as conditions to query a historical change record document list in the document database and query out http request record information; the data change information searching module, i.e. according to a request time range in step S2, searches associated change records by using the sql statement hash value; the application can realize positive positioning of change conditions of the http request to the database record according to the http request.

Description

Technical Field

[0001] This invention relates to the field of computer technology, and in particular to a data change backtracking query system from an HTTP request to a database. Background Technology

[0002] As one of the most popular open-source databases, MySQL does not provide backtracking functionality for database records. When a business logic requests to retrieve the historical change history of a specific data record, MySQL cannot provide support. This patent uses MySQL's binlog logs, combined with an HTTP request interceptor and a database driver filter, to provide the functionality to backtrack data changes in MySQL and trace the HTTP requests that triggered the changes to the MySQL records. Summary of the Invention

[0003] In view of this, the purpose of the present invention is to provide a data change backtracking query system that can locate changes to database records by HTTP requests in a forward manner based on HTTP requests, or locate the HTTP requests that triggered data record changes and the HTTP requests of SQL statements that triggered data record changes in a reverse manner based on changes to database records.

[0004] This invention employs the following method: a data change backtracking query system for HTTP requests to a database, comprising a record storage module, a change query module, and a data change information retrieval module; the record storage module is used when a user sends an HTTP request, which in turn sends an SQL statement to the server. The database receives and executes the SQL statement, then pushes it to a parsing server, which sends the received change record and the hash value of the SPL statement to a message queue server, and finally to a document database for storage; the change query module searches for historical records in the database based on business conditions, extracts them, and uses them as conditions to query a list of historical change record documents in the document database to retrieve the HTTP request record information; the data change information retrieval module, in step S2, uses the hash value of the SPL statement based on the request time range to search for associated change records, thereby enabling the forward location of changes made to database records by the HTTP request.

[0005] Furthermore, the recording and storage module specifically includes the following steps:

[0006] Step S1: The user performs an operation on the webpage, initiating an HTTP request;

[0007] Step S2: The HTTP request enters the web application server, is recorded by the HTTP request interceptor, and appended to the context.

[0008] Step S3: The HTTP request enters the web application server to execute business logic. The database driver sends an SQL statement to the database server. At the same time, the database driver filter intercepts the SQL statement and extracts the HTTP request from the context, and sends it to Topic:T1 of the message queue server.

[0009] Step S4: After receiving the SQL statement, the MySQL database starts execution. After execution, it generates binlog records and pushes them to the binlog parsing server.

[0010] Step S5: The binlog parsing server sends the received rowdata change records and the hash value of the SQL statement to Topic T2 of the message queue server;

[0011] Step S6: The consumer pulls the data from the message queue server and pushes it to the document database for persistent storage. Topic:T1 is mapped and stored in document database D1, and Topic:T2 is mapped and stored in document database D2.

[0012] Furthermore, the query change module specifically includes the following steps: Step S10: Based on business conditions, search for the historical record information to be retrieved in the MySQL database; Step S20: Extract the primary key from Step S10 and combine it with the time period of the historical change to be queried as the query condition; Step S30: Use the information extracted in Step S20 as the condition to query the list of historical change record documents in the document database D2; Step S40: Use the hash value of the SQL statement of each document in the document list retrieved in Step S30 as the association query condition to query the HTTP request record information in the D1 database.

[0013] Furthermore, the data change information search module specifically includes the following steps: Step S50: In the D1 database, based on the request time range, request body, and request header, combine the above conditions to construct a query statement to obtain the HTTP request record to be searched; Step S60: Each record returned by the query in Step S50 has an SQL statement hash field, and the third query operation is performed using all the SQL statement hash values ​​in the record; Step S70: Using the SQL statement hash values, search for the associated change record in the D2 database, and the search result is caused by this HTTP request interface.

[0014] The beneficial effects of this invention are as follows: Traditional database auditing can only focus on changes to database records, but it cannot know which operations the user performed (i.e., which service interfaces were called), which parameters were passed, and which ultimately led to the data change. This invention can query which interfaces the user called that triggered the data change through database change records. At the same time, it can also reverse the process by checking which interfaces the user called to perceive which data was triggered after calling these interfaces (i.e., a connection is established between interface calls and data changes, and forward and reverse queries are possible). Ultimately, this provides assistance to developers in querying online issues, making it easier for them to locate the location and cause of the problem. Attached Figure Description

[0015] Figure 1 This is a system principle block diagram of the present invention.

[0016] Figure 2 This is a schematic diagram of the method flow for the record storage module.

[0017] Figure 3 This is a flowchart illustrating the method of the query change module.

[0018] Figure 4 This is a flowchart illustrating the method for the module that retrieves data change information. Detailed Implementation

[0019] The invention will now be further described with reference to the accompanying drawings.

[0020] Please see Figure 1 As shown, this invention provides an embodiment: a data change backtracking query system for HTTP requests to a database, including a record storage module, a change query module, and a data change information retrieval module. The record storage module handles user HTTP requests, which in turn send SQL statements to the server. The database receives and executes the SQL statements, then pushes them to a parsing server. The received change records and the hash value of the SQL statement are sent to a message queue server and finally stored in a document database. The change query module searches for historical records in the database based on business conditions, extracts them, and uses them as criteria to query a list of historical change record documents in the document database, retrieving the HTTP request record information. The data change information retrieval module, in step S2, searches for associated change records based on the request time range and the hash value of the SQL statement, thereby enabling the forward location of changes made to database records by the HTTP request.

[0021] The present invention will be further illustrated by a specific embodiment below:

[0022] Please see Figure 2 As shown, the recording and storage module includes the following steps:

[0023] S1: The user interacts with the webpage, initiating an HTTP request.

[0024] S2: An HTTP request enters the web application server, is recorded by the HTTP interceptor, and appended to the context (in a special ThreadLocal variable in the Java project).

[0025] S3: After the HTTP request enters the web application server and executes business logic (such as creating an order or querying user information), it uses the database driver to send an SQL statement to the database server. Simultaneously, the database driver filter intercepts the SQL statement and extracts the HTTP request from the context (the information appended in step S2), sending it to the message queue server's Topic: T1.

[0026] S4: After receiving an SQL statement, the MySQL database begins execution. Upon completion, it generates binlog (MySQL's synchronization log) records and pushes them to the binlog parsing server. The binlog parsing server then correlates each query type data within the most recent transaction with subsequent insert, update, and delete type data.

[0027] S5: The binlog parsing server sends the received rowdata change records and the SQL hash value to the message queue server's Topic: T2.

[0028] S6: The consumer pulls data from the message queue server and pushes it to the document database for persistent storage. Topic:T1 is mapped and stored in document database D1, and Topic:T2 is mapped and stored in document database D2.

[0029] The steps to query change history based on business records are as follows:

[0030] Please see Figure 3 As shown, Q1: Based on business requirements, search for the historical records in MySQL that need to be retrieved.

[0031] Q2: Extract the primary key from Q1 and combine it with the time period for which historical changes need to be queried as the query condition.

[0032] Q3: Using the information extracted in Q2 as conditions, query the list of documents with historical change records in the document database D2.

[0033] Q4: Using the SQL hash value of each document in the document list retrieved in Q3 as a join query condition, retrieve the HTTP request record information in D1.

[0034] What you get in Q4 is the historical change record information of the MySQL record you want to query within the selected time period.

[0035] Please see Figure 4 As shown, the triggered data change information is found based on the HTTP request interface.

[0036] Q2_1: In D1, combine the requested time range, request body, and request headers to construct a query to retrieve the HTTP records to be found (here, Elasticsearch is used as the document database, and the query is constructed as time>xxx AND time). <xxx AND data:“xxx”)

[0037] Q2_2: Each record returned by the query in Q2_1 has an SQL hash field. The third query operation is performed using all the SQL hash values ​​of the records.

[0038] Q2_3: Use the SQL hash value to search for the associated change record in D2. The search results are caused by this HTTP interface.

[0039] Database record change record

[0040] For example:

[0041] The user logged in on the page.

[0042] The login operation generated a POST / auth / login request.

[0043] After the request enters the interface, the interface verifies the user's legitimacy, executes the SQL statement `Update Member set lastLoginTime='2022-05-06 10:00:00' where id=1`, and sends it to the MySQL server for execution. Simultaneously, the HTTP request information for `POST / auth / login` and the hash value (0x8f77c988) of `Update Member set lastLoginTime='2022-05-06 10:00:00'` are recorded in D1. The MySQL server updates the `Member` table, changing the last update time of the user with `id` 1 to `2022-05-06 10:00:00`. At the same time, two binlog records are generated: one for the SQL execution statement `Update Member set lastLoginTime='2022-05-06 10:00:00' where`. The first record has id=1. The second record is a data change record with type update, which records the change information as {"old":{"lastLoginTime":"2022-05-05 13:01:00"}, "data":{"id":1,"lastLoginTime":"2022-05-06 10:00:00"}}. This record is pushed to the binlog parsing server. After receiving it, the binlog server merges the two queries into one change record and records the query's hash value 0x8f77c98 and the values ​​before and after the data change in D2. When a user indicates that they have not logged in, we can find the login change record in D2. At the same time, we can find the HTTP request record of the user's login in D1 through SQL hash association, and further verify with the user based on the IP information and User-Agent information in the request record.

[0044] The above description is only a preferred embodiment of the present invention. All equivalent changes and modifications made within the scope of the claims of the present invention should be included in the scope of the present invention.

Claims

1. A data change backtracking query system from an HTTP request to a database, characterized in that: The system includes a record storage module, a change query module, and a data change information retrieval module. The record storage module handles user HTTP requests, which in turn send SQL statements to the server. The database receives and executes the SQL statements, then pushes them to a parsing server. The received change records and the hash value of the SPL statement are sent to a message queue server and finally stored in a document database. The change query module searches the database for historical records based on business conditions, extracts them, and uses them as criteria to query a list of historical change record documents in the document database, retrieving the HTTP request record information. The data change information retrieval module, in step S2, searches for associated change records based on the request time range and the SPL statement hash value, thereby enabling the forward location of changes made to database records by the HTTP request. The record storage module specifically includes the following steps: Step S1: The user performs an operation on the webpage, initiating an HTTP request; Step S2: The HTTP request enters the web application server, is recorded by the HTTP request interceptor, and appended to the context; Step S3: The HTTP request enters the web application server to execute business logic. The database driver sends an SQL statement to the database server. At the same time, the database driver filter intercepts the SQL statement and extracts the HTTP request from the context, and sends it to Topic:T1 of the message queue server. Step S4: After receiving the SQL statement, the MySQL database starts execution. After execution, it generates binlog records and pushes them to the binlog parsing server. Step S5: The binlog parsing server sends the received rowdata change records and the hash value of the SQL statement to Topic T2 of the message queue server; Step S6: The consumer pulls the data from the message queue server and pushes it to the document database for persistent storage. Topic:T1 is mapped and stored in document database D1, and Topic:T2 is mapped and stored in document database D2.

2. The data change backtracking query system from HTTP request to database according to claim 1, characterized in that: The query change module specifically includes the following steps: Step S10: Based on business conditions, search for the historical record information to be found in the MySQL database; Step S20: Extract the primary key from Step S10 and combine it with the time period of the historical change to be queried as the query condition; Step S30: Use the information extracted in Step S20 as the condition to query the list of historical change record documents in the document database D2; Step S40: Use the hash value of the SQL statement of each document in the document list found in Step S30 as the association query condition to query the HTTP request record information in the D1 database.

3. The data change backtracking query system from HTTP request to database according to claim 1, characterized in that: The data change information search module specifically includes the following steps: Step S50: Based on the request time range, request body, and request header, combine the above conditions in the D1 database to construct a query statement to obtain the HTTP request record to be searched; Step S60: Each record returned by the query in Step S50 has an SQL statement hash field. Use all the SQL statement hash values ​​in the record to perform the third query operation; Step S70: Use the SQL statement hash values ​​to search for related change records in the D2 database. The search results are caused by this HTTP request interface.

Images