Data checking method, device and equipment of MySQL dual-active library and medium

Abstract

The application belongs to the technical field of data processing, and discloses a data checking method, device and equipment of a MySQL dual-active database and a medium. The method comprises the following steps: extracting real-time data from a first dual-active database and a second dual-active database respectively; determining the containing content of the real-time data according to the extraction mode of the real-time data; determining the field to be extracted of the real-time data according to the containing content; extracting the general field of the real-time data according to the field to be extracted; associating the first data and the second data in the real-time data based on the general field; checking the first data and the second data to obtain a checking result; and notifying a developer when the checking result is abnormal. In the above manner, data checking can be realized without consuming database resources.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, and in particular to a method, apparatus, device, and medium for data verification of dual active databases in MySQL. Background Technology

[0002] For MySQL dual-active database data reconciliation, common industry solutions include end-of-day batch reconciliation and intraday micro-batch reconciliation. End-of-day batch reconciliation is generally achieved by executing batch SQL queries in the original database (or exported to the data warehouse), while intraday micro-batch reconciliation is generally achieved by scheduling the execution of micro-batch SQL queries in the source database. However, both end-of-day batch reconciliation and intraday micro-batch reconciliation require traversing a large amount of data during execution, which can put significant pressure on the database and may potentially affect other normal business operations running on the database. Summary of the Invention

[0003] The main objective of this invention is to provide a method, apparatus, device, and medium for data verification of dual active-active MySQL databases, aiming to solve the technical problem that data verification of dual active-active MySQL databases in the prior art can put a lot of pressure on the database and affect other normal operations running on the database.

[0004] To achieve the above objectives, this invention provides a method for data verification between two active MySQL databases, the method comprising the following steps:

[0005] The step of extracting real-time data from the first dual-active database and the second dual-active database respectively includes:

[0006] The real-time data is extracted from the first dual-active database and the second dual-active database respectively using a first method, wherein the first method includes Flink CDC sampling and Canal sampling.

[0007] The real-time data is extracted from the first dual-active database and the second dual-active database respectively by a second method, wherein the second method includes Lpdatasource4rdb scheduling for data extraction.

[0008] Optionally, determining the content of the real-time data based on the extraction method of the real-time data includes:

[0009] When the real-time data is extracted using the first method, the content of the real-time data is determined to include all tables.

[0010] When the real-time data is extracted using the second method, the content of the real-time data is determined to include only one table.

[0011] Optionally, determining the field to be extracted from the real-time data based on the included content includes:

[0012] If the included content includes all tables, then the field to be extracted from the real-time data is the first general field, wherein the first general field includes the database name, schema name, table name, primary key, version, and event time.

[0013] If the content contains only one table, then the field to be extracted from the real-time data is the second general field, which includes the primary key, version, and event time.

[0014] Optionally, the real-time data includes first-side data and second-side data; wherein,

[0015] The association of the first and second data in the real-time data based on the general field includes:

[0016] Projecting within a time range between the first-side data and the second-side data based on the event time of the general field, so as to segment the real-time data into bounded data;

[0017] If the included content includes all tables, then the database name, schema name, table name, primary key, and version of the first general field are used as association conditions.

[0018] If the content includes only one table, then the primary key and version of the second general field will be used as the association conditions;

[0019] Based on the association conditions, the first data and the second data in the bounded data are associated.

[0020] Optionally, the first-side data is obtained by extracting from the first dual-active database, and the second-side data is obtained by extracting from the second dual-active database; wherein,

[0021] The step of associating the first data and the second data in the bounded data based on the association condition includes:

[0022] Create a first dynamic table and a second dynamic table, wherein the first dynamic table is used to receive data from the first side and the second dynamic table is used to receive data from the second side.

[0023] Use FULL OUTER JOIN to perform a full join between the first dynamic table and the second dynamic table;

[0024] After performing a full association between the first dynamic table and the second dynamic table, the first data and the second data in the bounded data are associated based on the association conditions.

[0025] Optionally, the step of projecting the real-time data into bounded data within a time range based on the event time of the general field between the first-side data and the second-side data includes:

[0026] Determine the preset delay;

[0027] The time range corresponding to the event time is determined according to the preset delay;

[0028] Projection within the event range is performed between the first-side data and the second-side data to segment the real-time data into bounded data.

[0029] Furthermore, to achieve the above objectives, the present invention also proposes a data verification device for dual active-active MySQL databases, the data verification device for dual active-active MySQL databases comprising:

[0030] The extraction module is used to extract real-time data from the first dual-active database and the second dual-active database, respectively.

