Methods, devices, systems and media for tracking database DDL change operations

Abstract

This invention discloses a method, apparatus, system, and medium for tracking database DDL change operations, relating to the financial technology field. The method includes the following steps: upon receiving an SQL statement through a preset Structured Query Language (SQL) execution interface, detecting whether the SQL statement meets preset execution conditions; if the SQL statement meets the execution conditions, obtaining a first DDL change statement corresponding to the SQL statement after successful execution; obtaining user information of the user corresponding to the SQL statement, and storing the user information and the first DDL change statement in a preset database. This invention tracks DDL change operations of SQL statements through the stored DDL change statements, improving the success rate and efficiency of SQL statement execution, and determining the user corresponding to the SQL statement based on the stored user information, facilitating the management of the user corresponding to the SQL statement.

Description

Technical Field

[0001] This invention relates to the field of data processing technology in financial technology (Fintech), and in particular to a method, apparatus, system and medium for tracking database DDL change operations. Background Technology

[0002] With the continuous development of fintech, especially internet fintech, more and more technologies (such as distributed systems, blockchain, artificial intelligence, etc.) are being applied in the financial field. However, the financial industry is also placing higher demands on technology, and more and more functions need to be implemented through SQL (Structured Query Language) statements.

[0003] Currently, SQL execution tools typically act as database clients, executing SQL statements without checking them. This makes the corresponding systems vulnerable to vulnerabilities and malfunctions, and these tools cannot identify the user executing the SQL statement. Therefore, to improve the security of the business system executing the SQL statement and reduce vulnerabilities and malfunctions, SQL statement auditing is necessary. Currently, this is primarily done using the automated operations and maintenance system Inception. Inception is an automated operations and maintenance system that integrates auditing, execution, and rollback. It is modified from MySQL code and can clearly, thoroughly, and accurately audit MySQL SQL statements. However, Inception cannot track DDL (Data Definition Language) changes to SQL statements in the database.

[0004] In summary, currently, during the execution of SQL statements, it is impossible to determine the user corresponding to the SQL statement, and it is impossible to track DDL change operations within the SQL statement. Summary of the Invention

[0005] The main objective of this invention is to provide a method, apparatus, system, and medium for tracking database DDL change operations, aiming to solve the existing technical problems of being unable to track DDL change operations of SQL statements and being unable to determine the user corresponding to the SQL statement.

[0006] To achieve the above objectives, the present invention provides a method for tracking database DDL change operations, the method comprising the following steps:

[0007] When an SQL statement is received through the preset Structured Query Language (SQL) execution interface, it is checked whether the SQL statement meets the preset execution conditions.

[0008] If the SQL statement meets the execution conditions, then after the SQL statement is successfully executed, the first DDL change statement corresponding to the SQL statement is obtained;

[0009] Obtain the user information corresponding to the SQL statement, and store the user information and the first DDL change statement in a preset database.

[0010] Preferably, after the step of storing the user information and the first DDL change statement in a preset database, the method further includes:

[0011] Obtain the execution log of the SQL statement;

[0012] The second DDL change statement corresponding to the SQL statement is obtained from the execution log, and the second DDL change statement is stored in the preset database.

[0013] Preferably, after the step of storing the user information and the first DDL change statement in a preset database, the method further includes:

[0014] When a command to obtain the preset database snapshot is detected, the configuration information of the MySQL database corresponding to the SQL statement is obtained.

[0015] Obtain a first snapshot of the preset database based on the configuration information, and detect whether a second snapshot is stored in the preset database;

[0016] If the preset database stores a second snapshot, then determine whether there is a difference between the first snapshot and the second snapshot;

[0017] If there is a difference between the first snapshot and the second snapshot, the SQL statements in the first snapshot that differ from those in the second snapshot, along with the first snapshot, are stored in the preset database.

[0018] Preferably, the step of determining whether there is a difference between the first snapshot and the second snapshot if the preset database stores a second snapshot includes:

[0019] If the preset database stores a second snapshot, then calculate the first hash value of the first snapshot and the second hash value of the second snapshot, and determine whether the first hash value is equal to the second hash value;

[0020] If the first hash value is equal to the second hash value, then it is determined that there is no difference between the first snapshot and the second snapshot;

[0021] If the first hash value is not equal to the second hash value, then it is determined that there is a difference between the first snapshot and the second snapshot.

[0022] Preferably, after the step of storing the SQL statements in the first snapshot that differ from the second snapshot and the first snapshot in the preset database if there is a difference between the first snapshot and the second snapshot, the method further includes:

[0023] A database health report is generated based on the first snapshot, and the database health report is stored in the preset database.

[0024] Preferably, the step of detecting whether the SQL statement meets the preset execution conditions after receiving the SQL statement through the preset SQL execution interface includes:

[0025] When an SQL statement is received through a preset SQL execution interface, it is checked whether the SQL statement conforms to the preset syntax rules.

[0026] If the SQL statement is detected to be inconsistent with the syntax specification, then it is determined that the SQL statement does not meet the preset execution conditions;

[0027] If the SQL statement is detected to conform to the syntax specification, then check whether the SQL statement conforms to the preset format specification;

[0028] If the SQL statement is detected to conform to the format specification, then the SQL statement is determined to meet the execution conditions;

[0029] If the SQL statement is detected to be inconsistent with the format specification, then the SQL statement is determined to be inconsistent with the execution conditions.

[0030] Preferably, the step of detecting whether the SQL statement conforms to a preset format specification if the SQL statement is detected to conform to the syntax specification includes:

[0031] If the SQL statement is detected to conform to the syntax specification, then it is checked whether the character set of the database creation statement in the SQL statement is a preset character encoding, whether the data table creation statement in the SQL statement has a primary key and comments, and whether each field in the SQL statement has comments.

