Incremental synchronization method and device for database table structure, equipment and storage medium

Abstract

The invention discloses an incremental synchronization method, device and equipment of a database table structure and a storage medium, and is applied to the field of big data, and the method comprises the following steps: obtaining a DDL statement which causes the structure change of a source database through a trigger, and adding metadata of the DDL statement into a DDL operation record table; generating a pre-write log according to an addition operation of the DDL operation record table, and writing the pre-write log into the logic copy slot; and when a log reading request sent by the client through log monitoring is received, sending the pre-written log to the client, so that the client restores the DDL statement according to the pre-written log and sends the DDL statement to the target database, and the target database and the source database keep table structure increment synchronization. According to the method, the pre-write log which can be recognized and processed by the logic replication slot is generated through the trigger, the pre-write log is monitored for the logic replication slot through the client, and the DDL statement is analyzed and restored, so that corresponding DDL operation is executed in the target database, and increment synchronization of the table structure is realized.

Description

Technical Field

[0001] The present invention relates to the field of big data technology, and in particular to a method, device, equipment and storage medium for incremental synchronization of a database table structure. Background Art

[0002] When the existing master-slave database uses a table structure based on Write-Ahead Logging (WAL) for synchronization, physical replication slots are usually established to replicate changes in the form of data blocks, thereby replicating changes in actual files and ultimately achieving the purpose of table structure synchronization.

[0003] However, physical replication slots are limited to use between master and slave libraries and cannot be extended to other types of clients, so they have low versatility and scalability. In contrast, logical replication slots have greater flexibility. They can perform log parsing across different clients and can selectively filter the tables or event types to be replicated. They are usually used for data synchronization. It is worth noting that although WAL logs can record data changes in the database, such as insert, update, or delete operations, they do not include modifications to the database structure, such as creating, modifying, or deleting tables, indexes, and views. Therefore, although log parsing can be performed across different clients based on logical replication slots, it can only be used for data synchronization. Therefore, the existing database synchronization methods have poor versatility and flexibility and cannot meet users' database structure synchronization needs. Summary of the invention

[0004] The present invention provides an incremental synchronization method for a database table structure to achieve consistency of table structures in different database environments.

[0005] According to a first aspect of the present invention, there is provided a method for incremental synchronization of a database table structure, comprising: creating a data definition language DDL operation record table and a trigger in a source database;

[0006] Acquire the DDL statement that causes the source database structure change through the trigger, and add the metadata of the DDL statement to the DDL operation record table;

[0007] Generate a write-ahead log according to the add operation of the DDL operation record table, and write the write-ahead log into the logical replication slot, wherein the write-ahead log includes a data manipulation language DML statement;

[0008] When a log read request sent by a client through log monitoring is received, the write-ahead log is sent to the client, so that the client restores the DDL statement according to the write-ahead log and sends it to the target database, and the target database keeps incremental synchronization of the table structure with the source database.

[0009] According to another aspect of the present invention, there is provided an incremental synchronization device for a database table structure, comprising:

[0010] DDL operation record table creation module, used to create a data definition language DDL operation record table and trigger in the source database;

[0011] Adding a DDL operation record table module, used to obtain the DDL statement that causes the source database structure change through the trigger, and add the metadata of the DDL statement to the DDL operation record table;

[0012] A write-ahead log writing module, used to generate a write-ahead log according to the adding operation of the DDL operation record table, and write the write-ahead log into the logical replication slot, wherein the write-ahead log includes a data manipulation language DML statement;

[0013] The log monitoring module is used to send the write-ahead log to the client when receiving a log reading request sent by the client through log monitoring, so that the client can restore the DDL statement according to the write-ahead log and send it to the target database, and keep the target database and the source database in incremental synchronization of table structure.

[0014] According to another aspect of the present invention, there is provided an electronic device, the electronic device comprising:

[0015] at least one processor; and

[0016] a memory communicatively connected to the at least one processor; wherein,

[0017] The memory stores a computer program that can be executed by the at least one processor. The computer program is executed by the at least one processor so that the at least one processor can perform the method described in any embodiment of the present invention.

[0018] According to another aspect of the present invention, a computer-readable storage medium is provided, wherein the computer-readable storage medium stores computer instructions, and the computer instructions are used to enable a processor to implement the method described in any embodiment of the present invention when executed.

[0019] According to another aspect of the present invention, a computer program product is provided, comprising a computer program, and when the computer program is executed by a processor, the method described in any embodiment of the present invention is implemented.

