CN117632991B - Method and system for acquiring database change data - Google Patents
Method and system for acquiring database change data Download PDFInfo
- Publication number
- CN117632991B CN117632991B CN202410108868.7A CN202410108868A CN117632991B CN 117632991 B CN117632991 B CN 117632991B CN 202410108868 A CN202410108868 A CN 202410108868A CN 117632991 B CN117632991 B CN 117632991B
- Authority
- CN
- China
- Prior art keywords
- transaction
- log
- database
- information
- message queue
- 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
Links
- 230000008859 change Effects 0.000 title claims abstract description 41
- 238000000034 method Methods 0.000 title claims abstract description 26
- 238000012545 processing Methods 0.000 claims abstract description 15
- 230000008569 process Effects 0.000 claims description 9
- 238000013480 data collection Methods 0.000 claims description 8
- 230000005540 biological transmission Effects 0.000 claims 1
- 230000009286 beneficial effect Effects 0.000 abstract description 2
- 238000012986 modification Methods 0.000 description 5
- 230000004048 modification Effects 0.000 description 5
- 238000012217 deletion Methods 0.000 description 2
- 230000037430 deletion Effects 0.000 description 2
- 239000002253 acid Substances 0.000 description 1
- 238000007792 addition Methods 0.000 description 1
- 238000013461 design Methods 0.000 description 1
- 230000006870 function Effects 0.000 description 1
- 238000002955 isolation Methods 0.000 description 1
- 230000002688 persistence Effects 0.000 description 1
- 230000009467 reduction Effects 0.000 description 1
- 238000005096 rolling process Methods 0.000 description 1
- 238000006467 substitution reaction Methods 0.000 description 1
- 238000012546 transfer Methods 0.000 description 1
Abstract
The invention provides a method and a system for acquiring database change data, comprising the following steps: processing the transaction log by the database, extracting the transaction meta-information, and outputting record change information according to configuration; the log transmitter classifies the records according to the transaction identification and executes corresponding operations according to the transaction type; the data collector reads the committed transaction record from the message queue. The invention has the beneficial effects that: the transaction of record change is ensured for the data collector, the performance problems caused by long transactions and large transactions are solved, the performance and the reliability of the database are greatly improved, and the use experience of the database is improved.
Description
Technical Field
The invention belongs to the technical field of computers, and particularly relates to a method and a system for acquiring database change data.
Background
The collection of database change data is usually realized by using a logical log output mode, and each log is mainly the content of addition, deletion and modification of the records of a database table, so that two requirements are set for the output log:
A log of table records of changes within a transaction must be output after the transaction commits;
As time progresses, multiple parallel transactions within the database commit in some order, so must the log we output. In other words, if the log within a committed transaction is not exported, the log of the transaction committed later cannot be exported.
These two requirements bring about problems:
Particularly when a large number of changes are made within a transaction, in this scenario we need to cache all the changes involved in the transaction in the database until the transaction commits or terminates, which can put a significant strain on the storage of the machine on which the database resides.
In this scenario, if the memory does not allow caching of so much data, it is also necessary to drop these logs to disk. When the transaction is submitted, data is still read from the disk and then output, which not only consumes the computing resources of the system, but also occupies quite long operation time because of the large number of disk operations, blocks the output of the log of the subsequent transaction, causes the reduction of the throughput of the whole data processing system, the increase of the data processing time delay and the jitter of the system performance.
Disclosure of Invention
Accordingly, the present invention is directed to a method and system for database change data collection, which is designed to solve at least one of the above problems.
In order to achieve the above purpose, the technical scheme of the invention is realized as follows:
the first aspect of the present invention provides a method for collecting database modification data, including:
processing the transaction log by the database, extracting the transaction meta-information, and outputting record change information according to configuration;
The log transmitter classifies the records according to the transaction identification and executes corresponding operations according to the transaction type;
the data collector reads the committed transaction record from the message queue.
Further, the process of processing the transaction log by the database, extracting the transaction meta-information and outputting record change information according to the configuration comprises the following steps:
for each transaction log read by the database, the database outputs meta-information of corresponding transactions, including transaction identification, transaction opening, transaction submission, transaction rollback or termination;
and outputting information of record change according to a configuration specified format for each record change log read by the database, wherein the information comprises transaction identification of the log and specific information of adding and deleting the log.
Further, the process of the log transmitter executing the corresponding operation according to the transaction type includes:
if the transaction type is transaction opening information, notifying a downstream message queue to open the transaction;
If the transaction type is the record information, pushing the message to the appointed transaction of the message queue;
If the transaction type is transaction commit or rollback abort information, the message queue will be notified that the transaction committed or aborted.
The second aspect of the present invention proposes a system for database modification data acquisition, comprising:
database: supporting log output after logic decoding, outputting changed records in transactions and carrying transaction information;
Log transmitter: receiving a logic decoding log of a database, and pushing the log into a message queue according to a transaction mode;
message queues: caching or storing the input information, and outputting the log to a data collector in a transaction mode;
data collector: and receiving the output of the message queue and performing subsequent data processing.
Further, the database outputs the logically decoded log stream immediately when processing the transaction, and identifies the start and end of the transaction in the output log stream.
Further, the log transmitter receives logic log information transmitted from the database;
when receiving the transaction starting information, recording a transaction identifier, starting a transaction on a message queue, and corresponding the transaction identifier to the transaction information on the message queue;
Each time a logical log belonging to the database transaction is received, outputting the log to a corresponding transaction of a message queue;
when the transaction end information is received, the corresponding transaction on the message queue is operated according to the end type, and is committed or aborted.
Further, the message queue outputs the received transactions according to the configuration, the submitted transactions are sequentially output according to the submitted order, and the aborted transactions are not output.
Compared with the prior art, the method and the system for acquiring the database change data have the following beneficial effects:
exposing the transaction information of the database along with the logic log, and controlling a message queue through a log transmitter, wherein the transaction information and the message queue are integrated, so that the transaction property of record change is ensured for a data collector;
The various performance pressures are not related to the responsibilities of the database, and the message queue components, such as kafka, are solutions suitable for the pressure scene, and by the invention, the pressures are released from the database and put into a more suitable message queue, so that the performance problems caused by long transactions and large transactions are solved, the performance and the reliability of the database are greatly improved, and the use experience of the database is improved.
Drawings
The accompanying drawings, which are included to provide a further understanding of the invention and are incorporated in and constitute a part of this specification, illustrate embodiments of the invention and together with the description serve to explain the invention. In the drawings:
fig. 1 is a flowchart of a method for collecting database change data according to an embodiment of the present invention.
Detailed Description
It should be noted that, without conflict, the embodiments of the present invention and features of the embodiments may be combined with each other.
The invention will be described in detail below with reference to the drawings in connection with embodiments.
A method of database change data collection, comprising:
s1, processing a transaction log by a database, extracting transaction meta-information, and outputting record change information according to configuration;
s2, the log transmitter classifies the records according to the transaction identification and executes corresponding operations according to the transaction type;
s3, the data collector reads the submitted transaction record from the message queue.
The process of processing the transaction log by the database, extracting the transaction meta-information and outputting record change information according to the configuration comprises the following steps:
for each transaction log read by the database, the database outputs meta-information of corresponding transactions, including transaction identification, transaction opening, transaction submission, transaction rollback or termination;
and outputting information of record change according to a configuration specified format for each record change log read by the database, wherein the information comprises transaction identification of the log and specific information of adding and deleting the log.
Wherein the recorded change log is: operations such as adding, deleting and modifying the database belong to changing operations, the description of the changing operations is an output record change log, and the record change log mainly comprises record data after changing, changing types and other auxiliary information;
The configuration specification format is: the user-specified format of the record change log, such as the content format of the auxiliary information, whether the record change log contains changed old data (such as update and deletion operations, which generally carry old data before necessary change), outputs record change information according to the configuration-specified format, that is, if the record change log includes the old data, the output record change information should also carry corresponding old data.
The process of the log transmitter executing corresponding operation according to the transaction type comprises the following steps:
if the transaction type is transaction opening information, notifying a downstream message queue to open the transaction;
If the transaction type is the record information, pushing the message to the appointed transaction of the message queue;
If the transaction type is transaction commit or rollback abort information, the message queue will be notified that the transaction committed or aborted.
A system for database change data collection, comprising:
database: supporting log output after logic decoding, outputting changed records in transactions and carrying transaction information;
Log transmitter: receiving a logic decoding log of a database, and pushing the log into a message queue according to a transaction mode;
message queues: caching or storing the input information, and outputting the log to a data collector in a transaction mode;
data collector: and receiving the output of the message queue and performing subsequent data processing.
The transaction mode specifically comprises the following steps: in pushing the log into the message queue, transactions are employed to ensure atomicity, consistency, isolation, and persistence (ACID properties) of the operation; specifically, the push process of the logical decode log will be considered as one transaction unit, either all successful pushes or all failed rollbacks. This ensures that in the data transfer between the database and the message queue, either all steps complete successfully or rollback to the state of the beginning of the transaction when an error occurs, maintaining data consistency and integrity.
When the database processes the transaction, the logically decoded log stream is output immediately, the beginning and the end of the transaction are identified in the output log stream, and in the processing mode, the beginning and the end of each transaction can be monitored in real time by observing the output log stream, so that the number and the state of the transaction currently processed by the system are known; the real-time output log stream is helpful for timely finding and positioning errors or anomalies in the system, and the start and end of the identification transaction can help track problems and quickly position fault points; meanwhile, a system administrator or a developer can timely take measures, such as interrupting or rolling back the problematic transaction, according to the log stream output in real time, so that potential data loss or consistency problems are reduced.
The log transmitter receives logic log information transmitted from the database;
when receiving the transaction starting information, recording a transaction identifier, starting a transaction on a message queue, and corresponding the transaction identifier to the transaction information on the message queue;
Each time a logical log belonging to the database transaction is received, outputting the log to a corresponding transaction of a message queue;
when the transaction end information is received, the corresponding transaction on the message queue is operated according to the end type, and is committed or aborted.
The message queue outputs the received transactions according to the configuration, the submitted transactions are sequentially output according to the submitted sequence, and the aborted transactions are not output.
Those of ordinary skill in the art will appreciate that the elements and method steps of each example described in connection with the embodiments disclosed herein may be implemented as electronic hardware, computer software, or combinations of both, and that the elements and steps of each example have been described generally in terms of functionality in the foregoing description to clearly illustrate this interchangeability of hardware and software. Whether such functionality is implemented as hardware or software depends upon the particular application and design constraints imposed on the solution. Skilled artisans may implement the described functionality in varying ways for each particular application, but such implementation decisions should not be interpreted as causing a departure from the scope of the present invention.
In the several embodiments provided in the present application, it should be understood that the disclosed methods and systems may be implemented in other ways. For example, the above-described division of units is merely a logical function division, and there may be another division manner when actually implemented, for example, a plurality of units or components may be combined or may be integrated into another system, or some features may be omitted or not performed. The units may or may not be physically separate, and components shown as units may or may not be physical units, may be located in one place, or may be distributed over a plurality of network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the embodiment of the present application.
Finally, it should be noted that: the above embodiments are only for illustrating the technical solution of the present invention, and not for limiting the same; although the invention has been described in detail with reference to the foregoing embodiments, it will be understood by those of ordinary skill in the art that: the technical scheme described in the foregoing embodiments can be modified or some or all of the technical features thereof can be replaced by equivalents; such modifications and substitutions do not depart from the spirit of the invention, and are intended to be included within the scope of the appended claims and description.
The foregoing description of the preferred embodiments of the invention is not intended to be limiting, but rather is intended to cover all modifications, equivalents, alternatives, and improvements that fall within the spirit and scope of the invention.
Claims (5)
1.A method for database change data collection, comprising:
processing the transaction log by the database, extracting the transaction meta-information, and outputting record change information according to configuration;
The log transmitter classifies the records according to the transaction identification and executes corresponding operations according to the transaction type;
the data collector reads the submitted transaction record from the message queue;
Constructing a corresponding database change data acquisition system according to a database change data acquisition method, wherein the system comprises:
database: supporting log output after logic decoding, outputting changed records in transactions and carrying transaction information;
Log transmitter: receiving a logic decoding log of a database, and pushing the log into a message queue according to a transaction mode;
message queues: caching or storing the input information, and outputting the log to a data collector in a transaction mode;
Data collector: receiving the output of the message queue and performing subsequent data processing;
The log transmitter receives logic log information transmitted from the database;
when receiving the transaction starting information, recording a transaction identifier, starting a transaction on a message queue, and corresponding the transaction identifier to the transaction information on the message queue;
Each time a logical log belonging to the database transaction is received, outputting the log to a corresponding transaction of a message queue;
when receiving the transaction ending information, operating the corresponding transaction on the message queue according to the ending type, and submitting or suspending the corresponding transaction;
The transaction mode specifically comprises the following steps: and taking the pushing process of the logic decoding log as a transaction unit, and controlling all logs to be pushed successfully or rolled back in a failure mode in data transmission between the database and the message queue.
2. A method of database change data collection according to claim 1, wherein:
the process of processing the transaction log by the database, extracting the transaction meta-information and outputting record change information according to the configuration comprises the following steps:
for each transaction log read by the database, the database outputs meta-information of corresponding transactions, including transaction identification, transaction opening, transaction submission, transaction rollback or termination;
and outputting information of record change according to a configuration specified format for each record change log read by the database, wherein the information comprises transaction identification of the log and specific information of adding and deleting the log.
3. A method of database change data collection according to claim 1, wherein: the process of the log transmitter executing corresponding operation according to the transaction type comprises the following steps:
if the transaction type is transaction opening information, notifying a downstream message queue to open the transaction;
If the transaction type is the record information, pushing the message to the appointed transaction of the message queue;
If the transaction type is transaction commit or rollback abort information, the message queue will be notified that the transaction committed or aborted.
4. A method of database change data collection according to claim 1, wherein:
the database outputs the logically decoded log stream immediately when processing the transaction, and identifies the start and end of the transaction in the output log stream.
5. A method of database change data collection according to claim 1, wherein:
the message queue outputs the received transactions according to the configuration, the submitted transactions are sequentially output according to the submitted sequence, and the aborted transactions are not output.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202410108868.7A CN117632991B (en) | 2024-01-26 | 2024-01-26 | Method and system for acquiring database change data |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202410108868.7A CN117632991B (en) | 2024-01-26 | 2024-01-26 | Method and system for acquiring database change data |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN117632991A CN117632991A (en) | 2024-03-01 |
| CN117632991B true CN117632991B (en) | 2024-05-03 |
Family
ID=90016720
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202410108868.7A Active CN117632991B (en) | 2024-01-26 | 2024-01-26 | Method and system for acquiring database change data |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN117632991B (en) |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN105426410A (en) * | 2015-11-02 | 2016-03-23 | 东软集团股份有限公司 | Data acquisition system and analytic method for same |
| CN109933630A (en) * | 2019-03-19 | 2019-06-25 | 武汉达梦数据库有限公司 | Database data real-time synchronization method and equipment |
| CN115361219A (en) * | 2022-08-24 | 2022-11-18 | 北京天融信网络安全技术有限公司 | Log file processing method, electronic device and storage medium |
| CN116303789A (en) * | 2023-03-21 | 2023-06-23 | 天津南大通用数据技术股份有限公司 | Parallel synchronization method and device for multi-fragment multi-copy database and readable medium |
Family Cites Families (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CA3177620A1 (en) * | 2021-05-06 | 2022-11-06 | Strong Force Iot Portfolio 2016, Llc | Quantum, biological, computer vision, and neural network systems for industrial internet of things |
-
2024
- 2024-01-26 CN CN202410108868.7A patent/CN117632991B/en active Active
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN105426410A (en) * | 2015-11-02 | 2016-03-23 | 东软集团股份有限公司 | Data acquisition system and analytic method for same |
| CN109933630A (en) * | 2019-03-19 | 2019-06-25 | 武汉达梦数据库有限公司 | Database data real-time synchronization method and equipment |
| CN115361219A (en) * | 2022-08-24 | 2022-11-18 | 北京天融信网络安全技术有限公司 | Log file processing method, electronic device and storage medium |
| CN116303789A (en) * | 2023-03-21 | 2023-06-23 | 天津南大通用数据技术股份有限公司 | Parallel synchronization method and device for multi-fragment multi-copy database and readable medium |
Also Published As
| Publication number | Publication date |
|---|---|
| CN117632991A (en) | 2024-03-01 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| EP3968175B1 (en) | Data replication method and apparatus, and computer device and storage medium | |
| CN108459919B (en) | Distributed transaction processing method and device | |
| EP2062139B1 (en) | Method for improving transfer of event logs for replication of executing programs | |
| US7996363B2 (en) | Real-time apply mechanism in standby database environments | |
| US8868492B2 (en) | Method for maximizing throughput and minimizing transactions response times on the primary system in the presence of a zero data loss standby replica | |
| US7783601B2 (en) | Replicating and sharing data between heterogeneous data systems | |
| EP2474919A1 (en) | System and method for data replication between heterogeneous databases | |
| CN113396407A (en) | System and method for augmenting database applications using blockchain techniques | |
| US20030074600A1 (en) | Data backup/recovery system | |
| US20070239758A1 (en) | Loosely Coupled Log Sequence Identifiers | |
| CN102945278A (en) | Method and device for redoing logs of database records | |
| US5734897A (en) | Reduction of logging in distributed systems | |
| US7254683B2 (en) | Speculative data mirroring apparatus method and system | |
| US9330153B2 (en) | System, method, and computer readable medium that coordinates between devices using exchange of log files | |
| CN113961153A (en) | Method and device for writing index data into disk and terminal equipment | |
| JP2992349B2 (en) | How to warm up a preliminary process in a replicated real-time system, especially in a telephone exchange | |
| US20130318059A1 (en) | Transfer of data from transactional data sources to partitioned databases in restartable environment | |
| US20230315713A1 (en) | Operation request processing method, apparatus, device, readable storage medium, and system | |
| CN113438275B (en) | Data migration method and device, storage medium and data migration equipment | |
| CN117632991B (en) | Method and system for acquiring database change data | |
| CN114116665A (en) | Method for parallel writing transaction log in database to improve processing efficiency | |
| CN114138833A (en) | Method and system for data synchronization of relational database and cache database | |
| US7913109B2 (en) | Storage control apparatus and storage control method | |
| CN113779087A (en) | Database high-availability method and system based on remote direct memory access | |
| CN112905613A (en) | Data bidirectional synchronization method and device for heterogeneous database |
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 |