[0032] If the character set of the database creation statement in the SQL statement is a preset character encoding, the data table creation statement in the SQL statement contains a primary key and comments, and each field in the SQL statement contains comments, then the SQL statement is determined to conform to the preset format specification.

[0033] Preferably, the step of obtaining the user information of the user corresponding to the SQL statement and storing the user information and the first DDL change statement in a preset database includes:

[0034] Obtain the user information of the user corresponding to the SQL statement, as well as the execution time of the SQL statement and the database name of the MySQL database corresponding to the SQL statement;

[0035] The user information, the execution time, the database name, and the first DDL change statement are associated and stored in a preset database.

[0036] Furthermore, to achieve the above objectives, the present invention also provides a tracking device for database DDL change operations, the tracking device for database DDL change operations comprising:

[0037] The detection module is used to detect whether the SQL statement meets the preset execution conditions after receiving the SQL statement through the preset structured query language (SQL) execution interface.

[0038] The acquisition module is used to acquire the first DDL change statement corresponding to the SQL statement after the SQL statement is successfully executed, if the SQL statement meets the execution conditions; and to acquire the user information of the user corresponding to the SQL statement.

[0039] The storage module is used to store the user information and the first DDL change statement in a preset database.

[0040] Furthermore, to achieve the above objectives, the present invention also provides a database DDL change operation tracking system, the database DDL change operation tracking system including a memory, a processor, and a database DDL change operation tracking program stored in the memory and executable on the processor, the database DDL change operation tracking program being executed by the processor implementing the steps of the database DDL change operation tracking method as described above.

[0041] Furthermore, to achieve the above objectives, the present invention also provides a computer-readable storage medium storing a database DDL change operation tracking program, which, when executed by a processor, implements the steps of the database DDL change operation tracking method as described above.

[0042] This invention provides a unified SQL execution interface. This interface receives all SQL statements to be executed and checks whether each SQL statement meets the execution conditions before execution. Only if the SQL statement meets the conditions will it be executed. After successful execution, the DDL modification statement and the user information of the corresponding user are retrieved and stored. By checking whether the SQL statement meets the execution conditions, the security of the system hosting the SQL statement is improved, and vulnerabilities are reduced. Furthermore, by retrieving the DDL modification statement to track the DDL modification operations of the SQL statement and by identifying the user corresponding to the SQL statement based on the stored user information, management of the user associated with the SQL statement is facilitated. This allows for quick identification of the corresponding user when problems arise during SQL statement execution, resolving issues and improving the success rate and efficiency of SQL statement execution. Attached Figure Description

[0043] Figure 1 This is a flowchart illustrating the first embodiment of the database DDL change operation tracking method of the present invention;

[0044] Figure 2 This is a flowchart illustrating the second embodiment of the database DDL change operation tracking method of the present invention;

[0045] Figure 3 This is a flowchart illustrating the third embodiment of the database DDL change operation tracking method of the present invention;

[0046] Figure 4 This is a functional schematic diagram of a preferred embodiment of the tracking device for database DDL change operations of the present invention.

[0047] Figure 5 This is a schematic diagram of the hardware operating environment involved in the embodiments of the present invention.

[0048] The realization of the objective, functional features and advantages of the present invention will be further explained in conjunction with the embodiments and with reference to the accompanying drawings. Detailed Implementation

[0049] It should be understood that the specific embodiments described herein are merely illustrative of the invention and are not intended to limit the invention.

[0050] This invention provides a method for tracking database DDL change operations, referring to... Figure 1 , Figure 1 This is a flowchart illustrating the first embodiment of the database DDL change operation tracking method of the present invention.

[0051] This invention provides an embodiment of a method for tracking database DDL change operations. It should be noted that although the logical order is shown in the flowchart, in some cases, the steps shown or described may be performed in a different order than that shown here.

[0052] The method for tracking database DDL change operations is applied in a DDL tracking system. The DDL tracking system may include mobile terminals such as tablets, laptops, handheld computers, and personal digital assistants (PDAs), as well as fixed terminals such as digital TVs and desktop computers. In various embodiments of the method for tracking database DDL change operations, the executing entity is omitted from the description for ease of explanation.

[0053] The DDL tracing system includes a UI (User Interface) module, a DB (Database) management module, a DDL change history module, a DB health report module, an SQL parser, an SQL specification detector, a DB snapshot scheduled task module, an SQL executor, a binlog subscription module, and a persistence module. Among them, binlog is a binary file used to record the SQL statements used by users to update the database.

[0054] The UI module provides users with an interface for logging into the DDL tracing system, managing various databases within the system, and executing SQL statements. In other words, the UI provides a unified SQL execution interface. The DB management module registers and maintains the configuration information of the DB databases, including but not limited to the database name, IP address (Internet Protocol), and port number. The DDL change history module generates DDL change records based on DDL change statements and provides functions for querying these records. This allows users to manage the versions of SQL statements in the MySQL database based on these records, reducing the risk of deploying business systems using the MySQL database. Each executed SQL statement carrying a DDL change statement represents a version of the SQL statement and is recorded. The DDL health report module generates a database health report based on DB snapshots. The SQL parser parses SQL statements and analyzes the parsing results to ensure they conform to SQL syntax rules. The SQL specification checker checks whether SQL statements conform to preset format specifications. The SQL executor calls the MySQL database API (Application Programming Interface) to execute SQL statements in the MySQL database and returns the execution results to the terminal corresponding to the SQL statement. The DB snapshot scheduled task module is used to obtain scheduled DB snapshots. The binlog subscription module is used to subscribe to and parse the binlog logs generated after executing SQL statements. The persistence module is responsible for the storage and management of DDL tracing system data. The MySQL database is the business system database that needs to be tracked and managed in this embodiment of the invention. The MySQL database stores tables and data corresponding to each business system. In the DDL tracing system, there is at least one business system MySQL database that is being tracked and managed. The DB is used to store configuration information, DDL change statements, DB snapshots, and DB health reports for each business system MySQL database. The configuration information of the business system MySQL database includes, but is not limited to, the database name, IP address, and port number.

