Data processing method and device and electronic equipment

Abstract

The invention discloses a data processing method and device and electronic equipment. The method comprises the following steps: acquiring a data stream output by a logic replication slot output plug-in of a database server side, and persisting the data stream into a log file; incremental data in the log file is analyzed, an analyzed message of the original message corresponding to the incremental data is obtained, and the analyzed message comprises a transaction ID, a timestamp and an sql statement of the original message; the analysis message is written into a data analysis engine, target data are inquired through target information in the analysis message, and the target information is any one of a transaction ID and a timestamp; and executing a data recovery operation on the target data. According to the method and the device, the technical problem that the data is permanently lost and cannot be recovered due to the fact that the data cannot be recovered according to the last snapshot if the data is lost or damaged in a backup period in the related technology is solved.

Description

Technical Field

[0001] The present application relates to the field of data processing, and in particular, to a method, device and electronic device for data processing. Background Art

[0002] With the rapid development of cloud computing, more and more enterprises are choosing to move to the cloud. However, the security of databases in cloud environments is seriously challenged, especially the data integrity issues caused by data loss during transmission or storage. In a cloud computing environment, because users cannot directly control and access the systems and environments of cloud server providers, the risk of data loss must be given priority and handled.

[0003] For application systems using PostgreSQL, the solution in a non-cloud environment is to use full backup and full recovery, which requires reliance on some third-party plug-ins or backup tools. However, in a cloud environment, many tools cannot be used, and most cloud vendors do not support third-party plug-ins, so it is difficult to use solutions in a non-cloud environment in a cloud environment. At present, the solutions provided by most cloud vendors are to regularly back up database data and generate a snapshot, so that when data is damaged or accidentally lost, it can be quickly restored through the snapshot to ensure data integrity. However, there is a problem with the above solution. No matter how short the interval between regular backups is, if data is lost or damaged within a backup cycle, it cannot be restored based on the last snapshot. This part of the data will be permanently lost and cannot be recovered, and the requirement of zero data loss cannot be achieved.

[0004] To address the above-mentioned problems, no effective solution has been proposed yet. Summary of the invention

[0005] The embodiments of the present application provide a data processing method, device and electronic device to at least solve the technical problem in the related art that if data loss or damage occurs within a backup cycle, it is impossible to restore based on the last snapshot, resulting in permanent loss of data that cannot be recovered.

[0006] According to one aspect of an embodiment of the present application, a method for data processing is provided, including: obtaining a data stream output by a logical replication slot output plug-in on a database server side, and persisting the data stream in a log file; parsing incremental data in the log file to obtain a parsed message of the original message corresponding to the incremental data, wherein the parsed message includes a transaction ID, a timestamp, and a SQL statement of the original message; writing the parsed message into a data analysis engine, and querying target data through target information in the parsed message, wherein the target information is any one of a transaction ID and a timestamp; and performing a data recovery operation on the target data.

[0007] Optionally, before obtaining the data stream output by the logical replication slot output plug-in on the database server side, the method also includes: obtaining the configuration file on the database server side; detecting whether the logical replication slot is enabled in the configuration file to obtain a detection result; if the detection result indicates that the logical replication slot is not enabled on the database server side, modifying the parameters in the configuration file to enable the logical replication slot on the database server side; creating a new logical replication slot through a SQL command, and determining the output plug-in of the created logical replication slot, wherein the logical replication slot is used to store changed data in the database.

[0008] Optionally, after persisting the data stream in the log file, the method includes: creating a listening thread for listening to the log file; and reading incremental data in the log file when the listening thread detects that there is updated content in the log file.

[0009] Optionally, parsing the incremental data in the log file includes: parsing the original message corresponding to the incremental data to obtain the transaction ID, timestamp and change node of the original message, wherein the change node is used to indicate the number of change operations under the transaction ID; parsing the change node to determine the SQL statement corresponding to the original message.

[0010] Optionally, after parsing the original message corresponding to the incremental data and obtaining the transaction ID, timestamp and change node of the original message, the method also includes: determining from the original message that the first message has an empty change node, and discarding the first message; determining from the original message a second message whose change node length is 1, and performing a single operation on each second message; determining from the original message a third message whose change node length is greater than 1, generating a transaction block for each third message, and performing batch operations on each third message through the transaction block, wherein the transaction block is used to encapsulate all change operations of each third message.

[0011] Optionally, parsing the change node to determine the SQL statement corresponding to the original message includes: parsing the change node, reading the tablespace, table name and change type of the change node; when the change type is an insert type and an update type, obtaining the changed field name and the changed field value; generating a corresponding SQL statement based on the tablespace, table name, changed field name and the changed field value; when the change type is an update type and a delete type, obtaining a target key-value pair; generating a corresponding SQL statement based on the tablespace, table name and the target key-value pair.

[0012] Optionally, the target data is queried by parsing the target information in the message, including: querying the target information through the API of the data analysis engine to obtain query results; exporting the query results to obtain a query statement for querying the target data; and querying the target data from the database server through the query statement and the transaction ID and timestamp of the target data.

