Database migration post-backup data integrity and consistency comparison method

Abstract

The application discloses a kind of database migration backup data integrity and consistency comparison method, it is related to database management technical field, comprising the following steps: S1, metadata consistency verification;S2, statistical characteristics fast comparison;S3, content accurate comparison;S4, difference analysis and report.The database migration backup data integrity and consistency comparison method adopts layered verification strategy, through the layered verification process of four steps of metadata consistency verification, statistical characteristics fast comparison, content accurate comparison and difference analysis report, greatly improves the comparison efficiency, reduces full amount comparison time, and this layered progressive method makes the comparison process more efficient and accurate.At the same time, the application also supports the automatic comparison between heterogeneous databases, can support the automatic comparison between heterogeneous databases across platforms, reduces the technical threshold, so that the migration and backup verification between different databases become easier and more feasible.

Description

Technical Field

[0001] The present invention relates to the technical field of database management, and in particular to a method for comparing data integrity and consistency after database migration and backup. Background Art

[0002] Database migration and backup refers to the process of migrating a database from one environment (such as production) to another (such as a test environment, backup environment, or cloud environment) while ensuring the integrity and consistency of the data in the new environment remains the same as in the original environment. This process typically involves exporting, transferring, importing, and verifying data. After a database migration and backup, comparing data integrity and consistency is crucial. By verifying that the migrated data is fully consistent with the original data, data loss, alteration, or contamination can be effectively avoided, potential data issues can be promptly identified and remediated, and overall system reliability can be improved.

[0003] Traditional methods rely on manual sampling, where professionals randomly select a portion of data for manual comparison. This approach is inefficient and prone to missing issues. Furthermore, due to human factors, accuracy is difficult to guarantee. With the continuous advancement of computer technology, traditional manual inspections are gradually being replaced by tools. However, existing tools typically only perform simple record count comparisons and are unable to detect changes in data content. They typically use a full-database comparison, which requires a line-by-line comparison of all data in the source and target databases. This is time-consuming, significantly impacts business continuity, and is unsuitable for large-scale database scenarios.

[0004] Furthermore, during the comparison process, data relevance and consistency verification of business rules are often overlooked, and verification methods for migrations between heterogeneous databases are clearly insufficient. Currently, no such comparison tools exist in China. While similar open-source tools exist abroad, such as reladiff, which relies on command-based operations, they lack post-comparison repair capabilities. After database migration or backup, data inconsistencies and integrity violations can occur, seriously impacting the normal operation of business systems. Summary of the Invention

[0005] The purpose of the present invention is to provide a method for comparing data integrity and consistency after database migration and backup, so as to solve the problems raised in the above background technology.

[0006] To achieve the above object, the present invention provides the following technical solution: a method for comparing data integrity and consistency after database migration and backup, comprising the following steps:

[0007] S1. Metadata consistency verification:

[0008] Automatically compares metadata such as table structure, field types, and constraints between the source and target databases, and verifies the integrity of database objects such as indexes, triggers, and stored procedures.

[0009] S2. Rapid comparison of statistical features:

[0010] A hash-based sampling statistical method is used to quickly verify the number of records in each table. Based on this, statistical characteristics such as numerical distribution and number of unique values ​​of key fields are compared, and Bloom filters are used to quickly identify data sets that may have differences.

[0011] S3. Accurate content comparison:

[0012] Using multi-threading / multi-processing, it accurately compares identified discrepant data sets record by record. It also supports consistency verification of related data based on business rules to ensure data consistency at the business level. This step not only checks whether the direct data values ​​match, but also verifies, based on business logic, whether cross-table and cross-field data associations meet pre-set business rules.

[0013] S4. Difference Analysis and Reporting:

[0014] Automatically classify difference types, such as data loss, data change, data pollution, etc., generate a visual difference report to intuitively display the data differences, and provide a difference data repair script generation tool to help quickly fix data problems.

