Database migration method, apparatus, device, medium, and program product

Abstract

Embodiments of the present application provide a database migration method, device, equipment, medium and program product. The method comprises: at the starting moment of migrating the inventory data of a source database to a target database, for any one of a plurality of tables to be migrated, based on a change data capture table, obtaining a first target log sequence number of a migration transaction corresponding to the table to be migrated; recording the first target log sequence number as low-water information; at the end moment of migrating the inventory data of the source database to the target database, based on the change data capture table, obtaining a second target log sequence number of a migration transaction corresponding to the table to be migrated; recording the second target log sequence number as high-water information; and based on the high-water information and the low-water information, synchronizing the incremental data of the source database to the target database. The method is used to improve the efficiency of database migration.

Description

Technical Field

[0001] This application relates to the field of database migration, and more particularly to a database migration method, apparatus, device, medium, and program product. Background Technology

[0002] In enterprise-level database migration scenarios, SQL Server (a type of database) serves as the core data storage system, commonly used to support business systems in sectors such as finance, telecommunications, and healthcare that have extremely high requirements for data consistency and high availability. When business expansion, system upgrades, or disaster recovery failover are required, it is necessary to completely migrate all existing data (full data) and real-time incremental data (such as transaction logs and business operation records) from the SQL Server database to a heterogeneous database.

[0003] Traditional migration solutions typically rely on intermediate databases or backup and restore technologies, but these methods have significant drawbacks in large database scenarios: intermediate databases require additional storage space and computing resources, and the backup and restore process is time-consuming, failing to meet the real-time requirements of production environments.

[0004] Therefore, improving the efficiency of database migration is an urgent problem to be solved. Summary of the Invention

[0005] This application provides a database migration method, apparatus, device, medium, and program product to improve the efficiency of database migration.

[0006] In a first aspect, embodiments of this application provide a database migration method, including:

[0007] At the start of migrating existing data from the source database to the target database, for any one of the multiple tables to be migrated, the first target log sequence number of the migration transaction corresponding to the table to be migrated is obtained based on the change data capture table.

[0008] The first target log sequence number is recorded as low-water mark information; the low-water mark information is used to characterize the starting position of the migration transaction.

[0009] At the end of the migration of existing data from the source database to the target database, the second target log sequence number of the migration transaction corresponding to the table to be migrated is obtained based on the change data capture table.

[0010] The second target log sequence number is recorded as high-water mark information; the high-water mark information is used to characterize the end position of the migration transaction;

[0011] Based on the high water level information and the low water level information, the incremental data of the source database is synchronized to the target database.

[0012] In one possible implementation, obtaining the first target log sequence number of the migration transaction corresponding to the table to be migrated, based on the change data capture table, includes:

[0013] Based on the change data capture table, obtain the first log sequence number of the migration transaction corresponding to the table to be migrated; the first log sequence number is located before the first target log sequence number.

[0014] Within the range of the first log sequence number and the first end log sequence number, the first target log sequence number is obtained; the first end log sequence number is used to characterize the end record position of the migration transaction corresponding to the table to be migrated at the start time.

[0015] In one possible implementation, obtaining the second target log sequence number of the migration transaction corresponding to the table to be migrated, based on the change data capture table, includes:

[0016] Based on the change data capture table, obtain the second log sequence number of the migration transaction corresponding to the table to be migrated; the second log sequence number is located before the second target log sequence number.

[0017] Within the range of the second log sequence number and the second end log sequence number, the second target log sequence number is obtained; the second end log sequence number is used to characterize the last record position of the migration transaction corresponding to the table to be migrated at the end time.

[0018] In one possible implementation, before synchronizing incremental data from the source database to the target database based on the high water level information and the low water level information, the method further includes:

[0019] The log files of the source database are parsed to obtain the log sequence numbers in the log files;

[0020] The step of synchronizing incremental data from the source database to the target database based on the high water level information and the low water level information includes:

[0021] When the log sequence number is less than the low watermark information, the operation corresponding to the log sequence number is not executed;

[0022] or,

[0023] When the log sequence number is greater than or equal to the low watermark information and less than the high watermark information, conflict handling is performed on the operation corresponding to the log sequence number.

[0024] When the log sequence number is greater than or equal to the high watermark information, the operation corresponding to the log sequence number is synchronized to the target database.

[0025] In one possible implementation, the conflict handling for the operation corresponding to the log sequence number includes:

[0026] In response to the operation corresponding to the log sequence number being an update operation, a target conflict handling step is executed; the target conflict handling step includes: splitting the update operation into a delete operation and then executing an insert operation.

[0027] In one possible implementation, the conflict handling includes:

[0028] If the operation corresponding to the log sequence number is an insert operation, and the incremental synchronization transaction time corresponding to the insert operation is before the existing data migration transaction time corresponding to the insert operation, an error message will be output.

[0029] Secondly, embodiments of this application provide a database migration apparatus, the apparatus comprising:

[0030] The first acquisition module is used to acquire the first target log sequence number of the migration transaction corresponding to any one of the multiple tables to be migrated, based on the change data capture table, at the start time of migrating the existing data of the source database to the target database.

[0031] The first recording module is used to record the first target log sequence number as low-water mark information; the low-water mark information is used to characterize the starting position of the migration transaction.

[0032] The second acquisition module is used to acquire the second target log sequence number of the migration transaction corresponding to the table to be migrated based on the change data capture table at the end time of migrating the existing data of the source database to the target database.

[0033] The second recording module is used to record the second target log sequence number as high-water mark information; the high-water mark information is used to characterize the end position of the migration transaction.

