A bidirectional data synchronization method and device for heterogeneous databases

Abstract

The present invention provides a method and device for bidirectional data synchronization of heterogeneous databases, which can be used in the financial field or other technical fields. The method comprises: obtaining a message queue from a message middleware, and synchronizing the data change records of a first database stored in the message queue to a second database; intercepting a maintenance script of the second database, and synchronizing the data of the first database and the second database based on the maintenance script. The device is used to execute the above method. The method and device for bidirectional data synchronization of heterogeneous databases provided in an embodiment of the present invention avoid transaction failures caused by missing data and improve the reliability of the database.

Description

Technical Field

[0001] The present invention relates to the technical field of databases, and in particular to a method and device for bidirectional data synchronization of heterogeneous databases. Background Art

[0002] Traditional databases usually adopt a centralized database architecture. With the continuous development of business, the amount of data and business volume have increased explosively. The centralized database architecture has encountered bottlenecks in scalability and has become increasingly unsuitable for the storage and concurrent processing of massive data.

[0003] At present, the IBM mainframe, DB2 database and DS8000 storage devices used by the banking industry adopt the traditional centralized architecture, which is increasingly unable to meet the needs of banking business development. In the existing technology, it has been proposed to switch from the traditional centralized architecture to a safe and controllable distributed architecture, and replace the DB2 database with an open source distributed MySQL database, which can reduce the cost of system expansion and maintenance. However, the technology of the MySQL database is still in the pilot stage, and the reliability of the MySQL database cannot be fully guaranteed. The usual solution is to deploy the distributed MySQL database and the old DB2 database in parallel for production. If there is a problem with the MySQL database during the pilot process, it can be switched to the DB2 database to provide external services. In the process of parallel operation of the MySQL database and the DB2 database, it is necessary to ensure the data consistency of the MySQL database and the DB2 database to ensure that no errors will occur during business transactions due to data loss. Therefore, how to achieve two-way data synchronization between the MySQL database and the DB2 database has become an important issue that needs to be solved in this field. Summary of the invention

[0004] In view of the problems in the prior art, an embodiment of the present invention provides a method for bidirectional data synchronization of heterogeneous databases, which can at least partially solve the problems in the prior art.

[0005] On the one hand, the present invention provides a method for bidirectional data synchronization of heterogeneous databases, comprising:

[0006] Obtain a message queue from the message middleware, and synchronize the data change records of the first database stored in the message queue to the second database;

[0007] A maintenance script of the second database is intercepted, and data synchronization between the first database and the second database is performed based on the maintenance script.

[0008] In another aspect, the present invention provides a bidirectional data synchronization device for heterogeneous databases, comprising:

[0009] A first synchronization unit, used to obtain a message queue from the message middleware, and synchronize the data change records of the first database stored in the message queue to the second database;

[0010] The second synchronization unit is used to intercept the maintenance script of the second database and perform data synchronization between the first database and the second database based on the maintenance script.

[0011] On the other hand, the present invention provides an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein when the processor executes the program, the steps of the method for bidirectional data synchronization of heterogeneous databases described in any of the above embodiments are implemented.

[0012] In yet another aspect, the present invention provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of the method for bidirectional data synchronization of heterogeneous databases described in any of the above embodiments.

[0013] The method and device for bidirectional data synchronization of heterogeneous databases provided in an embodiment of the present invention obtain a message queue from a message middleware, and synchronize the data change records of a first database stored in the message queue to a second database, intercept a maintenance script of the second database, and synchronize the data of the first database and the second database based on the maintenance script, thereby realizing bidirectional data synchronization of the first database and the second database, ensuring the data consistency of the first database and the second database, avoiding transaction failures due to data missing, and improving the reliability of the database. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0015] Figure 1 The figure is a schematic diagram of the principle of a method for bidirectional data synchronization of heterogeneous databases provided by an embodiment of the present invention.

[0016] Figure 2 It is a flowchart of a method for bidirectional data synchronization of heterogeneous databases provided by an embodiment of the present invention.

[0017] Figure 3 It is a flowchart of a method for bidirectional data synchronization of heterogeneous databases provided by another embodiment of the present invention.

[0018] Figure 4It is a flowchart of a method for bidirectional data synchronization of heterogeneous databases provided by another embodiment of the present invention.

[0019] Figure 5 It is a structural diagram of a data bidirectional synchronization system for heterogeneous databases provided by an embodiment of the present invention.

[0020] Figure 6 It is a flowchart of a data synchronization method for heterogeneous databases provided by an embodiment of the present invention.

[0021] Figure 7 It is a flowchart of a data synchronization method for heterogeneous databases provided by another embodiment of the present invention.

[0022] Figure 8 It is a schematic diagram of the structure of a bidirectional data synchronization device for heterogeneous databases provided by an embodiment of the present invention.

[0023] Fig. 9 It is a schematic diagram of the structure of a bidirectional data synchronization device for heterogeneous databases provided by another embodiment of the present invention.

[0024] Fig.10 It is a schematic diagram of the structure of a bidirectional data synchronization device for heterogeneous databases provided by another embodiment of the present invention.

[0025] Fig.11 It is a schematic diagram of the physical structure of an electronic device provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0026] In order to make the purpose, technical scheme and advantages of the embodiments of the present invention more clear, the embodiments of the present invention are further described in detail below in conjunction with the accompanying drawings. Here, the illustrative embodiments of the present invention and their descriptions are used to explain the present invention, but are not intended to limit the present invention. It should be noted that, in the absence of conflict, the embodiments in this application and the features in the embodiments can be combined with each other arbitrarily.