[0055] Methods for tracking database DDL changes include:

[0056] Step S10: After receiving an SQL statement through the preset Structured Query Language (SQL) execution interface, check whether the SQL statement meets the preset execution conditions.

[0057] When an SQL statement is received through the pre-configured SQL execution interface in the DDL tracing system, it is parsed by an SQL parser to obtain the parsing result. Based on the parsing result, the system checks whether the received SQL statement meets the preset execution conditions. It should be noted that in this embodiment, the DDL tracing system provides a unified SQL execution interface through a UI module. After logging into the DDL tracing system, the user can input the SQL statement to be executed through this interface. This SQL statement can be a modification statement that alters existing MySQL databases and tables in the business system, or it can be a statement that inserts, updates, or queries data.

[0058] Further, step S10 includes:

[0059] Step a: After receiving an SQL statement through the preset SQL execution interface, check whether the SQL statement conforms to the preset syntax rules.

[0060] Step b: If the SQL statement is detected to be inconsistent with the syntax specification, then it is determined that the SQL statement does not meet the preset execution conditions.

[0061] Specifically, after receiving an SQL statement through a preset SQL execution interface, the system checks whether the SQL statement conforms to a preset syntax specification. This preset syntax specification can be SQL92, SQL99, or a MySQL dialect, etc. The specific syntax specification used in this embodiment can be set by the user of the DDL tracing system according to specific needs. For example, SQL92 specifies the data types, conditional expressions, usable functions, constraints between database tables, and database commands in SQL statements; while the MySQL dialect has specific requirements for built-in functions in SQL statements.

[0062] If an SQL statement is detected as not conforming to the syntax rules, it is determined that the SQL statement does not meet the preset execution conditions. For example, if the preset syntax rule is SQL92, and an SQL statement is detected as not conforming to the syntax rules set by SQL92, it is determined that the SQL statement does not meet the preset execution conditions. It should be noted that there can be one or more preset syntax rules.

[0063] Step c: If the SQL statement is detected to conform to the syntax specification, then check whether the SQL statement conforms to the preset format specification.

[0064] Step d: If the SQL statement is detected to conform to the format specification, then the SQL statement is determined to meet the execution conditions.

[0065] Step e: If the SQL statement is detected to be inconsistent with the format specification, then it is determined that the SQL statement does not meet the execution conditions.

[0066] If the SQL statement is detected to conform to the pre-set syntax rules, then it is checked whether the SQL statement conforms to the pre-set format rules. If the SQL statement conforms to the format rules, it is determined that the SQL meets the execution conditions; otherwise, it is determined that the SQL does not meet the execution conditions. The format rules can be set according to specific needs. For example, the format rules can be set so that the number of characters in the database table name and field name corresponding to the SQL statement is less than or equal to a preset number. This preset number can be set according to specific needs, such as 64 characters or 48 characters. In this case, if the number of characters in the database table name and the corresponding field name of the SQL statement are both less than or equal to the preset number, the SQL statement meets the execution conditions; if the number of characters in the database table name and / or the corresponding field name of the SQL statement is greater than the preset number, it is determined that the SQL statement does not meet the execution conditions. It should be noted that the database table name and field name corresponding to the SQL statement are the database table names and field names appearing in the SQL statement.

[0067] Further, step c includes:

[0068] Step c1: If the SQL statement is detected to conform to the syntax specification, then check whether the character set of the database creation statement in the SQL statement is a preset character encoding, check whether the data table creation statement in the SQL statement has a primary key and comments, and check whether each field in the SQL statement has comments.

[0069] Furthermore, the process of detecting whether an SQL statement conforms to a preset format specification can also include: if the SQL statement is found to conform to the syntax specification, then checking whether the character set of the database creation statement in the SQL statement is a preset character encoding, checking whether the data table creation statement in the SQL statement contains a primary key and comments, and checking whether each field in the SQL statement contains comments. In this embodiment, the preset character encoding can be UTF-8 (8-bit Unicode Transformation Format), which is a variable-length character encoding for Unicode, also known as Universal Character Set. In other embodiments, the preset character encoding can be set to other character encodings.

[0070] It should be noted that in SQL statements, statements that create a database contain the database keyword, and statements that create a table contain the table keyword. Therefore, the presence of a database creation statement or a table creation statement in an SQL statement can be detected by using the database keyword. In this embodiment, the form in which the table keyword and database keyword are represented is not limited. A primary key refers to a column or combination of columns whose values ​​uniquely identify each row in a table. It enforces the integrity of the table entity and is primarily used for foreign key relationships with other tables, as well as for modifying and deleting records. Comments are explanations and clarifications of the code. Furthermore, to improve the success rate of SQL statement execution, in the process of detecting whether comments exist in various fields of an SQL statement, only the user-defined fields in the SQL statement need to be checked for comments.

[0071] Understandably, if the SQL statement does not contain a database creation statement or a table creation statement, it is not necessary to check whether the character set of the database creation statement in the SQL statement is the preset character encoding, nor is it necessary to check whether the table creation statement in the SQL statement contains a primary key or comments. In this case, it is possible to directly check whether each field in the SQL statement contains comments, and whether the number of characters in the corresponding database name and table name in the SQL statement is less than or equal to the preset number, etc.

