Database data synchronization method and device, electronic equipment and storage medium

Abstract

The invention discloses a database data synchronization method. The method comprises the following steps: acquiring a to-be-synchronized business data log of a source end database; obtaining the existing log data of each field of the last log data; analyzing the existing log data according to the data dictionary to obtain original business data; traversing historical log data of the fields; traversing each field; if the analysis is successful, outputting the original business data corresponding to the historical log data of the current field; if the analysis fails, judging whether the type of the source end database is a database of which the column type can be modified or not; if yes, determining a corresponding latest column modification statement in the DDL log; according to a previous statement of the latest column modification statement, determining a historical type of the column; and analyzing the historical log data of the current field according to the historical type of the column. According to the method, the existing log data and the historical log data of the database field can be completely restored by combining the data dictionary with the DDL log, and the reliability and the integrity of data synchronization are improved.

Description

Technical Field

[0001] The present invention relates to the technical field of database data processing, and in particular to a database data synchronization method, device, electronic device and storage medium. Background Art

[0002] In the field of data synchronization, it is usually necessary to synchronize the business data of the source database to the target database in real time or periodically to ensure data consistency, support data analysis, or achieve disaster recovery. During the database synchronization process, it is necessary to parse the original business data from the data log of the source database according to the data type defined for each field in the data log, and then accurately synchronize this data to the target database. However, some database systems allow the data type of a column to be dynamically modified when the table already stores business data. For example, an administrator may change a column from VARCHAR type to INT, or adjust it from DATETIME to TIMESTAMP. This dynamic modification will cause the historical records in the data log to be inconsistent with the data type definition of the current column. Specifically, the logs generated before the data type change still store data according to the old type, while the logs after the change are stored in the new type. If the data synchronization system only parses all historical logs based on the latest data type definition, parsing errors will occur due to data type mismatch, which will eventually lead to data truncation, format errors, or even process crashes during the synchronization process, resulting in data synchronization failure and affecting the reliability and integrity of data synchronization. Summary of the invention

[0003] In order to solve the above problems existing in the prior art, the present invention provides a database data synchronization method, device, electronic device and storage medium. The technical problem to be solved by the present invention is achieved through the following technical solutions: A first aspect of an embodiment of the present invention provides a method for synchronizing database data, comprising the following steps: In response to a request to synchronize business data, obtain a business data log to be synchronized from a source database; Obtain existing log data for each field of the last log data in the business data log to be synchronized; Parse the existing log data of each field according to the data dictionary to obtain the original business data corresponding to the existing log data of each field; Traversing the historical log data of the field: traversing each log data from the log data before the last log data in the business data log to be synchronized; Traverse each field: parse the historical log data of each field in the current log data; If the parsing is successful, the original business data corresponding to the historical log data of the current field is output, and the step of traversing each field is returned; If the parsing fails, determine whether the source database is a database of a modifiable column type; If yes, determine the corresponding latest column modification statement in the DDL log according to the column identifier corresponding to the current field; Determining a history type of a column according to a previous statement of the latest column modification statement; Parse the historical log data of the current field according to the historical type of the column, obtain the original business data corresponding to the historical log data of the current field, return to the step of traversing each field, until all fields in the current log data are traversed, and return to the step of traversing the historical log data of the traversed field.

[0004] In one embodiment of the present invention, parsing the existing log data of each field according to the data dictionary to obtain the original business data corresponding to the existing log data of each field includes: Determine the existing data type for each field in the data dictionary; The existing log data of the field is parsed according to the existing data type to obtain original business data corresponding to the existing log data of each field.

[0005] In one embodiment of the present invention, parsing the historical log data of each field in the current log data includes: The historical log data of each field in the current log data is parsed according to the existing data type.

[0006] In one embodiment of the present invention, the method further includes: if the type of the source database is not a database of a modifiable column type, outputting parsing exception information and returning to the step of traversing each field.