[0027] Figure 1 FIG. 1 is a schematic diagram showing the principle of a bidirectional data synchronization method for heterogeneous databases provided by an embodiment of the present invention. Figure 1 As shown, the overall process of the bidirectional data synchronization method for heterogeneous databases provided by the embodiment of the present invention can be divided into two parts. One part is to use the message middleware 2 to synchronize the transaction data generated by the first database 1 to the second database through the conversion server 3. The other part is that the conversion server 3 intercepts the maintenance script of the second database, obtains the transaction data that needs to be written to the second database 4 from the maintenance script, and writes the transaction data to both the first database 1 and the second database 4.

[0028] Among them, the first database 1 can be a DB2 database, and the second database 4 can be a MySQL database. The message middleware 2 can use KAFKA message middleware. The conversion server 3 can use Mybatis interceptor when intercepting the maintenance script of the second database. The first database 1 and the second database 4 can be deployed on a server or a server cluster, and can be set according to actual needs, which is not limited in the embodiment of the present invention. The message middleware 2 can be deployed on a server or a server cluster, and can be set according to actual needs, which is not limited in the embodiment of the present invention.

[0029] The following takes the conversion server as an execution subject as an example to illustrate the implementation process of the data bidirectional synchronization method for heterogeneous databases provided by the embodiment of the present invention.

[0030] Figure 2 FIG. 1 is a flow chart of a method for bidirectional synchronization of data in heterogeneous databases provided by an embodiment of the present invention. Figure 2 As shown, the method for bidirectional data synchronization of heterogeneous databases provided by the embodiment of the present invention includes:

[0031] S201, obtaining a message queue from a message middleware, and synchronizing data change records of a first database stored in the message queue to a second database;

[0032] Specifically, the changes in the transaction data of the first database can be copied to the message queue of the message middleware to become the data change record of the first database. The conversion server pulls the data change record of the first database from the message queue, and then synchronizes the data change record of the first database to the second database, thereby synchronizing the changed transaction data of the first database to the second database.

[0033] For example, the conversion server selects the first data to be synchronized from the data change record of the first database according to the filtering condition, then assembles the execution statement of the second database corresponding to the first data to be synchronized according to the first data to be synchronized, and then executes the execution statement of the second database corresponding to the first data to be synchronized to synchronize the first data to be synchronized to the second database. The filtering condition is preset.

[0034] S202: intercept a maintenance script of the second database, and synchronize data between the first database and the second database based on the maintenance script.

[0035] Specifically, the conversion server can intercept the maintenance script of the second database, obtain the transaction data to be changed in the second database from the maintenance script, and write the transaction data to be changed to the first database and the second database, so as to synchronize the changed transaction data in the second database to the first database.

[0036] For example, the conversion server obtains the first type of maintenance script from the maintenance script of the second database, and obtains the second data to be synchronized according to the first type of maintenance script and records the data status as to be synchronized. If the second type of maintenance script obtained from the maintenance script is a submission script, the data interface of the first database is called to write the second data to be synchronized into the first database. After the second data to be synchronized is successfully written into the first database, the second data to be synchronized is written to the second database and the data status of the second data to be synchronized is updated to synchronized.

[0037] The method for bidirectional data synchronization of heterogeneous databases provided in an embodiment of the present invention obtains a message queue from a message middleware, and synchronizes the data change record of a first database stored in the message queue to a second database, intercepts a maintenance script of the second database, and synchronizes the data of the first database and the second database based on the maintenance script, thereby realizing bidirectional data synchronization of the first database and the second database, ensuring the data consistency of the first database and the second database, avoiding transaction failures due to data missing, and improving the reliability of the database.

[0038] Figure 3 FIG. 1 is a flow chart of a method for bidirectional synchronization of data in heterogeneous databases provided by another embodiment of the present invention. Figure 3 As shown, based on the above embodiments, further, synchronizing the data change record of the first database stored in the message queue to the second database includes:

[0039] S2011, filtering out first data to be synchronized from the data change record according to a filtering condition; wherein the filtering condition is preset;

[0040] Specifically, the conversion server screens the data change records of the first database stored in the message queue according to the filtering condition, and uses the data change records that meet the filtering condition as the first data to be synchronized. The filtering condition is preset and is set according to actual needs, and is not limited in the embodiment of the present invention.

[0041] For example, the data change records of the first database stored in the message queue may include the data table name, the operation type, the data before the change and the data after the change. The filtering condition can be set as the data table name, and the data corresponding to the data table name included in the filtering condition is the data that needs to be synchronized. When the conversion server performs data filtering according to the above filtering condition, it will filter out the data change records corresponding to the data table name included in the filtering condition as the first data to be synchronized. Among them, the first data to be synchronized can be stored in the form of a data table, and each data to be synchronized in the data table includes the data table name, the operation type, the data before the change and the data after the change. The data before the change and the data after the change include data fields and corresponding values, and can be data saved in the form of key-value.

[0042] S2012: Assembling an execution statement of a second database corresponding to the first data to be synchronized according to the first data to be synchronized;

