A method and device for analyzing ring cascade synchronization DDL based on database log

Abstract

The present application relates to the technical field of computer, particularly relates to a method and device for analyzing ring cascade synchronization DDL based on database log, in the present application, a target synchronization service writes database node information to be transmitted into an auxiliary table by using the auxiliary table, and then synchronizes a DDL operation, a source synchronization service captures the auxiliary table, analyzes the database node information to be transmitted from the auxiliary table, associates the database node information with a subsequent captured DDL operation, and then delivers the DDL operation to a next target synchronization service, the next target synchronization service analyzes database node information in the received DDL operation information, when the database node information includes a current database node identifier, filters the corresponding DDL operation, and avoids that the DDL operation is repeatedly executed in a certain database node, and the present application is suitable for all databases and is more universal.

Description

Technical Field

[0001] This invention relates to the field of computer technology, and in particular to a method and apparatus for parsing ring-cascaded synchronous DDL based on database logs. Background Technology

[0002] In a real-time database synchronization system based on a log parsing architecture, the source synchronization service is responsible for capturing the operation logs of the source database, parsing the operation logs, reconstructing the corresponding database operations, and then sending them to the target synchronization service. The target synchronization service is responsible for executing the received database operations in the target database, thereby completing the data synchronization process.

[0003] Ring-based cascading synchronization built on log parsing is a common synchronization architecture. For example, in bidirectional synchronization between two nodes, due to the independence of DDL (Data Definition Language) operations, most database DDL operations are implicitly committed and cannot be placed in the same transaction as auxiliary table insertions. This results in DDL operations being decoupled from source information into two independent logs. The synchronization tool cannot associate a DDL with its corresponding source identifier, and therefore cannot distinguish whether the DDL was initiated locally or synchronized from the other party. Thus, this method cannot achieve accurate filtering, and without additional processing, it is easy to form a circular synchronization.

[0004] In some database systems (such as Oracle and DM), the target synchronization service can embed comments in DDL statements when executing DDL operations. The comments will append information about the database nodes being synchronized by the current DDL operation. The source synchronization service can capture the comments embedded in the DDL statements by capturing the DDL operation logs in the log stream. By parsing the database node information in the comments, the source of the operation can be identified and the DDL operation can be filtered out to prevent loops.

[0005] However, some database systems (such as MySQL) remove comments from DDL statements when logging DDL operations, making it impossible to identify the source of DDL operations using the methods described above.

[0006] Therefore, overcoming the shortcomings of the existing technology is an urgent problem to be solved in this technical field. Summary of the Invention

[0007] The technical problem to be solved by this invention is how to solve the problem that some database systems in the prior art cannot effectively filter DDL operations, resulting in the repeated execution of DDL operations.

[0008] The present invention adopts the following technical solution: Firstly, a method for parsing ring-cascaded synchronous DDL based on database logs is provided, including: Before performing the synchronization DDL operation, the target-side synchronization service inserts DDL operation information containing database node information into the auxiliary table. The source synchronization service sequentially captures DDL operation information from the auxiliary table and DDL operations generated by the database, associates the captured DDL operation information with the corresponding DDL operations, and delivers the associated DDL operation information and DDL operations to the next-level target synchronization service. The next-level target synchronization service parses the database node information in the received DDL operation information. When the database node information includes the current database node identifier, the corresponding DDL operation is filtered out.

[0009] Preferably, before synchronizing the DDL operation, the target-side synchronization service inserts DDL operation information containing database node information into the auxiliary table, specifically including: The target synchronization service receives the associated DDL operation information and DDL operations sent from the previous source synchronization service, and extracts the database node information attached to the DDL operation information. When the extracted database node information contains the current database node identifier, the extracted DDL operations are filtered out. When the extracted database node information does not contain the current database node identifier, the current database node identifier is appended to the extracted database node information to obtain the updated database node information, and the updated database node information is inserted into the auxiliary table of the current database.

[0010] Preferably, the source-end synchronization service sequentially captures DDL operation information from the auxiliary table and DDL operations generated by the database, associates the captured DDL operation information with the corresponding DDL operations, and delivers the associated DDL operation information and DDL operations to the next-level target-end synchronization service, specifically including: The source synchronization service parses the database logs. When it captures an insert operation in the auxiliary table, it extracts DDL operation information from the inserted data in the auxiliary table and stores the extracted DDL operation information in a memory linked list. When a DDL operation is captured, the starting LSN of the DDL operation log is extracted, and the log of the DDL operation is parsed according to the starting LSN of the DDL operation log to obtain DDL operation information including object name and DDL operation type. The DDL operation information with the same object name and DDL operation type is matched in the memory linked list to obtain the matching result, and the associated DDL operation information and DDL operation are delivered to the next level target synchronization service according to the matching result.

[0011] Preferably, the step of matching DDL operation information with the same object name and DDL operation type in the memory linked list to obtain a matching result, and then delivering the associated DDL operation information and DDL operation to the next-level target synchronization service according to the matching result, specifically includes: When there is no DDL operation information with the same object name and DDL operation type in the memory linked list, the current database node identifier is appended to the current DDL operation and delivered to the next level target synchronization service. When there are DDL operation information with the same object name and DDL operation type in the memory linked list, the starting LSN of the current DDL operation is written to the matched DDL operation information in the memory linked list. Extract the database node information recorded in the matched DDL operation information in the memory linked list, integrate the database node information into the current DDL operation, and deliver the integrated DDL operation and database node information to the next level target synchronization service.

[0012] Preferably, the method further includes: The source synchronization service saves the DDL operation information in the memory linked list and the starting LSN of the log used for source parsing to the checkpoint file at preset intervals. When the source synchronization service restarts, it loads the DDL operation information saved in the checkpoint file and restores it to the memory linked list, and sets the starting LSN of the log parsed by the source to the starting LSN of the log saved in the checkpoint file.

[0013] Preferably, the source-end synchronization service saves the DDL operation information in the memory linked list and the log starting LSN used for source-end parsing to the checkpoint file every preset time interval, specifically including: The source synchronization service obtains the fault recovery point LSN from the target synchronization service as the checkpoint LSN; Traverse the memory linked list and save the DDL operation information for which no starting LSN has been set, and the DDL operation information for which a starting LSN has been set but which is greater than the checkpoint LSN, to the checkpoint file. Update the current checkpoint LSN to the latest fault recovery point LSN, traverse the memory linked list, and release DDL operation information whose starting LSN is less than the current checkpoint LSN.