[0072] Furthermore, it can also detect whether there are comments in each field of the SQL statement. Understandably, when there are comments in the databases, tables, and fields of the SQL statement, it can be determined that the SQL statement conforms to the preset comment format specifications; when there are no comments in the databases, tables, and fields of the SQL statement, it can be determined that the SQL statement does not conform to the preset comment format specifications.

[0073] Step c2: If the character set of the database creation statement in the SQL statement is a preset character encoding, the data table creation statement in the SQL statement contains a primary key and comments, and each field in the SQL statement contains comments, then it is determined that the SQL statement conforms to the preset format specification.

[0074] If the SQL statement creates a database using a preset character encoding, creates a table using a primary key and comments, and all fields in the SQL statement contain comments, then the SQL statement is determined to conform to the preset format specification. If the SQL statement meets at least one of the following three conditions, then the SQL statement is determined to not conform to the preset format specification: the character set of the SQL statement creates a database not using a preset character encoding, the SQL statement creates a table without a primary key and comments, and all fields in the SQL statement contain comments.

[0075] It should be noted that by checking whether SQL statements meet the execution conditions, non-compliant SQL statements are reduced, thereby reducing vulnerabilities in the corresponding business systems and the likelihood of failures in those systems. This lowers the risk to the business processes associated with SQL statements and improves their robustness.

[0076] Step S20: If the SQL statement meets the execution conditions, then after the SQL statement is successfully executed, obtain the first DDL change statement corresponding to the SQL statement.

[0077] Step S30: Obtain the user information of the user corresponding to the SQL statement, and store the user information and the first DDL change statement in a preset database.

[0078] If an SQL statement is detected as meeting the execution conditions, it is executed by the SQL executor, and the success of the SQL statement execution is checked. It can be understood that when the SQL statement is a database creation statement, if the SQL statement executes successfully, a database will be generated in the corresponding business system; when the SQL statement is a data query, if the SQL statement executes successfully, the required data results can be obtained. If the SQL statement executes successfully, the corresponding DDL change statement is obtained after the SQL statement is executed, and this DDL change statement is recorded as the first DDL change statement. The user information of the user corresponding to the SQL statement operation is obtained, and this user information and the first DDL change statement are stored in a preset database. The DDL change statement refers to the DDL change operations involved in the execution of the SQL statement, and the user information includes the user's account, name, email address, and / or contact number, etc., who submitted the SQL statement to the DDL tracking system. In this embodiment, the preset database is a DB database. The DDL change statement corresponding to the SQL statement is a modification statement in the SQL statement involving creating a database, modifying a database, creating a data table, modifying a data table, deleting a data table, creating an index, deleting an index, inserting data into the database, and deleting data. It is understood that in this embodiment, the executed SQL statement is a modification statement that modifies the MySQL database. If the SQL statement is a query statement, there is no corresponding DDL change statement, and in this case, it is not necessary to track the DDL change operation corresponding to the SQL statement.

[0079] Furthermore, after a SQL statement executes successfully, a success message is generated and sent to the terminal corresponding to the SQL statement. This message prompts the user who submitted the SQL statement, indicating successful execution. It should be noted that if the SQL statement is a query, the success message will include the retrieved data, which will then be sent to the terminal corresponding to the SQL statement. Conversely, if an SQL statement fails to execute, a failure message is generated and sent to the terminal corresponding to the SQL statement. This message prompts the user who submitted the SQL statement, indicating execution failure.

[0080] For example, when it is necessary to modify certain data in a financial institution (bank, insurance or securities institution), the SQL execution interface can receive the SQL statement for modifying the data uploaded by the corresponding user of the financial institution. Only when the SQL statement is detected to meet the execution conditions will the data corresponding to the financial institution be modified according to the SQL statement, and the DDL change statement corresponding to the SQL statement and the user information of the corresponding user of the financial institution be stored.

[0081] This embodiment provides a unified SQL execution interface. This interface receives all SQL statements to be executed and checks whether each SQL statement meets the execution conditions before execution. Only if the SQL statement meets the conditions will it be executed. After successful execution, the corresponding DDL modification statement and the user information of the user associated with the SQL statement are retrieved and stored. By checking whether the SQL statement meets the execution conditions, the security of the system hosting the SQL statement is improved, and vulnerabilities are reduced. Furthermore, by retrieving the DDL modification statement to track the DDL modification operations of the SQL statement and by identifying the user associated with the SQL statement based on the stored user information, management of the user associated with the SQL statement is facilitated. This allows for quick identification of the corresponding user when problems arise during SQL statement execution, resolving issues during execution and improving the success rate and efficiency of SQL statement execution.

[0082] Furthermore, a second embodiment of the method for tracking database DDL change operations of the present invention is proposed.

[0083] The difference between the second embodiment of the database DDL change operation tracking method and the first embodiment of the database DDL change operation tracking method is that, referring to... Figure 2 The methods for tracking database DDL change operations also include:

[0084] Step S40: Obtain the execution log of the SQL statement.

[0085] After executing an SQL statement, the system subscribes to the MySQL binlog to obtain the execution log of the SQL statement. In this embodiment, the execution log is the binlog log. In this embodiment, the binlog subscription module pre-subscribes to the binlog logs of the SQL statement execution. When the MySQL database generates new binlog logs, the binlog subscription module can obtain them in real time. When calling the MySQL database to execute SQL statements, corresponding binlog logs are generated during processes such as creating the database, modifying the database, creating tables, modifying tables, deleting tables, creating indexes, deleting indexes, inserting data into the database, deleting data, and querying data.

[0086] Step S50: Obtain the second DDL change statement corresponding to the SQL statement according to the execution log, and store the second DDL change statement in the preset database.

