Database synchronization method and device, equipment and storage medium

Abstract

The invention belongs to the technical field of database synchronization, and discloses a database synchronization method and device, equipment and a storage medium. The method comprises the steps that a database synchronization task is started, and a corresponding triggering mode is determined; acquiring corresponding data based on the triggering mode, and processing the acquired data to obtain processed data; detecting the state of the target database; and based on the state of the target database, selecting a corresponding strategy to write the processed data into the target database so as to realize database synchronization. By means of the mode, the database native log is analyzed to achieve non-intrusive data capture, meanwhile, writing can be recovered through the retry queue after data synchronization is interrupted, and the database synchronization efficiency is improved.

Description

Technical Field

[0001] The present invention relates to the technical field of database synchronization, and particularly to a database synchronization method, device, equipment and storage medium. Background Art

[0002] In enterprise applications, data synchronization is a common and important task to ensure data consistency and real-time between different systems or databases.

[0003] However, the current database synchronization has the problem that full synchronization is prone to incomplete data due to network interruption. At the same time, traditional database synchronization cannot achieve non-intrusive data capture.

[0004] The above content is only used to assist in understanding the technical solution of the present invention, and does not represent an admission that the above content is prior art. Summary of the Invention

[0005] The main object of the present invention is to provide a database synchronization method, device, equipment and storage medium, aiming to solve the technical problems that the current database synchronization is prone to incomplete data due to network interruption during full synchronization, and traditional database synchronization cannot achieve non-intrusive data capture.

[0006] To achieve the above object, the present invention provides a database synchronization method, which includes the following steps:

[0007] Start a database synchronization task and determine the corresponding trigger mode;

[0008] Obtain corresponding data based on the trigger mode, and process the obtained data to obtain processed data;

[0009] Detect the status of the target database;

[0010] Based on the status of the target database, select a corresponding strategy to write the processed data into the target database to achieve database synchronization, and the strategies include normal writing and multiple writes through a retry queue.

[0011] In some embodiments, the trigger mode includes timed trigger and real-time trigger. The timed trigger is used to scan the database incremental log, and the real-time trigger is used to monitor database events.

[0012] In some embodiments, the obtaining corresponding data based on the trigger mode includes:

[0013] When the trigger mode is a timed trigger, scan the incremental log file generated since the last synchronization to obtain corresponding data;

[0014] When the triggering mode is real-time triggering, the database events are monitored, and the change events are captured in real time through monitoring to obtain corresponding data from the change events.

[0015] In some embodiments, the processing of the obtained data includes:

[0016] Performing formatting processing on the obtained data to obtain formatted data that meets the storage requirements of the target database;

[0017] Performing compression and encryption processing on the formatted data to obtain processed data.

[0018] In some embodiments, the writing of the processed data into the target database based on the status of the target database by selecting corresponding policies includes:

[0019] When the target database is in a normal state, the processed data is normally written into the target database, and after the writing is successful, corresponding operation audit logs are generated and the database synchronization status is updated.

[0020] In some embodiments, the writing of the processed data into the target database based on the status of the target database by selecting corresponding policies includes:

[0021] When the target database is in an abnormal state, the processed data is added to the retry queue;

[0022] The processed data is written into the target database through the retry queue, and after the writing is successful, corresponding operation audit logs are generated and the database synchronization status is updated.

[0023] In some embodiments, the method further includes:

[0024] If the processed data is not written into the target database through the retry queue, corresponding alarm prompts are output to remind that the retry fails.

[0025] In addition, to achieve the above object, the present invention also proposes a database synchronization device, and the database synchronization device includes:

[0026] A judgment module that starts a database synchronization task and determines a corresponding triggering mode;

[0027] A processing module for obtaining corresponding data based on the triggering mode and processing the obtained data to obtain processed data;

[0028] A detection module for detecting the status of the target database;

[0029] A synchronization module, configured to select a corresponding policy based on the status of the target database to write the processed data into the target database, so as to achieve database synchronization, where the policy includes normal writing and multiple writes through a retry queue.

[0030] In addition, to achieve the above object, the present invention also provides a database synchronization device, including: a memory, a processor, and a database synchronization program stored on the memory and executable on the processor, where the database synchronization program is configured to implement the steps of the database synchronization method as described above.