[0043] Specifically, after obtaining the first data to be synchronized, the conversion server can generate an execution statement of the second database corresponding to each piece of data included in the first data to be synchronized, and the execution statement of the second database corresponding to each piece of data can synchronize the first data to be synchronized to the second database after execution. The execution statements of the second database corresponding to all the data included in the first data to be synchronized constitute the execution statements of the second database corresponding to the first data to be synchronized.

[0044] For example, the second database is a MySQL database, and the standard format of each piece of data in the first data to be synchronized is: data table name, operation type, data before change and data after change, and the data before change and data after change are saved in key-value form. The operation types include insert, update and delete. If the operation type is insert, the data before change is empty; if the operation type is update, the data before change and the data after change exist at the same time; if the operation type is delete, the data after change is empty.

[0045] The conversion server obtains a piece of data a from the first data to be synchronized, obtains the operation type of data a from data a, and if the operation type of data a is insert, obtains the data table name and the changed data of data a, and assembles the changed data into an INSERT statement according to MySQL statement rules, with the key value included in the changed data as the data table field name and the value value as the content to be inserted corresponding to the data table field name. After execution, the INSERT statement can insert the value value under the key value of the data table corresponding to the data table name. If the operation type of data a is update, then the data table name, data before change and data after change of data a are obtained, and the data before change of data a is used as the query content, and assembled into a SELECT statement according to MySQL statement rules; the key value included in the data after change is the data table field name, and the value value is the content to be inserted corresponding to the data table field name, and assembled into an INSERT statement or UPDATE statement according to MySQL statement rules; if the operation type of data a is delete, then the data table name and data before change of data a are obtained, and the data before change is used as the record to be deleted, and assembled into a DELETE statement according to MySQL statement rules, so as to delete the data before change of data a from the data table corresponding to the data table name.

[0046] S2013: Execute an execution statement of the second database corresponding to the first data to be synchronized to synchronize the first data to be synchronized to the second database.

[0047] Specifically, after obtaining the execution statement of the second database corresponding to the first data to be synchronized, the conversion server will execute the execution statement of the second database corresponding to the first data to be synchronized, and perform data operations on the relevant data tables in the second database, that is, perform at least one of the three data operations of inserting data, updating data and deleting data on the relevant data tables, thereby synchronizing the first data to be synchronized to the second database.

[0048] For example, the second database is a MySQL database. After the conversion server obtains the execution statement of the MySQL database corresponding to the first data to be synchronized, it executes the above execution statements one by one, including but not limited to INSERT statements, SELECT statements, UPDATE statements and DELETE statements. For INSERT statements and UPDATE statements, if the execution result returned after the execution is successful, the conversion server can register the relevant messages and execution results in the message middleware consumption log. If the returned execution result is an execution failure, it can wait for a preset time and then re-execute. If the execution result returned again is an execution success, then register the message middleware consumption log. If the execution result returned again is an execution failure, then the message middleware consumption log is registered. If the execution result returned again is still an execution failure, then the monitoring alarm can be triggered and the alarm log is registered. For DELETE statements, no retry is performed whether the execution fails or not, and the relevant messages and execution results are directly registered in the message middleware consumption log.

[0049] Figure 4 FIG. 1 is a flow chart of a method for bidirectional synchronization of data in heterogeneous databases provided by another embodiment of the present invention. Figure 4 As shown, based on the above embodiments, further, the data synchronization between the first database and the second database based on the maintenance script includes:

[0050] S2021. Obtain a first type of maintenance script from the maintenance script, obtain second data to be synchronized according to the first type of maintenance script, and record the data status as to be synchronized;

[0051] Specifically, when the transaction data of the second database changes, it is necessary to operate the data table of the second database through an execution statement, and the maintenance script will record the above execution statement. The conversion server can obtain the first type of maintenance script from the maintenance script by keyword filtering the maintenance script, and then obtain the second data to be synchronized from the first type of maintenance script, and record the data status of the second data to be synchronized as to be synchronized. The keywords are set according to actual needs, and the embodiment of the present invention does not limit them.

[0052] For example, the second database is a MySQL database, and the execution statements of the MySQL database may include INSERT statements, UPDATE statements, and DELETE statements. The INSERT statement is used to insert data into a data table, the UPDATE statement is used to update existing data in the data table, and the DELETE statement is used to delete existing data in the data table. INSERT, UPDATE, and DELETE are set as keywords of the first type of maintenance scripts, and the maintenance scripts of the MySQL database are filtered by the above keywords, so that the INSERT statement, UPDATE statement, and DELETE statement can be obtained from the maintenance scripts.

[0053] For INSERT statements, the conversion server can parse the INSERT statement to obtain the operation type of insert and the content to be inserted, save the operation type: insert and the content to be inserted into the dual write tracking table, and record the corresponding data status as pending synchronization. For UPDATE statements, the conversion server can parse the UPDATE statement to obtain the operation type of update and the where condition, and then assemble it into a SELECT statement according to the where condition and the MySQL statement rules, execute the above SELECT statement, and obtain the data before the update, and then execute the UPDATE statement to change the data before the update, execute the above SELECT statement again, and obtain the updated data, save the operation type: update, the data before the update, and the updated data into the dual write tracking table, and record the corresponding data status as pending synchronization. For DELETE statements, the conversion server can parse the DELETE statement to obtain the operation type of delete and the where condition, and then assemble it into a SELECT statement according to the where condition and the MySQL statement rules, execute the above SELECT statement, and obtain the data before the deletion, save the operation type: update, and the data before the deletion into the dual write tracking table, and record the corresponding data status as pending synchronization. The second data to be synchronized includes at least one of the content to be inserted, the data before the update, the data after the update, and the data before the deletion, and can be saved in a key-value format.