[0020] The beneficial technical effect of the present invention is that the DDL operation is recorded in the operation record table through the trigger mechanism of the trigger to generate a pre-write log that can be recognized and processed by the logical replication slot, and the logical replication slot is monitored by the client to obtain the pre-write log and parse and restore the DDL statement to execute the corresponding DDL operation in the target database, thereby realizing incremental synchronization of the table structure, avoiding human operation errors, ensuring the consistency of the table structure in different database environments, and meeting compliance requirements.

[0021] It should be understood that the contents described in this section are not intended to identify the key or important features of the embodiments of the present invention, nor are they intended to limit the scope of the present invention. Other features of the present invention will become easily understood through the following description. BRIEF DESCRIPTION OF THE DRAWINGS

[0022] In order to more clearly illustrate the technical solutions in the embodiments of the present invention, the following briefly introduces the drawings required for use in the description of the embodiments. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work.

[0023] Figure 1 is a flowchart of an incremental synchronization method of a database table structure provided according to Embodiment 1 of the present invention;

[0024] Figure 2 is a flowchart of an incremental synchronization method of a database table structure provided according to Embodiment 2 of the present invention;

[0025] Figure 3 is a schematic diagram of the structure of an incremental synchronization device for a database table structure provided according to a third embodiment of the present invention;

[0026] Figure 4 It is a schematic diagram of the structure of an electronic device provided by Embodiment 4 of the present invention. DETAILED DESCRIPTION

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

[0028] It should be noted that the terms "first", "second", etc. in the specification and claims of the present invention 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 interchanged where appropriate, so that the embodiments of the present invention described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having" and any variations thereof are intended to cover non-exclusive inclusions, for example, a process, method, system, product or device that includes a series of steps or units is not necessarily limited to those steps or units that are clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0029] Embodiment 1

[0030] Figure 1 A flowchart of a method for incremental synchronization of a database table structure is provided for the first embodiment of the present invention. This embodiment is applicable to the case where table structures in different database environments are incrementally synchronized. The method can be executed by an incremental synchronization device for a database table structure, which can be implemented in the form of hardware and / or software. Figure 1 As shown, the method includes:

[0031] Step S101: Create a data definition language DDL operation record table and a trigger in a source database.

[0032] Specifically, in database programming, Data Definition Language (DDL) is mainly used to describe and define the structure of database objects, including creating, modifying and deleting objects such as tables, views and indexes in the database. DDL incremental synchronization plays a key role in maintaining database structure consistency between multiple environments, implementing version control, automated deployment and meeting compliance. Among them, database structure consistency means that there may be different database instances in development, test and production environments. DDL incremental synchronization ensures that the structures of different database instances are consistent to avoid problems caused by structural differences; version control refers to synchronizing DDL changes as incremental scripts to complete version control of database structure changes, thereby tracking the change history of the database structure and being able to roll back or reapply the changes of the specified version when necessary; automated deployment refers to incorporating database structure changes into the automated deployment process through DDL incremental synchronization, thereby ensuring that the database structure is changed accordingly with the application code and quickly deployed to the target environment; compliance refers to some industry standards and regulations requiring auditing and recording of database structure changes, and DDL incremental synchronization is used to record the history of structure changes and meet compliance requirements. In summary, we can see that DDL incremental synchronization plays a very important role in maintaining the consistency of data structures in different environments.

[0033] Among them, the database involved in this embodiment can specifically be a PostgreSQL database. Of course, this embodiment is only an example and does not limit the type of database. And in this embodiment, the DDL operation is recorded in the operation record table by utilizing the powerful trigger technology of PostgreSQL, and the DDL statement is parsed and restored on the client by subscribing to the WAL log of the operation record table, and the corresponding DDL operation is performed on the target source, thereby realizing incremental synchronization of the table structure. This method can not only capture the metadata of database structure changes, but also quickly and reliably synchronize these changes in different environments, ensuring the consistency of the database structure and meeting various compliance requirements.

[0034] Optionally, a data definition language DDL operation record table and a trigger are created in the source database, including: receiving a DDL operation record table creation instruction, and creating a DDL operation record table in the source database according to the DDL operation record table creation instruction, wherein the creation instruction includes an operation type field and an operation table name field; defining the trigger logic of the trigger according to the received trigger definition instruction, and adding the defined trigger to the source database.

[0035] In a specific implementation, a DDL operation record table is created to record the metadata of the DDL statement, specifically, a DDL operation record table creation instruction is received, and the creation instruction includes fields that have a significant impact on the database structure change, such as the operation type field and the operation table name field, so as to accurately track the change history of the database structure. Of course, this embodiment is only an example, and does not limit the field type included in the creation instruction. As long as the incremental synchronization of the database structure table can be achieved, it is within the protection scope of this application. And the DDL operation record table created by the above creation instruction is a blank table containing only a table header, so it does not contain specific values ​​at the initial moment.

