1. A method for realizing real-time relational database data synchronization is characterized by comprising the following steps:

and (3) a synchronization process: when the data of the master library is subjected to the operations of increasing, deleting and modifying, the Java application layer captures the operations of increasing, deleting and modifying the master library, executes the operations of increasing, deleting and modifying on the master library, asynchronously sends the operations of increasing, deleting and modifying to a message queue, synchronously processes the operations to the slave library, and executes the operations of increasing, deleting and modifying on the slave library;

and (3) compensation process: and setting timing check time, and performing timing check, if finding that the operation of increasing, deleting and modifying the data which is not synchronized to the slave library beyond the preset time, re-synchronizing the operation of increasing, deleting and modifying the data which is not synchronized to the slave library until the data in the slave library is updated completely.

2. The method for implementing real-time relational database data synchronization according to claim 1, wherein:

when the data updated by the main library is grabbed, the Java application program is grabbed through the ORM layer plug-in.

3. The method for implementing real-time relational database data synchronization according to claim 1, wherein:

the message queue is Kafka.

4. The method for implementing real-time relational database data synchronization according to claim 1, wherein:

when the Java application layer grabs the operation of adding and deleting modification to the master library, the synchronous state of the grabbed operation of adding and deleting modification is modified into 'to be synchronous'/'to be deleted', the slave library executes the operation of adding and deleting modification and returns a synchronous result after the synchronization is finished, the synchronous result comprises 'synchronized'/'synchronization failure'/'deletion failure', the synchronous state is updated according to the synchronous result,

and after the Java application layer grabs the operation of increasing, deleting and modifying the master library, adding the current time of the system into the captured operation of increasing, deleting and modifying as the final modifying time.

5. The method for implementing real-time relational database data synchronization according to claim 4, wherein:

when synchronization to multiple slave banks is needed, the field of the synchronization state is changed to a variable length field, wherein each bit in the variable length field represents the update state of one slave bank.

6. The method for implementing real-time relational database data synchronization according to claim 4, wherein:

the compensation process discovers that the add/delete operation which fails to be synchronized to the slave library is determined by the synchronization status, the last modification time and the current time.

7. The method for implementing real-time relational database data synchronization according to claim 4, wherein:

when the slave library is updated, the time of the updated data is later than the time of the corresponding data in the slave library, otherwise, the updated data is discarded; when the slave library data is updated, if the corresponding record does not exist in the slave library, the updating operation is converted into an inserting operation to add the record in the slave library.

8. The method for implementing real-time relational database data synchronization according to claim 1, wherein:

if the operation of the Java application layer for grabbing and adding, deleting and modifying the master library is a deletion operation, the deletion operation executed on the master library is changed into an updating operation, the synchronization state is updated to be 'to be deleted', the master library is deleted after the deletion operation is executed in the slave library in the synchronization process, and otherwise, the updating synchronization state is 'deletion failure'.

9. The method for implementing real-time relational database data synchronization according to claim 1, wherein:

if the operations of increasing, deleting and modifying the master library are not performed through the operations of increasing, deleting and modifying the master library captured by the Java application layer, the synchronization state and the time for executing the operations of increasing, deleting and modifying the master library are increased in the operations of increasing, deleting and modifying the master library.

10. The method for implementing real-time relational database data synchronization according to claim 1, further comprising:

and an abnormal monitoring process inquiring step, namely updating the synchronous state to be synchronous failure when errors are generated by executing the adding, deleting and modifying operation in the slave library, inquiring the master library by the abnormal monitoring process, giving an alarm if data with synchronous failure exist, and synchronizing the slave library again by the compensation process after the abnormality is eliminated so as to complete data synchronization in the slave library.

Background

Read-write separation is a common technique for solving the performance of relational databases. As shown in fig. 1, an application that needs to read and write accesses the master library, and an application that only needs to read data accesses the slave library. The master library and the slave libraries are synchronized on data through data replication, and the data consistency of the master library and the slave library is ensured. Here, data synchronization is a key technology for read-write separation.

At present, the commonly used mode in the industry is to send the database log of the master library to the slave library through the log mechanism of the relational database, and to play back and execute implementation in the slave library. This method has several disadvantages:

1. the log synchronization needs the bottom support of the database, and commercial database often needs commercial software support, which increases the cost.

2. Master and slave library tables are typically required to be structurally consistent.

3. Strict read-only from the library is usually required.

4. Generally, single-thread synchronization is performed, and the synchronization performance is limited.

Disclosure of Invention

The present invention has been made to solve the above problems, and an object of the present invention is to provide a method for realizing data synchronization of real-time relational databases, which can support all relational databases, can make master-slave table structures inconsistent, can make slave tables not strictly read-only, can perform multithread processing, can perform bidirectional synchronization, and has high fault tolerance.