[0014] Preferably, the method further includes: The target-side synchronization service performs auxiliary table insertion operations and DDL operations through the same database connection; After inserting DDL operation information into the auxiliary table, keep the transaction in an uncommitted state; By utilizing the implicit commit mechanism triggered by subsequent DDL operations, the auxiliary table insertion operation and the DDL operation are committed simultaneously as atomic transactions.

[0015] Preferably, the DDL operation information includes the database node information and the current database node identifier that the corresponding DDL operation sent by the previous source synchronization service passes through in the synchronization link. The DDL operation information also includes the database object information targeted by the corresponding DDL operation and the DDL operation type; wherein, when the DDL operation type is a column modification operation, the DDL operation type includes the column names involved.

[0016] Secondly, an apparatus for parsing ring-cascaded synchronous DDL based on database logs is provided. The apparatus for parsing ring-cascaded synchronous DDL based on database logs includes: a processor and a memory for storing processor-executable instructions. The processor is configured to execute the method of parsing ring-cascaded synchronous DDL based on database logs.

[0017] Thirdly, a non-volatile computer storage medium is provided, the computer storage medium storing computer-executable instructions, which are executed by one or more processors to perform the method of ring-cascaded synchronous DDL based on database log parsing described in the first aspect.

[0018] Fourthly, a chip is provided, comprising: a processor and an interface for calling and running a computer program stored in memory from memory, executing the method of parsing ring-cascaded synchronous DDL based on database logs as described in the first aspect.

[0019] Fifthly, a computer program product containing instructions is provided that, when executed on a computer or processor, causes the computer or processor to perform the method of parsing ring-cascaded synchronous DDL based on database log parsing as described in the first to fourth aspects and any one of them.

[0020] Compared with the prior art, the beneficial effects of the present invention are as follows: In this invention, the target synchronization service uses an auxiliary table to write the database node information to be transmitted into the auxiliary table before synchronizing DDL operations. After capturing the auxiliary table, the source synchronization service parses the database node information to be transmitted from the auxiliary table and associates it with the subsequently captured DDL operations before delivering it to the next-level target synchronization service. The next-level target synchronization service parses the database node information in the received DDL operation information. When the database node information includes the current database node identifier, the corresponding DDL operation is filtered out, avoiding the DDL operation from being executed repeatedly in a certain database node. This invention is suitable for all databases and is more universal. Attached Figure Description

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

[0022] Figure 1 This is a flowchart illustrating a method for parsing ring-cascaded synchronous DDL based on database logs, provided in an embodiment of the present invention. Figure 2 This is a schematic diagram of an auxiliary table insertion process provided in an embodiment of the present invention; Figure 3 This is a schematic diagram of the operation flow of a source-end synchronization service provided by an embodiment of the present invention; Figure 4 This is another flowchart illustrating a method for parsing ring-cascaded synchronous DDL based on database logs provided in an embodiment of the present invention; Figure 5 This is a schematic diagram of a process for saving checkpoint files provided in an embodiment of the present invention; Figure 6 This is a schematic diagram of a database cascading structure provided in an embodiment of the present invention; Figure 7 This is a schematic diagram of the structure of a device based on a ring-cascaded synchronous DDL parsing database log provided in an embodiment of the present invention. Detailed Implementation

[0023] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the invention.

[0024] Unless the context otherwise requires, throughout the specification and claims, the term "comprising" is interpreted as openly inclusive, meaning "including, but not limited to." In the description of the specification, terms such as "one embodiment," "some embodiments," "exemplary embodiment," "example," "specific example," or "some examples" are intended to indicate that a particular feature, structure, material, or characteristic associated with that embodiment or example is included in at least one embodiment or example of this disclosure. The illustrative representations of the above terms do not necessarily refer to the same embodiment or example. Furthermore, the specific features, structures, materials, or characteristics mentioned may be included in any suitable manner in any one or more embodiments or examples; that is, although they may be incorporated into embodiments or examples using the above terms for reasons such as order and position, it does not limit them to be incorporated in combination by a single embodiment or example.

[0025] In the description of this invention, the terms "first" and "second" are used for descriptive purposes only and should not be construed as indicating or implying relative importance or implicitly specifying the number of indicated technical features. Thus, a feature defined with "first" or "second" may explicitly or implicitly include one or more of that feature. In the description of embodiments of this disclosure, unless otherwise stated, "a plurality of" means two or more. Furthermore, for example, the description may use the prefix "A" or "B" to describe the same type of nouns as two independent entities. In this case, the corresponding features defined with "A" and "B" are used only to distinguish between similar entities and should not be construed as indicating or implying relative importance or implicitly specifying the number of indicated technical features.

[0026] In describing some embodiments, the terms "coupled," "coupled," and "connected," and their derivative expressions, may be used. For example, the term "connected" may be used in describing some embodiments to indicate that two or more components have direct physical or electrical contact with each other. Similarly, the term "coupled" may be used in describing some embodiments to indicate that two or more components have direct physical or electrical contact. However, the terms "connected" or "coupled" may also refer to two or more components that do not have direct contact with each other but still cooperate or interact with each other, such as "optical coupling," "wireless connection," etc. The embodiments disclosed herein are not necessarily limited to the scope of this invention.

[0027] Furthermore, the technical features involved in the various embodiments of the present invention described below can be combined with each other as long as they do not conflict with each other.

[0028] Example 1: To address the problems of existing technologies, this embodiment proposes a method for ring-cascaded synchronous DDL based on database log parsing. In one embodiment, such as... Figure 1 As shown, the method for parsing ring-cascaded synchronous DDL based on database logs includes: Step 101: Before synchronizing the DDL operations, the target-side synchronization service inserts DDL operation information containing database node information into the auxiliary table.

[0029] In one embodiment, each database node is equipped with a source synchronization service and a target synchronization service, which share database node information. The source synchronization service is used to send data synchronization operations to the next-level database node, and the target synchronization service is used to receive data synchronization operations sent by the previous-level database node.