[0036] In addition, in this implementation, the trigger logic of the trigger is defined according to the trigger definition instruction, and the defined trigger is added to the source database. The trigger logic definition is specifically implemented by writing a trigger execution function, wherein the trigger logic can specifically be when a structural change occurs in the source database, obtain the metadata of the DDL operation, and add its information to the DDL operation record table. Of course, this implementation is only an example for illustration, and does not limit the specific creation process of the trigger in the source database.

[0037] Step S102: obtaining the DDL statement causing the source database structure change through the trigger, and adding the metadata of the DDL statement to the DDL operation record table.

[0038] Optionally, obtaining the DDL statement causing the source database structure change through a trigger includes: starting the trigger when the structure change of the source database meets the trigger logic; identifying the DDL statement causing the source database structure change through the trigger, and capturing the DDL statement to obtain the DDL statement.

[0039] Optionally, adding metadata of the DDL statement to the DDL operation record table includes: extracting field values ​​in the DDL statement that match fields in the DDL operation record table; and adding the field values ​​as metadata to the DDL operation record table.

[0040] Specifically, in this embodiment, after the DDL operation record table and the trigger are created, when the structure of the source database changes, the trigger will be started through the trigger logic, and the DDL statement that causes the source database structure change will be identified and captured by the trigger in the started state, wherein, when the structure of the database changes, the number of captured DDL statements is at least one. Of course, the specific number and type of the captured DDL statements are not limited in this embodiment, as long as they are related to the structural change of the database, they are all within the protection scope of this application. In addition, since the DDL statement includes multiple fields, metadata is extracted and added according to the fields in the operation record table, and key metadata related to the table structure change is extracted, thereby reducing the amount of data extraction while ensuring the incremental synchronization of the database, and significantly improving the data processing efficiency. And when adding metadata, the field value in the DDL statement that matches the field in the DDL operation record table is used as metadata. For example, if the DDL operation record table contains a gender field, the "female" in the captured DDL statement is used as metadata, and the metadata is added to the DDL operation record table. Of course, this embodiment is only an example, and the specific content of the field value is not limited.

[0041] It should be noted that the DDL operation record table created in this embodiment is set with a certain record capacity. When the data in the DDL operation record table reaches the set capacity, a prompt message will be generated, such as "the current DDL operation record table has reached the maximum record capacity". At this time, the user can process the DDL operation record table according to the prompt message. Among them, a variety of processing methods can be used for the prompt message, for example, expanding the capacity of the DDL operation record table itself, or transferring and backing up the data in the DDL operation record table. Of course, this embodiment is only an example, and does not limit the specific method used. As long as the original data in the DDL operation record table can be guaranteed not to be lost, data can continue to be added to the DDL operation record table, which is within the protection scope of this application.

[0042] Step S103, generating a write-ahead log according to the adding operation of the DDL operation record table, and writing the write-ahead log into the logical replication slot, wherein the write-ahead log includes a data manipulation language DML statement.

[0043] Optionally, a write-ahead log is generated according to the add operation of the DDL operation record table. Before the write-ahead log is written into the logical replication slot, the process also includes: taking the source database as the master node and the client as the slave node of the source database; establishing a logical replication slot on the master node, and establishing a streaming replication connection between the slave node and the master node based on the logical replication slot.

[0044] Optionally, a write-ahead log is generated according to the add operation of the DDL operation record table, and the write-ahead log is written into the logical replication slot, including: converting each add operation of the DDL operation record table to generate a DML statement that matches the add operation; obtaining the conversion time of the DML statement, and adding the conversion time as a label to the DML statement; generating a write-ahead log according to the DML statement with the label added, and writing the write-ahead log into the logical replication slot, so that the client monitors the log of the logical replication slot through the streaming replication connection.