[0013] According to another aspect of an embodiment of the present application, a data processing device is also provided, including: an acquisition module, used to acquire the data stream output by the logical replication slot output plug-in of the database server side, and persist the data stream in a log file; a parsing module, used to parse the incremental data in the log file, and obtain a parsed message of the original message corresponding to the incremental data, wherein the parsed message includes the transaction ID, timestamp and SQL statement of the original message; a query module, used to write the parsed message into a data analysis engine, and query target data through the target information in the parsed message, wherein the target information is any one of the transaction ID and timestamp; a recovery module, used to perform data recovery operations on the target data.

[0014] According to another aspect of the embodiment of the present application, an electronic device is also provided, including: a memory for storing program instructions; a processor, connected to the memory, for executing program instructions to implement the following functions: obtaining a data stream output by a logical replication slot output plug-in on a database server side, and persisting the data stream in a log file; parsing incremental data in the log file to obtain a parsed message of the original message corresponding to the incremental data, wherein the parsed message includes the transaction ID, timestamp and SQL statement of the original message; writing the parsed message into a data analysis engine, and querying target data through target information in the parsed message, wherein the target information is any one of the transaction ID and timestamp; performing data recovery operations on the target data.

[0015] According to another aspect of the embodiments of the present application, a non-volatile storage medium is provided, which includes a stored computer program, wherein a device where the non-volatile storage medium is located executes the above-mentioned data processing method by running the computer program.

[0016] According to another aspect of the embodiments of the present application, a computer program product is provided, including computer instructions, which implement the above-mentioned data processing method when executed by a processor.

[0017] In an embodiment of the present application, the data stream output by the logical replication slot output plug-in on the database server side is obtained, and the data stream is persisted in a log file; the incremental data in the log file is parsed to obtain a parsed message of the original message corresponding to the incremental data, wherein the parsed message includes the transaction ID, timestamp and SQL statement of the original message; the parsed message is written into a data analysis engine, and the target data is queried through the target information in the parsed message, wherein the target information is any one of the transaction ID and the timestamp; a data recovery operation is performed on the target data, so as to achieve the purpose of accurately recovering the data, thereby realizing the technical effect of zero data loss, and further solving the technical problem in the related technology that if data loss or damage occurs within a backup cycle, it cannot be restored according to the last snapshot, resulting in permanent loss of data and irrecoverable data. BRIEF DESCRIPTION OF THE DRAWINGS

[0018] The drawings described herein are used to provide a further understanding of the present application and constitute a part of the present application. The illustrative embodiments of the present application and their descriptions are used to explain the present application and do not constitute an improper limitation on the present application. In the drawings:

[0019] Figure 1 is a hardware structure block diagram of a computer terminal for implementing a data processing method according to an embodiment of the present application;

[0020] Figure 2 is a flow chart of a data processing method according to an embodiment of the present application;

[0021] Figure 3 is a flow chart of parsing an original message according to an embodiment of the present application;

[0022] Figure 4 is a flow chart of parsing a change node according to an embodiment of the present application;

[0023] Figure 5 It is a structural diagram of a data processing device according to an embodiment of the present application. DETAILED DESCRIPTION

[0024] In order to enable those skilled in the art to better understand the solution of the present application, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work should fall within the scope of protection of the present application.

[0025] It should be noted that the terms "first", "second", etc. in the specification and claims of the present application and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the data used in this way can be interchangeable where appropriate, so that the embodiments of the present application described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having" and any of their variations are intended to cover non-exclusive inclusions, for example, a process, method, system, product or device comprising a series of steps or units is not necessarily limited to those steps or units clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0026] First, some nouns or terms that appear in the process of explaining the embodiments of the present application are subject to the following explanations:

[0027] Logical replication slot (LRS): In PostgreSQL, logical replication slot is a feature used to implement logical replication. Logical replication slot allows changes from the source database to be streamed to the target database and enables the target database to be updated according to the changes in the source database. Logical replication slots are only valid in the current session of the source database and will be lost after the session is closed. If you want to save the replication slot after the database is restarted, you need to persist it manually, and each logical replication slot must have a unique name. Logical replication slots must be associated with an output plugin to determine how changes are propagated to the target database. Common output plugins include pgoutput, wal2json, etc.

[0028] xid (Transaction ID, XID): Transaction ID is an identifier used to uniquely identify transactions in PostgreSQL. In MVCC, each transaction is assigned an incremental transaction ID when it is created. The transaction ID is globally unique in the database and is used to associate the modifications made by each transaction to the database.

[0029] timestamp: Timestamp is the time point at which PostgreSQL data is changed, in microseconds.

[0030] watchdog: is a Python library for monitoring file system changes. It allows developers to trigger custom actions when files or directories change. Its main function is to monitor file system changes, such as file creation, modification, deletion, and movement, and respond to these changes in real time.

