CN112434108B - Database synchronization method, device and equipment - Google Patents
Database synchronization method, device and equipment Download PDFInfo
- Publication number
- CN112434108B CN112434108B CN202011250392.9A CN202011250392A CN112434108B CN 112434108 B CN112434108 B CN 112434108B CN 202011250392 A CN202011250392 A CN 202011250392A CN 112434108 B CN112434108 B CN 112434108B
- Authority
- CN
- China
- Prior art keywords
- synchronization
- data table
- synchronization time
- time point
- target data
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING OR CALCULATING; COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/27—Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
- G06F16/273—Asynchronous replication or reconciliation
Landscapes
- Engineering & Computer Science (AREA)
- Databases & Information Systems (AREA)
- Theoretical Computer Science (AREA)
- Computing Systems (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The application provides a database synchronization method, a device and equipment, wherein the method comprises the following steps: acquiring a first synchronization time point and a second synchronization time point, wherein the first synchronization time point is the synchronization time point of a source data table in a source database, and the second synchronization time point is the synchronization time point of a target data table in a target database; determining a synchronization time difference value, wherein the synchronization time difference value is the difference between the first synchronization time point and the second synchronization time point; and according to the synchronization time difference value, selecting a mode of using full synchronization or incremental synchronization to synchronize the source data table to the target data table. According to the application, the source data table is synchronized to the target data table by flexibly selecting the full-quantity synchronization or the increment synchronization mode through different synchronization time differences, and the synchronization efficiency can be improved on the premise of ensuring that the data in the target data table is consistent with the data in the source data table.
Description
Technical Field
The present application relates to the field of data processing technologies, and in particular, to a method, an apparatus, and a device for database synchronization.
Background
With the development of network applications, data is increasingly accumulated, and a single database is not suitable for storing and managing the data which is increased explosively. In the ERP application field, with the development of service diversity, the number of data is increased and the types are complex, in order to facilitate data management, a developer splits a database according to a service subsystem, and splits one database into a plurality of databases. After splitting the database, the linked list query cannot be realized across databases, so that the data in one database needs to be synchronized to other databases needing to use the data.
The two common synchronization modes of the database are full-volume synchronization and incremental synchronization, wherein the full-volume synchronization is one-time synchronization of all data, the incremental synchronization is that only data is synchronously changed on the basis of the first full-volume synchronization, and the changed data comprises deleted data, updated data and inserted data. At present, the related solution is to synchronize the data of the source database to the target database in full quantity at regular time, so that the consistency of the data can be ensured, but with the increase of the data, the full quantity synchronization can take a lot of time each time; or on the basis of the first full-quantity synchronization, only incremental synchronization is performed, the data quantity in each synchronization is small, and the synchronization speed is high, but some errors may occur in the synchronization process to cause data inconsistency.
At present, no method is available for flexibly selecting a synchronization mode according to practical application conditions.
Disclosure of Invention
The application provides a database synchronization method, device and equipment, which can improve the synchronization efficiency on the premise of ensuring that data in a target data table is consistent with data in a source data table.
In a first aspect, a method of database synchronization is provided, the method comprising:
Acquiring a first synchronization time point and a second synchronization time point, wherein the first synchronization time point is the synchronization time point of a source data table in a source database, and the second synchronization time point is the synchronization time point of a target data table in a target database;
determining a synchronization time difference value, wherein the synchronization time difference value is the difference between the first synchronization time point and the second synchronization time point;
And according to the synchronization time difference value, selecting a mode of using full synchronization or incremental synchronization to synchronize the source data table to the target data table.
According to the method provided by the application, the database synchronization equipment firstly acquires the first synchronization time point and the second synchronization time point, then obtains the synchronization time difference value by calculating the difference value of the first synchronization time point and the second synchronization time point, and finally selects an incremental synchronization or full synchronization mode to synchronize the source data table to the target data table according to the synchronization time difference value. The application flexibly selects the incremental synchronization or the full synchronization mode to synchronize the source data table to the target data table according to different synchronization time differences, and can select different synchronization modes to synchronize according to actual application conditions relative to other methods in the related art, thereby improving the synchronization efficiency on the premise of ensuring that the data of the target data table is consistent with the data of the source data table.
With reference to the first aspect, in certain implementation manners of the first aspect, according to the synchronization time difference value, selecting to synchronize the source data table to the target data table in a manner of using full synchronization or incremental synchronization includes:
If the synchronization time difference is smaller than 0, synchronizing the source data table to the target data table in a full-scale synchronization mode;
if the synchronization time difference value is greater than or equal to 0 and less than or equal to a tracking change time threshold value, synchronizing the source data table to the target data table in an incremental synchronization mode;
And if the synchronization time difference value is larger than the tracking change time threshold value, synchronizing the source data table to the target data table in a full-scale synchronization mode.
In an actual application scene, according to different synchronous time difference values of the target data table and the source data table, a full-quantity synchronous or incremental synchronous mode is selected to be used for synchronization, so that the data of the target data table is consistent with the data of the source data table, and the synchronization efficiency is improved.
With reference to the first aspect, in certain implementation manners of the first aspect, acquiring a first synchronization time point and a second synchronization time point includes:
the first synchronization time point and/or the second synchronization time point are not acquired;
the method further comprises the steps of:
The source data table is synchronized to the target data table using a full synchronization approach.
In this implementation, if the first synchronization time point and/or the second synchronization time point do not exist, which means that the source data table is never synchronized to the target data table before, all data of the source data table is synchronized to the target data table directly by using a full-volume synchronization mode, so as to ensure that the data of the target data table is consistent with that of the source data table.
With reference to the first aspect, in certain implementations of the first aspect, synchronizing the source data table to the target data table using incremental synchronization includes:
And synchronizing the change data after the second synchronization time in the source data table to the target data table according to the operation sequence of deleting, updating and inserting.
In the implementation mode, the modified data are ordered according to the operation sequence of deleting, updating and inserting, and then are synchronized according to the sequence, so that the problem of data synchronization failure caused by unique index conflict in the synchronization process can be solved.
Optionally, the deleting operation is batch deleting, the updating operation is batch updating, and the inserting operation is batch inserting.
Based on the alternative mode, the operations of deleting, updating and inserting are finished in batches, so that the time required for synchronization is shortened, and the synchronization efficiency is improved.
With reference to the first aspect, in certain implementations of the first aspect, synchronizing the source data table to the target data table using a full synchronization approach includes:
acquiring synchronization configuration information, and determining whether the type of the full synchronization is forced synchronization or not according to the synchronization configuration information;
if yes, deleting all the data of the target data table, and inserting all the data of the source data table into the target data table in batches;
If not, synchronizing all the data of the source data table into the target data table in a merging mode.
Through full synchronization, the data consistency of the target data table and the source data table can be ensured; and the updating and inserting operation of the target data table data can be completed simultaneously by synchronizing in a merging mode.
With reference to the first aspect, in certain implementation manners of the first aspect, each of the source data table and the target data table includes a plurality of source data tables and a plurality of target data tables in one-to-one correspondence;
The acquiring the first synchronization time point and the second synchronization time point includes:
Acquiring a plurality of first synchronization time points corresponding to a plurality of source data tables, and acquiring a plurality of second synchronization time points corresponding to a plurality of target data tables;
The determining the synchronization time difference value includes:
Determining a plurality of synchronization time differences according to a plurality of corresponding first synchronization time points and a plurality of corresponding second synchronization time points;
The step of synchronizing the source data table to the target data table by using a full synchronization or incremental synchronization mode according to the synchronization time difference value comprises the following steps:
And synchronizing the source data tables to the target data tables in a parallel manner according to the synchronization time differences.
In this implementation, the synchronization of multiple source data tables to multiple target data tables is performed in parallel by multiple threads, with one thread performing one source data table to one target data table synchronization. Through the multithreading parallel mode, synchronization from multiple pairs of source data tables to target data tables is simultaneously carried out, so that synchronization time is shortened, and synchronization efficiency is improved.
In a second aspect, there is provided an apparatus for database synchronization, comprising:
An acquisition unit configured to acquire a first time point and a second time point;
a determining unit configured to determine a synchronization time difference value, the synchronization time difference value being a difference between the first time point and the second time point;
and the execution unit is used for selecting to synchronize the source data table to the target data table in a full-volume synchronization or incremental synchronization mode according to the synchronization time difference value.
The beneficial effects of the device provided by the present application can be seen from the beneficial effects of the method for synchronizing databases provided in the first aspect, and are not described herein.
With reference to the second aspect, in certain implementations of the second aspect, the execution unit is specifically configured to:
If the synchronization time difference is smaller than 0, synchronizing the source data table to the target data table in a full-scale synchronization mode;
if the synchronization time difference value is greater than or equal to 0 and less than or equal to a tracking change time threshold value, synchronizing the source data table to the target data table in an incremental synchronization mode;
And if the synchronization time difference value is larger than the tracking change time threshold value, synchronizing the source data table to the target data table in a full-scale synchronization mode.
With reference to the second aspect, in some implementations of the second aspect, the acquiring unit does not acquire the first synchronization time point and/or the second synchronization time point, and the executing unit is further configured to: the source data table is synchronized to the target data table using a full synchronization approach.
With reference to the second aspect, in some implementations of the second aspect, the source data table is synchronized to the target data table using incremental synchronization, and the execution unit is further configured to: and synchronizing the change data after the second synchronization time point in the source data table to the target data table according to the operation sequence of deleting, updating and inserting.
With reference to the second aspect, in certain implementations of the second aspect, the execution unit is further configured to: and performing batch deletion operation, batch updating operation and batch insertion operation.
With reference to the second aspect, in some implementations of the second aspect, the source data table is synchronized to the target data table using a full-volume synchronization manner, and the obtaining unit is further configured to: acquiring synchronous configuration information; the determining unit is further configured to: determining whether the type of the full-quantity synchronization is forced synchronization or not according to the synchronization configuration information; the execution unit is further to: if yes, deleting all the data of the target data table, and inserting all the data of the source data table into the target data table in batches; if not, synchronizing all the data of the source data table to the target data table in a merging mode.
With reference to the second aspect, in certain implementation manners of the second aspect, each of the source data table and the target data table includes a plurality of source data tables, and the plurality of source data tables corresponds to the plurality of target data tables one to one; the acquisition unit is further configured to: acquiring a plurality of first synchronization time points corresponding to a plurality of source data tables, and acquiring a plurality of second synchronization time points corresponding to a plurality of target data tables; the determining unit is further configured to: determining a plurality of synchronization time differences according to a plurality of corresponding first synchronization time points and a plurality of corresponding second synchronization time points; the execution unit is further to: and synchronizing the source data tables to the target data tables in a parallel manner according to the synchronization time differences.
In a third aspect, an apparatus for database synchronization is provided that includes a memory, a processor, and a computer program stored in the memory and executable on the processor.
Optionally, the apparatus comprises: computer, server.
In a fourth aspect, there is provided a computer readable storage medium storing a computer program which when executed performs any one of the methods of the first aspect.
In a fifth aspect, there is provided a computer program product comprising: computer program code which, when run on a computer, causes the computer to perform any of the methods of the first aspect described above.
Drawings
FIG. 1 is a schematic flow chart of a method for database synchronization provided by an embodiment of the present application.
FIG. 2 is a schematic diagram showing specific steps of an alternative implementation of the method according to an embodiment of the present application.
Fig. 3 is a schematic diagram of an apparatus for database synchronization according to an embodiment of the present application.
Fig. 4 is a schematic structural diagram of an apparatus for database synchronization according to an embodiment of the present application.
Detailed Description
The technical scheme of the application will be described below with reference to the accompanying drawings. It will be apparent that the described embodiments are only some, but not all, embodiments of the application.
In the following description, for purposes of explanation and not limitation, specific details are set forth such as the particular system architecture, techniques, etc., in order to provide a thorough understanding of the embodiments of the present application. It will be apparent, however, to one skilled in the art that the present application may be practiced in other embodiments that depart from these specific details. In other instances, detailed descriptions of well-known systems, devices, circuits, and methods are omitted so as not to obscure the description of the present application with unnecessary detail.
The term "comprises/comprising" when used herein is taken to specify the presence of stated features, integers, steps, operations, elements, and/or components, but does not preclude the presence or addition of one or more other features, integers, steps, operations, elements, components, and/or groups thereof. The terms "comprising," "including," "having," and variations thereof mean "including but not limited to," unless expressly specified otherwise.
The term "and/or" is herein merely an association relationship describing an associated object, meaning that there may be three relationships, e.g., a and/or B, may represent: a exists alone, A and B exist together, and B exists alone. In addition, the character "/" herein generally indicates that the front and rear associated objects are an "or" relationship.
A single database has a certain advantage in the aspects of linked list query and the like, but with the explosive growth of data, storing all data with a single database cannot meet the requirement of convenient data management and data security, so that developers store data in a plurality of databases.
In the ERP application field, as data accumulation increases, developers create databases corresponding to each service according to different services, and only relevant data of the corresponding service is stored in each database. When all data is stored in a single database, one service can obtain information of another service through the linked list query, but the linked list query cannot be realized across databases. The method is characterized in that a database storing information of a certain service is assumed to be a source database, other services need partial information of the service, the partial information is stored in a source data table in the source database, the database storing information of other services is assumed to be a target database, and in order to realize information intercommunication among the services, the source data table needs to be synchronized to a target data table in the target database.
There are two common synchronization methods: full synchronization and incremental synchronization, wherein the full synchronization is one-time synchronization of all data, and the incremental synchronization is only to synchronize and change data on the basis of the first full synchronization. Currently, related solutions are either timed full synchronization or incremental synchronization only on the basis of the first full synchronization. And none of the methods can flexibly synchronize according to the actual application situation.
In order to solve the problems, the application provides a database synchronization method, which flexibly selects a full-quantity synchronization or incremental synchronization mode according to an actual application scene, thereby improving the synchronization efficiency.
Fig. 1 is a schematic flow chart of a method 100 provided by an embodiment of the present application. In the following, a method 100 provided by an embodiment of the present application is described with reference to fig. 1, where the method 100 includes:
Step S110, a first synchronization time point and a second synchronization time point are acquired.
The first synchronization time point is a synchronization time point of a source data table in the source database, and indicates that the source data is synchronized to a target data table at the time point; the second synchronization time point is a synchronization time point of the target data table in the target database, and indicates that the target data table performs synchronization of the source data table at the time point. They are a specific point in time, accurate to the order of seconds, for example 15 minutes 30 seconds at day 16 of 10 months 29. The first synchronization time point and the second synchronization time point may be the same or different, and if the first synchronization time point and the second synchronization time point are the same, it is indicated that the source data table and the target data table keep real-time synchronization; if the data are different, the user is stated to restore the source database and/or the target database.
The first synchronization point in time is stored in a table in the source database and the second synchronization point in time is stored in a table in the target database. When the source data table is synchronized to the target data table, the first synchronization time point and the second synchronization time point are updated to the current time point at which synchronization is performed.
Optionally, step S110 includes: the first synchronization point in time and/or the second synchronization point in time are not acquired. The source data table is described as not being synchronized to the target data table, and the source data table is synchronized to the target data table using a full synchronization approach.
Step S120, determining a synchronization time difference value.
The synchronization time difference value is a value obtained by subtracting the second synchronization time point from the first synchronization time point, and may be a positive number, zero or negative number.
When the synchronization time difference is positive, the first synchronization time point is larger than the second synchronization time point, which indicates that the user performs the restore operation on the target database, and accordingly, the target data table is restored.
For example, assuming that the first synchronization time point is 10 months 29 days 17 hours 15 minutes 0 seconds, the second synchronization time point is 10 months 29 days 17 hours 15 minutes 0 seconds, and at 17 hours 15 minutes 3 seconds, the user has restored the target database to 10 months 29 days 17 hours 0 minutes 0 seconds, at which time the second synchronization time point is updated to 10 months 29 days 17 hours 0 minutes 0 seconds, the first synchronization time point remains unchanged, and in this case, the synchronization time difference is 15 minutes 0 seconds, which is a positive number.
When the synchronization time difference is zero, the first synchronization time point is equal to the second synchronization time point, which indicates that the synchronization states of the source data table and the target data table are consistent, and neither the source database nor the target database is restored.
Illustratively, assuming that the first synchronization time point is 10 months 29 days 18 hours 30 minutes 0 seconds, the second synchronization time point is 10 months 29 days 18 hours 30 minutes 0 seconds, and at 18 hours 30 minutes 10 seconds, the source data table is synchronized to the target data table. At this time, the synchronization time difference is zero, and after the synchronization is completed, both the first synchronization time point and the second synchronization time point are updated to 18 hours for 30 minutes and 10 seconds.
When the synchronization time difference is negative, the first synchronization time point is smaller than the second synchronization time point, which indicates that the user performs the restore operation on the source database, and accordingly, the source data table is restored.
Illustratively, assuming that the first synchronization time point is 10 months 29 days 19 hours 20 minutes 15 seconds, the second synchronization time point is 10 months 29 days 19 hours 20 minutes 15 seconds, and at 19 hours 20 minutes 18 seconds, the user has restored the source database to 10 months 29 days 18 hours 50 minutes 0 seconds, at which time the first synchronization time point is updated to 10 months 29 days 18 hours 50 minutes 0 seconds, and the second synchronization time point remains unchanged. In this case, the synchronization time difference is-30 minutes 18 seconds, negative.
Step S130, according to the synchronization time difference, the source data table is synchronized to the target data table by using the full synchronization or the increment synchronization.
According to the database synchronization method provided by the embodiment of the application, the database synchronization equipment firstly acquires the first synchronization time point and the second synchronization time point, then acquires the synchronization time difference value by calculating the difference value of the first synchronization time point and the second synchronization time point, and finally selects an incremental synchronization or full synchronization mode to synchronize the source data table to the target data table according to the synchronization time difference value. According to different synchronization time differences, the source data table is synchronized to the target data table by flexibly selecting an incremental synchronization or full synchronization mode, different synchronization modes can be selected for synchronization according to actual application conditions, and the synchronization efficiency can be improved on the premise that the data of the target data table is consistent with the data of the source data table.
Fig. 2 is a specific step schematic diagram of an alternative implementation of step S130, including:
Step S131, judging whether the synchronization time difference value is more than or equal to 0 and less than or equal to a tracking change time threshold;
If yes, step S132 is executed to synchronize the source data table to the target data table by using the incremental synchronization method.
If not, step S133 is performed to synchronize the source data table to the target data table using the full synchronization scheme.
Wherein the trace change time threshold is a record duration of the trace change function provided by the database, representing a duration of each change data recorded. Altering the type of data includes: deleted data, updated data, and inserted data. The user may set the track change time threshold as desired, e.g., 1 minute, 48 hours, 72 hours. By this track change function, change data in the source data table can be recorded within the set recording time period.
Illustratively, the track change time threshold is set to 48 hours, and the data of a row in the source data table is updated at 10 months 29 days 15 hours 0 minutes 0 seconds, and the row update data is recorded until 10 months 31 days 15 hours 0 minutes 0 seconds. The record of the updated data can be queried at any time in the period from 29 days 15 to 31 days 15, and cannot be queried after 31 days 15.
Full synchronization is the synchronization of all data of a source data table to a target data table.
Optionally, when the synchronization time difference is greater than the track change time threshold or the synchronization time difference is negative, the source data table is synchronized to the target data table by selecting a full synchronization mode.
When the synchronization time difference value is larger than the tracking change time threshold value, the user is informed of performing a restoring operation on the target database, and the state before the tracking change time threshold value is restored. Since the trace change function records the duration of change data to be limited, in which case a portion of the change data is lost and unavailable, a full amount of synchronization is selected.
When the synchronization time difference is negative, the user is indicated to restore the source database. In this case, a full-scale synchronization method is selected.
In both cases, it is determined that it takes more time to change the data resynchronization than to directly perform the full synchronization, so selecting the full synchronization mode can shorten the synchronization time and can ensure that the target data table is consistent with the data in the source data table.
Optionally, the type of full-volume synchronization is configured as forced synchronization.
Specifically, all data of the target data table are deleted first, and then all data in the source data table are inserted into the target data table in batches. Batch insertion may be accomplished through a database-supplied operating tool, such as SqlBulkCopy in SQL SERVER.
Alternatively, the type of full synchronization is not configured, and all data of the source data table is synchronized to the target data table by merging.
Specifically, the merging mode is to keep original data in the target data table, if the primary key value of the target data table is equal to the primary key value of the source data table, the data corresponding to the primary key in the target data table is updated, and if the primary key value of the source data table does not exist in the target data table, the line data corresponding to the primary key value is inserted in the target data table.
If the data of the source data table does not exist in the target data table, inserting the non-existing data into the target data table, if the data in the source data table exists in the target data table, updating if the existing data needs to be updated.
Optionally, the merging mode is realized by a Merge statement.
By means of merging, data of the target data table does not need to be cleared before synchronization, meanwhile, the data consistency of the target data table and the data of the source data table can be guaranteed, data insertion and updating are completed through one-time operation, and synchronization efficiency is improved.
Incremental synchronization is the synchronization of only the change data in the source data table into the target data table, the change data including deleted data, updated data, and inserted data.
Optionally, when the synchronization time difference is greater than or equal to 0 and less than or equal to the tracking time change threshold, the source data table is synchronized to the target data table using incremental synchronization.
Specifically, when the synchronization time difference is greater than 0 and less than or equal to the tracking time change threshold, it is indicated that the user has performed a restore operation on the target database and restored to a state within the tracking time threshold. All the modified data in the source data table is recorded within the tracking time threshold, and it is easy to determine which data has been modified, so that incremental synchronization is performed.
Specifically, when the synchronization time difference is equal to zero, it is indicated that the target data table and the source data table maintain real-time synchronization, and incremental synchronization is performed according to the record of the change data.
In both cases above, the change data is easily obtained. When only data is changed synchronously, the data amount required to be synchronized is greatly reduced, the synchronization time is correspondingly shortened, and the data in the target data table is consistent with the data in the source data table.
Optionally, the incremental synchronization synchronizes the modified data in the source data table to the target data table in the order of deletion, update, insertion.
Specifically, the modified data in the source data table is classified into three sections according to three operation types of deletion, updating and insertion, and then the three sections are operated in series according to the sequence of deletion, updating and insertion. Namely, in the target data table, deleting the data needing to be deleted, updating the data needing to be updated, and finally inserting new data. The data within each segment is manipulated through batch manipulation.
Alternatively, the bulk delete operation is accomplished with a first table variable, which is a temporary table. The first table variable stores primary key information of data deleted in the source data table, and data in the target data table is deleted through the primary key information.
Alternatively, the batch update operation is accomplished with a second table variable, which is another temporary table. The data structure of the second table variable is identical to the source data table, wherein the updated data in the source data table is stored, and the data in the target data table is updated in batches through the data in the second table variable.
Alternatively, the batch insert operation is accomplished by a batch operation tool provided by a database, such as SqlBulkCopy in SQL SERVER.
In the synchronization process, if the change data in the source data table is directly synchronized to the target database, there is a problem of unique index conflict. For example, the unique indexes of the source data table and the target data table are set as ID numbers, a record with ID number 5 is arranged in the source data table, the record is synchronized in the target data table, then the record with ID number 5 is updated in the source data table, the ID number is changed to 6, and a record with ID number 5 is inserted. In the direct synchronization process, if the insertion operation is performed first, the insertion will fail because there is already a record with ID number 5 in the target data table.
The changed data are ordered according to the deleting, updating and inserting modes and then are operated according to the order, so that the problem of data synchronization failure caused by unique index conflict in the synchronization process can be avoided. And the modified data are divided into three sections according to three modes, and batch operation is carried out in each section, so that the synchronization efficiency is greatly improved, and the time required by synchronization is shortened.
Alternatively, if the update operation and the insert operation fail due to an error, a retry is performed by a merging method.
The success rate of synchronization can be improved through the retried fault-tolerant processing, and updating and inserting are completed through one-time operation in a merging mode, so that the synchronization speed is improved.
Optionally, when synchronizing data, the data to be synchronized in the source data table is stored by using a queue, and a multithreading fast consumption mode is used, so that the residence time of the data in the memory is reduced, and the stability is improved.
It should be appreciated that the method 100 provided by the present embodiment is performed once after a certain time interval has elapsed, which is set by the user. If the method is applied to a scene with high requirement on the synchronization precision, the time interval can be set to be 5 seconds, namely, a first synchronization time point and a second synchronization time point are acquired once every 5 seconds, so that the calculation of the synchronization time difference value and the synchronization process are further carried out; if the ageing requirements for the synchronisation are not high, the time interval may be set longer, for example 1 minute.
Optionally, before executing step S110, the method 100 provided in this embodiment further includes the following steps:
Step S101, obtaining the configuration of a source database, a target database, a source data table and a target data table by a user.
By user configuration, it is possible to determine which databases and tables need to be synchronized.
Step S102, whether the change tracking function is in an on state or not is confirmed, and if not, the change tracking function is started.
The implementation of the method provided by the embodiment of the application utilizes the trace modification function, so that the trace modification function needs to be ensured to be in an on state.
Step S103, creating a first table variable and a second table variable.
The first and second table variables are used for batch delete and batch update operations, respectively, in delta synchronization.
Optionally, when there is a newly added source data table in the source database, the source data table is synchronized in full to the target data table.
Optionally, when the source data table has a new field, the data structure of the target data table is updated.
The method 100 provided in this embodiment has high expansibility by synchronizing the newly added source data table and updating the fields of the target data table.
Optionally, the source data table and the target data table each comprise a plurality of source data tables and a plurality of target data tables which are in one-to-one correspondence. Step S110 of the method 100 further includes: acquiring a plurality of first synchronization time points corresponding to a plurality of source data tables and a plurality of second synchronization time points corresponding to a plurality of target data tables; step S120 further includes: determining a plurality of synchronization time differences according to a plurality of corresponding first synchronization time points and a plurality of corresponding second synchronization time points; step S130 further includes: and synchronizing the plurality of source data tables to the plurality of target data tables in a parallel manner according to the plurality of synchronization time differences.
Specifically, there are multiple source data tables in the source database, multiple target databases, and multiple target data tables in each target database. The method 100 provided by the present implementation may be performed in parallel by multiple threads, with one thread performing a synchronization process of a source data table to a target data table. Through the multithreading parallel mode, synchronization from multiple pairs of source data tables to target data tables is simultaneously carried out, so that synchronization time is shortened, and synchronization efficiency and performance are improved.
Illustratively, the method 100 is applied to a device having 4-core processors, where there are 10 source data tables in the source database, and the 4-core processors can process 4 threads simultaneously, and the 10 source data tables can be synchronized by 3 parallel processes.
The method for database synchronization provided by the embodiment of the application is described in detail above with reference to fig. 1 and 2. The database synchronization apparatus provided in the embodiment of the present application is described in detail below with reference to fig. 3. It should be appreciated that the apparatus shown in fig. 3 is capable of implementing one or more of the steps of the method flows shown in fig. 1 and 2. To avoid repetition, details are not repeated here.
Fig. 3 is a schematic view of an apparatus according to an embodiment of the present application, and the apparatus 300 shown in fig. 3 includes:
An obtaining unit 310, configured to obtain a first synchronization time point and a second synchronization time point.
The determining unit 320 obtains the synchronization time difference by determining a difference between the first synchronization time point and the second synchronization time point.
And an execution unit 330, configured to select to synchronize the source data table to the target data table using full synchronization or incremental synchronization according to the synchronization time difference.
Optionally, the execution unit 330 is specifically configured to:
if the synchronization time difference is less than 0, synchronizing the source data table to the target data table in a full-scale synchronization mode;
if the synchronization time difference value is greater than or equal to 0 and less than or equal to the tracking change time threshold value, synchronizing the source data table to the target data table in an incremental synchronization mode;
And if the synchronization time difference value is larger than the tracking change time threshold value, synchronizing the source data table to the target data table by using a full-scale synchronization mode.
Optionally, if the acquiring unit 320 does not acquire the first synchronization time point and the second synchronization time point, the executing unit 330 is further configured to: the source data table is synchronized to the target data table using a full synchronization approach.
Optionally, the source data table is synchronized to the target data table using a full synchronization manner, and the obtaining unit 310 is further configured to: acquiring synchronous configuration information; the determining unit 320 is further configured to: determining whether the type of full synchronization is configured as forced synchronization, if so, the execution unit 330 is further configured to: deleting all data of the target data table, and then inserting all data in the source data table into the target data table in batches; if not, the execution unit 330 is further configured to: all data of the source data table are synchronized to the target data table in a merging mode.
Optionally, the source data table is synchronized to the target data table using incremental synchronization, and the execution unit 330 is further configured to: and synchronizing the change data in the source data table into the target data table according to the operation sequence of deletion, updating and insertion.
Optionally, the execution unit 330 is further configured to: and performing batch deletion operation, batch updating operation and batch insertion operation.
Optionally, the source data table and the target data table each comprise a plurality of source data tables, and the source data tables and the target data tables are in one-to-one correspondence; the acquisition unit 310 is further configured to: acquiring a plurality of first synchronization time points corresponding to a plurality of source data tables and a plurality of second synchronization time points corresponding to a plurality of target data tables; the determining unit 320 is further configured to: determining a plurality of synchronization time differences according to a plurality of corresponding first synchronization time points and a plurality of corresponding second synchronization time points; the execution unit 330 is further configured to: and synchronizing a plurality of source data tables to a plurality of target data tables in a parallel manner according to a plurality of synchronization time differences.
Fig. 4 is a schematic structural diagram of an apparatus for database synchronization according to an embodiment of the present application. The apparatus 400 includes: a processor 410, a memory 420, and a computer program 430 stored in the memory 420 and executable on the processor 410. Processor 410, when executing computer program 430, implements the functions of the various modules in the list page designer embodiment.
The device 400 may be a tablet computer, personal terminal computer, server, or the like. Device 400 may include, but is not limited to, a processor 410, a memory 420. Those skilled in the art will appreciate that fig. 4 is merely an example of the apparatus 400 and does not constitute a limitation of the apparatus 400, and may include more or fewer components than shown, or may combine certain components, or different components, such as: the device 400 should also include an output display device, a communication interface, etc.
The present application also provides a computer readable storage medium comprising a computer program which, when run on a computer, causes the computer to perform the method provided by the method embodiments described above.
The present application also provides a computer program product comprising instructions which, when run on a computer, cause the computer to perform the method provided by the method embodiments described above.
The Processor 410 may be a central processing unit (Central Processing Unit, CPU), but may also be other general purpose processors, digital signal processors (DIGITAL SIGNAL Processor, DSP), application SPECIFIC INTEGRATED Circuit (ASIC), field-Programmable gate array (Field-Programmable GATE ARRAY, FPGA) or other Programmable logic device, discrete gate or transistor logic device, discrete hardware components, or the like. A general purpose processor may be a microprocessor or the processor may be any conventional processor or the like.
The memory 420 may be an internal storage unit of the device 400, such as a hard disk or a memory of the device 400. The memory 420 may also be an external storage device of the device 400, such as a plug-in hard disk, a smart memory card (SMART MEDIA CARD, SMC), a Secure Digital (SD) card, a flash memory card (FLASH CARD) or the like, which are provided on the device 400. Further, the memory 420 may also include both internal storage units and external storage devices of the device 400. The memory 420 is used for storing the computer program and other programs and data required by the terminal device. The memory 420 may also be used to temporarily store data that has been output or is to be output.
Those of ordinary skill in the art will appreciate that the various illustrative elements and algorithm steps described in connection with the embodiments disclosed herein may be implemented as electronic hardware, or combinations of computer software and electronic hardware. Whether such functionality is implemented as hardware or software depends upon the particular application and design constraints imposed on the solution. Skilled artisans may implement the described functionality in varying ways for each particular application, but such implementation decisions should not be interpreted as causing a departure from the scope of the present application.
It will be clear to those skilled in the art that, for convenience and brevity of description, specific working procedures of the above-described systems, apparatuses and units may refer to corresponding procedures in the foregoing method embodiments, and are not repeated herein.
In the several embodiments provided by the present application, it should be understood that the disclosed systems, devices, and methods may be implemented in other manners. For example, the apparatus embodiments described above are merely illustrative, e.g., the division of the units is merely a logical function division, and there may be additional divisions when actually implemented, e.g., multiple units or components may be combined or integrated into another system, or some features may be omitted or not performed. Alternatively, the coupling or direct coupling or communication connection shown or discussed with each other may be an indirect coupling or communication connection via some interfaces, devices or units, which may be in electrical, mechanical or other form.
The units described as separate units may or may not be physically separate, and units shown as units may or may not be physical units, may be located in one place, or may be distributed on a plurality of network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.
In addition, each functional unit in the embodiments of the present application may be integrated in one processing unit, or each unit may exist alone physically, or two or more units may be integrated in one unit.
The functions, if implemented in the form of software functional units and sold or used as a stand-alone product, may be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present application may be embodied essentially or in a part contributing to the prior art or in a part of the technical solution, in the form of a software product stored in a storage medium, comprising several instructions for causing a computer device (which may be a personal computer, a server, a network device, etc.) to perform all or part of the steps of the method according to the embodiments of the present application. And the aforementioned storage medium includes: a U-disk, a removable hard disk, a Read-Only Memory (ROM), a random access Memory (Random Access Memory, RAM), a magnetic disk, or an optical disk, or other various media capable of storing program codes.
The foregoing is merely illustrative of the present application, and the present application is not limited thereto, and any person skilled in the art will readily recognize that variations or substitutions are within the scope of the present application. Therefore, the protection scope of the present application shall be subject to the protection scope of the claims.
Claims (9)
1. A method of database synchronization, comprising:
Acquiring a first synchronization time point and a second synchronization time point, wherein the first synchronization time point is the synchronization time point of a source data table in a source database, and the second synchronization time point is the synchronization time point of a target data table in a target database;
determining a synchronization time difference value, wherein the synchronization time difference value is the difference between the first synchronization time point and the second synchronization time point;
If the synchronization time difference is smaller than 0, synchronizing the source data table to the target data table in a full-scale synchronization mode;
if the synchronization time difference value is greater than or equal to 0 and less than or equal to a tracking change time threshold value, synchronizing the source data table to the target data table in an incremental synchronization mode;
And if the synchronization time difference value is larger than the tracking change time threshold value, synchronizing the source data table to the target data table in a full-scale synchronization mode.
2. The method of claim 1, wherein the acquiring the first synchronization point in time and the second synchronization point in time comprises:
the first synchronization time point and/or the second synchronization time point are not acquired;
the method further comprises the steps of:
The source data table is synchronized to the target data table using a full synchronization approach.
3. The method of claim 1, wherein synchronizing the source data table to the target data table using incremental synchronization comprises:
and synchronizing the change data after the second synchronization time point in the source data table to the target data table according to the operation sequence of deleting, updating and inserting.
4. A method according to claim 3, wherein the delete operation is a bulk delete, the update operation is a bulk update, and the insert operation is a bulk insert.
5. The method according to claim 1 or 2, wherein said synchronizing the source data table to the target data table using full synchronization comprises:
acquiring synchronization configuration information, and determining whether the type of the full synchronization is forced synchronization or not according to the synchronization configuration information;
if yes, deleting all the data of the target data table, and inserting all the data of the source data table into the target data table in batches;
if not, synchronizing all the data of the source data table to the target data table in a merging mode.
6. The method of claim 1, wherein the source data table and the target data table each comprise a plurality of the source data tables and the target data tables in one-to-one correspondence;
The acquiring the first synchronization time point and the second synchronization time point includes:
Acquiring a plurality of first synchronization time points corresponding to a plurality of source data tables, and acquiring a plurality of second synchronization time points corresponding to a plurality of target data tables;
The determining the synchronization time difference value includes:
Determining a plurality of synchronization time differences according to a plurality of corresponding first synchronization time points and a plurality of corresponding second synchronization time points;
after determining the synchronization time difference value, the method comprises the following steps:
And synchronizing the source data tables to the target data tables in a parallel manner according to the synchronization time differences.
7. An apparatus for database synchronization, comprising:
the acquisition unit is used for acquiring a first synchronization time point and a second synchronization time point, wherein the first synchronization time point is the synchronization time point of a source data table in the source database, and the second synchronization time point is the synchronization time point of a target data table in the target database;
a determining unit configured to determine a synchronization time difference value, where the synchronization time difference value is a difference between the first synchronization time point and the second synchronization time point;
the execution unit is used for synchronizing the source data table to the target data table in a full-scale synchronization mode if the synchronization time difference value is smaller than 0;
if the synchronization time difference value is greater than or equal to 0 and less than or equal to a tracking change time threshold value, synchronizing the source data table to the target data table in an incremental synchronization mode;
And if the synchronization time difference value is larger than the tracking change time threshold value, synchronizing the source data table to the target data table in a full-scale synchronization mode.
8. A database synchronization device comprising a memory, a processor and a computer program stored in the memory and executable on the processor, characterized in that the processor implements the method according to any of claims 1 to 6 when executing the computer program.
9. A computer readable storage medium storing a computer program, characterized in that the computer program when executed by a processor implements the method according to any one of claims 1 to 6.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011250392.9A CN112434108B (en) | 2020-11-10 | 2020-11-10 | Database synchronization method, device and equipment |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011250392.9A CN112434108B (en) | 2020-11-10 | 2020-11-10 | Database synchronization method, device and equipment |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN112434108A CN112434108A (en) | 2021-03-02 |
| CN112434108B true CN112434108B (en) | 2024-07-19 |
Family
ID=74699630
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202011250392.9A Active CN112434108B (en) | 2020-11-10 | 2020-11-10 | Database synchronization method, device and equipment |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN112434108B (en) |
Families Citing this family (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112948494B (en) * | 2021-03-04 | 2025-01-14 | 北京沃东天骏信息技术有限公司 | Data synchronization method, device, electronic device and computer readable medium |
| CN113486110B (en) * | 2021-06-18 | 2025-04-11 | 浪潮卓数大数据产业发展有限公司 | A method, device and medium for data synchronization |
| CN114741401B (en) * | 2022-04-02 | 2024-04-26 | 浪潮卓数大数据产业发展有限公司 | Data synchronization method, device and medium based on update time and primary key |
| CN117033516B (en) * | 2023-08-17 | 2025-09-30 | 中国工商银行股份有限公司 | Database synchronization method and device, storage medium and electronic device |
Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101035014A (en) * | 2006-03-07 | 2007-09-12 | 大唐移动通信设备有限公司 | Method for realizing the reverse synchronization in the network management system |
Family Cites Families (15)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US7142878B1 (en) * | 1999-11-12 | 2006-11-28 | Lucent Technologies Inc. | Method of timing calibration |
| EP1107017A3 (en) * | 1999-12-03 | 2003-05-02 | Lucent Technologies Inc. | A method of timing calibration |
| CN102263798B (en) * | 2010-05-25 | 2013-11-20 | 中国移动通信集团甘肃有限公司 | Data synchronization method, device and system for communication systems |
| CN101958934B (en) * | 2010-09-21 | 2015-04-01 | 中兴通讯股份有限公司 | Electronic program guide incremental content synchronization method, device and system |
| US9959178B2 (en) * | 2014-11-25 | 2018-05-01 | Sap Se | Transactional and parallel log replay for asynchronous table replication |
| CN104881494B (en) * | 2015-06-12 | 2019-02-19 | 北京奇虎科技有限公司 | Method, device and system for data synchronization with Redis server |
| CN109784648A (en) * | 2018-12-14 | 2019-05-21 | 北京三快在线科技有限公司 | Scheduling resource distributing method, device, electronic equipment and readable storage medium storing program for executing |
| CN111767495B (en) * | 2019-04-01 | 2024-10-18 | 北京沃东天骏信息技术有限公司 | Method and system for synthesizing web page |
| CN110647579A (en) * | 2019-08-16 | 2020-01-03 | 北京百度网讯科技有限公司 | Data synchronization method and apparatus, computer equipment and readable medium |
| CN110909006B (en) * | 2019-10-15 | 2024-06-11 | 中国平安人寿保险股份有限公司 | Method, device, computer equipment and storage medium for data synchronization |
| CN111143476B (en) * | 2019-12-31 | 2023-06-27 | 杭州趣链科技有限公司 | Intelligent contract data rapid query method based on database |
| CN111245548B (en) * | 2020-01-14 | 2024-08-20 | 平安国际智慧城市科技股份有限公司 | Data synchronization method and device based on time stamp and computer equipment |
| CN111651519B (en) * | 2020-05-08 | 2023-04-25 | 携程计算机技术（上海）有限公司 | Data synchronization method, data synchronization device, electronic equipment and storage medium |
| CN111708841B (en) * | 2020-06-08 | 2024-01-23 | 中国工商银行股份有限公司 | Database data synchronization method and device |
| CN111881210B (en) * | 2020-06-29 | 2022-08-26 | 平安国际智慧城市科技股份有限公司 | Data synchronization method, device, intranet server and medium |
-
2020
- 2020-11-10 CN CN202011250392.9A patent/CN112434108B/en active Active
Patent Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101035014A (en) * | 2006-03-07 | 2007-09-12 | 大唐移动通信设备有限公司 | Method for realizing the reverse synchronization in the network management system |
Also Published As
| Publication number | Publication date |
|---|---|
| CN112434108A (en) | 2021-03-02 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN112434108B (en) | Database synchronization method, device and equipment | |
| US9953051B2 (en) | Multi-version concurrency control method in database and database system | |
| US6694337B1 (en) | Synchronizing databases | |
| CN102110121B (en) | A kind of data processing method and system thereof | |
| US9183268B2 (en) | Partition level backup and restore of a massively parallel processing database | |
| CN108509462B (en) | Method and device for synchronizing activity transaction table | |
| US20220405178A1 (en) | Distributed database remote backup | |
| CN108255909B (en) | Data table backup method and server based on Oracle database | |
| US20110295796A1 (en) | Synchronization of subsets of data including support for varying set membership | |
| US10467192B2 (en) | Method and apparatus for updating data table in keyvalue database | |
| CN113010608A (en) | Data real-time synchronization method and device and computer readable storage medium | |
| CN106325933A (en) | Method and device for synchronizing batch data | |
| CN110046172B (en) | Online computing data processing method and system | |
| US20150006485A1 (en) | High Scalability Data Management Techniques for Representing, Editing, and Accessing Data | |
| US9026493B1 (en) | Multi-master RDBMS improvements for distributed computing environment | |
| CN115098519A (en) | Data storage method and device | |
| US20210042279A1 (en) | History of slowly changing dimensions | |
| CN119336837A (en) | Data synchronization method, system, storage medium and electronic device | |
| US10901854B2 (en) | Temporal logical transactions | |
| CN109960602B (en) | Information management method, device, equipment and medium | |
| CN113656380B (en) | Data lifecycle management method and device | |
| CN114996210A (en) | Data processing method, data processing device, computer equipment and storage medium | |
| CN117633107A (en) | Cross-platform heterogeneous data synchronization method and device | |
| CN110147355B (en) | Data synchronization method, device and server | |
| CN116340430A (en) | Data synchronization verification method and related equipment |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |