Multi-target full-amount and incremental data synchronization method and system

Abstract

The invention relates to the technical field of data synchronization, in particular to a multi-target total and incremental data synchronization method and system, which can respectively construct mutually independent data pipelines for each type of data; using the data pipeline to write historical business data into a target database; monitoring the Binlog of the source database to obtain real-time incremental data, writing the incremental data into the target database by using the data pipeline, and meanwhile, periodically recording GTIDs and corresponding timestamps, and storing the GTIDs and the corresponding timestamps as recovery points; and when incremental data writing is abnormal, selecting a recovery point, and re-executing subsequent incremental data writing. It can be understood that according to the technical scheme shown by the invention, if abnormal interruption occurs, accurate recovery can be carried out from the latest GTID, and data loss is avoided.

Description

Technical Field

[0001] This invention relates to the field of data synchronization technology, specifically to a method and system for synchronizing multi-target full and incremental data. Background Technology

[0002] In data-intensive applications, as the amount of data continues to grow, performing analysis and calculations directly on the production business database can lead to a decline in the performance of core transaction services, and in severe cases, even cause business failure.

[0003] Therefore, to ensure the performance of the core online transaction processing system, it is usually necessary to synchronize business data from the production database to one or more downstream analytical databases to support complex queries and data analysis. This separates the pressure of transaction processing from that of data analysis.

[0004] However, data recovery becomes difficult in the event of an interruption during data synchronization. For example, the loss of financial-grade data directly impacts clearing, settlement, and financial reconciliation. Traditional Binlog-based synchronization methods struggle to accurately recover synchronization points after abnormal interruptions, easily leading to data loss. Summary of the Invention

[0005] In view of this, the purpose of the present invention is to provide a method and system for synchronizing full and incremental data for multiple targets, so as to solve the problem that data compensation is difficult in the prior art when data synchronization is interrupted.

[0006] According to a first aspect of the present invention, a method for synchronizing multi-target full and incremental data is provided, comprising: Based on the data types in the source database, data pipelines are built for each type of data, and the data pipelines corresponding to different types of data are independent of each other; Using the data pipeline, historical business data from the source database is written to the target database; Monitor the Binlog of the source database to obtain real-time incremental data, and use the data pipeline to write the incremental data into the target database. When writing the incremental data to the target database, the GTID and corresponding timestamp of the Binlog are periodically recorded, and the GTID and corresponding timestamp are stored as a recovery point. When an incremental data write fails, the abnormal time point is compared with the timestamp to select a recovery point. Based on the recovery point, subsequent incremental data writes are re-executed.

[0007] Preferably, historical business data from the source database is written to the target database, including: The auto-incrementing primary key ID of each data entry in the source database is used as the splitting condition to split all historical business data in the source database into multiple batches. The historical business data from each batch is written to the target database sequentially.

[0008] Preferably, when writing the real-time incremental data to the target database, the method further includes: The data pipeline is used to subscribe to the source database's Binlog to obtain incremental change events in real time. The incremental change event is parsed to obtain incremental data, and the format of the incremental data is converted. Based on the primary key of the incremental data, determine whether the incremental data exists in the target database; If it does not exist, perform an insertion operation; if it exists, perform an update operation.

[0009] Preferably, the GTID and corresponding timestamp of the Binlog are recorded periodically, and the GTID and corresponding timestamp are stored as a recovery point, including: In the source database, the GTIDs of the incremental change events that have been completed are periodically recorded and integrated into a GTID set. The GTID set is then bound to the current timestamp and stored as a recovery point in the log table. When an incremental data write fails, the timestamp of the failure point is compared with the timestamp of the recovery point stored in the log table. The recovery point preceding the failure point is selected, and the GTID set corresponding to the recovery point is configured into the starting point parameter of the data pipeline so that the data pipeline can re-execute the subsequent incremental data write.

