Data synchronization method and device for master and slave databases, electronic equipment and storage medium

Abstract

The invention discloses a data synchronization method and device of a master database and a slave database, electronic equipment and a storage medium, and relates to the technical field of big data or other related technical fields, and the method comprises the following steps: monitoring a preset event of the slave database through a trigger; when it is monitored that the preset event exists in the slave database, querying an intermediate table based on the data change record of the slave database; under the condition that the data change record does not exist in the intermediate table, newly adding a to-be-synchronized record in the intermediate table based on the data change record; and reading the intermediate table through a data synchronization tool, and synchronizing the to-be-synchronized data in the slave database to the master database based on a reading result. According to the method and the device, the technical problem of relatively low synchronization efficiency of a data synchronization mode based on a database full-amount scanning and data comparison mode in related technologies is solved.

Description

Technical Field

[0001] This invention relates to the field of big data technology or other related technical fields. Specifically, it relates to a data synchronization method and apparatus, electronic device and storage medium for master-slave databases. Background Technology

[0002] In today's era of rapid information and digital development, large enterprise groups face increasingly complex and massive data management needs, especially in the area of ​​financial management. With the accelerating pace of globalization, enterprises are demanding more centralized data management, risk control, and efficient use of funds. This necessitates an advanced and efficient system for facilitating the integration of data between multiple treasuries within the group, addressing the challenges of data synchronization, processing, and integration.

[0003] In related technologies, data synchronization between master and slave treasurers often involves full database scanning and data comparison, which results in low synchronization efficiency.

[0004] There is currently no effective solution to the above problems. Summary of the Invention

[0005] This invention provides a data synchronization method, apparatus, electronic device, and storage medium for master-slave databases, to at least solve the technical problem of low synchronization efficiency in related technologies that rely on full database scanning and data comparison for data synchronization.

[0006] According to one aspect of the present invention, a data synchronization method for a master-slave database is provided, comprising: listening to a preset event in the slave database via a trigger; querying an intermediate table based on data change records in the slave database when the preset event is detected; adding a record to be synchronized in the intermediate table based on the data change records when the data change record is not found in the intermediate table; reading the intermediate table using a data synchronization tool, and synchronizing the data to be synchronized in the slave database to the master database based on the reading result.

[0007] Furthermore, before listening to preset events in the database via a trigger, the process includes: configuring the trigger for the database; configuring the trigger with change events to be listened to, wherein the change events include at least one of the following: data addition operation, data update operation, and data deletion operation.

[0008] Furthermore, the step of listening to preset events in the database through a trigger includes: listening to the change events in the database through the trigger; and reading the change status of the change events when the change events are detected in the database.

[0009] Furthermore, when a preset event is detected in the slave database, the step of querying the intermediate table based on the data change record of the slave database includes: when the change event is detected in the slave database and the change status of the change event is the final change status, generating the data change record based on the change event in the slave database; and using the data identifier in the data change record as the query key to query the intermediate table.

[0010] Furthermore, after querying the intermediate table based on the data change records from the database, the method further includes: if the intermediate table contains a historical synchronization record corresponding to the data change record, updating the historical synchronization record based on the data change record; and modifying the status field of the historical synchronization record in the intermediate table to a pending synchronization status.

[0011] Furthermore, the step of adding a record to be synchronized in the intermediate table based on the data change record includes: extracting preset field data from the intermediate table based on the record to be synchronized, wherein the preset field data of the intermediate table includes at least one of the following: source table identifier, source table record primary key, status, number of synchronization failures, and record timestamp; and adding the record to be synchronized in the intermediate table based on the preset field data of the intermediate table.

[0012] Furthermore, the step of reading the intermediate table using a data synchronization tool and synchronizing the data to be synchronized from the slave database to the master database based on the reading results includes: scanning the intermediate table using the data synchronization tool to obtain historical synchronization records and records to be synchronized that are in a state of pending synchronization, and obtaining scan results; determining the data to be synchronized in the slave database based on the scan results, and synchronizing the data to be synchronized from the slave database to the master database using the data synchronization tool; and updating the intermediate table based on the synchronization results.

[0013] According to another aspect of the present invention, a data synchronization device for a master-slave database is also provided, comprising: a listening unit for listening to a preset event of the slave database via a trigger; a query unit for querying an intermediate table based on data change records of the slave database when the preset event is detected; an adding unit for adding a record to be synchronized to the intermediate table based on the data change record when the data change record does not exist in the intermediate table; and a synchronization unit for reading the intermediate table using a data synchronization tool and synchronizing the data to be synchronized from the slave database to the master database based on the reading result.

[0014] Furthermore, the master-slave database data synchronization device further includes: a first configuration module for configuring the trigger for the slave database; and a second configuration module for configuring a change event to be monitored for the trigger, wherein the change event includes at least one of the following: data addition operation, data update operation, and data deletion operation.

[0015] Furthermore, the monitoring unit includes: a first monitoring module, used to monitor the change event in the slave database through the trigger; and a first reading module, used to read the change status of the change event when the change event is detected in the slave database.

[0016] Furthermore, the query unit includes: a first generation module, configured to generate the data change record based on the change event in the slave database when the change event is detected and the change status of the change event is the final change status; and a first query module, configured to use the data identifier in the data change record as the query key to query the intermediate table.

[0017] Furthermore, the master-slave database data synchronization device further includes: a first update module, used to update the historical synchronization record based on the data change record when the intermediate table has a historical synchronization record corresponding to the data change record; and a first modification module, used to modify the status field of the historical synchronization record in the intermediate table to a pending synchronization status.