[0030] by Figure 6 For example, consider three existing database nodes, A, B, and C. A cascading synchronization link is established on these nodes, synchronizing from A to B, B to C, and C to A. On A, a source synchronization service A1 and a target synchronization service A2 are deployed. A1 captures and parses logs before sending them to the next-level node B; A2 receives data synchronization operations from the previous-level node C. Source synchronization service A1 and target synchronization service A2 share the database node information A. Similarly, source synchronization service B1 and target synchronization service B2 are deployed on node B; and source synchronization service C1 and target synchronization service C2 are deployed on node C.

[0031] The target-side synchronization service is responsible for receiving and executing DDL operations from the source (i.e., the parent database node). DDL operations are Data Definition Language operations; for example, creating tables and modifying table structures are DDL operations. The auxiliary table is a dedicated table created within the target-side service to store metadata related to DDL operations, providing support for subsequent synchronization and association operations. Database node information includes database identifiers and topology information along the path of the corresponding DDL operation, used to determine the source, path, and target of the DDL operation.

[0032] Step 102: The source synchronization service sequentially captures the DDL operation information in the auxiliary table and the DDL operations generated by the database, associates the captured DDL operation information with the corresponding DDL operations, and delivers the associated DDL operation information and DDL operations to the next-level target synchronization service.

[0033] The source-end synchronization service monitors DDL operations in the database and pushes these operations to the target-end synchronization service. Capture is achieved by listening to database logs or auxiliary tables to identify DDL operations. By establishing a correspondence between DDL operation information and actual DDL operations, it ensures that subsequent synchronization or filtering of corresponding DDL operations can be performed by comparing the database node information in the DDL operation information with the current database node identifier.

[0034] Step 103: The next-level target synchronization service parses the database node information in the received DDL operation information. When the database node information includes the current database node identifier, the corresponding DDL operation is filtered out.

[0035] For ease of explanation, the target synchronization service in step 101 and the source synchronization service in step 102 are for the same database node (the current database node), and the next-level target synchronization service in step 103 refers to the target synchronization service deployed in the next database node of the current database node.

[0036] In one embodiment, the database node identifier (such as A, B, and C mentioned above) is a unique identifier for each database node, used to identify itself. The database node identifier can be an integer or a string; regardless of the data type, it must be unique within the synchronization link. In one embodiment, when a DDL operation is found to have originated on the current node (i.e., the current database node identifier is matched in the parsed database node information), the DDL operation is not executed again, thus avoiding the situation where the same DDL operation is repeatedly synchronized on a single database node.

[0037] In this embodiment, the target synchronization service writes the database node information to be transmitted into an auxiliary table before synchronizing DDL operations. After capturing the auxiliary table, the source synchronization service parses the database node information to be transmitted from the auxiliary table and associates it with the subsequently captured DDL operations before delivering it to the next-level target synchronization service. The next-level target synchronization service parses the database node information in the received DDL operation information. When the database node information includes the current database node identifier, the corresponding DDL operation is filtered out, avoiding the DDL operation from being executed repeatedly in a certain database node. The solution proposed in this embodiment is suitable for all databases and is more universal.

[0038] The specific steps of the method for parsing ring-cascaded synchronous DDL based on database logs will be described below.

[0039] In one embodiment, before executing the method for ring-cascaded synchronization DDL based on database log parsing, a source synchronization service and a target synchronization service are simultaneously deployed on the database service environment. The source synchronization service is responsible for parsing the current database log operations and delivering them to the next-level target synchronization service. The target synchronization service is responsible for receiving and executing the operations delivered by the previous-level source synchronization service. The source synchronization service and the target synchronization service in the current environment share the current database node.

[0040] In one embodiment, such as Figure 2 As shown, before performing the synchronization DDL operation, the target-side synchronization service inserts DDL operation information containing database node information into the auxiliary table, specifically including: Step 1011: The target synchronization service receives the associated DDL operation information and DDL operation from the source synchronization service at the previous level, and extracts the database node information attached to the DDL operation information.

[0041] In one embodiment, the DDL operation information includes database node information and the current database node identifier that the corresponding DDL operation sent by the upper-level source synchronization service passes through in the synchronization link; the DDL operation information also includes database object information and DDL operation type for the corresponding DDL operation; wherein, when the DDL operation type is a column modification operation, the DDL operation type includes the column names involved.

[0042] Specifically, the target synchronization service acquires and parses DDL operations and associated DDL operation information from the previous-level source synchronization service. DDL operations are database structure modification statements to be executed; DDL operation information is an extended information carrier attached to the DDL operations, the core of which is database node information (i.e., the set of paths of historical database nodes that have been synchronized with this DDL operation).

[0043] In one embodiment, the target-side synchronization service first extracts the executable DDL statement body (corresponding to the DDL operation), and then parses the path set of historical database nodes in the DDL operation information to reconstruct the complete transmission trajectory of the corresponding DDL operation. Step 1011 is mainly to enable the system to know the specific DDL operation content to be executed and the historical propagation path of the DDL operation in the distributed environment.

[0044] Step 1012: When the extracted database node information contains the current database node identifier, filter out the extracted DDL operations.

[0045] In one embodiment, based on the parsing result of step 1011, loop prevention determination is performed in a distributed environment. It is verified whether the current database node identifier already exists in the historical propagation path. If the current database node identifier already exists in the historical propagation path, the corresponding DDL operation is determined to be a circular topology backhaul (i.e., the operation arrives at this database node again after being passed around in a loop). The corresponding DDL operation is filtered out to prevent circular synchronization. If the current database node identifier does not exist in the historical propagation path, the DDL operation is considered to be a valid operation that arrives at this database node for the first time, and subsequent step 1013 is executed.

[0046] Step 1013: When the extracted database node information does not contain the current database node identifier, append the current database node identifier to the extracted database node information to obtain the updated database node information, and insert the updated database node information into the auxiliary table of the current database.

[0047] Specifically, when a corresponding DDL operation is determined to be a valid operation that arrives at this database node for the first time, the current database node identifier can be appended to the database node information extracted in step 1011 to update the database node information. Then, the updated database node information is inserted into the auxiliary table of the current database. After inserting the updated database node information into the auxiliary table of the current database, the target synchronization service completes the synchronization operation for the corresponding DDL operation.

