Data synchronization methods, systems, electronic devices, and storage media between databases

Abstract

This application relates to the field of data synchronization technology between databases, and provides a data synchronization method, system, electronic device, and storage medium for databases. The data synchronization method includes: acquiring data change records from an upstream database; pre-storing the data change records; reading and pre-processing the data change records to obtain first change record objects; then, grouping first change record objects with the same table name and row ID into the same region; for each region, creating an integration window; receiving the first change record objects from the corresponding region through the integration window; integrating the first change record objects in the integration window to obtain second change record objects; pre-storing the second change record objects; and finally reading the second change record objects and synchronizing their data to the downstream database. This application can improve the synchronization efficiency of changed data between databases.

Description

Technical Field

[0001] This application belongs to the field of data synchronization technology between databases, and particularly relates to data synchronization methods, systems, electronic devices and storage media between databases. Background Technology

[0002] In the era of big data, enterprises are accelerating their digital transformation and deepening their work. During this process, companies often use databases such as SQL Server and MySQL to store business data. For example, an upstream subsidiary might first store business data in an SQL Server database and then synchronize the data from the SQL Server database to the head office's MySQL database.

[0003] However, as the volume of business data continues to increase and the number of users continues to grow, real-time synchronization of full data between databases can easily consume a lot of resources, while incremental data synchronization technology still suffers from low synchronization efficiency. Summary of the Invention

[0004] This application provides a data synchronization method, system, electronic device, and storage medium between databases to solve the problem of low synchronization efficiency of incremental data between upstream and downstream databases.

[0005] This application is achieved through the following technical solution: In a first aspect, embodiments of this application provide a data synchronization method between databases, comprising: creating a first topic and a second topic in Kafka; obtaining data change records from an upstream database and storing the data change records in the first topic; reading target data change records from the first topic and preprocessing the target data change records to obtain a first change record object, the first change record object including a table name, row ID, table fields, and change operation type, the target data change record being one or more of the data change records; in a Flink execution environment, grouping first change record objects with the same table name and the same row ID into the same region; creating an integration window for each region; receiving first change record objects from the corresponding region through the integration window; when the number of first change record objects in the integration window reaches the capacity of the integration window, integrating the first change record objects in the integration window based on the table fields and the change operation type to obtain a second change record object, storing the second change record object in the second topic; reading the second change record object from the second topic and synchronizing the data in the second change record object to the downstream database.

[0006] In conjunction with the first aspect, in some embodiments, the first change record object further includes a data field, wherein the data field is the data corresponding to the table field; the step of integrating the first change record objects in the integration window based on the table field and the change operation type to obtain a second change record object includes: selecting two first change record objects in the integration window, integrating the change operation types of the two first change record objects by analyzing the change operation types of the two first change record objects, and integrating the data corresponding to the same table fields in the two first change record objects based on the table fields of the two first change record objects to obtain a third change record object; selecting the third change record object and the integration window Another first change record object is analyzed by the change operation types of the third change record object and the first change record object. The change operation types in the third change record object and the first change record object are integrated. Based on the table fields of the third change record object and the first change record object, the data corresponding to the same table fields in the third change record object and the first change record object are integrated to obtain a new third change record object. The process then jumps to the step of selecting the third change record object and another first change record object in the integration window, and repeats this process until the integration window only includes the latest third change record object, which is the second change record object.

[0007] In conjunction with the first aspect, in some embodiments, the first change record object further includes a time field; the step of selecting two first change record objects in the integration window and integrating the change operation types of the two first change record objects by analyzing their change operation types includes: selecting the two first change record objects received earlier according to the receiving order of the integration window; obtaining the change operation type and time field of the two first change record objects, and converting the time field into a millisecond-level timestamp; if the change operation type of the first change record object with the larger timestamp is deletion, then the change operation type of the two first change record objects is integrated as deletion; If the change operation type of the first change record object with the larger timestamp is insert, then the change operation types of the two first change record objects are merged into insert; if the change operation type of the first change record object with the smaller timestamp is insert, and the change operation type of the first change record object with the larger timestamp is update, then the change operation types of the two first change record objects are merged into insert; if the change operation type of the first change record object with the larger timestamp is update, and the change operation type of the first change record object with the smaller timestamp is update or delete, then the change operation types of the two first change record objects are merged into update.

[0008] In conjunction with the first aspect, in some embodiments, the step of integrating the data corresponding to the same table fields in the two first change record objects based on the table fields of the two first change record objects includes: creating a data integration object based on the table fields, the data integration object including an id value and change data; assigning a value to the data integration object to obtain a data integration field; the id value of the data integration field is the row id of any one of the two first change record objects, and the change data of the data integration field is the data corresponding to the table field of the first change record object with the larger timestamp among the two first change record objects; setting the data integration field as the data field after the two first change record objects are integrated.

[0009] In conjunction with the first aspect, in some embodiments, obtaining data change records from the upstream database includes: configuring information about the database tables to be monitored and user authorization information in the upstream database; enabling the CDC function of the upstream database based on the database table information and the user authorization information; registering the target connector in Kafka based on the database table information, user authorization information, and target connector information, and then capturing the data change records of the upstream database through the target connector based on the CDC function of the upstream database.

[0010] Secondly, embodiments of this application provide a data synchronization system between databases, including: an initial creation module for creating a first topic and a second topic in Kafka; a change extraction module for obtaining data change records from an upstream database and storing the data change records in the first topic; a change processing module for reading target data change records from the first topic and preprocessing the target data change records to obtain a first change record object, the first change record object including a table name, row ID, table fields, and change operation type, wherein the target data change record is one or more of the data change records; the change processing module is further used in a Flink execution environment to... First change record objects with the same table name and the same row ID are grouped into the same region; for each region, an integration window is created; the integration window receives the first change record objects from the corresponding region; the change processing module is further configured to, when the number of first change record objects in the integration window reaches the capacity of the integration window, integrate the first change record objects in the integration window based on the table fields and the change operation type to obtain second change record objects, and store the second change record objects in the second topic; the change synchronization module is configured to read the second change record objects from the second topic and synchronize the data in the second change record objects to the downstream database.

[0011] Thirdly, embodiments of this application provide an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the data synchronization method between databases as described in any of the first aspects.

[0012] Fourthly, embodiments of this application provide a computer-readable storage medium storing a computer program that, when executed by a processor, implements the data synchronization method between databases as described in any of the first aspects.

[0013] Fifthly, embodiments of this application provide a computer program product that, when run on an electronic device, causes the electronic device to execute the data synchronization method between databases described in any of the first aspects above.

[0014] The advantages of the embodiments in this application compared with related technologies are: This application provides a data synchronization method between databases. The method includes: First, creating a first topic and a second topic in Kafka; then, obtaining data change records from the upstream database and storing the data change records in the first topic; next, reading target data change records from the first topic and preprocessing them to obtain a first change record object, which includes a table name, row ID, table fields, and change operation type, wherein the target data change record is one or more of the data change records; then, in the Flink execution environment, grouping first change record objects with the same table name and row ID into the same region, creating an integration window for each region, and receiving the first change record objects from the corresponding region through the integration window; when the number of first change record objects in the integration window reaches the capacity of the integration window, integrating the first change record objects in the integration window based on the table fields and change operation type to obtain a second change record object, and storing the second change record object in the second topic; finally, reading the second change record object from the second topic and synchronizing the data in the second change record object to the downstream database. In this way, before data synchronization with the downstream database, the data change records obtained from the upstream database are integrated. Multiple data change records with the same table name and row ID can be integrated into one change record, which helps to save a lot of invalid intermediate data, effectively reduces the data load of the downstream database, reduces the pressure on the downstream database, and improves the efficiency of data synchronization between upstream and downstream databases.

[0015] It is understood that the beneficial effects of the second to fifth aspects mentioned above can be found in the relevant descriptions in the first aspect mentioned above, and will not be repeated here.

[0016] It should be understood that the above general description and the following detailed description are exemplary and explanatory only, and are not intended to limit this specification. Attached Figure Description

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

[0018] Figure 1 This is a schematic diagram illustrating an application scenario of the data synchronization method between databases provided in an embodiment of this application; Figure 2 This is a flowchart illustrating a data synchronization method between databases provided in an embodiment of this application; Figure 3 This is a flowchart illustrating a data synchronization method between databases provided in another embodiment of this application; Figure 4 This is a flowchart illustrating a data synchronization method between databases provided in another embodiment of this application; Figure 5 This is a flowchart illustrating a data synchronization method between databases provided in another embodiment of this application; Figure 6 This is a schematic diagram illustrating the integration of two first change record objects provided in an embodiment of this application; Figure 7 This is a schematic diagram of the architecture of the data synchronization system between databases provided in the embodiments of this application; Figure 8 This is a flowchart of a data synchronization method between databases provided in an embodiment of this application; Figure 9 This is a schematic diagram of the structure of the data synchronization system between databases provided in the embodiments of this application; Figure 10 This is a schematic diagram of the structure of the electronic device provided in the embodiments of this application. Detailed Implementation

[0019] In the following description, specific details such as particular system architectures and techniques are set forth for illustrative purposes and not for limitation, in order to provide a thorough understanding of the embodiments of this application. However, those skilled in the art will understand that this application may also be implemented in other embodiments without these specific details. In other instances, detailed descriptions of well-known systems, circuits, and methods are omitted so as not to obscure the description of this application with unnecessary detail.

[0020] It should be understood that, when used in this application specification and the appended claims, the term "comprising" indicates the presence of the described features, integrals, steps, operations, elements and / or components, but does not exclude the presence or addition of one or more other features, integrals, steps, operations, elements, components and / or a collection thereof.

[0021] It should also be understood that the term “and / or” as used in this application specification and the appended claims means any combination of one or more of the associated listed items and all possible combinations, and includes such combinations.

[0022] As used in this application specification and the appended claims, the term "if" may be interpreted, depending on the context, as "when," "once," "in response to determination," or "in response to detection." Similarly, the phrase "if determined" or "if detected [the described condition or event]" may be interpreted, depending on the context, as meaning "once determined," "in response to determination," "once detected [the described condition or event]," or "in response to detection [the described condition or event]."

[0023] Furthermore, in the description of this application and the appended claims, the terms "first," "second," "third," etc., are used only to distinguish descriptions and should not be construed as indicating or implying relative importance.

[0024] References to "one embodiment" or "some embodiments" as described in this specification mean that one or more embodiments of this application include a specific feature, structure, or characteristic described in connection with that embodiment. Therefore, the phrases "in one embodiment," "in some embodiments," "in other embodiments," "in still other embodiments," etc., appearing in different parts of this specification do not necessarily refer to the same embodiment, but rather mean "one or more, but not all, embodiments," unless otherwise specifically emphasized. The terms "comprising," "including," "having," and variations thereof mean "including but not limited to," unless otherwise specifically emphasized.

[0025] Data synchronization is a process of data transmission and processing performed to maintain consistency between data sources and destinations. In this application embodiment, data synchronization can be a data update between a main system and a subsystem.

[0026] In databases, Change Data Capture (CDC) technology is a set of software design patterns used to identify and track changed data.

[0027] In related technologies, incremental data synchronization can be achieved by employing CDC technology, ETL (Extraction Transformation Load) tools, and related database components. However, with the continuous increase in data volume, how to efficiently achieve incremental data synchronization is a pressing issue that needs to be addressed.

[0028] In BI (Business Intelligence) projects, incremental data synchronization can enable timely monitoring of business data, thereby helping users make quick and accurate business decisions. However, when faced with the synchronization of massive amounts of data changes, downstream databases need to perform a large number of change operations when reproducing the data, resulting in excessive computational pressure and a significant decrease in data synchronization efficiency.

[0029] To address the aforementioned issues, this application provides a method, system, electronic device, and storage medium for data synchronization between databases. By capturing data change records to obtain changed data from the upstream database, and integrating these records before reproducing the data in the downstream database, the number of change operations required by the downstream database is reduced. Therefore, when dealing with massive amounts of data changes, the method in this application can significantly improve the efficiency of data synchronization.

[0030] For example, embodiments of this application can be applied to, for example... Figure 1 The exemplary scenario 100 shown includes an upstream database 110, an extraction module 120, a processing module 130, a synchronization module 140, and a downstream database 150. The upstream database 110 is the source database, and it needs to synchronize changed data to the downstream database 150, which is the destination database. The downstream database 150 needs to synchronize changed data based on the changes in the upstream database 110. In this embodiment, the extraction module 120 extracts data change records from the upstream database 110, then transmits these records to the processing module 130. The processing module 130 integrates the data change records, and finally, the integrated change records are transmitted to the synchronization module 140, which synchronizes the data in the integrated change records to the downstream database 150.

[0031] In one application scenario, the upstream database can be the subsidiary's database, and the downstream database can be the head office's database. The data synchronization method between databases provided in this application embodiment can realize the real-time update of business data aggregated by the subsidiary to the head office, thereby assisting the head office's managers to make business decisions faster and more accurately.

[0032] Figure 2 This is a flowchart illustrating a data synchronization method between databases provided in an embodiment of this application. (Refer to...) Figure 2 The data synchronization method between the databases is described in detail below: S201, create the first and second topics in Kafka.

[0033] Kafka is a distributed message queue that can handle high-throughput messages with relatively low resource consumption. The basic architecture of Kafka includes producers, consumers, topics, and brokers. Producers send messages to Kafka; consumers read messages from Kafka; and Kafka partitions messages by topic. Producers produce messages for topics, and consumers consume messages from topics. A Kafka cluster consists of multiple brokers, and a broker can hold multiple topics.

[0034] S202, retrieve the data change records from the upstream database and store the data change records in the first topic.

[0035] In some embodiments, step S202 can be implemented by steps S2021 to S2023: S2021, configure the information of the database tables to be monitored and the user authorization information in the upstream database.

[0036] Optionally, the upstream database can be a database such as SQL Server, MongoDB, Oracle, or PostgreSQL, and the downstream database can be a database such as MySQL.

[0037] Optionally, the database tables to be monitored can be those included in the data synchronization commands entered by the user. User authorization information may include username and password, among other things.

[0038] S2022, based on the information in the database table and the user's authorization information, enable the CDC function of the upstream database.

[0039] S2023, based on the information of the database tables to be monitored, the user's authorization information, and the target connector information, first registers the target connector in Kafka, and then captures the data change records of the upstream database through the target connector based on the CDC function of the upstream database.

[0040] In some embodiments, the target connector can be a Debezium connector. Debezium is an open-source incremental data synchronization tool that extracts data change records from database logs in real time and outputs the captured data change records as a data stream.

[0041] Optionally, if the upstream database is an SQL Server database, the target connector is the Debezium SQL Server connector.

[0042] In one possible implementation, the target connector is registered in Kafka based on information about the database tables to be monitored, user authorization information, and target connector information. This can be accomplished by the target connector sending a registration command to Kafka, which includes the aforementioned database table information and user authorization information. The target connector information is the plugin path of the target connector in the Kafka connector configuration file.

[0043] In one possible implementation, an example of the registration command for the target connector described above is as follows: curl -i -X ​​POST -H "Accept:application / json" -H "Content-Type:application / json" localhost:8083 / connectors / -d' { "name": "score-sqlserver-connector", "config":{ "connector.class": "io.debezium.connector.sqlserver.SqlServerConnector", "tasks.max": "1", "database.hostname": "118.190.208.219", "database.port": "1433", "database.user": "sa", "database.password": "********", "database.dbname": "testCDC", "database.server.name": "fullfillment", "database.whitelist": "dbo.score", "database.history.kafka.bootstrap.servers": "118.190.208.219:9092", "database.history.kafka.topic": "mssqlhistory.score2"} }' In the above implementation, if the target connector is successfully registered, a "Configuration Normal" message is output. This "Configuration Normal" message can be: {"version":"2.6.0","commit":"62abe01bee039651","kafka_cluster_id":"WeC-6lrLQDWMd2YjWqKMVw"} In the above embodiments, Debezium's connector is used to capture data change records. This method monitors data changes in the source database based on database logs. This non-intrusive reading method can avoid a significant drop in database performance. Compared with change data capture methods based on triggers or timestamps, the method in this embodiment does not occupy source database resources and does not affect the performance of the source database.

[0044] In related technologies, real-time querying of SQL Server database changes can be accomplished using the official SQL Server Management Studio tool. However, this tool does not provide incremental data update functionality. Using this tool to assist in capturing change data requires a certain understanding of its underlying code principles, and the data update component is highly coupled with the tool, making it inconvenient for error troubleshooting and rapid deployment. In contrast, the method in this application embodiment only requires registering the Debezium SQL Server connector in Kafka to achieve real-time extraction of change data. Therefore, the method in this application embodiment has the advantages of simplicity, speed, and strong real-time performance, meeting the needs for real-time data querying and analysis. Furthermore, based on Kafka's distributed and high-performance fault-tolerance mechanism, even if the application stops service or even crashes suddenly, Kafka will not miss or lose data change records after the application restarts.