[0010] Preferably, based on the data types in the source database, a data pipeline is constructed separately for each data type, further including: Each data pipeline is configured with a data preprocessing node and a multi-target distribution node; The data preprocessing node is used to perform field mapping, type conversion, data masking, and abnormal data filtering; The multi-target distribution node is used to write the incremental data into multiple target databases.

[0011] According to a second aspect of the present invention, a multi-target full and incremental data synchronization system is provided, comprising: The data pipeline construction module is used to build data pipelines for each type of data based on the data type in the source database. The data pipelines for different types of data are independent of each other. The historical data synchronization module is used to write historical business data from the source database to the target database using the data pipeline. The incremental data synchronization module is used to monitor the Binlog of the source database, obtain real-time incremental data, and write the incremental data to the target database using the data pipeline. When writing the incremental data to the target database, the GTID and corresponding timestamp of the Binlog are periodically recorded, and the GTID and corresponding timestamp are stored as a recovery point. When an incremental data writing error occurs, the error time point is compared with the timestamp to select a recovery point, and subsequent incremental data writing is re-executed based on the recovery point.

[0012] The technical solutions provided by the embodiments of the present invention may include the following beneficial effects: It is understood that the technical solution presented in this invention can construct independent data pipelines for each type of data; using these data pipelines, historical business data is written to the target database; the source database's Binlog is monitored to obtain real-time incremental data, which is then written to the target database using the data pipelines. Simultaneously, GTIDs and corresponding timestamps are periodically recorded and stored as recovery points; when an incremental data writing failure occurs, a recovery point is selected, and subsequent incremental data writing is re-executed. It is understood that the technical solution presented in this invention, in the event of an abnormal interruption, can accurately recover from the most recent GTID, avoiding data loss; furthermore, different data pipelines are isolated from each other, and the pause, failure, or anomaly of a single data synchronization task will not affect other tasks, ensuring system stability in high-concurrency, high-real-time scenarios.

[0013] 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 the invention. Attached Figure Description

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

[0015] Figure 1 This is a flowchart illustrating a multi-target full and incremental data synchronization method according to an exemplary embodiment. Detailed Implementation

[0016] Exemplary embodiments will now be described in detail, examples of which are illustrated in the accompanying drawings. When the following description relates to the drawings, unless otherwise indicated, the same numerals in different drawings denote the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with the present invention. Rather, they are merely examples of apparatuses and methods consistent with some aspects of the invention as detailed in the appended claims.

[0017] In one embodiment, a method for synchronizing multi-target full and incremental data is provided, comprising: Based on the data types in the source database, data pipelines are built for each type of data, and the data pipelines corresponding to different types of data are independent of each other; Using the data pipeline, historical business data from the source database is written to the target database; Monitor the Binlog of the source database to obtain real-time incremental data, and use the data pipeline to write the incremental data into the target database. When writing the incremental data to the target database, the GTID (Global Transaction Identifier) ​​and the corresponding timestamp of the Binlog are periodically recorded, and the GTID and the corresponding timestamp are stored as a recovery point. When an incremental data write fails, the abnormal time point is compared with the timestamp to select a recovery point. Based on the recovery point, subsequent incremental data writes are re-executed.

[0018] In practice, Figure 1 This is a flowchart illustrating a multi-target full and incremental data synchronization method according to an exemplary embodiment. See also... Figure 1 First, a data pipeline is constructed. This embodiment leverages StreamSets' visual pipeline capabilities to design independent data pipelines for different types of data. For example, in the public transport NFC card industry, the existing data types include: card swiping records, recharge records, and card activation data. Independent data pipelines can be designed for each type, allowing for data synchronization between different data types using these independent pipelines. Simultaneously, each pipeline is assigned a unique serverId, a core parameter for MySQL Binlog subscriptions: each task subscribing to Binlog must have an independent serverId to avoid Binlog consumption conflicts.

[0019] In a preferred embodiment, a data pipeline is constructed for each type of data according to the data type in the source database, and the method further includes: Each data pipeline is configured with a data preprocessing node and a multi-target distribution node.