[0048] In one embodiment, the DDL operation information to be executed is inserted into the auxiliary table so that the source synchronization service can parse the DDL operation information in the INSERT operation in the auxiliary table and associate it with the subsequently captured DDL operation (the association method is described in detail below), thereby passing the database node information that the DDL operation passes through in the synchronization link to the next database node.

[0049] The target-side synchronization service needs to execute two transactions: an auxiliary table insertion and a DDL operation synchronization. To enable the source-side synchronization service to quickly associate the two transactions, in one embodiment, the method for ring-cascaded synchronization DDL based on database log parsing further includes: the target-side synchronization service executing the auxiliary table insertion operation and the DDL operation through the same database connection; after inserting the DDL operation information into the auxiliary table, maintaining the transaction in an uncommitted state; and using the implicit commit mechanism triggered by subsequent DDL operations, committing the auxiliary table insertion operation and the DDL operation as atomic transactions simultaneously.

[0050] In this scenario, the target-side synchronization service can use the same database connection when operating on the auxiliary table and performing synchronization DDL operations. After inserting DDL operation information into the auxiliary table, the transaction should not be committed. Instead, the automatic commit feature of subsequent DDL operations can be used to assist in committing the transaction inserted into the auxiliary table. This optimization can reduce the gap between the auxiliary table and its corresponding DDL operation log in the database log, which is beneficial for the source-side synchronization service to quickly associate the two transactions.

[0051] In one embodiment, such as Figure 3 As shown, the source-end synchronization service sequentially captures DDL operation information from the auxiliary table and DDL operations generated by the database, associates the captured DDL operation information with the corresponding DDL operations, and delivers the associated DDL operation information and DDL operations to the next-level target-end synchronization service. Specifically, this includes: Step 1021: The source synchronization service parses the database logs. When an insert operation in the auxiliary table is captured, the DDL operation information is extracted from the inserted data in the auxiliary table and stored in the memory linked list.

[0052] The parsing tool can identify the insertion operation on the auxiliary table. In order to enable fast subsequent queries, DDL operation information is extracted based on the insertion operation of the auxiliary table and temporarily stored in a memory linked list.

[0053] In one embodiment, the target synchronization service executes two transactions when synchronizing DDL operations: one is the transaction for inserting DDL operation information into the auxiliary table, and the other is the transaction for the DDL operation itself. If the target synchronization service restarts between these two transactions, the transaction for inserting DDL operation information into the auxiliary table will be redone, resulting in duplicate DDL operation information in the auxiliary table. Therefore, when the source synchronization service captures the insert operation in the auxiliary table, it needs to perform deduplication processing on the DDL operation information in this case.

[0054] Step 1022: When a DDL operation is captured, extract the starting LSN of the DDL operation log, and parse the log of the DDL operation according to the starting LSN of the DDL operation log to obtain DDL operation information including object name and DDL operation type.

[0055] When an actual DDL operation is captured, the source synchronization service extracts the starting LSN (Log Sequence Number) of that DDL operation from the log and further parses the operation log based on this starting LSN to obtain detailed information about the DDL operation. The LSN is a unique identifier in the log, accurately locating the start position of the DDL operation. By analyzing the log content, the names of the objects involved in the operation and the operation type are determined.

[0056] Step 1023: Match DDL operation information with the same object name and DDL operation type in the memory linked list to obtain a matching result, and deliver the associated DDL operation information and DDL operation to the next level target synchronization service according to the matching result.

[0057] Specifically, step 1023 includes: when no DDL operation information with the same object name and DDL operation type exists in the memory linked list, the current database node identifier is appended to the current DDL operation and delivered to the next-level target synchronization service. If the captured DDL operation does not match the corresponding DDL operation information in the memory linked list, it means that the DDL operation is not executed by the target synchronization service, and this DDL operation is being executed for the first time in the synchronization link, so it is directly delivered to the next level.

[0058] In one embodiment, when DDL operation information with the same object name and DDL operation type exists in the memory linked list, the starting LSN of the current DDL operation's log is written to the matched DDL operation information in the memory linked list. Database node information recorded in the matched DDL operation information in the memory linked list is extracted, integrated into the current DDL operation, and the integrated DDL operation and database node information are delivered to the next-level target synchronization service. In this way, the database node information traversed by the DDL operation in the link can be inserted into an auxiliary table by the target synchronization service, captured by the source synchronization service, associated with subsequent actual DDL operations, and then delivered to the next-level target synchronization service, thus realizing the transmission of database node information traversed by the DDL operation.

[0059] To ensure that the DDL operation information and DDL operations of the auxiliary tables can still be correctly associated after the synchronization failure is recovered, in one embodiment, when the source synchronization service saves the synchronization checkpoint (the log position LSN value of the failure recovery), it also needs to save the DDL operation information of the auxiliary tables that are not associated in the source synchronization service system, so that the DDL operations in the log can continue to be associated after the failure is recovered.

[0060] In one embodiment, such as Figure 4 As shown, the method for parsing ring-cascaded synchronous DDL based on database logs further includes: Step 201: The source synchronization service saves the DDL operation information in the memory linked list and the log starting LSN used for source parsing to the checkpoint file at preset intervals.

[0061] The log start LSN refers to the current log position being processed. The checkpoint file is a persistent file used to periodically save the current state so that previous progress can be quickly restored upon service restart. The source synchronization service continuously listens for and parses the database logs during operation, recording DDL operations to an in-memory linked list when detected. To prevent data loss due to service crashes, the system writes the DDL operation information from the in-memory linked list and the current log start LSN to a checkpoint file at preset time intervals (e.g., every 5 minutes). This ensures that even if the service is unexpectedly interrupted, the state of DDL operations and the log read position can be restored through the checkpoint file. It also avoids duplicate processing or omission of DDL operations, improving the system's fault tolerance and consistency.

[0062] In one embodiment, such as Figure 5 As shown, the source-side synchronization service saves the DDL operation information in the memory linked list and the log starting LSN used for source-side parsing to the checkpoint file every preset time interval, specifically including: Step 2011: The source synchronization service obtains the fault recovery point LSN from the target synchronization service as the checkpoint LSN.