[0045] S203, Read the target data change record from the first topic, and preprocess the target data change record to obtain the first change record object. The first change record object includes table name, row ID, table fields and change operation type. The target data change record is one or more of the data change records.

[0046] In some embodiments, prior to S203, the data synchronization method between the databases further includes: starting the Flink execution environment and creating a Kafka consumer, which is used to read target data change records from the first topic.

[0047] Optionally, the preprocessing of the target data change records in step S203 above to obtain the first change record object can be performed by converting the target data change records into a first change record object in a unified format suitable for Flink processing. This facilitates the subsequent unified processing of these first change record objects.

[0048] In some embodiments, the first change record object may further include a data field and a time field. The data field is the data corresponding to a table field; for example, the data field may include data from the table field before the change and data from the table field after the change. There may be one or more table fields. The time field may be the time when the change operation occurred.

[0049] Optionally, the first change record object obtained after preprocessing can be passed to the next step for processing in the form of an event stream.

[0050] S204. In the Flink execution environment, the first change record objects with the same table name and the same row ID are grouped into the same region; for each region, an integration window is created; the first change record object in the corresponding region is received through the integration window.

[0051] In some embodiments, prior to step S204, the data synchronization method between databases may further include: in the Flink execution environment, using a filter operator to filter out invalid or empty first change record objects. Invalid first change record objects may be first change record objects that do not include change operation types, or they may be first change record objects with data corruption. Empty first change record objects may be first change record objects with data loss.

[0052] In some embodiments, step S204 above can be implemented by the following steps: In the Flink execution environment, the `keyBy` operator is used to partition the data stream consisting of multiple first-change record objects, grouping first-change record objects with the same table name and row ID into the same region. On the data stream of each region, a consolidation window is created using the `countWindow` operator; this window receives the first-change record objects from the corresponding region. The number of objects that the consolidation window can hold can be defined by user-inputted window parameters, or by determining the amount of data to be synchronized based on user-inputted data synchronization commands, and then defining the number of objects that the consolidation window can hold based on the relationship between this data amount and a preset threshold. The `keyBy` and `countWindow` operators are Flink operators.

[0053] S205, when the number of first change record objects in the integration window reaches the capacity of the integration window, the first change record objects in the integration window are integrated based on the table fields and change operation type to obtain second change record objects, and the second change record objects are stored in the second topic.

[0054] In some embodiments, see Figure 3 The step S205 above, which integrates the first change record object in the integration window based on table fields and change operation type to obtain the second change record object, can include steps S2051 to S2053: S2051, select two first change record objects in the integration window, analyze the change operation types of the two first change record objects, integrate the change operation types in the two first change record objects, and integrate the data corresponding to the same table fields in the two first change record objects based on the table fields of the two first change record objects to obtain a third change record object.

[0055] Optional, see Figure 4 The above step S2051, which selects two first change record objects in the integration window, and integrates the change operation types of the two first change record objects by analyzing their change operation types, can be achieved through the following steps: S401, select the two first change record objects received first according to the receiving order of the integration window.

[0056] S402, obtain the change operation type and time field of the two first change record objects mentioned above, and convert the time field into a millisecond-level timestamp.

[0057] S4031, if the change operation type of the first change record object with the larger timestamp among the two first change record objects is deletion, then the change operation types of the two first change record objects are merged into deletion.

[0058] S4032, if the change operation type of the first change record object with the larger timestamp among the two first change record objects is insert, then the change operation types of the two first change record objects are merged into insert.

[0059] S4033, if the change operation type of the first change record object with the smaller timestamp is insert and the change operation type of the first change record object with the larger timestamp is update, then the change operation types of the two first change record objects are merged into insert.

[0060] S4034, if among the two first change record objects, the change operation type of the first change record object with the larger timestamp is update, and the change operation type of the first change record object with the smaller timestamp is update or delete, then the change operation types of the two first change record objects are merged into update.

[0061] Optional, see Figure 5 The step S2051 above, which integrates the data corresponding to the same table fields in the two first change record objects based on the table fields of the two first change record objects, can be achieved through the following steps: S501, based on table fields, create a data integration object, which includes id values ​​and change data.

[0062] S502, assign values ​​to the data integration object to obtain the data integration field; the id value of the data integration field is the row id of any one of the two first change record objects, and the changed data of the data integration field is the data corresponding to the table field of the first change record object with the larger timestamp among the two first change record objects.

[0063] S503 sets the data integration field to the data field after the two first change record objects are integrated.

[0064] Optionally, after step S503 above, the data synchronization method between data in this application embodiment may further include: setting the time field of the first change record object with the larger timestamp among the two first change record objects as the time field after the two first change record objects are integrated.

[0065] Figure 6 This is a schematic diagram illustrating the integration of two first change record objects provided in an embodiment of this application. See also... Figure 6 Two first change record objects were selected from the same integration window: the earlier first change record object 610 and the later first change record object 620. After integrating the earlier first change record object 610 and the later first change record object 620, a third change record object 630 was obtained. (See also...) Figure 6 In this embodiment, the first change record object includes, from left to right, the table name, row ID, table field, change operation type, data field, and time field.

[0066] The integration process for the first change record object 610 (preceding) and the second first change record object 620 is as follows: First, the change operation type of the first change record object 610 is determined to be "insert," and the change operation type of the second first change record object 620 is determined to be "update." Simultaneously, the time field of the first change record object 610 is determined to be "time 1," and the time field of the second first change record object 620 is determined to be "time 2." Time 1 and Time 2 are converted to millisecond-level timestamps. Based on the order of the times and the conversion principle of timestamps, it is known that the timestamp corresponding to Time 1 is smaller, and the timestamp corresponding to Time 2 is larger. According to step S4033 above, since the change operation type of the first change record object 610 with the smaller timestamp is "insert," and the change operation type of the second first change record object 620 with the larger timestamp is "update," the change operation types of the two first change record objects are integrated into "insert." Following steps S501 and S503 above, the data fields are integrated into id_001[before(1,5),after(3,5)]. It can be seen that the data in the integrated data field is the same as the data field in the first subsequent change record object 620. The integrated time field is the same as the time field in the first subsequent change record object 620.