[0007] A second aspect of an embodiment of the present invention provides a database data synchronization device, including: A first acquisition module, configured to obtain a log of business data to be synchronized from a source database in response to a request for synchronizing business data; A second acquisition module is used to acquire the existing log data of each field of the last log data in the business data log to be synchronized; A first parsing module, used to parse the existing log data of each field according to a data dictionary to obtain original business data corresponding to the existing log data of each field; A traversal module, used for traversing the historical log data of a field: traversing each log data from the log data before the last log data in the business data log to be synchronized; The second parsing module is used to traverse each field: parse the historical log data of each field in the current log data; A first output module, configured to output the original business data corresponding to the historical log data of the current field if the parsing is successful, and return to the step of traversing each field; A judgment module, used for judging whether the type of the source database is a database of a modifiable column type if the parsing fails; A search module, configured to, if yes, determine the corresponding latest column modification statement in the DDL log according to the column identifier corresponding to the current field; A determination module, configured to determine a history type of a column according to a previous statement of the latest column modification statement; The third parsing module is used to parse the historical log data of the current field according to the historical type of the column, obtain the original business data corresponding to the historical log data of the current field, return to the step of traversing each field until all fields in the current log data are traversed, and return to the step of traversing the historical log data of the traversed field.

[0008] In one embodiment of the present invention, parsing the existing log data of each field according to the data dictionary to obtain the original business data corresponding to the existing log data of each field includes: Determine the existing data type for each field in the data dictionary; The existing log data of the field is parsed according to the existing data type to obtain original business data corresponding to the existing log data of each field.

[0009] In one embodiment of the present invention, parsing the historical log data of each field in the current log data includes: The historical log data of each field in the current log data is parsed according to the existing data type.

[0010] In one embodiment of the present invention, it further includes: a second output module, which is used to output parsing exception information and return to the step of traversing each field if the type of the source database is not a database of a modifiable column type.

[0011] A third aspect of an embodiment of the present invention provides an electronic device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein when the processor executes the program, a database data synchronization method provided by the first aspect of an embodiment of the present invention is implemented.

[0012] A fourth aspect of an embodiment of the present invention provides a computer-readable storage medium having a computer program stored thereon. When the computer program is executed by a processor, the method for synchronizing database data provided by the first aspect of an embodiment of the present invention is implemented.

[0013] Beneficial effects of the present invention: The present invention can completely restore the current data and historical log data of the database fields through the data dictionary combined with the DDL log, and then synchronize the restored data, so that all the data of the source database can be completely synchronized to the target data, avoiding parsing errors caused by the mismatch between the historical log data type and the current data, and avoiding data synchronization failure caused by data truncation and format errors during the synchronization process, thereby improving the reliability and integrity of data synchronization.

[0014] Other features and advantages of the present invention will be described in the following description, and partly become apparent from the description, or understood by practicing the present invention. The purpose and other advantages of the present invention can be realized and obtained by the structures particularly pointed out in the written description, claims, and drawings.

[0015] The technical solution of the present invention is further described in detail below through the accompanying drawings and embodiments. BRIEF DESCRIPTION OF THE DRAWINGS

[0016] The accompanying drawings are used to provide a further understanding of the present invention and constitute a part of the specification. Together with the embodiments of the present invention, they are used to explain the present invention and do not constitute a limitation of the present invention. In the accompanying drawings: Figure 1 A schematic diagram of a flow chart of a database data synchronization method provided by an embodiment of the present invention; Figure 2 A schematic block diagram of a database data synchronization device provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0017] The present invention is further described in detail below with reference to specific embodiments, but the embodiments of the present invention are not limited thereto.

[0018] like Figure 1 As shown, a first aspect of an embodiment of the present invention provides a database data synchronization method, comprising the following steps: Step 11: In response to a request to synchronize business data, obtain a log of business data to be synchronized from a source database.

[0019] Step 12: Obtain the existing log data of each field of the last log data in the business data log to be synchronized.

[0020] Step 13: parse the existing log data of each field according to the data dictionary to obtain the original business data corresponding to the existing log data of each field.

[0021] Step 14, traverse the historical log data of the field: traverse each log data from the previous log data of the last log data in the business data log to be synchronized.

[0022] Step 15, traverse each field: parse the historical log data of each field in the current log data.

[0023] Step 16: If the parsing is successful, the original business data corresponding to the historical log data of the current field is output, and the step of traversing each field is returned.

[0024] Step 17: If the parsing fails, determine whether the source database is a database of a modifiable column type.

[0025] Step 18: If yes, determine the latest column modification statement in the DDL log according to the column identifier corresponding to the current field. DDL is a data definition language.

[0026] Step 19, determine the history type of the column based on the previous statement of the latest column modification statement.

[0027] Step 20, parse the historical log data of the current field according to the historical type of the column, obtain the original business data corresponding to the historical log data of the current field, return to the step of traversing each field, until all fields in the current log data are traversed, and return to the step of traversing the historical log data of the field.

