A database synchronization method and apparatus, an electronic device, and a storage medium

Abstract

The application discloses a database synchronization method and device, electronic equipment and a storage medium. The database synchronization method comprises the following steps: acquiring log information to be synchronized and data file information; determining an operation data page corresponding to a to-be-synchronized operation indicated by the log information to be synchronized according to the log information to be synchronized and the data file information; when an operation type corresponding to the to-be-synchronized operation is an update operation, determining original row data and updated row data corresponding to the to-be-synchronized operation according to the log information to be synchronized and the operation data page, wherein the original row data and the updated row data are respectively corresponding data before and after the execution of the to-be-synchronized operation; and performing database synchronization according to the original row data, the updated row data and the log information to be synchronized. According to the above technical scheme, the specific content of the to-be-synchronized operation can be determined according to the original row data, the updated row data and the log information to be synchronized, and then the synchronization of the database can be realized without opening CDC or publishing and subscribing.

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and in particular to a database synchronization method, apparatus, electronic device and storage medium. Background Technology

[0002] When the content of a database table changes, it is necessary to parse the update operation (i.e., updating the content of records in the table), insert operation (i.e., inserting content into the table), or delete operation (i.e. deleting the content of records in the table) performed on the database table, and realize database synchronization based on the parsing results.

[0003] Database synchronization based on online logs is a common database synchronization method. When using this method, parsing update operations is mostly achieved by enabling additional logging for the database. For example, when synchronizing a Structured Query Language (SQL) database, the SQL Server online log only records changed bytes, offsets, and primary keys for update operations. Since only changed bytes and offsets are recorded, it's insufficient to parse update operations. Therefore, enabling Change Data Capture (CDC) or publish / subscribe on the tables to be synchronized makes parsing update operations feasible.

[0004] However, enabling CDC on a database requires SQL Server version 2008 or later, and Enterprise or Developer edition or later. Enabling publish-subscribe requires the table to have a primary key. Furthermore, after enabling CDC or publish-subscribe on a database, the table cannot be cleared. Additionally, the increased log volume will also impact system performance. Summary of the Invention

[0005] This invention provides a database synchronization method, apparatus, electronic device, and storage medium that can achieve database synchronization without enabling CDC or publish / subscribe.

[0006] In a first aspect, embodiments of the present invention provide a database synchronization method, including:

[0007] Obtain the log information and data file information to be synchronized;

[0008] Based on the log information to be synchronized and the data file information, determine the operation data page corresponding to the synchronization operation indicated by the log information to be synchronized;

[0009] When the operation type corresponding to the operation to be synchronized is an update operation, the original row data and the updated row data corresponding to the operation to be synchronized are determined according to the log information to be synchronized and the operation data page. The original row data and the updated row data are the data before and after the operation to be synchronized are executed, respectively.

[0010] Database synchronization is performed based on the original row data, the updated row data, and the log information to be synchronized.

[0011] Secondly, embodiments of the present invention provide a database synchronization device, comprising:

[0012] The acquisition module is used to acquire the log information and data file information to be synchronized;

[0013] The data page determination module is used to determine the operation data page corresponding to the synchronization operation indicated by the log information to be synchronized, based on the log information to be synchronized and the data file information.

[0014] The row data determination module is used to determine the original row data and updated row data corresponding to the operation to be synchronized based on the log information to be synchronized and the operation data page when the operation type corresponding to the operation to be synchronized is an update operation. The original row data and the updated row data are respectively the data before and after the operation to be synchronized is executed.

[0015] The synchronization module is used to synchronize the database based on the original row data, the updated row data, and the log information to be synchronized.

[0016] Thirdly, embodiments of the present invention provide an electronic device, including:

[0017] At least one processor; and

[0018] A memory communicatively connected to the at least one processor; wherein,

[0019] The memory stores a computer program that can be executed by the at least one processor to enable the at least one processor to perform the method as described in the first aspect.

[0020] Fourthly, embodiments of the present invention provide a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the method described in the first aspect.

[0021] The technical solution of this invention first obtains the log information and data file information to be synchronized; then, based on the log information and data file information, it determines the operation data page corresponding to the synchronization operation indicated by the log information; then, when the operation type corresponding to the synchronization operation is an update operation, it determines the original row data and updated row data corresponding to the synchronization operation based on the log information and operation data page, where the original row data and updated row data are the data before and after the synchronization operation is executed, respectively; finally, it performs database synchronization based on the original row data, updated row data, and log information to be synchronized. This technical solution determines the original row data and updated row data corresponding to the synchronization operation through the log information and data file information, and then determines the specific content of the synchronization operation based on the original row data, updated row data, and log information to be synchronized, thereby achieving database synchronization without enabling CDC or publish / subscribe on the database.

[0022] It should be understood that the description in this section is not intended to identify key or essential features of the embodiments of the present invention, nor is it intended to limit the scope of the invention. Other features of the invention will become readily apparent from the following description. Attached Figure Description

[0023] To more clearly illustrate the technical solutions in the embodiments of the present invention, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0024] Figure 1 This is a flowchart of a database synchronization method provided according to Embodiment 1 of the present invention;

[0025] Figure 2 This is a flowchart of a database synchronization method provided according to Embodiment 2 of the present invention;

[0026] Figure 3 This is a schematic diagram of obtaining new row data based on the original row data and the update log according to Embodiment 3 of the present invention;

[0027] Figure 4 This is a schematic diagram of a data page in a data file according to Embodiment 3 of the present invention;

[0028] Figure 5 This is a schematic diagram of a full-scale phase according to Embodiment 3 of the present invention;