[0015] Furthermore, in step S1, SQL query statements are used to extract metadata such as table structure, field type, and constraints from the source and target libraries, and automated scripts are used to scan and compare the metadata of the source and target libraries, as follows:

[0016] 1) Table structure comparison

[0017] Comparison items: basic table attributes and table partition information;

[0018] The basic table attributes include: table name, storage engine (InnoDB / MyISAM, etc.), character set and collation (utf8mb4_general_ci, etc.), table comment, row format (COMPACT / DYNAMIC), and auto-increment start value;

[0019] The table partition information includes: partition key, partition type (RANGE / LIST / HASH), number of partitions, and subpartition definition;

[0020] 2) Field type comparison

[0021] Comparison dimensions: basic type, extended attributes, default values;

[0022] The basic types include: data type (INT / VARCHAR / DATE, etc.), length / precision (CHAR(10) vs. CHAR(20)), and sign attribute (UNSIGNED);

[0023] The extended attributes include: whether NULL is allowed, auto-increment attribute (AUTO_INCREMENT), virtual column (GENERATED ALWAYS AS), and field comments;

[0024] The default values ​​include: static values ​​(such as DEFAULT 0), function calls (DEFAULT CURRENT_TIMESTAMP), and expressions;

[0025] 3) Constraint comparison

[0026] Constraint type: primary key, foreign key, unique key, CHECK constraint, not null constraint;

[0027] The primary key includes: constraint name, included fields (order sensitive), clustered index attributes (CLUSTERED / NONCLUSTERED);

[0028] The foreign key includes: associated table and field, update / delete rules (CASCADE / SET NULL / RESTRICT), and constraint name;

[0029] The unique key includes: field combination (order-insensitive), constraint name, and filter condition (WHERE clause);

[0030] The CHECK constraint includes: a conditional expression (spaces and capitalization must be standardized) and a constraint name;

[0031] The non-null constraint includes: a NOT NULL statement at the field level.

[0032] Furthermore, in step S1, the process of database object integrity verification is as follows:

[0033] Verify indexes: Check whether the indexes in the source database are correctly created in the target database. Use the database's own index verification tool or a third-party tool to verify whether the index type, column order, and uniqueness are consistent with those in the source database.

[0034] Trigger verification: Compare the trigger definitions in the source and target databases to check whether the trigger events, trigger times, and trigger actions are the same. Simulate the trigger conditions in the target database to verify whether the trigger execution results are consistent with those in the source database.

[0035] Verify stored procedures: Compare the stored procedure definitions in the source and target databases to check whether the stored procedure input parameters, output parameters, and logical processing are consistent. Execute the stored procedure in the target database to verify whether the execution results are the same as those in the source database.

[0036] Furthermore, in step S2, the specific operation of sampling statistics is: by converting the key features of the database records (such as a key field value) into hash values, a hash value set is obtained, and then the hash value set is statistically analyzed, such as calculating the frequency distribution of the hash values, the number of unique values, etc., to quickly determine the similarity of the data set and the consistency of the number of records.

[0037] Furthermore, in step S2, the statistical feature comparison specifically includes:

[0038] Numerical distribution comparison: By drawing statistical charts such as histograms and box plots, the numerical distribution of key fields in different data sets can be compared to intuitively discover the similarities and differences between data sets;

[0039] Comparison of the number of unique values: Calculate and compare the number of unique values ​​of key fields in different datasets to evaluate the diversity and repeatability of the datasets. The greater the difference in the number of unique values, the greater the difference between the datasets.

[0040] Furthermore, in step S2, the process of using a Bloom filter to identify different datasets is as follows: a Bloom filter is constructed for each dataset, and the key field values ​​in the dataset are added to the Bloom filter. By comparing the Bloom filters of different datasets, datasets that may have differences are quickly identified. That is, if an element does not exist in the Bloom filter of one dataset but exists in the Bloom filter of another dataset, the two datasets are considered to be different.

[0041] The Bloom filter maps elements to a bit array through multiple hash functions. If an element exists in the set, all its corresponding hash values ​​in the bit array should be set to 1; when querying, it is only necessary to check whether all hash values ​​are 1.

[0042] Furthermore, step S3 includes the following sub-steps:

[0043] S31. Task division: Divide the identified difference data sets into multiple subtasks based on certain logic (such as data volume, table structure, business relevance, etc.). Each subtask includes the comparison of a portion of the difference data.

[0044] S32. Thread / Process Allocation: Each subtask is assigned an independent thread or process for comparison. In actual operation, the degree of parallelism (i.e., the number of threads / processes) can be dynamically adjusted based on system resources and the complexity of the comparison task. When resources are sufficient, the degree of parallelism can be increased to improve the comparison speed. When resources are limited, the degree of parallelism can be reduced to avoid system overload.

[0045] S33, Data Reading: Each thread / process reads the portion of data it is responsible for from the difference data set, specifically using paging reading, batch processing, etc.

[0046] S34, comparison logic: For each read record, perform an accurate comparison according to the preset comparison rules (such as field value equality, business rule consistency, etc.), and record the difference information during the comparison process, such as difference type, difference location, difference value, etc.

[0047] S35. Result aggregation: Each thread / process aggregates the comparison results to the main thread.

[0048] Furthermore, in the multi-threaded / multi-process environment, secure access to shared resources (such as database connections, memory data, etc.) is ensured by using synchronization mechanisms such as locks and semaphores to avoid data contention and deadlock problems. The operation process is as follows:

[0049] 1) Identify shared resources and critical sections: Identify resources shared by multiple threads or processes, such as memory data structures, files, and I / O devices, and identify the code segments that access these shared resources. These code segments are called critical sections. When the code within a critical section is executed, it is necessary to ensure that no other threads or processes access the same shared resources at the same time.

[0050] 2) Select synchronization primitives:

[0051] Mutex lock: used to protect critical sections, ensuring that only one thread or process can access shared resources at a time, suitable for simple mutual exclusion scenarios;

[0052] Semaphore: Provides a flexible counting mechanism to control the number of concurrent accesses to shared resources. It can be used not only for mutual exclusion but also for synchronization between threads.

[0053] Condition variables: used for coordination between threads, allowing threads to enter a waiting state when a specific condition is not met until other threads notify that the condition has been met. They are usually used in conjunction with mutex locks.

[0054] Read-write lock: allows multiple threads to read shared resources simultaneously, but only allows one thread to write to shared resources. It is suitable for scenarios with frequent read operations and few write operations.

[0055] 3) Implement synchronization mechanism: Before entering the critical section, the thread or process obtains the corresponding lock or semaphore. If the lock or semaphore is not available, it falls into a waiting state (a timeout is set for waiting for the lock or semaphore. If the waiting time is too long, the lock or semaphore is automatically released and the waiting process is interrupted). While holding the lock or semaphore, the shared resource can be accessed safely. After the access is completed, the thread or process releases the lock or semaphore so that other threads or processes can access the shared resource.

[0056] Furthermore, in step S4, the difference report specifically includes:

[0057] Difference overview: number of difference tables, total number of difference records, distribution of difference types (loss / change / contamination), and risk level statistics (high / medium / low);

[0058] Difference details: table name, field name, difference type, source data value vs. target data value, primary key / unique key identifier (to locate the difference record);

[0059] Business impact analysis: impacted core business indicators (such as order amount, user status), association rule violations (such as foreign key breakage);

[0060] Repair suggestions: automatically generated SQL repair scripts, manual review marks (sensitive operations requiring confirmation), and recommended execution order;

[0061] Visual charts: difference type pie chart, difference record trend chart (distributed by time), table-level difference heat map.

[0062] Furthermore, in step S4, the difference data repair script generation tool performs the following process:

[0063] Difference information analysis: Analyze the difference analysis report and extract detailed information about the difference data, including the difference type (such as data loss, data change, data pollution, etc.), difference location (such as table name, field name, record ID, etc.) and difference value (such as original value, new value, etc.);

[0064] Repair script generation: Automatically generates corresponding SQL repair scripts based on difference information. For example, if data is lost, an INSERT statement is generated to complete the data; if data is changed, an UPDATE statement is generated to update the data; if data is contaminated, a DELETE statement is generated to clear the dirty data.

[0065] Script optimization: Optimize the generated repair script to ensure execution efficiency and accuracy. For example, similar SQL statements can be merged to reduce the number of database accesses, and transaction control can be used to ensure the atomicity and consistency of script execution.

[0066] Script execution and verification: execute the generated repair script in the database, and at the same time, verify the execution result to ensure that the data problem is completely repaired.

[0067] The application provides a database migration backup data integrity and consistency comparison method, which has the following beneficial effects:

[0068] 1、The application adopts a layered verification strategy, and through four steps of layered verification processes of metadata consistency verification, statistical feature rapid comparison, content accurate comparison and difference analysis report, the comparison efficiency is greatly improved, the full comparison time is reduced, and the layered and progressive method makes the comparison process more efficient and accurate. Meanwhile, the application also supports automatic comparison between heterogeneous databases, can support automatic comparison between heterogeneous databases across platforms, reduces the technical threshold, and makes the migration and backup verification between different databases more easy and feasible.

[0069] 2、The application provides comprehensive data integrity verification, which not only verifies the content of the data, but also includes the consistency verification of the association relationship and business rules of the data, ensures the overall integrity of the data and the accuracy of the business rules, cooperates with automatic classification of difference types, and generates visual difference report and repair suggestion, so that the problem root can be quickly located, the user can quickly solve the problem of inconsistent data or integrity damage, and the difference data repair script automatic generation function is also provided, the manual intervention demand is reduced, the data repair process is more automated and efficient, and meanwhile, the visual difference report generated by the user can more intuitively understand the data comparison result and difference situation. BRIEF DESCRIPTION OF DRAWINGS

[0070] Figure 1 It is a step flowchart of the database migration backup data integrity and consistency comparison method of the application;

[0071] Figure 2 It is a step S3 schematic view of the database migration backup data integrity and consistency comparison method of the application;

[0072] Figure 3 It is a running logic schematic view of the database migration backup data integrity and consistency comparison method of the application. DETAILED DESCRIPTION

[0073] The embodiments of the application will be further described in detail below with reference to the drawings and examples. The following examples are used to illustrate the application, but cannot be used to limit the scope of the application.

[0074] As shown in the figure, a database migration backup data integrity and consistency comparison method comprises the following steps: Figure 1-Figure 3 ​

[0075] S1. Metadata consistency verification:

[0076] Automatically compares metadata such as table structure, field types, and constraints between the source and target databases, and verifies the integrity of database objects such as indexes, triggers, and stored procedures.

[0077] In this example, SQL query statements are used to extract metadata such as table structure, field types, and constraints from the source and target libraries. Automated scripts are then used to scan and compare the metadata of the source and target libraries, as follows:

[0078] 1) Table structure comparison

[0079] Comparison items: basic table attributes and table partition information;

[0080] Basic table attributes include: table name, storage engine (InnoDB / MyISAM, etc.), character set and collation (utf8mb4_general_ci, etc.), table comment, row format (COMPACT / DYNAMIC), and auto-increment start value;

[0081] Table partition information includes: partition key, partition type (RANGE / LIST / HASH), number of partitions, and subpartition definition;

[0082] 2) Field type comparison

[0083] Comparison dimensions: basic type, extended attributes, default values;

[0084] Basic types include: data type (INT / VARCHAR / DATE, etc.), length / precision (CHAR(10) vs CHAR(20)), sign attribute (UNSIGNED);

[0085] Extended attributes include: whether to allow NULL, auto-increment attribute (AUTO_INCREMENT), virtual column (GENERATEDALWAYS AS), and field comments;

