Data synchronization method and terminal
By monitoring the master-slave synchronization delay time and dynamically selecting single-threaded or multi-threaded aggregated database operation logs, the problem of insufficient data synchronization performance and throughput in the existing technology is solved, and efficient data synchronization is achieved in scenarios with low data requirements.
Patent Information
- Application Number
- CN202111244896.4
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2021-10-26
- Publication Date
- 2024-11-29
- Estimated Expiration
- 2041-10-26
AI Technical Summary
During the data synchronization process, the existing technology has insufficient synchronization performance and throughput when ensuring data accuracy, especially in scenarios where high data accuracy is not required. Transaction characteristics will limit the throughput of data synchronization and increase master-slave synchronization delay time.
By monitoring the master-slave synchronization delay time of the slave node, if the delay time exceeds the first delay time but does not exceed the second delay time, a single thread will be used to aggregate the database operation log; if the delay time exceeds the second delay time, multiple uses The thread aggregates the database operation logs and synchronizes the data based on the aggregation database statements.
In scenarios where data requirements are not high, non-transactional data synchronization method is adopted to improve data synchronization throughput, reduce the delay time of master-slave synchronization, and accept inaccurate data in multi-threaded scenarios to improve efficiency.
Smart Images

Figure CN114090609B_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of database technology, and in particular to a data synchronization method and a terminal. Background Art
[0002] At present, in order to achieve capacity expansion and large data storage, each database cluster generally adopts a distributed approach to deploy database nodes. At the same time, in order to further improve the performance of the system, the database master-slave separation will be carried out, that is, the data is written to the master node, synchronized to the slave node through data synchronization, and the data is read from the slave node.
[0003] In order to ensure the accuracy of synchronization, the old data synchronization must be performed one by one in the order of logs according to the characteristics of the transaction, but the synchronization performance needs to be improved. In addition, in some data synchronization scenarios where it is not necessary to ensure data accuracy, the use of transaction characteristics for data synchronization will further limit the throughput of data synchronization and increase the delay time of master-slave synchronization. Summary of the invention
[0004] The technical problem to be solved by the present invention is: a data synchronization method and a terminal, which can improve the throughput of data synchronization in a scenario where the data accuracy requirement is not high.
[0005] In order to solve the above technical problems, the technical solution adopted by the present invention is:
[0006] A data synchronization method comprises the steps of:
[0007] Monitor the master-slave synchronization delay time of each slave node, if the delay time exceeds the first delay time, determine whether the delay time exceeds the second delay time, if so, use multiple threads to aggregate database statements on the database operation log, otherwise, use a single thread to aggregate database statements on the database operation log;
[0008] Data synchronization is performed based on the aggregated database statements.
[0009] In order to solve the above technical problems, another technical solution adopted by the present invention is:
[0010] A data synchronization terminal comprises a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the following steps when executing the computer program:
[0011] Monitor the master-slave synchronization delay time of each slave node, if the delay time exceeds the first delay time, determine whether the delay time exceeds the second delay time, if so, use multiple threads to aggregate database statements on the database operation log, otherwise, use a single thread to aggregate database statements on the database operation log;
[0012] Data synchronization is performed based on the aggregated database statements.
[0013] The beneficial effects of the present invention are as follows: by monitoring the master-slave synchronization delay time of each slave node, when the delay time exceeds the first delay time and does not exceed the second delay time, a single thread is used to aggregate database statements on the database operation log; when the delay time exceeds the second delay time, multiple threads are used to aggregate database statements on the database operation log, but data aggregation in a multi-threaded scenario may cause inaccurate data; data synchronization is performed based on the aggregated database statements, therefore, for scenarios with low data requirements, the use of a non-transactional data synchronization method can improve the throughput of data synchronization and reduce the delay time of master-slave synchronization. BRIEF DESCRIPTION OF THE DRAWINGS
[0014] Figure 1 A flowchart of a data synchronization method according to an embodiment of the present invention;
[0015] Figure 2 The figure is a schematic diagram of a data synchronization terminal according to an embodiment of the present invention. DETAILED DESCRIPTION
[0016] In order to explain the technical content, achieved objectives and effects of the present invention in detail, the following is an explanation in combination with the implementation modes and the accompanying drawings.
[0017] Please refer to Figure 1 , an embodiment of the present invention provides a data synchronization method, comprising the steps of:
[0018] Monitor the master-slave synchronization delay time of each slave node, if the delay time exceeds the first delay time, determine whether the delay time exceeds the second delay time, if so, use multiple threads to aggregate database statements on the database operation log, otherwise, use a single thread to aggregate database statements on the database operation log;
[0019] Data synchronization is performed based on the aggregated database statements.
[0020] From the above description, it can be seen that the beneficial effects of the present invention are: by monitoring the master-slave synchronization delay time of each slave node, when the delay time exceeds the first delay time and does not exceed the second delay time, a single thread is used to aggregate database statements on the database operation log; when the delay time exceeds the second delay time, multiple threads are used to aggregate database statements on the database operation log, but data aggregation in a multi-threaded scenario will cause inaccurate data; data synchronization is performed based on the aggregated database statements. Therefore, for scenarios with low data requirements, the use of non-transactional data synchronization can improve the throughput of data synchronization and reduce the delay time of master-slave synchronization.
[0021] Furthermore, the aggregating database statements on the database operation log using multiple threads includes:
[0022] Obtaining the operation logs of the database within a preset time range, and allocating a preset number of the operation logs to each thread;
[0023] In each thread, the database operation log is aggregated for database statements.
[0024] From the above description, it can be seen that by obtaining the database operation log within a preset time range, a preset number of operation logs are allocated to each thread, and database statements are aggregated for the operation logs through a single thread in each thread. Therefore, when the delay time exceeds the second delay time, the multi-thread aggregation method is used to timely reduce the delay time of master-slave synchronization.
[0025] Furthermore, the aggregating database statements on the database operation log using a single thread includes:
[0026] Group the database operation logs within the preset time range according to the primary key ID;
[0027] Determine whether there is a database statement in the deletion state in each group of the operation logs, and if so, only save the database statement in the deletion state;
[0028] Otherwise, determine whether there is a database statement of the add state in each group of the operation logs. If so, overwrite the database statement of the update state with the database statement of the add state to obtain an aggregated database statement of the add state. Otherwise, aggregate the database statement of the update state to obtain an aggregated database statement of the update state.
[0029] From the above description, it can be seen that when a single-threaded database statement is aggregated, the operation logs are grouped according to the primary key ID. When there is a database statement in the deletion state, only the database statement in the deletion state is saved for the primary key ID; when there is no database statement in the deletion state, the database statements in the update state or the add state are aggregated. Therefore, only one database statement will be generated for a primary key ID within the same preset time range, and there is no need to perform database operations multiple times, which reduces resource usage.
[0030] Furthermore, the data synchronization according to the aggregated database statements includes:
[0031] Determine whether there is only one database statement aggregated for the primary key ID. If so, directly execute the aggregated database statement in the slave node;
[0032] Otherwise, determine whether the aggregated database statements include a database statement in a deletion state. If so, execute the database statement in the deletion state last in the slave node. Otherwise, execute the aggregated database statements in sequence in the slave nodes.
[0033] From the above description, it can be seen that if multi-threaded aggregation is performed, multiple aggregated database statements may be generated for one primary key ID. Therefore, if the database statements to be aggregated include database statements in the deletion state, the database statements in the deletion state are processed last to reduce the error rate when synchronizing data.
[0034] Furthermore, it also includes:
[0035] If the delay time does not exceed the first delay time, the database statements in the log are executed one by one in the slave node according to the time sequence of the operation log.
[0036] It can be seen from the above description that when the delay time does not exceed the first delay time, the database statements in the operation log are directly obtained one by one at the slave node for processing, so that the data synchronization method can be flexibly selected according to the master-slave synchronization situation.
[0037] Please refer to Figure 2 Another embodiment of the present invention provides a data synchronization terminal, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the following steps when executing the computer program:
[0038] Monitor the master-slave synchronization delay time of each slave node, if the delay time exceeds the first delay time, determine whether the delay time exceeds the second delay time, if so, use multiple threads to aggregate database statements on the database operation log, otherwise, use a single thread to aggregate database statements on the database operation log;
[0039] Data synchronization is performed based on the aggregated database statements.
[0040] From the above description, it can be seen that by monitoring the master-slave synchronization delay time of each slave node, when the delay time exceeds the first delay time and does not exceed the second delay time, a single thread is used to aggregate database statements on the database operation log; when the delay time exceeds the second delay time, multiple threads are used to aggregate database statements on the database operation log, but data aggregation in a multi-threaded scenario will cause inaccurate data; data synchronization is performed based on the aggregated database statements. Therefore, for scenarios with low data requirements, using a non-transactional data synchronization method can improve the throughput of data synchronization and reduce the delay time of master-slave synchronization.
[0041] Furthermore, the aggregating database statements on the database operation log using multiple threads includes:
[0042] Obtaining the operation logs of the database within a preset time range, and allocating a preset number of the operation logs to each thread;
[0043] In each thread, the database operation log is aggregated for database statements.
[0044] From the above description, it can be seen that by obtaining the database operation log within a preset time range, a preset number of operation logs are allocated to each thread, and database statements are aggregated for the operation logs through a single thread in each thread. Therefore, when the delay time exceeds the second delay time, the multi-thread aggregation method is used to timely reduce the delay time of master-slave synchronization.
[0045] Furthermore, the aggregating database statements on the database operation log using a single thread includes:
[0046] Group the database operation logs within the preset time range according to the primary key ID;
[0047] Determine whether there is a database statement in the deletion state in each group of the operation logs, and if so, only save the database statement in the deletion state;
[0048] Otherwise, determine whether there is a database statement of the add state in each group of the operation logs. If so, overwrite the database statement of the update state with the database statement of the add state to obtain an aggregated database statement of the add state. Otherwise, aggregate the database statement of the update state to obtain an aggregated database statement of the update state.
[0049] From the above description, it can be seen that when a single-threaded database statement is aggregated, the operation logs are grouped according to the primary key ID. When there is a database statement in the deletion state, only the database statement in the deletion state is saved for the primary key ID; when there is no database statement in the deletion state, the database statements in the update state or the add state are aggregated. Therefore, only one database statement will be generated for a primary key ID within the same preset time range, and there is no need to perform database operations multiple times, which reduces resource usage.
[0050] Furthermore, the data synchronization according to the aggregated database statements includes:
[0051] Determine whether there is only one database statement aggregated for the primary key ID. If so, directly execute the aggregated database statement in the slave node;
[0052] Otherwise, determine whether the aggregated database statements include a database statement in a deletion state. If so, execute the database statement in the deletion state last in the slave node. Otherwise, execute the aggregated database statements in sequence in the slave nodes.
[0053] From the above description, it can be seen that if multi-threaded aggregation is performed, multiple aggregated database statements may be generated for one primary key ID. Therefore, if the database statements to be aggregated include database statements in the deletion state, the database statements in the deletion state are processed last to reduce the error rate when synchronizing data.
[0054] Furthermore, it also includes:
[0055] If the delay time does not exceed the first delay time, the database statements in the log are executed one by one in the slave node according to the time sequence of the operation log.
[0056] It can be seen from the above description that when the delay time does not exceed the first delay time, the database statements in the operation log are directly obtained one by one at the slave node for processing, so that the data synchronization method can be flexibly selected according to the master-slave synchronization situation.
[0057] A data synchronization method and terminal of the present invention are suitable for realizing data synchronization between master and slave nodes within the same data source, and improving synchronization efficiency during the data synchronization process. The following is an explanation through specific implementation methods:
[0058] Embodiment 1
[0059] Please refer to Figure 1 , a data synchronization method, comprising the steps of:
[0060] S1. Monitor the master-slave synchronization delay time of each slave node. If the delay time exceeds the first delay time, determine whether the delay time exceeds the second delay time. If so, use multiple threads to aggregate database statements on the database operation log. Otherwise, use a single thread to aggregate database statements on the database operation log.
[0061] Specifically, a monitoring program is set up to monitor the master-slave synchronization delay of each slave node. Assuming that the normal value of the master-slave synchronization delay of a slave node is 1 second, when the master-slave synchronization delay is between 1 and 10 seconds, it means that there is a certain delay risk. At this time, a single-threaded data aggregation and data synchronization method can be used. When the master-slave synchronization delay is greater than 10 seconds, it means that there is a large amount of data delay and the data synchronization efficiency needs to be further improved. Therefore, a multi-threaded data aggregation and data synchronization method is used.
[0062] S11: If the delay time exceeds the first delay time but does not exceed the second delay time, a single-threaded data aggregation and data synchronization method is adopted.
[0063] The method of aggregating database statements on the database operation log using a single thread includes:
[0064] Group the database operation logs within the preset time range according to the primary key ID;
[0065] Determine whether there is a database statement in the deletion state in each group of the operation logs, and if so, only save the database statement in the deletion state;
[0066] Otherwise, determine whether there is a database statement of the add state in each group of the operation logs. If so, overwrite the database statement of the update state with the database statement of the add state to obtain an aggregated database statement of the add state. Otherwise, aggregate the database statement of the update state to obtain an aggregated database statement of the update state.
[0067] Specifically, when a single thread is used for data aggregation, an operation log data set within a preset time is obtained, and the logs are grouped according to the primary key ID, and each log record in each group includes three states: add, update, and delete.
[0068] When a group has a delete status, the primary key ID will only retain the delete database statement;
[0069] When there is no delete state, such as the add state, the fields in the update can be overwritten into the add statement, and finally only one overwritten add database statement is recorded;
[0070] When there is no delete state, such as no add state, multiple update statements can be overwritten and merged into the same statement, and finally only one update database statement is recorded.
[0071] Therefore, in a single-threaded scenario, the accuracy of master-slave synchronization data can be guaranteed.
[0072] S12: If the delay time exceeds the second delay time, a multi-threaded data aggregation and data synchronization method is adopted.
[0073] The method of aggregating database statements on the database operation log using multiple threads includes:
[0074] Obtaining operation logs of the database within a preset time range, and allocating a preset number of the operation logs to different threads;
[0075] In each thread, the database operation log is aggregated for database statements.
[0076] Specifically, when multi-threading is used for data aggregation, the main thread obtains a large amount of log data and simultaneously distributes a fixed number of logs to a separate thread. The subsequent operation steps in the separate thread are consistent with the steps for single-threaded data aggregation.
[0077] In this embodiment, when multi-threading is used for data aggregation, a set of operation log data within a preset time is obtained, and a preset number of operation logs are allocated to different threads;
[0078] In each thread, the primary key ID in the log is grouped, and each log record in each group contains three states: add, update, and delete.
[0079] When a group has a delete status, the primary key ID will only retain the delete database statement;
[0080] When there is no delete state, such as the add state, the fields in the update can be overwritten into the add statement, and finally only one overwritten add database statement is recorded;
[0081] When there is no delete state, such as no add state, multiple update statements can be overwritten and merged into the same statement, and finally only one update database statement is recorded.
[0082] Thus, the operation log data in each thread can be aggregated, and data synchronization can be performed based on the results of the aggregation in each thread. However, due to the possibility of data operations overlapping each other in multi-threaded operation scenarios, there may be scenarios where data is inaccurate. Therefore, if it is acceptable for synchronization scenarios with large amounts of data and non-important data such as operation behavior logs, partial data inaccuracy is acceptable, so the synchronization method of this embodiment can be used.
[0083] S13: If the delay time does not exceed the first delay time, the database statements in the log are executed one by one in the slave node according to the time sequence of the operation log.
[0084] Specifically, if the delay time does not exceed the first delay time, the database operation is first recorded in the log, and then the records in the log are executed one by one in sequence, and the data is restored in the slave node, thereby realizing data synchronization between the master and slave nodes.
[0085] S2. Synchronize data based on the aggregated database statements.
[0086] Specifically, step S2 includes:
[0087] Determine whether there is only one database statement aggregated for the primary key ID. If so, directly execute the aggregated database statement in the slave node;
[0088] Otherwise, determine whether the aggregated database statements include a database statement in a deletion state. If so, execute the database statement in the deletion state last in the slave node. Otherwise, execute the aggregated database statements in sequence in the slave nodes.
[0089] In this embodiment, when performing single-threaded aggregation, one primary key ID will only aggregate one database statement, which will be executed directly in the slave node; when performing multi-threaded aggregation, one primary key ID may aggregate multiple database statements, so it is necessary to determine whether the database statement contains a database statement in the deletion state. If so, it needs to be executed last.
[0090] Therefore, in this embodiment, data synchronization is performed on the operation log of the database. The operation log is recorded in SQL mode. Only the fields that are changed in the database will be operated in SQL. Assuming that a table has 100 fields, if the data changes only one field each time, only this field will be changed each time the data is synchronized, and other unchanged fields will not be processed, thus reducing the amount of data to be processed.
[0091] Embodiment 2
[0092] Please refer to Figure 2A data synchronization terminal includes a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, each step of a data synchronization method of embodiment 1 is implemented.
[0093] In summary, the present invention provides a data synchronization method and terminal, which monitors the master-slave synchronization delay time of each slave node, and when the delay time exceeds the first delay time and does not exceed the second delay time, uses a single thread to aggregate database statements on the operation log of the database; when the delay time exceeds the second delay time, uses multiple threads to aggregate database statements on the operation log of the database; wherein, when the single-thread database statement is aggregated, the operation log is grouped according to the primary key ID, and when there is a database statement in the deletion state, only the database statement in the deletion state is saved for the primary key ID; when there is no database statement in the deletion state, the database statements in the update state or the addition state are aggregated, so that only one database statement will be generated for a primary key ID within the same preset time range, and there is no need to perform database operations multiple times, thereby reducing resource usage; data synchronization is performed according to the aggregated database statements, so for scenarios with low data requirements, the use of non-transactional data synchronization can improve the throughput of data synchronization and reduce the delay time of master-slave synchronization.
[0094] The above descriptions are merely embodiments of the present invention and are not intended to limit the patent scope of the present invention. Any equivalent transformations made using the contents of the present invention's specification and drawings, or directly or indirectly applied in related technical fields, are also included in the patent protection scope of the present invention.
Claims
1. A data synchronization method, It is characterized in that Includes steps: Monitor the master-slave synchronization delay time of each slave node. If the delay time exceeds the first delay time, determine whether the delay time exceeds the second delay time. If so, obtain the operation log of the database within a preset time range, distribute the preset number of operation logs to different threads, and aggregate database statements for the operation log of the database in each thread. Otherwise, use a single thread to aggregate database statements for the operation log of the database; Data synchronization is performed based on the aggregated database statements.
2. A data synchronization method according to claim 1, It is characterized in that The aggregating of database statements on the operation log of the database using a single thread includes: Group the database operation logs within the preset time range according to the primary key ID; Determine whether there is a database statement in the deletion state in each group of the operation logs, and if so, only save the database statement in the deletion state; Otherwise, determine whether there is a database statement of the add state in each group of the operation logs. If so, overwrite the database statement of the update state with the database statement of the add state to obtain an aggregated database statement of the add state. Otherwise, aggregate the database statement of the update state to obtain an aggregated database statement of the update state.
3. A data synchronization method according to claim 1, It is characterized in that The data synchronization according to the aggregated database statements includes: Determine whether there is only one database statement aggregated for the primary key ID. If so, directly execute the aggregated database statement in the slave node; Otherwise, determine whether the aggregated database statements include a database statement in a deletion state. If so, execute the database statement in the deletion state last in the slave node. Otherwise, execute the aggregated database statements in sequence in the slave nodes.
4. A data synchronization method according to claim 1, It is characterized in that Also includes: If the delay time does not exceed the first delay time, the database statements in the log are executed one by one in the slave node according to the time sequence of the operation log.
5. A data synchronization terminal, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, It is characterized in that When the processor executes the computer program, the following steps are implemented: Monitor the master-slave synchronization delay time of each slave node. If the delay time exceeds the first delay time, determine whether the delay time exceeds the second delay time. If so, obtain the operation log of the database within a preset time range, distribute the preset number of operation logs to different threads, and aggregate database statements for the operation log of the database in each thread. Otherwise, use a single thread to aggregate database statements for the operation log of the database; Data synchronization is performed based on the aggregated database statements.
6. A data synchronization terminal according to claim 5, It is characterized in that The method of aggregating database statements on the operation log of the database using a single thread includes: Obtaining operation logs of a database within a preset time range, and grouping the operation logs of the database according to a primary key ID; Determine whether there is a database statement in the deletion state in each group of the operation logs, and if so, only save the database statement in the deletion state; Otherwise, determine whether there is a database statement of the add state in each group of the operation logs. If so, overwrite the database statement of the update state with the database statement of the add state to obtain an aggregated database statement of the add state. Otherwise, aggregate the database statement of the update state to obtain an aggregated database statement of the update state.
7. A data synchronization terminal according to claim 5, It is characterized in that The data synchronization according to the aggregated database statements includes: Determine whether there is only one database statement aggregated for the primary key ID. If so, directly execute the aggregated database statement in the slave node; Otherwise, determine whether the aggregated database statements include a database statement in a deletion state. If so, execute the database statement in the deletion state last in the slave node. Otherwise, execute the aggregated database statements in sequence in the slave nodes.
8. A data synchronization terminal according to claim 5, It is characterized in that Also includes: If the delay time does not exceed the first delay time, the database statements in the log are executed one by one in the slave node according to the time sequence of the operation log.
Citation Information
Patent Citations
Cross-room database synchronization method and system
CN103778136A
Database recovery method and apparatus and database server
CN104991836A
Log analysis-based database copying method and device
CN105955970A
