Real-time synchronization method for data of main and standby collaborative database

Abstract

The invention discloses a main and standby collaborative database data real-time synchronization method. The method comprises the following steps: S1) performing full data synchronization at a database standby end; s2) performing incremental data synchronization at the database master end through real-time log analysis; s3) connecting full-amount synchronous data and incremental synchronous data from two different databases through a data acquisition module; and S4) analyzing the acquired data through a data analysis module, and positioning an accurate merging time point. According to the method, an existing data synchronization mode that only a main end serves as a source end is optimized into a mode that full initialization is carried out at a database standby end, and real-time log analysis application is carried out at the database main end, so that resources of the database standby end can be utilized to the maximum extent, the influence on the performance of the main end is reduced, meanwhile, logs are captured at the main end to carry out real-time data synchronization, and the data synchronization efficiency is improved. And the data effectiveness is ensured.

Description

Technical Field

[0001] The present invention relates to a method for database data synchronization, and more particularly to a method for real-time synchronization of database data with master-slave cooperation. Background Art

[0002] Currently, the field of database technology presents a diverse and rapidly developing trend. Traditional relational database systems are still the mainstream of enterprise data management, such as MySQL, Oracle, SQL Server, etc., which are famous for their stability, consistency, and transaction processing capabilities. With the continuous advancement of informatization construction and the rise of the software industry, domestic databases have gradually emerged. They have good performance, security, and reliability, and are widely used in industries such as government, finance, telecommunications, energy, and manufacturing. With the continuous development and maturity of domestic databases, more and more enterprises have started to choose domestic databases as their core data management platforms, which has also brought about the trend of domestic substitution of databases.

[0003] With the advancement of domestic substitution, enterprises are bound to have multiple database systems, and data is stored in different databases. Heterogeneous data synchronization has become an urgent problem to be solved. Data synchronization between heterogeneous databases needs to overcome the differences of different database systems to ensure the consistency and integrity of data between different systems. For example, an enterprise may use multiple database systems such as Oracle, MySQL, and Xinchuang databases at the same time, and it is necessary to synchronize the data between them to ensure business continuity and data consistency. In addition, with the expansion of enterprise business and the increase in data volume, such as data synchronization between CRM systems and ERP systems, the databases carried by the systems may be of different types, which also puts higher requirements on the real-time, accuracy, and stability of heterogeneous data synchronization.

[0004] To meet these needs, there are already various methods for database synchronization: (1) ETL tools: ETL (Extract, Transform, Load) tools are a commonly used data synchronization method. It can extract data from the source database, perform appropriate transformation and processing, and then load it into the target database.

[0005] (2) Application dual writing: Data is written into two databases simultaneously through an application program to achieve data synchronization within the database. The writing, duplicate removal, breakpoint control, etc. of the data are all controlled by the dual writing program.

[0006] (3) Log-based synchronization: By parsing the transaction logs of the database to capture data changes, the data changes on the master side are converted into SQL statements for the standby database and executed on the standby side to achieve real-time data synchronization.

[0007] Although the above methods have their own characteristics, there are still some challenges in practical applications, and the main problems are as follows: (1) Poor real-time performance 1. ETL technology is usually batch-based and requires regular execution of data extraction, transformation, and loading operations, making it impossible to achieve real-time data synchronization.

[0008] 2. Although log-based synchronization can achieve relatively high real-time performance, there are still delays in some cases, especially in the case of large amounts of data and high concurrency.

[0009] 3. Although application double-writing can achieve real-time data synchronization, writing data to multiple storage systems simultaneously will increase the complexity and burden of the system and may affect system performance.

[0010] (2) Difficulty in ensuring data consistency 1. In the process of data transformation and loading, ETL technology may have problems such as data loss and repeated loading, resulting in inconsistent data between the target database and the source database.

[0011] 2. In the process of parsing database logs and applying them to the target database, log-based synchronization may encounter errors, resulting in incomplete or inconsistent data synchronization.

[0012] 3. Application double-writing needs to ensure the success of data writing operations in multiple storage systems; otherwise, it may lead to data inconsistency.

[0013] (3) Impact on the source database 1. When performing data extraction and transformation, ETL technology may cause a large load on the source database, affecting the performance of the source database.