[0031] The determining module is used to determine the contents of the real-time data based on the extraction method of the real-time data.

[0032] The determining module is further configured to determine the field to be extracted from the real-time data based on the included content;

[0033] The extraction module is used to extract common fields from the real-time data based on the fields to be extracted;

[0034] The association module is used to associate the first data and the second data in the real-time data based on the general field;

[0035] The verification module is used to verify the first data and the second data to obtain the verification result;

[0036] The notification module is used to notify the developers when the verification result is an error.

[0037] Furthermore, to achieve the above objectives, the present invention also proposes a data verification device for a dual-active MySQL database. The data verification device for a dual-active MySQL database includes: a memory, a processor, and a data verification program for a dual-active MySQL database stored in the memory and capable of running on the processor. The data verification program for a dual-active MySQL database is configured to implement the steps of the data verification method for a dual-active MySQL database as described above.

[0038] Furthermore, to achieve the above objectives, the present invention also proposes a storage medium storing a MySQL dual-active database data verification program, wherein when the MySQL dual-active database data verification program is executed by a processor, it implements the steps of the MySQL dual-active database data verification method described above.

[0039] This invention proposes a MySQL dual-active database data verification method, apparatus, device, and medium. The method involves: extracting real-time data from a first and a second dual-active database; determining the content of the real-time data based on the extraction method; determining the fields to be extracted from the real-time data based on the content; extracting common fields from the real-time data based on the fields to be extracted; associating the first and second data in the real-time data based on the common fields; verifying the first and second data to obtain a verification result; and notifying developers when the verification result is abnormal. By extracting real-time data from the first and second dual-active databases, associating the data, and then verifying the associated data, data verification between the primary and standby databases can be completed without consuming database resources. Attached Figure Description

[0040] Figure 1 This is a schematic diagram of the structure of the data verification device for the MySQL dual-active database in the hardware operating environment involved in the embodiments of the present invention;

[0041] Figure 2 This is a flowchart illustrating the first embodiment of the MySQL dual-active database data verification method of the present invention;

[0042] Figure 3 This is a schematic diagram of the specific process of data verification in the first embodiment of the MySQL dual-active database data verification method of the present invention;

[0043] Figure 4 This is a flowchart illustrating the second embodiment of the MySQL dual-active database data verification method of the present invention;

[0044] Figure 5 This is a schematic diagram illustrating the monitoring of data loss in the dual-active database in the second embodiment of the MySQL dual-active database data verification method of the present invention;

[0045] Figure 6 This is a structural block diagram of the first embodiment of the MySQL dual-active database data verification device of the present invention.

[0046] The realization of the objective, functional features and advantages of the present invention will be further explained in conjunction with the embodiments and with reference to the accompanying drawings. Detailed Implementation

[0047] It should be understood that the specific embodiments described herein are for illustrative purposes only and are not intended to limit the scope of the invention.

[0048] Reference Figure 1 , Figure 1 This is a schematic diagram of the data verification device structure for a dual-active MySQL database in the hardware operating environment involved in the embodiments of the present invention.

[0049] like Figure 1 As shown, the data verification device for the dual-active MySQL database may include: a processor 1001, such as a central processing unit (CPU), a communication bus 1002, a user interface 1003, a network interface 1004, and a memory 1005. The communication bus 1002 is used to establish communication between these components. The user interface 1003 may include a display screen or an input unit such as a keyboard; optionally, the user interface 1003 may also include a standard wired interface or a wireless interface. The network interface 1004 may optionally include a standard wired interface or a wireless interface (such as a Wireless-Fidelity (Wi-Fi) interface). The memory 1005 may be a high-speed random access memory (RAM) or a stable non-volatile memory (NVM), such as a disk drive. The memory 1005 may also optionally be a storage device independent of the aforementioned processor 1001.

[0050] Those skilled in the art will understand that Figure 1 The structure shown does not constitute a limitation on the data verification device for dual-active MySQL databases and may include more or fewer components than shown, or combine certain components, or have different component arrangements.

[0051] like Figure 1 As shown, the memory 1005, which serves as a storage medium, may include an operating system, a network communication module, a user interface module, and a data verification program for a dual-active MySQL database.

[0052] exist Figure 1In the MySQL dual-active database data verification device shown, the network interface 1004 is mainly used for data communication with the network server; the user interface 1003 is mainly used for data interaction with the user; the processor 1001 and memory 1005 in the MySQL dual-active database data verification device of the present invention can be set in the MySQL dual-active database data verification device. The MySQL dual-active database data verification device calls the MySQL dual-active database data verification program stored in the memory 1005 through the processor 1001 and executes the MySQL dual-active database data verification method provided in the embodiment of the present invention.