[0029] Figure 6 This is a schematic diagram of an incremental stage provided according to Embodiment 3 of the present invention;

[0030] Figure 7 This is a schematic diagram of the structure of a database synchronization device according to Embodiment 4 of the present invention;

[0031] Figure 8 This is a schematic diagram of the structure of an electronic device that implements the database synchronization method of this invention. Detailed Implementation

[0032] To enable those skilled in the art to better understand the present invention, the technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings of the embodiments of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of the present invention.

[0033] It should be noted that the terms "first," "second," etc., used in this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of the invention described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0034] It is understood that before using the technical solutions disclosed in the various embodiments of the present invention, users should be informed of the types, scope of use, and usage scenarios of the personal information involved in this disclosure in an appropriate manner in accordance with relevant laws and regulations, and user authorization should be obtained.

[0035] Example 1

[0036] Figure 1 This is a flowchart of a database synchronization method according to Embodiment 1 of the present invention. This embodiment is applicable to situations involving database synchronization. The method can be executed by a database synchronization device, which can be implemented in software and / or hardware and integrated into an electronic device. Further, the electronic device includes, but is not limited to, computers, laptops, smartphones, servers, etc. Figure 1 As shown, the method includes:

[0037] S110. Obtain the log information and data file information to be synchronized.

[0038] When a row of data in a database table changes, a log entry can be created to record this change, such as recording the changed bytes and their offset. The log information to be synchronized is the information indicated by the log generated when a single row of data in the database changes. When multiple rows of data in a database table change, each row change generates corresponding log information to be synchronized. This embodiment of the invention performs a database synchronization operation on a single log entry to be synchronized. Correspondingly, when there are multiple log entries to be synchronized, multiple log entries can be read in a loop, and the database synchronization method provided in this embodiment can be used to synchronize the database for each log entry.

[0039] The method for obtaining the log information to be synchronized is not limited. For example, the electronic device can periodically read the log information to be synchronized from the database, or the electronic device can read the log information to be synchronized from the database as needed. When there are multiple log information to be synchronized corresponding to a transaction, multiple log information to be synchronized can be read in a loop, and the database synchronization method provided in this embodiment of the invention can be used to synchronize the database for each log information to be synchronized until the database synchronization of all log information to be synchronized corresponding to the transaction is completed.

[0040] Data file information refers to the information indicated by data files in the database. This information can contain information indicated by one or more data files, without specific limitations. The method of obtaining data file information is not limited. For example, before database synchronization, i.e., during the full synchronization phase (when row data has not changed), the data file information in the database can be pre-transmitted to a backup database. The backup database can be a database integrated into an electronic device or a data platform, as long as it can store the data file information. When database synchronization is required, the data file information stored in the backup database can be retrieved from the backup database via an electronic device.

[0041] S120. Based on the log information to be synchronized and the data file information, determine the operation data page corresponding to the operation to be synchronized indicated by the log information to be synchronized.

[0042] The operation to be synchronized can be the synchronization operation recorded in the log indicated by the log information to be synchronized. The operation type of the operation to be synchronized can be an update operation, a delete operation, or an insert operation, etc. The operation data page can be the data page operated on by the operation to be synchronized. For example, when the operation type of the operation to be synchronized is an update operation, the operation data page is the data page that performs the update operation; when the operation type of the operation to be synchronized is a delete operation, the operation data page is the data page that performs the delete operation.

[0043] The method for determining the operation data page corresponding to the synchronization operation indicated by the log information to be synchronized is not limited, based on the log information to be synchronized and the data file information. The log information to be synchronized may record the file identifier of the target data file operated on by the synchronization operation, as well as the data page identifier of the operation data page operated on by the synchronization operation. Based on the file identifier recorded in the log information to be synchronized, the target data file corresponding to the file identifier can be found among the multiple data files indicated by the data file information. This target data file is the data file where the synchronization operation is performed. After determining the target data file, the operation data page in the target data file can be determined through the data page identifier recorded in the data file information.

[0044] In one embodiment, firstly, based on the file identifier recorded in the log information to be synchronized, the target data file corresponding to the file identifier is determined among the multiple data files indicated by the data file information. For example, the target data file is determined to be the primary data file (mdf) in the database. In the primary data file, the data files are managed in the form of pages. The operation data page in the target data file is determined by the data page identifier recorded in the data file information. For example, when the data page identifier is 100 and the size of the data page is uniformly 8192 bytes, the product of the data page identifier 100 and the data page size 8192, 819200, can be determined as the offset of the starting position of the operation data page in the primary data file. That is, the operation data page starts at position 819200 in the primary data file, and the size of the operation data page is 8192 bytes.

[0045] S130. When the operation type corresponding to the operation to be synchronized is an update operation, determine the original row data and updated row data corresponding to the operation to be synchronized based on the log information to be synchronized and the operation data page. The original row data and updated row data are the data corresponding to the operation before and after execution, respectively.

[0046] The original row data and the updated row data are the data before and after the synchronization operation is executed, respectively. That is, the original row data is the data before the change of the row data, and the updated row data is the data after the change of the row data. In other words, the updated row data is the row data after the original row data is updated, which is the row data after the synchronization operation of the operation type of update is executed.

[0047] When the operation type corresponding to the operation to be synchronized is an update operation, the original row data corresponding to the operation can be determined in the operation data page based on the slot identifier recorded in the log information to be synchronized. Specifically, each row data in the operation data page is called a slot, and each slot has a unique slot identifier. The slot identifier identifies the unique slot, and thus the unique row data corresponding to that slot identifier. All slots in the operation data page are arranged closely together without separators, and the position of each slot can be located using the offset table recorded at the end of the operation data page. The slot identifier recorded in the log information to be synchronized is the identifier of the slot corresponding to the original row data. After determining the slot identifier recorded in the log information to be synchronized, the offset corresponding to that slot identifier can be looked up in the offset table recorded at the end of the operation data page. This offset is the offset of the row data corresponding to that slot identifier in the operation data page, thus determining the original row data.