[0031] ES (Elasticsearch): Elasticsearch is a distributed, highly scalable, and highly real-time search and data analysis engine. Elasticsearch provides near real-time search and analysis for all types of data. Whether it is structured or unstructured text, digital data, or geospatial data, Elasticsearch can efficiently store and index it in a way that supports fast search.

[0032] ELK (Elasticsearch, Logstash, Kibana): is a complete solution, which is the acronym of three software products, Elasticsearch, Logstash and Kibana. These three software are open source software and are usually used together. Logstash is a data collection engine with real-time channel capabilities. Kibana, written in JRuby language, is a web platform based on the Apache open source protocol and written in JavaScript language to provide analysis and visualization for Elasticsearch. It can search and interact with data in the Elasticsearch index and generate tables and charts of various dimensions.

[0033] In PostgreSQL, lrs is a function for implementing logical replication. lrs allows changes from the source database to be streamed to the target database, and enables the target database to be updated according to the changes in the source database. Therefore, backup and recovery of lost or damaged data can be implemented based on lrs, but lrs has a non-persistent characteristic, and the data will be lost when the session is closed, so certain measures need to be taken to make it persistent. An embodiment of the present application provides a method for data processing, which uses lrs and subsequently persists it to a log file, and then reads and parses the log file into executable SQL statements and xid and timestamp information. The above information is stored in ES, and the corresponding SQL statement can be retrieved through the retrieval function of ES, and the data can be recovered by executing the corresponding statement. Thereby solving the problems existing in the related technology. The data processing method can be run on Figure 1 In the computer terminal shown, the computer terminal is explained below.

[0034] The data processing method embodiments provided in the embodiments of the present application can be executed in a mobile terminal, a computer terminal or a similar computing device. Figure 1 FIG. 1 shows a hardware structure block diagram of a computer terminal for implementing a data processing method. Figure 1As shown, the computer terminal 10 may include one or more (102a, 102b, ..., 102n are used to illustrate) processors (the processor may include but is not limited to a processing device such as a microprocessor MCU or a programmable logic device FPGA), a memory 104 for storing data, and a transmission module 106 for communication functions connected via a wired and / or wireless network. In addition, it may also include: a display, a keyboard, a cursor control device, an input / output interface (I / O interface), a universal serial bus (USB) port (which may be included as one of the ports of the I / O interface), a network interface, and a BUS bus. Those skilled in the art can understand that Figure 1 The structure shown is only for illustration and does not limit the structure of the above electronic device. Figure 1 More or fewer components as shown, or with Figure 1 Different configurations shown.

[0035] It should be noted that the one or more processors and / or other data processing circuits described above may generally be referred to herein as "data processing circuits". The data processing circuits may be embodied in whole or in part as software, hardware, firmware, or any other combination thereof. In addition, the data processing circuit may be a single independent processing module, or may be incorporated in whole or in part into any of the other components in the computer terminal 10. As described in the embodiments of the present application, the data processing circuit acts as a processor control (e.g., selection of a variable resistor terminal path connected to an interface).

[0036] The memory 104 can be used to store software programs and modules of application software, such as program instructions / data storage devices corresponding to the data processing method in the embodiment of the present application. The processor executes various functional applications and data processing by running the software programs and modules stored in the memory 104, that is, realizing the above-mentioned data processing method. The memory 104 may include a high-speed random access memory, and may also include a non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some examples, the memory 104 may further include a memory remotely arranged relative to the processor, and these remote memories may be connected to the computer terminal 10 via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0037] The transmission module 106 is used to receive or send data via a network. The specific example of the above network may include a wireless network provided by a communication provider of the computer terminal 10. In one example, the transmission module 106 includes a network adapter (Network Interface Controller, NIC), which can be connected to other network devices through a base station so as to communicate with the Internet. In one example, the transmission module 106 can be a radio frequency (RF) module, which is used to communicate with the Internet wirelessly.

[0038] The display may be, for example, a touch screen liquid crystal display (LCD) that enables a user to interact with a user interface of the computer terminal 10 .

[0039] It should be noted that, in some optional embodiments, the above Figure 1 The computer terminal shown may include hardware elements (including circuits), software elements (including computer code stored on a computer-readable medium), or a combination of hardware elements and software elements. It should be noted that Figure 1 This is merely one example of a particular embodiment and is intended to illustrate the types of components that may be present in the computer terminal described above.

[0040] In the above-mentioned operating environment, an embodiment of the present application provides an embodiment of a method for data processing. It should be noted that the steps shown in the flowchart of the accompanying drawings can be executed in a computer system such as a set of computer executable instructions, and although the logical order is shown in the flowchart, in some cases, the steps shown or described can be executed in an order different from that shown here.

[0041] Figure 2 is a flow chart of a data processing method according to an embodiment of the present application, such as Figure 2 As shown, the method comprises the following steps:

[0042] Step S202, obtaining the data stream output by the logical replication slot output plug-in on the database server side, and persisting the data stream in a log file.