[0053] Based on the above hardware structure, an embodiment of the data verification method for dual-active MySQL databases of the present invention is proposed.

[0054] Reference Figure 2 , Figure 2 This is a flowchart illustrating the first embodiment of a MySQL dual-active database data verification method according to the present invention.

[0055] In this embodiment, the data verification method for the dual-active MySQL databases includes the following steps:

[0056] Step S10: Extract real-time data from the first dual-active database and the second dual-active database respectively.

[0057] It should be noted that the executing entity in this embodiment can be a computing service device with data processing, network communication, and program execution functions, such as a mobile phone, tablet computer, or personal computer, or an electronic device capable of performing the above functions or a MySQL dual-active database data verification device. The following description uses the MySQL dual-active database data verification device as an example to illustrate this embodiment and the subsequent embodiments.

[0058] It's important to note that a MySQL dual-active database refers to setting up the same database architecture in two different data centers as a hot backup application, used for bidirectional database data synchronization. When one data center fails, the other takes over, ensuring high application availability and data reliability. The MySQL dual-active database architecture consists of two databases: a master and a slave. The master handles all write operations, while the slave handles all read operations. When the master fails, the slave can take over the master's read and write operations until the master recovers. Implementing a MySQL dual-active database requires using MySQL's built-in replication technology—master-slave replication. Master-slave replication means that when data changes occur on the master, the changes are automatically synchronized to the slave, achieving data backup and read / write separation.

[0059] It should be noted that the first and second dual-active databases are the primary and standby databases in the architecture of a MySQL dual-active database; real-time data includes data extracted from the first dual-active database and data extracted from the second dual-active database.

[0060] In one embodiment, the step of extracting real-time data from the first dual-active database and the second dual-active database respectively includes:

[0061] The real-time data is extracted from the first dual-active database and the second dual-active database respectively using a first method, wherein the first method includes Flink CDC sampling and Canal sampling.

[0062] The real-time data is extracted from the first dual-active database and the second dual-active database respectively by a second method, wherein the second method includes Lpdatasource4rdb scheduling for data extraction.

[0063] It's important to note that Flink CDC sampling, Canal sampling, and Lpdatasource4rdb scheduling sampling can all be used to obtain real-time data. Flink CDC sampling and Canal sampling are essentially binlog time-series transaction logs, while Lpdatasource4rdb scheduling sampling is essentially cursor-segmented snapshot data. The main difference between the first and second methods lies in the data source from which the real-time data is obtained.

[0064] Step S20: Determine the contents of the real-time data according to the extraction method of the real-time data.

[0065] It should be noted that "included content" can be understood as what data is included in the real-time data.

[0066] In one embodiment, determining the content of the real-time data based on the extraction method of the real-time data includes:

[0067] When the real-time data is extracted using the first method, the content of the real-time data is determined to include all tables.

[0068] When the real-time data is extracted using the second method, the content of the real-time data is determined to include only one table.

[0069] It's important to note that Flink CDC and Canal data extraction are essentially based on binlog time-series transaction logs (i.e., Flink CDC and Canal data extraction are essentially extracting binlog data). Since the binlog represents the entire database, Flink CDC and Canal data extraction will extract data from all tables in the database. Therefore, the real-time data obtained using the first method includes data from all tables. Lpdatasource4rdb scheduling data extraction, on the other hand, extracts data by querying the database, focusing on a single table. Essentially, it segments and snapshots data according to a cursor. Therefore, the real-time data obtained using the second method only includes data from one table.

[0070] Step S30: Determine the fields to be extracted from the real-time data based on the included content.

[0071] In one embodiment, determining the field to be extracted from the real-time data based on the included content includes:

[0072] If the included content includes all tables, then the field to be extracted from the real-time data is the first general field, wherein the first general field includes the database name, schema name, table name, primary key, version, and event time.

[0073] If the content contains only one table, then the field to be extracted from the real-time data is the second general field, which includes the primary key, version, and event time.

[0074] It should be noted that the meanings of the various general fields are as follows:

[0075] Database Name (DB_NAME) field: Used to indicate which database the data belongs to.

[0076] The schema name (SCHEMA_NAME) field indicates which schema the data belongs to.

[0077] The TABLE_NAME field indicates which table the data belongs to.

[0078] Primary key (PK) field: A uniqueness constraint field. When used alone, it can guarantee the uniqueness of the data in the table. When used together with the version field, it can guarantee the uniqueness of the data in the transaction flow.