[0063] The fault recovery point (LSN) is a log position identifier maintained by the target synchronization service, indicating that the target has successfully processed and persisted the log content to a certain LSN position. The checkpoint LSN is a temporary variable used to mark the log position on which the current checkpoint is based.

[0064] The source synchronization service periodically and proactively sends requests to the target synchronization service, inquiring about its current "Fault Recovery Point LSN". The target returns this LSN value, indicating that it has completed the synchronization of data changes in this part of the log. The source synchronization service uses this LSN value as the reference position for this checkpoint, denoted as the "Checkpoint LSN". This ensures that only DDL operations that have not yet been confirmed as completed by the target are retained; it avoids repeatedly saving or transmitting data that has already been processed by the target, improving system efficiency; and in the event of a fault restart, the source can continue reading logs from this LSN position, ensuring synchronization continuity.

[0065] Step 2012: Traverse the memory linked list and save the DDL operation information for which no starting LSN has been set, and the DDL operation information for which a starting LSN has been set but which is greater than the checkpoint LSN, to the checkpoint file.

[0066] The starting LSN of a DDL operation indicates the log position corresponding to that DDL operation. A DDL operation without a set starting LSN indicates that the DDL operation has not yet been bound to a specific log position, and may be a recently captured operation that has not yet been fully parsed. A DDL operation with a set starting LSN, but where this LSN is greater than the checkpoint LSN, indicates that the DDL operation occurred after the target end has not yet confirmed its processing, and should still be retained for subsequent synchronization.

[0067] Traverse every DDL operation record in the entire memory linked list; for each DDL operation record, determine whether it meets one of the two conditions mentioned above; if it does, serialize it and write it to the checkpoint file; records that do not meet the conditions (i.e., confirmed operations with LSN ≤ checkpoint LSN) are not written to the checkpoint file. Only DDL operations that have not yet been confirmed and processed by the target end are retained, reducing the size of the checkpoint file, improving performance, and preventing duplicate synchronization of completed operations.

[0068] Step 2013: Update the current checkpoint LSN to the latest fault recovery point LSN, traverse the memory linked list, and release DDL operation information where the starting LSN of the DDL operation is less than the current checkpoint LSN.

[0069] Update the checkpoint locations maintained locally on the source end to the latest fault recovery point LSN provided by the target end. Also, remove DDL operations that have been confirmed to have been processed by the target end from the memory linked list.

[0070] Update the "Current Checkpoint LSN" maintained locally on the source end to the latest acquired fault recovery point LSN; traverse the memory linked list again; for each DDL operation: if its starting LSN < current checkpoint LSN, it means that it has been confirmed and processed by the target end; delete the record and release memory resources; the remaining records are DDL operations that need to be synchronized.

[0071] In one embodiment, when the target synchronization service synchronizes DDL, it first inserts DDL operation information into the auxiliary table and then synchronizes the DDL operation. These two steps are independent transactions. The checkpoint LSN has a certain probability of splitting these two transactions in the log stream, causing the source synchronization service to be unable to re-associate the DDL operation information in the auxiliary table with its corresponding DDL operation after restarting. Therefore, it is necessary to periodically save the DDL operation information in the memory linked list to the checkpoint file by the checkpoint thread, and restore the DDL operation information back to the memory linked list through the checkpoint file upon restart. This ensures that subsequently captured DDL operations can be correctly associated.

[0072] Step 202: After the source synchronization service restarts, load the DDL operation information saved in the checkpoint file and restore it to the memory linked list, and set the log start LSN parsed by the source to the log start LSN saved in the checkpoint file.

[0073] Here, "restart of source-side synchronization service" refers to the situation where the source-side synchronization service restarts after being stopped due to maintenance, upgrades, downtime, or other reasons. The process involves loading the checkpoint file, reading the previously saved checkpoint file, and extracting the DDL operation information and LSNs. The extracted DDL operations are then reinserted into the memory linked list for subsequent processing and synchronization. The log parsing position is reset to the previously saved LSN, allowing the source-side synchronization service to resume reading logs from where it left off, rather than starting from the beginning.

[0074] The specific implementation process includes: when the source synchronization service restarts, it checks whether a new checkpoint file exists. If it does, it opens and parses the checkpoint file, extracts all DDL operation information, and inserts these DDL operation information entries into the memory linked list one by one to restore their original order and state. At the same time, it sets the starting position of log parsing to the LSN value recorded in the checkpoint. After the restoration is completed, the source synchronization service will continue to listen to and parse new log content normally and synchronize any incomplete DDL operations.

[0075] The above process enables the restoration of the service state after restart, ensuring that DDL operations are not lost or duplicated, thereby improving the availability and stability of the system.

[0076] In summary, in the method for ring-cascaded synchronization of DDL based on database log parsing proposed in this embodiment, firstly, in the environment of database log-based cascaded synchronization, the database node information traversed by the DDL operation during synchronization needs to be transmitted level by level. The target synchronization service filters the DDL operations to be synchronized by judging whether the current database node identifier exists in the database node information traversed in the DDL operation, so as to avoid the same DDL operation being executed repeatedly on the same database node.

[0077] Secondly, when synchronizing DDL operations, the target-side synchronization service appends the DDL operation information and the current database node information to the database node information attached to the extracted DDL transaction, inserts the appended information into the auxiliary table of the current database, and then synchronizes the corresponding DDL operation. In this way, the database node information that the DDL operation passes through in the synchronization link is transmitted.

[0078] Finally, the source synchronization service captures the DDL operation information of the auxiliary tables in the log stream and associates subsequent DDL operations with the DDL operation type. This integrates the database node information passed through by the DDL operation into the corresponding DDL operation and sends it to the next-level target synchronization service. This ensures that the next-level target synchronization service can correctly filter the DDL operations and prevent the DDL operations from being executed repeatedly on the database nodes.

[0079] Example 2: To further understand the method of ring-cascaded synchronous DDL based on database log parsing proposed in Example 1, this example will be presented to further illustrate it.

[0080] In one embodiment, such as Figure 6 As shown, there are three existing database nodes, A, B and C. A cascaded ring synchronization link is built on these three database nodes, with A synchronizing to B, B synchronizing to C, and C synchronizing to A.