[0031] In addition, to achieve the above object, the present invention also provides a storage medium, on which a database synchronization program is stored, and when the database synchronization program is executed by a processor, it implements the steps of the database synchronization method as described above.

[0032] In the present invention, a database synchronization task is started to determine a corresponding triggering method; corresponding data is obtained based on the triggering method, and the obtained data is processed to obtain processed data; the status of the target database is detected; a corresponding policy is selected based on the status of the target database to write the processed data into the target database, so as to achieve database synchronization. By the above method, the original database log is parsed to achieve non-intrusive data capture, and at the same time, the retry queue can be used to resume writing after the data synchronization is interrupted, improving the efficiency of database synchronization. Description of the Drawings

[0033] Figure 1 It is a schematic flowchart of the first embodiment of the database synchronization method of the present invention;

[0034] Figure 2 It is a schematic diagram of the overall framework in the database synchronization method of the present invention;

[0035] Figure 3 It is a schematic diagram of the underlying working process of DBSyncDataSoutce in the database synchronization method of the present invention;

[0036] Figure 4 It is a schematic diagram of the class diagram relationship of DBSyncTemplate in the database synchronization method of the present invention;

[0037] Figure 5 It is a structural block diagram of the first embodiment of the database synchronization device of the present invention.

[0038] The realization, functional features, and advantages of the object of the present invention will be further described with reference to the embodiments and the accompanying drawings. Detailed Embodiments

[0039] It should be understood that the specific embodiments described herein are only used to explain the present invention and are not used to limit the present invention.

[0040] An embodiment of the present invention provides a database synchronization method. Refer to Figure 1 , Figure 1 which is a schematic flowchart of the first embodiment of a database synchronization method of the present invention.

[0041] In this embodiment, the database synchronization method includes the following steps:

[0042] Step S10: Start a database synchronization task and determine the corresponding triggering method.

[0043] In this embodiment, the execution subject of this embodiment is a database synchronization device. Among them, the database synchronization device has functions such as data processing, data communication, and program running. The database synchronization device can be a computer terminal device or other network devices. Of course, it can also be other devices with similar functions. This embodiment does not limit this.

[0044] It should be noted that in enterprise-level applications, data synchronization is a common and important task to ensure data consistency and real-time between different systems or databases. At present, there are problems in database synchronization that full-scale synchronization is prone to data incompleteness due to network interruption, and at the same time, traditional database synchronization cannot achieve non-intrusive data capture.

[0045] To solve the above technical problems, in this embodiment, a database synchronization task is started to determine the corresponding triggering method; based on the triggering method, the corresponding data is obtained, and the obtained data is processed to obtain the processed data; the state of the target database is detected; based on the state of the target database, a corresponding strategy is selected to write the processed data into the target database to achieve database synchronization. Through the above method, non-intrusive data capture is achieved by parsing the original database log, and at the same time, the retry queue can be used to resume writing after the data synchronization is interrupted, improving the efficiency of database synchronization. Specifically, it can be implemented as follows.

[0046] In a specific implementation, in this embodiment, a synchronization software or tool DBSync used during database synchronization is first proposed to implement the above-mentioned database synchronization method. DBSync encapsulates a large amount of boilerplate code, including opening connections, processing SQL statements, processing result sets, and closing connections. This makes database operations more concise and clear. DBSync automatically manages database resources, including closing connections, statements, and result sets, avoiding common resource leakage problems. DBSync converts SQL exceptions into data access exceptions (DataAccessException), which is an unchecked exception, making exception handling more concise. The specific application process includes Step 1: Agent program installation; lightweight agent programs are respectively deployed on the source database and the target database server for data capture, transmission, and status monitoring. Step 2: Synchronization rule configuration; define synchronization policies through the management interface: Data scope: Select the tables / fields to be synchronized (such as only synchronizing the core fields of the order table), Trigger condition: Scheduled task (at 0:00 every day) / Real-time monitoring (binlog monitoring), Conflict handling rule: Timestamp priority / Manual review mechanism. Step 3: Data incremental capture; the agent program captures change events in real time through the database log (such as binlog) and generates a structured incremental data packet (including operation type, timestamp, primary key value). Step 4: Data compression and transmission; the data packet is compressed using a columnar compression algorithm (compression rate > 70%) and transmitted to the target end through a TLS encrypted channel. Step 5: Transaction-level writing; the agent at the target end replays the operations in transaction order to ensure atomicity: Batch merge INSERT statements (1000 statements per batch), and trigger an automatic rollback and retry mechanism (maximum retry times: 3 times) when it fails. Step 6: Status visualization; the management interface displays the synchronization progress, latency time (in milliseconds), and error logs in real time, and supports manual triggering of data verification (checksum comparison). Through the above synchronization software, consistency control and efficiency optimization can be achieved. The specific implementation methods and technical effects are shown in Table 1 (consistency control) and Table 2 (efficiency optimization) below respectively.