[0014] 2. Log-based synchronization may need to parse and capture the logs of the source database, which may increase the load on the source database.

[0015] 3. Application double-writing needs to write data to multiple storage systems simultaneously, which may increase the write load on the source database.

[0016] As can be seen from the above, although ETL, log-based synchronization, and application double-writing technologies play important roles in different business scenarios and have their unique advantages, they still need to be further improved to solve the problems of real-time performance, consistency, and stability in the database synchronization process. Summary of the Invention

[0017] The technical problem to be solved by the present invention is to provide a method for real-time synchronization of database data with master-slave collaboration, which can overcome the limitations of existing methods and achieve real-time, accurate, and master-slave collaborative data synchronization to meet the growing demand for database synchronization.

[0018] The technical solution adopted by the present invention to solve the above technical problems is to provide a method for real-time synchronization of database data with primary and standby collaboration, including the following steps: S1) Perform full-volume data synchronization at the standby end of the database; S2) Perform incremental data synchronization at the primary end of the database by real-time log parsing; S3) Connect the full-volume synchronization data and incremental synchronization data from two different databases through a data acquisition module; S4) Analyze the collected data through a data analysis module to locate the accurate merging time point.

[0019] Further, the data collected by the data acquisition module includes the synchronization delay between the primary and standby databases, the start time point of full-volume synchronization, the incremental data during the delay period, and the number of records of the incremental data in the primary database. The data analysis module queries the synchronization delay between the primary and standby databases. If the delay meets the preset conditions, it initiates incremental log parsing in the primary database and full-volume data synchronization in the standby database; and determines the connection point between full-volume synchronization and incremental synchronization as the merging time point.

[0020] Further, the synchronization delay between the primary and standby databases is obtained from the internal view of the database, with the unit of seconds and the duration less than 1 minute; the synchronization delay between the primary and standby databases comes from the apply lag column in the v$dataguard_stats view of the Oracle database, or is obtained by executing show slave status in the MySQL database, or comes from the pg_stat_replication view of the PostgreSQL database.

[0021] Further, the start time point of full-volume synchronization is collected as follows: When the full synchronization is initiated at the standby end, the start time information start_sync_time is recorded, in the format of YYYY / MM / DD HH24:MI:SS, with the precision accurate to milliseconds.

[0022] Further, the incremental data during the delay period is collected as follows: Control the initiation of incremental synchronization to be prior to full-volume synchronization. After the incremental synchronization is initiated, the changed data in the primary end log is parsed in real time. At the moment when the full-volume synchronization is initiated, start_sync_time - 2×delay_time is obtained from the changed data parsed from the incremental synchronization log, that is, the changed data parsed starting from the time point 2 times the synchronization delay before the full-volume synchronization initiation time, and it is saved as the incremental data during the delay period.

[0023] Furthermore, the delayed period incremental data format includes all field values of the synchronization table. The format of the delayed period incremental data entry is as follows: The first column is ID, which is the serial number assigned according to the entries sorted by time; the second column is time, which is the information parsed from the log, usually the time when the transaction is committed and written to the log, and multiple records can be included at the same time; the third column is table name, which is the name of the table for data operation in the record; the fourth column is operation, which is the type of operation, including insert, update, and delete; the fifth column is column name, which is the name of the column involved in the table; the sixth column is column value, which is the value of the modified column in this operation.

[0024] Furthermore, the number of records in the incremental data master database is collected as follows: At the moment when the slave database initiates full synchronization, according to the delayed period incremental data parsed from the log, the number of changed data is queried and recorded in the master database by starting a transaction; at the same time, the primary key column of the fields on this table is recorded; after the full synchronization is completed, the number of delayed period incremental data is queried in the new database, and the number of entries is recorded.

[0025] Furthermore, before the data analysis module executes the 2-fold delay time in the slave database synchronization, it queries the number of incremental data, and determines the docking time point of full synchronization and incremental synchronization according to the query number through the primary key priority analysis strategy, the merge strategy of insert and delete for the same record, and the first inconsistent strategy.