[0018] Furthermore, the newly added unit includes: a first extraction module, used to extract preset field data of an intermediate table based on the record to be synchronized, wherein the preset field data of the intermediate table includes at least one of the following: source table identifier, source table record primary key, status, number of synchronization failures, and record timestamp; and a first adding module, used to add the record to be synchronized to the intermediate table based on the preset field data of the intermediate table.

[0019] Furthermore, the synchronization unit includes: a first scanning module, used to scan the intermediate table using the data synchronization tool to obtain historical synchronization records and records to be synchronized that are in a state of pending synchronization, and to obtain scanning results; a first synchronization module, used to determine the data to be synchronized in the slave database based on the scanning results, and to synchronize the data to be synchronized from the slave database to the master database using the data synchronization tool; and a second update module, used to update the intermediate table based on the synchronization results.

[0020] According to another aspect of the present invention, a computer-readable storage medium is also provided, the computer-readable storage medium including a stored computer program, wherein, when the computer program is executed, it controls the device where the computer-readable storage medium is located to perform any of the above-described master-slave database data synchronization methods.

[0021] According to another aspect of the present invention, an electronic device is also provided, including one or more processors and a memory, the memory being used to store one or more programs, wherein when the one or more programs are executed by the one or more processors, the one or more processors cause the one or more processors to implement any of the above-described master-slave database data synchronization methods.

[0022] According to another aspect of the present invention, a computer program product is also provided, the computer program product comprising a computer program, wherein the computer program, when executed by a processor, implements any of the above-described master-slave database data synchronization methods.

[0023] In this application, the following steps are performed: a trigger is used to listen for preset events in the slave database; when a preset event is detected in the slave database, an intermediate table is queried based on the data change records in the slave database; if no data change records are found in the intermediate table, a new record to be synchronized is added to the intermediate table based on the data change records; the intermediate table is read using a data synchronization tool; and the data to be synchronized from the slave database is synchronized to the master database based on the read results.

[0024] In this application, a trigger is configured for the slave database. When a preset event is detected, data synchronization is automatically triggered. An intermediate table is used to store data change records of the slave database. The data synchronization tool determines the data to be synchronized by scanning the intermediate table and synchronizes the data from the slave database to the master database. This eliminates the need for a full scan and comparison of the data tables, greatly improving data synchronization efficiency. This solves the technical problem of low synchronization efficiency in related technologies that rely on full database scans and data comparisons for data synchronization. Attached Figure Description

[0025] The accompanying drawings, which are included to provide a further understanding of the invention and form part of this application, illustrate exemplary embodiments of the invention and, together with their description, serve to explain the invention and do not constitute an undue limitation thereof. In the drawings:

[0026] Figure 1 A hardware structure block diagram of a computer terminal (or mobile device) for implementing a data synchronization method for a master-slave database is shown.

[0027] Figure 2This is a flowchart of an optional master-slave database data synchronization method according to an embodiment of the present invention;

[0028] Figure 3 This is a schematic diagram of an optional master-slave database data synchronization process according to an embodiment of the present invention;

[0029] Figure 4 This is a schematic diagram of an optional master-slave database data synchronization device according to an embodiment of the present invention;

[0030] Figure 5 This is a hardware structure block diagram of an electronic device (or mobile device) for executing a master-slave database data synchronization method according to an embodiment of the present invention. Detailed Implementation

[0031] To enable those skilled in the art to better understand the present invention, the technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings of the embodiments of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of the present invention.

[0032] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of the invention described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover a non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0033] To facilitate understanding of the present invention by those skilled in the art, some terms or nouns involved in the various embodiments of the present invention are explained below:

[0034] ETL tools, short for Extract, Transform, Load, are data warehouse management tools that provide rich data processing functions. Extract is for extracting data, Transform is for transforming data, and Load is for loading data.

[0035] It should be noted that the master-slave database data synchronization method and apparatus in this application can be used in the field of big data technology for data synchronization of master-slave databases, and can also be used in any field other than big data technology for data synchronization of master-slave databases. This application does not limit the application field of the master-slave database data synchronization method and apparatus.

[0036] It should be noted that the information collected in this application (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for display, data used for analysis, etc.) are information and data authorized by the user or fully authorized by all parties. Furthermore, the collection, storage, use, processing, transmission, provision, disclosure, and application of this data all comply with relevant laws, regulations, and standards, necessary confidentiality measures have been taken, and they do not violate public order and good morals. Corresponding access points are provided for users to choose to authorize or refuse. For example, interfaces are set up between this system and relevant users or organizations, providing users with corresponding access points to choose to agree to or refuse automated decision-making results; if the user chooses to refuse, the process proceeds to the expert decision-making stage.

[0037] The following embodiments of the present invention can be applied to various master-slave database data synchronization systems / applications / devices. The present invention employs table triggers to monitor the final state of business data tables, enabling real-time identification of data changes, particularly triggering the synchronization process when data reaches its final state (i.e., a state where it no longer changes). This mechanism avoids the data latency that may exist in traditional fixed-frequency data synchronization, ensuring the real-time performance and accuracy of data synchronization. Simultaneously, the introduction of an intermediate table makes the data synchronization process stateful, allowing for precise tracking of the synchronization status of each data entry, reducing duplicate and missed synchronizations, and improving the efficiency and reliability of data synchronization between master and slave databases.

[0038] The present invention will now be described in detail with reference to various embodiments.

[0039] Example 1

[0040] According to an embodiment of the present invention, an embodiment of a data synchronization method for a master-slave database is provided. It should be noted that the steps shown in the flowchart in the accompanying drawings can be executed in a computer system such as a set of computer-executable instructions. Furthermore, although a logical order is shown in the flowchart, in some cases, the steps shown or described may be executed in a different order than that shown here.