[0034] The synchronization module is used to synchronize incremental data from the source database to the target database based on the high water level information and the low water level information.

[0035] Thirdly, embodiments of this application provide an electronic device, including: a memory and a processor;

[0036] The memory stores computer-executed instructions;

[0037] The processor executes computer execution instructions stored in the memory, causing the processor to perform the method described in any of the first aspects above.

[0038] Fourthly, embodiments of this application provide a computer-readable storage medium storing computer-executable instructions, which, when executed by a processor, are used to implement the method described in any of the first aspects above.

[0039] Fifthly, embodiments of this application provide a computer program product, including a computer program that, when executed by a processor, implements the method described in any of the first aspects above.

[0040] This application provides a database migration method, apparatus, device, medium, and program product that accurately obtains the start and end log sequence numbers of migration transactions by changing the data capture table, defines high and low water level information, achieves efficient synchronization of incremental data, ensures the integrity and consistency of data during the migration process, and significantly improves the accuracy of data synchronization and migration efficiency. Attached Figure Description

[0041] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with this application and, together with the description, serve to explain the principles of this application.

[0042] Figure 1 A schematic flowchart illustrating a database migration method provided in an embodiment of this application;

[0043] Figure 2 A flowchart illustrating a specific database migration method provided in this application embodiment;

[0044] Figure 3 A flowchart illustrating the process of recording LSN information in all read threads is provided in this embodiment of the application.

[0045] Figure 4 A water level time perspective view provided for an embodiment of this application;

[0046] Figure 5 A schematic diagram of a database migration device provided in this application;

[0047] Figure 6 This is a schematic diagram of the structure of an electronic device provided in this application.

[0048] The accompanying drawings have illustrated specific embodiments of this application, which will be described in more detail below. These drawings and descriptions are not intended to limit the scope of the concept in any way, but rather to illustrate the concept of this application to those skilled in the art through reference to specific embodiments. Detailed Implementation

[0049] Exemplary embodiments will now be described in detail, examples of which are illustrated in the accompanying drawings. When the following description relates to the drawings, unless otherwise indicated, the same numbers in different drawings represent the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with this application.

[0050] In this application, the term "comprising" and its variations can refer to non-limiting inclusion; the term "or" and its variations can refer to "and / or". The terms "first", "second", etc., in this application are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. In this application, "multiple" refers to two or more. "And / or" describes the relationship between related objects, indicating that three relationships can exist. For example, A and / or B can represent: A existing alone, A and B existing simultaneously, and B existing alone. The character " / " generally indicates that the preceding and following related objects have an "or" relationship.

[0051] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties. Furthermore, the collection, use and processing of the relevant data must comply with the relevant laws, regulations and standards of the relevant countries and regions, and corresponding operation entry points are provided for users to choose to authorize or refuse.

[0052] In enterprise-level database migration scenarios, SQL Server databases, as core data storage systems, are commonly used to support business systems in sectors such as finance, telecommunications, and healthcare that have extremely high requirements for data consistency and high availability. When business expansion, system upgrades, or disaster recovery failover are required, it is necessary to completely migrate all existing data (full data) and real-time incremental data (such as transaction logs and business operation records) from the SQL Server database to a heterogeneous database.

[0053] Traditional migration solutions typically rely on intermediate databases or backup and restore technologies, but these methods have significant drawbacks in large database scenarios: intermediate databases require additional storage space and computing resources, and the backup and restore process is time-consuming, failing to meet the real-time requirements of production environments.

[0054] Therefore, there is an urgent need for a heterogeneous migration solution that requires no intermediate databases, requires no downtime, and can efficiently handle the synchronization of full and incremental data to ensure business continuity, data consistency, and migration efficiency. Such scenarios place extremely high demands on the stability, performance, and conflict handling capabilities of migration tools, especially when dealing with high-concurrency transactions and massive amounts of data, requiring innovative technical means to overcome the bottlenecks of existing methods.

[0055] This application embodiment accurately obtains the start and end log sequence numbers of migration transactions by changing the data capture table, defines high and low water level information, realizes efficient synchronization of incremental data, ensures the integrity and consistency of data during the migration process, and significantly improves the accuracy of data synchronization and migration efficiency.

[0056] The technical solution of this application and how the technical solution of this application solves the above-mentioned technical problems are described in detail below with specific embodiments. These specific embodiments can be combined with each other, and the same or similar concepts or processes may not be described again in some embodiments. The embodiments of this application will now be described with reference to the accompanying drawings.

[0057] First, the terms used in the embodiments of this application will be explained.

[0058] Log Sequence Number (LSN): A unique identifier used by SQL Server databases to identify and track transaction status and data changes.

[0059] Low watermark: During the migration process of a certain table, it is used to identify the maximum LSN of the existing data that has been successfully migrated to the target database.

[0060] High watermark: For a table during migration, it is used to identify the LSN corresponding to the end position of the existing data being migrated.

[0061] Conflict: A situation where the current transaction fails because it has already been executed on the target side.

[0062] Full migration: A method of moving existing data from one database to another using a certain tool.

[0063] Incremental synchronization: A method that replays dynamically added data changes on the target device based on the full migration of data.

[0064] It should be noted that the executing entity of this application can be any electronic device with processing capabilities, such as a user terminal or a server, for example, a computer.

[0065] Figure 1 This is a flowchart illustrating a database migration method provided in an embodiment of this application. Figure 1 As shown, the method includes:

[0066] S101. At the start of migrating the existing data from the source database to the target database, for any one of the multiple tables to be migrated, obtain the first target log sequence number of the migration transaction corresponding to the table to be migrated based on the change data capture table.