[0048] The log information to be synchronized also records the changed bytes and their offsets corresponding to the synchronization operation. After determining the original row data, the changed bytes can be replaced with the offsets recorded in the log information to be synchronized, based on the original row data, to obtain the updated row data.

[0049] S140. Perform database synchronization based on the original row data, updated row data, and log information to be synchronized.

[0050] Before database synchronization, during the full phase (when row data has not changed), in addition to transferring the data file information in the database to the backup database in advance, all table structures in the full phase database can also be saved, such as to disk. Subsequently, during the incremental phase (when row data has changed), a synchronization program can be used to maintain the table structures saved on disk separately. For example, if the table structure changes during the incremental process, the table structure saved by the synchronization program can be modified.

[0051] The log information to be synchronized also records the table identifier corresponding to the operation to be synchronized. This table identifier is the identifier of the database table corresponding to the operation to be synchronized. After determining the table identifier recorded in the log information to be synchronized, the table structure corresponding to the table identifier can be found from all the table structures stored on the disk. The found table structure is the table structure of the database table corresponding to the operation to be synchronized.

[0052] After determining the table structure of the database table corresponding to the synchronization operation, the original row data and the updated row data can be parsed according to the determined table structure. Then, the data of each field in the original row data and the updated row data can be obtained respectively. By comparing the obtained data, the fields and data that have changed in the updated row data compared with the original row data can be determined, that is, the update operation content corresponding to the synchronization operation can be determined.

[0053] After determining the update operation content corresponding to the operation to be synchronized, the update operation content can be transferred to the backup database for subsequent processing. At the same time, the update row data can be written to the operation data page, thereby realizing the database synchronization.

[0054] The technical solution of this invention first obtains the log information and data file information to be synchronized; then, based on the log information and data file information, it determines the operation data page corresponding to the synchronization operation indicated by the log information; then, when the operation type corresponding to the synchronization operation is an update operation, it determines the original row data and updated row data corresponding to the synchronization operation based on the log information and operation data page, where the original row data and updated row data are the data before and after the synchronization operation is executed, respectively; finally, it performs database synchronization based on the original row data, updated row data, and log information to be synchronized. This technical solution determines the original row data and updated row data corresponding to the synchronization operation through the log information and data file information, and then determines the specific content of the synchronization operation based on the original row data, updated row data, and log information to be synchronized, thereby achieving database synchronization without enabling CDC or publish / subscribe on the database.

[0055] Example 2

[0056] Figure 2 This is a flowchart of a database synchronization method according to Embodiment 2 of the present invention. This embodiment is a further refinement based on Embodiment 1 described above, such as... Figure 2 As shown, the method includes:

[0057] S110. Obtain the log information and data file information to be synchronized.

[0058] S121. Determine the file identifier and data page identifier included in the log information to be synchronized. The file identifier indicates the identifier of the target data file corresponding to the synchronization operation, and the data page identifier indicates the identifier of the data page to be synchronized.

[0059] The log information to be synchronized records the file identifier of the target data file corresponding to the operation to be synchronized, and the data page identifier of the operation data page corresponding to the operation to be synchronized. After obtaining the log information to be synchronized in step S110, the file identifier and data page identifier included in the log information to be synchronized can be determined.

[0060] S122. Among the one or more data files indicated by the data file information, the data file corresponding to the file identifier is determined as the target data file.

[0061] The data file information may contain information indicated by one or more data files, where each data file has a unique identifier. By using the file identifier included in the log information to be synchronized, the data file corresponding to the file identifier included in the log information to be synchronized can be found from one or more data files indicated by the data file information. This data file is the target data file corresponding to the synchronization operation.

[0062] S123. In the target data file, the product of the data page identifier and the data page size is determined as the starting position of the operation data page in the target data file. The target data file includes one or more data pages, and the multiple data pages have the same data page size.

[0063] The target data file is managed in the form of pages. A target data file can contain one or more data pages, all with the same page size. Each data page has a unique identifier. Using the data page identifiers included in the log information to be synchronized, the product of the data page identifier and the page size can be used to determine the starting position of the data page to be operated on within the target data file. For example, if the data page identifier is 100 and the data page size is 8192 bytes, the product of the data page identifier 100 and the data page size 8192, 819200, can be used to determine the starting position of the data page to be operated on within the target data file.

[0064] S124. Determine the operation data page by taking the page start position as the starting position of the operation data page in the target data file and taking the data page size as the size of the operation data page.

[0065] The starting position of the operation data page in the target data file is the page start position, and the size of the operation data page in the target data file is the data page size, thus determining the operation data page.

[0066] S131. When the operation type corresponding to the operation to be synchronized is an update operation, determine the slot identifier included in the log information to be synchronized. The slot identifier indicates the identifier of the slot corresponding to the original row data of the operation to be synchronized.

[0067] The log information to be synchronized records the slot identifier of the slot corresponding to the original row data of the operation to be synchronized. After obtaining the log information to be synchronized in step S110, the slot identifier included in the log information to be synchronized can be determined.

[0068] S132. Determine the position mapping relationship of the records in the operation data page. The position mapping relationship indicator slot indicates the offset of the corresponding original row data in the operation data page.