[0079] The VERSION field: This is typically a unidirectional incrementing sequence or the current timestamp. It's essential to ensure that the VERSION field value changes and remains identical in both active tables A and B with each data update. Using the primary key (PK) and VERSION field together guarantees the uniqueness of the data within the transaction flow.

[0080] The event time (LAST_MODIFIED_TIME) field indicates the time when the data was added / modified / deleted, and is the basis for time interval projection in real-time data verification.

[0081] It is understandable that "containing all tables" means that the content includes data from all tables; "containing only one table" means that the content includes data from only one table.

[0082] Step S40: Extract the common fields of the real-time data based on the fields to be extracted.

[0083] In the actual implementation, the extracted common fields and the original data are sent down to the downstream Kafka Topic for the next step of real-time data verification.

[0084] Step S50: Associate the first data and the second data in the real-time data based on the general field.

[0085] It should be noted that the first data refers to the real-time data extracted from the first dual-active database, and the second data refers to the real-time data extracted from the second dual-active database. The first data and the second data are interconnected based on common fields. Since the primary and standby databases in the MySQL dual-active database should be consistent, the first data and the second data interconnected through common fields should be consistent. Specifically, the first data can be a table extracted from the first dual-active database, and the second data can be a table extracted from the second dual-active database.

[0086] In the specific implementation, common fields include database name (DB_NAME), schema name (SCHEMA_NAME), table name (TABLE_NAME), primary key (PK), version (VERSION), and event time (LAST_MODIFIED_TIME).

[0087] Step S60: Verify the first data and the second data to obtain the verification result.

[0088] It should be noted that the verification results include both normal and abnormal results. When the verification result is normal, there is no need to notify the developers.

[0089] In practice, the first data and the second data can be compared one by one. When the first data and the second data are different, the verification result is determined to be an anomaly; when the first data and the second data are the same, the verification result is determined to be normal.

[0090] Step S70: When the verification result is an anomaly, notify the developers.

[0091] It should be noted that when the verification result is abnormal, developers can be notified in real time through instant messaging tools such as "Hello". Specifically, when a record with missing data is found in the first or second data after verification, it can be determined that the dual active database has lost data, which means that a verification abnormality has occurred.

[0092] In practical implementation, common dual-active implementations include asynchronous writing of transaction logs and asynchronous dual writing at the business layer. All data additions, deletions, and modifications are reflected in the binlog of the dual-active database, and the binlog data has a 1:1 correspondence. Using common fields, Flink SQL can be used to realize real-time verification of dual-active database data and monitor the data loss of either database in the dual-active database.

[0093] In specific implementations, such as Figure 3 As shown, the data verification method for MySQL dual-active databases mainly includes real-time data extraction, extraction of common fields, real-time data verification, and push of verification results.

[0094] This embodiment extracts real-time data from a first dual-active database and a second dual-active database respectively; determines the content of the real-time data according to the extraction method; determines the fields to be extracted from the real-time data based on the content; extracts the common fields of the real-time data based on the fields to be extracted; associates the first data and the second data in the real-time data based on the common fields; verifies the first data and the second data to obtain a verification result; and notifies the developers when the verification result is an anomaly. Through the above method, real-time data is extracted from the first and second dual-active databases respectively, then associated, and finally verified. This achieves data verification between the primary and standby databases without consuming database resources, thereby avoiding impacting other normally operating services on the database.

[0095] refer to Figure 4 , Figure 4 This is a flowchart illustrating the second embodiment of a MySQL dual-active database data verification method according to the present invention.

[0096] Based on the first embodiment described above, the real-time data includes first-side data and second-side data. The data verification method for the MySQL dual-active database in this embodiment, which associates the first and second data in the real-time data based on the common field, includes:

[0097] Step S501: Project the real-time data into bounded data by projecting it within a time range based on the event time of the general field between the first side data and the second side data.

[0098] It should be noted that the first-side data refers to the real-time data extracted from the first dual-active database, and the second-side data refers to the real-time data obtained from the second dual-active database. The real-time data is unbounded data. After determining the time range through the event time, the real-time data can be divided into bounded data. That is, the time range for cutting is determined according to the event time (i.e., the start time and end time of the cutting), and then the real-time data is cut according to the time range.

[0099] In specific implementations, such as Figure 5 As shown, the event time of A is T1. Then the start time in the time range is T1-ΔTL, and the end time is T1+ΔTL, where ΔTL is the allowable time for data to arrive late.

[0100] In the specific implementation, the interval join syntax is used to project the time range (e.g., 5 seconds) between PRIMARY_SOURCE and BACKPU_SOURCE, thus cutting the unbounded stream into a bounded stream.