[0067] Optionally, the source database can be the original database for data migration, such as a SQL Server database. The target database can be the receiving end of the data migration, and it can be isomorphic or heterogeneous with the source database. Existing data can be static data that already exists in the source database at the start of the migration.

[0068] Optionally, the table to be migrated can be a table that requires data migration. The table to be migrated can contain data that needs to be migrated from the source database to the target database, and different tables to be migrated can have different table structures, data volumes, and business purposes.

[0069] Optionally, the Change Data Capture (CDC) table can be used to record data change information in the database. It can capture data insertion, update, deletion and other operations, and record the corresponding log sequence number and other information to track data changes.

[0070] Optionally, the first target log sequence number can be a specific number in the log sequence corresponding to the migration transaction of the table to be migrated, obtained by querying the change data capture table at the start of the migration of inventory data from the source database to the target database. This number identifies the starting record position of the migration transaction at that moment. A migration transaction can be a set of related operations that perform data operations on the database table during the data migration process.

[0071] Optionally, the electronic device can access the CDC table by writing SQL query statements, and filter out the log sequence number of the migration transaction corresponding to the table to be migrated from the CDC table based on the relevant identification information of the table to be migrated (such as the table name), thereby obtaining the first target log sequence number.

[0072] S102. Record the first target log sequence number as low-water information; the low-water information is used to characterize the starting position of the migration transaction.

[0073] Optionally, low-water mark information can record the starting point of migration transactions in the source database log, which can serve as the starting point for existing data migration and the benchmark point for incremental data synchronization. Electronic devices can store the acquired first target log sequence number in a specific data structure (such as variables, configuration files, database tables, etc.) so that this information can be easily read and used during subsequent data synchronization to determine the starting position of incremental data synchronization.

[0074] S103. At the end of the migration of existing data from the source database to the target database, obtain the second target log sequence number of the migration transaction corresponding to the table to be migrated based on the change data capture table.

[0075] Optionally, the end time can be the time when the operation of migrating existing data from the source database to the target database is completed. The second target log sequence number can be another specific number in the log sequence corresponding to the migration transaction of the table to be migrated, obtained by querying the CDC table at the end time of migrating existing data from the source database to the target database, used to identify the end record position of the migration transaction at that time.

[0076] Optionally, similar to obtaining the first target log sequence number, the electronic device can again write an SQL query to access the change data capture table, and based on the identification information of the table to be migrated, filter out the log sequence number of the migration transaction corresponding to the table to be migrated at the end of the existing data migration from the CDC table, which is the second target log sequence number.

[0077] S104. Record the second target log sequence number as high-water mark information; the high-water mark information is used to characterize the end position of the migration transaction.

[0078] Optionally, high-water mark information can be used to mark the end position of a migration transaction, recording the end point of the migration transaction in the source database log. High-water mark information can determine which data in the source database has been processed and which has not at the end of the migration transaction, thus avoiding duplicate or missed processing.

[0079] Optionally, the electronic device can store the acquired first target log sequence number in a specific data structure (such as a variable, configuration file, database table, etc.) so that the information can be easily read and used during subsequent data synchronization to determine the cutoff position for incremental data synchronization.

[0080] S105. Based on high water level information and low water level information, the incremental data of the source database is synchronized to the target database.

[0081] Optionally, incremental data can be new data changes that occur in the source database after the existing data migration is completed. Electronic devices can synchronize incremental data changes to the target database in real time or near real time, maintaining eventual consistency between the two databases.

[0082] For example, electronic devices can first parse the logs of the source database to obtain incremental data from the source database. Based on the logs and high-water mark and low-water mark information of the database, they can determine the data entry strategy for the incremental data and transform or specially process the data that needs to be conflicted in order to achieve the synchronization of incremental data.

[0083] For example, an electronic device can obtain the high-water mark and low-water mark information of the data to be migrated from the source database during the migration of existing data from the source database to the target database through the following process:

[0084] 1. Read all table information (i.e. data to be migrated) from the source database through the system table.

[0085] 2. Place all tables in a queue to await data migration.

[0086] 3. Retrieve table information from the queue and create threads to perform data migration. This process will be executed cyclically, with the number of threads controlled by the thread pool.

[0087] 4. Create a Java Database Connectivity (JDBC) database connection in the read thread.

[0088] 5. Execute the LSN acquisition method via JDBC connection and record the current LSN as the low watermark in memory.

[0089] 6. Start reading data from the source database and writing data to the target database via JDBC connection.

[0090] 7. After the data migration of this table is completed, obtain the current LSN as the high watermark of this table, and write the high and low watermarks temporarily stored in memory to the specified file.

[0091] This application embodiment accurately obtains the start and end log sequence numbers of migration transactions by changing the data capture table, defines high and low water level information, realizes efficient synchronization of incremental data, ensures the integrity and consistency of data during the migration process, and significantly improves the accuracy of data synchronization and migration efficiency.

[0092] The following section provides a detailed description of how electronic devices obtain the first and second target log sequence numbers of the migration transactions corresponding to the tables to be migrated based on the change data capture table.

[0093] Electronic devices can first obtain the first log sequence number of the migration transaction corresponding to the table to be migrated based on the change data capture table; the first log sequence number is located before the first target log sequence number.

[0094] Optionally, the first log sequence number is a log sequence number recorded in the CDC table related to the migration transaction corresponding to the table to be migrated. It is located earlier in the data change log sequence, before the first target log sequence number, and is used to initially locate the starting range of the data change. The first log sequence number is the log sequence number in the CDC table that is closest to the first target log sequence number.