[0045] Specifically, the source database and the target database may not have a subordinate relationship. In this implementation, the source database may be used as a master node, the client may be disguised as a slave node of the database, and a logical replication slot may be established on the source database serving as the master node. Since the logical replication slot of this implementation has greater flexibility, it can perform log parsing across different clients. Therefore, even if the source database and the target database do not have a subordinate relationship, that is, they are located on different clients, data synchronization can be achieved. In addition, although the logical replication slot can solve the problem of non-master-slave between databases, since the WAL log cannot record structural modifications in the database, that is, DDL statements, in the present embodiment, in the process of adding DDL statements to the operation record table through triggers, a WAL log containing only DML statements can be generated according to the adding process, that is, although the DDL statement itself cannot be recorded in the WAL log, the operation process of adding the metadata in the DDL statement to the operation record table each time can generate a DML statement in data form, that is, the adding operation is converted into a DML statement, and the conversion time is obtained for each conversion process, and the conversion time is added to the DML statement as a label, so that a pre-write log can be generated according to each DML statement with a label added. At this time, the generated pre-write log only contains DML statements in data form, and the source database will write the generated pre-write log into the pre-created logical replication slot.

[0046] It is worth mentioning that, since in the present embodiment, when a logical replication slot is established on the master node, a stream replication connection relationship between the master node and the logical replication slot has been established based on the logical replication slot, that is, the information in the source database can be transmitted to the client through the logical replication slot, and the corresponding client can further transmit the acquired data to the target database, that is, although the source database and the target database do not have a master-slave relationship, the two can exchange information through the client as a slave node.

[0047] Step S104, when receiving a log reading request sent by the client through log monitoring, sending the write-ahead log to the client, so that the client restores the DDL statement according to the write-ahead log and sends it to the target database, and keeps the table structure of the target database synchronized with the source database.

[0048] Specifically, in this embodiment, the client can monitor the log in the logical replication slot of the master node. When it is monitored that the pre-write log is stored in the logical replication slot, a read request will be sent to the source database. At this time, the client as a slave node will be identified. When it is determined that the client is a registered legal slave node, it will respond to the read request and open a stream replication connection to send the saved pre-write log to the client through the stream replication connection to achieve the replication of the pre-write log. When it is determined through identity recognition that the client sending the request is an unregistered illegal node, the stream replication connection will be closed. When the read request of the client is received multiple times, an identity verification prompt will be generated to notify the administrator of the source database side to verify whether the client is a node that has missed registration and whether to include the client in the legal node. Of course, this embodiment is only an example, and does not limit the specific process of pre-writing logs through logical replication slots. As long as the pre-write logs generated in the source database can be transmitted to the client, they are all within the protection scope of this application.

[0049] Among them, after obtaining the pre-write log in data form, the client will parse it, that is, parse the log into the metadata of the row changes in the DDL operation record table, and convert it into a form that is easy to understand and operate, and restore the DDL statement with the same semantics as the original execution DDL statement by splicing SQL statements based on the metadata of the row changes. Among them, when splicing, a template matching method can be used, that is, a matching SQL connection template is called, and the metadata is spliced ​​according to the splicing order in the connection template. Of course, the splicing can also be performed in a user-defined manner. The specific splicing method is not limited in this embodiment. The main restored DDL statement is the same as the original execution DDL statement, and they are all within the protection scope of this application.

[0050] It should be noted that, since only some key metadata in the DDL statement may be added when adding the DDL operation record table, the metadata of the restored DDL statement may be less than that of the original executed DDL statement, or it may be the same as the original executed DDL statement. However, as long as the two have the same semantics and achieve the same database structure change effect, they are within the scope of protection of this application and are not limited in this embodiment.

[0051] In the implementation mode of the present application, the DDL operation is recorded in the operation record table through the trigger mechanism of the trigger to generate a pre-write log that can be recognized and processed by the logical replication slot, and the logical replication slot is monitored by the client to obtain the pre-write log and parse and restore the DDL statement to execute the corresponding DDL operation in the target database, thereby realizing incremental synchronization of the table structure, avoiding human operational errors, ensuring the consistency of the table structure in different database environments, and meeting compliance requirements.

[0052] Embodiment 2

[0053] Figure 2 This is a flowchart of a method for incremental synchronization of a database table structure provided in the second embodiment of the present invention. This embodiment is based on the above embodiment. After generating a write-ahead log according to the addition operation of the DDL operation record table and writing the write-ahead log into the logical replication slot, it also includes: detecting the write-ahead log and repairing the source database based on the abnormal DML statements obtained by the detection. Figure 2 As shown, the method includes:

[0054] Step S201: Create a data definition language DDL operation record table and a trigger in the source database.

[0055] Optionally, a data definition language DDL operation record table and a trigger are created in the source database, including: receiving a DDL operation record table creation instruction, and creating a DDL operation record table in the source database according to the DDL operation record table creation instruction, wherein the creation instruction includes an operation type field and an operation table name field; defining the trigger logic of the trigger according to the received trigger definition instruction, and adding the defined trigger to the source database.