[0081] Deploy source synchronization service A1 and target synchronization service A2 on node A. A1 captures and parses logs before sending them to the next-level node B; A2 is responsible for receiving data synchronization operations sent by the previous-level node C. Source synchronization service A1 and target synchronization service A2 share the database node information A. Similarly, deploy source synchronization service B1 and target synchronization service B2 on node B; and deploy source synchronization service C1 and target synchronization service C2 on node C.

[0082] Each target-side synchronization service creates an auxiliary table T on its respective database node: CREATE TABLE T(TYPE VARCHAR(100),SCH_NAME VARCHAR(100),TAB_NAMEVARCHAR(100), COL_NAME VARCHAR(100),DB_INFO VARCHAR(100)).

[0083] The TYPE column stores the type of DDL operation: DROP, CREATE, ALTER, and TRUNCATE. The SCH_NAME column stores the schema name involved in the DDL operation. The TAB_NAME column stores the object name involved in the DDL operation. The COL_NAME column stores the column name of the table object involved in the DDL operation. The DB_INFO column stores the database node information that the DDL operation passes through in the synchronization link.

[0084] Each source-end synchronization service creates a checkpoint thread and a memory linked list L (hereinafter referred to as linked list L) to store DDL operation information.

[0085] The method for parsing ring-cascaded synchronization DDL based on database logs includes the following steps: 1. A third-party application executes a table creation operation CREATE TABLE SX(C INT) on the A node database and names this DDL operation DDL operation Y.

[0086] 2. The source synchronization service A1 of node A captures DDL operation Y, and does not find a matching DDL operation information in the linked list L. It integrates the current database node information A into DDL operation Y and sends it to the next level. At this time, the database node information of DDL operation Y is {A}.

[0087] 3. Target-side synchronization service B2 synchronizes DDL operation Y.

[0088] 1) Extract the database node information {A} attached to the DDL operation Y, and determine that the current database node information B does not exist.

[0089] 2) Append the operation information of DDL operation Y (DDL type: CREATE, schema name: S, table name: X) and the current database node identifier to the database node information {A, B} attached to the extracted DDL transaction and insert it into the current database auxiliary table T.

[0090] Insertion operation on auxiliary table T: INSERT INTO T VALUES('CREATE','S','X',NULL,'A,B'). This insertion operation on auxiliary table T uses database connection D, and no additional commit operation is required after insertion.

[0091] 3) Synchronize DDL operations to the current database using database connection D: CREATE TABLE SX(CINT). After DDL synchronization is complete, the insert operation on the auxiliary table T in the previous step will be automatically committed, forming two independent transactions: the insert transaction on the auxiliary table T and the DDL operation Y transaction.

[0092] 4. After the source synchronization service B1 captures the logs, restore the DDL transaction according to the following steps.

[0093] 1) Parse the captured logs and capture the insert operation of the auxiliary table T. Extract the DDL operation information (DDL type: CREATE, schema name: S, table name: X, database node information {A, B}) from the data of the insert operation in the auxiliary table T and save it to the linked list L. When saving, it is necessary to remove duplicates of the current DDL operation information in the linked list L.

[0094] 2) Capture DDL operation Y, extract the starting LSN1 of the log corresponding to DDL operation Y, and parse the operation information in the DDL log (DDL type: CREATE, schema name: S, table name: X).

[0095] 3) Use the DDL operation type: CREATE, pattern name: S, table name: X in the linked list L to match and find the corresponding DDL operation information.

[0096] 4) If there is a matching DDL operation information in the linked list L, set the starting LSN1 of the DDL operation Y to the matching DDL operation information in the linked list L, then extract the database node information {A, B} from the matching DDL operation information, integrate the database node information into the current DDL operation and send it together to the target synchronization service C2.

[0097] 5. Target-side synchronization service C2 synchronizes DDL operation Y.

[0098] 1) Extract the database node information {A, B} attached to the DDL operation Y, and determine that the current database node information C does not exist.

[0099] 2) Append the operation information of this DDL (DDL type: CREATE, schema name: S, table name: X) and the current database node information to the database node information {A, B, C} attached to the extracted DDL transaction and insert it into the current database auxiliary table T.

[0100] Insertion operation on auxiliary table T: INSERT INTO T VALUES('CREATE', 'S', 'X', NULL, 'A,B,C'). This operation uses database connection D, and no additional commit operation is required after insertion.

[0101] 3) Synchronize DDL transactions to the current database by executing the following command using database connection D: CREATE TABLE SX(CINT).

[0102] After DDL synchronization is complete, the previous step of inserting into auxiliary table T will be automatically committed, forming two independent transactions: the insertion transaction for auxiliary table T and the DDL operation Y transaction.

[0103] 6. After the source synchronization service C1 captures the logs, restore the DDL transaction according to the following steps.

[0104] 1) Parse the captured logs and capture the insert operation of the auxiliary table T. Extract the DDL operation information (DDL type: CREATE, schema name: S, table name: X, database node information {A, B, C}) contained in the insert operation data of the auxiliary table T and save it to the linked list L. When saving, it is necessary to remove duplicates of the current DDL operation information in the linked list L.

[0105] 2) Capture DDL operation Y, extract the starting LSN2 of the log corresponding to DDL operation Y, and parse the operation information in the DDL log (DDL type: CREATE, schema name: S, table name: X).

[0106] 3) Use the DDL operation type: CREATE, pattern name: S, table name: X in the linked list L to match and find the corresponding DDL operation information.

[0107] 4) If there is a matching DDL operation information in the linked list L, set the starting LSN2 of the DDL operation Y to the matching DDL operation information in the linked list L, then extract the database node information {A, B, C} from the matching DDL operation information, integrate the database node information into the current DDL operation and send it together to the target synchronization service A2.

[0108] 7. Target-side synchronization service A2 synchronizes DDL operation Y.

[0109] 1) Extract the database node information {A, B, C} attached to the DDL operation Y, and determine whether the current database node information A exists.

[0110] 2) Filter out the DDL operation and do not synchronize it, thereby preventing the DDL operation Y from being executed repeatedly on database node A.

[0111] 8. The source synchronization service saves the DDL operation information of the auxiliary table T in the linked list L and the source log parsing checkpoint LSN every 60 seconds, taking C1 as an example.