[0069] At the end of the operation data page, the offset of the row data corresponding to the different slot identifiers in the operation data page can be recorded. That is, the offset of the original row data corresponding to the slot identifiers included in the log information to be synchronized in the operation data page can be recorded in the operation data page.

[0070] S133. Determine the original row data corresponding to the operation to be synchronized based on the slot identifier and position mapping relationship.

[0071] Based on the slot identifiers included in the log information to be synchronized, the offset corresponding to the slot identifier can be queried in the location mapping relationship. This offset is the offset of the original row data corresponding to the operation to be synchronized in the operation data page.

[0072] In one embodiment, determining the original row data corresponding to the synchronization operation based on the slot identifier and position mapping relationship includes:

[0073] Based on the slot identifier, a query is performed in the position mapping relationship to determine the offset of the original row data corresponding to the slot identifier in the operation data page;

[0074] The offset is used to determine the original row data corresponding to the operation to be synchronized by offsetting within the operation data page.

[0075] The query is performed in the position mapping relationship based on the slot identifier. If the slot identifier is slot0, the offset of slot0 in the position mapping relationship is 110. This offset is the offset of the original row data in the operation data page. The offset is 110 at the starting position of the operation data page. The position obtained by offset is the position of the original row data corresponding to the operation to be synchronized. Thus, the original row data corresponding to the operation to be synchronized can be determined.

[0076] S134. Based on the log information to be synchronized and the original row data, determine the update row data corresponding to the synchronization operation.

[0077] The log information to be synchronized also records the changed bytes and their offsets corresponding to the synchronization operation. After determining the original row data, the changed bytes can be replaced with the offsets recorded in the log information to be synchronized, based on the original row data, to obtain the updated row data.

[0078] In one embodiment, determining the update row data corresponding to the synchronization operation based on the log information to be synchronized and the original row data includes:

[0079] Determine the data update information included in the log information to be synchronized. The data update information indicates the updated bytes and the corresponding update offsets in the original row data.

[0080] Based on the starting position of the original row data, the offset position after the offset is determined by the updated offset.

[0081] Replace the bytes at the offset positions in the original row data with the updated bytes to obtain the updated row data.

[0082] The data update information indicates the updated byte and its corresponding update offset in the original row data. The updated byte is the changed byte corresponding to the synchronization operation, and the update offset is the offset corresponding to the updated byte. After obtaining the log information to be synchronized in step S110, the data update information included in the log information to be synchronized can be determined.

[0083] Based on the starting position of the original row data, the offset position is determined by offsetting the update offset recorded in the log information to be synchronized. The updated bytes are then replaced with the offset positions in the original row data to obtain the updated row data.

[0084] S141. Determine the table identifiers included in the log information to be synchronized. The table identifiers indicate the identifiers of the database tables corresponding to the synchronization operations.

[0085] The log information to be synchronized also records the table identifier corresponding to the operation to be synchronized. This table identifier is the identifier of the database table corresponding to the operation to be synchronized. After obtaining the log information to be synchronized in step S110, the table identifier included in the log information to be synchronized can be determined.

[0086] S142. Determine the table structure of the database table corresponding to the table identifier.

[0087] After determining the table identifier of the log information to be synchronized, the table structure corresponding to the table identifier can be found from all the table structures stored on the disk. The found table structure is the table structure of the database table corresponding to the synchronization operation.

[0088] S143. Based on the table structure, parse the original row data and the updated row data to obtain the update operation content corresponding to the operation to be synchronized. The update operation content indicates the fields and data that have changed in the updated row data compared to the original row data.

[0089] The original row data and the updated row data are parsed based on the table structure, and the data of each field in the original row data and the updated row data can be obtained respectively. By comparing the data of the same fields in the original row data and the updated row data, the fields and data that have changed in the updated row data compared with the original row data can be determined, that is, the update operation content corresponding to the synchronization operation can be determined.

[0090] S144. Transfer the update operation content to the backup database.

[0091] S145. Write the updated row data to the operation data page.

[0092] In one embodiment, the method further includes:

[0093] When the operation type corresponding to the operation to be synchronized is a deletion operation, the operation to be synchronized is parsed according to the table structure of the database table corresponding to the operation to be synchronized, and the deletion operation content corresponding to the operation to be synchronized is obtained.

[0094] Transfer the deletion operation details to the backup database;

[0095] In the operation data page, delete the data corresponding to the slot identifier included in the log information to be synchronized.

[0096] When the operation type corresponding to the operation to be synchronized is a deletion operation, the synchronization log information can record complete row data. There is no need to read the operation data page corresponding to the operation to be synchronized. Instead, the data corresponding to all fields can be obtained by parsing the table structure of the database table corresponding to the operation to be synchronized. Thus, the deletion operation content corresponding to the operation to be synchronized can be determined. The deletion operation content is the content deleted by executing the operation to be synchronized.

[0097] The deletion operation content is transferred to the backup database for subsequent processing. At the same time, in the operation data page, the data corresponding to the slots included in the log information to be synchronized is deleted. The slots included in the log information to be synchronized are the identifiers corresponding to the slots that need to be deleted. Deleting the data corresponding to the slots can be understood as deleting the row data corresponding to the slots, that is, realizing the synchronization operation with the operation type of deletion.

[0098] The technical solution of this invention determines the operation data page corresponding to the synchronization operation by using the file identifier and data page identifier included in the log information to be synchronized; determines the original row data corresponding to the synchronization operation based on the slot identifier included in the log information to be synchronized; determines the update row data based on the original row data and the data update information included in the log information to be synchronized; parses the original row data and the update row data according to the table structure corresponding to the table identifier included in the log information to be synchronized to obtain the update operation content; and writes the update row data into the operation data page, thus achieving database synchronization without enabling CDC or publish / subscribe for the database.