[0043] In the above step S202, in the PostgreSQL database, the logical replication slot is a persistent record for capturing data change streams, which allows data changes to be streamed to another database or external system for processing. In an embodiment of the present application, after PostgreSQL has been configured to support logical replication, by creating a logical replication slot, and enabling and configuring the output plug-in, whenever data changes occur in the database, the output plug-in will capture these changes and convert them into data streams that can be consumed by downstream systems (such as log files, Kafka, etc.). Persistent data streams to log files are usually completed using the pg_recvlogical tool. pg_recvlogical is a tool provided by PostgreSQL for consuming logical replication slot output data, which can receive data from the logical replication slot and write it to a specified log file. When using pg_recvlogical, it is necessary to specify the name of the logical replication slot and the path of the output file. By setting the include-xids parameter and the include-timestamp parameter, the operation timestamp timestamp of the corresponding operation record and the corresponding xid are recorded, and finally the specific stored log file path and name are specified in the command.

[0044] Since the log files are stored on the local disk, they will not be lost due to database server restart or failure. This means that even if the database service is unavailable, the information about data changes can still be restored through the records in the log files, thus providing a basis for data recovery.

[0045] Step S204, parse the incremental data in the log file to obtain a parsed message of the original message corresponding to the incremental data, wherein the parsed message includes a transaction ID, a timestamp and a SQL statement of the original message.

[0046] In the above step S204, incremental data is recorded according to transactions, and each transaction may contain one or more database operations (such as INSERT, UPDATE, DELETE). For each original message in json format, the parsing process will convert it into a message including a transaction ID, a timestamp and a SQL statement. This conversion involves an in-depth analysis of the change node (change node), and the corresponding SQL statement is generated according to the kind (operation type: such as insert, update or delete). During the parsing process, the transaction ID (xid) is used to track and manage transactions in the database to ensure data consistency and integrity. The timestamp records the time point of transaction execution, which is particularly important for replaying transactions in chronological order during data recovery. These two information make it possible to accurately locate and execute the required transactions during the data recovery process, thereby avoiding data loss.

[0047] Step S206, writing the parsed message into the data analysis engine, and querying the target data through the target information in the parsed message, wherein the target information is any one of the transaction ID and the timestamp.

[0048] Step S208: performing a data recovery operation on the target data.

[0049] In the above steps, you can first write the parsed message to the Kafka server, then use Logstash to read the latest data in Kafka and write it to Elasticsearch (ES, the above-mentioned data analysis engine). Specifically, in ES, data is stored in the index, so you need to design a suitable index structure to store the parsed message. Index design should consider factors such as field type (such as string, date, integer), mapping (how to store and index fields), and word segmentation (how to segment text fields) to optimize query performance and data retrieval. Use ES's API or data transmission tools such as Logstash to write the parsed message to a pre-designed index. As part of the ELK stack, Logstash can easily read data from data sources such as Kafka, convert, filter or enhance the format, and then send it to ES for storage. This step ensures the persistence of data, and even if the database or log file system fails, the data information can still be safely stored and accessed.

[0050] Once the parsed message is written to ES, its powerful query capabilities can be used to retrieve the target data. The target information can be either a transaction ID or a timestamp:

[0051] 1. Transaction ID query: The transaction ID (xid) is the unique identifier of a transaction in PostgreSQL, which can be used to accurately query all data changes related to a transaction. Using the ES query API, you can query based on the transaction ID field to obtain detailed information about all parsed messages under the transaction, including SQL statements and timestamps.

[0052] 2. Timestamp range query: Timestamp provides information about the time sequence of data changes. ES supports complex time range queries, and can retrieve all data change records within a specific time period based on the timestamp field. After data loss, you can quickly locate the data changes before the most recent backup point or failure point.

[0053] Users send query requests through ES's API (such as REST API) or visualization tools such as Kibana. ES retrieves relevant documents based on the target information (transaction ID or timestamp) in the request. Based on the query results, users can obtain SQL statements corresponding to the data that is lost or needs to be recovered. These statements can be executed on the database server to recover the data.

[0054] By storing and querying parsed messages in ES, the embodiments of the present application achieve fast retrieval and accurate recovery of data, greatly improving the efficiency and reliability of the data zero loss strategy. ES's efficient query capabilities, combined with the precise information of transaction IDs and timestamps, provide a solid foundation for database recovery operations, ensuring that specific data records can be retrieved even in the event of data loss, avoiding permanent data loss.

[0055] In step S202 of the above data processing method, before obtaining the data stream output by the logical replication slot output plug-in of the database server side, the method also includes: obtaining a configuration file of the database server side; detecting whether the logical replication slot is enabled in the configuration file, and obtaining a detection result; if the detection result indicates that the logical replication slot is not enabled on the database server side, modifying the parameters in the configuration file to enable the logical replication slot on the database server side; creating a new logical replication slot through a SQL command, and determining the output plug-in of the created logical replication slot, wherein the logical replication slot is used to store changed data in the database.