[0054] S2022: If the second type of maintenance script obtained from the maintenance script is a submission script, calling the data interface of the first database to write the second data to be synchronized into the first database;

[0055] Specifically, the conversion server can obtain the second type of maintenance script from the maintenance script by keyword filtering. If the obtained second type of maintenance script is a commit script, the data interface of the first database can be called to write the second data to be synchronized into the first database.

[0056] For example, the second database is a MySQL database, and the maintenance script of the MySQL database may include a COMMIT script, which is used to commit operations on the second database, that is, to execute the first type of maintenance script to insert, update, and delete data in the MySQL database. COMMIT is set as a keyword of the second type of maintenance script, and the COMMIT script can be filtered from the maintenance script through COMMIT.

[0057] The first database is a DB2 database, and the DB2 database provides a WAS+DB2 interface. After obtaining the COMMIT script, the conversion server calls the WAS+DB2 interface to write the data in the dual-write tracking table with a data status of pending synchronization into the DB2 database. After the data in the data status of pending synchronization is written into the DB2 database, a write result is returned, and the above write result is a write success or a write failure.

[0058] S2023: If the second data to be synchronized is successfully written into the first database, write the second data to be synchronized into the second database and update the data status of the second data to be synchronized to synchronized.

[0059] Specifically, if the conversion server receives feedback information that the second data to be synchronized is successfully written into the first database, it confirms that the second data to be synchronized is successfully written into the first database, and then writes the second data to be synchronized into the second database and updates the data status of the second data to be synchronized to synchronized.

[0060] For example, the second database is a MySQL database. After the conversion server receives the above write result, if the write result is successful, it is confirmed that the second data to be synchronized is successfully written into the first database. The conversion server will perform a COMMIT operation on the intercepted first-class maintenance script of the MySQL database to write the second data to be synchronized into the second database. The conversion server will update the corresponding data status in the dual-write tracking table to synchronized.

[0061] On the basis of the above embodiments, further, the method for bidirectional data synchronization of heterogeneous databases provided in the embodiment of the present invention also includes:

[0062] If the second type of maintenance script obtained from the maintenance script is a rollback script, the entire transaction is directly rolled back and the data status of the second data to be synchronized is updated to no synchronization required.

[0063] Specifically, the conversion server can obtain the second type of maintenance script from the maintenance script by keyword filtering the maintenance script. If the obtained second type of maintenance script is a rollback (ROLLBACK) script, the entire transaction can be directly rolled back and the data status of the second data to be synchronized can be updated to not require synchronization.

[0064] For example, the second database is a MySQL database, and the maintenance script of the MySQL database may include a ROLLBACK script, which is used to cancel the operation on the second database, that is, not to execute the first type of maintenance script. ROLLBACK is set as a keyword of the second type of maintenance script, and the ROLLBACK script can be filtered from the maintenance script by the keyword ROLLBACK.

[0065] After obtaining the ROLLBACK script, the conversion server does not execute the intercepted first-type maintenance script, and updates the corresponding data status in the double-write tracking table to no synchronization required.

[0066] On the basis of the above embodiments, further, the method for bidirectional data synchronization of heterogeneous databases provided in the embodiment of the present invention also includes:

[0067] If the second data to be synchronized fails to be written into the first database, the entire transaction is rolled back and the data status of the second data to be synchronized is updated to synchronization failure.

[0068] Specifically, if the conversion server receives feedback information that the second data to be synchronized has failed to be written into the first database, then it confirms that the second data to be synchronized has failed to be written into the first database. In order to ensure the consistency of data in the first database and the second database, the conversion server will roll back the entire transaction and update the data status of the second data to be synchronized to synchronization failure.

[0069] For example, if the second database is a MySQL database, after the conversion server receives the writing result of the second data to be synchronized being written into the first database, if the writing result is a writing failure, then it is confirmed that the writing of the second data to be synchronized into the first database has failed. The conversion server will perform a rollback operation on the intercepted first-class maintenance script of the MySQL database, that is, will not execute the intercepted first-class maintenance script, and will update the corresponding data status in the dual-write tracking table to synchronization failure.

[0070] On the basis of the above embodiments, further, obtaining the message queue from the message middleware includes:

[0071] The message queue is obtained from the message middleware periodically.

[0072] Specifically, the conversion server periodically obtains the message queue from the message middleware and synchronizes the changed transaction data in the first database to the second database. The periodicity is set according to actual needs, for example, and is not limited in the embodiment of the present invention.

[0073] Figure 5 FIG. 1 is a schematic diagram of a bidirectional data synchronization system for heterogeneous databases provided by an embodiment of the present invention. Figure 5 As shown, the data bidirectional synchronization system of heterogeneous databases provided by the embodiment of the present invention includes an old platform 51, a middleware server 52, a conversion server 53 and a new platform 54, wherein:

[0074] The old platform 51 is connected to the middleware server 52, and the conversion server 53 is connected to the old platform 51, the middleware server 52 and the new platform 54. The old platform 51 is configured with a DB2 database, the middleware server 52 is configured with a KAFKA message middleware, the conversion server 53 is configured with a Mybatis interceptor, and the new platform 54 is configured with a MySQL database.