[0087] After obtaining the execution log of the SQL statement, the execution log is parsed, i.e., the binlog log is parsed, and the DDL modification statement corresponding to the SQL statement is obtained based on the parsing results. For ease of differentiation, in this embodiment, the DDL modification statement in the SQL statement obtained through the execution log is recorded as the second DDL modification statement, and this second DDL modification statement is stored in a preset database. Specifically, the binlog log is parsed, and the modification keyword corresponding to the DDL modification statement is searched in the binlog log. The second DDL modification statement in the SQL statement is determined based on the modification keyword, such as "create table" for creating a data table and "delete" for deleting data. It should be noted that the modification keywords corresponding to the DDL modification statement are not limited to the examples given in this embodiment. Users can set one or more modification keywords for each DDL modification statement according to specific needs.

[0088] Under normal circumstances, there are many execution paths for SQL statements, which may result in situations where DDL change operations in the MySQL database are not tracked. However, the execution of any SQL statement will generate binlog logs. This embodiment uses binlog logs to track DDL change operations in the MySQL database, thereby improving the accuracy of tracking DDL change operations in the MySQL database.

[0089] Furthermore, in this embodiment, the binlog subscription module and the SQL executor adopt a master-slave synchronization mechanism, so that the process of executing SQL statements and the process of obtaining the second DDL change statement through binlog logs adopt a master-slave synchronization mechanism, so as not to affect the performance of MySQL database, that is, not to affect other functions of MySQL database, while tracking the DDL change operation of MySQL database through binlog logs.

[0090] Furthermore, a third embodiment of the method for tracking database DDL change operations of the present invention is proposed.

[0091] The difference between the third embodiment of the database DDL change operation tracking method and the first or second embodiment of the database DDL change operation tracking method is that, referring to... Figure 3 The methods for tracking database DDL change operations also include:

[0092] Step S60: After detecting the instruction to obtain the preset database snapshot, obtain the configuration information of the MySQL database corresponding to the SQL statement.

[0093] The DDL tracing system retrieves information from the tracked database at a set time every morning. Upon detecting a command to retrieve a preset database snapshot, it obtains the configuration information of the MySQL database corresponding to the traced SQL statements. A database snapshot is a read-only, static view of the DDL statements of the tables and databases in the tracked database (source database). Therefore, by using a snapshot, modified content in the database can be obtained.

[0094] In this embodiment, the trigger time for obtaining a snapshot of a preset database is pre-set. Specifically, the trigger time is set to a relatively idle time for the preset database, so as to achieve the effect of tracking DDL changes in the MySQL database without affecting the performance of the preset database. For example, the trigger time can be set to 12:00 AM or 1:00 AM. Furthermore, it can also detect whether there is no data stored in the preset database within a preset time period. If no data is detected in the preset database within the preset time period, it is determined that the preset database is in an idle state, and the acquisition command can be automatically triggered at this time. The preset time period can be set according to specific needs, such as 20 minutes, 30 minutes, or 15 minutes.

[0095] Step S70: Obtain the first snapshot of the preset database according to the configuration information, and detect whether the preset database stores a second snapshot.