[0101] Step S502: If the included content includes all tables, then the database name, schema name, table name, primary key, and version of the first general field are used as association conditions.

[0102] It should be noted that the first general field includes the database name, schema name, table name, primary key, version, and event time.

[0103] Step S503: If the content includes only one table, then the primary key and version of the second general field are used as the association conditions.

[0104] It should be noted that the second general field includes the primary key, version, and event time.

[0105] It should be noted that the meanings of each common field are as follows:

[0106] Database Name (DB_NAME) field: Used to indicate which database the data belongs to.

[0107] The schema name (SCHEMA_NAME) field indicates which schema the data belongs to.

[0108] The TABLE_NAME field indicates which table the data belongs to.

[0109] Primary key (PK) field: A uniqueness constraint field. When used alone, it can guarantee the uniqueness of the data in the table. When used together with the version field, it can guarantee the uniqueness of the data in the transaction flow.

[0110] The VERSION field: This is typically a unidirectional incrementing sequence or the current timestamp. It's essential to ensure that the VERSION field value changes and remains identical in both active tables A and B with each data update. Using the primary key (PK) and VERSION field together guarantees the uniqueness of the data within the transaction flow.

[0111] The event time (LAST_MODIFIED_TIME) field indicates the time when the data was added / modified / deleted, and is the basis for time interval projection in real-time data verification.

[0112] It is understandable that "containing all tables" means that the content includes data from all tables; "containing only one table" means that the content includes data from only one table.

[0113] Step S504: Based on the association conditions, associate the first data and the second data in the bounded data.

[0114] It should be noted that the association conditions are related to the method of extracting real-time data. When the real-time data is extracted using the first method, the database name (DB_NAME), schema name (SCHEMA_NAME), table name (TABLE_NAME), primary key (PK), and version (VERSION) are used as conditions. When the real-time data is extracted using the second method, the primary key (PK) and version (VERSION) are used as association conditions.

[0115] Understandably, the real-time data extracted using the first method contains multiple tables of different types, so more common fields are needed to accurately link the first and second data. The real-time data extracted using the second method contains only one table, so only the primary key (PK), version (VERSION), and event time (LAST_MODIFIED_TIME) are needed to link the first and second data.

[0116] In the actual implementation, WHERE is used to filter out records where PRIMARY_SOURCE.PK or BACKPU_SOURCE.PK is NULL.

[0117] In one embodiment, the first-side data is obtained by extracting from the first dual-active database, and the second-side data is obtained by extracting from the second dual-active database; wherein,

[0118] The step of associating the first data and the second data in the bounded data based on the association condition includes:

[0119] Create a first dynamic table and a second dynamic table, wherein the first dynamic table is used to receive data from the first side and the second dynamic table is used to receive data from the second side.

[0120] Use FULL OUTER JOIN to perform a full join between the first dynamic table and the second dynamic table;

[0121] After performing a full association between the first dynamic table and the second dynamic table, the first data and the second data in the bounded data are associated based on the association conditions.

[0122] It should be noted that the first dynamic table is used to receive real-time data (i.e., first-side data) extracted from the first dual-active database, and the second dynamic table is used to receive real-time data (i.e., second-side data) extracted from the second dual-active database.

[0123] In practice, you can use CREATE TABLE to create a first dynamic table PRIMARY_SOURCE and a second dynamic table BACKPU_SOURCE; you can also use CREATE TABLE to create a dynamic table ERSULT_SINK to receive the verification results.

[0124] In the specific implementation, FULL OUTER JOIN is used to perform a full join on the first dynamic table PRIMARY_SOURCE and the second dynamic table BACKPU_SOURCE. The join condition is that the database name (DB_NAME), schema name (SCHEMA_NAME), table name (TABLE_NAME), primary key (PK), and version (VERSION) fields of the first dynamic table PRIMARY_SOURCE and the second dynamic table BACKPU_SOURCE are equal.

[0125] In one embodiment, the step of projecting the real-time data into bounded data within a time range based on the event time of the general field between the first-side data and the second-side data includes:

[0126] Determine the preset delay;

[0127] The time range corresponding to the event time is determined according to the preset delay;

[0128] Projection within the event range is performed between the first-side data and the second-side data to segment the real-time data into bounded data.

[0129] It should be noted that real-time data is unbounded data. After determining the time range based on the event time, the real-time data can be segmented into bounded data. That is, the time range for segmentation (i.e., the start and end times of the segmentation) is determined based on the event time, and then the real-time data is segmented according to the time range. The preset delay can be set in advance according to the data situation. The preset delay is used to wait for late data. Specifically, if the event time of A is T1, then the start time in the time range is T1-ΔTL-a, and the end time is T1+ΔTL+a, where ΔTL is the allowed time for data to arrive late, and a represents the preset delay.