[0047] Table 1:

[0048]

[0049] Table 2:

[0050]

[0051]

[0052] Use DBSyncDataSource to create, connect, and release database connections. To improve the efficiency of DBSyncDataSource, the code is designed based on the principle of "less is more". It avoids overly complex functions and redundant logic, retains only the core functions of connection pool management, and reduces unnecessary overhead. This makes the code very small and runs more efficiently. CAS operation is used, which is a lock-free concurrency mechanism. When acquiring and releasing connections, using CAS can reduce the waiting time of threads in critical areas and improve throughput. Traditional connection pools usually use synchronous locks (such as synchronized) when acquiring and releasing connections, which can easily lead to thread blocking. DBSyncDataSource avoids global locks as much as possible, reduces synchronous access to shared resources, and thus reduces lock contention. Built-in leak detection function, if a connection has not been returned for a long time (such as forgetting to call connection.close()), DBSyncDataSource will detect and log possible connection leaks. Through this mechanism, it can help developers discover connection management problems earlier.

[0053] DBSync is a persistent database synchronization software based on the general JDBC protocol. The underlying principle of connecting to the database is implemented based on the DBSyncTemplate (Java Database Connectivity) API, which establishes a connection with the database through the JDBC driver and performs database operations.

[0054] DBSyncTemplate is the core class of the DBSync framework. It is an encapsulation of the traditional JDBC. It mainly provides the following functions: loading the driver, obtaining the connection Connection (DBSyncDataSource), obtaining the statement object for executing SQL, executing the result, processing the result, and releasing resources. The update and query methods provided by DBSyncTemplate are its core methods. The overall architecture of this solution is as follows Figure 2 As shown, the underlying workflow of DBSyncDataSoutce is as follows Figure 3 As shown in the DBSyncTemplate class diagram, the relationship is as follows Figure 4 shown.

[0055] Furthermore, in a specific database synchronization method, after starting the database synchronization task, it is necessary to first determine the corresponding triggering method. In this embodiment, the triggering method is divided into timed triggering and real-time triggering. The timed triggering is used to scan the database incremental log, and the real-time triggering is used to monitor database events.

[0056] Step S20: Obtain corresponding data based on the triggering method, and process the obtained data to obtain processed data.

[0057] In specific implementation, if the triggering method is timed triggering, then scan the incremental log file generated since the last synchronization to obtain corresponding data, that is, obtain the newly added or newly reduced database data from the incremental log file as the data that needs to be imported into the target database subsequently. If the triggering method is real-time triggering, then listen for database events, capture change events in real time through listening, so as to obtain corresponding data from the change events, that is, obtain the changed data from the database through the change events, and this data is used as the data that needs to be imported into the target database subsequently. By the above method, the consistency of the data in the two databases can be ensured.

[0058] Further, after obtaining the data that needs to be synchronously written into the target database, in this embodiment, the obtained data needs to be processed first. The processing process includes formatting and compression encryption respectively. Through formatting processing, the data can meet the storage requirements of the target database. Compressing and encrypting the formatted data obtained after formatting can improve the efficiency of data writing and enhance the security of the data.

[0059] Step S30: Detect the status of the target database.

[0060] In this embodiment, in order to ensure normal data writing, the status of the target database also needs to be detected before writing. The status of the target database includes a normal state and an abnormal state. When the target database is in a normal state, the processed data can be directly written. When the target database is in an abnormal state, for example, in an abnormal state after a write interruption, in this embodiment, the retry queue can be used for re-writing, and the remaining unwritten data can be continuously written from the interruption point.