[0041] The method embodiment provided in Embodiment 1 of this application can be executed on a mobile terminal, computer terminal, or similar computing device. Figure 1 A hardware block diagram of a computer terminal (or mobile device) for implementing a master-slave database data synchronization method is shown. Figure 1As shown, the computer terminal 10 (or mobile device) may include one or more processors 102 (shown as 102a, 102b, ..., 102n in the figure) 102 (processor 102 may include, but is not limited to, a microprocessor MCU or a programmable logic device FPGA, etc.), a memory 104 for storing data, and a transmission device 106 for communication functions. In addition, it may also include: a display, an input / output interface (I / O interface), a universal serial bus (USB) port (which may be included as one of the ports of a BUS bus), a network interface, a power supply, and / or a camera. Those skilled in the art will understand that... Figure 1 The structure shown is for illustrative purposes only and does not limit the structure of the aforementioned electronic device. For example, computer terminal 10 may also include... Figure 1 The more or fewer components shown, or having the same Figure 1 The different configurations shown.

[0042] It should be noted that the aforementioned one or more processors 102 and / or other data processing circuits are generally referred to herein as "data processing circuits". These data processing circuits may be embodied, in whole or in part, in software, hardware, firmware, or any other combination thereof. Furthermore, the data processing circuits may be a single, independent processing module, or may be integrated, in whole or in part, into any other element within the computer terminal 10 (or mobile device). As involved in the embodiments of this application, the data processing circuits serve as a processor control mechanism (e.g., selection of a variable resistor termination path connected to an interface).

[0043] The memory 104 can be used to store software programs and modules of application software, such as the program instructions / data storage device corresponding to the master-slave database data synchronization method in this embodiment. The processor 102 executes various functional applications and data processing by running the software programs and modules stored in the memory 104, thereby realizing the aforementioned master-slave database data synchronization method. The memory 104 may include high-speed random access memory, and may also include non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some instances, the memory 104 may further include memory remotely located relative to the processor 102, and these remote memories can be connected to the computer terminal 10 via a network. Examples of such networks include, but are not limited to, the Internet, corporate intranets, local area networks, mobile communication networks, and combinations thereof.

[0044] The transmission device 106 is used to receive or send data via a network. Specific examples of the network described above may include a wireless network provided by the communication provider of the computer terminal 10. In one example, the transmission device 106 includes a Network Interface Controller (NIC), which can connect to other network devices via a base station to communicate with the Internet. In another example, the transmission device 106 may be a Radio Frequency (RF) module, used for wireless communication with the Internet.

[0045] The display may be, for example, a touchscreen liquid crystal display (LCD) that allows the user to interact with the user interface of the computer terminal 10 (or mobile device).

[0046] Under the aforementioned operating environment, this application provides the following: Figure 2 The master-slave database data synchronization method shown is implemented by the master-slave database data synchronization system.

[0047] Figure 2 This is a flowchart of an optional master-slave database data synchronization method according to an embodiment of the present invention, such as... Figure 2 As shown, the method includes the following steps:

[0048] Furthermore, before listening to preset events of the slave database via triggers, the process includes: configuring triggers for the slave database; configuring change events to be listened to for the triggers, wherein the change events include at least one of the following: data addition operation, data update operation, and data deletion operation.

[0049] To achieve more effective real-time data synchronization, especially for data integration in master-slave databases and master-sub-treasury systems, this invention proposes an improved method based on a trigger mechanism. By pre-configuring specific triggers in the slave database and finely setting the trigger's listening events, a highly sensitive capture of data changes is achieved, thereby significantly improving data synchronization efficiency.

[0050] Specifically, firstly, a dedicated trigger needs to be created in the slave database (i.e., the database used by the sub-treasurer). The purpose of this trigger is to continuously monitor the various tables within the slave database and react to any data changes that occur. As a database object, a trigger can respond to data insertion, update, or deletion operations on a specified table, automatically executing predefined operations to ensure the integrity of the database data.

[0051] Secondly, specify the specific listener events for the trigger. These change events mainly include, but are not limited to, data insertion, data update, and data deletion operations. These events cover the basic scenarios in which data in the database table changes. By configuring listener events, the trigger can respond immediately when the corresponding data changes occur, such as recording change information or marking the changed data as pending synchronization.

[0052] Step S201: Listen for preset events from the database using triggers.

[0053] In this embodiment of the invention, the trigger is designed as a database object capable of automatically responding to specific database events, such as INSERT, UPDATE, or DELETE operations. When a preset event occurs from the database, the trigger is activated, executing predefined logic or operations. This mechanism ensures the real-time nature and high efficiency of data synchronization; the preset event refers to a specific change event in its final state.

[0054] By using a trigger-based monitoring mechanism, data changes can be responded to immediately, rather than relying on periodic batch checks, which greatly improves the real-time performance of data synchronization.

[0055] Furthermore, the steps of listening to preset events in the database through triggers include: listening to change events in the database through triggers; and reading the change status of the change events when a change event is detected in the database.

[0056] Specifically, in the slave database (i.e., the sub-treasury database), listener triggers are configured for the data tables that need to be monitored. These triggers are designed to respond to specific database change events, such as INSERT, UPDATE, and DELETE operations. The triggers monitor the data tables in real time. Upon detecting one of these change events, the change status is read. If the status is not final, no response is needed. If the status is final, meaning the change operation in the data table has been completed, a response is triggered.

[0057] Step S202: If a preset event is detected in the database, query the intermediate table based on the data change records in the database.