[0020] The data preprocessing node is used to perform field mapping, type conversion, data masking, and abnormal data filtering. The multi-target distribution node is used to write the incremental data into multiple target databases.

[0021] Data preprocessing nodes can resolve issues such as incompatibility between source and downstream database data formats, user privacy leaks, and abnormal data interfering with analysis. For example, field mapping: the `card_num` field (card number) in the "Card Swiping Records" table in the source database needs to be mapped to `card_id` in the downstream Elasticsearch database (using a unified field naming convention). Type conversion: for example, the `recharge time` field in the source database, which is of type varchar (e.g., "2024-05-20 08:30:00"), is converted to the datetime type in the downstream ClickHouse database. Data anonymization: for example, user mobile phone numbers in "Card Opening Data" are anonymized to prevent user privacy leaks. Abnormal data filtering: for example, filtering abnormal records in "Card Swiping Records" where the amount is negative (e.g., `pay_amt=-10`) or the swiping time is empty.

[0022] Each pipeline can perform data format conversion and rule processing to adapt to different downstream storage target databases.

[0023] The multi-target distribution node can distribute data to different databases, such as ClickHouse for complex statistics and large-scale analysis, Elasticsearch for real-time retrieval and fuzzy queries, and MySQL for analytics libraries used for daily reports and financial settlements.

[0024] Once the data channel is built, initial operation can begin. During initial operation, the first step is to write all the data. Taking bus / subway NFC cards as an example, a full migration of historical swipe data, recharge data, and card activation data is performed first, importing the historical data completely into the downstream database. Then, real-time incremental capture is achieved through MySQL's Binlog log monitoring, ensuring that newly generated data is continuously synchronized.

[0025] In a preferred embodiment, writing historical business data from the source database into the target database includes: The auto-incrementing primary key ID of each data record in the source database is used as the splitting condition to split all historical business data in the source database into multiple batches; the historical business data in each batch is then written into the target database in sequence.

[0026] In this embodiment, an auto-incrementing primary key ID is selected as the splitting condition for data partitioning. The auto-incrementing primary key ID is a special field preset in MySQL business tables (such as the "card swipe record table," "recharge record table," and "card activation information table" in the public transportation industry). It has uniqueness and automatic increment.

[0027] By using an auto-incrementing primary key ID, data can be accurately divided into batches, avoiding a "full table scan" of the MySQL business database at once. Alternatively, a timestamp field can be used as a splitting condition for data partitioning.

[0028] In practical applications, the following SQL statements can be executed in batches to divide all historical business data in the source database into multiple batches, for example: SELECT * FROM table WHERE id>0 AND id<= 1000; SELECT * FROM table WHERE id>1000 AND id<= 2000; SELECT * FROM table WHERE id>2000 AND id<= 3000. The above SQL statement performs a full import of the database in batches of 1000 rows.

[0029] Each batch of exported data is read through a data pipeline (StreamSets JDBC Origin component) and written to the downstream target database. This process is repeated until all data in the source table is synchronized.

[0030] Understandably, this batch full export method based on primary key range can effectively avoid the performance bottleneck caused by a one-time full table scan, ensuring that initialization synchronization can still be completed efficiently even with large amounts of data.

[0031] Incremental data synchronous writing, it should be noted that, when writing the real-time incremental data to the target database, it also includes: The data pipeline is used to subscribe to the Binlog of the source database to obtain incremental change events in real time; the incremental change events are parsed to obtain incremental data, and the format of the incremental data is converted; based on the primary key of the incremental data, it is determined whether the incremental data exists in the target database; if it does not exist, an insertion operation is performed; if it exists, an update operation is performed.