[0061] Step S40: Select a corresponding policy based on the status of the target database to write the processed data into the target database to achieve database synchronization.

[0062] In a specific implementation, if the database is in a normal state, in this embodiment, the processed data can be normally written into the target database, and after the writing is successful, corresponding operation audit logs are generated and the database synchronization status is updated. If the target database is in an abnormal state, the processed data is added to the retry queue; the processed data is written into the target database through the retry queue, and after the writing is successful, corresponding operation audit logs are generated and the database synchronization status is updated. Among them, if a writing interruption occurs, the remaining unwritten data is in the retry queue. If the processed data is not written into the target database through the retry queue, corresponding alarm prompts are output to remind that the retry fails. The operation audit logs are used to record the key operations and status changes during the entire synchronization process for subsequent review and analysis. Updating the synchronization status can update the dashboard that displays the synchronization status, enabling the administrator to understand the current synchronization progress and results.

[0063] In this embodiment, a database synchronization task is started to determine the corresponding triggering method; corresponding data is obtained based on the triggering method, and the obtained data is processed to obtain processed data; the status of the target database is detected; and corresponding strategies are selected based on the status of the target database to write the processed data into the target database to achieve database synchronization. Through the above method, parsing the original database logs realizes non-intrusive data capture, and at the same time, the retry queue can be used to resume writing after a data synchronization interruption, improving the efficiency of database synchronization.

[0064] In addition, an embodiment of the present invention further provides a storage medium, on which a database synchronization program is stored. When the database synchronization program is executed by a processor, the steps of the database synchronization method described above are implemented.

[0065] Refer to Figure 5 , Figure 5 which is the structural block diagram of the first embodiment of the database synchronization device of the present invention.

[0066] As Figure 5 shown, the database synchronization device proposed by the embodiment of the present invention includes:

[0067] A decision module 10 that starts a database synchronization task and determines the corresponding triggering method;

[0068] A processing module 20 for obtaining corresponding data based on the triggering method and processing the obtained data to obtain processed data;

[0069] A detection module 30 for detecting the status of the target database;

[0070] A synchronization module 40, configured to select a corresponding policy based on the state of the target database to write the processed data into the target database, so as to achieve database synchronization, where the policy includes normal writing and multiple writes through a retry queue.

[0071] In this embodiment, a database synchronization task is started, and a corresponding triggering method is determined; corresponding data is obtained based on the triggering method, and the obtained data is processed to obtain processed data; the state of the target database is detected; a corresponding policy is selected based on the state of the target database to write the processed data into the target database, so as to achieve database synchronization. Through the above method, the original database log is parsed to achieve non-invasive data capture, and at the same time, the retry queue can be used to resume writing after the data synchronization is interrupted, improving the efficiency of database synchronization.

[0072] In some embodiments, the triggering method includes timed triggering and real-time triggering. The timed triggering is used to scan the database incremental log, and the real-time triggering is used to monitor database events.

[0073] In some embodiments, the processing module 20 is configured to, when the triggering method is timed triggering, scan the incremental log file generated since the last synchronization to obtain corresponding data;

[0074] When the triggering method is real-time triggering, monitor database events, and capture change events in real time through monitoring to obtain corresponding data from the change events.

[0075] In some embodiments, the processing module 20 is configured to perform formatting processing on the obtained data to obtain formatted data, and the formatted data meets the storage requirements of the target database;

[0076] Perform compression and encryption processing on the formatted data to obtain processed data.

[0077] In some embodiments, the synchronization module 40 is configured to, when the target database is in a normal state, write the processed data into the target database normally, and after the writing is successful, generate corresponding operation audit logs and update the database synchronization status.

[0078] In some embodiments, the synchronization module 40 is configured to select a corresponding policy based on the state of the target database to write the processed data into the target database, including:

[0079] When the target database is in an abnormal state, add the processed data to the retry queue;

[0080] Write the processed data into the target database through the retry queue, and after the writing is successful, generate corresponding operation audit logs and update the database synchronization status.