[0130] In practice, the implementation principle of the MySQL dual-active database data verification method is as follows:

[0131] (1) In the table creation statement, WATERMARK FOR `event_time` AS `event_time` - INTERVAL '1' MINUTE is used to define the watermark, which is a unique semantic of Flink SQL. The purpose is to wait for late data. The maximum tolerance for data to be late is 1 minute, but at the same time, a 1-minute delay will be introduced.

[0132] (2) The join statement PRIMARY_SOURCE FULL OUTER JOIN BACKUP_SOURCE uses an outer join, and its semantics are the same as the database SQL semantics. If either side of the dual active database is lost, the join can be established; if both sides are lost, the join cannot be established.

[0133] (3) The join condition PRIMARY_SOURCE.`TABLE_NAME`=BACKUP_SOURCE.`TABLE_NAME`ANDPRIMARY_SOURCE.`PK`=BACKUP_SOURCE.`PK`ANDPRIMARY_SOURCE.`VERSION`=BACKUP_SOURCE.`VERSION` is a join condition for two active databases, with semantics identical to the database SQL. Here, "table name + primary key value + data version value" is used to locate the data. Regarding the TABLE_NAME field, this field is used to distinguish different tables when data from multiple tables are mixed in one data source (for example, Flink CDC / Canal generally extracts the binlog of the entire database into a Kafka topic). The TABLE_NAME field can be omitted when there is one table per topic. Regarding the VERSION field, this field represents the data version. The most common one is the familiar LAST_MODIFIED_TIME. In the Flink CDC / Canal asynchronous dual-write solution, it is possible to ensure that the LAST_MODIFIED_TIME of the two active databases is the same, so LAST_MODIFIED_TIME can be used as the VERSION field. In the business layer dual-write solution, it is more difficult to ensure that the LAST_MODIFIED_TIME of the two active databases is the same (generally, the database is allowed to update automatically), so other fields need to be selected as the VERSION field.

[0134] (4) The association condition BACKUP_SOURCE.`event_time`BETWEEN PRIMARY_SOURCE.`event_time`-INTERVAL'5'SECOND and PRIMARY_SOURCE.`event_time`+INTERVAL'5'SECOND is the Flink SQL interval join syntax. As shown in the diagram, interval join can project a time range from ΔTL to ΔTH, which is used to cut the unbounded flow into a bounded flow to complete the join semantics. Here, the event time of the two active databases can be tolerated to fluctuate by ±5 seconds, while also introducing a 5-second delay.

[0135] (5) Filtering condition WHERE PRIMARY_SOURCE.`PK`IS NULL OR BACKUP_SOURCE.`PK`ISNULL directly filters out data loss cases in dual-active database A or dual-active database B (data loss on both sides cannot be verified).

[0136] This embodiment segments the real-time data into bounded data by projecting the event time of the general field between the first-side data and the second-side data within a time range. If the included content contains all tables, the database name, schema name, table name, primary key, and version of the first general field are used as association conditions. If the included content contains only one table, the primary key and version of the second general field are used as association conditions. Based on these association conditions, the first and second data in the bounded data are associated. By determining the association conditions for associating data according to the included content of the real-time data, rapid data association can be achieved, thereby effectively improving the data verification speed.

[0137] Furthermore, this embodiment of the invention also proposes a storage medium storing a MySQL dual-active database data verification program. When the MySQL dual-active database data verification program is executed by a processor, it implements the steps of the MySQL dual-active database data verification method described above.

[0138] Reference Figure 6 , Figure 6 This is a structural block diagram of the first embodiment of the MySQL dual-active database data verification device of the present invention.

[0139] like Figure 6 As shown, the MySQL dual-active database data verification device proposed in this embodiment of the invention includes:

[0140] The extraction module 10 is used to extract real-time data from the first dual-active database and the second dual-active database, respectively.

[0141] The determining module 20 is used to determine the contents of the real-time data according to the extraction method of the real-time data.

[0142] The determining module 20 is further configured to determine the fields to be extracted from the real-time data based on the included content.

[0143] Extraction module 30 is used to extract the common fields of the real-time data based on the fields to be extracted.

[0144] The association module 40 is used to associate the first data and the second data in the real-time data based on the general field.

[0145] The verification module 50 is used to verify the first data and the second data to obtain the verification result.

[0146] The notification module 60 is used to notify the developers when the verification result is an error.