[0056] Step S202: obtaining the DDL statement causing the source database structure change through the trigger, and adding the metadata of the DDL statement to the DDL operation record table.

[0057] Optionally, obtaining the DDL statement causing the source database structure change through a trigger includes: starting the trigger when the structure change of the source database meets the trigger logic; identifying the DDL statement causing the source database structure change through the trigger, and capturing the DDL statement to obtain the DDL statement.

[0058] Optionally, adding metadata of the DDL statement to the DDL operation record table includes: extracting field values ​​in the DDL statement that match fields in the DDL operation record table; and adding the field values ​​as metadata to the DDL operation record table.

[0059] Step S203, generating a write-ahead log according to the adding operation of the DDL operation record table, and writing the write-ahead log into the logical replication slot, wherein the write-ahead log includes a data manipulation language DML statement.

[0060] Optionally, a write-ahead log is generated according to the add operation of the DDL operation record table. Before the write-ahead log is written into the logical replication slot, the process also includes: taking the source database as the master node and the client as the slave node of the source database; establishing a logical replication slot on the master node, and establishing a streaming replication connection between the slave node and the master node based on the logical replication slot.

[0061] Optionally, a write-ahead log is generated according to the add operation of the DDL operation record table, and the write-ahead log is written into the logical replication slot, including: converting each add operation of the DDL operation record table to generate a DML statement that matches the add operation; obtaining the conversion time of the DML statement, and adding the conversion time as a label to the DML statement; generating a write-ahead log according to the DML statement with the label added, and writing the write-ahead log into the logical replication slot, so that the client monitors the log of the logical replication slot through the streaming replication connection.

[0062] Step S204: detect the write-ahead log, and repair the source database based on the abnormal DML statements obtained through the detection.

[0063] Optionally, the write-ahead log is detected, and the source database is repaired based on the abnormal DML statements obtained through the detection, including: detecting the write-ahead log to obtain abnormal DML statements with faults; obtaining conversion time tags added to the abnormal DML statements, and obtaining abnormal operations of source database structure changes based on the conversion time tags, and repairing the source database based on the abnormal operations.

[0064] Specifically, in this embodiment, after obtaining the pre-write log, the pre-write log will be detected, specifically, whether there is an abnormal DML statement with a fault in the pre-write log, for example, the abnormal DML statement can be a normal indicator whose value exceeds the parameter, the statement structure is wrong, or the statement execution method is inconsistent with the database, etc. Of course, this embodiment is only an example, and the specific type of abnormal DML statement is not limited. Among them, the reason for the failure of the DML statement may be that the operation of the structure change of the corresponding source database is wrong, such as the existence of a malicious attack by an illegal person, etc. Since a conversion time label is added to the abnormal DML statement, the abnormal operation of the structure change corresponding to the abnormal DML statement can be queried according to the conversion time label. If the operation on the source database is abnormal, it will cause an error in the source database, such as data failure, total loss, or even cause the equipment to crash, thereby causing data security issues. Of course, this embodiment is only an example, and the specific error situation of the source data is not limited. Therefore, after locating the abnormal operation, the source database can be repaired based on the abnormal operation. For example, the error parameters in the abnormal operation are obtained and the error parameters are corrected; or the error actions in the abnormal operation are obtained, and the source database is rolled back based on the error actions. Of course, this embodiment is only an example, and does not limit the specific repair method of the source database. In this embodiment, the abnormal operation of the structure change in the source database can be quickly located through the time label in the DML statement, so as to achieve rapid and accurate repair of the source database to ensure the accuracy of the structure change in the source database, so as to ensure that the target database refers to the source database with accurate structure change to perform accurate table structure incremental synchronization.

[0065] It should be noted that if the repair operation on the database in this embodiment involves a structural change of the database, the write-ahead log will be updated synchronously according to the repair operation, thereby ensuring that the target database and the source database maintain synchronization of the table structure. The specific update process of the write-ahead log can be roughly the same as the above-mentioned generation process of the write-ahead log, and will not be repeated in this embodiment.

[0066] Step S205, when receiving a log reading request sent by the client through log monitoring, sending the write-ahead log to the client, so that the client restores the DDL statement according to the write-ahead log and sends it to the target database, and keeps the table structure of the target database synchronized with the source database.

[0067] In the implementation mode of the present application, the DDL operation is recorded in the operation record table through the trigger mechanism of the trigger to generate a pre-write log that can be recognized and processed by the logical replication slot, and the logical replication slot is monitored by the client to obtain the pre-write log and parse and restore the DDL statement to execute the corresponding DDL operation in the target database, thereby realizing incremental synchronization of the table structure, avoiding human operational errors, ensuring the consistency of the table structure in different database environments, and meeting compliance requirements.