[0096] After obtaining the MySQL database configuration information, the system connects the MySQL database to a preset database based on this information to retrieve all tables stored in the MySQL database. Then, it retrieves the table creation statements for all tables stored in the MySQL database, i.e., the SQL statements for creating the tables. The system then extracts the table structure information from these statements and generates a first (optionally current) snapshot of the preset database based on this information. Finally, it checks whether a second (optionally previous day's) snapshot is stored in the preset database. The table structure information includes, but is not limited to, the database name, primary key, comments, and table creation time. The second snapshot is a historical snapshot stored in the preset database.

[0097] Step S80: If the preset database stores a second snapshot, then determine whether there is a difference between the first snapshot and the second snapshot.

[0098] If a second snapshot is detected in the preset database, it is determined whether there is a difference between the first and second snapshots. It should be noted that if there is no difference between the first and second snapshots, it is determined that there are no DDL modification statements in the MySQL database at this time; if there is a difference between the first and second snapshots, it is determined that there are DDL modification statements in the MySQL database at this time.

[0099] Further, in step S80,

[0100] Step f: If the preset database stores a second snapshot, calculate the first hash value of the first snapshot and the second hash value of the second snapshot, and determine whether the first hash value is equal to the second hash value.

[0101] Step g: If the first hash value is equal to the second hash value, then it is determined that there is no difference between the first snapshot and the second snapshot.

[0102] In step h, if the first hash value is not equal to the second hash value, then it is determined that there is a difference between the first snapshot and the second snapshot.

[0103] Specifically, if a second snapshot is detected stored in the preset database, the hash values ​​of the first and second snapshots are calculated, and the hash value of the first snapshot is recorded as the first hash value, and the hash value of the second snapshot is recorded as the second hash value. The hash value calculation algorithm includes, but is not limited to, MD5 (Message Digest Algorithm) and SHA (Secure Hash Algorithm). After calculating the first and second hash values, it is determined whether the first hash value is equal to the second hash value. If the first hash value is equal to the second hash value, it is determined that there is no difference between the first and second snapshots; if the first hash value is not equal to the second hash value, it is determined that there is a difference between the first and second snapshots.

[0104] Step S90: If there is a difference between the first snapshot and the second snapshot, then store the SQL statements in the first snapshot that differ from those in the second snapshot, along with the first snapshot, into the preset database.

[0105] If a difference is determined between the first snapshot and the second snapshot, then the SQL statements in the first snapshot that differ from those in the second snapshot are identified, and these SQL statements, along with the first snapshot itself, are stored in a preset database. It can be understood that the SQL statements in the first snapshot that differ from those in the second snapshot are DDL change statements.

[0106] Furthermore, if the second snapshot is not stored in the preset database, the first snapshot is directly stored in the preset database; or if it is determined that there is no difference between the first snapshot and the second snapshot, the first snapshot is not stored in the preset database. In this case, it indicates that no SQL statement with DDL change statement has been executed in the MySQL database since the last snapshot of the preset database was obtained.

[0107] This embodiment further tracks DDL change statements in the MySQL database by using database snapshots, avoiding the omission of DDL change statements during SQL statement execution and improving the accuracy of DDL change operation tracking.

[0108] Furthermore, the methods for tracking database DDL change operations also include:

[0109] Step i: Generate a database health report based on the first snapshot, and store the database health report in the preset database.

[0110] Furthermore, after storing the first snapshot in the preset database, a database health report is generated based on the first snapshot and stored in the preset database. The database health report contains the detection results of whether the SQL statement meets the execution conditions, the execution result of the SQL statement, and the execution time of the SQL statement. Furthermore, during the generation of the database health report, it is also possible to generate a database health report based on the first snapshot, the first DDL change statement, and the second DDL change statement. In this case, only the information that differs between the first snapshot, the first DDL change statement, and the second DDL change statement will be used to generate the database health report, ensuring the integrity of the generated database health report and avoiding duplication of information in the database health report.

[0111] It should be noted that after the database health report is generated, the user responsible for maintaining the business system can obtain the database health report of the corresponding database from the preset database as needed, and then maintain the corresponding database according to the database health report to improve the robustness and maintainability of the corresponding business system.

[0112] Furthermore, a fourth embodiment of the method for tracking database DDL change operations of the present invention is proposed.

[0113] The difference between the fourth embodiment of the database DDL change operation tracking method and the first, second, or third embodiment of the database DDL change operation tracking method is that step S30 includes:

[0114] Step j: Obtain the user information of the user corresponding to the SQL statement, as well as the execution time of the SQL statement and the database name of the MySQL database corresponding to the SQL statement;

[0115] Step k involves associating and storing the user information, execution time, database name, and the first DDL change statement in a preset database.

[0116] The system retrieves user information corresponding to the SQL statement, as well as the execution time and database name of the corresponding MySQL database. The MySQL database storing the SQL statement is the one used for this process. Once the user information, execution time, and database name are obtained, they are associated with and stored in a preset database. This allows for the retrieval of other information related to the DDL change statement within the preset database. By storing the execution time, user information, and database name in the preset database, the system can categorize and query the corresponding DDL change statement using these parameters, improving the intelligence and efficiency of DDL change statement queries.

[0117] In addition, refer to Figure 4 The present invention also provides a tracking device for database DDL change operations, the tracking device for database DDL change operations comprising:

[0118] The detection module 10 is used to detect whether the SQL statement meets the preset execution conditions after receiving the SQL statement through the preset structured query language (SQL) execution interface.

[0119] The acquisition module 20 is used to acquire the first DDL change statement corresponding to the SQL statement after the SQL statement is successfully executed if the SQL statement meets the execution conditions; and to acquire the user information of the user corresponding to the SQL statement.

[0120] Storage module 30 is used to store the user information and the first DDL change statement in a preset database.

[0121] Furthermore, the acquisition module 20 is also used to acquire the execution log of the SQL statement; and acquire the second DDL change statement corresponding to the SQL statement based on the execution log;

[0122] The storage module 30 is also used to store the second DDL change statement into the preset database.

[0123] Furthermore, the acquisition module 20 is also configured to, upon detecting an acquisition instruction to acquire the preset database snapshot, acquire configuration information of the MySQL database corresponding to the SQL statement; and acquire the first snapshot of the preset database according to the configuration information;

[0124] The detection module 10 is also used to detect whether the preset database stores a second snapshot;

[0125] The database DDL change operation tracking device also includes:

[0126] The judgment module is used to determine whether there is a difference between the first snapshot and the second snapshot if the preset database stores a second snapshot;

[0127] The storage module 30 is further configured to store the SQL statements in the first snapshot that differ from those in the second snapshot, along with the first snapshot, into the preset database if there is a difference between the first snapshot and the second snapshot.

[0128] Furthermore, the determination module includes:

[0129] A calculation unit is configured to calculate the first hash value of the first snapshot and the second hash value of the second snapshot if the preset database stores a second snapshot;

[0130] A judgment unit is used to determine whether the first hash value is equal to the second hash value;

[0131] The first determining unit is configured to determine that there is no difference between the first snapshot and the second snapshot if the first hash value is equal to the second hash value; and to determine that there is a difference between the first snapshot and the second snapshot if the first hash value is not equal to the second hash value.

[0132] Furthermore, the database DDL change operation tracking device also includes:

[0133] The generation module is used to generate a database health report based on the first snapshot;

[0134] The storage module 30 is also used to store the database health report into the preset database.

[0135] Furthermore, the detection module 10 includes:

[0136] The detection unit is used to detect whether the SQL statement conforms to the preset syntax rules after receiving the SQL statement through the preset SQL execution interface.

[0137] The second determining unit is used to determine that the SQL statement does not meet the preset execution conditions if it is detected that the SQL statement does not conform to the syntax specification.

[0138] The detection unit is further configured to detect whether the SQL statement conforms to a preset format specification if the SQL statement is detected to conform to the syntax specification.

[0139] The second determining unit is further configured to determine that the SQL statement meets the execution conditions if the SQL statement is detected to conform to the format specification;

[0140] If the SQL statement is detected to be inconsistent with the format specification, then the SQL statement is determined to be inconsistent with the execution conditions.

[0141] Furthermore, the detection unit includes:

[0142] The detection subunit is used to detect whether the character set of the database creation statement in the SQL statement is a preset character encoding, whether the data table creation statement in the SQL statement has a primary key and comments, and whether each field in the SQL statement has comments if the SQL statement is found to conform to the syntax specification.

[0143] The sub-unit is determined if the character set of the database creation statement in the SQL statement is a preset character encoding, the data table creation statement in the SQL statement contains a primary key and comments, and each field in the SQL statement contains comments, then the SQL statement is determined to conform to the preset format specification.

[0144] Furthermore, the acquisition module 20 is also used to acquire the execution time of the SQL statement and the database name of the MySQL database corresponding to the SQL statement;

[0145] The storage module 30 is also used to associate and store the user information, the execution time, the database name, and the first DDL change statement in a preset database.

[0146] It should be noted that the various embodiments of the database DDL change operation tracking device are basically the same as the various embodiments of the database DDL change operation tracking method described above, and will not be described in detail here.

[0147] Furthermore, this invention also provides a system for tracking database DDL change operations. For example... Figure 5 As shown, Figure 5 This is a schematic diagram of the hardware operating environment involved in the embodiments of the present invention.

[0148] It should be noted that, Figure 5 This is a schematic diagram of the hardware operating environment of a system for tracking database DDL change operations. In this embodiment of the invention, the system for tracking database DDL change operations can be a PC, a laptop, or other terminal device.

[0149] like Figure 5As shown, the database DDL change operation tracking system may include: a processor 1001, such as a CPU; a memory 1005; a user interface 1003; a network interface 1004; and a communication bus 1002. The communication bus 1002 is used to establish communication between these components. The user interface 1003 may include a display screen or an input unit such as a keyboard; optionally, the user interface 1003 may also include a standard wired interface or a wireless interface. The network interface 1004 may optionally include a standard wired interface or a wireless interface (such as a Wi-Fi interface). The memory 1005 may be high-speed RAM or non-volatile memory, such as a disk drive. Optionally, the memory 1005 may also be a storage device independent of the aforementioned processor 1001.

[0150] Optionally, the database DDL change operation tracking system may also include a camera, RF (Radio Frequency) circuitry, sensors, audio circuitry, WiFi module, etc.

[0151] Those skilled in the art will understand that Figure 5 The database DDL change operation tracking system structure shown in the figure does not constitute a limitation on the database DDL change operation tracking system. It may include more or fewer components than shown, or combine certain components, or have different component arrangements.

[0152] like Figure 5 As shown, the memory 1005, which serves as a computer storage medium, may include an operating system, a network communication module, a user interface module, and a database DDL change operation tracking program. The operating system is a program that manages and controls the hardware and software resources of the database DDL change operation tracking system, and supports the operation of the database DDL change operation tracking program and other software or programs.

[0153] exist Figure 5 In the database DDL change operation tracking system shown, the user interface 1003 is mainly used to connect to the client (user terminal) and communicate data with the client (user terminal); the network interface 1004 is mainly used to communicate data with the backend server; the processor 1001 can be used to call the database DDL change operation tracking program stored in the memory 1005 and execute the steps of the database DDL change operation tracking method as described above.

[0154] The specific implementation of the database DDL change operation tracking system of the present invention is basically the same as the embodiments of the database DDL change operation tracking method described above, and will not be repeated here.

[0155] Furthermore, embodiments of the present invention also propose a computer-readable storage medium storing a database DDL change operation tracking program, which, when executed by a processor, implements the steps of the database DDL change operation tracking method described above.

[0156] The specific implementation of the computer-readable storage medium of the present invention is basically the same as the embodiments of the above-described database DDL change operation tracking method, and will not be described again here.

[0157] It should be noted that, in this document, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Unless otherwise specified, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes that element.

[0158] The sequence numbers of the above embodiments of the present invention are for descriptive purposes only and do not represent the superiority or inferiority of the embodiments.

[0159] Through the above description of the embodiments, those skilled in the art can clearly understand that the methods of the above embodiments can be implemented by means of software plus necessary general-purpose hardware platforms. Of course, they can also be implemented by hardware, but in many cases the former is a better implementation method. Based on this understanding, the technical solution of the present invention, in essence, or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk) and includes several instructions to cause a terminal device (which may be a mobile phone, computer, server, air conditioner, or network device, etc.) to execute the methods described in the various embodiments of the present invention.