[0028] In this embodiment, the current data and historical log data of the database fields can be completely restored through the data dictionary combined with the DDL log, and then the restored data can be synchronized, so that all the data of the source database can be completely synchronized to the target data, avoiding parsing errors caused by the mismatch between the historical log data type and the current data, and avoiding data truncation and format errors during the synchronization process that lead to data synchronization failure, thereby improving the reliability and integrity of data synchronization.

[0029] In a feasible implementation, the method of this embodiment is applied to a third-party synchronization tool.

[0030] On the basis of the first aspect of the embodiment of the present invention, the second aspect of the embodiment of the present invention further describes a database data synchronization method in detail. The second aspect of the embodiment of the present invention provides a database data synchronization method, comprising the following steps: Step 21, in response to a request to synchronize business data, obtain a log of business data to be synchronized from a source database.

[0031] In this step, when the third-party data synchronization party needs to synchronize all historical and current data of the source database, it can only synchronize historical and current data based on the business data log.

[0032] The third-party synchronization tool receives the request for synchronizing business data and responds to the request, obtaining the business data log to be synchronized of the source database indicated by the request.

[0033] Step 22: Obtain the existing log data of each field of the last log data in the business data log to be synchronized.

[0034] In this step, the last log data in the business data log to be synchronized is the latest data, and the existing log data of each field is the latest recorded data of the field during synchronization.

[0035] Step 23: parse the existing log data of each field according to the data dictionary to obtain the original business data corresponding to the existing log data of each field. The data dictionary records the latest data type of each field of the business data log.

[0036] The specific steps of step 23 include step 231-step 232: Step 231, determine the existing data type of each field in the data dictionary.

[0037] Look up the existing data type of the field in the data dictionary, that is, the latest data type corresponding to the current latest data of the field.

[0038] Step 232: parse the existing log data of the field according to the existing data type to obtain the original business data corresponding to the existing log data of each field.

[0039] Traverse each field, parse the existing log data of each field, and obtain the original business data of the existing log data of each field until the data parsing of all fields in the last log data is completed.

[0040] Step 24, traverse each log data from the log data before the last log data in the business data log to be synchronized.

[0041] In this step, after the data parsing of all fields in the last log data is completed, the log data before the last log data is traversed forward one by one until all log data is traversed and the parsing is stopped, and the parsed data is synchronized to the target end.

[0042] Step 25, parse the historical log data of each field in the current log data, and return to step 24 after all fields are traversed.

[0043] Specifically, the historical log data of each field in the current log data is traversed and parsed according to the existing data type. The data type of the historical log data may be the same as the existing data type, or may be different.

[0044] Step 26, if the parsing is successful, the original business data corresponding to the historical log data of the current field is output, and the process returns to step 25. The field currently being parsed is one of the fields of a log data, and the historical log data of the current field is parsed. If the data type of the historical log data is the same as the existing data type, the parsing is successful, the corresponding original business data is output, and the process returns to step 25 to continue parsing the historical log data of the next field of the current log data.

[0045] Step 27: If the parsing fails, determine whether the source database is a database of a modifiable column type.

[0046] If the data type of the historical log data is different from the existing data type, the parsing fails and a parsing exception occurs. At this time, the parsing needs to be continued, and it is necessary to determine whether the type of the source database is a database of a modifiable column type.

[0047] Step 28: If the source database is not a database of a modifiable column type, then output parsing exception information and return to step 25 to continue parsing the historical log data of the next field of the current log data.

[0048] At this point, it means that the database column type is not modifiable, that is, the field type does not support modification. Then the field data can be parsed using the type in the data dictionary. If the parsing fails, it means that a parsing exception occurs.

[0049] Here, the data with parsing exception can be located according to the output parsing exception information, and modifications and supplements can be made in time.

[0050] Step 29: If the source database is a database of a modifiable column type, determine the corresponding latest column modification statement in the DDL log according to the column identifier corresponding to the current field, and execute step 30.

[0051] In this step, if the database supports column type modification, it means that the column type corresponding to the current field can be modified. Therefore, modifying the column type means modifying the data type of the field. As a result, the existing data type (modified data type) cannot parse the historical log data, resulting in parsing failure. However, further parsing can be performed according to DDL. The definition and modification statements of the column type of each column are recorded in DDL. Usually, one field corresponds to one column of data, which means that the definition and modification statements of the data type of the field are recorded in DDL. The latest column modification statement records the latest modified content, and the statement before the latest column modification statement records the historical modified content. The column identification information is recorded in the column modification statement.

[0052] The above data types have corresponding encoding rules. For example, the data type is number, which corresponds to binary or decimal encoding rules. When parsing, it is parsed according to the data type, that is, the column type and the corresponding encoding rule.