[0068] Embodiment 3

[0069] Figure 3 This is a schematic diagram of a structure of an incremental synchronization device for a database table structure provided by Embodiment 3 of the present invention. Figure 3 As shown, the device includes: a DDL operation record table creation module 310 , a DDL operation record table addition module 320 , a write-ahead log writing module 330 and a log monitoring module 340 .

[0070] The DDL operation record table creation module 310 is used to create a data definition language DDL operation record table and a trigger in the source database;

[0071] A DDL operation record table adding module 320 is used to obtain the DDL statement that causes the source database structure change through a trigger, and add the metadata of the DDL statement to the DDL operation record table;

[0072] A write-ahead log writing module 330 is used to generate a write-ahead log according to the add operation of the DDL operation record table, and write the write-ahead log into the logical replication slot, wherein the write-ahead log includes a data manipulation language DML statement;

[0073] The log monitoring module 340 is used to send the pre-write log to the client when receiving a log reading request sent by the client through log monitoring, so that the client can restore the DDL statement according to the pre-write log and send it to the target database, and keep the target database and the source database synchronized with each other in table structure increments.

[0074] Optionally, a DDL operation record table creation module is used to receive a DDL operation record table creation instruction, and create a DDL operation record table in the source database according to the DDL operation record table creation instruction, wherein the creation instruction includes an operation type field and an operation table name field;

[0075] The trigger logic of the trigger is defined according to the received trigger definition instruction, and the defined trigger is added to the source database.

[0076] Optionally, the module for adding a DDL operation record table includes a DDL statement acquisition unit, which is used to start a trigger when the structure change of the source database meets the trigger logic;

[0077] The trigger identifies the DDL statements that cause the source database structure to change, and captures the DDL statements to obtain the DDL statements.

[0078] Optionally, the module for adding a DDL operation record table further includes an adding a DDL operation record table unit, which is used to extract field values ​​in the DDL statement that match fields in the DDL operation record table;

[0079] Add the field value as metadata to the DDL operation record table.

[0080] Optionally, the device further comprises a logic replication slot establishment module, which is used to use the source database as a master node and the client as a slave node of the source database;

[0081] A logical replication slot is created on the master node, and a streaming replication connection is established between the slave node and the master node based on the logical replication slot.

[0082] Optionally, a write-ahead log writing module is used to convert each add operation of the DDL operation record table to generate a DML statement matching the add operation;

[0083] Get the conversion time of the DML statement and add the conversion time as a tag to the DML statement;

[0084] Generates a write-ahead log based on the tagged DML statement and writes the write-ahead log to the logical replication slot so that the client can monitor the log of the logical replication slot through the streaming replication connection.

[0085] Optionally, the device further includes a source database repair module, which is used to detect the write-ahead log to obtain abnormal DML statements with faults;

[0086] The conversion time label added to the abnormal DML statement is obtained, and the abnormal structure change operation of the source database is obtained based on the conversion time label, and the source database is repaired based on the abnormal operation.

[0087] The incremental synchronization device for a database table structure provided in an embodiment of the present invention can execute the incremental synchronization method for a database table structure provided in any embodiment of the present invention, and has functional modules and beneficial effects corresponding to the execution method.

[0088] Embodiment 4

[0089] Figure 4A schematic diagram of the structure of an electronic device 10 that can be used to implement an embodiment of the present invention is shown. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital processing, cellular phones, smart phones, wearable devices (such as helmets, glasses, watches, etc.) and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely examples and are not intended to limit the implementation of the present invention described and / or required herein.

[0090] like Figure 4 As shown, the electronic device 10 includes at least one processor 11, and a memory connected to the at least one processor 11, such as a read-only memory (ROM) 12, a random access memory (RAM) 13, etc., wherein the memory stores a computer program that can be executed by at least one processor, and the processor 11 can perform various appropriate actions and processes according to the computer program stored in the read-only memory (ROM) 12 or the computer program loaded from the storage unit 18 to the random access memory (RAM) 13. In the RAM 13, various programs and data required for the operation of the electronic device 10 can also be stored. The processor 11, the ROM 12, and the RAM 13 are connected to each other through a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.