[0081] In some embodiments, the synchronization module 40 is configured to output a corresponding warning prompt if the processed data is not written into the target database through the retry queue, so as to remind that the retry fails.

[0082] An embodiment of the present application further provides a database synchronization device, including a processor, a communication interface, a memory, and a communication bus. Among them, the processor, the communication interface, and the memory complete communication with each other through the communication bus. The memory is used to store a database synchronization program; the processor is configured to implement the above-mentioned database synchronization method when executing the program stored on the memory.

[0083] The communication bus mentioned in the above database synchronization device may be a Peripheral Component Interconnect (PCI) bus or an Extended Industry Standard Architecture (EISA) bus, etc. This communication bus can be divided into an address bus, a data bus, a control bus, etc.

[0084] The communication interface is used for communication between the above database synchronization device and other devices.

[0085] The memory may include a Random Access Memory (RAM), and may also include a Non-Volatile Memory (NVM), such as at least one disk memory. Optionally, the memory may also be at least one storage device located far from the aforementioned processor.

[0086] The above-mentioned processor may be a general-purpose processor, including a Central Processing Unit (CPU), a Network Processor (NP), etc.; it may also be a Digital Signal Processor (DSP), an Application Specific Integrated Circuit (ASIC), a Field-Programmable Gate Array (FPGA), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components.

[0087] In the above embodiments, it can be implemented in whole or in part by software, hardware, firmware, or any combination thereof. When implemented using software, it can be implemented in whole or in part in the form of a computer program product. The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, the processes or functions described in the embodiments of the present application are generated in whole or in part. The computer can be a general-purpose computer, a special-purpose computer, a computer network, or other programmable devices. The computer instructions can be stored in a computer-readable storage medium, or transmitted from one computer-readable storage medium to another computer-readable storage medium. For example, the computer instructions can be transmitted from one website, computer, server, or data center to another website, computer, server, or data center by wire (such as coaxial cable, optical fiber, digital subscriber line (DSL)) or wireless (such as infrared, wireless, microwave, etc.). The computer-readable storage medium can be any available medium that can be accessed by a computer, or a data storage device such as a server or data center that includes one or more integrated available media. The available medium can be a magnetic medium (such as a floppy disk, hard disk, magnetic tape), an optical medium (such as a DVD), or a semiconductor medium (such as a solid state disk (SSD)).

[0088] It should be noted that, in this document, relational terms such as first and second are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any actual relationship or order between these entities or operations. Moreover, the term "comprising", "including" or any other variation thereof is intended to cover non-exclusive inclusion, so that a process, method, article or device comprising a series of elements includes not only those elements, but also other elements not expressly listed, or elements inherent to such process, method, article or device. Without further limitation, an element defined by the statement "comprising a..." does not exclude the existence of additional identical elements in the process, method, article or device comprising the element.

[0089] Each embodiment in this specification is described in a related manner. The same or similar parts among the embodiments can be referred to each other, and the differences between each embodiment and other embodiments are emphasized. In particular, for the system embodiment, since it is basically similar to the method embodiment, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiment.

[0090] The above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it; although the present invention has been described in detail with reference to the foregoing embodiments, those of ordinary skill in the art should understand that they can still modify the technical solutions described in the foregoing embodiments, or perform equivalent replacements for some of the technical features; and these modifications or replacements do not make the essence of the corresponding technical solutions deviate from the spirit and scope of the technical solutions of the various embodiments of the present invention.

[0091] It should be understood that the above is only for illustration and does not constitute any limitation to the technical solutions of the present invention. In specific applications, those skilled in the art can set according to needs, and the present invention does not limit this.

[0092] It should be noted that the above-described work process is only illustrative and does not limit the protection scope of the present invention. In actual applications, those skilled in the art can select some or all of them according to actual needs to achieve the purpose of the solution of this embodiment, and there is no limitation here.

[0093] In addition, for the technical details not described in detail in this embodiment, reference can be made to the database synchronization method provided in any embodiment of the present invention, and details will not be repeated here.

[0094] In addition, it should be noted that in this article, the terms "include", "comprise" or any other variant thereof are intended to cover non-exclusive inclusion, so that a process, method, article or system including a series of elements not only includes those elements, but also includes other elements not explicitly listed, or also includes elements inherent to such a process, method, article or system. Without more limitations, the element defined by the statement "including one..." does not exclude the existence of another identical element in the process, method, article or system including the element.