[0032] In practice, firstly, configure Binlog (binary log, ROW format) in the MySQL source database to ensure row-level data changes are captured. Use a data pipeline (StreamSets JDBC Origin component) to subscribe to the MySQL source database's Binlog logs, obtaining incremental change events such as INSERT / UPDATE / DELETE events in real time for card swipe records, recharge records, and card issuance data. After parsing the incremental change events, they are formatted by a preprocessing node. Finally, check if the primary key of the incremental data already exists in the downstream target database; if not, perform an insert operation; otherwise, perform an update operation, ensuring the idempotency of incremental synchronization to complete the writing to multiple downstream target databases.

[0033] During incremental data writing, GTID compensation point management is required. It should be noted that the GTID and corresponding timestamp of the Binlog are periodically recorded, and this GTID and corresponding timestamp are stored as a recovery point, including: In the source database, the GTIDs of the incremental change events that have been completed are periodically recorded and integrated into a GTID set. The GTID set is then bound to the current timestamp and stored as a recovery point in the log table.

[0034] In practical applications, the following statements are executed periodically on the source database: SELECT @@GLOBAL.gtid_executed, @@GLOBAL.gtid_purged, NOW(); @@GLOBAL.gtid_executed: Represents the set of GTIDs of all transactions that have been successfully executed in the MySQL instance (i.e., "transaction identifiers corresponding to synchronized data without omissions"). @@GLOBAL.gtid_purged: This represents the set of GTIDs of transactions that have been cleaned up and deleted from the Binlog in the MySQL instance (i.e., "historical transactions that cannot be recovered, and such GTIDs should be avoided"). NOW(): Records the timestamp of the currently executed SQL.

[0035] The query results (GTID set + timestamp) are stored in the log table to form a one-to-one correspondence between timestamps and GTID sets. This is equivalent to taking a "timed snapshot" of the data synchronization process. If there is an interruption later, the corresponding GTID set can be found in reverse by "time".

[0036] When an incremental data write fails, the timestamp of the failure point is compared with the timestamp of the recovery point stored in the log table. The recovery point preceding the failure point is selected, and the GTID set corresponding to the recovery point is configured into the starting point parameter of the data pipeline so that the data pipeline can re-execute the subsequent incremental data write.

[0037] In practical applications, if an abnormality occurs during incremental data writing and causes an interruption, the recovery point can be accurately found by referring to the log table at the time of the abnormality. For example, if synchronization is interrupted at 14:30 on 2024-05-20, the recovery point is found by looking at the last record in the log table with "timestamp ≤ 14:30".

[0038] Configure the GTID of the recovery point in the starting position parameter of the StreamSets CDC Origin component, and set a new serverId to avoid conflict with the serverId of the task before the interruption, which would cause abnormal Binlog consumption.

[0039] After restarting the task, the Binlog is consumed again from the recovery point to achieve accurate recovery and ensure that compensation can be completed when data synchronization is missing. At the same time, since the incremental operation supports idempotent operation, duplicate consumption can be avoided.

[0040] In practical applications, StreamSets supports automatic system recovery in case of task failure; if automatic recovery fails, manual recovery can be performed using GTID recovery points. Furthermore, the failure of a single data pipeline task will not affect the normal operation of other tasks.

[0041] In another embodiment, a multi-target full and incremental data synchronization system is provided, comprising: The data pipeline construction module is used to build data pipelines for each type of data based on the data type in the source database. The data pipelines for different types of data are independent of each other. The historical data synchronization module is used to write historical business data from the source database to the target database using the data pipeline. The incremental data synchronization module is used to monitor the Binlog of the source database, obtain real-time incremental data, and write the incremental data to the target database using the data pipeline. When writing the incremental data to the target database, the GTID and corresponding timestamp of the Binlog are periodically recorded, and the GTID and corresponding timestamp are stored as a recovery point. When an incremental data writing error occurs, the error time point is compared with the timestamp to select a recovery point, and subsequent incremental data writing is re-executed based on the recovery point.

[0042] The technical solution shown in this invention has the following advantages: Full and incremental data are synchronized in one process: Traditional solutions often separate full migration from incremental synchronization, resulting in complex integration and difficult switching.