[0091] A number of components in the electronic device 10 are connected to the I / O interface 15, including: an input unit 16, such as a keyboard, a mouse, etc.; an output unit 17, such as various types of displays, speakers, etc.; a storage unit 18, such as a disk, an optical disk, etc.; and a communication unit 19, such as a network card, a modem, a wireless communication transceiver, etc. The communication unit 19 allows the electronic device 10 to exchange information / data with other devices through a computer network such as the Internet and / or various telecommunication networks.

[0092] The processor 11 may be a variety of general and / or special processing components with processing and computing capabilities. Some examples of the processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any appropriate processor, controller, microcontroller, etc. The processor 11 executes the various methods and processes described above, such as an incremental synchronization method for a database table structure.

[0093] In some embodiments, the incremental synchronization method applied to the database table structure may be implemented as a computer program, which is tangibly contained in a computer-readable storage medium, such as a storage unit 18. In some embodiments, part or all of the computer program may be loaded and / or installed on the electronic device 10 via the ROM 12 and / or the communication unit 19. When the computer program is loaded into the RAM 13 and executed by the processor 11, one or more steps of the incremental synchronization method of the database table structure described above may be performed. Alternatively, in other embodiments, the processor 11 may be configured to perform the incremental synchronization method of the database table structure in any other appropriate manner (e.g., by means of firmware).

[0094] Various implementations of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field programmable gate arrays (FPGAs), application specific integrated circuits (ASICs), application specific standard products (ASSPs), systems on chips (SOCs), load programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various implementations can include: being implemented in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which can be a special purpose or general purpose programmable processor that can receive data and instructions from a storage system, at least one input device, and at least one output device, and transmit data and instructions to the storage system, the at least one input device, and the at least one output device.

[0095] Computer programs for implementing the methods of the present invention may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, so that when the computer program is executed by the processor, the functions / operations specified in the flow chart and / or block diagram are implemented. The computer program may be executed entirely on the machine, partially on the machine, partially on the machine and partially on a remote machine as a stand-alone software package, or entirely on a remote machine or server.

[0096] In the context of the present invention, a computer-readable storage medium may be a tangible medium that may contain or store a computer program for use by or in combination with an instruction execution system, device or equipment. A computer-readable storage medium may include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, devices or equipment, or any suitable combination of the foregoing. Alternatively, a computer-readable storage medium may be a machine-readable signal medium. A more specific example of a machine-readable storage medium may include an electrical connection based on one or more lines, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.

[0097] To provide interaction with a user, the systems and techniques described herein may be implemented on an electronic device having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and a pointing device (e.g., a mouse or trackball) through which the user can provide input to the electronic device. Other types of devices may also be used to provide interaction with the user; for example, the feedback provided to the user may be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user may be received in any form (including acoustic input, voice input, or tactile input).

[0098] The systems and techniques described herein may be implemented in a computing system that includes backend components (e.g., as a data server), or a computing system that includes middleware components (e.g., an application server), or a computing system that includes frontend components (e.g., a user computer with a graphical user interface or a web browser through which a user can interact with implementations of the systems and techniques described herein), or a computing system that includes any combination of such backend components, middleware components, or frontend components. The components of the system may be interconnected by any form or medium of digital data communication (e.g., a communication network). Examples of communication networks include: a local area network (LAN), a wide area network (WAN), a blockchain network, and the Internet.

[0099] A computing system may include a client and a server. The client and the server are generally remote from each other and usually interact through a communication network. The client and server relationship is generated by computer programs running on the corresponding computers and having a client-server relationship with each other. The server may be a cloud server, also known as a cloud computing server or cloud host, which is a host product in the cloud computing service system to solve the defects of difficult management and weak business scalability in traditional physical hosts and VPS services.

[0100] Embodiment 5

[0101] An embodiment of the present invention further provides a computer program product, including a computer program, which, when executed by a processor, implements the incremental synchronization method of the database table structure provided in any embodiment of the present application.

[0102] In the process of implementation, the computer program product can be written in one or more programming languages ​​or a combination thereof to perform the computer program code of the present invention, including object-oriented programming languages, such as Java, Smalltalk, C++, and conventional procedural programming languages, such as "C" language or similar programming languages. The program code can be executed entirely on the user's computer, partially on the user's computer, as a separate software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the case of a remote computer, the remote computer can be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or can be connected to an external computer (for example, using an Internet service provider to connect through the Internet).

[0103] It should be noted that in the embodiments of the present application, certain software, components, models and other existing solutions in the industry may be mentioned, and they should be regarded as exemplary. Their purpose is only to illustrate the feasibility of implementing the technical solution of the present application, but it does not mean that the applicant has or will necessarily use the solution.