[0147] It should be understood that the above are merely illustrative examples and do not constitute any limitation on the technical solutions of the present invention. In specific applications, those skilled in the art can make settings as needed, and the present invention does not impose any restrictions on this.

[0148] This embodiment extracts real-time data from a first dual-active database and a second dual-active database respectively; determines the content of the real-time data according to the extraction method; determines the fields to be extracted from the real-time data based on the content; extracts the common fields of the real-time data based on the fields to be extracted; associates the first data and the second data in the real-time data based on the common fields; verifies the first data and the second data to obtain a verification result; and notifies the developers when the verification result is an anomaly. Through the above method, real-time data is extracted from the first and second dual-active databases respectively, then associated, and finally verified, thereby achieving data verification between the primary and standby databases without consuming database resources.

[0149] In one embodiment, the extraction module 10 is further configured to:

[0150] The real-time data is extracted from the first dual-active database and the second dual-active database respectively using a first method, wherein the first method includes Flink CDC sampling and Canal sampling.

[0151] The real-time data is extracted from the first dual-active database and the second dual-active database respectively by a second method, wherein the second method includes Lpdatasource4rdb scheduling for data extraction.

[0152] In one embodiment, the determining module 20 is further configured to:

[0153] When the real-time data is extracted using the first method, the content of the real-time data is determined to include all tables.

[0154] When the real-time data is extracted using the second method, the content of the real-time data is determined to include only one table.

[0155] In one embodiment, the determining module 20 is further configured to:

[0156] If the included content includes all tables, then the field to be extracted from the real-time data is the first general field, wherein the first general field includes the database name, schema name, table name, primary key, version, and event time.

[0157] If the content contains only one table, then the field to be extracted from the real-time data is the second general field, which includes the primary key, version, and event time.

[0158] In one embodiment, the real-time data includes first-side data and second-side data; wherein,

[0159] The association module 40 is also used for:

[0160] Projecting within a time range between the first-side data and the second-side data based on the event time of the general field, so as to segment the real-time data into bounded data;

[0161] If the included content includes all tables, then the database name, schema name, table name, primary key, and version of the first general field are used as association conditions.

[0162] If the content includes only one table, then the primary key and version of the second general field will be used as the association conditions;

[0163] Based on the association conditions, the first data and the second data in the bounded data are associated.

[0164] In one embodiment, the first-side data is obtained by extracting from the first dual-active database, and the second-side data is obtained by extracting from the second dual-active database; wherein,

[0165] The association module 40 is also used for:

[0166] Create a first dynamic table and a second dynamic table, wherein the first dynamic table is used to receive data from the first side and the second dynamic table is used to receive data from the second side.

[0167] Use FULL OUTER JOIN to perform a full join between the first dynamic table and the second dynamic table;

[0168] After performing a full association between the first dynamic table and the second dynamic table, the first data and the second data in the bounded data are associated based on the association conditions.

[0169] In one embodiment, the association module 40 is further configured to:

[0170] Determine the preset delay;

[0171] The time range corresponding to the event time is determined according to the preset delay;

[0172] Projection within the event range is performed between the first-side data and the second-side data to segment the real-time data into bounded data.

[0173] It should be noted that the workflow described above is merely illustrative and does not limit the scope of protection of this invention. In practical applications, those skilled in the art can select some or all of the workflow to achieve the purpose of this embodiment according to actual needs, and no restrictions are imposed here.

[0174] In addition, for technical details not described in detail in this embodiment, please refer to the data verification method for dual-active MySQL databases provided in any embodiment of the present invention, which will not be repeated here.

[0175] Furthermore, it should be noted that, in this document, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or system that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or system. Unless otherwise specified, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or system that includes that element.

[0176] The sequence numbers of the above embodiments of the present invention are for descriptive purposes only and do not represent the superiority or inferiority of the embodiments.

[0177] Through the above description of the embodiments, those skilled in the art can clearly understand that the methods of the above embodiments can be implemented by means of software plus necessary general-purpose hardware platforms. Of course, they can also be implemented by hardware, but in many cases the former is a better implementation method. Based on this understanding, the technical solution of the present invention, or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product is stored in a storage medium (such as read-only memory (ROM) / RAM, magnetic disk, optical disk) and includes several instructions to cause a terminal device (which may be a mobile phone, computer, server, or network device, etc.) to execute the methods described in the various embodiments of the present invention.

[0178] The above are merely preferred embodiments of the present invention and do not limit the scope of the patent. Any equivalent structural or procedural transformations made based on the description and drawings of the present invention, or direct or indirect applications in other related technical fields, are similarly included within the scope of patent protection of the present invention.

Claims