[0095] Secondly, within the range of the first log sequence number and the first end log sequence number, the electronic device can obtain the first target log sequence number; the first end log sequence number is used to characterize the last record position of the migration transaction corresponding to the table to be migrated at the start time.

[0096] Optionally, the first end log sequence number is used to identify the last record position of the migration transaction corresponding to the table to be migrated in the CDC table at the start of the data migration. It defines an end boundary of the data change and, together with the first log sequence number, forms an interval for further filtering the first target log sequence number. The first target log sequence number can be the latest log sequence number within the interval between the first log sequence number and the first end log sequence number.

[0097] This application's embodiments optimize the process of obtaining low-water level information based on the change data capture table by introducing a first log sequence number and a first end log sequence number. This improves the accuracy of low-water level acquisition, reduces the resource consumption of full table scan log files, and speeds up migration startup.

[0098] Similarly, electronic devices can first obtain the second log sequence number of the migration transaction corresponding to the table to be migrated based on the change data capture table; the second log sequence number is located before the second target log sequence number.

[0099] Optionally, the second log sequence number is one of two log sequence numbers recorded in the CDC table related to the migration transaction corresponding to the table to be migrated. It is located earlier in the data change log sequence, before the second target log sequence number, and is used to initially locate the starting range of the data change. The second log sequence number is the log sequence number in the CDC table that is closest to the second target log sequence number.

[0100] Next, within the range of the second log sequence number and the second end log sequence number, obtain the second target log sequence number; the second end log sequence number is used to characterize the last record position of the migration transaction corresponding to the table to be migrated at the end time.

[0101] Optionally, the second end log sequence number is used to identify the last record position of the migration transaction corresponding to the table to be migrated in the CDC table at the start of the data migration. It defines the end boundary of the data change and, together with the second log sequence number, forms an interval for further filtering the second target log sequence number. The second target log sequence number can be the latest log sequence number within the interval between the second log sequence number and the second end log sequence number.

[0102] This application's embodiments optimize the high-water mark information acquisition process at the end of the migration. By introducing a second log sequence number and a second end log sequence number, the accuracy of high-water mark acquisition is improved, ensuring data consistency and integrity during incremental synchronization and avoiding data inconsistency issues caused by inaccurate high-water mark positioning.

[0103] For example, an electronic device can use the following SQL statement to obtain the first target log sequence number and the second target log sequence number of the migration transaction corresponding to the table to be migrated.

[0104] The SQL statement is SELECT max(start_lsn) from cdc.lsn_time_mapping, and the return value is 0000374e0000013F0001.

[0105] The SQL statement is SELECT max([Current LSN]) FROM fn_dblog('0x0000374e:0000013F:0001', NULL), and the return value is 0000374e:00000177:0003.

[0106] In this context, 0000374e0000013F0001 can be either the first log sequence number or the second log sequence number, 0000374e:00000177:0003 can be either the first target log sequence number or the second target log sequence number, and NULL can be either the first end log sequence number or the second end log sequence number.

[0107] In one implementation, before synchronizing incremental data from the source database to the target database based on high-water level information and low-water level information, the electronic device can also parse the log file of the source database to obtain the log sequence number in the log file.

[0108] Optionally, the electronic device can use a preset function to parse the log file of the source database to obtain the log sequence number in the log file. After obtaining the log sequence number in the log file, the electronic device can perform different operations on the incremental data of the source database based on the comparison between the high-water mark information, low-water mark information and the log sequence number, as follows:

[0109] If the log sequence number is less than the low watermark, it means that the operation occurred before the data migration started and has already been processed during the existing data migration process. Therefore, the electronic device does not need to execute it again, that is, it does not execute the operation corresponding to the log sequence number.

[0110] When the log sequence number is greater than or equal to the high-water mark, it indicates that the operation was an incremental data change that occurred after data migration was completed. In this case, the electronic device can synchronize the operation corresponding to the log sequence number to the target database. Optionally, the corresponding operation can be performed in the target database based on the type of log event (such as insert, update, delete, etc.).

[0111] When the log sequence number is greater than or equal to the low watermark and less than the high watermark, it indicates that the operation corresponding to that log sequence number occurred during data migration. In this case, it's necessary to check if any data changes related to that operation already exist in the target database. If so, a conflict may occur, requiring conflict resolution for the operation corresponding to the log sequence number.

[0112] This application's embodiments are based on the log file parsing process before incremental data synchronization and formulate a precise synchronization strategy based on high and low water level information. This method improves synchronization efficiency and reduces unnecessary data processing by precisely controlling the incremental synchronization range. Simultaneously, this method enhances system stability, reduces errors and conflicts during the synchronization process, and ensures the accuracy and reliability of data migration.

[0113] The following provides a detailed explanation of how electronic devices handle conflict executions corresponding to log sequence numbers.

[0114] In one implementation, conflict handling for an operation corresponding to a log sequence number may be performed in response to the operation corresponding to the log sequence number being an update operation, by executing a target conflict handling step, which may include splitting the update operation into a delete operation and then performing an insert operation.

[0115] Optionally, an update operation can be an atomic operation that modifies a data record, such as changing the value of a field in a database table from A to B. A delete operation can be the removal of a data record (such as the DELETE statement in SQL). An insert operation can be the creation of a new data record (such as the INSERT statement in SQL). The conflict resolution steps can be to resolve conflicts by breaking down an update operation into a delete operation followed by an insert operation, ensuring the consistency of the final data state.

[0116] Optionally, when the electronic device detects that the operation corresponding to the log sequence number is an update operation, it can trigger conflict handling logic, first verify the legality of the operation (such as permissions and data version), and then execute the split sub-operations according to a predetermined strategy, that is, execute the deletion operation first and then execute the insertion operation.