[0058] In step S202 above, when a change event, such as data insertion, update, or deletion, occurs in the business data table of the database (usually the subsidiary's database) and is in its final state, the data synchronization process is triggered. First, an intermediate table needs to be queried based on the database's change records. This intermediate table stores records of all data to be synchronized, including but not limited to data ID, source table name, change status, and number of synchronization failures. The purpose of the query is to confirm whether the changed data already exists in the intermediate table and its current synchronization status.

[0059] The introduction of intermediate tables enables data synchronization to be stateful, allowing for precise tracking of the status of each piece of data, avoiding duplicate synchronization and omissions, and improving the accuracy and efficiency of data synchronization.

[0060] Furthermore, when a preset event is detected in the database, the step of querying the intermediate table based on the data change record in the database includes: when a change event is detected in the database and the change event is in the final state, generating a data change record based on the change event in the database; and using the data identifier in the data change record as the query key to query the intermediate table.

[0061] Specifically, when a data change event occurs in a business data table in the database (i.e., the sub-treasury database), the configured trigger (a database mechanism) responds immediately. However, the response is not for all change events, but specifically for those events where the change state has reached a final state. A final state refers to a situation where, after a series of processes, no further modifications are expected; this is usually the trigger point for data synchronization. Once a change event is confirmed to have reached a final state, the system will generate a detailed data change record based on this event. This record includes, but is not limited to, the changed data ID, change type (insert, update, or delete), source table name, and other key information. This provides an accurate data source for subsequent data synchronization.

[0062] After a data change record is generated, the system uses the data identifier in the record as the query key to query the intermediate table (data synchronization register). The intermediate table stores registration information for all data to be synchronized, including data ID, status, and number of synchronization failures. The purpose of the query is to confirm whether the data change record already exists in the intermediate table, as well as its current status and synchronization history.

[0063] Furthermore, after querying the intermediate table based on the data change records from the database, the process also includes: if there are historical synchronization records corresponding to the data change records in the intermediate table, updating the historical synchronization records based on the data change records; and modifying the status field of the historical synchronization records in the intermediate table to a pending synchronization status.

[0064] Specifically, when the trigger identifies that data changes in the database have reached their final state, the system uses the data identifier (usually a data ID) in the data change record as the query key to search for a matching historical synchronization record in the intermediate table (the data synchronization register). This query step allows the system to locate the correct data row, thereby accurately managing the data synchronization status. If the query results show that a corresponding historical synchronization record exists in the intermediate table, the system will update the historical synchronization record based on the new data change record. The updates may include the latest data version information, the change type (e.g., whether it was added, updated, or deleted), and possible state resets. The purpose of this step is to maintain the consistency and up-to-dateness of the data in the intermediate table, ensuring that the data synchronization tool uses the most accurate and up-to-date data changes when performing data synchronization.

[0065] During the update process of historical synchronization records, the system modifies the status field of the historical records in the intermediate table from any previous status (which could be synchronization successful, synchronization failed, or other statuses) to a pending synchronization status. This reset action, especially for records that may have been marked as synchronization failed, allows the system to retry data synchronization until the data is successfully synchronized to the master treasury or the maximum number of retries is reached. The purpose of this is to improve the success rate of data synchronization, reduce permanent data synchronization failures, and also facilitate the tracking and management of data synchronization progress.

[0066] Step S203: If there are no data change records in the intermediate table, add a record to be synchronized in the intermediate table based on the data change records.

[0067] In step S203 above, if there are no data change records in the intermediate table, it means that the data change has not yet been registered as a task to be synchronized. At this time, the system will add a new record to be synchronized in the intermediate table based on the data change record. The new record will include all the key information in the data change record, such as data ID, source table name, change type, and initial status set to pending synchronization. In addition, other fields of the intermediate table, such as the number of failures and timestamps, will also be initialized for reference by the subsequent data synchronization tool during data synchronization.

[0068] Furthermore, the step of adding a record to be synchronized in the intermediate table based on the data change record includes: extracting preset field data from the intermediate table based on the record to be synchronized, wherein the preset field data of the intermediate table includes at least one of the following: source table identifier, source table record primary key, status, number of synchronization failures, and record timestamp; and adding a record to be synchronized in the intermediate table based on the preset field data of the intermediate table.

[0069] Specifically, when a new record to be synchronized is added to the intermediate table, the system extracts a series of preset fields based on the data change record. These preset fields include, but are not limited to: source table identifier, indicating which specific data table the data change comes from; source table record primary key, used to uniquely identify the data entry in the data change record; status, initially set to "pending synchronization," indicating that the data change record needs to be synchronized; synchronization failure count, initialized to 0, used to track the number of failures that may be encountered during subsequent data synchronization; and record timestamp, recording the time when the data change record was created in the intermediate table.

[0070] The system creates a new record to be synchronized in the intermediate table based on the extracted preset field data. This record will contain all the extracted information, and its physical primary key (auto-incrementing column) and logical primary key (source table identifier + source table record primary key) will ensure the record's uniqueness and traceability. The purpose of creating this record is to accurately identify and locate the data changes that need to be synchronized in the subsequent data synchronization process, while also recording and managing their synchronization status.

[0071] The creation of records to be synchronized ensures the orderly progress of data synchronization, improves the efficiency and accuracy of data synchronization, reduces the retry cost of synchronization failures, and enhances the overall reliability and performance of the master-subsidiary data connection.

[0072] Step S204: Read the intermediate table using the data synchronization tool, and synchronize the data to be synchronized from the database to the main database based on the reading results.

[0073] In step S204 above, a data synchronization task is initiated periodically or on demand through a pre-configured distributed task scheduling system. The goal of this task is to read records from the intermediate table and identify data change records in the pending synchronization state. The relevant task is executed, using an ETL data synchronization tool to connect to the intermediate table and perform a query operation to filter out all records in the pending synchronization state with a synchronization failure count less than a preset threshold (e.g., 3 times). This process ensures that the tool will only process data changes that are ready and have not reached the upper limit of the number of attempts, thereby optimizing resource usage and synchronization efficiency. For each filtered pending synchronization record, the data synchronization tool needs to retrieve the data to be synchronized from the source table in the secondary database and synchronize it to the target table in the primary database.

[0074] Furthermore, the steps of reading the intermediate table using a data synchronization tool and synchronizing the data to be synchronized from the secondary database to the primary database based on the reading results include: scanning the intermediate table using a data synchronization tool to obtain historical synchronization records and records to be synchronized that are in a state of pending synchronization, and obtaining the scan results; determining the data to be synchronized from the secondary database based on the scan results, and synchronizing the data to be synchronized from the secondary database to the primary database using a data synchronization tool; and updating the intermediate table based on the synchronization results.

[0075] Specifically, after the data synchronization tool starts, its first operation is to scan the intermediate table (the data synchronization register). This process aims to find all historical synchronization records and records to be synchronized that are marked as pending synchronization (Status=1). The scan is based on the declared index "source table name + status + cumulative failure count" to improve query efficiency and quickly locate the target record. After the scan is complete, a scan result set containing the records to be synchronized is obtained. These records typically contain key information such as the source table name, data ID, status, and number of failures, providing clear instructions and data source locations for subsequent data synchronization. Using the scan results, the data synchronization tool can determine which data needs to be synchronized from the slave database to the master database. By referencing the source table name and data ID, the tool will accurately extract data in the final state, ensuring the accuracy of synchronization.

[0076] Finally, the data synchronization tool will begin retrieving this data from the secondary database and, based on the preset field mapping relationships, synchronize the data to the primary database. After synchronization is complete, regardless of success or failure, the data synchronization tool will update the records in the intermediate tables according to the synchronization results. If synchronization is successful, the corresponding records will be deleted, indicating that the data synchronization task is complete; if synchronization fails, the record's status will be updated to synchronization failed (Status=2), and the cumulative number of failures will increment.

[0077] The intelligent scanning of intermediate tables and the precise synchronization based on the scan results by the data synchronization tool greatly improve the efficiency and reliability of data synchronization.

[0078] Furthermore, the intermediate table also includes a data synchronization priority field, used to set the data synchronization priority based on the timeliness and importance of the data. Specifically, before data synchronization, the records to be synchronized in the intermediate table are sorted according to the data synchronization priority field; the distributed task scheduling system processes the high-priority data to be synchronized first according to the sorting results, realizing the priority synchronization of key data, thereby improving the system's responsiveness and the real-time performance of fund management.

[0079] Furthermore, this invention proposes an automatic data synchronization conflict resolution mechanism to automatically repair data when a data synchronization conflict is found in the target table of the master database. The conflict resolution mechanism automatically selects the latest version of the data for synchronization by comparing the version information of the same data identifier in the source table of the slave database and the target table of the master database, thereby avoiding synchronization failure caused by inconsistent data versions and ensuring data consistency and accuracy.

[0080] Through the above steps, a trigger is used to listen for preset events in the slave database. When a preset event is detected in the slave database, the intermediate table is queried based on the data change records in the slave database. If no data change records are found in the intermediate table, a new record to be synchronized is added to the intermediate table based on the data change records. The intermediate table is read by the data synchronization tool, and the data to be synchronized from the slave database is synchronized to the master database based on the read results.

[0081] In this embodiment, a trigger is configured from the database to automatically trigger data synchronization when a preset event is detected. An intermediate table is used to store data change records from the database. The data synchronization tool determines the data to be synchronized by scanning the intermediate table and synchronizes the data from the database to the master database. This eliminates the need for a full scan and comparison of the data tables, greatly improving data synchronization efficiency. This solves the technical problem of low synchronization efficiency in related technologies that rely on full database scans and data comparisons for data synchronization.

[0082] The following describes in detail another optional implementation method.

[0083] Figure 3 This is a schematic diagram of an optional master-slave database data synchronization process according to an embodiment of the present invention, such as... Figure 3 As shown, the data synchronization process of the master-slave database includes:

[0084] Step 1, Begin;

[0085] Step 2: Create a trigger on the source table and listen for preset events;

[0086] Create a trigger on the source table (the table that needs data synchronization) to listen for INSERT / UPDATE / DELETE operations and trigger the logic after the data is committed:

[0087] Determine if the currently modified record is in a final state. If it is not in a final state, no operation is required and the process ends directly. If it is in a final state, first query the intermediate table for a corresponding record based on the source table record ID and status information. If a record is found, update it to the latest record to be synchronized. If no record is found, add a new record to be synchronized.

[0088] Step 3: Upon detecting a preset event, register it in the intermediate table;

[0089] Intermediate table design: Fields include auto-increment column, source table name, source table record primary key, status (1-to be synchronized, 2-synchronization failed), cumulative number of failures, registration timestamp, and last modification timestamp.

[0090] Physical primary key: auto-incrementing column.

[0091] Logical primary key: source table name + source table record ID.

[0092] Step 4: Call the data synchronization tool;

[0093] The newly built distributed task scheduling system uses a timed task to trigger an ETL data synchronization tool for data synchronization. The ETL data synchronization tool 2) scans the intermediate tables to determine the data to be synchronized.