[0086] Default values ​​include: static values ​​(such as DEFAULT 0), function calls (DEFAULT CURRENT_TIMESTAMP), and expressions;

[0087] 3) Constraint comparison

[0088] Constraint type: primary key, foreign key, unique key, CHECK constraint, not null constraint;

[0089] The primary key includes: constraint name, included fields (order sensitive), clustered index properties (CLUSTERED / NONCLUSTERED);

[0090] Foreign keys include: associated tables and fields, update / delete rules (CASCADE / SET NULL / RESTRICT), and constraint names;

[0091] A unique key includes: a field combination (order-insensitive), a constraint name, and a filter condition (WHERE clause).

[0092] A CHECK constraint includes: a conditional expression (spaces and uppercase / lowercase letters must be standardized) and a constraint name;

[0093] Non-null constraints include: NOT NULL declaration at the column level.

[0094] In this embodiment, the process of database object integrity verification is as follows:

[0095] Verify indexes: Check whether the indexes in the source database are correctly created in the target database. Use the database's own index verification tool or a third-party tool to verify whether the index type, column order, and uniqueness are consistent with those in the source database.

[0096] Trigger verification: Compare the trigger definitions in the source and target databases to check whether the trigger events, trigger times, and trigger actions are the same. Simulate the trigger conditions in the target database to verify whether the trigger execution results are consistent with those in the source database.

[0097] Verify stored procedures: Compare the stored procedure definitions in the source and target databases to check whether the stored procedure input parameters, output parameters, and logical processing are consistent. Execute the stored procedure in the target database to verify whether the execution results are the same as those in the source database.

[0098] In actual operation, an automated script is written using scripting languages ​​such as Python and Shell to implement the above-mentioned comparison and verification process. The script can automatically extract metadata, perform comparisons, generate reports, and perform corresponding verification operations based on the comparison results.

[0099] S2. Rapid comparison of statistical features:

[0100] A hash-based sampling statistical method is used to quickly verify the number of records in each table: by converting the key features of the database records (such as a key field value) into hash values, a hash value set is obtained, and then statistical analysis is performed on the hash value set, such as calculating the frequency distribution of hash values, the number of unique values, etc., to quickly determine the similarity of the data set and the consistency of the number of records.

[0101] On this basis, the statistical characteristics of key fields such as numerical distribution and number of unique values ​​are compared:

[0102] Numerical distribution comparison: By drawing statistical charts such as histograms and box plots, the numerical distribution of key fields in different data sets can be compared to intuitively discover the similarities and differences between data sets;

[0103] Comparison of the number of unique values: Calculate and compare the number of unique values ​​of key fields in different datasets to evaluate the diversity and repeatability of the datasets. The greater the difference in the number of unique values, the greater the difference between the datasets.

[0104] Use Bloom filters to quickly identify datasets that may differ: Build a Bloom filter for each dataset, add the key field values ​​in the dataset to the Bloom filter, and quickly identify datasets that may differ by comparing the Bloom filters of different datasets. For example, if an element does not exist in the Bloom filter of one dataset but exists in the Bloom filter of another dataset, the two datasets are considered to be different.

[0105] Bloom filters map elements to bit arrays through multiple hash functions. If an element exists in the set, all its corresponding hash values ​​in the bit array should be set to 1; when querying, you only need to check whether all hash values ​​are 1.

[0106] S3. Accurate content comparison:

[0107] Using multi-threading / multi-processing, it performs precise record-by-record comparison of identified discrepant data sets and supports consistency verification of associated data based on business rules to ensure data consistency at the business level. This step not only checks whether the direct data values ​​match, but also verifies whether the data association relationships across tables and fields meet the preset business rules based on business logic.

[0108] S31. Task division: Divide the identified difference data sets into multiple subtasks based on certain logic (such as data volume, table structure, business relevance, etc.). Each subtask includes the comparison of a portion of the difference data.