1. A method for data verification between two active MySQL databases, characterized in that, The data verification method for the dual-active MySQL databases includes: Real-time data is extracted from the first dual-active database and the second dual-active database respectively. The real-time data includes first-side data and second-side data. The first-side data is obtained by extracting from the first dual-active database, and the second-side data is obtained by extracting from the second dual-active database. The content of the real-time data is determined based on the extraction method of the real-time data. The fields to be extracted from the real-time data are determined based on the included content; Extract the common fields of the real-time data based on the fields to be extracted; The real-time data is associated with first-side data and second-side data based on the general field, wherein: a time range projection is performed between the first-side data and second-side data according to the event time of the general field to segment the real-time data into bounded data. The projection process includes: determining a preset delay; determining the time range corresponding to the event time according to the preset delay; performing the projection within the time range between the first-side data and second-side data to segment the real-time data into bounded data; if the included content includes all tables, then the database name, schema name, table name, primary key, and version are used as association conditions; if the included content includes only one table, then the primary key and version are used as association conditions; based on the association conditions, the first-side data and second-side data in the bounded data are associated. Verify the data from the first side and the data from the second side to obtain the verification result; If the verification result is abnormal, notify the developers.

2. The method as described in claim 1, characterized in that, The step of extracting real-time data from the first dual-live database and the second dual-live database respectively includes: The real-time data is extracted from the first dual-active database and the second dual-active database respectively using a first method, wherein the first method includes Flink CDC sampling and Canal sampling. The real-time data is extracted from the first dual-active database and the second dual-active database respectively by a second method, wherein the second method includes Lpdatasource4rdb scheduling for data extraction.

3. The method as described in claim 2, characterized in that, The step of determining the content of the real-time data according to the extraction method of the real-time data includes: When the real-time data is extracted using the first method, the content of the real-time data is determined to include all tables. When the real-time data is extracted using the second method, the content of the real-time data is determined to include only one table.

4. The method as described in claim 3, characterized in that, The step of determining the fields to be extracted from the real-time data based on the included content includes: If the included content includes all tables, then the field to be extracted from the real-time data is the first general field, wherein the first general field includes the database name, schema name, table name, primary key, version, and event time. If the content contains only one table, then the field to be extracted from the real-time data is the second general field, which includes the primary key, version, and event time.

5. The method as described in claim 1, characterized in that, The step of associating the first-side data and the second-side data in the bounded data based on the association conditions includes: Create a first dynamic table and a second dynamic table, wherein the first dynamic table is used to receive data from the first side and the second dynamic table is used to receive data from the second side. Use FULL OUTER JOIN to perform a full join between the first dynamic table and the second dynamic table; After performing a full association between the first dynamic table and the second dynamic table, the first-side data and the second-side data in the bounded data are associated based on the association conditions.

6. A data verification device for dual active-active MySQL databases, characterized in that, The MySQL dual-active database data verification device includes: An extraction module is used to extract real-time data from a first dual-active database and a second dual-active database, respectively. The real-time data includes first-side data and second-side data. The first-side data is obtained by extracting from the first dual-active database, and the second-side data is obtained by extracting from the second dual-active database. The determining module is used to determine the contents of the real-time data based on the extraction method of the real-time data. The determining module is further configured to determine the field to be extracted from the real-time data based on the included content; The extraction module is used to extract common fields from the real-time data based on the fields to be extracted; The association module is used to associate first-side data and second-side data in the real-time data based on the general field, wherein: a time range projection is performed between the first-side data and the second-side data according to the event time of the general field to segment the real-time data into bounded data. The projection process includes: determining a preset delay; determining the time range corresponding to the event time according to the preset delay; performing the projection within the time range between the first-side data and the second-side data to segment the real-time data into bounded data; if the included content includes all tables, then the database name, schema name, table name, primary key, and version are used as association conditions; if the included content includes only one table, then the primary key and version are used as association conditions; based on the association conditions, the first-side data and second-side data in the bounded data are associated. The verification module is used to verify the data on the first side and the data on the second side to obtain the verification result; The notification module is used to notify the developers when the verification result is an error.

7. A data verification device for dual active-active MySQL databases, characterized in that, The device includes: a memory, a processor, and a MySQL dual-active database data verification program stored in the memory and executable on the processor, the MySQL dual-active database data verification program being configured to implement the steps of the MySQL dual-active database data verification method as described in any one of claims 1 to 5.

8. A storage medium, characterized in that, The storage medium stores the XX program, and when the MySQL dual-active database data verification program is executed by the processor, it implements the steps of the MySQL dual-active database data verification method as described in any one of claims 1 to 5.

Images