[0026] Furthermore, the primary key priority analysis strategy determines that the synchronization docking time point is between the last record with consistent primary key and the first inconsistent record, specifically including: checking whether the delayed period incremental data entries are sorted by time, and if not, sorting them by time; screening the entries with non-null primary key columns; comparing the entries with non-null primary key columns in the master database and the new database in sequence. The merge strategy of insert and delete for the same record includes: S41) checking whether the delayed period incremental data entries are sorted by time, and if not, sorting them by time; S42) screening the entries with the operation column being insert or delete and the column values of insert and delete being the same; S43) comparing the entries with the operation column being insert or delete and the column values being the same in the master database and the new database in sequence; S44) selecting the first entry with the operation being insert or delete, if the number of entries is the same, then comparing the other delete or insert entry with the same column value, if the number is also the same, it means that all entries in this group are consistent and can be skipped; if the number of entries is different, then determine that the synchronization docking time point is between this whole group.

[0027] Further, the first inconsistency strategy includes: S45) Select the earliest record after sorting within the window defined by the primary key priority analysis strategy, the same record insert and delete merge strategy, and the first inconsistency strategy, or within the incremental data entries during the entire latency period; S46) Compare the entries with non-null primary key columns in the primary database and the new database. If they are consistent, repeat S45. If they are inconsistent, the time of this record is the synchronization docking time point 4, and incremental synchronization starts from this record.

[0028] The present invention has the following beneficial effects compared with the prior art: The method for real-time synchronization of database data with master-slave collaboration provided by the present invention optimizes the existing data synchronization method with only the master end as the source end in the existing mainstream log synchronization solution to perform full-scale initialization at the database slave end and perform real-time log parsing and application at the database master end, enabling maximum utilization of the resources of the database slave end, reducing the impact on the performance of the master end, and at the same time capturing logs at the master end for real-time data synchronization to ensure data timeliness. The present invention can accurately dock the full-scale synchronization at the slave end with the incremental synchronization at the master end, and maximize the use of existing resources to complete real-time and accurate synchronization of data. BRIEF DESCRIPTION OF THE DRAWINGS

[0029] Figure 1 It is a schematic diagram of the real-time synchronization framework of database data with master-slave collaboration of the present invention; Figure 2 It is a schematic diagram for determining each time point of the real-time synchronization of database data with master-slave collaboration of the present invention; Figure 3 It is a schematic diagram of the real-time synchronization process of database data with master-slave collaboration of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0030] The present invention will be further described below with reference to the drawings and embodiments.

[0031] The present invention provides a method for real-time synchronization of database data with master-slave collaboration, including the following steps: S1) Perform full-scale data synchronization at the database slave end; S2) Perform incremental data synchronization at the database master end through real-time log parsing; S3) Connect the full-scale synchronization data and incremental synchronization data from two different databases through a data acquisition module; S4) Analyze the collected data through a data analysis module to locate the accurate merge time point.

[0032] The purpose of the proposed invention is to solve the problem that heterogeneous database data synchronization can only be performed on the primary side or the standby side, enabling full - volume data synchronization to be completed quickly by making full use of the standby - side computing power, and ensuring data timeliness through real - time incremental data synchronization on the primary side. For this purpose, based on the existing mainstream log - based synchronization solution, the present invention optimizes the existing data synchronization method with only the primary side as the source side to perform full - volume initialization on the standby side of the database and perform real - time log parsing and application on the primary side of the database, so as to make the best use of the resources of the standby side of the database, reduce the impact on the performance of the primary side, and at the same time capture logs on the primary side for real - time data synchronization to ensure data timeliness, as Figure 1 shown.

[0033] The newly proposed logic unit of the present invention is located before full - volume synchronization and incremental synchronization are written into the target new database. After the full - volume synchronization data and incremental synchronization data are generated, they are collected and analyzed by the newly added logic unit, and the full - volume synchronization data and incremental synchronization data from two different databases are docked to generate a complete data stream.

[0034] The newly added logic unit includes two major modules: data collection and data analysis. The data collection module is mainly responsible for docking with the master database and the slave database to collect the necessary metrics and parameters of the program. The analysis module analyzes based on the data of the collection module to locate the accurate merging time point.

[0035] Please refer to Figure 2 . At time T1, the master - database incremental log parsing is initiated. At time T2, the slave - database full - volume data synchronization is initiated. At time T3, the slave - database full - volume data synchronization ends. The proposed invention of the present invention realizes accurately locating time T2 in the incremental data stream, enabling the full - volume data stream and the incremental data stream to be merged at time T2. T4 is the connection point between full - volume synchronization and incremental synchronization. The proposed invention of the present invention locates T4 through the following methods.

[0036] (I) Data collection module The data collection module is responsible for collecting the data required for the newly added logic unit to analyze the merging time point, mainly including the master - slave database synchronization delay, the full - volume synchronization initiation time point T2, the incremental data during the T2 - synchronization - delay period, and the number of incremental data records in the master database, including the following aspects of data: 1. Master - slave database synchronization delay The master - slave database synchronization delay can be obtained from the internal view of the database. For example, in the Oracle database, the apply lag column in the v$dataguard_stats view; in the MySQL database, execute show slave status; in the PostgreSQL database, the pg_stat_replication view; the master - slave database synchronization delay or the calculated delay value delay_time can be directly obtained from various database views, with the unit of seconds, usually less than 1 minute; 2. Full - volume synchronization initiation time When the standby side initiates full - synchronization, record the initiation time information start_sync_time, in the format of YYYY / MM / DD HH24:MI:SS with precision accurate to milliseconds.

[0037] 3. Incremental data during the delay period The incremental synchronization is initiated prior to the full - volume synchronization. After initiation, the program will parse the changed data in the master - side log in real - time. At the moment when the full - volume synchronization is initiated, obtain the data parsed from the changed log of the incremental synchronization starting from start_sync_time - 2×delay_time, that is, the changed data parsed from the moment 2 times the synchronization delay before the full - volume synchronization initiation time, and save it to ensure that the incremental synchronization start time is earlier than the full - volume synchronization initiation time.

[0038] The format of the incremental data during the delay period is related to the synchronization table format and contains all field values of the synchronization table. The format of the incremental data entries during the delay period is as follows: The first column is ID, which is the serial number assigned according to the entries sorted by time; The second column is time, which is the information parsed from the log, usually the time written to the log after the transaction is committed. There may be multiple records at the same time; The third column is table name, which is the name of the table for which the data operation is recorded in the record; The fourth column is operation, which is the type of operation, such as insert, update, delete, etc.; The fifth column is column name, which is the name of the column involved in the table; The sixth column is column value, which is the value of the column modified by this operation.

[0039] 4. Number of records in the master database of incremental data At the moment when the standby database initiates full - volume synchronization, according to the incremental data during the delay period parsed from the log, start a transaction in the master database to query the number of changed data and record the number; at the same time, record the primary key column of the fields on this table. The specific information is as follows: After the full - volume synchronization is completed, query the number of incremental data entries during the delay period in the new database and record the number of entries. The specific information is as follows: (2) Data analysis module The data analysis module identifies the T4 time point when the log application starts by comparing the differences in the number of recorded entries in the master database and the new database for the incremental data generated from start_sync_time - 2×delay_time to the full data start time in the acquisition module. The data analysis module implements X strategies, and jointly judges and analyzes the T4 time point through these strategies.

[0040] 1. Primary key priority analysis strategy, T4 is between the last record with the same primary key and the first inconsistent record. (1) Check whether the incremental data entries in the delay period are sorted by time. If not, sort them by time. (2) Filter the entries with non-null primary key columns. (3) Compare the entries with non-null primary key columns in the master database and the new database in order. For the incremental data records in the delay period of the master database and the new database, entries with primary keys are compared first. Since the incremental data in the delay period is strictly sorted by time, if the number of entries with primary keys in the master database and the new database is the same, it means that they are the same before this ID or time. For example, for records ID1 and ID2, T4 should be greater than or equal to the time of ID2. However, the records of ID5 are inconsistent between the new database and the master database. Therefore, T4 is between ID2 and ID5.

[0041] 2. Merge analysis strategy for the same record insert and delete (1) Check whether the incremental data entries in the delay period are sorted by time. If not, sort them by time. (2) Filter the entries where the operation column is insert or delete and the values of the insert and delete columns are the same. (3) Compare the entries in the master database and the new database where the operation column is insert or delete and the column values are the same in order. (4) Select the first entry that is insert or delete. If the number of entries is the same, compare the other delete or insert entry with the same column value. If the number is also the same, it means that all entries in this group are the same and can be skipped. If the number of entries is different, the T4 time is between this entire group. Repeat step (4).

[0042] The incremental data in the delay period merges and analyzes the insert and delete operations based on the full fields of the records. For example, for the records ID1 and ID5 in the above table, if the number of entries where the insert and delete records are located is the same in the master database and the new database, the T4 time should be before ID1 or after ID5. If the number of entries where the insert and delete records are located is different in the master database and the new database, the T4 time should be between ID1 and ID5.

[0043] If the number of entries at the ID1 moment in the above case is the same, but the number of entries at the ID moment is different, so T4 is between ID1 and ID5; merged with the result of Strategy 1, T4 should be between ID2 and ID5 3. The first inconsistency is the T4 time point strategy (1) Within the window range defined by Strategy 1 and Strategy 2, or within all incremental data entries during the entire delay period, select the earliest record after sorting; (2) Compare the entries with non-null primary key columns in the main database and the new database. If they are the same, repeat (1). If they are different, the time of this record is T4, and incremental synchronization starts from this record; Within the time window determined by the primary key priority analysis strategy, such as from ID2 to ID5, compare the consistency of the number of records in the new database and the main database in the order of ID. The time of the first inconsistent record found is the T4 time point. As shown in the above table at the ID3 moment, the record in the main database is 0 and the record in the new database is 1, indicating that the incremental log has not been applied to the new database at this moment. Therefore, the start time of incremental synchronization is the record time of ID3, which is 2024 / 5 / 30 13:15:21.922.

[0044] Through the primary key priority analysis strategy, the merge strategy of insert and delete for the same record, and the first inconsistency strategy, the docking time points of full-scale synchronization and incremental synchronization can be accurately located, enabling seamless connection between the full-scale data stream and the incremental data stream, and realizing the data accuracy of the original main database or slave database synchronization.

[0045] The method involved in this invention proposal includes two major functional modules: the data acquisition module and the analysis module, which realize full-scale data synchronization and incremental data synchronization that can be executed at different nodes. Full-scale data synchronization is executed at the slave database, making full use of the computing resources of the slave database to complete full-scale data synchronization as soon as possible. Since the incremental data size is small and has high requirements for latency, it can be executed at the main database, solving the problem that the existing solution can only synchronize at one node, and ensuring the timeliness and security of data synchronization.

[0046] Please refer to Figure 3 , the real-time data synchronization process of the master-slave collaborative database of this invention is as follows: 1. Query the synchronization delay between the master and slave databases, and judge whether it meets the condition of being less than 1 minute. If not, wait.

[0047] 2. When the delay meets the condition, initiate incremental log parsing at the main database.

[0048] 3. Initiate full-scale data synchronization at the slave database.

[0049] 4. Query the number of incremental data entries before the slave database synchronizes and executes for 2 times the delay time.

[0050] 5. Analyze separately according to three strategies based on the number of queries to determine the connection point between the full data and the incremental data.

[0051] 6. Apply the incremental data log after the connection time point.

[0052] Although the present invention has been disclosed above in preferred embodiments, it is not intended to limit the present invention. Any person skilled in the art can make some modifications and improvements without departing from the spirit and scope of the present invention. Therefore, the protection scope of the present invention shall be defined by the claims.

Claims

1. A method for real-time synchronization of database data with primary and standby collaboration, characterized in that, It includes the following steps: S1) Perform full - volume data synchronization at the standby end of the database; S2) Perform incremental data synchronization at the master end of the database by real - time log parsing; S3) Connect the full - volume synchronization data and incremental synchronization data from two different databases through the data acquisition module; S4) Analyze the collected data through the data analysis module to locate the accurate merge time point.

2. The method for real-time synchronization of database data with primary and standby collaboration according to claim 1, characterized in that, The data collected by the data acquisition module includes the master - slave database synchronization delay, the start time point of full - volume synchronization, the incremental data during the delay period, and the number of records of incremental data in the master database. The data analysis module queries the master - slave database synchronization delay. If the delay meets the preset conditions, it initiates incremental log parsing in the master database and full - volume data synchronization in the slave database; and determines the connection point between full - volume synchronization and incremental synchronization as the merge time point.