[0043] This invention proposes a "full-volume and incremental integration" solution, which efficiently exports historical data in batches; then switches to Binlog log monitoring to capture data changes in real time; achieving seamless connection between full initialization and incremental updates, ensuring data integrity and real-time performance.

[0044] A precise compensation mechanism based on GTID: Existing Binlog-based synchronization methods struggle to accurately pinpoint recovery points after task interruptions, leading to potential omissions. This invention introduces a GTID compensation point mechanism, achieving precise recovery and traceability of the synchronization process, effectively solving the data compensation problem.

[0045] StreamSets Pipeline Orchestration and Task Isolation: Compared to traditional manual writing of synchronous scripts, this invention utilizes StreamSets' visual pipeline functionality, configuring card swiping transactions, recharge transactions, and card opening data as independent synchronous data pipelines; each data pipeline does not interfere with the others, and an anomaly in a single task will not affect other tasks; data in the pipeline can undergo field mapping, format conversion, desensitization, and anomaly filtering, reducing manual intervention and improving stability and security.

[0046] One-time data collection, multi-target distribution: Traditional solutions often require developing separate synchronization tasks for different downstream databases, resulting in redundant data collection and wasted resources. This invention supports multi-target synchronization. After collecting data once, it can be simultaneously distributed to ClickHouse (complex statistical analysis), Elasticsearch (real-time retrieval and query), and MySQL analysis database (daily reports and financial settlements); achieving "one-time collection, multiple uses," significantly improving system efficiency and scalability.

[0047] High availability and financial-grade data protection: Addressing the cash flow characteristics of the public transportation / subway industry, this invention enhances high availability and consistency with automatic retries and breakpoint resumption: synchronization task anomalies can be automatically recovered; manual intervention and GTID recovery: in case of automatic recovery failure, engineers can accurately recover through recovery points; ensuring no data loss and traceability, meeting the data security requirements of financial-grade business scenarios.

[0048] It is understood that the same or similar parts in the above embodiments can be referred to each other, and the contents not described in detail in some embodiments can be referred to the same or similar contents in other embodiments.

[0049] It should be noted that in the description of this invention, the terms "first," "second," etc., are used for descriptive purposes only and should not be construed as indicating or implying relative importance. Furthermore, in the description of this invention, unless otherwise stated, "a plurality of" means at least two.

[0050] Any process or method description in the flowchart or otherwise herein can be understood as representing a module, segment, or portion of code comprising one or more executable instructions for implementing a particular logical function or process, and the scope of the preferred embodiments of the invention includes additional implementations in which functions may be performed not in the order shown or discussed, including substantially simultaneously or in reverse order depending on the functions involved, as will be understood by those skilled in the art to which embodiments of the invention pertain.

[0051] It should be understood that various parts of the present invention can be implemented in hardware, software, firmware, or a combination thereof. In the above embodiments, multiple steps or methods can be implemented in software or firmware stored in memory and executed by a suitable instruction execution system. For example, if implemented in hardware, as in another embodiment, it can be implemented using any one or a combination of the following techniques known in the art: discrete logic circuits having logic gates for implementing logical functions on data signals, application-specific integrated circuits (ASICs) having suitable combinational logic gates, programmable gate arrays (PGAs), field-programmable gate arrays (FPGAs), etc.

[0052] Those skilled in the art will understand that all or part of the steps of the methods in the above embodiments can be implemented by a program instructing related hardware. The program can be stored in a computer-readable storage medium, and when executed, the program includes one or a combination of the steps of the method embodiments.

[0053] Furthermore, the functional units in the various embodiments of the present invention can be integrated into a processing module, or each unit can exist physically separately, or two or more units can be integrated into a module. The integrated module can be implemented in hardware or as a software functional module. If the integrated module is implemented as a software functional module and sold or used as an independent product, it can also be stored in a computer-readable storage medium.

[0054] The storage media mentioned above can be read-only memory, disk, or optical disk, etc.