[0109] S32. Thread / Process Allocation: Each subtask is assigned an independent thread or process for comparison. In actual operation, the degree of parallelism (i.e., the number of threads / processes) can be dynamically adjusted based on system resources and the complexity of the comparison task. When resources are sufficient, the degree of parallelism can be increased to improve the comparison speed. When resources are limited, the degree of parallelism can be reduced to avoid system overload.

[0110] S33, Data Reading: Each thread / process reads the portion of data it is responsible for from the difference data set, specifically using paging reading, batch processing, etc.

[0111] S34, comparison logic: For each read record, perform an accurate comparison according to the preset comparison rules (such as field value equality, business rule consistency, etc.), and record the difference information during the comparison process, such as difference type, difference location, difference value, etc.

[0112] S35. Result aggregation: Each thread / process aggregates the comparison results to the main thread.

[0113] In a multi-threaded / multi-process environment, ensure secure access to shared resources (such as database connections, memory data, etc.), and use synchronization mechanisms such as locks and semaphores to avoid data contention and deadlock problems. The operation process is as follows:

[0114] 1) Identify shared resources and critical sections: Identify resources shared by multiple threads or processes, such as memory data structures, files, and I / O devices, and identify the code segments that access these shared resources. These code segments are called critical sections. When the code within a critical section is executed, it is necessary to ensure that no other threads or processes access the same shared resources at the same time.

[0115] 2) Select synchronization primitives:

[0116] Mutex: Used to protect critical sections, ensuring that only one thread or process can access shared resources at a time. Suitable for simple mutual exclusion scenarios.

[0117] Semaphore: Provides a flexible counting mechanism for controlling the number of concurrent accesses to shared resources. It can be used not only for mutual exclusion but also for synchronization between threads.

[0118] Condition Variable: Used for coordination between threads, allowing a thread to enter a waiting state when a specific condition is not met until other threads notify it that the condition has been met. It is usually used in conjunction with a mutex lock.

[0119] Reader-Writer Lock: Allows multiple threads to read shared resources simultaneously, but only allows one thread to write to shared resources. It is suitable for scenarios with frequent read operations and few write operations.

[0120] 3) Implement synchronization mechanism: Before entering the critical section, the thread or process obtains the corresponding lock or semaphore. If the lock or semaphore is not available, it falls into a waiting state (a timeout is set for waiting for the lock or semaphore. If the waiting time is too long, the lock or semaphore is automatically released and the waiting process is interrupted). While holding the lock or semaphore, the shared resource can be accessed safely. After the access is completed, the thread or process releases the lock or semaphore so that other threads or processes can access the shared resource.

[0121] S4. Difference Analysis and Reporting:

[0122] Through the difference analysis algorithm (rule-based classification), the comparison results are classified and organized, and the difference types such as data loss, data change, data pollution, etc. are automatically classified. A visual difference report is generated to intuitively display the data differences. A difference data repair script generation tool is also provided to help quickly fix data problems.

[0123] In this embodiment, the difference report specifically includes:

[0124] Difference overview: number of difference tables, total number of difference records, distribution of difference types (loss / change / contamination), and risk level statistics (high / medium / low);

[0125] Difference details: table name, field name, difference type, source data value vs. target data value, primary key / unique key identifier (to locate the difference record);

[0126] Business impact analysis: impacted core business indicators (such as order amount, user status), association rule violations (such as foreign key breakage);

[0127] Repair suggestions: automatically generated SQL repair scripts, manual review marks (sensitive operations requiring confirmation), and recommended execution order;

[0128] Visual charts: difference type pie chart, difference record trend chart (distributed by time), table-level difference heat map.

[0129] In this embodiment, the implementation of the difference data repair script generation tool is based on existing programming languages ​​and database technologies. For example, Python is used as the development language, and an ORM framework such as SQLAlchemy is used to operate the database; a template engine such as Jinja2 is used to generate SQL repair scripts; and a web framework such as Flask can be used to build a user interaction interface.