[0067] S2052, select a third change record object and another first change record object in the integration window. By analyzing the change operation types of the third change record object and the first change record object, integrate the change operation types in the third change record object and the first change record object. Based on the table fields of the third change record object and the first change record object, integrate the data corresponding to the same table fields in the third change record object and the first change record object to obtain a new third change record object.

[0068] Optionally, the other first change record object mentioned above is the next first change record object arranged in the receiving order in the integration window.

[0069] S2053, jump to the step of selecting the third change record object and another first change record object in the integration window, and perform the loop operation until the integration window only includes the latest third change record object, and the latest third change record object is the second change record object.

[0070] When a large number of database tables in the upstream database are frequently updated simultaneously, and the tables have many fields, the number of data change records that need to be synchronized can be massive. In this case, the method described in the above embodiment can be used to first integrate the data change records of the same database table and the same row, and then synchronize them to the downstream database. The downstream database only needs to perform a small number of change operations to achieve data synchronization, thereby effectively reducing the pressure on the downstream database.

[0071] In one possible implementation, tens of thousands of data change operations may occur in a database table within a very short period of time. If the database table has 100 rows of data, there are 100 different row IDs. For a large number of data changes, the capacity of the consolidation window can be set to a larger value. For example, one consolidation window can hold 40 first change record objects. Ideally, if an average of 200 change operations occur per row, then for each row's data change operations, the consolidation window can consolidate the 200 data change records into 5 second change record objects. For 100 rows of data changes, the final number of change operations that the downstream database needs to perform may only be 500. Compared to the tens of thousands of operations required without consolidation, consolidating the first change record objects can significantly improve the data synchronization efficiency of the downstream database.

[0072] S206, Read the second change record object from the second topic and synchronize the data in the second change record object to the downstream database.

[0073] In some embodiments, step S206 can be implemented as follows: At least one second change record object is read from the second topic. The at least one second change record object is parsed to obtain at least one SQL statement. Data in the at least one second change record object is synchronized to the downstream database using the at least one SQL statement.

[0074] Corresponding to the data synchronization method between databases described in the above embodiments, Figure 7 A schematic diagram of the architecture of a data synchronization system between databases provided in an embodiment of this application is shown. The architecture of the data synchronization system between databases includes a web backend 700, a web frontend 750, and peripheral devices. The web backend 700 includes an infrastructure layer 710, a change extraction layer 720, a change processing layer 730, and a change synchronization layer 740.

[0075] The infrastructure layer 710 includes the Hadoop distributed infrastructure and multiple SQL Server databases. Infrastructure layer 710 primarily handles the setup and rapid deployment of the server environment, while also enabling data access. The change extraction layer 720 includes a Kafka cluster, the Flink distributed stream processing engine, and the Debezium incremental data synchronization tool (target connector). Change extraction layer 720 first uses the Flink distributed stream processing engine to establish data connections with each SQL Server database. Then, through the Debezium incremental data synchronization tool in conjunction with Kafka, it captures data change records in real-time from the SQL Server database logs and stores them in Kafka topics for consumption.

[0076] The change processing layer 730 includes the Flink distributed stream processing engine and a cached state event stream. Utilizing Kafka consumers and the Flink distributed stream processing engine, it consumes and processes data change records from the topics in the change extraction layer 720, reducing invalid intermediate data in the change records and lowering the data load on subsequent links. The data is then stored in another topic awaiting further consumption.

[0077] The change synchronization layer 740 includes a Kafka cluster and a MySQL database. It utilizes Kafka consumers to subscribe to and consume data change records from topics in the change processing layer 730, and synchronizes them to the MySQL database in real time.

[0078] Corresponding to the above Figure 7 The architecture of the data synchronization system between databases in the illustrated embodiment is as follows: Figure 8 The flowchart illustrates the system's implementation of the aforementioned inter-database data synchronization method. In this embodiment, the upstream database is an SQL Server database, and the downstream database is a MySQL database. Before capturing data change records, the Debezium SQL Server connector sends a registration command to Kafka, and simultaneously, Kafka receives the registration command and configures the Debezium SQL Server connector. Then, the Debezium SQL Server connector captures data change records from the SQL Server database and stores them in a first topic. Next, in the Flink execution environment, the first topic is consumed; that is, the target data change records are read from the first topic and preprocessed to obtain a first change record object. Then, an integration window is created, and the first change record object is integrated through the integration window to obtain a second change record object. The second change record object is stored in a second topic. Finally, the second topic is consumed; that is, the second change record object is read from the second topic, parsed into an SQL statement, and passed to the MySQL database. The MySQL database synchronizes the changed data based on the SQL statement.

[0079] The above Figures 7 to 8 The embodiments shown can achieve the following technical effects: 1. During the change extraction process, the intrusion on the source database is minimized, and its performance is not compromised. The Debezium SQL Server connector is used to non-intrusively read the database logs to capture data change records. This method avoids significant performance degradation or even database crashes. Compared to change data capture methods based on triggers or timestamps, the method in the above embodiment does not consume source database resources and does not affect the source database's performance.

[0080] 2. Capable of quickly and in real-time synchronizing incremental data. Data extraction is accomplished through the Debezium SQL Server connector. This method only requires configuring and registering the Debezium SQL Server connector in Kafka to achieve real-time extraction of changed data. Subsequently, by operating a Kafka consumer in the MySQL database to consume the changed data, the changed data can be synchronized to the MySQL database.