[0117] This application embodiment solves the conflict problem caused by the existence of the same primary key record in the target database when directly updating the primary key or condition column by splitting the update operation into deleting the old record and inserting the new record, thus ensuring eventual consistency.

[0118] In one implementation, if the operation corresponding to the log sequence number is an insert operation, and the incremental synchronization transaction time corresponding to the insert operation is before the existing data migration transaction time corresponding to the insert operation, an error message is output.

[0119] Optionally, the electronic device can monitor the operation type (insert / update / delete) corresponding to the log sequence number in real time and associate the operation with the timestamp (or transaction ID). For insert operations, it is necessary to additionally check the start time of the incremental synchronization transaction to which it belongs and the start time of the current existing data migration transaction to determine whether the time of the incremental synchronization transaction corresponding to the insert operation is before the time of the existing data migration transaction corresponding to the insert operation.

[0120] If the incremental synchronization transaction time corresponding to the insert operation is before the existing data migration transaction time corresponding to the insert operation, it may cause the data inserted by the incremental synchronization transaction to be overwritten or conflicted by the existing data migration transaction, violating the unique key constraint. In this case, an error message can be output.

[0121] Optionally, electronic devices can output error messages via pop-up windows. It should be noted that, based on the consistency principle, electronic devices may only block conflicting operations without automatically rolling back or repairing data. Error messages can contain sufficient information for manual intervention (such as adjusting task order).

[0122] In this embodiment of the application, when the incremental synchronization insertion operation time is earlier than the existing data migration transaction time, the situation of data overwriting or unique key conflict is reduced, thus ensuring data security.

[0123] For example, the basic process of the database migration method in this application embodiment may include:

[0124] 1. Enable the source log parsing function of the incremental data synchronization program;

[0125] 2. Start the full data migration module and enable the low watermark recording function. At the same time, all primary key and unique key constraints need to be migrated.

[0126] 3. After the full migration module is completed, enable the high-water mark recording function and start the target end writing function of the incremental data synchronization program.

[0127] 4. The target end of the incremental data synchronization program uses a conflict handling function to resolve conflicting data between high and low water levels;

[0128] 5. Once all incremental data has been caught up, or once the high-water mark synchronization of all table data has been completed, the synchronization process will be automatically turned off, and the migration of other objects, such as foreign keys and triggers, which may restrict data migration, will be completed.

[0129] The key to a full-scale migration is obtaining the timing of high and low water levels. The specific steps are as follows:

[0130] 1. Read all table information to be migrated from the source end through the system table;

[0131] 2. Place all tables in a queue to await data migration;

[0132] 3. Retrieve table information from the queue and create threads to perform data migration. This process will be executed cyclically, with the number of threads controlled by the thread pool.

[0133] 4. Create a JDBC database connection in the read thread;

[0134] 5. Execute the low-water mark retrieval method via JDBC connection and temporarily store it in memory;

[0135] 6. Begin reading data from the source via JDBC connection;

[0136] 7. After the result set is returned, execute the high-water mark retrieval method via JDBC connection and temporarily store it in memory;

[0137] 8. After the data migration of this table is completed, the high and low watermarks temporarily stored in memory will be written to the specified file.

[0138] In step 5 of the full migration, existing technology can use the following SQL statement to obtain the low watermark: `SELECT max([Current LSN]) FROM fn_dblog(NULL,NULL)`. This SQL statement parses the LSNs of all transactions from the log file and returns the largest one, which is the current LSN. However, in a production environment, due to the large number of transactions, this method is very slow and may even fail to return results, making it unusable in actual production.

[0139] Therefore, this embodiment uses the SQL statement: SELECT max(start_lsn) from cdc.lsn_time_mapping to query the latest LSN in the CDC table. Because CDC has a delay, but this table has high query efficiency, a relatively recent LSN is obtained, with a return value of 0000374e0000013F0001. Then, a second SQL statement is used: SELECT max([Current LSN]) FROM fn_dblog('0x0000374e:0000013F:0001', NULL), to obtain the actual latest LSN based on the first LSN, with a return value of 0000374e:00000177:0003. This way, the query does not traverse all logs, thus significantly improving the efficiency of obtaining the LSN.

[0140] In step 6, unlike MySQL (a type of database), where the high watermark can be retrieved immediately after the result set is retrieved, SQL Server requires all data to be read before the high watermark can be retrieved. This is because data not yet read in SQL Server can be modified or deleted, and newly added data can also be retrieved from the already retrieved result set. Therefore, the high watermark must be obtained only after all data has been read, using the latest LSN as the high watermark.

[0141] The record format for water level files can be as follows:

[0142] {

[0143] “database1”: {

[0144] "table_1": ["0000374e000001770003", "0000374e000001920002"],

[0145] "table_2": ["0000374e000001830004", "0000374e0000018a0001"]

[0146] }

[0147] }

[0148] Repeat this process until all tables that need to be migrated are migrated, and then pass the water level file to the subsequent steps.

[0149] During the data import process into the target database, incremental data synchronization is achieved by importing data parsed from the source database. The following steps need to be followed:

[0150] 1. Obtain the parsed data information;

[0151] 2. Determine the inbound strategy based on the LSN and water level information in the data file;

[0152] 3. Conflict resolution data;

[0153] 4. Directly import data into the database.

[0154] In step 2, firstly, all tables involved in the currently being processed are operated on separately, and the current log offset is compared with the water level information in the water level file.

[0155] If the data is less than the low water level, the operation is ignored;

[0156] If the data is greater than or equal to the low watermark but less than the high watermark, then source-side operations are performed, and conflict handling is required. Conflict handling methods will be detailed later.