[0094] Step 5: Read data from the source table;

[0095] Step 6: Synchronize the data to the target table;

[0096] Configure the field mapping relationship between the source table and the target table, and then synchronize the data.

[0097] Step 7: Update the intermediate table;

[0098] Based on the synchronization results, if the synchronization is successful, the record in the intermediate table is deleted; if the synchronization fails, the synchronization status of the intermediate table is updated to "synchronization failed," and the number of synchronization failures is recorded.

[0099] Step eight, end.

[0100] This invention employs table triggers to monitor the final state of business data tables, enabling real-time identification of data changes. Specifically, when data reaches its final state (i.e., a state where it no longer changes), the synchronization process is triggered. This mechanism avoids the data latency that may exist in traditional fixed-frequency data synchronization, ensuring the real-time nature and accuracy of data synchronization. Simultaneously, the introduction of an intermediate table makes the data synchronization process stateful, allowing for precise tracking of the synchronization status of each data entry, reducing duplicate and missed synchronizations, and improving the efficiency and reliability of data synchronization between master and slave databases.

[0101] The following is a detailed description with reference to another embodiment.

[0102] Example 2

[0103] The master-slave database data synchronization device provided in this embodiment includes multiple implementation units, each of which corresponds to a specific implementation step in the above embodiment one. The specific implementation method and beneficial effects can be referred to the foregoing method embodiment, and will not be repeated here.