[0112] 1) After the source synchronization service C1 captures the DDL operation information of the auxiliary table T, before capturing its corresponding DDL operation Y, it obtains a fault recovery point LSN3 from the target synchronization service as a checkpoint LSN. At this time, the DDL operation information in the linked list L has not yet matched its corresponding DDL operation.

[0113] 2) Traverse the linked list L and save the DDL operation information (DDL type: CREATE, schema name: S, table name: X, database node information set {A, B, C}) that does not have a set starting LSN for the DDL operation and the DDL operation information that has a set starting LSN for the corresponding DDL operation but whose LSN value is greater than the checkpoint LSN3 to the checkpoint file.

[0114] 3) Set the current checkpoint LSN3 as the latest source synchronization service failure recovery LSN, and traverse the linked list L to release DDL operation information whose starting LSN is less than the current checkpoint LSN.

[0115] 9. After the source synchronization service C1 saves the checkpoint, it restarts. After starting, it first loads the DDL operation information (DDL type: CREATE, schema name: S, table name: X, database node information set {A, B, C}) saved in the checkpoint file and restores it to the linked list L. Then, it sets the starting position LSN of log parsing to the checkpoint LSN3 saved in the checkpoint file.

[0116] 1) Capture DDL operation Y, extract the starting LSN2 of the log corresponding to DDL operation Y, and parse the operation information in the DDL log (DDL type: CREATE, schema name: S, table name: X).

[0117] 2) Use the DDL operation type: CREATE, schema name: S, table name: X in the linked list L to match and find the corresponding DDL operation information.

[0118] 3) If there is a matching DDL operation information in the linked list L, set the starting LSN2 of the DDL operation Y to the matching DDL operation information in the linked list L, then extract the database node information {A, B, C} from the matching DDL operation information, integrate the database node information into the current DDL operation and send it together to the target synchronization service A2.

[0119] As can be seen from the above process, DDL operation Y is initiated by a third party on database node A. After the source synchronization service A1 captures the DDL operation Y, it integrates the information of database node A and sends it to the target synchronization service B2. Before B2 executes DDL operation Y, it needs to append the current database node information B when inserting the DDL operation information into the auxiliary table T. Then, the source synchronization service B1 captures the DDL operation Y and sends it to the target synchronization service C2. At this time, the set of database node information passed through by DDL operation Y changes from {A} to {A, B}. And so on. When the target synchronization service A2 receives DDL operation Y delivered by the source synchronization service C1, the set of database nodes attached to the DDL operation is {A, B, C}, which includes the current database node information A. Therefore, the DDL operation Y can be filtered out and desynchronized, thus preventing the DDL operation Y from being executed repeatedly on database node A.

[0120] Example 3: In Embodiment 1, a method for parsing ring-cascaded synchronous DDL based on database logs was provided. In this embodiment, an apparatus for parsing ring-cascaded synchronous DDL based on database logs will be proposed. The apparatus for parsing ring-cascaded synchronous DDL based on database logs includes: a processor and a memory for storing processor-executable instructions; wherein, the processor is configured to execute the method for parsing ring-cascaded synchronous DDL based on database logs described in Embodiment 1.

[0121] like Figure 7As shown, the device for parsing ring-cascaded synchronous DDL based on database logs includes a processor 21 and a memory 22, wherein the processor 21 and the memory 22 can be connected by a bus or other means.

[0122] Processor 21 can be a Central Processing Unit (CPU). Processor 21 can also be other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, or combinations of the above types of chips.

[0123] The memory 22, as a non-transitory computer-readable storage medium, can be used to store non-transitory software programs, non-transitory computer-executable programs, and modules, such as the program instructions / modules corresponding to the method based on database log parsing ring cascade synchronization DDL in Embodiment 1 of this invention. The processor executes various functional applications and training processes by running the non-transitory software programs, instructions, and modules stored in the memory.

[0124] The memory 22 may include a program storage area and a training storage area. The program storage area may store the operating system and applications required for at least one function; the training storage area may store training data created by the processor. Furthermore, the memory may include high-speed random access memory and non-transitory memory, such as at least one disk storage device, flash memory, or other non-transitory solid-state storage device. In some embodiments, the memory 22 may optionally include memory remotely located relative to the processor, which can be connected to the processor via a network. Examples of such networks include, but are not limited to, the Internet, intranets, local area networks, mobile communication networks, and combinations thereof. The one or more modules stored in the memory 22, when executed by the processor 21, perform functions such as... Figure 1 The method for parsing ring-cascaded synchronization DDL based on database logs in Example 1 is shown below. For specific details of the above method for parsing ring-cascaded synchronization DDL based on database logs, please refer to the relevant documentation. Figure 1 , Figure 2 and Figure 3 The relevant descriptions and effects in the embodiments shown are for reference only and will not be repeated here.

[0125] This embodiment also provides a computer storage medium storing a computer program that can be executed by a processor to complete the method for parsing ring-cascaded synchronous DDL based on database log parsing described in Embodiment 1.

[0126] The computer storage medium stores computer-executable instructions, which can execute the method of parsing the ring-cascaded synchronous DDL based on database logs in any of the above method embodiments. The storage medium can be a magnetic disk, optical disk, read-only memory (ROM), random access memory (RAM), flash memory, hard disk drive (HDD), or solid-state drive (SSD), etc.; the storage medium may also include combinations of the above types of memory.

[0127] The specific steps of the method for parsing ring-cascaded synchronous DDL based on database logs are described in Example 1, and will not be repeated in this example.

[0128] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of the present invention should be included within the protection scope of the present invention.

Claims