[0157] If the data is greater than or equal to the high water level, the source operation is executed directly. If there is a problem, error handling is required, but conflict handling is not allowed.

[0158] If any of the above operations involve operations that can be merged, then those operations should be merged to improve execution efficiency.

[0159] The conflict resolution method is explained in detail below.

[0160] Data with a value greater than or equal to the low watermark but less than the high watermark will result in an error (due to conflicting operations). This is because the data in the full migration does not belong to the same transaction, leading to numerous data conflicts. For example, if a transaction adds data between the high and low watermarks, duplicate operations may occur during the target database insertion. Furthermore, all update operations need to be converted to delete and insert operations to avoid issues arising from modified filter conditions that could lead to subsequent data inconsistencies.

[0161] In actual migration, the main situations can be divided into the following categories:

[0162] 1. Insertion operations performed by synchronous transactions before existing data migration transactions may violate unique key constraints. This situation does not require special handling and also satisfies the principle of eventual consistency.

[0163] 2. Insertion operations performed by synchronous transactions after existing data migration transactions will not result in any errors and will satisfy the principle of eventual consistency.

[0164] 3. If a deletion operation is performed in a synchronous transaction before the existing data migration transaction, no error will occur when the deletion is performed again, thus satisfying the principle of eventual consistency.

[0165] 4. The deletion operation performed after the existing data migration transaction will not cause any errors and will satisfy the principle of eventual consistency.

[0166] 5. Update operations performed by synchronous transactions before existing data migration transactions will also be executed normally, thus satisfying the principle of eventual consistency.

[0167] 6. Synchronous transactions perform update operations after existing data migration transactions. At this time, no errors will occur, and the principle of eventual consistency is also satisfied.

[0168] The above covers all the scenarios considered in conflict resolution.

[0169] During a non-disruptive migration, if an error occurs in one record within a transaction, the entire transaction can be skipped while other records execute normally. If a record within a transaction fails to execute, the transaction is committed. The behavior varies depending on the database. Manually constructing conflicting transactions and committing them after a conflict occurs are also possible. The database behaviors in these scenarios are shown in Table 1.

[0170] Table 1

[0171]

[0172] When resolving conflicts, it's important to handle them differently depending on the database's behavior. In databases where data is successfully entered without errors, no special handling is needed; they can be ignored. However, in databases where transactions have failed, special processing is required. This article uses KES as an example to propose a solution.

[0173] When the target is KES, during insert operations, if the number of transaction SQLs is small, no record needs to be made; all SQLs can be re-executed, and problematic SQLs can be skipped. If there are many records, an execution sequence number needs to be recorded, and a record should be made every N SQLs executed to ensure that too many SQLs are not rolled back.

[0174] After the data is synchronized, you can begin migrating other objects such as foreign keys and triggers. At this point, synchronization should be paused until the migration of other objects is complete. Restart the data synchronization function and disable conflict handling during the non-stop migration.

[0175] In summary, waiting for the migration of all data in this table or database to complete can be very long, resulting in a significant difference between the high and low watermarks. This leads to substantial performance overhead for conflict handling during subsequent synchronization and requires more time to catch up on the data. The embodiments of this application can immediately obtain the high watermark after returning the result set, thus reducing the gap between the high and low watermarks.

[0176] This application embodiment can also employ different conflict handling methods based on the order of synchronization and migration transactions, as well as different operation types, to achieve final data consistency. Using different processing methods for transactions at different stages significantly reduces the performance overhead of conflict handling; for example, synchronization transactions below the low watermark can be directly filtered out.

[0177] The embodiments of this application can achieve the following technical effects:

[0178] 1. By reducing the difference between high and low watermarks, and by obtaining high and low watermark information separately for each table, operations that ignore transactions outside of the high and low watermarks have been added. Overall, the window range for conflict handling has been significantly reduced, improving overall synchronization performance.

[0179] 2. Most errors in conflict resolution do not require any processing to achieve eventual consistency, thus improving conflict resolution performance.

[0180] 3. The update operation has been converted into delete and insert operations, which solves the problem of not being able to achieve eventual consistency when updating primary keys or condition columns.

[0181] Figure 2 A flowchart illustrating a specific database migration method provided in this application embodiment is shown below. Figure 2 As shown, the process may include:

[0182] 1. Synchronization program log parsing begins.

[0183] 2. The full migration process starts and retrieves information from all tables.

[0184] 3. Obtain low water level information and record it in memory.

[0185] 4. Query the data and complete the migration of this table.

[0186] 5. Obtain high water level information and record it in memory.

[0187] 6. Water level information is recorded to a file.

[0188] 7. The incremental synchronization target program starts and reads the water level file.

[0189] 8. Data Ingestion. Data ingestion can be divided into three types: INSERT, UPDATE, and DELETE. UPDATE can be further divided into DELETE and INSERT.

[0190] 9. Put into storage.

[0191] 10. Determine if an error occurs. If so, perform conflict resolution; otherwise, end the process.

[0192] Take a table as an example. Figure 3 This is a schematic diagram illustrating a process for recording LSN information across all read threads, as provided in an embodiment of this application. Figure 3 As shown, this process can first obtain the current LSN information as low watermark information and store it in memory, then execute a query statement to read the data and write it to the thread; then obtain the current LSN information as high watermark information, and finally record it. Then read the data and finally write the data to the thread for processing. This process ensures the accurate recording of watermark information and the stable reading of data.