[0056] In an embodiment of the present application, the configuration file of PostgreSQL is generally referred to as postgresql.conf, which contains various configuration parameters required for the operation of the database server. To ensure that the logical replication slot function is available, you first need to access and read this configuration file to check whether the relevant configuration is enabled. In the postgresql.conf file, there are several key parameters that control whether the logical replication slot function is enabled or not, including: 1. wal_level: must be set to logical to enable logical replication; 2. max_replication_slots: controls the maximum number of logical replication slots that can be created; 3. max_wal_senders: controls the maximum number of wal sender processes that can be run, which are used to stream WAL records into the replication slot. You can also specify archive_timeout to constrain the archiving cycle, as shown below:

[0057] wal_level=logical #Open logical replication slot

[0058] max_replication_slots = 10 #The number of slots can be adjusted according to demand

[0059] max_wal_senders = 10 #The number of stream replication processes can be adjusted according to demand

[0060] archive_timeout=1800#Archive every 30 minutes

[0061] Check whether the settings of these parameters meet the requirements of logical replication. If the detection results show that wal_level is not set to logical, or the values ​​of max_replication_slots and max_wal_senders are insufficient, the database configuration needs to be modified. Modify these parameters in the postgresql.conf file, and then restart the database server to apply the new configuration. After the configuration file is modified and the database server is restarted, SQL commands can be used to create logical replication slots. When creating lrs, the output plugin type of lrs determines how the data is processed and sent. Considering the subsequent parsing and persistent storage of output information, wal2json can be selected as the output plugin. Each subsequent database change operation will be written to the defined lrs through the WAL log. The following is an example of defining slot_name as my_slot and using the wal2json output plugin:

[0062] SELECT pg_create_logical_replication_slot('my_slot','wal2json');

[0063] In the above settings, my_slot is the name of the logical replication slot, and wal2json is the name of the output plugin, which determines how the data will be encoded and output. The wal2json output plugin outputs the data in JSON format for subsequent processing. The logical replication slot acts as a persistent "recorder" in PostgreSQL, which can continuously capture all change data in the database (such as INSERT, UPDATE, DELETE operations) and output this data in the form of a stream. The output plugin (such as wal2json) encodes and formats this data so that it can be consumed and parsed by downstream systems (such as log files, Kafka, etc.).

[0064] In step S202 of the above data processing method, after persisting the data stream in the log file, the method includes: creating a listening thread for listening to the log file; when the listening thread detects that there is updated content in the log file, reading the incremental data in the log file. By creating and configuring the listening thread, the update of the log file can be monitored in real time to ensure that any newly generated data stream can be captured and processed in time, which provides real-time and integrity guarantees for the data zero loss method.

[0065] In an embodiment of the present application, a monitoring thread can be created in the database cloud host to monitor changes in log files, parse each incremental log in real time, and push the parsed data to Kafka. The watchdog module of Python is a very powerful library that provides a simple and powerful API to observe changes in the file system. Based on its advantages such as ease of use, real-time notification, high performance, cross-platform and stability, this application uses the watchdog component to create a monitoring thread to monitor changes in log files. At the end of each monitored file, if no new content is written, it will sleep for one second and then continue to monitor. If there is new content, the newly added message data of the line is converted into a json object, the nodes in the json are parsed, the xid and timestamp information are identified, and the information in the change node is parsed into an executable SQL statement, encapsulated into a parsed message, and the message is written to Kafka.

[0066] In step S204 of the above data processing method, the incremental data in the log file is parsed, including: parsing the original message corresponding to the incremental data to obtain the transaction ID, timestamp and change node of the original message, wherein the change node is used to indicate the number of change operations under the transaction ID; parsing the change node to determine the SQL statement corresponding to the original message.

[0067] In the above steps, after parsing the original message corresponding to the incremental data and obtaining the transaction ID, timestamp and change node of the original message, the method also includes: determining from the original message that the change node is empty as the first message, and discarding the first message; determining from the original message a second message whose change node length is 1, and performing a single operation on each second message; determining from the original message a third message whose change node length is greater than 1, generating a transaction block for each third message, and performing batch operations on each third message through the transaction block, wherein the transaction block is used to encapsulate all change operations of each third message.

[0068] In the embodiments of the present application, Figure 3 As shown in the figure, by parsing the original message, the xid (transaction ID) and timestamp (timestamp) in the message can be obtained, which can be used as the basis for subsequent query to determine whether the data is lost and needs to be restored. The specific change operation of the database is recorded in the change node, and further parsing the change node can parse the corresponding SQL statement.

[0069] If the change node is empty, it means that there is no change operation in the first message corresponding to this transaction, and the first message is discarded. If the length of the change node is 1, it means that the second message corresponding to this transaction has only one change information (or change operation), and no additional processing is required, and a single operation is performed. If the length of the change node is greater than 1, it means that there are multiple change information in the third message corresponding to this transaction, and an additional unified transaction processing mechanism is required to perform batch operations. After traversing all changes and determining the operations performed, parse the change node, obtain the DML SQL statement, and obtain the parsed message (i.e. the parsed message mentioned above) based on the transaction ID, timestamp, and SQL statement.