[0104] Note that the above are only preferred embodiments of the present invention and the technical principles used. Those skilled in the art will understand that the present invention is not limited to the specific embodiments herein, and that various obvious changes, readjustments and substitutions can be made by those skilled in the art without departing from the scope of protection of the present invention. Therefore, although the present invention has been described in more detail through the above embodiments, the present invention is not limited to the above embodiments, and may include more other equivalent embodiments without departing from the concept of the present invention, and the scope of the present invention is determined by the scope of the appended claims.

Claims

1. A method for incremental synchronization of a database table structure, characterized in that: include: Create a data definition language (DDL) operation record table and trigger in the source database; Acquire the DDL statement that causes the source database structure change through the trigger, and add the metadata of the DDL statement to the DDL operation record table; Generate a write-ahead log according to the add operation of the DDL operation record table, and write the write-ahead log into the logical replication slot, wherein the write-ahead log includes a data manipulation language DML statement; When a log read request sent by a client through log monitoring is received, the write-ahead log is sent to the client, so that the client restores the DDL statement according to the write-ahead log and sends it to the target database, and the target database keeps incremental synchronization of the table structure with the source database.

2. The method according to claim 1, characterized in that The step of creating a data definition language DDL operation record table and a trigger in the source database includes: Receive a DDL operation record table creation instruction, and create the DDL operation record table in the source database according to the DDL operation record table creation instruction, wherein the creation instruction includes an operation type field and an operation table name field; The trigger logic of the trigger is defined according to the received trigger definition instruction, and the defined trigger is added to the source database.

3. The method according to claim 2, characterized in that The obtaining of the DDL statement causing the source database structure change through the trigger includes: When the structural change of the source database conforms to the trigger logic, the trigger is activated; The DDL statement causing the structure change of the source database is identified through the trigger, and the DDL statement is captured to obtain the DDL statement.

4. The method according to claim 2, characterized in that: The step of adding the metadata of the DDL statement to the DDL operation record table includes: Extracting field values ​​in the DDL statement that match fields in the DDL operation record table; The field value is added as the metadata to the DDL operation record table.

5. The method according to claim 1, characterized in that The step of generating a write-ahead log according to the adding operation of the DDL operation record table and before writing the write-ahead log into the logical replication slot further includes: Using the source database as a master node and the client as a slave node of the source database; The logical replication slot is established on the master node, and a streaming replication connection between the slave node and the master node is established based on the logical replication slot.

6. The method according to claim 5, characterized in that The step of generating a write-ahead log according to the adding operation of the DDL operation record table and writing the write-ahead log into the logical replication slot includes: Convert each add operation of the DDL operation record table to generate the DML statement matching the add operation; Obtaining the conversion time of the DML statement, and adding the conversion time as a tag to the DML statement; The write-ahead log is generated according to the DML statement with the tag added, and the write-ahead log is written into the logical replication slot, so that the client monitors the log of the logical replication slot through the streaming replication connection.

7. The method according to claim 6, characterized in that After generating a write-ahead log according to the adding operation of the DDL operation record table and writing the write-ahead log into the logical replication slot, the method further includes: Detecting the write-ahead log to obtain abnormal DML statements with faults; The conversion time label added to the abnormal DML statement is obtained, and the abnormal operation of the source database structure change is obtained based on the conversion time label, and the source database is repaired based on the abnormal operation.

8. An incremental synchronization device for a database table structure, characterized in that: The device comprises: DDL operation record table creation module, used to create a data definition language DDL operation record table and trigger in the source database; Adding a DDL operation record table module, used to obtain the DDL statement that causes the source database structure change through the trigger, and add the metadata of the DDL statement to the DDL operation record table; A write-ahead log writing module, used to generate a write-ahead log according to the adding operation of the DDL operation record table, and write the write-ahead log into the logical replication slot, wherein the write-ahead log includes a data manipulation language DML statement; The log monitoring module is used to send the write-ahead log to the client when receiving a log reading request sent by the client through log monitoring, so that the client can restore the DDL statement according to the write-ahead log and send it to the target database, and keep the target database and the source database in incremental synchronization of table structure.

9. An electronic device, characterized in that: The electronic device comprises: at least one processor; and a memory communicatively connected to the at least one processor; wherein, The memory stores a computer program executable by the at least one processor, and the computer program is executed by the at least one processor so that the at least one processor can perform the method according to any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer instructions, and the computer instructions are used to enable a processor to implement the method according to any one of claims 1 to 7 when executed.

11. A computer program product, characterized in that The invention comprises a computer program, which implements the method according to any one of claims 1 to 7 when being executed by a processor.

Images