CN111241094B - Database deleted column synchronization method and device based on log analysis - Google Patents

Database deleted column synchronization method and device based on log analysis Download PDF

Info

Publication number
CN111241094B
CN111241094B CN201911421032.8A CN201911421032A CN111241094B CN 111241094 B CN111241094 B CN 111241094B CN 201911421032 A CN201911421032 A CN 201911421032A CN 111241094 B CN111241094 B CN 111241094B
Authority
CN
China
Prior art keywords
database
log
column
synchronization
destination
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Active
Application number
CN201911421032.8A
Other languages
Chinese (zh)
Other versions
CN111241094A (en
Inventor
付铨
孙峰
彭青松
余院兰
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Wuhan Dream Database Co ltd
Original Assignee
Wuhan Dameng Database Co Ltd
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by Wuhan Dameng Database Co Ltd filed Critical Wuhan Dameng Database Co Ltd
Priority to CN201911421032.8A priority Critical patent/CN111241094B/en
Publication of CN111241094A publication Critical patent/CN111241094A/en
Application granted granted Critical
Publication of CN111241094B publication Critical patent/CN111241094B/en
Active legal-status Critical Current
Anticipated expiration legal-status Critical

Links

Images

Classifications

    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/22—Indexing; Data structures therefor; Storage structures
    • G06F16/2282—Tablespace storage structures; Management thereof
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/10—File systems; File servers
    • G06F16/18—File system types
    • G06F16/1805—Append-only file systems, e.g. using logs or journals to store data
    • G06F16/1815—Journaling file systems
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/27—Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor

Abstract

The invention relates to the technical field of databases, and provides a database deleted column synchronization method and device based on log analysis. The method comprises the steps of judging that the DDL affair is a column deleting operation when the collected log content corresponding to the DDL affair is a user table INSERT operation, and constructing a table establishing operation according to the column information of a table to which the column deleting operation belongs and sending the table establishing operation to a destination end; constructing a query insertion operation, sending the query insertion operation to a target end synchronous service execution, and copying data in a corresponding original table of a target end database to a new table of the target end database; and the source end database synchronization system continues to capture the log until the submitted log is captured, so that the synchronization system of the source end database informs the synchronization system of the destination end database to delete the original table, and the DDL transaction synchronization is completed. The invention can basically ensure that the source database and the destination database delete the list at almost the same time.

Description

Database deleted column synchronization method and device based on log analysis
Technical Field
The invention relates to the technical field of databases, in particular to a database deleted column synchronization method and device based on log analysis.
Background
In a database real-time synchronization system (supporting heterogeneous) based on a log resolution architecture, deletion column operation synchronization of table modification tends to slow down the performance of synchronization. Mainly, in an actual application system, the data volume of some tables is too large, if the table structure is modified on the table with a large number of records, such as deleting a certain column, the table structure of the database can be reconstructed, and due to the large number of records of the tables, the database consumes a large amount of time and generates a large amount of database logs during reconstruction.
In the traditional synchronization method, a source end log analysis service needs to capture a submission log of a source end database table modification operation and then begin to analyze and synchronize the table modification operation, before the operation submission log is captured, a large amount of logs generated by the operation need to be collected and cached, the caching operation can generate a large amount of IO (input/output) to influence the performance of a source end database, the table modification operation is sent to a target end, the database of the target end can be completed by consuming a large amount of time when the modification operation is executed, and during the period, when the data synchronization service synchronizes the subsequent operation of the table, the data synchronization service can be performed only after the target end database completes the table modification, so that great synchronization delay is caused.
For example, if an a-table deletion of 1 hundred million rows of records in the source database takes approximately 60 minutes to execute in the source database, and if synchronization to the destination database also takes at least 60 minutes to complete, then data synchronization is delayed by at least 60 minutes.
In an environment of building main-standby synchronization, a target-end database is often used as a query library to share the pressure of the main library, when a table deletes a column, query operation for the table is blocked, long-time table modification operation of the target-end database affects the query function of an application, and how to shorten synchronization delay of large table modification operation and reduce the influence of synchronization on an application program on the target-end database becomes a technical problem to be solved in the industry.
Disclosure of Invention
The technical problem to be solved by the present invention is that in an environment where a main-standby synchronization is established, a target-side database is often used as a query library to share the pressure of the main library, when a table deletes a column, query operation for the table will be blocked, long-time table modification operation of the target-side database will affect the query function of an application, and how to shorten synchronization delay of large table modification operation and reduce the influence of synchronization on an application program on the target-side database becomes a technical problem to be solved in the industry.
The invention adopts the following technical scheme:
in a first aspect, the present invention provides a database deletion column synchronization method based on log parsing, including:
a synchronization system of a source end database judges that an operation to be synchronized in a log is a DDL transaction, and then collects the subsequent log belonging to the DDL transaction according to the transaction number;
when the collected log content corresponding to the DDL transaction is the user table INSERT operation, judging that the DDL transaction is a column deleting operation, and constructing a table building operation according to the column information of a table to which the column deleting operation belongs and sending the table building operation to a destination end synchronous system for execution; the table building operation comprises building a table name of a new table and building a mapping relation with an original table name, and column information of the new table is set by referring to column information corresponding to the original table after column deletion operation is completed;
constructing a query insertion operation and sending the query insertion operation to a target end synchronous system for execution, and copying data in a corresponding original table of a target end database to a new table of the target end database;
and the source end database synchronization system continues to capture the log until the submission log of the DDL transaction is captured, so that the synchronization system of the source end database informs the synchronization system of the destination end database to delete the original table, and the table name of the newly built table is replaced by the table name of the original table in the destination end database, thereby completing the DDL transaction synchronization.
Preferably, the method for synchronizing a DDL transaction in a log includes that a synchronization system of a source database determines that an operation to be synchronized in the log is a DDL transaction, and then collects subsequent logs belonging to the DDL transaction according to the transaction number, and further includes:
when the log content corresponding to the DDL transaction is collected firstly, the log content is not the user table INSERT operation but the log is submitted, the DDL transaction is judged to be the operation of deleting the column, and the operation of deleting the column is sent to a destination end synchronization system.
Preferably, after receiving the column deletion operation of the synchronization system of the source database, the synchronization system of the destination database directly executes the column deletion operation on the newly-created table on the destination database, thereby completing the DDL transaction synchronization.
Preferably, before the DDL, if a query request is received by the destination database, the method further includes:
the original table of the destination database is locked so as to be in an inaccessible state; and after the DDL things are synchronized, the query request is completed through the new table replacing the original table.
Preferably, the source database synchronization system continues to capture the log, further comprising:
when the rollback log of the DDL transaction is captured, the synchronization system of the source database constructs and deletes the operation of newly building the table of the destination and sends the operation to the destination for execution, and the DDL transaction synchronization is completed.
Preferably, before completing the DDL transaction synchronization, if the destination receives a query request, the method further includes:
and the destination database provides the original table as a queried object of the query request, wherein the query request process does not influence the process of copying the original table data into the newly built table.
Preferably, when the collected log content corresponding to the DDL transaction is a user table INSERT operation, determining whether the DDL transaction is a column deletion operation, specifically including:
aiming at DDL affairs, a source end database generates a log of a system table and then generates a log corresponding to a common table;
after the log of the system table is collected, the operation to which the DDL transaction belongs is reversely deduced through the log of the system table;
the condition for collecting the log of the system table is as follows: generating a commit or rollback message for the DDL transaction, and/or generating a log of a common table corresponding to the DDL transaction.
Preferably, the database comprises an Oracle and/or damming database.
Preferably, in the method, synchronization systems are deployed in the source end database and the destination end database, the source end database synchronization system reads logs from the source end database, and the destination end database synchronization system is responsible for applying synchronization operations sent by the source end to the destination end database.
In a second aspect, the present invention further provides a database deleted column synchronization apparatus based on log parsing, which is used to implement the database deleted column synchronization method based on log parsing in the first aspect, and the apparatus includes:
at least one processor; and a memory communicatively coupled to the at least one processor; wherein the memory stores instructions executable by the at least one processor for performing the log parsing based database delete column synchronization method of the first aspect.
In a third aspect, the present invention further provides a non-volatile computer storage medium, where the computer storage medium stores computer-executable instructions, which are executed by one or more processors, for performing the log parsing-based database deletion column synchronization method according to the first aspect.
The invention captures the user table operation log corresponding to the DDL things as the basis for triggering the target end to carry out new table operation, further deletes the original table of the target end database only when capturing the submission log or rollback log of the affair, and replaces the new table name with the deleted original table, thereby basically ensuring that the original and target databases carry out the operation of deleting columns at almost the same time.
Drawings
In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings required to be used in the embodiments of the present invention will be briefly described below. It is obvious that the drawings described below are only some embodiments of the invention, and that for a person skilled in the art, other drawings can be derived from them without inventive effort.
Fig. 1 is a schematic flowchart of a database deletion column synchronization method based on log parsing according to an embodiment of the present invention;
FIG. 2 is a flowchart illustrating a more complicated database deletion column synchronization method based on log parsing according to an embodiment of the present invention;
FIG. 3 is a schematic flowchart of a complicated database deletion column synchronization method based on log parsing according to an embodiment of the present invention;
FIG. 4 is a flowchart of a method for analyzing operations of DDL things according to an embodiment of the present invention;
FIG. 5 is a flowchart illustrating another database deletion column synchronization method based on log parsing according to an embodiment of the present invention;
fig. 6 is a schematic flowchart of a database deleted column synchronization method based on log parsing in an SQL scenario according to an embodiment of the present invention;
fig. 7 is a schematic structural diagram of a database deletion column synchronization apparatus based on log parsing according to an embodiment of the present invention.
Detailed Description
In order to make the objects, technical solutions and advantages of the present invention more apparent, the present invention is described in further detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of the invention and are not intended to limit the invention.
In the description of the present invention, the terms "inner", "outer", "longitudinal", "lateral", "upper", "lower", "top", "bottom", and the like, refer to an orientation or positional relationship based on that shown in the drawings, which is for convenience of description only and does not require that the present invention be constructed and operated in a particular orientation, and therefore, should not be construed as limiting the present invention.
In addition, the technical features involved in the embodiments of the present invention described below may be combined with each other as long as they do not conflict with each other.
Example 1:
the embodiment 1 of the invention provides a database deleted column synchronization method based on log analysis, wherein the database comprises an Oracle database and/or a Damomeng database. As shown in fig. 1, includes:
in step 201, the synchronization system of the source database determines that the operation to be synchronized in the log is a DDL transaction, and then collects subsequent logs belonging to the DDL transaction according to the transaction number.
Aiming at DDL affairs, a source end database generates a log of a system table and then generates a log corresponding to a common table; and step 201 of the embodiment of the present invention mainly refers to collecting the log of the system table.
In step 202, when the collected log content corresponding to the DDL transaction is a user table INSERT operation (when the table is in a modified state, for example, a delete column in the embodiment of the present invention, the table is locked inside the database, other transactions cannot operate the table, and only after the DDL is completed, the table can be accessed by other transactions, but the INSERT operation is a boundary ridge and belongs to the DDL operation before the INSERT operation), it is determined that the DDL transaction is an operation of deleting the column, and a table creating operation is constructed according to the column information of the table to which the delete column operation belongs, and sent to the destination synchronization system for execution; the table building operation comprises building a mapping relation between a table name of a new table and an original table name, and column information of the new table is set by referring to column information corresponding to the original table after the column deleting operation is completed.
In the prior art, it is common that the source database sends the existing deleted column operation to the destination database after the deleted column operation is completed, which is why "the operation takes about 60 minutes to execute on the source database, and the operation takes at least 60 minutes to complete when synchronized to the destination database, which causes a delay of at least 60 minutes. In the embodiment of the present invention, it is different that once the operation of deleting the column is collected through the log (at this time, the operation process of deleting the column is just started for the source), a table building operation is sent to the synchronization system of the destination database, where the operation is actually performed after delaying for 60 minutes (taking about 60 minutes from the source database execution) as exemplified in the background art, and is prepared to be completed in advance.
The practical significance of the improvement of the steps in the embodiment of the present invention is that in specific applications, once the deletion column operation is executed in the source database, the probability of more than 99% of the deletion column operation is normally completed except for the respective reasons of downtime, network interruption, etc. Because of the objective factors for stably completing, it is realized that the above-mentioned "when the collected log content corresponding to the DDL transaction is the user table INSERT operation, the DDL transaction is judged to be the operation of deleting the column, and according to the column information of the table to which the operation of deleting the column belongs, a table building operation is constructed and sent to the destination synchronization system to be executed" becomes an effective and feasible operation mode, thereby becoming the root place capable of finally obtaining "the operation of deleting the column from the table at almost the same time in the source and destination databases can be basically ensured".
In step 203, a query insertion operation is constructed and sent to the destination synchronization system for execution, and the data in the corresponding original table of the destination database is copied to the new table of the destination database.
It should be emphasized that the operation of step 203 does not lock the original table in the destination database as in the prior art, and therefore, the query operation on the original table can be performed normally while step 203 is executed. Since the synchronization operation between the source-side database and the destination-side database is performed by the synchronization systems on both sides, and the synchronization system is an independent layer with respect to the database itself, it can be understood that the synchronization system is a control subject that sends a specified DML operation to the database, and therefore, there is a space for implementing the improvement steps proposed in the above embodiment of the present invention.
In step 204, the source database synchronization system continues to capture the log until capturing the commit log of the DDL transaction, so the synchronization system of the source database notifies the synchronization system of the destination database to delete the original table, and replaces the table name of the newly created table with the table name of the original table in the destination database, thereby completing the DDL transaction synchronization.
Before completing the DDL transaction synchronization, if a query request is received by the destination, the method further comprises: and the destination database provides the original table as a queried object of the query request, wherein the query request process does not influence the process of copying the original table data into the newly built table.
The embodiment of the invention captures the user table operation log corresponding to the DDL things as the basis for triggering the target end to carry out new table operation, further deletes the original table of the target end database only when capturing the submission log or rollback log of the affair, and replaces the new table name with the deleted original table, thereby basically ensuring that the source and the target database almost carry out column deletion operation at the same time.
As shown in fig. 2, after the synchronization system of the source database in step 201 determines that the operation to be synchronized in the log is a DDL transaction, and collects subsequent logs belonging to the DDL transaction according to the transaction number, in addition to the above-mentioned case in step 202, another case may be encountered, specifically:
in step 205, when the log content corresponding to the DDL transaction is collected first, instead of the user table INSERT operation, but a commit log, it is determined that the DDL transaction is a delete column operation, and then a delete column operation is sent to the destination synchronization system. Here, it is considered that if the commit log in step 205 is collected instead of the INSERT operation in step 202, it can be determined that the original table on which the delete column operation needs to be performed is an empty table, and there is no delay described in the background art by using the conventional mechanism, because the creation of the empty table can be completed quickly by using the conventional database mechanism, and at this time, there is some redundancy by using the method process of step 203 and 204 proposed in the embodiment of the present invention; therefore, in this case, synchronization can be performed directly using the delete column operation instruction between the two-side synchronization systems.
Further, from the destination database, after receiving the delete column operation from the source database in step 205, the synchronization system of the destination database directly executes the delete column operation on the new table on the destination database after receiving the delete column operation of the synchronization system of the source database, thereby completing the DDL transaction synchronization. And on the destination database, the process of executing the column deletion operation comprises the following steps: locking the original table of the target end database, creating a table name of a newly created table and establishing a mapping relation with the original table name, keeping the column information of the newly created table consistent with the column information of the corresponding original table after the column deleting operation is finished, and deleting the original table of the target end database after the new created table is established
In this embodiment of the present invention, before completing the DDL transaction synchronization, if a query request is received by a destination database, the method further includes:
the original table of the destination database is locked so as to be in an inaccessible state; and after the DDL things are synchronized, the query request is completed through the new table replacing the original table.
As shown in fig. 3, the source-side database synchronization system continues to capture logs, and includes the case of rolling back logs in addition to the commit log described in step 204, specifically:
in step 206, when the rollback log of the DDL transaction is captured, the synchronization system of the source database constructs an operation of deleting the newly-built table of the destination and sends the operation to the destination for execution, thereby completing the DDL transaction synchronization.
In this embodiment of the present invention, when the collected log content corresponding to the DDL transaction is a user table INSERT operation, determining whether the DDL transaction is a column deletion operation, as shown in fig. 4, specifically includes:
in step 301, for a DDL transaction, the source database generates a log of a system table and then generates a log corresponding to a common table.
In step 302, after the log of the system table is collected, the operation to which the DDL transaction belongs is reversely deduced through the log of the system table; for example, in embodiment 1 of the present invention, the inverse push out of the INSERT operation log by the user table is a delete column operation. Col analysis determines that a column in the table is less defined by one column, and thus determines a delete column operation, for example, as described in example 3.
The condition for collecting the log of the system table is as follows: generating a commit or rollback message for the DDL transaction, and/or generating a log of a common table corresponding to the DDL transaction.
Example 2:
the embodiment of the present invention describes the implementation process of embodiment 1 of the present invention from a relatively complete method step process, and compared with embodiment 1, the process of the embodiment of the present invention is more complete in performance and more instantiated. In the embodiment of the present invention, synchronization systems are deployed in a source database and a destination database, the source database synchronization system reads a log from the source database, and the destination database synchronization system is responsible for applying a synchronization operation sent by a source to the destination database, as shown in fig. 5, including the following steps:
in step 401, the source database synchronization system determines whether the operation in the log belongs to a system table operation, if so, the operation is classified as a DDL transaction, obtains a transaction number of the DDL operation, and collects subsequent logs belonging to the transaction according to the transaction number.
When the commit log of the above-described DDL transaction is collected, the delete column is synchronized in step 402 using conventional means. The conventional manner described herein may be a delete column synchronization mechanism provided by each platform itself, which is not described herein for brevity.
In step 403, when the user table INSERT operation log related to the DDL transaction is collected, it needs to determine whether the DDL transaction is a column deletion operation, and according to the column information of the table to which the column deletion operation belongs, a table creation operation is constructed and sent to the destination for synchronous service execution, so as to ensure that the table name a _ BAK of the table creation is different from the original table name a, but the column information is consistent with the original table.
In step 404, a query insertion operation is constructed and sent to the destination synchronous system for execution, and the data in the A table is copied to the A _ BAK table.
In step 405, the source-side database synchronization system continues to capture logs, and subsequent DML logs belonging to the delete column transaction may be discarded directly until a commit or rollback log of the transaction is captured.
When the rollback log of the delete column is captured, in step 406, the operation to construct the delete table a _ BAK is sent to the destination for execution, since the destination has already performed the create table a _ BAK operation, completing the transaction synchronization.
In step 407, when the commit log of the deleted column is captured, the destination synchronization service is notified to perform a renaming operation on the newly created table, the destination synchronization service deletes the original table a, and then renames the new table a _ BAK to a, thereby completing the transaction synchronization.
When the scheme is operated, the operation of deleting the columns of the table can be basically ensured to be carried out by the source database and the target database at the same time, and the target end is carried out in a mode of reserving the original table to create a new table when deleting the columns, and the query operation of the original table is not influenced by the synchronization of the column deleting operation, so that the delay caused by the synchronization of the column deleting operation is shortened, and the influence of the synchronization of the column deleting operation on the application of the target end is reduced.
Example 3:
in the embodiment of the present invention, when the source database is ORACLE, the method content in the above embodiment 2 is further developed and instantiated as follows:
all object information of the source database is stored in the related system dictionary tables (such as sys. obj $, sys. col $, sys. icol $), and taking ORACLE as an example, the table definition of the table is stored in the sys. obj $ system table, and the column definition is placed in the sys. col system $ table. When ORACLE deletes the column C from the table a, it deletes a row definition of the table in the sys.col $ system table first, and the synchronization service can acquire definition information of the column C to be deleted in the log by capturing the column deletion operation of the sys.col $ system table; after the database constructs the dictionary of the A table, the database constructs the data recorded in the A table, the operation generates a large amount of INSERT logs aiming at the A table in the logs, the synchronization service can know that the log operation of the system table is finished when acquiring the insertion operation of the A table, and the original deletion column operation can be restored according to the collected system table operation.
Examples are as follows:
the existing table a (C INT, COL INT) of the source database, a, has 1 row of data, and executes delete column operation at the source end:
ALTER TABLE A DROP COLUMN COL;
as shown in fig. 6, the synchronization process is as follows:
in step 501, the source data synchronization service collects a system table operation log for the deleted column.
In step 502, when the INSERT operation log of A is found in the DDL transaction of the deleted COLUMN, the log recovery of the parsing system TABLE obtains the operation ALTER TABLE A DROP COLUMN COL of the deleted COLUMN.
In step 503, the source data synchronization constructs a TABLE build operation to the destination synchronization service, and the TABLE structure uses the deleted column, and the TABLE build SQL is CREATE TABLE a _ bak (c int).
In step 504, the operation of query insertion is constructed by the source data synchronization to send to the destination synchronization system, so as to copy the data of the a table to a _ BAK, and the query insertion SQL is INSERT intra a _ BAK (C) SELECT C FROM a.
In step 505, if the source delete column operation is rolled back, the SQL constructed by the delete TABLE in the above example is DROP TABLE a _ BAK, and needs to be sent to the destination for execution, so as to delete the newly created TABLE a _ BAK.
In step 506, if the source delete column operation commits, the renamed end database a _ BAK needs TO be TABLE a, so a delete TABLE operation needs TO be sent first TO delete the old TABLE a, the SQL TO delete the TABLE is DROP TABLE a, the TABLE a _ BAK is renamed TO a, and the SQL TO RENAME the TABLE is ALTER TABLE a _ BAK restore TO a.
In step 507, synchronization is completed.
The embodiment of the invention captures the user table operation log corresponding to the DDL things as the basis for triggering the target end to carry out new table operation, further deletes the original table of the target end database only when capturing the submission log or rollback log of the affair, and replaces the new table name with the deleted original table, thereby basically ensuring that the source and the target database almost carry out column deletion operation at the same time.
Example 4:
fig. 7 is a schematic diagram illustrating an architecture of a database deleted column synchronization apparatus based on log parsing according to an embodiment of the present invention. The database deletion column synchronization device based on log parsing of the present embodiment includes one or more processors 21 and a memory 22. In fig. 7, one processor 21 is taken as an example.
The processor 21 and the memory 22 may be connected by a bus or other means, and fig. 7 illustrates the connection by a bus as an example.
The memory 22, which is a non-volatile computer-readable storage medium, may be used to store a non-volatile software program and a non-volatile computer-executable program, such as the database deletion column synchronization method based on log parsing in embodiment 1. The processor 21 executes the log resolution based database delete column synchronization method by executing non-volatile software programs and instructions stored in the memory 22.
The memory 22 may include high speed random access memory and may also include non-volatile memory, such as at least one magnetic disk storage device, flash memory device, or other non-volatile solid state storage device. In some embodiments, the memory 22 may optionally include memory located remotely from the processor 21, and these remote memories may be connected to the processor 21 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 program instructions/modules are stored in the memory 22 and, when executed by the one or more processors 21, perform the log parsing-based database deletion column synchronization method of embodiment 1 described above, for example, perform the steps shown in fig. 1 to 6 described above.
It should be noted that, for the information interaction, execution process and other contents between the modules and units in the apparatus and system, the specific contents may refer to the description in the embodiment of the method of the present invention because the same concept is used as the embodiment of the processing method of the present invention, and are not described herein again.
Those of ordinary skill in the art will appreciate that all or part of the steps of the various methods of the embodiments may be implemented by associated hardware as instructed by a program, which may be stored on a computer-readable storage medium, which may include: read Only Memory (ROM), Random Access Memory (RAM), magnetic or optical disks, and the like.
The above description is only for the purpose of illustrating the preferred embodiments of the present invention and is not to be construed as limiting the invention, and any modifications, equivalents and improvements made within the spirit and principle of the present invention are intended to be included within the scope of the present invention.

Claims (9)

1. A database deletion column synchronization method based on log analysis is characterized by comprising the following steps:
a synchronization system of a source end database judges that an operation to be synchronized in a log is a DDL transaction, and then collects the subsequent log belonging to the DDL transaction according to the transaction number of the operation to be synchronized as the DDL transaction;
when the collected log content corresponding to the DDL transaction is the user table INSERT operation, judging that the DDL transaction is a column deleting operation, and constructing a table building operation according to the column information of a table to which the column deleting operation belongs and sending the table building operation to a destination end synchronous system for execution; the table building operation comprises the steps of building a mapping relation between a table name of a new table and an original table name, and column information of the new table is set by referring to column information corresponding to the original table after column deletion operation is completed;
constructing a query insertion operation and sending the query insertion operation to a target end synchronous system for execution, and copying data in a corresponding original table of a target end database to a new table of the target end database;
the source end database synchronization system continues to capture the log until the submitted log of the DDL transaction is captured, so that the synchronization system of the source end database informs the synchronization system of the destination end database to delete the original table, and the table name of the newly built table is replaced by the table name of the original table in the destination end database, thereby completing the DDL transaction synchronization;
when the collected log content corresponding to the DDL transaction is an INSERT operation of the user table, determining whether the DDL transaction is a column deletion operation, specifically including:
aiming at DDL affairs, a source end database generates a log of a system table and then generates a log corresponding to a common table;
after the log of the system table is collected, the operation to which the DDL transaction belongs is reversely deduced through the log of the system table;
the synchronization system of the source database determines that the definition of columns in the table is less by one column through analyzing a system table log SYS.COL, thereby determining that the operation is a column deletion operation;
the condition for collecting the log of the system table is as follows: generating a commit or rollback message for the DDL transaction, and/or generating a log of a common table corresponding to the DDL transaction.
2. The method according to claim 1, wherein the synchronization system of the source database determines that the operation to be synchronized in the log is a DDL transaction, and collects subsequent logs belonging to the DDL transaction according to the transaction number of the DDL transaction, and further comprises:
when the log content corresponding to the DDL transaction is collected firstly, the log content is not the user table INSERT operation but the log is submitted, the DDL transaction is judged to be the operation of deleting the column, and the operation of deleting the column is sent to a destination end synchronization system.
3. The method as claimed in claim 2, wherein after receiving the delete column operation of the synchronization system of the source database, the synchronization system of the destination database directly executes delete column operation on the new table on the destination database, thereby completing the DDL transaction synchronization.
4. The log resolution-based database deletion column synchronization method as claimed in claim 2, wherein before the DDL transaction is synchronized, if a query request is received, the method further comprises:
the original table of the destination database is locked so as to be in an inaccessible state; and after the DDL transaction synchronization is completed, the query request is completed by replacing the newly-built table of the original table.
5. The log resolution-based database deletion column synchronization method of claim 1, wherein the source-side database synchronization system continues to capture logs, further comprising:
when the rollback log of the DDL transaction is captured, the synchronization system of the source database constructs and deletes the operation of newly building the table of the destination and sends the operation to the destination for execution, and the DDL transaction synchronization is completed.
6. The log resolution-based database deletion column synchronization method according to claim 1, wherein before the destination completes the DDL transaction synchronization, if a query request is received, the method further comprises:
and the destination database provides the original table as a queried object of the query request, wherein the query request process does not influence the process of copying the original table data into the newly built table.
7. The log resolution based database delete column synchronization method of any of claims 1-6, wherein said database comprises an Oracle and/or damming database.
8. The database deletion column synchronization method based on log parsing of any one of claims 1-6, wherein synchronization systems are deployed in a source database and a destination database, the source database synchronization system reads logs from the source database, and the destination database synchronization system is responsible for applying synchronization operations sent from the source to the destination database.
9. An apparatus for deleting columns from a database based on log parsing, the apparatus comprising:
at least one processor; and a memory communicatively coupled to the at least one processor; wherein the memory stores instructions executable by the at least one processor to perform the log resolution based database delete column synchronization method of any of claims 1-8.
CN201911421032.8A 2019-12-31 2019-12-31 Database deleted column synchronization method and device based on log analysis Active CN111241094B (en)

Priority Applications (1)

Application Number Priority Date Filing Date Title
CN201911421032.8A CN111241094B (en) 2019-12-31 2019-12-31 Database deleted column synchronization method and device based on log analysis

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
CN201911421032.8A CN111241094B (en) 2019-12-31 2019-12-31 Database deleted column synchronization method and device based on log analysis

Publications (2)

Publication Number Publication Date
CN111241094A CN111241094A (en) 2020-06-05
CN111241094B true CN111241094B (en) 2021-06-08

Family

ID=70879739

Family Applications (1)

Application Number Title Priority Date Filing Date
CN201911421032.8A Active CN111241094B (en) 2019-12-31 2019-12-31 Database deleted column synchronization method and device based on log analysis

Country Status (1)

Country Link
CN (1) CN111241094B (en)

Families Citing this family (4)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN112433992B (en) * 2020-11-16 2023-06-02 武汉船舶通信研究所（中国船舶重工集团公司第七二二研究所） Data synchronization log optimization method
CN112559626B (en) * 2020-12-11 2022-06-21 武汉达梦数据库股份有限公司 Synchronous method and synchronous system of DDL operation based on log analysis
CN112765180B (en) * 2021-01-27 2023-01-17 上海英方软件股份有限公司 Method and device for analyzing column names of table building logs of DB2 database
CN116804994B (en) * 2023-08-23 2023-11-14 北京逐风科技有限公司 Data synchronization method, system, device, electronic equipment and storage medium

Citations (10)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN105574187A (en) * 2015-12-23 2016-05-11 武汉达梦数据库有限公司 Duplication transaction consistency guaranteeing method and system for heterogeneous databases
CN105589961A (en) * 2015-12-21 2016-05-18 武汉达梦数据库有限公司 Method and system for detecting data consistency of real-time synchronous system of database
CN108351900A (en) * 2015-10-07 2018-07-31 甲骨文国际公司 Relational database tissue for fragment
CN108920698A (en) * 2018-07-16 2018-11-30 北京京东金融科技控股有限公司 A kind of method of data synchronization, device, system, medium and electronic equipment
CN109189852A (en) * 2018-08-01 2019-01-11 武汉达梦数据库有限公司 A kind of method that data are synchronous and the device synchronous for data
CN109241185A (en) * 2018-08-27 2019-01-18 武汉达梦数据库有限公司 A kind of method and data synchronization unit that data are synchronous
CN109558452A (en) * 2018-11-19 2019-04-02 武汉达梦数据库有限公司 It is a kind of to inquire the synchronous method for building table handling
CN109614443A (en) * 2018-11-06 2019-04-12 武汉达梦数据库有限公司 For the DDL synchronous method and equipment of V9.7 version d B2 database
CN109656934A (en) * 2018-11-19 2019-04-19 武汉达梦数据库有限公司 Source oracle database DDL synchronous method and equipment based on log parsing
CN110232093A (en) * 2019-04-30 2019-09-13 武汉达梦数据库有限公司 Initialization stowage and equipment in database synchronization based on flashback query

Family Cites Families (2)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US10303702B2 (en) * 2014-02-07 2019-05-28 Ignite Scalarc Solutions, Inc. System and method for analysis and management of data distribution in a distributed database environment
US10268743B2 (en) * 2015-06-19 2019-04-23 Sap Se Distributed database transaction protocol

Patent Citations (10)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN108351900A (en) * 2015-10-07 2018-07-31 甲骨文国际公司 Relational database tissue for fragment
CN105589961A (en) * 2015-12-21 2016-05-18 武汉达梦数据库有限公司 Method and system for detecting data consistency of real-time synchronous system of database
CN105574187A (en) * 2015-12-23 2016-05-11 武汉达梦数据库有限公司 Duplication transaction consistency guaranteeing method and system for heterogeneous databases
CN108920698A (en) * 2018-07-16 2018-11-30 北京京东金融科技控股有限公司 A kind of method of data synchronization, device, system, medium and electronic equipment
CN109189852A (en) * 2018-08-01 2019-01-11 武汉达梦数据库有限公司 A kind of method that data are synchronous and the device synchronous for data
CN109241185A (en) * 2018-08-27 2019-01-18 武汉达梦数据库有限公司 A kind of method and data synchronization unit that data are synchronous
CN109614443A (en) * 2018-11-06 2019-04-12 武汉达梦数据库有限公司 For the DDL synchronous method and equipment of V9.7 version d B2 database
CN109558452A (en) * 2018-11-19 2019-04-02 武汉达梦数据库有限公司 It is a kind of to inquire the synchronous method for building table handling
CN109656934A (en) * 2018-11-19 2019-04-19 武汉达梦数据库有限公司 Source oracle database DDL synchronous method and equipment based on log parsing
CN110232093A (en) * 2019-04-30 2019-09-13 武汉达梦数据库有限公司 Initialization stowage and equipment in database synchronization based on flashback query

Also Published As

Publication number Publication date
CN111241094A (en) 2020-06-05

Similar Documents

Publication Publication Date Title
CN111241094B (en) Database deleted column synchronization method and device based on log analysis
CN111221907B (en) Database added column synchronization method and device based on log analysis
CN109241185B (en) Data synchronization method and data synchronization device
US11704290B2 (en) Methods, devices and systems for maintaining consistency of metadata and data across data centers
CN110262929B (en) Method for ensuring consistency of copying affairs and corresponding copying device
EP3803618B1 (en) Distributed transactions in cloud storage with hierarchical namespace
US10430298B2 (en) Versatile in-memory database recovery using logical log records
CN110196856B (en) Distributed data reading method and device
US20160078044A1 (en) Main-memory database checkpointing
US20170315882A1 (en) Protected write-back cache transaction replication
WO2019231689A1 (en) Multi-protocol cloud storage for big data and analytics
CN103875229A (en) Asynchronous replication method, device and system
US11886298B2 (en) Using a storage log to generate an incremental backup
CN111858501B (en) Log reading method based on log analysis synchronization and data synchronization system
CN111221909B (en) Database modification column synchronization method and device based on log analysis
CN113391885A (en) Distributed transaction processing system
US20200034472A1 (en) Asynchronous cache coherency for mvcc based database systems
CN112559626A (en) Synchronous method and synchronous system of DDL operation based on log analysis
CN111241193B (en) Database added column synchronization method and device based on log analysis
US11061889B2 (en) Systems and methods of managing manifest refresh in a database
CN111858504B (en) Operation merging execution method based on log analysis synchronization and data synchronization system
US9020905B2 (en) Synchronizing database and non-database resources without a commit coordinator
KR20140047448A (en) Client and database server for resumable transaction and method thereof
CN112800060A (en) Data processing method and device, computer readable storage medium and electronic equipment
CN114741453A (en) Method, system and computer readable storage medium for data synchronization

Legal Events

Date Code Title Description
PB01 Publication
PB01 Publication
SE01 Entry into force of request for substantive examination
SE01 Entry into force of request for substantive examination
GR01 Patent grant
GR01 Patent grant
CB03 Change of inventor or designer information
CB03 Change of inventor or designer information

Inventor after: Sun Feng

Inventor after: Peng Qingsong

Inventor after: Yu Yuanlan

Inventor before: Fu Quan

Inventor before: Sun Feng

Inventor before: Peng Qingsong

Inventor before: Yu Yuanlan

CP01 Change in the name or title of a patent holder
CP01 Change in the name or title of a patent holder

Address after: 430000 16-19 / F, building C3, future technology building, 999 Gaoxin Avenue, Donghu New Technology Development Zone, Wuhan, Hubei Province

Patentee after: Wuhan dream database Co.,Ltd.

Address before: 430000 16-19 / F, building C3, future technology building, 999 Gaoxin Avenue, Donghu New Technology Development Zone, Wuhan, Hubei Province

Patentee before: WUHAN DAMENG DATABASE Co.,Ltd.

TR01 Transfer of patent right
TR01 Transfer of patent right

Effective date of registration: 20220907

Address after: 430073 16-19 / F, building C3, future science and technology building, 999 Gaoxin Avenue, Donghu New Technology Development Zone, Wuhan City, Hubei Province

Patentee after: Wuhan dream database Co.,Ltd.

Patentee after: HUAZHONG University OF SCIENCE AND TECHNOLOGY

Address before: 430000 16-19 / F, building C3, future technology building, 999 Gaoxin Avenue, Donghu New Technology Development Zone, Wuhan, Hubei Province

Patentee before: Wuhan dream database Co.,Ltd.

TR01 Transfer of patent right
TR01 Transfer of patent right

Effective date of registration: 20230802

Address after: 16-19/F, Building C3, Future Science and Technology Building, No. 999 Gaoxin Avenue, Donghu New Technology Development Zone, Wuhan City, Hubei Province, 430206

Patentee after: Wuhan dream database Co.,Ltd.

Address before: 430073 16-19 / F, building C3, future science and technology building, 999 Gaoxin Avenue, Donghu New Technology Development Zone, Wuhan City, Hubei Province

Patentee before: Wuhan dream database Co.,Ltd.

Patentee before: HUAZHONG University OF SCIENCE AND TECHNOLOGY