[0055] In the description of this specification, references to terms such as "one embodiment," "some embodiments," "example," "specific example," or "some examples," etc., indicate that a specific feature, structure, material, or characteristic described in connection with that embodiment or example is included in at least one embodiment or example of the invention. In this specification, the illustrative expressions of the above terms do not necessarily refer to the same embodiment or example. Furthermore, the specific features, structures, materials, or characteristics described may be combined in any suitable manner in one or more embodiments or examples.

[0056] Although embodiments of the present invention have been shown and described above, it is understood that the above embodiments are exemplary and should not be construed as limiting the present invention. Those skilled in the art can make changes, modifications, substitutions and variations to the above embodiments within the scope of the present invention.

Claims

1. A method for synchronizing multi-objective full and incremental data, characterized in that, include: Based on the data types in the source database, data pipelines are built for each type of data, and the data pipelines corresponding to different types of data are independent of each other; Using the data pipeline, historical business data from the source database is written to the target database; Monitor the Binlog of the source database to obtain real-time incremental data, and use the data pipeline to write the incremental data into the target database. When writing the incremental data to the target database, the GTID and corresponding timestamp of the Binlog are recorded periodically, and the GTID and corresponding timestamp are stored as a recovery point. When an incremental data write fails, the abnormal time point is compared with the timestamp to select a recovery point. Based on the recovery point, subsequent incremental data writes are re-executed.

2. The method for synchronizing multi-target full and incremental data according to claim 1, characterized in that, Write historical business data from the source database to the target database, including: The auto-incrementing primary key ID of each data entry in the source database is used as the splitting condition to split all historical business data in the source database into multiple batches. The historical business data from each batch is written to the target database sequentially.

3. The method for synchronizing multi-target full and incremental data according to claim 1, characterized in that, When writing the real-time incremental data to the target database, the process also includes: The data pipeline is used to subscribe to the source database's Binlog to obtain incremental change events in real time. The incremental change event is parsed to obtain incremental data, and the format of the incremental data is converted. Based on the primary key of the incremental data, determine whether the incremental data exists in the target database; If it does not exist, perform an insertion operation; if it exists, perform an update operation.

4. The method for synchronizing multi-target full and incremental data according to claim 3, characterized in that, Periodically record the GTID and corresponding timestamp of the Binlog, and store the GTID and corresponding timestamp as a recovery point, including: In the source database, the GTIDs of the incremental change events that have been completed are periodically recorded and integrated into a GTID set. The GTID set is then bound to the current timestamp and stored as a recovery point in the log table. When an incremental data write fails, the timestamp of the failure point is compared with the timestamp of the recovery point stored in the log table. The recovery point preceding the failure point is selected, and the GTID set corresponding to the recovery point is configured into the starting point parameter of the data pipeline so that the data pipeline can re-execute the subsequent incremental data write.

5. The method for synchronizing multi-target full and incremental data according to claim 1, characterized in that, Based on the data types in the source database, a data pipeline is constructed for each data type, including: Each data pipeline is configured with a data preprocessing node and a multi-target distribution node; The data preprocessing node is used to perform field mapping, type conversion, data masking, and abnormal data filtering; The multi-target distribution node is used to write the incremental data into multiple target databases.

6. A multi-target full and incremental data synchronization system, characterized in that, include: The data pipeline construction module is used to build data pipelines for each type of data based on the data type in the source database. The data pipelines for different types of data are independent of each other. The historical data synchronization module is used to write historical business data from the source database to the target database using the data pipeline. The incremental data synchronization module is used to monitor the Binlog of the source database, obtain real-time incremental data, and write the incremental data to the target database using the data pipeline. When writing the incremental data to the target database, the GTID and corresponding timestamp of the Binlog are periodically recorded, and the GTID and corresponding timestamp are stored as a recovery point. When an incremental data writing error occurs, the error time point is compared with the timestamp to select a recovery point, and subsequent incremental data writing is re-executed based on the recovery point.

Images