[0081] 3. A decompression strategy for massive data is provided. In the above embodiment, an intermediate decompression processing program for data change records was developed based on the incremental data log characteristics of SQL Server databases. First, the database change records are preprocessed by analyzing the captured SQL Server database data change records and processing them into first change record objects of a unified format. Then, an integration window is established to analyze and process the first change record objects. The change record objects arriving at the integration window are compared and analyzed. By analyzing the change operation types of these first change record objects, invalid change operations within a certain time period are deleted or related change operations are integrated. Simultaneously, the parameters of the integration window can be flexibly adjusted according to the user's actual needs, thereby meeting the real-time update requirements of large amounts of data in industrial big data scenarios and responding promptly to market changes. Finally, the integrated second change record object is sent downstream. Because the above integration process filters out invalid intermediate operations in real time, the downstream database only needs to perform a small number of change operations to achieve data synchronization. Therefore, based on the system architecture in the above embodiment, the above method can support the real-time synchronization of massive change records in big data scenarios, achieving decompression of massive data.

[0082] 4. It features high consistency and robust fault tolerance. When retrieving data change records, Debezium combined with Kafka is used for real-time extraction of these records. Benefiting from Kafka's distributed and high-performance fault tolerance mechanisms, even if the application stops service or crashes suddenly, no data change record will be missed or lost after restarting.

[0083] The data synchronization method between databases provided in this application integrates data change records before the downstream database reproduces the data. Multiple first change record objects within an integration window are merged into a single second change record object. Consequently, the downstream database only needs to synchronize data based on this second change record object, effectively reducing the number of change operations required. Therefore, when faced with massive data changes, the method in this application significantly reduces the pressure on the downstream database and improves data synchronization efficiency.

[0084] It should be understood that the sequence number of each step in the above embodiments does not imply the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiments of this application.

[0085] Corresponding to the data synchronization method between databases described in the above embodiments, Figure 9 A schematic diagram of the structure of a data synchronization system 900 between databases provided in an embodiment of this application is shown. For ease of explanation, only the parts related to the embodiment of this application are shown.

[0086] See Figure 9 The data synchronization system 900 between databases in this application embodiment may include an initial creation module 910, a change extraction module 920, a change processing module 930, and a change synchronization module 940.

[0087] The initial creation module 910 is used to create the first and second topics in Kafka.

[0088] The change extraction module 920 is used to obtain data change records from the upstream database and store the data change records in the first topic.

[0089] The change processing module 930 is used to read the target data change record from the first topic and preprocess the target data change record to obtain the first change record object. The first change record object includes the table name, row ID, table fields and change operation type. The target data change record is one or more of the data change records.

[0090] The change processing module 930 is also used to group the first change record objects with the same table name and the same row ID into the same region in the Flink execution environment; for each region, an integration window is created; and the first change record object in the corresponding region is received through the integration window.

[0091] The change processing module 930 is also used to integrate the first change record objects in the integration window based on table fields and change operation type when the number of first change record objects in the integration window reaches the capacity of the integration window, to obtain second change record objects, and to store the second change record objects in the second topic.

[0092] The change synchronization module 940 is used to read the second change record object from the second topic and synchronize the data in the second change record object to the downstream database.

[0093] Optionally, the change extraction module 920 can be used to configure the database table information to be monitored and the user's authorization information in the upstream database; enable the CDC function of the upstream database based on the database table information and the user's authorization information; register the target connector in Kafka based on the database table information and the target connector information, and then capture the data change records of the upstream database through the target connector based on the CDC function of the upstream database.

[0094] Optionally, the first change record object may also include data fields, which are the data corresponding to the table fields. The change processing module 930 can specifically be used to select two first change record objects in the integration window, analyze the change operation types of the two first change record objects, integrate the change operation types in the two first change record objects, and integrate the data corresponding to the same table fields in the two first change record objects based on the table fields of the two first change record objects, to obtain a third change record object. Then, the third change record object and another first change record object in the integration window are selected. The change operation types of the third change record object and the first change record object are analyzed, and the change operation types in the third change record object and the first change record object are integrated. Based on the table fields of the third change record object and the first change record object, the data corresponding to the same table fields in the third change record object and the first change record object are integrated, to obtain a new third change record object. The process jumps to the step of selecting the third change record object and another first change record object in the integration window, and repeats this process until the integration window only includes the latest third change record object, which is the second change record object.

[0095] Optionally, the first change record object may also include a time field. The change processing module 930 can further be used to select the two first change record objects received earlier according to the receiving order of the integration window. It obtains the change operation type and time field of the two first change record objects and converts the time field to a millisecond-level timestamp. If the change operation type of the first change record object with the larger timestamp is deletion, then the change operation types of the two first change record objects are integrated into deletion. If the change operation type of the first change record object with the larger timestamp is insertion, then the change operation types of the two first change record objects are integrated into insertion. If the change operation type of the first change record object with the smaller timestamp is insertion, and the change operation type of the first change record object with the larger timestamp is update, then the change operation types of the two first change record objects are integrated into insertion. If the change operation type of the first change record object with the larger timestamp is update, and the change operation type of the first change record object with the smaller timestamp is either update or deletion, then the change operation types of the two first change record objects are integrated into update.

[0096] Optionally, the change processing module 930 can also be used to create a data integration object based on table fields. The data integration object includes an id value and change data. Values ​​are assigned to the data integration object to obtain the data integration field; the id value of the data integration field is the row id of either of the two first change record objects, and the change data is the data corresponding to the table field of the first change record object with the larger timestamp. The data integration field is then set as the data field after the two first change record objects have been integrated.

[0097] Optionally, the change synchronization module 940 can be used to read at least one second change record object from the second topic. The at least one second change record object is parsed to obtain at least one SQL statement. The data in the at least one second change record object is synchronized to the downstream database using the at least one SQL statement.