3. The method for real-time synchronization of database data with primary and standby collaboration according to claim 2, wherein, The master - slave database synchronization delay is obtained from the internal view of the database, with the unit of seconds and the duration less than 1 minute; The master - slave database synchronization delay comes from the apply lag column in the v$dataguard_stats view of the Oracle database, or is obtained by executing show slave status in the MySQL database, or comes from the pg_stat_replication view of the PostgreSQL database.

4. The method for real-time synchronization of database data with primary and standby collaboration according to claim 2, characterized in that, The start time point of full - volume synchronization is collected as follows: when the full - volume synchronization is initiated at the standby end, record the start time information start_sync_time in the format of YYYY / MM / DD HH24:MI:SS, with the precision accurate to milliseconds.

5. The method for real-time synchronization of database data with primary and standby collaboration as claimed in claim 2, wherein The incremental data during the delay period is collected as follows: control the incremental synchronization to be initiated prior to the full - volume synchronization. After the incremental synchronization is initiated, real - time parse the changed data in the master - end log. At the moment when the full - volume synchronization is initiated, obtain start_sync_time - 2×delay_time from the changed data parsed from the incremental - synchronization log, that is, the changed data parsed starting from the moment 2 times the synchronization delay before the full - volume synchronization start time, and save it as the incremental data during the delay period.

6. The method for real-time synchronization of database data with primary and standby collaboration as claimed in claim 5, wherein The format of the incremental data during the delay period contains all field values of the synchronized table. The format of the incremental data entry during the delay period is as follows: The first column is ID. ID is the serial number assigned according to the order of the entries by time; The second column is time. The time is the information parsed from the log, usually the time written to the log after the transaction is committed. Multiple records may be included at the same time; The third column is table name, which is the name of the table for data operation in the record; The fourth column is operation, and the type of operation includes insert, update, and delete; The fifth column is column name, which is the name of the column involved in the table; The sixth column is column value, which is the value of the modified column in this operation.

7. The method for real-time synchronization of database data with primary and standby collaboration according to claim 2, wherein The number of records of incremental data in the master database is collected as follows: at the moment when the slave database initiates full - volume synchronization, according to the incremental data during the delay period parsed from the log, start a transaction in the master database to query the number of changed data and record the number; at the same time, record the primary key column of the fields on the table; after the full - volume synchronization is completed, query the number of incremental data entries during the delay period in the new database and record the number of entries.

8. The method for real-time synchronization of database data with primary and standby collaboration according to claim 2, wherein Before the data analysis module executes the 2x latency time in the slave database synchronization, it queries the number of incremental data records, and determines the full synchronization and incremental synchronization docking time points according to the query results through the primary key priority analysis strategy, the same record insert and delete merge strategy, and the first inconsistent strategy.

9. The method for real-time synchronization of database data with primary and standby collaboration according to claim 8, wherein, The primary key priority analysis strategy determines that the synchronization docking time point is between the last record with consistent primary keys and the first inconsistent record. Specifically, it includes: checking whether the incremental data entries in the latency period are sorted by time, and if not, sorting them by time; filtering the entries with non-null primary key columns; comparing the entries with non-null primary key columns in the master database and the new database in sequence. The same record insert and delete merge strategy includes: S41) Check whether the incremental data entries in the latency period are sorted by time, and if not, sort them by time; S42) Filter the entries with the operation column being insert or delete and the same column values for insert and delete; S43) Compare the entries with the operation column being insert or delete and the same column values in the master database and the new database in sequence; S44) Select the first entry with the operation being insert or delete. If the number of entries is the same, compare the other delete or insert entry with the same column values. If the number is also the same, it means that all entries in this group are consistent and can be skipped; if the number is different, determine that the synchronization docking time point is within this entire group.

10. The method for real-time synchronization of database data with primary and standby collaboration according to claim 9, characterized in that, The first inconsistent strategy includes: S45) Within the window defined by the primary key priority analysis strategy, the same record insert and delete merge strategy, and the first inconsistent strategy, or within all the incremental data entries in the entire latency period, select the earliest record after sorting; S46) Compare the entries with non-null primary key columns in the master database and the new database. If they are consistent, repeat S45; if they are inconsistent, the time of this record is the synchronization docking time point, and incremental synchronization starts from this record.

Images