[0075] The old platform 51 stores the changed transaction data in the DB2 database, and at the same time copies the changed transaction data to the message queue of the KAFKA message middleware of the middleware server 52. The conversion server 53 periodically pulls the data change records of the first database in the message queue of the KAFKA message middleware from the middleware server 52, and synchronizes them to the MySQL database of the new platform 54.

[0076] The transaction data generated on the new platform 54 will be stored in the MySQL database through the maintenance script. The conversion server 53 intercepts the maintenance script of the MySQL database through the Mybatis interceptor, obtains the second data to be synchronized from the maintenance script, calls the WAS+DB2 interface of the DB2 database to write the second data to be synchronized into the DB2 database, and after the writing is successful, writes the second data to be synchronized into the MySQL database.

[0077] Figure 6 FIG. 1 is a flow chart of a method for synchronizing data in a heterogeneous database according to an embodiment of the present invention. Figure 6 As shown, the data synchronization method for heterogeneous databases provided by the embodiment of the present invention is applied to Figure 5 The heterogeneous database data bidirectional synchronization system shown in the figure realizes data synchronization from DB2 database to MySQL database. The specific process is as follows:

[0078] The first step is to pull data. The old platform 51 stores the changed transaction data in the DB2 database, and at the same time copies the changed transaction data to the message queue of the KAFKA message middleware of the middleware server 52. The conversion server 53 regularly pulls the data change records of the first database in the message queue of the KAFKA message middleware from the middleware server 52.

[0079] Step 2: Filter data. The conversion server 53 filters out the first data to be synchronized from the data change records of the first database according to the filtering condition, wherein the filtering condition is preset.

[0080] Step 3: Determine the operation type. The conversion server 53 can obtain the operation type of each piece of data from the first data to be synchronized. Each piece of data in the first data to be synchronized includes a data table name, an operation type, data before the change, and data after the change. If the operation type is DELETE, then proceed to step 4; if the operation type is UPDATE, then proceed to step 6; if the operation type is INSERT, then proceed to step 10.

[0081] Step 4: Assemble a DELETE statement. The conversion server 53 obtains the data table name and the data before the change of the data, takes the data before the change as the record to be deleted, and assembles a DELETE statement according to the MySQL statement rules.

[0082] Step 5: Execute the DELETE statement. The conversion server 53 executes the assembled DELETE statement, obtains the execution result of the DELETE statement, and then proceeds to step 16.

[0083] Step 6: Assemble SELECT statements: The conversion server 53 obtains the data before the change of the data as the query content, and assembles it into a SELECT statement according to the MySQL statement rules.

[0084] Step 7: Determine whether a record is found. The conversion server 53 executes the assembled SELECT statement to obtain the execution result of the SELECT statement. The execution result is whether there is a record or no record. If there is no record, then go to step 9; if there is a record, go to step 8.

[0085] Step 8: Assemble and execute the UPDATE statement. The conversion server 53 assembles the changed data of the data into an UPDATE statement according to the MySQL statement rules, with the key value of the data table field name and the value value of the content to be inserted corresponding to the data table field name, and then executes the UPDATE statement to obtain the execution result of the UPDATE statement, and then proceeds to step 12.

[0086] Step 9: Convert to INSERT statement: The conversion server 53 assembles the changed data into an INSERT statement according to the MySQL statement rules, using the key value of the data table field name and the value value of the content to be inserted corresponding to the data table field name.

[0087] Step 10: Assemble INSERT statement: The conversion server 53 assembles the changed data into an INSERT statement according to MySQL statement rules, using the key value of the changed data as the data table field name and the value value as the content to be inserted corresponding to the data table field name.

[0088] Step 11: Execute the INSERT statement. The conversion server 53 executes the assembled INSERT statement and obtains the execution result of the INSERT statement.

[0089] Step 12: Determine whether the execution is normal. The conversion server 53 determines whether the execution is normal according to the execution result. If the execution result is successful, the execution is normal and the process goes to step 16; if the execution result is failed, the execution is abnormal and the process goes to step 13.

[0090] Step 13: Re-execution: After waiting for 5 seconds, the conversion server 53 re-executes the INSERT statement or UPDATE statement corresponding to the failed execution result to obtain the execution result again.

[0091] Step 14: Determine whether the execution is normal. The conversion server 53 determines whether the execution is normal according to the re-obtained execution result. If the re-obtained execution result is successful, the execution is normal and the process goes to step 16; if the re-obtained execution result is failed, the execution is abnormal and the process goes to step 15.

[0092] Step 15: abnormal alarm: The conversion server 53 generates abnormal alarm and registers the alarm log.

[0093] Step 16: Register the consumption log. The execution results of the DELETE statement are recorded in the Kafka consumption log truthfully, whether the execution result is normal or abnormal. The execution results of the INSERT statement and UPDATE statement are recorded in the Kafka consumption log only after the execution is successful.

[0094] Step 17: Determine whether synchronization is completed. The conversion server 53 determines whether there is any unsynchronized data in the first data to be synchronized. If yes, the process returns to step 3; if no unsynchronized data, the data synchronization is completed.

[0095] Figure 7 FIG. 1 is a flow chart of a method for synchronizing data in a heterogeneous database according to another embodiment of the present invention. Figure 7 As shown, the data synchronization method for heterogeneous databases provided by the embodiment of the present invention is applied to Figure 5 The heterogeneous database data bidirectional synchronization system shown in the figure realizes data synchronization from MySQL database to DB2 database. The specific process is as follows:

[0096] The first step is to intercept the maintenance script. The conversion server 53 intercepts the maintenance script of the MySQL database through the Mybatis interceptor.

[0097] Step 2: Determine whether there is a first-category maintenance script. The conversion server 53 determines whether there are three keywords, INSERT, UPDATE, or DELETE, in the execution statement of the maintenance script. If there is any of the three keywords, then there is a first-category maintenance script, and the process goes to step 3. If there are no three keywords, then there is no first-category maintenance script, and the process goes to step 6.

[0098] Step 3: Parse and obtain the second data to be synchronized. The conversion server 53 parses the first type of maintenance script to obtain the second data to be synchronized.

[0099] Step 4: Save data. The conversion server 53 saves the obtained second data to be synchronized into the dual write tracking table, and records the corresponding data status as to be synchronized.

[0100] Step 5: Loading data into the memory. The conversion server 53 loads the second to-be-synchronized data in the dual-write tracking table whose data status is to-be-synchronized into the memory.

[0101] Step 6: Determine whether it is a second-class maintenance script. The conversion server 53 determines whether the execution statement of the maintenance script contains the two keywords COMMIT and ROLLBACK. If the keyword COMMIT is present, then a second-class maintenance script exists, and the process proceeds to step 7; if the keyword ROLLBACK is present, then a second-class maintenance script exists, and the process proceeds to step 12; if the above two keywords are not present, then no second-class maintenance script exists, and the process proceeds to step 13.

[0102] Step 7: Obtain the second data to be synchronized. The conversion server 53 obtains the second data to be synchronized whose data status is to be synchronized from the memory.

[0103] Step 8: Writing data: The conversion server 53 calls the WAS+DB2 interface of the DB2 database to write the second data to be synchronized into the DB2 database and obtains the writing result of the data.

[0104] Step 9: Determine whether the writing is successful. The conversion server 53 determines whether the data is successfully written into the DB2 database according to the writing result. If the writing result is a writing failure, then the process goes to step 11; if the writing result is a writing success, then the process goes to step 10.

[0105] Step 10: Execute COMMIT operation: The conversion server 53 executes COMMIT operation, that is, executes the intercepted first type maintenance script.

[0106] Step 11: Roll back the entire transaction. After the write fails, the conversion server 53 rolls back the entire transaction, that is, does not execute the intercepted first-type maintenance script, and changes the data state of the second to-be-synchronized data in the dual-write tracking table from to-be-synchronized to synchronization failure.

[0107] Step 12: Roll back the entire transaction. After obtaining the ROLLBACK script, the conversion server 53 rolls back the entire transaction, that is, it does not execute the intercepted first-class maintenance script, and changes the data status of the second to-be-synchronized data in the dual-write tracking table from to-be-synchronized to not-needed-to-be-synchronized.

[0108] The method for bidirectional data synchronization of heterogeneous databases provided in an embodiment of the present invention combines the use of message middleware for data replication and database synchronization double-write technology, and can perform bidirectional data synchronization on the data of two heterogeneous databases through data replication or double-write. During pilot transactions, the data integrity of the business dimension is guaranteed for the transaction accounts in the pilot list, thereby providing data consistency guarantee during the migration and switchback of the old and new systems, avoiding transaction errors due to data missing.

[0109] Figure 8 FIG. 1 is a schematic diagram of a bidirectional data synchronization device for heterogeneous databases provided by an embodiment of the present invention. Figure 8 As shown, the bidirectional data synchronization device for heterogeneous databases provided in the embodiment of the present invention includes a first synchronization unit 801 and a second synchronization unit 802, wherein:

[0110] The first synchronization unit 801 is used to obtain a message queue from the message middleware, and synchronize the data change records of the first database stored in the message queue to the second database; the second synchronization unit 802 is used to intercept the maintenance script of the second database, and synchronize the data of the first database and the second database based on the maintenance script.

[0111] Specifically, changes in the transaction data of the first database can be copied to the message queue of the message middleware to become data change records of the first database. The first synchronization unit 801 pulls the data change records of the first database from the message queue, and then synchronizes the data change records of the first database to the second database, thereby synchronizing the changed transaction data of the first database to the second database.

[0112] The second synchronization unit 802 can intercept the maintenance script of the second database, obtain the transaction data to be changed in the second database from the maintenance script, and write the transaction data to be changed to the first database and the second database, so as to synchronize the changed transaction data in the second database to the first database.

[0113] The bidirectional data synchronization device for heterogeneous databases provided in an embodiment of the present invention obtains a message queue from a message middleware, and synchronizes the data change records of a first database stored in the message queue to a second database, intercepts a maintenance script of the second database, and synchronizes the data of the first database and the second database based on the maintenance script, thereby realizing bidirectional data synchronization between the first database and the second database, ensuring data consistency between the first database and the second database, avoiding transaction failures due to data missing, and improving the reliability of the database.

[0114] Fig. 9 FIG. 1 is a schematic diagram of a bidirectional data synchronization device for heterogeneous databases provided by another embodiment of the present invention. Fig. 9 As shown, based on the above embodiments, further, the first synchronization unit 801 includes a screening subunit 8011, an assembling subunit 8012 and an executing subunit 8013, wherein:

[0115] The screening subunit 8011 is used to screen out the first data to be synchronized from the data change record according to the filtering condition; wherein the filtering condition is preset; the assembling subunit 8012 is used to assemble the execution statement of the second database corresponding to the first data to be synchronized according to the first data to be synchronized; the executing subunit 8013 is used to execute the execution statement of the second database corresponding to the first data to be synchronized to synchronize the first data to be synchronized to the second database.

[0116] Specifically, the screening subunit 8011 screens the data change records of the first database stored in the message queue according to the filtering condition, and uses the data change records that meet the filtering condition as the first data to be synchronized. The filtering condition is preset and is set according to actual needs, and the embodiment of the present invention does not limit it.

[0117] After obtaining the first data to be synchronized, the assembly subunit 8012 can generate an execution statement of the second database corresponding to each piece of data included in the first data to be synchronized, and the execution statement of the second database corresponding to each piece of data can synchronize the first data to be synchronized to the second database after execution. The execution statements of the second database corresponding to all the data included in the first data to be synchronized constitute the execution statements of the second database corresponding to the first data to be synchronized.

[0118] After obtaining the execution statement of the second database corresponding to the first data to be synchronized, the execution sub-unit 8013 executes the execution statement of the second database corresponding to the first data to be synchronized, and performs data operations on the relevant data tables in the second database, that is, at least one of the three data operations of inserting data, updating data and deleting data on the relevant data tables, thereby synchronizing the first data to be synchronized to the second database.

[0119] Fig.10 FIG. 1 is a schematic diagram of a bidirectional data synchronization device for heterogeneous databases provided by another embodiment of the present invention. Fig.10 As shown, based on the above embodiments, further, the second synchronization unit 802 includes an obtaining subunit 8021, a calling subunit 8022 and a writing subunit 8023, wherein:

[0120] The obtaining sub-unit 8021 is used to obtain the first type of maintenance script from the maintenance script, and obtain the second data to be synchronized according to the first type of maintenance script and record the data status as to be synchronized; the calling sub-unit 8022 is used to call the data interface of the first database to write the second data to be synchronized into the first database if the second type of maintenance script obtained from the maintenance script is a submission script; the writing sub-unit 8023 is used to write the second data to be synchronized to the second database and update the data status of the second data to be synchronized to synchronized after the second data to be synchronized is successfully written into the first database.

[0121] Specifically, the acquisition subunit 8021 screens the data change records of the first database stored in the message queue according to the filtering condition, and uses the data change records that meet the filtering condition as the first data to be synchronized. The filtering condition is preset and is set according to actual needs, and is not limited in the embodiment of the present invention.

[0122] After obtaining the first data to be synchronized, the calling subunit 8022 can generate an execution statement of the second database corresponding to each piece of data included in the first data to be synchronized, and the execution statement of the second database corresponding to each piece of data can synchronize the first data to be synchronized to the second database after execution. The execution statements of the second database corresponding to all the data included in the first data to be synchronized constitute the execution statements of the second database corresponding to the first data to be synchronized.

[0123] After obtaining the execution statement of the second database corresponding to the first data to be synchronized, the writing sub-unit 8023 executes the execution statement of the second database corresponding to the first data to be synchronized, and performs data operations on the relevant data tables in the second database, that is, at least one of the three data operations of inserting data, updating data and deleting data on the relevant data tables, thereby synchronizing the first data to be synchronized to the second database.

[0124] The embodiments of the apparatus provided in the embodiments of the present invention can be specifically used to execute the processing flow of the above-mentioned method embodiments, and its functions are not described in detail here, and reference can be made to the detailed description of the above-mentioned method embodiments.

[0125] It should be noted that the method and device for bidirectional data synchronization of heterogeneous databases provided in the embodiments of the present invention can be used in the financial field, and can also be used in any technical field other than the financial field. The embodiments of the present invention do not limit the application field of the method and device for bidirectional data synchronization of heterogeneous databases.

[0126] Fig.11 is a schematic diagram of the physical structure of an electronic device provided by an embodiment of the present invention, such as Fig.11 As shown, the electronic device may include: a processor 1101, a communication interface 1102, a memory 1103 and a communication bus 1104, wherein the processor 1101, the communication interface 1102 and the memory 1103 communicate with each other through the communication bus 1104. The processor 1101 may call the logic instructions in the memory 1103 to execute the following method: obtain a message queue from a message middleware, and synchronize the data change records of the first database stored in the message queue to the second database; intercept the maintenance script of the second database, and synchronize the data of the first database and the second database based on the maintenance script.

[0127] In addition, the logic instructions in the above-mentioned memory 1103 can be implemented in the form of software functional units and can be stored in a computer-readable storage medium when sold or used as an independent product. Based on such an understanding, the technical solution of the present invention, in essence, or the part that contributes to the prior art or the part of the technical solution, can be embodied in the form of a software product, which is stored in a storage medium and includes several instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the method described in each embodiment of the present invention. The aforementioned storage medium includes: various media that can store program codes, such as a USB flash drive, a mobile hard disk, a read-only memory (ROM), a random access memory (RAM), a magnetic disk or an optical disk.

[0128] The present embodiment discloses a computer program product, which includes a computer program stored on a non-transitory computer-readable storage medium, and the computer program includes program instructions. When the program instructions are executed by a computer, the computer can execute the methods provided by the above-mentioned method embodiments, for example, including: obtaining a message queue from a message middleware, and synchronizing the data change records of a first database stored in the message queue to a second database; intercepting a maintenance script of the second database, and synchronizing the data of the first database and the second database based on the maintenance script.