1. A method for parsing ring-cascaded synchronous DDL based on database logs, characterized in that, include: Before performing the synchronization DDL operation, the target-side synchronization service inserts DDL operation information containing database node information into the auxiliary table. The source-side synchronization service sequentially captures DDL operation information from the auxiliary tables and DDL operations generated by the database, associates the captured DDL operation information with the corresponding DDL operations, and delivers the associated DDL operation information and DDL operations to the next-level target-side synchronization service. Specifically, this includes: The source synchronization service parses the database logs. When it captures an insert operation in the auxiliary table, it extracts DDL operation information from the inserted data in the auxiliary table and stores the extracted DDL operation information in a memory linked list. When a DDL operation is captured, the starting LSN of the DDL operation log is extracted, and the log of the DDL operation is parsed according to the starting LSN of the DDL operation log to obtain DDL operation information including object name and DDL operation type. The memory linked list is used to match DDL operation information with the same object name and DDL operation type to obtain a matching result. Based on the matching result, the associated DDL operation information and DDL operation are delivered to the next-level target synchronization service. Specifically, this includes: When there is no DDL operation information with the same object name and DDL operation type in the memory linked list, the current database node identifier is appended to the current DDL operation and delivered to the next level target synchronization service. When there are DDL operation information with the same object name and DDL operation type in the memory linked list, the starting LSN of the current DDL operation is written to the matched DDL operation information in the memory linked list. Extract the database node information recorded in the matched DDL operation information in the memory linked list, integrate the database node information into the current DDL operation, and deliver the integrated DDL operation and database node information to the next level target synchronization service. The next-level target synchronization service parses the database node information in the received DDL operation information. When the database node information includes the current database node identifier, the corresponding DDL operation is filtered out.

2. The method for ring-cascaded synchronous DDL based on database log parsing according to claim 1, characterized in that, Before performing the synchronization DDL operation, the target-side synchronization service inserts DDL operation information containing database node information into the auxiliary table, specifically including: The target synchronization service receives the associated DDL operation information and DDL operations from the previous source synchronization service, and extracts the database node information attached to the DDL operation information. When the extracted database node information contains the current database node identifier, the extracted DDL operations are filtered out. When the extracted database node information does not contain the current database node identifier, the current database node identifier is appended to the extracted database node information to obtain the updated database node information, and the updated database node information is inserted into the auxiliary table of the current database.

3. The method for ring-cascaded synchronous DDL based on database log parsing according to claim 1, characterized in that, The method further includes: The source synchronization service saves the DDL operation information in the memory linked list and the starting LSN of the log used for source parsing to the checkpoint file at preset intervals. When the source synchronization service restarts, it loads the DDL operation information saved in the checkpoint file and restores it to the memory linked list, and sets the starting LSN of the log parsed by the source to the starting LSN of the log saved in the checkpoint file.

4. The method for ring-cascaded synchronous DDL based on database log parsing according to claim 3, characterized in that, The source-end synchronization service saves the DDL operation information in the memory linked list and the starting LSN of the log used for source-end parsing to the checkpoint file at preset intervals, specifically including: The source synchronization service obtains the fault recovery point LSN from the target synchronization service as the checkpoint LSN; Traverse the memory linked list and save the DDL operation information for which no starting LSN has been set, and the DDL operation information for which a starting LSN has been set but which is greater than the checkpoint LSN, to the checkpoint file. Update the current checkpoint LSN to the latest fault recovery point LSN, traverse the memory linked list, and release DDL operation information whose starting LSN is less than the current checkpoint LSN.

5. The method for ring-cascaded synchronous DDL based on database log parsing according to claim 3, characterized in that, When the source synchronization service restarts, the DDL operation information saved in the checkpoint file is loaded and restored to the memory linked list, and the starting LSN of the log parsed by the source is set to the starting LSN of the log saved in the checkpoint file. Specifically, this includes: When the source synchronization service restarts, it checks if a new checkpoint file exists. If it does, it opens and parses the checkpoint file, extracts all DDL operation information, and inserts these DDL operation information entries into the memory linked list one by one to restore their original order and state. Meanwhile, the starting position for log parsing is set to the LSN value recorded in the checkpoint. After recovery is complete, the source synchronization service will continue to listen for and parse new log content normally, and synchronize any incomplete DDL operations.

6. The method for ring-cascaded synchronous DDL based on database log parsing according to claim 1, characterized in that, The method further includes: The target-side synchronization service performs auxiliary table insertion and DDL operations through the same database connection; After inserting DDL operation information into the auxiliary table, keep the transaction in an uncommitted state; By utilizing the implicit commit mechanism triggered by subsequent DDL operations, the auxiliary table insertion operation and the DDL operation are committed simultaneously as atomic transactions.

7. The method for ring-cascaded synchronous DDL based on database log parsing according to claim 1, characterized in that, The DDL operation information includes the database node information and the current database node identifier that the corresponding DDL operation sent by the previous source synchronization service passes through in the synchronization link.

8. The method for ring-cascaded synchronous DDL based on database log parsing according to claim 1, characterized in that, The DDL operation information also includes the database object information targeted by the corresponding DDL operation and the DDL operation type; wherein, when the DDL operation type is a column modification operation, the DDL operation type includes the column names involved.

9. The method for ring-cascaded synchronous DDL based on database log parsing according to claim 1, characterized in that, The method further includes: On the database service environment, both source synchronization service and target synchronization service are deployed simultaneously. The source synchronization service is responsible for parsing the current database log operations and delivering them to the next level target synchronization service, while the target synchronization service is responsible for receiving the operations delivered by the previous level source synchronization service and executing them. In the current environment, the source synchronization service and the target synchronization service share the current database node.

10. The method for ring-cascaded synchronous DDL based on database log parsing according to claim 1, characterized in that, The method further includes: Insert the DDL operation information to be executed into the auxiliary table so that the source synchronization service can parse the DDL operation information in the INSERT operation in the auxiliary table and associate it with the subsequently captured DDL operations.

11. The method for ring-cascaded synchronous DDL based on database log parsing according to claim 1, characterized in that, The method further includes: If a DDL operation is generated on the current node, that is, if the current database node identifier is matched in the parsed database node information, the DDL operation will not be executed again, thus avoiding the situation where the same DDL operation is repeatedly synchronized on a database node.

12. A device for ring-cascaded synchronous DDL based on database log parsing, characterized in that, The device for parsing ring-cascaded synchronous DDL based on database logs includes: a processor and a memory for storing processor-executable instructions; The processor is configured to execute the method of parsing ring-cascaded synchronous DDL based on database log parsing as described in any one of claims 1-11.

13. A non-volatile computer storage medium, characterized in that, The computer storage medium stores computer-executable instructions, which are executed by one or more processors to perform the method of circular cascading synchronous DDL based on database log parsing as described in any one of claims 1-11.

Images