The invention provides a method for realizing real-time relational database data synchronization, which is characterized by comprising the following steps of:

and (3) a synchronization process: when the data of the master library is subjected to the operations of increasing, deleting and modifying, the Java application layer captures the operations of increasing, deleting and modifying the master library, executes the operations of increasing, deleting and modifying on the master library, asynchronously sends the operations of increasing, deleting and modifying to a message queue, synchronously processes the operations to the slave library, and executes the operations of increasing, deleting and modifying on the slave library;

and (3) compensation process: and setting timing check time, and performing timing check, if finding that the operation of increasing, deleting and modifying the data which is not synchronized to the slave library beyond the preset time, re-synchronizing the operation of increasing, deleting and modifying the data which is not synchronized to the slave library until the data in the slave library is updated completely.

Further, the method for realizing the real-time relational database data synchronization provided by the invention can also have the following characteristics: when the data updated by the main library is grabbed, the Java application program is grabbed through the ORM layer plug-in.

Further, the method for realizing the real-time relational database data synchronization provided by the invention can also have the following characteristics: the message queue is Kafka.

Further, the method for realizing the real-time relational database data synchronization provided by the invention can also have the following characteristics: when the Java application layer grabs the operation of adding and deleting modification to the master library, the synchronous state of the grabbed operation of adding and deleting modification is modified into 'to be synchronous'/'to be deleted', the slave library executes the operation of adding and deleting modification and returns a synchronous result after the synchronization is finished, the synchronous result comprises 'synchronized'/'synchronization failure'/'deletion failure', the synchronous state is updated according to the synchronous result,

and after the Java application layer grabs the operation of increasing, deleting and modifying the master library, adding the current time of the system into the captured operation of increasing, deleting and modifying as the final modifying time.

Further, the method for realizing the real-time relational database data synchronization provided by the invention can also have the following characteristics: when synchronization to multiple slave banks is needed, the field of the synchronization state is changed to a variable length field, wherein each bit in the variable length field represents the update state of one slave bank.

Further, the method for realizing the real-time relational database data synchronization provided by the invention can also have the following characteristics: the compensation process discovers that the add/delete operation which fails to be synchronized to the slave library is determined by the synchronization status, the last modification time and the current time.

Further, the method for realizing the real-time relational database data synchronization provided by the invention can also have the following characteristics: when the slave library is updated, the time of the updated data is later than the time of the corresponding data in the slave library, otherwise, the updated data is discarded; when the slave library data is updated, if the corresponding record does not exist in the slave library, the updating operation is converted into an inserting operation to add the record in the slave library.

Further, the method for realizing the real-time relational database data synchronization provided by the invention can also have the following characteristics: if the operation of the Java application layer for grabbing and adding, deleting and modifying the master library is a deletion operation, the deletion operation executed on the master library is changed into an updating operation, the synchronization state is updated to be 'to be deleted', the master library is deleted after the deletion operation is executed in the slave library in the synchronization process, and otherwise, the updating synchronization state is 'deletion failure'.

Further, the method for realizing the real-time relational database data synchronization provided by the invention can also have the following characteristics: if the operations of increasing, deleting and modifying the master library are not performed through the operations of increasing, deleting and modifying the master library captured by the Java application layer, the synchronization state and the time for executing the operations of increasing, deleting and modifying the master library are increased in the operations of increasing, deleting and modifying the master library.

Further, the method for realizing the real-time relational database data synchronization provided by the invention can also have the following characteristics: the method also comprises an abnormal monitoring process query step, when the operation of adding, deleting and modifying is executed in the slave library to generate errors, the synchronization state is updated to be 'synchronization failure', the abnormal monitoring process queries the master library, if data of the synchronization failure exist, an alarm is given, and after the abnormality is eliminated, the compensation process carries out resynchronization on the slave library to enable the slave library to complete data synchronization.

The invention has the following advantages:

1. the method is realized through a Java application layer, is independent of specific database types, and can support all relational databases.

2. The master-slave table structures may be inconsistent and only part of the fields may be synchronized.

3. The slave library may not be a strictly read-only library, e.g., some fields may not be synchronized on the slave library.

4. The synchronization process and the compensation process can be processed in multiple threads.

5. Additional processing for more flexible service levels may be added, such as: data filtering (filtering certain lines during synchronization); data mapping (some data mapping transformations are performed during synchronization); data checking and cleaning; sub-table synchronization (splitting a master large table into tables of slave libraries according to a certain rule), and the like.

6. Bidirectional synchronization can be achieved.

7. The fault tolerance is high: no matter the message queue or the synchronization process has a fault, the synchronization can be automatically continued through the compensation process after recovery, and the final data consistency is ensured.