[0129] This embodiment provides a computer-readable storage medium, which stores a computer program. The computer program enables the computer to execute the methods provided by the above-mentioned method embodiments, for example, including: obtaining a message queue from a message middleware, and synchronizing the data change records of a first database stored in the message queue to a second database; intercepting a maintenance script of the second database, and synchronizing the data of the first database and the second database based on the maintenance script.

[0130] Those skilled in the art will appreciate that embodiments of the present invention may be provided as methods, systems, or computer program products. Therefore, the present invention may take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware. Moreover, the present invention may take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0131] The present invention is described with reference to flowcharts and / or block diagrams of methods, devices (systems), and computer program products according to embodiments of the present invention. It should be understood that each process and / or block in the flowchart and / or block diagram, as well as the combination of processes and / or blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0132] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0133] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.

[0134] In the description of this specification, the description with reference to the terms "one embodiment", "a specific embodiment", "some embodiments", "for example", "example", "specific example", or "some examples" means that the specific features, structures, materials or characteristics described in conjunction with the embodiment or example are included in at least one embodiment or example of the present invention. In this specification, the schematic representation of the above terms does not necessarily refer to the same embodiment or example. Moreover, the specific features, structures, materials or characteristics described may be combined in any one or more embodiments or examples in a suitable manner.

[0135] The specific embodiments described above further illustrate the objectives, technical solutions and beneficial effects of the present invention in detail. It should be understood that the above description is only a specific embodiment of the present invention and is not intended to limit the scope of protection of the present invention. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present invention should be included in the scope of protection of the present invention.

Claims

1. A method for bidirectional data synchronization of heterogeneous databases, characterized in that: include: Obtain a message queue from the message middleware, and synchronize the data change records of the first database stored in the message queue to the second database; intercepting a maintenance script of the second database, and performing data synchronization between the first database and the second database based on the maintenance script; The intercepting of the maintenance script of the second database and synchronizing the data of the first database and the second database based on the maintenance script includes: Intercept the maintenance script of the second database, obtain the transaction data to be changed in the second database from the maintenance script, and write the transaction data to be changed to the first database and the second database, so as to synchronize the changed transaction data in the second database to the first database.

2. The method according to claim 1, characterized in that The step of synchronizing the data change records of the first database stored in the message queue to the second database comprises: Filtering out first data to be synchronized from the data change record according to a filtering condition; wherein the filtering condition is preset; Assembling an execution statement of a second database corresponding to the first data to be synchronized according to the first data to be synchronized; An execution statement of the second database corresponding to the first data to be synchronized is executed to synchronize the first data to be synchronized into the second database.

3. The method according to claim 1, characterized in that The synchronizing the data of the first database and the second database based on the maintenance script includes: Obtaining a first type of maintenance script from the maintenance script, and obtaining second data to be synchronized according to the first type of maintenance script and recording the data status as to be synchronized; If the second type of maintenance script obtained from the maintenance script is a submission script, calling the data interface of the first database to write the second data to be synchronized into the first database; If the second data to be synchronized is successfully written into the first database, the second data to be synchronized is written into the second database and the data status of the second data to be synchronized is updated to synchronized.

4. The method according to claim 3, characterized in that Also includes: If the second type of maintenance script obtained from the maintenance script is a rollback script, the entire transaction is directly rolled back and the data status of the second data to be synchronized is updated to no synchronization required.

5. The method according to claim 3, characterized in that: Also includes: If the second data to be synchronized fails to be written into the first database, the entire transaction is rolled back and the data status of the second data to be synchronized is updated to synchronization failure.

6. The method according to any one of claims 1 to 5, characterized in that: The obtaining of the message queue from the message middleware comprises: The message queue is obtained from the message middleware periodically.

7. A bidirectional data synchronization device for heterogeneous databases, characterized in that: include: A first synchronization unit, used to obtain a message queue from the message middleware, and synchronize the data change records of the first database stored in the message queue to the second database; a second synchronization unit, configured to intercept a maintenance script of the second database, and perform data synchronization between the first database and the second database based on the maintenance script; Among them, the second synchronization unit is specifically used to intercept the maintenance script of the second database, obtain the transaction data to be changed in the second database from the maintenance script, and write the transaction data to be changed to the first database and the second database, so as to synchronize the changed transaction data in the second database to the first database.

8. The device according to claim 7, characterized in that The first synchronization unit comprises: A screening subunit, configured to screen out first data to be synchronized from the data change record according to a screening condition; wherein the screening condition is preset; an assembling subunit, configured to assemble, according to the first data to be synchronized, an execution statement of a second database corresponding to the first data to be synchronized; The execution subunit is used to execute the execution statement of the second database corresponding to the first data to be synchronized to synchronize the first data to be synchronized to the second database.

9. The device according to claim 7, characterized in that The second synchronization unit comprises: an obtaining subunit, configured to obtain a first type of maintenance script from the maintenance script, obtain second data to be synchronized according to the first type of maintenance script, and record the data status as to be synchronized; a calling subunit, configured to call a data interface of the first database to write the second data to be synchronized into the first database if the second type of maintenance script obtained from the maintenance script is a submission script; The writing subunit is used to write the second data to be synchronized into the second database and update the data status of the second data to be synchronized to synchronized after the second data to be synchronized is successfully written into the first database.

10. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the computer program, the steps of the method according to any one of claims 1 to 6 are implemented.

11. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 6 are implemented.

Images