[0104] Figure 4 This is a schematic diagram of an optional master-slave database data synchronization device according to an embodiment of the present invention, such as... Figure 4 As shown, the data synchronization device for the master-slave database may include: a monitoring unit 41, a query unit 42, an add unit 43, and a synchronization unit 44, wherein...

[0105] Listening unit 41 is used to listen for preset events from the database via triggers;

[0106] Query unit 42 is used to query the intermediate table based on the data change records of the database when a preset event is detected in the database.

[0107] A new unit 43 is added, which is used to add a record to be synchronized in the intermediate table based on the data change record when there is no data change record in the intermediate table.

[0108] Synchronization unit 44 is used to read intermediate tables through data synchronization tools and synchronize the data to be synchronized from the database to the main database based on the read results.

[0109] In the aforementioned master-slave database data synchronization device, the monitoring unit 41 monitors preset events in the slave database via triggers; the query unit 42 queries the intermediate table based on the data change records in the slave database when a preset event is detected in the slave database; the addition unit 43 adds a record to be synchronized in the intermediate table based on the data change records when no data change record exists in the intermediate table; and the synchronization unit 44 reads the intermediate table using a data synchronization tool and synchronizes the data to be synchronized from the slave database to the master database based on the reading results.

[0110] In this embodiment, a trigger is configured from the database to automatically trigger data synchronization when a preset event is detected. An intermediate table is used to store data change records from the database. The data synchronization tool determines the data to be synchronized by scanning the intermediate table and synchronizes the data from the database to the master database. This eliminates the need for a full scan and comparison of the data tables, greatly improving data synchronization efficiency. This solves the technical problem of low synchronization efficiency in related technologies that rely on full database scans and data comparisons for data synchronization.

[0111] Furthermore, the data synchronization device for the master-slave database also includes: a first configuration module for configuring triggers for the slave database; and a second configuration module for configuring change events to be monitored for the triggers, wherein the change events include at least one of the following: data addition operation, data update operation, and data deletion operation.

[0112] Furthermore, the listening unit includes: a first listening module, used to listen for change events in the database via a trigger; and a first reading module, used to read the change status of the change event when a change event is detected in the database.

[0113] Furthermore, the query unit includes: a first generation module, used to generate a data change record based on the change event in the database when a change event is detected in the database and the change event is in the final state of the change; and a first query module, used to query the intermediate table using the data identifier in the data change record as the query key.

[0114] Furthermore, the master-slave database data synchronization device also includes: a first update module, used to update the historical synchronization record based on the data change record when there is a historical synchronization record corresponding to the data change record in the intermediate table; and a first modification module, used to modify the status field of the historical synchronization record in the intermediate table to the pending synchronization status.

[0115] Furthermore, the newly added unit includes: a first extraction module, used to extract preset field data of the intermediate table based on the record to be synchronized, wherein the preset field data of the intermediate table includes at least one of the following: source table identifier, source table record primary key, status, number of synchronization failures, and record timestamp; and a first addition module, used to add a record to be synchronized in the intermediate table based on the preset field data of the intermediate table.

[0116] Furthermore, the synchronization unit includes: a first scanning module, used to scan the intermediate table using a data synchronization tool to obtain historical synchronization records and records to be synchronized that are in a state of pending synchronization, and to obtain the scanning results; a first synchronization module, used to determine the data to be synchronized from the slave database based on the scanning results, and to synchronize the data to be synchronized from the slave database to the master database using a data synchronization tool; and a second update module, used to update the intermediate table based on the synchronization results.

[0117] It should be noted that the aforementioned monitoring unit 41, query unit 42, addition unit 43, and synchronization unit 44 correspond to steps S201 to S204 in Embodiment 1. The instances and application scenarios implemented by the aforementioned units and their corresponding steps are the same, but are not limited to the content disclosed in Embodiment 1. It should be noted that the aforementioned modules or units may be hardware or software components stored in memory (e.g., memory 104) and processed by one or more processors (e.g., processors 102a, 102b, ..., 102n). The aforementioned modules or units may also be part of a device and can run in the computer terminal 10 provided in Embodiment 1.

[0118] The invention will now be described in conjunction with another alternative embodiment.

[0119] Example 3