[0053] Step 30: Determine the history type of the column according to the column modification statement before the latest column modification statement.

[0054] Step 31, parse the historical log data of the current field according to the historical type of the column.

[0055] Step 32, if the parsing is successful, obtain the original business data corresponding to the historical log data of the current field, return to step 25 to continue parsing the historical log data of the next field of the current log data, until all fields in the current log data are traversed, and return to step 24.

[0056] Step 33, if the parsing fails, the column modification statements corresponding to the historical type of the current column are traversed one by one, and after obtaining a historical type of a column, the historical log data is parsed according to the obtained historical type of the column until the parsing successfully ends the traversal or the parsing still fails after the traversal is completed, then the parsing exception information is output.

[0057] In this step, if the history type of the current column cannot parse the current history log data and the parsing fails, the next column modification statement is traversed forward, one by one until the parsing succeeds.

[0058] Here, after each log data in the synchronized business data log is traversed, the parsed data and the modified and supplemented data are synchronized to the target database.

[0059] For example, the user performs an operation on the source database: creates a column c in table t, the type of column c is varchar2, and inserts data aa into column c of table t.

[0060] Then perform another operation: change the type of column c to number, and then insert data bb into column c.

[0061] When synchronizing table t, bb is stored as 0110 in binary form in the business data log. The type of column c recorded in the data dictionary is number in the next operation. According to the number type and binary rules, 0110 is restored to bb. Then, when parsing the previous historical data aa, an error will be reported when parsing the data corresponding to aa in the business data log using the number type and binary rules. At this time, query the DDL log and there are two column modification statements for column c: create table t(c varchar2(8)); alter table t modify c number; The latest modification statement is alter table t modify c number, which means the type of column c is number. The second statement is create table t(c varchar2(8)), which shows that the type of column c in the creation statement is varchar2. The business data log is restored based on varchar2 to get aa, and the parsing is successful.

[0062] like Figure 2 As shown, a third aspect of an embodiment of the present invention provides a data synchronization device for a database, including: A first acquisition module 41 is used to obtain the business data log to be synchronized of the source database in response to a request for synchronizing business data; The second acquisition module 42 is used to acquire the existing log data of each field of the last log data in the business data log to be synchronized; A first parsing module 43 is used to parse the existing log data of each field according to the data dictionary to obtain the original business data corresponding to the existing log data of each field; The first traversal module 44 is used to traverse the historical log data of the field: traverse each log data from the log data before the last log data in the business data log to be synchronized; The second parsing module 45 is used to traverse each field: parse the historical log data of each field in the current log data; The first output module 46 is used to output the original business data corresponding to the historical log data of the current field if the parsing is successful, and return to the step of traversing each field; A judgment module 47 is used to judge whether the type of the source database is a database of a modifiable column type if the parsing fails; A search module 48 is used to determine the corresponding latest column modification statement in the DDL log according to the column identifier corresponding to the current field if yes; A determination module 49, for determining the history type of the column according to a previous statement of the latest column modification statement; The third parsing module 50 is used to parse the historical log data of the current field according to the historical type of the column, obtain the original business data corresponding to the historical log data of the current field, return to the step of traversing each field until all fields in the current log data are traversed, and return to the step of traversing the historical log data of the field.

[0063] In one embodiment of the present invention, the existing log data of each field is parsed according to the data dictionary to obtain the original business data corresponding to the existing log data of each field, including: Determine the existing data type for each field in the data dictionary; The existing log data of the field is parsed according to the existing data type to obtain the original business data corresponding to the existing log data of each field.

[0064] In one embodiment of the present invention, parsing the historical log data of each field in the current log data includes: Parse the historical log data of each field in the current log data according to the existing data type.

[0065] In one embodiment of the present invention, it further includes: a second output module, which is used to output parsing exception information and return to the step of traversing each field if the type of the source database is not a database of a modifiable column type.

[0066] A fourth aspect of an embodiment of 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, a database data synchronization method provided by the above-mentioned embodiment of the present invention is implemented.

[0067] A fifth aspect of an embodiment of the present invention further provides a computer-readable storage medium on which a computer program is stored. When the computer program is executed by a processor, the steps of a database data synchronization method provided by the above-mentioned embodiment of the present invention are implemented.

[0068] The memory may include a random access memory (RAM) or a non-volatile memory (NVM), such as at least one disk memory. Optionally, the memory may also be at least one storage device located away from the aforementioned processor.