[0095] The serial numbers of the above embodiments of the present invention are only for description and do not represent the advantages or disadvantages of the embodiments.

[0096] Through the description of the above embodiments, those skilled in the art can clearly understand that the above-described embodiment methods can be implemented by means of software plus a necessary general hardware platform. Of course, they can also be implemented by hardware, but in many cases the former is a better implementation method. Based on such an understanding, the technical solutions of the present invention, in essence or the part that contributes to the prior art, can be embodied in the form of a software product. The computer software product is stored in a storage medium (such as a read-only memory (ROM) / RAM, magnetic disk, optical disk), and includes several instructions to enable a terminal device (which can be a mobile phone, computer, server, or network device, etc.) to execute the methods described in the various embodiments of the present invention.

[0097] The above are only the preferred embodiments of the present invention, and do not limit the patent scope of the present invention accordingly. Any equivalent structure or equivalent process transformation made by using the content of the specification and drawings of the present invention, or directly or indirectly applied in other related technical fields, shall be equally included in the patent protection scope of the present invention.

[0098] It can be understood that the system provided by the embodiment of the present invention corresponds to the method provided by the embodiment of the present invention. The explanations, examples and beneficial effects of related content can refer to the corresponding parts in the above method.

Claims

1. A database synchronization method, characterized in that, The database synchronization method includes: Starting a database synchronization task and determining a corresponding triggering method; Obtaining corresponding data based on the triggering method and processing the obtained data to obtain processed data; Detecting the status of the target database; Selecting a corresponding strategy based on the status of the target database to write the processed data into the target database to achieve database synchronization, and the strategy includes normal writing and multiple writes through a retry queue.

2. The database synchronization method according to claim 1, wherein The triggering method includes timed triggering and real-time triggering. The timed triggering is used to scan the database incremental log, and the real-time triggering is used to monitor database events.

3. The database synchronization method according to claim 2, wherein The obtaining of corresponding data based on the triggering method includes: When the triggering method is timed triggering, scanning the incremental log file generated since the last synchronization to obtain corresponding data; When the triggering method is real-time triggering, monitoring database events, capturing change events in real time through monitoring, and obtaining corresponding data from the change events.

4. The database synchronization method according to claim 1, wherein The processing of the obtained data includes: Performing formatting processing on the obtained data to obtain formatted data that meets the storage requirements of the target database; Performing compression and encryption processing on the formatted data to obtain processed data.

5. The database synchronization method according to claim 1, wherein The selecting of a corresponding strategy based on the status of the target database to write the processed data into the target database includes: When the target database is in a normal state, normally writing the processed data into the target database, and after successful writing, generating corresponding operation audit logs and updating the database synchronization status.

6. The database synchronization method according to claim 1, wherein The selecting of a corresponding strategy based on the status of the target database to write the processed data into the target database includes: When the target database is in an abnormal state, adding the processed data to the retry queue; Writing the processed data into the target database through the retry queue, and after successful writing, generating corresponding operation audit logs and updating the database synchronization status.

7. The database synchronization method according to claim 6, wherein The method further includes: If the processed data is not written into the target database through the retry queue, outputting a corresponding warning prompt to remind of the retry failure.

8. A database synchronization device, characterized in that, The database synchronization device includes: A judgment module that starts a database synchronization task and determines a corresponding triggering method; A processing module for obtaining corresponding data based on the triggering method and processing the obtained data to obtain processed data; A detection module for detecting the status of the target database; A synchronization module for selecting a corresponding strategy based on the status of the target database to write the processed data into the target database to achieve database synchronization, and the strategy includes normal writing and multiple writes through a retry queue.

9. A database synchronization device, characterized in that, The database synchronization device includes: a memory, a processor, and a database synchronization program stored on the memory and executable on the processor. The database synchronization program is configured to implement the steps of the database synchronization method according to any one of claims 1 to 7.

10. A storage medium, characterized in that, A database synchronization program is stored on the storage medium, and when the database synchronization program is executed by a processor, the steps of the database synchronization method according to any one of claims 1 to 7 are implemented.

Images