[0193] Figure 4This application provides a water level time perspective view, illustrating the change of log offset over time and different stages of data migration. It should be noted that the log offset here represents the LSN (Local Subsequent Number), such as... Figure 4 As shown, the time axis is plotted horizontally and the log offset vertically, indicating the positions of the low and high watermarks. After log parsing, a full migration begins, filtering data based on the watermark information. This is followed by a potentially conflict-prone phase, as incremental data may overwrite the fully migrated data, leading to conflicts. After the full migration is complete, the incremental data is processed. The diagram uses a single table (TABLE1) as an example to illustrate the data migration process over time and the characteristics of different stages.

[0194] The above are the method embodiments provided in this application. The apparatus provided in this application will be described below.

[0195] Figure 5 A schematic diagram of a database migration device provided in this application is shown below. Figure 5 As shown, the database migration device 400 provided in this embodiment includes: a first acquisition module 401, a first recording module 402, a second acquisition module 403, a second recording module 404, and a synchronization module 405. Optionally, the database migration device may further include a processing module 406.

[0196] The first acquisition module 401 is used to acquire the first target log sequence number of the migration transaction corresponding to any one of the multiple tables to be migrated, based on the change data capture table, at the start time of migrating the existing data of the source database to the target database.

[0197] The first recording module 402 is used to record the first target log sequence number as low-water mark information. The low-water mark information is used to characterize the starting position of the migration transaction.

[0198] The second acquisition module 403 is used to obtain the second target log sequence number of the migration transaction corresponding to the table to be migrated based on the change data capture table at the end of the migration of the existing data of the source database to the target database.

[0199] The second recording module 404 is used to record the second target log sequence number as high-water mark information. The high-water mark information is used to indicate the end position of the migration transaction.

[0200] The synchronization module 405 is used to synchronize incremental data from the source database to the target database based on high water level information and low water level information.

[0201] Optionally, the first acquisition module 401 is specifically used to acquire the first log sequence number of the migration transaction corresponding to the table to be migrated based on the change data capture table; the first log sequence number is located before the first target log sequence number. Within the range of the first log sequence number and the first end log sequence number, the first target log sequence number is acquired; the first end log sequence number is used to characterize the last record position of the migration transaction corresponding to the table to be migrated at the start time.

[0202] Optionally, the second acquisition module 403 is specifically used to acquire the second log sequence number of the migration transaction corresponding to the table to be migrated based on the change data capture table; the second log sequence number is located before the second target log sequence number. Within the range of the second log sequence number and the second end log sequence number, the second target log sequence number is acquired; the second end log sequence number is used to characterize the last record position of the migration transaction corresponding to the table to be migrated at the end time.

[0203] In one implementation, before the synchronization module 405 synchronizes incremental data from the source database to the target database based on high-water mark and low-water mark information, the processing module 406 parses the log file of the source database to obtain the log sequence number in the log file. Specifically, the synchronization module 405 does not execute the operation corresponding to the log sequence number when the log sequence number is less than the low-water mark information. Alternatively, it performs conflict handling on the operation corresponding to the log sequence number when the log sequence number is greater than or equal to the low-water mark information and less than the high-water mark information. When the log sequence number is greater than or equal to the high-water mark information, the operation corresponding to the log sequence number is synchronized to the target database.

[0204] For example, the processing module 406 is specifically used to execute target conflict handling steps in response to the operation corresponding to the log sequence number being an update operation; the target conflict handling steps include: splitting the update operation into a delete operation and then executing an insert operation.

[0205] In one implementation, conflict handling includes outputting an error message if the operation corresponding to the log sequence number is an insert operation and the incremental synchronization transaction time corresponding to the insert operation is before the existing data migration transaction time corresponding to the insert operation.

[0206] The database migration device provided in this embodiment can execute the methods provided in any of the above method embodiments. The implementation principle and technical effect are similar, and will not be described in detail here.

[0207] Figure 6 This is a schematic diagram of the structure of an electronic device provided in this application. Figure 6As shown, the electronic device 500 provided in this embodiment includes at least one processor 501 and a memory 502. Optionally, the device 500 further includes a communication component 503. The processor 501, memory 502, and communication component 503 are connected via a bus 504.

[0208] In a specific implementation, at least one processor 501 executes computer execution instructions stored in memory 502, causing at least one processor 501 to perform the above-described method.

[0209] The specific implementation process of processor 501 can be found in the above method embodiments, and its implementation principle and technical effect are similar. It will not be repeated here.

[0210] In the above embodiments, it should be understood that the processor can be a Central Processing Unit (CPU), or other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), etc. The general-purpose processor can be a microprocessor or any conventional processor. The steps of the method disclosed in this invention can be directly implemented by a hardware processor, or implemented by a combination of hardware and software modules within the processor.

[0211] The memory may include random access memory (RAM) and may also include non-volatile memory (NVM), such as at least one disk storage device.

[0212] The bus can be an Industry Standard Architecture (ISA) bus, a Peripheral Component Interconnect (PCI) bus, or an Extended Industry Standard Architecture (EISA) bus, etc. Buses can be categorized as address buses, data buses, control buses, etc. For ease of illustration, the buses shown in the accompanying drawings are not limited to a single bus or a single type of bus.

[0213] This application also provides a computer program product, including a computer program that, when executed by a processor, implements the above-described method.

[0214] This application also provides a computer-readable storage medium storing computer-executable instructions, which, when executed by a processor, implement the above-described method.

[0215] The aforementioned readable storage medium can be implemented by any type of volatile or non-volatile storage device or a combination thereof, such as static random access memory (SRAM), electrically erasable programmable read-only memory (EEPROM), erasable programmable read-only memory (EPROM), programmable read-only memory (PROM), read-only memory (ROM), magnetic storage, flash memory, magnetic disk, or optical disk. The readable storage medium can be any available medium accessible to a general-purpose or special-purpose computer.