[0120] The present invention can also provide an electronic device. Figure 5 This is a hardware structure block diagram of an electronic device (or mobile device) for executing an optional master-slave database data synchronization method according to an embodiment of the present invention, such as... Figure 5 As shown, the electronic device may include: one or more ( Figure 5 (Only one is shown) processor 502, memory 504, memory controller, and peripheral interface, wherein the peripheral interface is connected to the radio frequency module, audio module and display.

[0121] The memory can be used to store software programs and modules, such as the program instructions / modules corresponding to the methods and apparatus in the embodiments of this application. The processor executes various functional applications and data processing by running the software programs and modules stored in the memory, thereby implementing the above-described methods. The memory may include high-speed random access memory, and may also include non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some instances, the memory may further include memory remotely located relative to the processor, and these remote memories can be connected to the terminal via a network. Examples of such networks include, but are not limited to, the Internet, corporate intranets, local area networks, mobile communication networks, and combinations thereof.

[0122] The processor can access information and applications stored in memory via a transmission device to perform the following steps: listen for preset events in the database using triggers; if a preset event is detected in the database, query an intermediate table based on the data change records in the database; if no data change records are found in the intermediate table, add a record to be synchronized in the intermediate table based on the data change records; read the intermediate table using a data synchronization tool, and synchronize the data to be synchronized from the database to the main database based on the read results.

[0123] The processor can invoke information and applications stored in memory via a transmission device to perform the following steps: configure a trigger for the database; configure a change event to be listened to for the trigger, wherein the change event includes at least one of the following: data addition operation, data update operation, and data deletion operation.

[0124] The processor can access information and applications stored in memory via a transmission device to perform the following steps: listen for change events from the database using a trigger; and read the change status of the change event if a change event is detected in the database.

[0125] The processor can invoke information and applications stored in memory through the transmission device to perform the following steps: when a change event is detected in the database and the change event is in the final state, a data change record is generated based on the change event in the database; the data identifier in the data change record is used as the query key to query the intermediate table.

[0126] The processor can access the information and application stored in the memory via the transmission device to perform the following steps: if there is a historical synchronization record corresponding to the data change record in the intermediate table, update the historical synchronization record based on the data change record; modify the status field of the historical synchronization record in the intermediate table to the pending synchronization status.

[0127] The processor can access information and applications stored in the memory via the transmission device to perform the following steps: extracting preset field data from the intermediate table based on the record to be synchronized, wherein the preset field data of the intermediate table includes at least one of the following: source table identifier, source table record primary key, status, number of synchronization failures, and record timestamp; adding a record to be synchronized in the intermediate table based on the preset field data of the intermediate table.

[0128] The processor can access information and applications stored in memory via a transmission device to perform the following steps: scan the intermediate table using a data synchronization tool to obtain historical synchronization records and records to be synchronized that are in a state of pending synchronization, and obtain the scan results; determine the data to be synchronized from the database based on the scan results, and use the data synchronization tool to synchronize the data to be synchronized from the secondary database to the primary database; update the intermediate table based on the synchronization results.

[0129] This invention provides a master-slave database data synchronization scheme. A trigger is configured for the slave database, automatically initiating data synchronization upon detecting a preset event. An intermediate table stores data change records from the slave database. The data synchronization tool scans the intermediate table to determine the data to be synchronized and synchronizes it from the slave database to the master database. This eliminates the need for a full scan and comparison of all data tables, significantly improving data synchronization efficiency. This solves the problem of low synchronization efficiency in related technologies that rely on full database scans and data comparisons for data synchronization.

[0130] Those skilled in the art will understand that Figure 5 The structure shown is for illustrative purposes only. Electronic devices can also be smartphones, tablets, handheld computers, mobile internet devices (MIDs), PADs, and other terminal devices. Figure 5 This does not limit the structure of the aforementioned electronic device. For example, electronic devices may also include components that are more... Figure 5The more or fewer components shown (such as network interfaces, display devices, etc.), or having the same Figure 5 The different configurations shown.

[0131] Those skilled in the art will understand that all or part of the steps in the various methods of the above embodiments can be implemented by a program instructing the hardware related to the terminal device. The program can be stored in a computer-readable storage medium, which may include: flash drive, read-only memory (ROM), random access memory (RAM), disk or optical disk, etc.

[0132] The invention will now be described in conjunction with another alternative embodiment.

[0133] Example 4

[0134] This invention also provides a computer-readable storage medium. Optionally, in this invention, the computer-readable storage medium can be used to store the program code executed by the master-slave database data synchronization method provided in Embodiment 1.

[0135] Optionally, in this embodiment of the invention, the storage medium may be located in any computer terminal in a group of computer terminals in a computer network, or in any mobile terminal in a group of mobile terminals.

[0136] This invention also provides a computer program product, which, when executed on a data processing device, is suitable for performing the steps of a master-slave database data synchronization method: listening to preset events in the slave database through triggers; querying an intermediate table based on data change records in the slave database when a preset event is detected; adding a record to be synchronized in the intermediate table based on the data change records when no data change record exists in the intermediate table; reading the intermediate table through a data synchronization tool, and synchronizing the data to be synchronized from the slave database to the master database based on the reading results.

[0137] This invention also provides a computer program product, which, when executed on a data processing device, is suitable for performing the steps of a master-slave database data synchronization method: configuring a trigger for the slave database; configuring a change event to be monitored for the trigger, wherein the change event includes at least one of the following: data addition operation, data update operation, and data deletion operation.

[0138] This invention also provides a computer program product, which, when executed on a data processing device, is suitable for performing the steps of a master-slave database data synchronization method: listening for change events in the slave database through triggers; and reading the change status of the change events when a change event is detected in the slave database.

[0139] This invention also provides a computer program product, which, when executed on a data processing device, is suitable for executing the steps of a master-slave database data synchronization method: when a change event is detected in the slave database and the change event is in the final state, a data change record is generated based on the change event in the slave database; and the data identifier in the data change record is used as the query key to query an intermediate table.

[0140] This invention also provides a computer program product, which, when executed on a data processing device, is suitable for performing the steps of a master-slave database data synchronization method: if a historical synchronization record corresponding to a data change record exists in an intermediate table, the historical synchronization record is updated based on the data change record; the status field of the historical synchronization record in the intermediate table is modified to a pending synchronization status.

[0141] This invention also provides a computer program product, which, when executed on a data processing device, is suitable for performing the steps of a master-slave database data synchronization method: extracting preset field data from an intermediate table based on the record to be synchronized, wherein the preset field data of the intermediate table includes at least one of the following: source table identifier, source table record primary key, status, number of synchronization failures, and record timestamp; and adding a record to be synchronized to the intermediate table based on the preset field data of the intermediate table.

[0142] This invention also provides a computer program product, which, when executed on a data processing device, is suitable for performing the steps of a master-slave database data synchronization method: using a data synchronization tool to scan an intermediate table, obtaining historical synchronization records and records to be synchronized that are in a state of pending synchronization, and obtaining scan results; determining the data to be synchronized in the slave database based on the scan results, and using the data synchronization tool to synchronize the data to be synchronized from the slave database to the master database; and updating the intermediate table based on the synchronization results.

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

[0144] In the above embodiments of the present invention, the descriptions of each embodiment have different focuses. For parts not described in detail in a certain embodiment, please refer to the relevant descriptions of other embodiments.

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

[0146] 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 units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.

[0147] Furthermore, the functional units in the various embodiments of the present invention can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or as a software functional unit.

[0148] 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, the technical solution of the present invention, in essence, or the part that contributes to the prior art, or all or part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of the present invention. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, read-only memory (ROM), random access memory (RAM), portable hard drives, magnetic disks, or optical disks.

[0149] The above description is only a preferred embodiment of the present invention. It should be noted that for those skilled in the art, several improvements and modifications can be made without departing from the principle of the present invention, and these improvements and modifications should also be considered within the scope of protection of the present invention.

Claims

1. A data synchronization method for a master-slave database, characterized in that, include: Listen for preset events from the database using triggers; If a preset event is detected in the slave database, the intermediate table is queried based on the data change records in the slave database. If the data change record does not exist in the intermediate table, a new record to be synchronized is added to the intermediate table based on the data change record. The intermediate table is read using a data synchronization tool, and the data to be synchronized from the secondary database is synchronized to the primary database based on the read results.

2. The method according to claim 1, characterized in that, Before listening for preset events from the database via triggers, the following should be included: Configure the trigger for the database; Configure the trigger with change events to be monitored, wherein the change events include at least one of the following: data addition operation, data update operation, and data deletion operation.

3. The method according to claim 2, characterized in that, The steps for listening to preset events from the database using triggers include: The trigger listens for the change events in the database. If the change event is detected in the database, the change status of the change event is read.

4. The method according to claim 3, characterized in that, When a preset event is detected in the slave database, the step of querying the intermediate table based on the data change records in the slave database includes: If the change event is detected in the slave database and the change event is in the final state, the data change record is generated based on the change event in the slave database. Use the data identifier in the data change record as the query key to query the intermediate table.

5. The method according to claim 1, characterized in that, After querying the intermediate table based on the data change records from the database, the process also includes: If a historical synchronization record corresponding to the data change record exists in the intermediate table, the historical synchronization record is updated based on the data change record. Modify the status field of the historical synchronization record in the intermediate table to "pending synchronization".

6. The method according to claim 1, characterized in that, The steps for adding a record to be synchronized to the intermediate table based on the data change record include: Based on the record to be synchronized, extract the preset field data of the intermediate table, wherein the preset field data of the intermediate table includes at least one of the following: source table identifier, source table record primary key, status, number of synchronization failures, and record timestamp; The record to be synchronized is added to the intermediate table based on the preset field data of the intermediate table.

7. The method according to claim 5, characterized in that, The steps of reading the intermediate table using a data synchronization tool and synchronizing the data to be synchronized from the database to the main database based on the reading results include: The data synchronization tool is used to scan the intermediate table to obtain historical synchronization records and records to be synchronized that are in a state of waiting to be synchronized, and the scan results are obtained. Based on the scan results, the data to be synchronized in the slave database is determined, and the data synchronization tool is used to synchronize the data to be synchronized from the slave database to the master database; The intermediate table is updated based on the synchronization results.

8. A data synchronization device for a master-slave database, characterized in that, include: The listening unit is used to listen for preset events from the database via triggers; The query unit is used to query the intermediate table based on the data change records of the slave database when a preset event is detected in the slave database. A new unit is used to add a record to be synchronized in the intermediate table based on the data change record if the data change record does not exist in the intermediate table. The synchronization unit is used to read the intermediate table through a data synchronization tool and synchronize the data to be synchronized from the secondary database to the primary database based on the reading results.

9. A computer-readable storage medium, characterized in that, The computer-readable storage medium includes a stored computer program, wherein, when the computer program is executed, it controls the device on which the computer-readable storage medium is located to perform the master-slave database data synchronization method according to any one of claims 1 to 7.

10. An electronic device, characterized in that, It includes one or more processors and a memory, the memory being used to store one or more programs, wherein when the one or more programs are executed by the one or more processors, the one or more processors cause the one or more processors to implement the master-slave database data synchronization method according to any one of claims 1 to 7.

11. A computer program product, characterized in that, The computer program product includes a computer program, wherein when the computer program is executed by a processor, it implements the master-slave database data synchronization method according to any one of claims 1 to 7.

Images