[0130] The tool for generating differential data repair scripts performs the following process:

[0131] Difference information analysis: Analyze the difference analysis report and extract detailed information about the difference data, including the difference type (such as data loss, data change, data pollution, etc.), difference location (such as table name, field name, record ID, etc.) and difference value (such as original value, new value, etc.);

[0132] Repair script generation: Automatically generates corresponding SQL repair scripts based on difference information. For example, if data is lost, an INSERT statement is generated to complete the data; if data is changed, an UPDATE statement is generated to update the data; if data is contaminated, a DELETE statement is generated to clear the dirty data.

[0133] Script optimization: Optimize the generated repair script to ensure execution efficiency and accuracy. For example, similar SQL statements can be merged to reduce the number of database accesses, and transaction control can be used to ensure the atomicity and consistency of script execution.

[0134] Script execution and verification: Execute the generated repair script in the database and verify the execution results to ensure that the data problem is completely repaired.

[0135] The embodiments of the present invention are presented for purposes of illustration and description and are not intended to be exhaustive or to limit the invention to the disclosed forms. Many modifications and variations will be apparent to those skilled in the art. The embodiments are chosen and described in order to better illustrate the principles of the invention and its practical application and to enable those skilled in the art to understand the invention and design various embodiments with various modifications as suited for specific applications.

Claims

1. A method for comparing data integrity and consistency after database migration and backup, characterized in that: The following steps are involved: S1. Metadata consistency verification: Automatically compare the metadata of the source and target databases and verify the integrity of database objects; S2. Rapid comparison of statistical features: A hash-based sampling statistical method is used to quickly verify the number of records in each table. Based on this, statistical features of key fields are compared, and Bloom filters are used to identify data sets that may have differences. S3, Precise Content Comparison: Using multi-threading / multi-processing, we perform precise record-by-record comparison of identified discrepant data sets, while also supporting consistency verification of linked data based on business rules. S4. Difference analysis and reporting: Automatically classify difference types, generate visual difference reports, and provide a tool to generate difference data repair scripts to help fix data problems; In step S2, the Bloom filter identifies different datasets using the following process: construct a Bloom filter for each dataset, add the key field values ​​in the dataset to the Bloom filter, and quickly identify datasets that may have differences by comparing the Bloom filters of different datasets. That is, if an element does not exist in the Bloom filter of one dataset but exists in the Bloom filter of another dataset, then the two datasets are considered to be different. The step S3 includes the following sub-steps: S31. Task division: Divide the identified difference data set into multiple subtasks, each subtask including the comparison of a portion of the difference data; S32, thread / process allocation: allocate an independent thread or process to each subtask for comparison; S33, data reading: Each thread / process reads the portion of data it is responsible for from the difference data set, specifically using paging reading and batch processing; S34, comparison logic: for each read record, accurately compare according to the preset comparison rules, and record the difference information during the comparison process; S35. Result aggregation: Each thread / process aggregates the comparison results to the main thread.

2. The method for comparing data integrity and consistency after database migration and backup according to claim 1, characterized in that: In step S1, SQL query statements are used to extract table structures, field types, and constraints from the source and target libraries, and automated scripts are used to scan and compare metadata of the source and target libraries, as follows: 1) Table structure comparison Comparison items: basic table attributes and table partition information; The basic attributes of the table include: table name, storage engine, character set and sorting rules, table comments, row format, and auto-increment start value; The table partition information includes: partition key, partition type, number of partitions, and sub-partition definition; 2) Field type comparison Comparison dimensions: basic type, extended attributes, default values; The basic types include: data type, length / precision, and symbol attributes; The extended attributes include: whether to allow NULL, auto-increment attribute, virtual column, and field annotation; The default values ​​include: static values, function calls, and expressions; 3) Constraint comparison Constraint type: primary key, foreign key, unique key, CHECK constraint, not null constraint; The primary key includes: constraint name, included fields, and clustered index attributes; The foreign key includes: associated table and field, update / delete rule, constraint name; The unique key includes: field combination, constraint name, and filter condition; The CHECK constraint includes: a conditional expression and a constraint name; The non-null constraint includes: a NOT NULL statement at the field level.