[0070] In the above steps, the change node is parsed to determine the SQL statement corresponding to the original message, including: parsing the change node, reading the table space, table name and change type of the change node; when the change type is an insert type and an update type, obtaining the changed field name and the changed field value; generating the corresponding SQL statement based on the table space, table name, changed field name and changed field value; when the change type is an update type and a delete type, obtaining the target key-value pair; generating the corresponding SQL statement based on the table space, table name and the target key-value pair.

[0071] In the embodiments of the present application, Figure 4 As shown, when parsing the change node, the changed tablespace and table name can be obtained by reading and parsing schema and table, and the specific change type can be obtained by reading kind. If the kind type is insert (i.e. the above-mentioned insert type) and update (i.e. the above-mentioned update type), columnnames and columnvalues ​​are parsed to obtain the changed field names and changed field values, and the corresponding DMLSQL statements are generated based on the tablespace, table name, changed field names and changed field values. If the kind type is update and delete (i.e. the above-mentioned delete type), the keynames and keyvalues ​​in oldkeys are parsed to obtain the target key-value pairs, and the DML SQL statements with the corresponding syntax are generated according to the kind type. Specifically, based on the keynames and keyvalues ​​of oldkeys, a WHERE clause is constructed for the UPDATE or DELETE operation to ensure that the operation only affects specific rows.

[0072] Based on Kafka's high performance, high reliability, distributed processing capabilities, buffering and peak-shaving asynchronous communication features, the parsed message is written into Kafka for use in subsequent processes. ES is selected as the final storage medium due to its powerful full-text search function, real-time data analysis capabilities, log and event data storage, business intelligence and analysis functions, as well as its applicability as a full-text retrieval engine, rich APIs, and extensive application integration such as ELK. The data in Kafka is written into ES for subsequent query and retrieval. It includes two steps: Logstash reads the latest data from Kafka and writes it into ES, and Kibana reads ES data.

[0073] Step 1: Logstash reads the latest data from Kafka and writes it to ES

[0074] Before using this feature, make sure that the two plug-ins, logstash-input-kafka and logstash-output-elasticsearch, have been installed. If the plug-ins have been installed, you need to configure the input and output parts of Logstash. In the input configuration, set the bootstrap_servers, topics, and consumer_group configurations of kafka, ensure that the Kafka cluster is correctly configured and Logstash can connect to it, and set the auto_offset_reset automatic offset reset to latest to ensure that the latest data is read from kafka every time. Set the ES settings in the output configuration, ensure that the ES cluster is correctly configured and Logstash can access it, and set the index index to determine which index of ES the data is written to. You can use the SQL-%{+YYYY.MM.dd} format to create a new index with a SQL string prefix every day according to the date, or you can specify a fixed index to write.

[0075] Step 2: Kibana reads ES data

[0076] Kibana is an important part of the ELK stack. It provides a user-friendly interface to query and visualize data stored in ES. The first thing is to make sure that ES and Kibana are installed and running, and that the correct connection has been established between them. Open a browser and access Kibana's URL address to access Kibana normally. If an index suffix with a time field is created every day in the previous step, on the homepage of Kibana, use a wildcard query to query the index name, such as SQL-*, otherwise just query the specified index accurately. Once the index pattern is created, you can filter database change information within a time range based on timestamp, or accurately find a change information based on xid.

[0077] Based on the above operations, you can use Kibana to query the data in Elasticsearch and display the results in a visual way. Users can choose to copy or export the retrieved information and manually execute the SQL statements in the database to recover the lost or damaged data.

[0078] In step S206 of the above data processing method, the target data is queried by parsing the target information in the message, including: querying through the target information in the API of the data analysis engine to obtain the query result; exporting the query result to obtain a query statement for querying the target data; and querying the target data from the database server through the query statement and the transaction ID and timestamp of the target data.

[0079] In an embodiment of the present application, through the ES API or page, all operation records within the time period can be queried through the timestamp field by filtering the time period range, and a certain operation record can be accurately queried through the xid field. The query results can be copied or exported. If the ES native API exports a json file containing SQL statements, if it is a custom developed page, accurate SQL statements can be exported. After obtaining a specific SQL statement (i.e., a query statement) and the corresponding xid and timestamp, the user can execute the corresponding SQL statement in the database by himself to retrieve the lost data, so that zero data loss can be achieved.

[0080] Figure 5 is a structural diagram of a data processing device according to an embodiment of the present application, such as Figure 5 As shown, the device comprises:

[0081] The acquisition module 50 is used to acquire the data stream output by the logical replication slot output plug-in on the database server side, and persist the data stream in a log file;

[0082] The parsing module 52 is used to parse the incremental data in the log file to obtain a parsed message of the original message corresponding to the incremental data, wherein the parsed message includes the transaction ID, timestamp and SQL statement of the original message;