[0160] The above are merely preferred embodiments of the present invention and do not limit the scope of the patent. Any equivalent structural or procedural transformations made based on the description and drawings of the present invention, or direct or indirect applications in other related technical fields, are similarly included within the scope of patent protection of the present invention.

Claims

1. A method for tracking database Data Definition Language (DDL) change operations, characterized in that, The method for tracking database data definition language (DDL) change operations includes the following steps: When an SQL statement is received through the preset Structured Query Language (SQL) execution interface, it is checked whether the SQL statement meets the preset execution conditions. If the SQL statement meets the execution conditions, then after the SQL statement is successfully executed, the first DDL change statement corresponding to the SQL statement is obtained; the first DDL change statement is the DDL change operation involved in the execution of the SQL statement. Obtain the user information of the user corresponding to the SQL statement, and store the user information and the first DDL change statement in a preset database; When a command to obtain the preset database snapshot is detected, the configuration information of the MySQL database corresponding to the SQL statement is obtained. The first snapshot of the preset database is obtained according to the configuration information, and it is detected whether the preset database stores a second snapshot; the second snapshot is a historical snapshot stored in the preset database. If the preset database stores a second snapshot, then determine whether there is a difference between the first snapshot and the second snapshot; If there is a difference between the first snapshot and the second snapshot, the SQL statements in the first snapshot that differ from the second snapshot and the first snapshot are stored in the preset database; the SQL statements that differ from the second snapshot include third DDL change statements.

2. The method for tracking database data definition language (DDL) change operations as described in claim 1, characterized in that, After the step of storing the user information and the first DDL change statement to a preset database, the method further includes: Obtain the execution log of the SQL statement; The second DDL change statement corresponding to the SQL statement is obtained from the execution log, and the second DDL change statement is stored in the preset database.