Drawings

FIG. 1 is a prior art method for relational database data synchronization.

Fig. 2 is a flow chart of the synchronization process in the present invention.

Fig. 3 is a flow chart of the compensation process in the present invention.

Detailed Description

In order to make the technical means, the creation features, the achievement objectives and the efficacy of the present invention easy to understand, the following embodiments specifically describe the method for implementing the real-time relational database data synchronization according to the present invention with reference to the accompanying drawings.

The method for realizing the real-time relational database data synchronization comprises the following steps:

as shown in fig. 2, the synchronization process: when the data of the master library is subjected to the adding, deleting and modifying operation, the Java application layer grabs the operation of adding, deleting and modifying the master library, namely SQL (INSERT/UPDATE/DELETE), executes the adding, deleting and modifying operation on the master library, asynchronously sends the adding, deleting and modifying operation to a message queue, synchronizes the progress to the slave library, and executes the adding, deleting and modifying operation by the slave library.

In this embodiment, the data updated by the main library is fetched by the Java application through the ORM layer plug-in. Taking JPA as an example: and adding event monitoring PostInsterEventListener/PostUpdateEventListener/PostDeleteEventLister and capturing operation commands for adding and deleting the master library. The message queue is Kafka.

As shown in fig. 3, the compensation process: and setting a timing check time, and if finding that the operation of adding and deleting the data which is not synchronized to the slave library is not synchronized for more than a preset time (for example, 1 minute), synchronizing the operation of adding and deleting the unsynchronized data to the slave library again until the updating in the slave library is completed. The compensation process is used for preventing the loss of messages and the failure of a message queue in the synchronization process, so that partial data cannot be synchronized to the slave library. It can be guaranteed by the compensation process that these data are eventually synchronized to the slave library.

In this embodiment, after the Java application layer grabs the operation of performing addition and deletion modification on the master library, the synchronization state of the grabbed operation of performing addition and deletion modification is modified to "to be synchronized"/"to be deleted", the slave library executes the operation of performing addition and deletion modification and returns a synchronization result after synchronization is completed, the synchronization result includes "synchronized"/"synchronization failure"/"deletion failure", the synchronization state is updated according to the synchronization result, and the updated synchronization state is "synchronized"/"synchronization failure"/"deletion failure".

And after the Java application layer grabs the operation of increasing, deleting and modifying the master library, adding the current time of the system into the captured operation of increasing, deleting and modifying as the final modifying time. The role of adding system time to the operations of adding and deleting modification is two: 1. the compensation process determines data that has not been synchronized for a long time. 2. For preventing out of order while synchronizing.

When the slave library is updated, the UPDATE operation is UPDATE in SQL, i.e., the change in add/delete operation. The time of the updated data is later than that of the corresponding data in the slave library, otherwise, the updated data is discarded; when the slave library data is updated, if the corresponding record does not exist in the slave library, the updating operation is converted into an inserting operation to add the record in the slave library. The method is used for solving the problems of message repetition and sequence disorder.

In this embodiment, the process of compensation finds that the add/delete operation that fails to synchronize to the slave library is determined by the synchronization status, the last modification time, and the current time. And if the synchronization state is 'to be synchronized'/'to be deleted' and the last modification time and the current time are greater than the preset synchronization timeout time, determining that the operation of adding, deleting and modifying the slave library cannot be synchronized.

In this embodiment, when synchronization to multiple slave banks is required, the field of the synchronization status is changed to a variable length field, where each bit in the variable length field represents the update status of one slave bank.

In this embodiment, if the operation of the Java application layer to grab and add and delete the master library is a delete operation, the delete operation performed on the master library is changed to an update operation, and the synchronization state is updated to "to be deleted", and after the synchronization process completes the execution of the delete operation in the slave library, the delete operation is performed on the master library, otherwise, the update synchronization state is "delete failure".

In this embodiment, if the add/delete operation performed on the master library is not performed through the operation of adding/deleting the master library captured by the Java application layer, and may be directly performed through the command of the add/delete operation issued initially, the synchronization status and the time for performing the add/delete operation are increased in the add/delete operation performed on the master library.

In this embodiment, the method for implementing real-time data synchronization of the relational database further includes an exception monitoring process query step, when an error occurs in performing add/delete modify operation in the slave library, the synchronization state is updated to "synchronization failure", the exception monitoring process queries the master library, if data of synchronization failure exists, an alarm is given, and after the exception is removed, the slave library is synchronized again by the compensation process, so that data synchronization is completed in the slave library. Specifically, after exception is eliminated, the synchronization state is updated from "synchronization failure" to-be-synchronized ", and the slave library is synchronized again by the compensation process.

The above embodiments are preferred examples of the present invention, and are not intended to limit the scope of the present invention.