[0099] Example 3

[0100] The embodiments of the present invention are exemplary descriptions of the above embodiments.

[0101] This invention provides a SQL Server synchronization method based on an MDF file without enabling CDC (Cybernetic Detection). This method enables database synchronization without enabling CDC or publishing / subscribing.

[0102] SQL Server data files typically have the .mdf extension, so we'll use ".mdf file" here to refer to data files in SQL Server. Update operations cannot be parsed without CDC or publish / subscribe enabled because the log contains insufficient data. It only records the changed bytes and their offsets within a single row, not the changed fields and data. Specifically, in SQL Server, a row of data is stored contiguously with all fields listed first (fixed-length fields first, variable-length fields last). There are no field identifiers (IDs), field separators, and the length of each fixed-length field is not specified. Therefore, even knowing the table structure, it's impossible to parse the update operation.

[0103] In the above scenario, if the row data before the update (i.e., the original row data) is known, the changed bytes (i.e., the updated bytes) and offset (i.e., the update offset) in the log can be added to obtain the complete row data after the update (i.e., the updated row data). Then, the content of the update operation (i.e., the update operation content) can be obtained by parsing the new and old row data according to the table structure.

[0104] Figure 3 This is a schematic diagram illustrating how to obtain new row data based on original row data and an update log according to Embodiment 3 of the present invention. The update log is the log indicated by the log information to be synchronized, recording the updated bytes (i.e., changed bytes) and the update offset. The new row data is the updated row data. Based on the original row data, the changed bytes are replaced with the offset recorded in the log information to be synchronized, thus obtaining the updated row data.

[0105] Therefore, obtaining the original row data is fundamental to solving the problem. All row data in SQL Server is recorded in an MDF file. Before synchronization begins, a backup of the MDF file (i.e., one or more data files indicated by the data file information) is required. When a specific update operation needs to be parsed, its original row data is located in the MDF backup, and subsequent parsing can proceed. Locating the original row data corresponding to a specific operation in the log is calculated using the fileid (file identifier), pageid (data page identifier), and slotid (slot identifier) ​​in the log. SQL Server allows a database to create multiple data files, each corresponding to a fileid. Using this fileid, the data file containing the operation row data in this log entry (i.e., the target data file) can be obtained. In principle, the operation method for each data file is the same; therefore, the following description assumes that the target data file has been determined using the fileid, and then describes the operation method for determining the original row data using the pageid and slotid.

[0106] The method for locating the original row data in the target data file is explained below:

[0107] Figure 4 This is a schematic diagram of a data page in a data file according to Embodiment 3 of the present invention. In SQL Server, data files are managed in the form of pages, with a uniform page size of 8192 bytes. Each page contains a fixed-length header, row data, and row data offsets. Each row data entry is called a slot, and all slots are arranged closely together without separators. The position of each slot is located through an offset table at the end of the page. The offset table represents the position mapping relationship, indicating the offset of the row data corresponding to different slot identifiers within the data page. For example, the offset corresponding to slot 0 is 110; the offset corresponding to slot 1 is 96; and the offset corresponding to slot 2 is 165, etc.

[0108] In SQL Server, each log entry records the changes to one row of data, along with the row's pageid, slotid (and fileid, which are ignored here), table ID, and primary key data (if the table contains a primary key). First, multiplying pageid by 8192 gives the offset of the data page within the data file. For example, if pageid is 100, the data page starts at position 819200 in the data file. After obtaining the data page, the position of the original row within the data page can be determined based on the offset table in the data page and the slotid in the log. For example, if slotid is 0 (i.e., slot0), the offset within the page is 110, because the offset table is arranged from right to left at the end of the page. After obtaining the original row data, the update data (i.e., update bytes and update offset) from the log is added to obtain the complete updated row data. Then, by parsing the original and updated row data according to the table structure to obtain the data for each field, the specific update operation can be determined.

[0109] It should be noted that for insert and delete operations, the SQL Server log records the complete row data. It is not necessary to read the data page; all field data can be obtained simply by referring to the table structure.

[0110] Because database synchronization is a continuous process, after parsing the specific operations, the modifications must be written to the MDF file to ensure correct data parsing later. For update operations, as mentioned earlier, the complete modified update row data can be obtained, and then the modified update row data can be updated into the operation data page. For insert operations, the row data obtained from the log can be directly inserted into the corresponding data page. For delete operations, the corresponding slot is deleted from the data page.

[0111] In the SQL Server synchronization method without CDC based on MDF files provided in this embodiment of the invention, two data stages are involved: the full stage and the incremental stage. The full stage is the data stage before database synchronization, and the incremental stage is the data stage when data changes during database synchronization.

[0112] Figure 5This is a schematic diagram of a full backup phase according to Embodiment 3 of the present invention. In the full backup phase, all data files (i.e., data file information) and table structures in the SQL Server database can be transferred to a backup repository (i.e., a backup database) for backup. The backup repository can be an SQL Server database, other relational databases, or other big data platforms. The table structures in the database can be backed up subsequently by querying system tables and system views to obtain the specific table structures of the tables that need to be synchronized.

[0113] Figure 6 This is a schematic diagram of an incremental phase according to Embodiment 3 of the present invention. In the incremental phase, it is necessary to read the online log file of SQL Server (i.e., the log information to be synchronized) and parse it in conjunction with the backup data file and table structure to obtain the specific synchronization operation.

[0114] This invention provides a SQL Server synchronization method based on MDF files without enabling CDC, as detailed below:

[0115] First, read the log (i.e., the log information to be synchronized) in a loop. As long as the end of the log is not reached, read out a log and process it.

[0116] Second, based on the table ID in the log, find the table for this operation from all the table structures exported in full, read the specific operation data page from the MDF backup file based on the page ID in the log, and proceed to the next step according to the specific operation type of this log.

[0117] Third, if the operation type is update, then as described above, the new row data and the complete update operation are parsed from the original row data and table structure in the data page. The obtained operation is sent to the loading end (i.e., the backup database), and the new row data is written back to the data page. If the operation type is insert, the specific insert operation is parsed from the table structure and sent to the loading end. The row data in the insert operation is inserted into the data page according to the slot. If the operation type is delete, the specific delete operation is obtained from the table structure and sent to the loading end. The slot in the page is deleted according to the slotid. If the operation type is delete split, i.e., data page split, all slots in the data page that are greater than or equal to the slotid are deleted according to the slotid. If the operation type is space management page setting, which means that a new data page has been allocated or an old data page has been cleared, the data page is cleared according to the pageid. Or, if the pageid has exceeded the current MDF file size, the MDF file needs to be expanded to at least include the data page pointed to by the pageid. There are other operation types that need to be handled, which will not be elaborated here.

[0118] Fourth, when parsing to the commit stage, a transaction has been parsed completely. At this point, the modified data pages in this transaction can be written back to the MDF file, and the data file can be flushed to disk periodically.

[0119] Fifth, use the Log Sequence Number (LSN) of the log as breakpoint information. The LSN is the location descriptor of the SQL Server log virtual file. Each log entry has a corresponding LSN. By recording the starting LSN of the last uncommitted transaction, you can use this LSN to continue reading the log after the program is interrupted.

[0120] The technical solution of this invention can achieve database synchronization without enabling CDC or publish / subscribe to the database.

[0121] Example 4

[0122] Figure 7 This is a schematic diagram of a database synchronization device according to Embodiment 4 of the present invention. This embodiment is applicable to situations involving database synchronization. Figure 7 As shown, the specific structure of the device includes:

[0123] Module 21 is used to obtain the log information and data file information to be synchronized;

[0124] The data page determination module 22 is used to determine the operation data page corresponding to the synchronization operation indicated by the log information to be synchronized, based on the log information to be synchronized and the data file information.

[0125] The row data determination module 23 is used to determine the original row data and updated row data corresponding to the operation to be synchronized based on the log information to be synchronized and the operation data page when the operation type corresponding to the operation to be synchronized is an update operation. The original row data and updated row data are the data corresponding to the operation before and after the operation to be synchronized is executed, respectively.

[0126] Synchronization module 24 is used to synchronize the database based on the original row data, updated row data, and log information to be synchronized.

[0127] The database synchronization device provided in this embodiment first acquires the log information and data file information to be synchronized through the acquisition module; then, the data page determination module determines the operation data page corresponding to the synchronization operation indicated by the log information to be synchronized based on the log information and data file information; then, the row data determination module determines the original row data and updated row data corresponding to the synchronization operation based on the log information and operation data page when the operation type corresponding to the synchronization operation is an update operation, with the original row data and updated row data being the data before and after the synchronization operation is executed, respectively; finally, the synchronization module performs database synchronization based on the original row data, updated row data, and log information to be synchronized.

[0128] Furthermore, the row data determination module 23 is specifically used for:

[0129] Determine the slot identifiers included in the log information to be synchronized. The slot identifiers indicate the identifiers of the slots corresponding to the original row data of the operation to be synchronized.

[0130] Determine the position mapping relationship of records in the operation data page. The position mapping relationship indicator slot indicates the offset of the corresponding original row data in the operation data page.

[0131] Based on the slot identifier and position mapping relationship, determine the original row data corresponding to the operation to be synchronized;

[0132] Based on the log information to be synchronized and the original row data, determine the update row data corresponding to the synchronization operation.

[0133] Furthermore, the row data determination module 23 is specifically used for:

[0134] Based on the slot identifier, a query is performed in the position mapping relationship to determine the offset of the original row data corresponding to the slot identifier in the operation data page;

[0135] The offset is used to determine the original row data corresponding to the operation to be synchronized by offsetting within the operation data page.

[0136] Furthermore, the row data determination module 23 is specifically used for:

[0137] Determine the data update information included in the log information to be synchronized. The data update information indicates the updated bytes and the corresponding update offsets in the original row data.

[0138] Based on the starting position of the original row data, the offset position after the offset is determined by the updated offset.

[0139] Replace the bytes at the offset positions in the original row data with the updated bytes to obtain the updated row data.

[0140] Furthermore, the synchronization module 24 is specifically used for:

[0141] Determine the table identifiers included in the log information to be synchronized. The table identifiers indicate the identifiers of the database tables corresponding to the synchronization operations.

[0142] Determine the table structure of the database table corresponding to the table identifier;

[0143] Based on the table structure, the original row data and the updated row data are parsed to obtain the update operation content corresponding to the operation to be synchronized. The update operation content indicates the fields and data that have changed in the updated row data compared to the original row data.

[0144] Transfer the update operation details to the backup database;

[0145] Write the updated row data to the operation data page.

[0146] Furthermore, the data page determination module 22 is specifically used for:

[0147] Determine the file identifier and data page identifier included in the log information to be synchronized. The file identifier indicates the identifier of the target data file corresponding to the synchronization operation, and the data page identifier indicates the identifier of the data page to be synchronized.

[0148] Among the one or more data files indicated by the data file information, the data file corresponding to the file identifier is identified as the target data file;