3. The method for tracking database data definition language (DDL) change operations as described in claim 1, characterized in that, The step of determining whether there is a difference between the first snapshot and the second snapshot if the preset database stores a second snapshot includes: If the preset database stores a second snapshot, then calculate the first hash value of the first snapshot and the second hash value of the second snapshot, and determine whether the first hash value is equal to the second hash value; If the first hash value is equal to the second hash value, then it is determined that there is no difference between the first snapshot and the second snapshot; If the first hash value is not equal to the second hash value, then it is determined that there is a difference between the first snapshot and the second snapshot.

4. The method for tracking database data definition language (DDL) change operations as described in claim 1, characterized in that, The step of storing the SQL statements in the first snapshot that differ from the second snapshot, along with the first snapshot itself, into the preset database if there is a difference between the first snapshot and the second snapshot is present, further includes: A database health report is generated based on the first snapshot, and the database health report is stored in the preset database.

5. The method for tracking database data definition language (DDL) change operations as described in claim 1, characterized in that, The step of detecting whether the SQL statement meets the preset execution conditions after receiving the SQL statement through the preset SQL execution interface includes: When an SQL statement is received through a preset SQL execution interface, it is checked whether the SQL statement conforms to the preset syntax rules. If the SQL statement is detected to be inconsistent with the syntax specification, then it is determined that the SQL statement does not meet the preset execution conditions; If the SQL statement is detected to conform to the syntax specification, then check whether the SQL statement conforms to the preset format specification; If the SQL statement is detected to conform to the format specification, then the SQL statement is determined to meet the execution conditions; If the SQL statement is detected to be inconsistent with the format specification, then the SQL statement is determined to be inconsistent with the execution conditions.

6. The method for tracking database data definition language (DDL) change operations as described in claim 5, characterized in that, The step of detecting whether the SQL statement conforms to the preset format specification if the SQL statement is detected to conform to the syntax specification includes: If the SQL statement is detected to conform to the syntax specification, then it is checked whether the character set of the database creation statement in the SQL statement is a preset character encoding, whether the data table creation statement in the SQL statement has a primary key and comments, and whether each field in the SQL statement has comments. If the character set of the database creation statement in the SQL statement is a preset character encoding, the data table creation statement in the SQL statement contains a primary key and comments, and each field in the SQL statement contains comments, then the SQL statement is determined to conform to the preset format specification.

7. The method for tracking database data definition language (DDL) change operations as described in any one of claims 1 to 6, characterized in that, The step of obtaining the user information of the user corresponding to the SQL statement and storing the user information and the first DDL change statement in a preset database includes: Obtain the user information of the user corresponding to the SQL statement, as well as the execution time of the SQL statement and the database name of the MySQL database corresponding to the SQL statement; The user information, the execution time, the database name, and the first DDL change statement are associated and stored in a preset database.

8. A device for tracking database data definition language (DDL) change operations, characterized in that, The tracking device for database data definition language (DDL) change operations includes: The detection module is used to detect whether the SQL statement meets the preset execution conditions after receiving the SQL statement through the preset structured query language (SQL) execution interface. The acquisition module is used to acquire, if the SQL statement meets the execution conditions, the first DDL change statement corresponding to the SQL statement after the SQL statement is successfully executed; the first DDL change statement is the DDL change operation involved in the execution of the SQL statement; and to acquire the user information of the user corresponding to the SQL statement. The storage module is used to store the user information and the first DDL change statement in a preset database; The acquisition module is further configured to, upon detecting an acquisition instruction to acquire the preset database snapshot, acquire configuration information of the MySQL database corresponding to the SQL statement; and acquire the first snapshot of the preset database based on the configuration information. The detection module is also used to detect whether a second snapshot is stored in the preset database; the second snapshot is a historical snapshot stored in the preset database. The tracking device for database data definition language (DDL) change operations also includes: The judgment module is used to determine whether there is a difference between the first snapshot and the second snapshot if the preset database stores a second snapshot; The storage module is further configured to store the SQL statements in the first snapshot that differ from the second snapshot and the first snapshot in the preset database if there is a difference between the first snapshot and the second snapshot; the SQL statements that differ from the second snapshot include third DDL change statements.

9. The tracking device for database data definition language (DDL) change operations as described in claim 8, characterized in that, The acquisition module is further configured to acquire the execution log of the SQL statement; and acquire the second DDL change statement corresponding to the SQL statement based on the execution log; The storage module is also used to store the second DDL change statement into the preset database.

10. The tracking device for database data definition language (DDL) change operations as described in claim 8, characterized in that, The tracking device for database data definition language (DDL) change operations also includes: The generation module is used to generate a database health report based on the first snapshot; The storage module is also used to store the database health report into the preset database.

11. The tracking device for database data definition language (DDL) change operations as described in claim 8, characterized in that, The acquisition module is also used to acquire the execution time of the SQL statement and the database name of the MySQL database corresponding to the SQL statement; The storage module is also used to associate and store the user information, the execution time, the database name, and the first DDL change statement in a preset database.

12. A system for tracking database data definition language (DDL) change operations, characterized in that, The database data definition language (DDL) change operation tracking system includes a memory, a processor, and a database data definition language (DDL) change operation tracking program stored in the memory and executable on the processor. When the database data definition language (DDL) change operation tracking program is executed by the processor, it implements the steps of the database data definition language (DDL) change operation tracking method as described in any one of claims 1 to 7.

13. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a tracking program for database data definition language (DDL) change operations, which, when executed by a processor, implements the steps of the tracking method for database data definition language (DDL) change operations as described in any one of claims 1 to 7.

Images