[0083] A query module 54 is used to write the parsed message into the data analysis engine, and query the target data through the target information in the parsed message, wherein the target information is any one of the transaction ID and the timestamp;

[0084] The recovery module 56 is used to perform a data recovery operation on the target data.

[0085] In the acquisition module in the above-mentioned data processing device, the acquisition module is also used to obtain the configuration file of the database server; detect whether the logical replication slot is enabled in the configuration file to obtain the detection result; when the detection result indicates that the logical replication slot is not enabled on the database server, modify the parameters in the configuration file to enable the logical replication slot on the database server; create a new logical replication slot through a SQL command, and determine the output plug-in of the created logical replication slot, wherein the logical replication slot is used to store the changed data in the database.

[0086] In the acquisition module in the above data processing device, the acquisition module is also used to create a listening thread for listening to the log file; when the listening thread detects that there is updated content in the log file, the incremental data in the log file is read.

[0087] In the parsing module in the above-mentioned data processing device, the parsing module is also used to parse the original message corresponding to the incremental data to obtain the transaction ID, timestamp and change node of the original message, wherein the change node is used to indicate the number of change operations existing under the transaction ID; parse the change node to determine the SQL statement corresponding to the original message.

[0088] In the parsing module in the above-mentioned data processing device, the parsing module is also used to determine from the original message that the first message has an empty change node, and discard the first message; determine from the original message that the second message has a change node length of 1, and perform a single operation on each second message; determine from the original message that the third message has a change node length greater than 1, generate a transaction block for each third message, and perform batch operations on each third message through the transaction block, wherein the transaction block is used to encapsulate all change operations of each third message.

[0089] In the parsing module in the above-mentioned data processing device, the parsing module is also used to parse the change node, read the table space, table name and change type of the change node; when the change type is an insert type and an update type, obtain the changed field name and the changed field value; generate corresponding SQL statements based on the table space, table name, changed field name and changed field value; when the change type is an update type and a delete type, obtain the target key-value pair; generate corresponding SQL statements based on the table space, table name and the target key-value pair.

[0090] In the query module in the above-mentioned data processing device, the query module is also used to query the target information through the API of the data analysis engine to obtain the query results; export the query results to obtain the query statement for querying the target data; query the target data from the database server through the query statement and the transaction ID and timestamp of the target data.

[0091] It should be noted that Figure 5 The data processing device shown is used to perform Figure 2 The data processing method shown, therefore the relevant explanations in the above data processing method are also applicable to the data processing device, and will not be repeated here.

[0092] An embodiment of the present application also provides an electronic device, which includes a memory and a processor, wherein the memory is used to store program instructions; the processor is connected to the memory, and is used to execute program instructions to implement the following functions: obtain a data stream output by a logical replication slot output plug-in on a database server side, and persist the data stream in a log file; parse the incremental data in the log file to obtain a parsed message of the original message corresponding to the incremental data, wherein the parsed message includes the transaction ID, timestamp and SQL statement of the original message; write the parsed message into a data analysis engine, and query the target data through the target information in the parsed message, wherein the target information is any one of the transaction ID and the timestamp; perform data recovery operations on the target data.

[0093] An embodiment of the present application also provides a non-volatile storage medium, which includes a stored computer program, wherein the device where the non-volatile storage medium is located executes the following data processing method by running the computer program: obtaining the data stream output by the logical replication slot output plug-in of the database server side, and persisting the data stream in a log file; parsing the incremental data in the log file to obtain a parsed message of the original message corresponding to the incremental data, wherein the parsed message includes the transaction ID, timestamp and SQL statement of the original message; writing the parsed message into a data analysis engine, and querying the target data through the target information in the parsed message, wherein the target information is any one of the transaction ID and the timestamp; performing data recovery operations on the target data.

[0094] The embodiments of the present application also provide a computer program product, including computer instructions, which, when executed by a processor, implement the steps of the data processing method in each embodiment of the present application.

[0095] The embodiments of the present application also provide a computer program, which, when executed by a processor, implements the steps of the data processing method in each embodiment of the present application.

[0096] The serial numbers of the above-mentioned embodiments of the present application are for description only and do not represent the advantages or disadvantages of the embodiments.

[0097] In the above embodiments of the present application, the description of each embodiment has its own emphasis. For parts that are not described in detail in a certain embodiment, please refer to the relevant description of other embodiments.

[0098] In the several embodiments provided in this application, it should be understood that the disclosed technical content can be implemented in other ways. Among them, the device embodiments described above are only schematic. For example, the division of the units can be a logical function division. There may be other division methods in actual implementation. For example, multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be through some interfaces, indirect coupling or communication connection of units or modules, which can be electrical or other forms.

[0099] The units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed on multiple units. Some or all of the units may be selected according to actual needs to achieve the purpose of the present embodiment.

[0100] In addition, each functional unit in each embodiment of the present application may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit. The above-mentioned integrated unit may be implemented in the form of hardware or in the form of software functional units.