[0149] In the target data file, the product of the data page identifier and the data page size is used to determine the starting position of the data page in the target data file. The target data file includes one or more data pages, and the multiple data pages have the same data page size.

[0150] The operation data page is determined by using the page start position as the starting position of the operation data page in the target data file and the data page size as the size of the operation data page.

[0151] Furthermore, the device also includes:

[0152] The deletion operation content determination module is used to parse the operation to be synchronized based on the table structure of the database table corresponding to the operation to be synchronized when the operation type corresponding to the operation to be synchronized is a deletion operation, and obtain the deletion operation content corresponding to the operation to be synchronized.

[0153] The deletion operation content transmission module is used to transmit the deletion operation content to the backup database;

[0154] The deletion module is used to delete the data corresponding to the slot identifier included in the log information to be synchronized in the operation data page.

[0155] The database synchronization device provided in this embodiment of the invention can execute the database synchronization method provided in any embodiment of the invention, and has the corresponding functional modules and beneficial effects of the method execution.

[0156] Example 5

[0157] Figure 8 This is a schematic diagram of the structure of an electronic device implementing the database synchronization method of this invention. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital processors, cellular phones, smartphones, wearable devices (e.g., helmets, glasses, watches, etc.), and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely illustrative and are not intended to limit the implementation of the invention described and / or claimed herein.

[0158] like Figure 8 As shown, the electronic device 10 includes at least one processor 11 and a memory, such as a read-only memory (ROM) 12 or a random access memory (RAM) 13, communicatively connected to the at least one processor 11. The memory stores computer programs executable by the at least one processor. The processor 11 can perform various appropriate actions and processes based on the computer program stored in the ROM 12 or loaded from storage unit 18 into the RAM 13. The RAM 13 may also store various programs and data required for the operation of the electronic device 10. The processor 11, ROM 12, and RAM 13 are interconnected via a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.

[0159] Multiple components in electronic device 10 are connected to I / O interface 15, including: input unit 16, such as keyboard, mouse, etc.; output unit 17, such as various types of displays, speakers, etc.; storage unit 18, such as disk, optical disk, etc.; and communication unit 19, such as network card, modem, wireless transceiver, etc. Communication unit 19 allows electronic device 10 to exchange information / data with other devices through computer networks such as the Internet and / or various telecommunications networks.

[0160] Processor 11 can be a variety of general-purpose and / or special-purpose processing components with processing and computing capabilities. Some examples of processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special-purpose artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any suitable processor, controller, microcontroller, etc. Processor 11 performs the various methods and processes described above, such as database synchronization methods.

[0161] In some embodiments, the database synchronization method may be implemented as a computer program tangibly contained in a computer-readable storage medium, such as storage unit 18. In some embodiments, part or all of the computer program may be loaded and / or installed on electronic device 10 via ROM 12 and / or communication unit 19. When the computer program is loaded into RAM 13 and executed by processor 11, one or more steps of the database synchronization method described above may be performed. Alternatively, in other embodiments, processor 11 may be configured to perform the database synchronization method by any other suitable means (e.g., by means of firmware).

[0162] Various embodiments of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field-programmable gate arrays (FPGAs), application-specific integrated circuits (ASICs), application-specific standard products (ASSPs), systems-on-a-chip (SoCs), payload-programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments may include implementations in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which may be a dedicated or general-purpose programmable processor, capable of receiving data and instructions from a storage system, at least one input device, and at least one output device, and transmitting data and instructions to the storage system, the at least one input device, and the at least one output device.

[0163] Computer programs used to implement the methods of the present invention can be written in any combination of one or more programming languages. These computer programs can be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when executed by the processor, the computer programs cause the functions / operations specified in the flowcharts and / or block diagrams to be performed. The computer programs can be executed entirely on a machine, partially on a machine, as a standalone software package partially on a machine and partially on a remote machine, or entirely on a remote machine or server.

[0164] In the context of this invention, a computer-readable storage medium can be a tangible medium that may contain or store a computer program for use by or in conjunction with an instruction execution system, apparatus, or device. A computer-readable storage medium may include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination thereof. Alternatively, a computer-readable storage medium may be a machine-readable signal medium. More specific examples of machine-readable storage media include electrical connections based on one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fibers, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof.

[0165] To provide interaction with a user, the systems and techniques described herein can be implemented on an electronic device having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and pointing device (e.g., a mouse or trackball) through which the user provides input to the electronic device. Other types of devices can also be used to provide interaction with the user; for example, feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including sound input, voice input, or tactile input).

[0166] The systems and technologies described herein can be implemented in computing systems that include backend components (e.g., as data servers), or computing systems that include middleware components (e.g., application servers), or computing systems that include frontend components (e.g., user computers with graphical user interfaces or web browsers through which users can interact with implementations of the systems and technologies described herein), or any combination of such backend, middleware, or frontend components. The components of the system can be interconnected via digital data communication of any form or medium (e.g., communication networks). Examples of communication networks include local area networks (LANs), wide area networks (WANs), blockchain networks, and the Internet.

[0167] A computing system can include clients and servers. Clients and servers are generally located far apart and typically interact through a communication network. The client-server relationship is created by computer programs running on the respective computers and having a client-server relationship with each other. The server can be a cloud server, also known as a cloud computing server or cloud host, which is a hosting product within the cloud computing service system to address the shortcomings of traditional physical hosts and VPS services, such as high management difficulty and weak business scalability.

[0168] It should be understood that the various forms of processes shown above can be used, with steps reordered, added, or deleted. For example, the steps described in this invention can be executed in parallel, sequentially, or in different orders, as long as the desired result of the technical solution of this invention can be achieved, and this is not limited herein.