[0216] An exemplary readable storage medium is coupled to a processor, enabling the processor to read information from and write information to the readable storage medium. Of course, the readable storage medium can also be a component of the processor. The processor and the readable storage medium can reside in an Application Specific Integrated Circuit (ASIC). Alternatively, the processor and the readable storage medium can exist as discrete components in the device.

[0217] The division of units is merely a logical functional division; in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be indirect coupling or communication connection through some interfaces, devices, or units, and may be electrical, mechanical, or other forms.

[0218] The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.

[0219] In addition, the functional units in the various embodiments of this application can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit.

[0220] If a function is implemented as a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, or a part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods of the various embodiments of this application. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.

[0221] Those skilled in the art will understand that all or part of the steps of the above-described method embodiments can be implemented by hardware related to program instructions. The aforementioned program can be stored in a computer-readable storage medium. When executed, the program performs the steps of the above-described method embodiments; and the aforementioned storage medium includes various media capable of storing program code, such as ROM, RAM, magnetic disks, or optical disks.

[0222] Finally, it should be noted that other embodiments of this application will readily conceive of by those skilled in the art upon consideration of the specification and practice of the invention disclosed herein. This application is intended to cover any variations, uses, or adaptations of this application that follow the general principles of this application and include common knowledge or customary techniques in the art not disclosed herein, and is not limited to the precise structures described above and shown in the accompanying drawings, and various modifications and changes may be made without departing from its scope.

Claims

1. A database migration method, characterized in that, The method includes: At the start of migrating existing data from the source database to the target database, for any one of the multiple tables to be migrated, the first target log sequence number of the migration transaction corresponding to the table to be migrated is obtained based on the change data capture table. The first target log sequence number is recorded as low-water mark information; the low-water mark information is used to characterize the starting position of the migration transaction. At the end of the migration of existing data from the source database to the target database, the second target log sequence number of the migration transaction corresponding to the table to be migrated is obtained based on the change data capture table. The second target log sequence number is recorded as high-water mark information; the high-water mark information is used to characterize the end position of the migration transaction; Based on the high water level information and the low water level information, the incremental data of the source database is synchronized to the target database.

2. The method according to claim 1, characterized in that, The step of obtaining the first target log sequence number of the migration transaction corresponding to the table to be migrated based on the change data capture table includes: Based on the change data capture table, obtain the first log sequence number of the migration transaction corresponding to the table to be migrated; the first log sequence number is located before the first target log sequence number. Within the range of the first log sequence number and the first end log sequence number, the first target log sequence number is obtained; the first end log sequence number is used to characterize the end record position of the migration transaction corresponding to the table to be migrated at the start time.

3. The method according to claim 1, characterized in that, The step of obtaining the second target log sequence number of the migration transaction corresponding to the table to be migrated based on the change data capture table includes: Based on the change data capture table, obtain the second log sequence number of the migration transaction corresponding to the table to be migrated; the second log sequence number is located before the second target log sequence number. Within the range of the second log sequence number and the second end log sequence number, the second target log sequence number is obtained; the second end log sequence number is used to characterize the last record position of the migration transaction corresponding to the table to be migrated at the end time.

4. The method according to any one of claims 1-3, characterized in that, Before synchronizing incremental data from the source database to the target database based on the high water level information and the low water level information, the method further includes: The log files of the source database are parsed to obtain the log sequence numbers in the log files; The step of synchronizing incremental data from the source database to the target database based on the high water level information and the low water level information includes: When the log sequence number is less than the low watermark information, the operation corresponding to the log sequence number is not executed; or, When the log sequence number is greater than or equal to the low watermark information and less than the high watermark information, conflict handling is performed on the operation corresponding to the log sequence number. When the log sequence number is greater than or equal to the high watermark information, the operation corresponding to the log sequence number is synchronized to the target database.

5. The method according to claim 4, characterized in that, The conflict handling for the operation corresponding to the log sequence number includes: In response to the operation corresponding to the log sequence number being an update operation, a target conflict handling step is executed; the target conflict handling step includes: splitting the update operation into a delete operation and then executing an insert operation.

6. The method according to claim 5, characterized in that, The conflict handling includes: If the operation corresponding to the log sequence number is an insert operation, and the incremental synchronization transaction time corresponding to the insert operation is before the existing data migration transaction time corresponding to the insert operation, an error message will be output.

7. A database migration device, characterized in that, The device includes: The first acquisition module is used to acquire the first target log sequence number of the migration transaction corresponding to any one of the multiple tables to be migrated, based on the change data capture table, at the start time of migrating the existing data of the source database to the target database. The first recording module is used to record the first target log sequence number as low-water mark information; the low-water mark information is used to characterize the starting position of the migration transaction. The second acquisition module is used to acquire the second target log sequence number of the migration transaction corresponding to the table to be migrated based on the change data capture table at the end time of migrating the existing data of the source database to the target database. The second recording module is used to record the second target log sequence number as high-water mark information; the high-water mark information is used to characterize the end position of the migration transaction. The synchronization module is used to synchronize incremental data from the source database to the target database based on the high water level information and the low water level information.

8. An electronic device, characterized in that, include: Memory, processor; The memory stores computer-executed instructions; The processor executes computer execution instructions stored in the memory, causing the processor to perform the method as described in any one of claims 1-6.

9. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer-executable instructions, which, when executed by a processor, are used to implement the method as described in any one of claims 1-6.

10. A computer program product, characterized in that, Includes a computer program that, when executed by a processor, implements the method described in any one of claims 1-6.

Images