3. The method for comparing data integrity and consistency after database migration and backup according to claim 1, characterized in that: In step S1, the process of database object integrity verification is as follows: Verify indexes: Check whether the indexes in the source database are created correctly in the target database. Use the database's built-in index verification tool to verify whether the index type, column order, and uniqueness are consistent with those in the source database. Trigger verification: Compare the trigger definitions in the source and target databases to check whether the trigger events, trigger times, and trigger actions are the same. Simulate the trigger conditions in the target database to verify whether the trigger execution results are consistent with those in the source database. Verify stored procedures: Compare the stored procedure definitions in the source and target databases to check whether the stored procedure input parameters, output parameters, and logical processing are consistent. Execute the stored procedure in the target database to verify whether the execution results are the same as those in the source database.

4. The method for comparing data integrity and consistency after database migration and backup according to claim 1, characterized in that: In step S2, the specific operation of sampling statistics is as follows: converting the key features of the database records into hash values ​​to obtain a hash value set, and then performing statistical analysis on the hash value set to determine the similarity of the data set and the consistency of the number of records.

5. The method for comparing data integrity and consistency after database migration and backup according to claim 1, characterized in that: In step S2, the statistical feature comparison specifically includes: Numerical distribution comparison: Compare the numerical distribution of key fields in different data sets by drawing statistical charts; Comparison of the number of unique values: Calculate and compare the number of unique values ​​of key fields in different data sets. The greater the difference in the number of unique values, the greater the difference between the data sets.

6. The method for comparing data integrity and consistency after database migration and backup according to claim 1, characterized in that: In the multi-threaded / multi-process environment, synchronization mechanisms such as locks and semaphores are used to avoid data competition and deadlock. The operation process is as follows: 1) Identify shared resources and critical sections: Identify resources shared by multiple threads or processes and identify the code segments that access these shared resources, i.e., critical sections; 2) Select synchronization primitives: Mutex lock: used to protect critical sections, ensuring that only one thread or process can access shared resources at a time; Semaphore: Provides a flexible counting mechanism to control the number of concurrent accesses to shared resources; Condition variables: used for coordination between threads, allowing a thread to enter a waiting state when a specific condition is not met until other threads notify it that the condition has been met; Read-write lock: allows multiple threads to read shared resources at the same time, but only allows one thread to write to shared resources; 3) Implement synchronization mechanism: Before entering the critical section, the thread or process obtains the corresponding lock or semaphore. If the lock or semaphore is not available, it enters a waiting state. While holding the lock or semaphore, it can safely access the shared resource, and the thread or process releases the lock or semaphore after the access is completed.

7. The method for comparing data integrity and consistency after database migration and backup according to claim 1, characterized in that: In step S4, the difference report specifically includes: Difference overview: number of difference tables, total number of difference records, difference type distribution, and risk level statistics; Difference details: table name, field name, difference type, source data value vs. target data value, primary key / unique key identifier; Business impact analysis: impact on core business indicators and association rule violations; Repair suggestions: automatically generated SQL repair scripts, manual review marks, and recommended execution order; Visual charts: difference type pie chart, difference record trend chart, table-level difference heat map.

8. The method for comparing data integrity and consistency after database migration and backup according to claim 1, characterized in that: In step S4, the difference data repair script generation tool performs the following process: Difference information analysis: Analyze the difference analysis report and extract detailed information of the difference data, including difference type, difference location and difference value; Repair script generation: Automatically generate the corresponding SQL repair script based on the difference information; Script optimization: optimize the generated repair script; Script execution and verification: Execute the generated repair script in the database and verify the execution results to ensure that the data problem is completely repaired.

Images