[0101] If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present application, in essence, or the part that contributes to the prior art or all or part of the technical solution can be embodied in the form of a software product, and the computer software product is stored in a storage medium, including a number of instructions to enable a computer device (which can be a personal computer, a server or a network device, etc.) to perform all or part of the steps of the method described in each embodiment of the present application. The aforementioned storage medium includes: U disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), mobile hard disk, disk or optical disk and other media that can store program codes.

[0102] The above is only a preferred implementation of the present application. It should be pointed out that for ordinary technicians in this technical field, several improvements and modifications can be made without departing from the principles of the present application. These improvements and modifications should also be regarded as the scope of protection of the present application.

Claims

1. A method for data processing, characterized in that: include: Obtain the data stream output by the logical replication slot output plug-in on the database server side, and persist the data stream in a log file; Parsing the incremental data in the log file to obtain a parsed message of the original message corresponding to the incremental data, wherein the parsed message includes a transaction ID, a timestamp, and a SQL statement of the original message; Writing the parsed message into a data analysis engine, and querying target data through target information in the parsed message, wherein the target information is any one of the transaction ID and the timestamp; A data recovery operation is performed on the target data.

2. The method according to claim 1, characterized in that Before obtaining the data stream output by the logical replication slot output plug-in on the database server side, the method further includes: Obtaining a configuration file of the database server; Detecting whether the logical replication slot is enabled in the configuration file, and obtaining a detection result; When the detection result indicates that the database server has not enabled the logical replication slot, modify the parameters in the configuration file to enable the logical replication slot of the database server; A new logical replication slot is created through a SQL command, and an output plug-in of the created logical replication slot is determined, wherein the logical replication slot is used to store change data in a database.

3. The method according to claim 1, characterized in that After persisting the data stream in a log file, the method includes: Create a monitoring thread for monitoring the log file; When the listening thread detects that there is updated content in the log file, the incremental data in the log file is read.

4. The method according to claim 1, characterized in that Parsing the incremental data in the log file includes: Parsing the original message corresponding to the incremental data to obtain a transaction ID, a timestamp, and a change node of the original message, wherein the change node is used to indicate the number of change operations existing under the transaction ID; Parse the changed node to determine the SQL statement corresponding to the original message.

5. The method according to claim 4, characterized in that Parsing the original message corresponding to the incremental data, After obtaining the transaction ID, timestamp and change node of the original message, the method further includes: Determine from the original message that the change node is empty as the first message, and discard the first message; Determine from the original message a second message whose length of the changed node is 1, and perform a single operation on each second message; Determine a third message whose length of the change node is greater than 1 from the original message, generate a transaction block for each third message, and perform batch operations on each third message through the transaction block, wherein the transaction block is used to encapsulate all change operations of each third message.

6. The method according to claim 4, characterized in that Parsing the changed node to determine the SQL statement corresponding to the original message includes: Parsing the change node, reading the table space, table name and change type of the change node; When the change type is an insert type or an update type, obtaining the changed field name and the changed field value; Generate a corresponding SQL statement according to the table space, the table name, the changed field name and the changed field value; When the change type is an update type or a delete type, obtaining a target key-value pair; Generate a corresponding SQL statement according to the table space, the table name and the target key-value pair.

7. The method according to claim 1, characterized in that Querying target data through the target information in the parsed message includes: Performing a query through the target information in the API of the data analysis engine to obtain a query result; Exporting the query result to obtain a query statement for querying the target data; The target data is queried from a database server using the query statement and the transaction ID and timestamp of the target data.

8. A data processing device, characterized in that: include: An acquisition module is used to acquire the data stream output by the logical replication slot output plug-in on the database server side, and persist the data stream in a log file; A parsing module, used to parse the incremental data in the log file to obtain a parsed message of the original message corresponding to the incremental data, wherein the parsed message includes a transaction ID, a timestamp and a SQL statement of the original message; A query module, used to write the parsed message into a data analysis engine, and query target data through target information in the parsed message, wherein the target information is any one of the transaction ID and the timestamp; A recovery module is used to perform a data recovery operation on the target data.

9. An electronic device, characterized in that: include: A memory for storing program instructions; A processor, connected to the memory, is used to execute program instructions that implement the following functions: obtain a data stream output by a logical replication slot output plug-in on a database server side, and persist the data stream in a log file; parse the incremental data in the log file to obtain a parsed message of an original message corresponding to the incremental data, wherein the parsed message includes a transaction ID, a timestamp, and a SQL statement of the original message; write the parsed message into a data analysis engine, and query target data through target information in the parsed message, wherein the target information is any one of the transaction ID and the timestamp; perform data recovery operations on the target data.

10. A non-volatile storage medium, characterized in that: The non-volatile storage medium includes a stored computer program, wherein the device where the non-volatile storage medium is located executes the data processing method according to any one of claims 1 to 7 by running the computer program.

11. A computer program product comprising computer instructions, characterized in that: When the computer instructions are executed by a processor, the data processing method described in any one of claims 1 to 7 is implemented.

Images