[0098] It should be noted that the information interaction and execution process between the above modules are based on the same concept as the method embodiments of this application. For details on their specific functions and technical effects, please refer to the method embodiments section, and they will not be repeated here.

[0099] Those skilled in the art will clearly understand that, for the sake of convenience and brevity, the above-described functional module division is merely an example. In practical applications, the above functions can be assigned to different functional units or modules as needed, that is, the internal structure of the system can be divided into different functional units or modules to complete all or part of the functions described above. Furthermore, the specific names of each functional module are only for easy differentiation and are not intended to limit the scope of protection of this application. The specific working process of each module in the above system can be referred to the corresponding process in the foregoing method embodiments, and will not be repeated here.

[0100] This application also provides an electronic device, see [link to relevant documentation] Figure 10 The electronic device 1010 may include: at least one processor 1010, a memory 1020, and a computer program stored in the memory 1020 and executable on the at least one processor 1010. When the processor 1010 executes the computer program, it implements the steps in any of the above-described method embodiments, for example... Figure 2 Steps S101 to S106 in the illustrated embodiment. Alternatively, when the processor 1010 executes the computer program, it implements the functions of each module / unit in the above-described device embodiments, for example... Figure 9 The functions of modules 910 to 940 are shown.

[0101] For example, a computer program may be divided into one or more modules / units, one or more of which are stored in memory 1020 and executed by processor 1010 to complete this application. The one or more modules / units may be a series of computer program segments capable of performing specific functions, which describe the execution process of the computer program in electronic device 1000.

[0102] Those skilled in the art will understand that Figure 10 This is merely an example of an electronic device and does not constitute a limitation on the electronic device. It may include more or fewer components than shown, or combinations of certain components, or different components, such as input / output devices, network access devices, buses, etc.

[0103] The processor 1010 can be a Central Processing Unit (CPU), or other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. The general-purpose processor can be a microprocessor or any conventional processor.

[0104] The memory 1020 can be an internal storage unit of the electronic device or an external storage device, such as a plug-in hard drive, a smart media card (SMC), a secure digital (SD) card, or a flash card. The memory 1020 is used to store the computer program and other programs and data required by the electronic device. The memory 1020 can also be used to temporarily store data that has been output or will be output.

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

[0106] The data synchronization method between databases provided in this application can be applied to electronic devices such as computers, wearable devices, in-vehicle devices, tablet computers, laptops, netbooks, personal digital assistants (PDAs), augmented reality (AR) / virtual reality (VR) devices, and mobile phones. This application does not impose any restrictions on the specific type of electronic device.

[0107] This application also provides a computer-readable storage medium storing a computer program that, when executed by a processor, implements the steps in the various embodiments of the data synchronization method between databases described above.

[0108] This application provides a computer program product that, when run on a mobile terminal, enables the mobile terminal to execute the steps in the various embodiments of the data synchronization method between databases described above.

[0109] If the integrated unit is implemented as a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a computer-readable storage medium, and when executed by a processor, it can implement the steps of the various method embodiments described above. The computer program includes computer program code, which can be in the form of source code, object code, executable files, or certain intermediate forms. The computer-readable medium can include at least: any entity or device capable of carrying the computer program code to a photographic device / electronic device, a recording medium, a computer memory, a read-only memory (ROM), a random access memory (RAM), an electrical carrier signal, a telecommunication signal, and a software distribution medium. Examples include USB flash drives, portable hard drives, magnetic disks, or optical disks.

[0110] In the above embodiments, the descriptions of each embodiment have different focuses. For parts that are not described in detail or recorded in a certain embodiment, please refer to the relevant descriptions of other embodiments.

[0111] Those skilled in the art will recognize that the units and algorithm steps of the various examples described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, or a combination of computer software and electronic hardware. Whether these functions are implemented in hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of this application.

[0112] In the embodiments provided in this application, it should be understood that the disclosed apparatus / network devices and methods can be implemented in other ways. For example, the apparatus / network device embodiments described above are merely illustrative. For instance, the division of modules or units is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be through some interfaces; the indirect coupling or communication connection between devices or units may be electrical, mechanical, or other forms.

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

[0114] The above-described embodiments are only used to illustrate the technical solutions of this application, and are not intended to limit them. Although this application has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of this application, and should all be included within the protection scope of this application.

Claims

1. A method for data synchronization between databases, characterized in that, include: Create a first topic and a second topic in Kafka; Obtain data change records from the upstream database and store the data change records in the first topic; Read the target data change records from the first topic, and preprocess the target data change records to obtain a first change record object. The first change record object includes a table name, row ID, table fields, and change operation type. The target data change records are one or more of the data change records. In the Flink execution environment, the first change record objects with the same table name and the same row ID are grouped into the same region; For each region, create an integrated window; The first change record object in the corresponding area is received through the integrated window; When the number of first change record objects in the integration window reaches the capacity of the integration window, the first change record objects in the integration window are integrated based on the table fields and the change operation type to obtain second change record objects, and the second change record objects are stored in the second topic; Read the second change record object from the second topic, and synchronize the data in the second change record object to the downstream database; The process of integrating the first change record objects in the integration window to obtain the second change record object includes: integrating multiple first change record objects in each integration window into one second change record object; The first change record object further includes a data field, wherein the data field is the data corresponding to the table field; the step of integrating the first change record object in the integration window based on the table field and the change operation type to obtain the second change record object includes: Select two first change record objects in the integration window, analyze the change operation types of the two first change record objects, integrate the change operation types of the two first change record objects, and integrate the data corresponding to the same table fields in the two first change record objects based on the table fields of the two first change record objects to obtain a third change record object; Select the third change record object and another first change record object in the integration window. By analyzing the change operation types of the third change record object and the first change record object, integrate the change operation types in the third change record object and the first change record object. Based on the table fields of the third change record object and the first change record object, integrate the data corresponding to the same table fields in the third change record object and the first change record object to obtain a new third change record object. Jump to the step of selecting the third change record object and another first change record object in the integration window, and perform the loop operation until the integration window only includes the latest third change record object, and the latest third change record object is the second change record object; The first change record object also includes a time field; selecting two first change record objects in the integration window, and integrating the change operation types of the two first change record objects by analyzing their change operation types, includes: Based on the receiving order of the integration window, select the two first change record objects received first; Obtain the change operation type and time field of the two first change record objects, and convert the time field into a millisecond-level timestamp; If the change operation type of the first change record object with the larger timestamp among the two first change record objects is deletion, then the change operation types of the two first change record objects will be merged into deletion; If the change operation type of the first change record object with the larger timestamp among the two first change record objects is insert, then the change operation types of the two first change record objects will be merged into insert; If, among the two first change record objects, the change operation type of the first change record object with the smaller timestamp is insert and the change operation type of the first change record object with the larger timestamp is update, then the change operation type of the two first change record objects will be merged into insert; If, among the two first change record objects, the first change record object with the larger timestamp has an update operation type, and the first change record object with the smaller timestamp has an update or delete operation type, then the change operation types of the two first change record objects will be merged into an update.