[0069] The above-mentioned processor can be a general-purpose processor, including a central processing unit (CPU), a network processor (NP), etc.; it can 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 systems.

[0070] The method provided in the embodiment of the present invention can be applied to electronic devices. Specifically, the electronic device can be: a desktop computer, a portable computer, an intelligent mobile terminal, a server, etc. This is not limited here, and any electronic device that can implement the present invention belongs to the protection scope of the present invention.

[0071] As for the device / electronic device 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.

[0072] 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.

[0073] 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.

[0074] 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.

[0075] Obviously, those skilled in the art can make various changes and modifications to the present invention without departing from the spirit and scope of the present invention. Thus, if these modifications and variations of the present invention fall within the scope of the claims of the present invention and their equivalents, the present invention is also intended to include these modifications and variations.

Claims

1. A method for synchronizing database data, characterized in that: The following steps are involved: In response to a request to synchronize business data, obtain a business data log to be synchronized from a source database; Obtain existing log data for each field of the last log data in the business data log to be synchronized; Parse the existing log data of each field according to the data dictionary to obtain the original business data corresponding to the existing log data of each field; Traversing the historical log data of the field: traversing each log data from the log data before the last log data in the business data log to be synchronized; Traverse each field: parse the historical log data of each field in the current log data; If the parsing is successful, the original business data corresponding to the historical log data of the current field is output, and the step of traversing each field is returned; If the parsing fails, determine whether the source database is a database of a modifiable column type; If yes, determine the corresponding latest column modification statement in the DDL log according to the column identifier corresponding to the current field; Determining a history type of a column according to a previous statement of the latest column modification statement; Parse the historical log data of the current field according to the historical type of the column, obtain the original business data corresponding to the historical log data of the current field, return to the step of traversing each field, until all fields in the current log data are traversed, and return to the step of traversing the historical log data of the traversed field.

2. The method according to claim 1, characterized in that The parsing the existing log data of each field according to the data dictionary to obtain the original business data corresponding to the existing log data of each field includes: Determine the existing data type for each field in the data dictionary; The existing log data of the field is parsed according to the existing data type to obtain original business data corresponding to the existing log data of each field.

3. The method according to claim 2, characterized in that The parsing of the historical log data of each field in the current log data includes: The historical log data of each field in the current log data is parsed according to the existing data type.

4. The method according to claim 2, characterized in that The method further includes: if the type of the source database is not a database of a modifiable column type, outputting parsing exception information and returning to the step of traversing each field.

5. A data synchronization device for a database, characterized in that: include: A first acquisition module, configured to obtain a log of business data to be synchronized from a source database in response to a request for synchronizing business data; A second acquisition module is used to acquire the existing log data of each field of the last log data in the business data log to be synchronized; A first parsing module, used to parse the existing log data of each field according to a data dictionary to obtain original business data corresponding to the existing log data of each field; A traversal module, used for traversing the historical log data of a field: traversing each log data from the log data before the last log data in the business data log to be synchronized; The second parsing module is used to traverse each field: parse the historical log data of each field in the current log data; A first output module, configured to output the original business data corresponding to the historical log data of the current field if the parsing is successful, and return to the step of traversing each field; A judgment module, used for judging whether the type of the source database is a database of a modifiable column type if the parsing fails; A search module, configured to, if yes, determine the corresponding latest column modification statement in the DDL log according to the column identifier corresponding to the current field; A determination module, configured to determine a history type of a column according to a previous statement of the latest column modification statement; The third parsing module is used to parse the historical log data of the current field according to the historical type of the column, obtain the original business data corresponding to the historical log data of the current field, return to the step of traversing each field until all fields in the current log data are traversed, and return to the step of traversing the historical log data of the traversed field.

6. The device according to claim 5, characterized in that The parsing the existing log data of each field according to the data dictionary to obtain the original business data corresponding to the existing log data of each field includes: Determine the existing data type for each field in the data dictionary; The existing log data of the field is parsed according to the existing data type to obtain original business data corresponding to the existing log data of each field.

7. The device according to claim 6, characterized in that The parsing of the historical log data of each field in the current log data includes: The historical log data of each field in the current log data is parsed according to the existing data type.

8. The device according to claim 6, characterized in that Also includes: The second output module is used to output parsing exception information and return to the step of traversing each field if the type of the source database is not a database of a modifiable column type.

9. 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 program, the database data synchronization method according to any one of claims 1 to 4 is implemented.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the database data synchronization method according to any one of claims 1 to 4 is implemented.

Images