[0169] The specific embodiments described above do not constitute a limitation on the scope of protection of this invention. Those skilled in the art should understand that various modifications, combinations, sub-combinations, and substitutions can be made according to design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of this invention should be included within the scope of protection of this invention.

Claims

1. A database synchronization method, characterized in that, include: Obtain the log information and data file information to be synchronized; Based on the log information to be synchronized and the data file information, determine the operation data page corresponding to the synchronization operation indicated by the log information to be synchronized; When the operation type corresponding to the operation to be synchronized is an update operation, the original row data and the updated row data corresponding to the operation to be synchronized are determined according to the log information to be synchronized and the operation data page. The original row data and the updated row data are the data before and after the operation to be synchronized are executed, respectively. Database synchronization is performed based on the original row data, the updated row data, and the log information to be synchronized. Based on the log information to be synchronized and the operation data page, determine the original row data and updated row data corresponding to the operation to be synchronized, including: Determine the slot identifier included in the log information to be synchronized, wherein the slot identifier indicates the identifier of the slot corresponding to the original row data of the operation to be synchronized; Determine the position mapping relationship recorded in the operation data page, wherein the position mapping relationship indicates the offset of the original row data corresponding to the slot identifier in the operation data page; Based on the slot identifier and the position mapping relationship, determine the original row data corresponding to the operation to be synchronized; Based on the log information to be synchronized and the original row data, determine the update row data corresponding to the synchronization operation.

2. The method according to claim 1, characterized in that, Based on the slot identifier and the position mapping relationship, the original row data corresponding to the synchronization operation is determined, including: Based on the slot identifier, a query is performed in the position mapping relationship to determine the offset of the original row data corresponding to the slot identifier in the operation data page; The offset is used to offset within the operation data page to determine the original row data corresponding to the operation to be synchronized.

3. The method according to claim 1, characterized in that, Based on the log information to be synchronized and the original row data, the update row data corresponding to the synchronization operation is determined, including: Determine the data update information included in the log information to be synchronized, wherein the data update information indicates the updated byte in the original row data and the update offset corresponding to the updated byte; Based on the starting position of the original row data, the offset position after offset is determined by the updated offset. The updated row data is obtained by replacing the byte corresponding to the offset position in the original row data with the updated byte.

4. The method according to claim 1, characterized in that, Database synchronization is performed based on the original row data, the updated row data, and the log information to be synchronized, including: Determine the table identifier included in the log information to be synchronized, wherein the table identifier indicates the identifier of the database table corresponding to the operation to be synchronized; Determine the table structure of the database table corresponding to the table identifier; Based on the table structure, the original row data and the updated row data are parsed to obtain the update operation content corresponding to the operation to be synchronized. The update operation content indicates the fields and data that have changed in the updated row data compared to the original row data. The update operation content is transferred to the backup database; Write the updated row data to the operation data page.

5. The method according to claim 1, characterized in that, Based on the log information to be synchronized and the data file information, determine the operation data page corresponding to the synchronization operation indicated by the log information to be synchronized, including: Determine the file identifier and data page identifier included in the log information to be synchronized, wherein the file identifier indicates the identifier of the target data file corresponding to the operation to be synchronized, and the data page identifier indicates the identifier of the operation data page; Among the one or more data files indicated by the data file information, the data file corresponding to the file identifier is determined as the target data file; In the target data file, the product of the data page identifier and the data page size is determined as the starting position of the operation data page in the target data file. The target data file includes one or more data pages, and the multiple data pages have the same data page size. The operation data page is determined by taking the starting position of the page as the starting position of the operation data page in the target data file and taking the size of the data page as the size of the operation data page.

6. The method according to claim 1, characterized in that, Also includes: When the operation type corresponding to the operation to be synchronized is a deletion operation, the operation to be synchronized is parsed according to the table structure of the database table corresponding to the operation to be synchronized to obtain the deletion operation content corresponding to the operation to be synchronized. The deletion operation content is transferred to the backup database; In the operation data page, delete the data corresponding to the slot identifier included in the log information to be synchronized.

7. A database synchronization device, characterized in that, include: The acquisition module is used to acquire the log information and data file information to be synchronized; The data page determination module is used to determine the operation data page corresponding to the synchronization operation indicated by the log information to be synchronized, based on the log information to be synchronized and the data file information. The row data determination module is used to determine the original row data and updated row data corresponding to the operation to be synchronized based on the log information to be synchronized and the operation data page when the operation type corresponding to the operation to be synchronized is an update operation. The original row data and the updated row data are respectively the data before and after the operation to be synchronized is executed. The synchronization module is used to synchronize the database based on the original row data, the updated row data, and the log information to be synchronized. The row data determination module is specifically used for: Determine the slot identifier included in the log information to be synchronized, wherein the slot identifier indicates the identifier of the slot corresponding to the original row data of the operation to be synchronized; Determine the position mapping relationship recorded in the operation data page, wherein the position mapping relationship indicates the offset of the original row data corresponding to the slot identifier in the operation data page; Based on the slot identifier and the position mapping relationship, determine the original row data corresponding to the operation to be synchronized; Based on the log information to be synchronized and the original row data, determine the update row data corresponding to the synchronization operation.

8. An electronic device, characterized in that, include: At least one processor; as well as A memory communicatively connected to the at least one processor; wherein, The memory stores a computer program that can be executed by the at least one processor to enable the at least one processor to perform the method as described in any one of claims 1-6.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the program is executed by the processor, it implements the method as described in any one of claims 1-6.

Images