2. The data synchronization method between databases as described in claim 1, characterized in that, The step of integrating the data corresponding to the same table fields in the two first change record objects based on the table fields of the two first change record objects includes: Based on the table fields, create a data integration object, which includes id values ​​and change data; The data integration object is assigned a value to obtain a data integration field; the id value of the data integration field is the row id of either of the two first change record objects, and the changed data of the data integration field is the data corresponding to the table field of the first change record object with the larger timestamp among the two first change record objects; Set the data integration field as the data field after the two first change record objects are integrated.

3. The data synchronization method between databases as described in claim 1, characterized in that, The process of obtaining data change records from the upstream database includes: Configure the database tables to be monitored and the user's authorization information in the upstream database; Based on the information in the database table and the user's authorization information, enable the CDC function of the upstream database; Based on the information in the database table, the user's authorization information, and the target connector information, the target connector is first registered in Kafka, and then, based on the CDC function of the upstream database, the data change records of the upstream database are captured through the target connector.

4. The data synchronization method between databases as described in claim 1, characterized in that, The step of reading the second change record object from the second topic and synchronizing the data in the second change record object to the downstream database includes: Read at least one second change record object from the second topic; The at least one second change record object is parsed to obtain at least one SQL statement; The data in the at least one second change record object is synchronized to the downstream database using the at least one SQL statement.

5. The data synchronization method between databases as described in claim 1, characterized in that, The upstream database is an SQL Server database, and the downstream database is a MySQL database.

6. A data synchronization system between databases, characterized in that, include: The initial creation module is used to create the first and second topics in Kafka; The change extraction module is used to obtain data change records from the upstream database and store the data change records in the first topic; The change processing module reads the target data change record from the first topic and preprocesses the target data change record to obtain a first change record object. The first change record object includes a table name, row ID, table fields, and change operation type. The target data change record is one or more of the data change records. The change processing module is also used to, in the Flink execution environment, divide the first change record objects with the same table name and the same row ID into the same region; for each region, create an integration window; and receive the first change record objects in the corresponding region through the integration window; The change processing module is further configured to, when the number of first change record objects in the integration window reaches the capacity of the integration window, integrate the first change record objects in the integration window based on the table fields and the change operation type to obtain a second change record object, and store the second change record object in the second topic; The change synchronization module is used to read the second change record object from the second topic and synchronize the data in the second change record object to the downstream database. The change processing module is specifically used to: integrate multiple first change record objects in each integration window into a second change record object; The first change record object also includes a data field, which is the data corresponding to the table field; the change processing module is specifically used for: Select two first change record objects in the integration window, analyze the change operation types of the two first change record objects, integrate the change operation types of the two first change record objects, and integrate the data corresponding to the same table fields in the two first change record objects based on the table fields of the two first change record objects to obtain a third change record object; Select the third change record object and another first change record object in the integration window. By analyzing the change operation types of the third change record object and the first change record object, integrate the change operation types in the third change record object and the first change record object. Based on the table fields of the third change record object and the first change record object, integrate the data corresponding to the same table fields in the third change record object and the first change record object to obtain a new third change record object. Jump to the step of selecting the third change record object and another first change record object in the integration window, and perform the loop operation until the integration window only includes the latest third change record object, and the latest third change record object is the second change record object; The first change record object also includes a time field; the change processing module can also be used for: Based on the receiving order of the integration window, select the two first change record objects received first; Obtain the change operation type and time field of the two first change record objects, and convert the time field into a millisecond-level timestamp; If the change operation type of the first change record object with the larger timestamp among the two first change record objects is deletion, then the change operation types of the two first change record objects will be merged into deletion; If the change operation type of the first change record object with the larger timestamp among the two first change record objects is insert, then the change operation types of the two first change record objects will be merged into insert; If, among the two first change record objects, the change operation type of the first change record object with the smaller timestamp is insert and the change operation type of the first change record object with the larger timestamp is update, then the change operation type of the two first change record objects will be merged into insert; If, among the two first change record objects, the first change record object with the larger timestamp has an update operation type, and the first change record object with the smaller timestamp has an update or delete operation type, then the change operation types of the two first change record objects will be merged into an update.

7. An electronic device comprising a memory and a processor, wherein the memory stores a computer program executable on the processor, characterized in that, When the processor executes the computer program, it implements the method as described in any one of claims 1 to 5.

8. A computer-readable storage medium storing a computer program, characterized in that, When the computer program is executed by a processor, it implements the method as described in any one of claims 1 to 5.

Images