CN116521692A - Data synchronization method and device, electronic equipment and storage medium - Google Patents

Abstract

One or more embodiments of the present disclosure provide a data synchronization method and apparatus, an electronic device, and a storage medium, where the method includes: creating a trigger, wherein the trigger is used for synchronizing the data change of the source table to the target table and recording the identification of the deleted data in the synchronization process in a deletion log; synchronizing data within the source table to the target table; and processing the data in the target table according to the identification in the deletion log. The trigger is used for performing incremental synchronization between the source table and the target table, and the source table is not locked in the process of performing full synchronization on the target table by the source table, namely the full synchronization process does not influence service processing; by deleting the log to record the deleted data in the incremental synchronization, the result of the incremental synchronization (for example, the deleted data in the incremental synchronization is added into the target table in the incremental synchronization) can be prevented from being covered by the full synchronization, so that the data in the target table is ensured to be effective and the latest business processing result.

Description

Data synchronization method and device, electronic equipment and storage medium

technical field

One or more embodiments of this specification relate to the technical field of databases, and in particular, to a data synchronization method and device, electronic equipment, and a storage medium.

Background technique

Today, with the rapid development of the Internet and information technology, the generation of data is growing explosively, so the requirements for databases and their management are getting higher and higher. There are a large number of data tables stored in the database, and the table structure in the database often changes in the case of application upgrades and business changes. When the table structure in the database changes, the data needs to be synchronized, and the table lock is often required during the data synchronization process, that is, concurrent DML (Data Manipulation Language, Data Manipulation Language) is not allowed, which affects business processing and generates business request timeouts and other business issues.

Contents of the invention

In view of this, one or more embodiments of this specification provide a data synchronization method and device, electronic equipment, and a storage medium.

In order to achieve the above purpose, one or more embodiments of this specification provide technical solutions as follows:

According to a first aspect of one or more embodiments of this specification, a data synchronization method is proposed, the method comprising:

Create a trigger, wherein the trigger is used to synchronize the data change of the source table to the target table, and record the identifier of the deleted data in the synchronization process in the deletion log;

synchronizing the data in the source table to the target table;

The data in the target table is processed according to the identifier in the deletion log.

In one embodiment of this specification, the creation trigger includes:

Create an Insert trigger, a Delete trigger, and an Update trigger, wherein the Insert trigger is used to synchronize the data insertion event that occurs in the source table to the target table, and the Delete trigger is used to synchronize the data insertion event that occurs in the source table. The data deletion event of the source table is synchronized to the target table, and the Update trigger is used to synchronize the data update event of the source table to the target table.

In an embodiment of this specification, the Insert trigger is also used to delete the identifier of the data inserted in the data insertion event in the deletion log.

In an embodiment of this specification, the Delete trigger is further used to add an identifier of the data deleted in the data deletion event to the deletion log.

In an embodiment of this specification, the Update trigger is also used to add the identifier of the data before updating in the data update event to the delete log, and delete the data after updating in the data update event logo.

In an embodiment of this specification, the processing of the data in the target table according to the identification of the data in the deletion log includes:

Delete the data in the target table identified as the identifier in the deletion log.

In one embodiment of this specification, the synchronizing the data in the source table to the target table includes:

Divide the data in the source table according to the preset size of the data synchronization block to obtain multiple data synchronization blocks;

A synchronization task to be synchronized to the target table is constructed for each data synchronization block in the plurality of data synchronization blocks, and a task queue is generated from the obtained plurality of synchronization tasks.

In an embodiment of the present specification, the source table includes multiple partition tables, and each partition table is stored in a partition of the distributed database;

The data in the source table is divided according to the preset size of the data synchronization block to obtain multiple data synchronization blocks, including:

Determine the identification upper limit and identification lower limit of the data in each partition table respectively;

For each partition table, divide the data in the partition table according to the upper and lower identification limits of the partition table and the preset size to obtain at least one data synchronization block corresponding to the partition table.

In one embodiment of this specification, the target table includes multiple partition tables, and each partition table is stored in a partition of the distributed database;

The method also includes:

For each data synchronization block, according to the partition table corresponding to each data in the data synchronization block in the target table, divide the data synchronization block to obtain at least one data sub-synchronization block corresponding to the data synchronization block ;

The constructing a synchronization task for synchronizing to the target table for each data synchronization block in the plurality of data synchronization blocks includes:

A synchronization task for synchronizing to the target table is constructed for each data sub-synchronization block in the plurality of data sub-synchronization blocks.

In one embodiment of the specification, the method further includes:

According to the table structure change instruction, a shadow table of the source table is created and the structure of the shadow table is modified to obtain the target table.

In one embodiment of the specification, the method further includes:

In response to the data in the target table satisfying a synchronization condition with respect to the data in the source table, switch the table names of the source table and the target table.

In one embodiment of this specification, the switching of the table names of the source table and the target table includes:

Determining the number of sessions between the service layer and the source table in response to the current time relative to the initial time or the last session statistics time reaching a preset time length, wherein the initial time includes the data in the target table relative to When the data in the source table meets the synchronization condition;

In response to the number of sessions between the business layer and the source table being less than the number threshold, killing all sessions between the business layer and the source table and switching the table names of the source table and the target table.

In one embodiment of the specification, the method further includes:

In response to the number of sessions counted reaching the number threshold, and the number of sessions between the business layer and the source table in each session counted result is not less than the number threshold, it is determined that the table structure modification fails.

According to a second aspect of one or more embodiments of the present specification, a data synchronization device is proposed, the device comprising:

An incremental synchronization module, configured to create a trigger, wherein the trigger is used to synchronize the data changes of the source table to the target table, and record the identifier of the deleted data in the synchronization process in the deletion log;

A full synchronization module, configured to synchronize data in the source table to the target table;

A processing module, configured to process the data in the target table according to the identifier in the deletion log.

In one embodiment of this specification, the incremental synchronization module is used for:

Create an Insert trigger, a Delete trigger, and an Update trigger, wherein the Insert trigger is used to synchronize the data insertion event that occurs in the source table to the target table, and the Delete trigger is used to synchronize the data insertion event that occurs in the source table. The data deletion event of the source table is synchronized to the target table, and the Update trigger is used to synchronize the data update event of the source table to the target table.

In an embodiment of this specification, the Insert trigger is also used to delete the identifier of the data inserted in the data insertion event in the deletion log.

In an embodiment of this specification, the Delete trigger is further used to add an identifier of the data deleted in the data deletion event to the deletion log.

In an embodiment of this specification, the Update trigger is also used to add the identifier of the data before updating in the data update event to the delete log, and delete the data after updating in the data update event logo.

In one embodiment of this specification, the processing module is used for:

Delete the data in the target table identified as the identifier in the deletion log.

In one embodiment of this specification, the full amount synchronization module is used for:

Divide the data in the source table according to the preset size of the data synchronization block to obtain multiple data synchronization blocks;

A synchronization task to be synchronized to the target table is constructed for each data synchronization block in the plurality of data synchronization blocks, and a task queue is generated from the obtained plurality of synchronization tasks.

In an embodiment of the present specification, the source table includes multiple partition tables, and each partition table is stored in a partition of the distributed database;

The full synchronization module is used to divide the data in the source table according to the preset size of the data synchronization block, and when multiple data synchronization blocks are obtained, it is specifically used for:

Determine the identification upper limit and identification lower limit of the data in each partition table respectively;

For each partition table, divide the data in the partition table according to the upper and lower identification limits of the partition table and the preset size to obtain at least one data synchronization block corresponding to the partition table.

In one embodiment of this specification, the target table includes multiple partition tables, and each partition table is stored in a partition of the distributed database;

The full synchronization module is also used for:

For each data synchronization block, according to the partition table corresponding to each data in the data synchronization block in the target table, divide the data synchronization block to obtain at least one data sub-synchronization block corresponding to the data synchronization block ;

When the full synchronization module is used to construct a synchronization task for each data synchronization block in the plurality of data synchronization blocks to synchronize to the target table, it is specifically used for:

A synchronization task for synchronizing to the target table is constructed for each data sub-synchronization block in the plurality of data sub-synchronization blocks.

In one embodiment of the specification, the device further includes a structure modification module, configured to:

According to the table structure change instruction, a shadow table of the source table is created and the structure of the shadow table is modified to obtain the target table.

In one embodiment of this specification, the device also includes a table name switching module, which is used for:

In response to the data in the target table satisfying a synchronization condition with respect to the data in the source table, switch the table names of the source table and the target table.

In one embodiment of this specification, the table name switching module is specifically used for:

Determining the number of sessions between the service layer and the source table in response to the current time relative to the initial time or the last session statistics time reaching a preset time length, wherein the initial time includes the data in the target table relative to When the data in the source table meets the synchronization condition;

In response to the number of sessions between the business layer and the source table being less than the number threshold, killing all sessions between the business layer and the source table and switching the table names of the source table and the target table.

In one embodiment of this specification, the table name switching module is also used for:

In response to the number of sessions counted reaching the number threshold, and the number of sessions between the business layer and the source table in each session counted result is not less than the number threshold, it is determined that the table structure modification fails.

According to a third aspect of one or more embodiments of the present specification, an electronic device is provided, including:

processor;

memory for storing processor-executable instructions;

Wherein, the processor implements the method according to the first aspect by running the executable instruction.

According to a fourth aspect of one or more embodiments of the present specification, a computer-readable storage medium is provided, on which computer instructions are stored, and when the instructions are executed by a processor, the steps of the method described in the first aspect are implemented.

The technical solutions provided by the embodiments of this specification may include the following beneficial effects:

The data synchronization method provided by the embodiment of this specification first creates a trigger, which can synchronize the data change to the target table when the data in the source table changes, and record the identifier of the data deleted during the synchronization process in the deletion log Next, the data in the source table is synchronized to the target table, and finally the data in the target table is processed according to the identifier in the deletion log. In this method, the trigger is used to perform incremental synchronization between the source table and the target table, and the source table is not locked during the process of full synchronization from the source table to the target table, that is, the full synchronization process will not affect business processing; moreover By deleting the log to record the deleted data in the incremental synchronization, it is possible to prevent the full synchronization from covering the results of the incremental synchronization (for example, the data deleted during the incremental synchronization is added to the target table during the full synchronization), ensuring the goal The data in the table is valid and is the latest business processing result.

Description of drawings

Fig. 1 is a flowchart of a data synchronization method provided by an exemplary embodiment.

Fig. 2 is a schematic diagram of an operation of a trigger on deleting a log in a data synchronization method provided by an exemplary embodiment.

Fig. 3 is a schematic diagram of a full synchronization process in a data synchronization method provided by an exemplary embodiment.

Fig. 4 is a schematic diagram of a table structure modification method provided by an exemplary embodiment.

Fig. 5 is a schematic structural diagram of a device provided by an exemplary embodiment.

Fig. 6 is a block diagram of a data synchronization device provided by an exemplary embodiment.

Detailed ways

Reference will now be made in detail to the exemplary embodiments, examples of which are illustrated in the accompanying drawings. When the following description refers to the accompanying drawings, the same numerals in different drawings refer to the same or similar elements unless otherwise indicated. Implementations described in the following exemplary embodiments do not represent all implementations consistent with one or more embodiments of this specification. Rather, they are merely examples of apparatuses and methods consistent with aspects of one or more embodiments of the present specification as recited in the appended claims.

It should be noted that in other embodiments, the steps of the corresponding methods are not necessarily performed in the order shown and described in this specification. In some other embodiments, the method may include more or less steps than those described in this specification. In addition, a single step described in this specification may be decomposed into multiple steps for description in other embodiments; multiple steps described in this specification may also be combined into a single step in other embodiments describe.

Today, with the rapid development of the Internet and information technology, the generation of data is growing explosively, so the requirements for databases and their management are getting higher and higher. There are a large number of data tables stored in the database, and the table structure in the database often changes when the application is upgraded, the business is changed, and the data ratio of the partition table is very different. When the table structure in the database changes, the data needs to be synchronized, and the table lock is often required during the data synchronization process, that is, concurrent DML (Data Manipulation Language, data manipulation language) is not allowed, which affects business processing and causes business request timeouts, etc. business problem.

Based on this, in the first aspect, at least one embodiment of this specification provides a data synchronization method, which can perform data synchronization between the source table and the target table when the structure of the database table is changed, and the source table before the structure change can be stored In a centralized or distributed database, the target table after structure change can also be stored in the centralized or distributed database. Data synchronization can include full synchronization and incremental synchronization. Full synchronization refers to the synchronization of all data in the source table to For the target table, incremental synchronization refers to the synchronization of data changes on the source table to the target table during the full synchronization process. This method does not need to lock the source table in the process of full synchronization, that is, the business layer can perform business data operations on the source table; and incremental synchronization can be synchronized in real time, that is, the source table will be synchronized to the target table immediately after the data changes ;Furthermore, although full synchronization and incremental synchronization do not limit the timing, the data in the target table is the latest business processing result after data synchronization is completed by this method, and there will be no problem that full synchronization overwrites incremental synchronization and the data is old data .

Please refer to Fig. 1 , which exemplarily shows the flow of the method, including step S101 to step S103.

In step S101, a trigger is created, wherein the trigger is used to synchronize the data change of the source table to the target table, and record the identifier of the deleted data in the deletion log in the synchronization process.

Among them, trigger (trigger) is a business rule to ensure data integrity. It is a special stored procedure related to table events. Its execution is not called by a program or manually started, but triggered by an event. For example, when an operation on a table causes its data to change, it will be activated to execute.

The trigger created in this step is used to ensure the data integrity of the target table. Since the target table is a table used to synchronize data in the source table, and the operation of the business layer is for the source table, it can also be said that the trigger in this step The device is used to ensure data consistency between the target and source tables. Among them, the trigger is triggered when the data of the source table changes due to the operation of the business layer, and the data changes of the source table are synchronized to the target table, so that the target table has the same data changes as the source table, thereby ensuring the relationship between the two consistency. The trigger will cause some data in the target table to be deleted during the process of synchronizing the data changes of the source table to the target table. If the incremental synchronization performed by the trigger occurs before the full synchronization, these data may be deleted during the full synchronization. The deleted data is re-added to the target table. In order to avoid this situation, the trigger can be used to record the identification of the deleted data in advance, so as to further check the target table after full synchronization.

It can be understood that the data in the source table is in the form of multiple rows and multiple columns, where each column is called a field, and each row is called a data row. A primary key and/or a unique key may exist in multiple fields of data in the source table, and a primary key or a certain unique key may be pre-defined as an identifier of a data row. The data changes in the source table can occur in units of data rows, and the identifier of each data row can be the above-mentioned pre-defined primary key or a unique key in the row.

Exemplarily, the data change of the source table may include data insertion events (such as inserting a certain data row into the source table), data deletion events (such as deleting a certain data row in the source table) and data update events (such as inserting The ID of a data row in the source table is updated to another ID). Therefore, creating a trigger in this step may include: creating an Insert trigger, a Delete trigger, and an Update trigger.

Wherein, the Delete trigger is used to synchronize the data deletion event occurring in the source table to the target table, that is, to delete the data deleted in the data deletion event synchronously in the target table. In addition, the Delete trigger is also used to add the identifier of the data deleted in the data deletion event to the deletion log. For example, the code for a Delete trigger could be:

In the above code, A is the source table, A_GHO is the target table, and DELETE_LOG is the delete log.

Wherein, the Insert trigger is used for synchronizing the data insertion event occurring in the source table to the target table, that is, synchronously inserting the data inserted in the data insertion event into the target table. In addition, the Insert trigger is also used to delete the identifier of the data inserted in the data insertion event in the deletion log, that is, if the identifier of the data inserted in the data insertion event exists in the deletion log, then Delete it. If the identifier of the data inserted in the data insertion event does not exist in the deletion log, the deletion operation does not need to be performed. In other words, if the data inserted in the data insertion event is data that existed in the source table before and has been deleted, its identifier is recorded in the deletion log, and this time the data is re-inserted, the latest business processing result The data exists in the source table, so its identity no longer needs to be recorded in the delete log. For example, the code for an Insert trigger could be:

In the above code, A is the source table, and A_GHO is the target table.

Wherein, the Update trigger is used to synchronize the data update event that occurs in the source table to the target table, that is, the data before updating in the data update event is synchronously deleted in the target table, and the updated data in the data update event Insert synchronously into the target table. In addition, the Update trigger is further configured to add, in the deletion log, the identifier of the data before update in the data update event, and delete the identifier of the data after update in the data update event. It can be understood that the data update event can be equivalent to a delete event for the data before the update and an insert event for the updated data, so the data before the update can be The identifier is added to the deletion log, and the identifier of the updated data is deleted from the deletion log. For example, the code for an Update trigger could be:

In the above code, A is the source table, A_GHO is the target table, and DELETE_LOG is the delete log.

Among the above three triggers, when the Delete trigger and the Insert trigger are triggered, it must be that the identity of a data row in the source table (that is, the primary key or unique key) has changed, for example, when the Delete trigger is triggered, a certain data When a row is deleted, its identifier is also deleted. For example, when a certain data row is inserted when an Insert trigger is triggered, its identifier is also inserted.

When the Update trigger is triggered, it must be that a data row in the source table has changed, but it does not necessarily mean that the identity of the data row (that is, the primary key or unique key) has changed, but only when the identity of the data row changes. Only in the deletion log, the server needs to add the identification of the data before the update in the data update event, and delete the identification of the updated data in the data update event; when the identification of the data row does not change, due to The incremental synchronization performed by the trigger on the target table uses Replace into to insert the updated row data (the row data includes the content of the identifier and other fields), while the full synchronization uses Insert ignore to insert the row data before the update, that is, if you want to insert If the identifier of the row data already exists in the target table, the data row will not be inserted. Therefore, if the full synchronization occurs first and the incremental synchronization occurs later, the result of the incremental synchronization will overwrite the result of the full synchronization. If the incremental If synchronization occurs before and full synchronization occurs later, full synchronization will not occur, that is, the result of incremental synchronization will not be overwritten, and the result of incremental synchronization will be maintained.

Please refer to Figure 2, which vividly shows the operation of the above three triggers on the delete log (DELETE_LOG). Deleting the log can avoid locking the source table, especially the data synchronization during the table structure change process of the distributed database. It can avoid locking the source table, so that the normal processing of the business will not be affected when the table structure is changed.

In step S102, the data in the source table is synchronized to the target table.

This step is used to complete the full synchronization from the source table to the target table. Since the source table is not locked (that is, a shared lock is added) during the full synchronization process, if the business layer performs operations on the source table during the full synchronization process, the source table will be triggered. The data of the table changes, and the trigger constructed in step S101 is triggered to perform incremental synchronization. It is understandable that the order of full synchronization and incremental synchronization of a data row is uncertain. It is possible that the full synchronization occurs before the incremental synchronization occurs, for example, after a data row of the source table is fully synchronized to the target table The operation of the business layer causes the change of the data row, and then the trigger performs incremental synchronization on the data row; it is also possible that the incremental synchronization occurs before the full synchronization occurs, for example, when a data row of the source table is read when it is fully synchronized When it is stored in the memory but not yet synchronized to the target table, the operation of the business layer causes the change of the data row, and then the trigger performs incremental synchronization on the data row, and then the data in the memory before the update of the data row is synchronized to the target table.

Next, with reference to FIG. 3 , an exemplary and detailed introduction will be given to the full synchronization process in this step.

First, divide the data in the source table according to the preset size of the data synchronization block to obtain multiple data synchronization blocks.

Wherein, the preset size of the data synchronization block may be represented by the number N of data rows.

Optionally, if the source table is stored in a centralized database, you can first determine the upper limit and lower limit of the identity of the source table, for example, sort the primary key or unique key used as the identity in the source table, and then determine the upper limit and lower limit of the identity based on the sorting result Identify the lower limit; then, according to the upper limit and lower limit of the identification and the number N of data rows in the data synchronization block, all the data rows in the source table can be divided to obtain multiple data synchronization blocks, and each data synchronization block has N data OK. For example, the primary key id in the source table is used as the identifier of the data row. After sorting, the primary key id of the data row in the source table ranges from 1 to 100, and the default size of the data synchronization block is 10 data rows. Therefore, after division, the Data synchronization block 1 of primary key id1 to 10, data synchronization block 2 of primary key id11 to 20, data synchronization block 3 of primary key id21 to 30, data synchronization block 4 of primary key id31 to 40, data synchronization block 5 of primary key id41 to 50, Data synchronization block 6 of primary key id51 to 60, data synchronization block 7 of primary key id61 to 70, data synchronization block 8 of primary key id71 to 80, data synchronization block 9 of primary key id81 to 90, and data synchronization block 10 of primary key id91 to 100 Ten data sync blocks.

Optionally, if the source table is stored in a distributed database, that is, the source table includes multiple partition tables, and each partition table is stored in a partition of the above-mentioned distributed database, the data in each partition table can be determined first. Identification upper limit and identification lower limit, such as sorting the primary key or unique key as the identification in the partition table, and then determining the identification upper limit and identification lower limit based on the sorting result; and then for each partition table, according to the identification upper limit and identification of the partition table The lower limit and the preset size divide the data in the partition table to obtain at least one data synchronization block corresponding to the partition table. For example, the primary key id in the source table is used as the identifier of the data row. The source table includes partition table 1 and partition table 2. The primary key id of the data row in partition table 1 is sorted from 1 to 100, and partition table 2 is sorted. The primary key id of the data row is from 101 to 200, and the preset size of the data synchronization block is 10 data rows, so after division, the data synchronization block 1 corresponding to the primary key id1 to 10 of the partition table 1 and the data synchronization of the primary key id11 to 20 are obtained Block 2, data synchronization block 3 of primary key id21 to 30, data synchronization block 4 of primary key id31 to 40, data synchronization block 5 of primary key id41 to 50, data synchronization block 6 of primary key id51 to 60, data synchronization of primary key id61 to 70 Block 7, data synchronization block 8 of primary key id71 to 80, data synchronization block 9 of primary key id81 to 90, data synchronization block 10 of primary key id91 to 100, a total of ten data synchronization blocks, and the corresponding primary key id101 to 110 of partition table 2 Data synchronization block 11, data synchronization block 12 of primary key id111 to 120, data synchronization block 13 of primary key id121 to 130, data synchronization block 14 of primary key id131 to 140, data synchronization block 15 of primary key id141 to 150, and data synchronization block of primary key id151 to 160 Data synchronization block 16, data synchronization block 17 of primary key id161 to 170, data synchronization block 18 of primary key id171 to 180, data synchronization block 19 of primary key id181 to 190, data synchronization block 20 of primary key id191 to 200, a total of ten data synchronization blocks .

In addition, it can be understood that if the source table is stored in the distributed database, the target table can also be stored in the distributed database, that is, the target table includes multiple partition tables, and each partition table is stored in a partition of the distributed database . In this case, for each data synchronization block, the data synchronization block can be further divided according to the partition table corresponding to each data in the data synchronization block in the target table, and the data synchronization block corresponding to the data synchronization block can be obtained. At least one data sub-sync block. For example, in the primary key id1-10 of a data synchronization block, primary keys id1, 2, 3, and 6 correspond to partition table 1 in the target table, primary keys id4, 5, and 7 correspond to partition table 2 in the target table, primary keys id8, 9 and 10 correspond to partition table 3 in the target table, so the data synchronization block is further divided to obtain data sub-synchronization block 1 composed of primary keys id1, 2, 3, and 6, and data sub-synchronization composed of primary keys id4, 5, and 7 The data sub-synchronization block 3 composed of block 2 and primary keys id8, 9, and 10 has three data sub-synchronization blocks in total.

Next, a synchronization task to be synchronized to the target table is constructed for each data synchronization block in the plurality of data synchronization blocks, and a task queue is generated from the obtained plurality of synchronization tasks.

Exemplarily, the production end puts data synchronization blocks from the source table into the task queue, and the consumer end removes the data synchronization blocks from the queue and adds them to the target table at the same time, thereby improving the efficiency of full synchronization. When the task queue is executed, multiple synchronization tasks can be executed at the same time, which can also improve the efficiency of full synchronization.

It can be understood that if the target table is stored in a distributed database, and the data synchronization block is further divided into data sub-synchronization blocks, then in this step, the Synchronize to the synchronization task of the target table, and generate a task queue from the obtained multiple synchronization tasks.

In the above full synchronization process, the source table is divided or even further divided to obtain multiple data synchronization blocks or data sub-synchronization blocks, and then a synchronization task is constructed for each data synchronization block or data sub-synchronization block to generate a task queue, so it can be Synchronize different data in the source table in parallel, and the production and consumption of task queues also have a parallel effect, thereby improving the efficiency of full synchronization, especially for full data synchronization in the process of changing the table structure of a distributed database, which can greatly improve The efficiency of full synchronization.

In step S103, the data in the target table is processed according to the identifier in the deletion log.

This step is used to correct the data rows whose full synchronization results cover the incremental synchronization results, so that these data rows can restore the incremental synchronization results, that is, keep the latest business processing results, and make the target table consistent with the source table.

Exemplarily, the data identified in the target table as the identifier in the deletion log may be deleted. It can be understood that if there is an identifier in the delete log in the target table, the data row described by the identifier in the target table will be deleted. If there is no identifier in the delete log in the target table, there is no need to execute the corresponding delete operation. For example, the data row whose primary key id is n in the source table has changed data, so the identifier n is recorded in the delete log, and the data row whose primary key is n performs incremental synchronization first and then performs full synchronization, that is When performing incremental synchronization, because there is no data row identified as n in the target table, the incremental synchronization is not performed, and the subsequent full synchronization will add the data row identified as n to the target table, that is, the incremental synchronization is overwritten In this step, because the identifier n is recorded in the deletion log, the data row with the identifier n in the target table is deleted, so that the incremental synchronization result is restored and is not overwritten by the full synchronization result.

Please refer to accompanying drawing 2, which vividly shows the operation of the replay thread pool (replay thread pool) on the target table (A_GHO) according to the delete log.

The data synchronization method provided by the embodiment of this specification first creates a trigger, which can synchronize the data change to the target table when the data in the source table changes, and record the identifier of the data deleted during the synchronization process in the deletion log Next, the data in the source table is synchronized to the target table, and finally the data in the target table is processed according to the identifier in the deletion log. In this method, the trigger is used to perform incremental synchronization between the source table and the target table, and the source table is not locked during the process of full synchronization from the source table to the target table, that is, the full synchronization process will not affect business processing; moreover By deleting the log to record the deleted data in the incremental synchronization, it is possible to prevent the full synchronization from covering the results of the incremental synchronization (for example, the data deleted during the incremental synchronization is added to the target table during the full synchronization), ensuring the goal The data in the table is valid and is the latest business processing result.

The data synchronization in the foregoing embodiment can be the data synchronization from the old table (i.e. the source table) to the new table (i.e. the target table) when the table structure of the database changes, so please refer to the table structure change process shown in accompanying drawing 4, In some embodiments of the present disclosure, before the above data synchronization, the shadow table of the source table can be created according to the table structure change instruction and the structure of the shadow table can be modified to obtain the target table; after the above data synchronization can be judging whether the data in the target table satisfies the synchronization condition relative to the data in the source table, and in response to the data in the target table satisfying the synchronization condition relative to the data in the source table, switch the source table and the target table The table name of the table, so as to complete the change of the table structure in the database.

Exemplarily, the modification of the structure of the shadow table may include adding fields, deleting fields, repartitioning the partition table, etc.; the repartitioning of the partition table can be performed based on the identification of the row data, and the minimum row data can be synchronized in the data as far as possible Cross-area synchronization occurs, for example, the primary key of data rows in partition table 1 of the source table is 1-100, the primary key of data rows in partition table 2 is 101-200, and the primary key of data rows in partition table 1 of the target table is 1-50. The primary key of the data row in Table 2 is 151-200.

Exemplarily, to judge whether the data in the target table satisfies the synchronization condition with respect to the data in the source table, it can be judged whether the data in the target table is consistent with the data in the source table, and the inconsistent data rows are filtered out. The data row is a data row where incremental synchronization occurs, and the content in the data row is consistent with the incremental synchronization, then it is determined that the data in the target table satisfies the synchronization condition relative to the data in the source table.

Exemplarily, when switching the table names of the source table and the target table, it may first respond to the fact that the duration of the current moment relative to the initial moment or the last session statistics moment reaches a preset duration (this time is the session statistics time), determine the number of sessions (session) between the business layer and the source table, wherein the initial time includes the time when the data in the target table meets the synchronization condition with respect to the data in the source table; then In response to the number of sessions between the business layer and the source table being less than the number threshold, kill all sessions between the business layer and the source table (kill session) and switch the table names of the source table and the target table . Since the business layer may continue to write data to the source table when the table name is switched, especially since the table structure synchronization of the distributed database is not atomic, the business layer is more likely to continue to write data to the source table when the table name is switched , so in this example, all sessions between the business layer and the source table are killed when the table name is switched, which prevents the business layer from continuing to write data to the source table and cause data loss; and the switch table name is between the business layer and the source table It is executed when the number of sessions between source tables is less than the number threshold, which can minimize the impact of switching table names on business processing; moreover, the method in this example does not lock the source table, which reduces the use of shared locks and further reduces The impact of switching table names on business processing.

In addition, the number of times threshold can also be set in advance, and in response to the number of times of session statistics reaching the number of times threshold, and the number of sessions between the business layer and the source table in the result of each session statistics is not less than the number threshold, determine the number of times The table structure change failed. This is because, if the number of sessions is judged to be greater than the number threshold for many times, the business layer has more business at this time, so at this time, killing the session to switch the table name will seriously affect business processing, so you can remind the user that the table structure change failed , wait until the business is less and then restart the table structure change through the operation.

The table structure change method shown in Figure 4 above does not lock the table when the table structure is changed, provides more efficient full data synchronization, and ensures that data will not be lost during incremental synchronization and table name switching, and will not affect the production environment business. In particular, this table method is suitable for table structure changes in distributed databases. When the structure changes, no table lock occurs, full synchronization is efficient, and incremental synchronization and table name switching can ensure that data will not be lost and will not affect the business of the production environment.

Fig. 5 is a schematic structural diagram of a device provided by an exemplary embodiment. Please refer to FIG. 5 , at the hardware level, the device includes a processor 502 , an internal bus 504 , a network interface 506 , a memory 508 and a non-volatile memory 510 , and of course may include hardware required by other tasks. One or more embodiments of this specification may be implemented based on software, for example, the processor 502 reads a corresponding computer program from the non-volatile memory 510 into the memory 508 and executes it. Of course, in addition to software implementations, one or more embodiments of this specification do not exclude other implementations, such as logic devices or a combination of software and hardware, etc., that is to say, the execution subject of the following processing flow is not limited to each A logic unit, which can also be a hardware or logic device.

Please refer to FIG. 6 , the data synchronization device can be applied to the device shown in FIG. 5 to realize the technical solution of this specification. Wherein, the data synchronization device may include:

The incremental synchronization module 601 is used to create a trigger, wherein the trigger is used to synchronize the data changes of the source table to the target table, and record the identifier of the deleted data in the synchronization process in the deletion log;

A full synchronization module 602, configured to synchronize the data in the source table to the target table;

The processing module 603 is configured to process the data in the target table according to the identifier in the deletion log.

In one embodiment of this specification, the incremental synchronization module is used for:

Create an Insert trigger, a Delete trigger, and an Update trigger, wherein the Insert trigger is used to synchronize the data insertion event that occurs in the source table to the target table, and the Delete trigger is used to synchronize the data insertion event that occurs in the source table. The data deletion event of the source table is synchronized to the target table, and the Update trigger is used to synchronize the data update event of the source table to the target table.

In an embodiment of this specification, the Insert trigger is also used to delete the identifier of the data inserted in the data insertion event in the deletion log.

In an embodiment of this specification, the Delete trigger is further used to add an identifier of the data deleted in the data deletion event to the deletion log.

In an embodiment of this specification, the Update trigger is also used to add the identifier of the data before updating in the data update event to the delete log, and delete the data after updating in the data update event logo.

In one embodiment of this specification, the processing module is used for:

Delete the data in the target table identified as the identifier in the deletion log.

In one embodiment of this specification, the full amount synchronization module is used for:

Divide the data in the source table according to the preset size of the data synchronization block to obtain multiple data synchronization blocks;

A synchronization task to be synchronized to the target table is constructed for each data synchronization block in the plurality of data synchronization blocks, and a task queue is generated from the obtained plurality of synchronization tasks.

In an embodiment of the present specification, the source table includes multiple partition tables, and each partition table is stored in a partition of the distributed database;

The full synchronization module is used to divide the data in the source table according to the preset size of the data synchronization block, and when multiple data synchronization blocks are obtained, it is specifically used for:

Determine the identification upper limit and identification lower limit of the data in each partition table respectively;

For each partition table, divide the data in the partition table according to the upper and lower identification limits of the partition table and the preset size to obtain at least one data synchronization block corresponding to the partition table.

In one embodiment of this specification, the target table includes multiple partition tables, and each partition table is stored in a partition of the distributed database;

The full synchronization module is also used for:

For each data synchronization block, according to the partition table corresponding to each data in the data synchronization block in the target table, divide the data synchronization block to obtain at least one data sub-synchronization block corresponding to the data synchronization block ;

When the full synchronization module is used to construct a synchronization task for each data synchronization block in the plurality of data synchronization blocks to synchronize to the target table, it is specifically used for:

A synchronization task for synchronizing to the target table is constructed for each data sub-synchronization block in the plurality of data sub-synchronization blocks.

In one embodiment of the specification, the device further includes a structure modification module, configured to:

According to the table structure change instruction, a shadow table of the source table is created and the structure of the shadow table is modified to obtain the target table.

In one embodiment of this specification, the device also includes a table name switching module, which is used for:

In response to the data in the target table satisfying a synchronization condition with respect to the data in the source table, switch the table names of the source table and the target table.

In one embodiment of this specification, the table name switching module is specifically used for:

Determining the number of sessions between the service layer and the source table in response to the current time relative to the initial time or the last session statistics time reaching a preset time length, wherein the initial time includes the data in the target table relative to When the data in the source table meets the synchronization condition;

In response to the number of sessions between the business layer and the source table being less than the number threshold, killing all sessions between the business layer and the source table and switching the table names of the source table and the target table.

In one embodiment of this specification, the table name switching module is also used for:

In response to the number of sessions counted reaching the number threshold, and the number of sessions between the business layer and the source table in each session counted result is not less than the number threshold, it is determined that the table structure modification fails.

The systems, devices, modules, or units described in the above embodiments can be specifically implemented by computer chips or entities, or by products with certain functions. A typical implementing device is a computer, which may take the form of a personal computer, laptop computer, cellular phone, camera phone, smart phone, personal digital assistant, media player, navigation device, e-mail device, game control device, etc. desktops, tablets, wearables, or any combination of these.

In a typical configuration, a computer includes one or more processors (CPUs), input/output interfaces, network interfaces and memory.

Memory may include non-permanent storage in computer readable media, in the form of random access memory (RAM) and/or nonvolatile memory such as read only memory (ROM) or flash RAM. Memory is an example of computer readable media.

Computer-readable media, including both permanent and non-permanent, removable and non-removable media, can be implemented by any method or technology for storage of information. Information may be computer readable instructions, data structures, modules of a program, or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read only memory (ROM), Electrically Erasable Programmable Read-Only Memory (EEPROM), Flash memory or other memory technology, Compact Disc Read-Only Memory (CD-ROM), Digital Versatile Disc (DVD) or other optical storage, Magnetic cassettes, disk storage, quantum memory, graphene-based storage media or other magnetic storage devices or any other non-transmission media that can be used to store information that can be accessed by computing devices. As defined herein, computer-readable media excludes transitory computer-readable media, such as modulated data signals and carrier waves.

It should also be noted that the term "comprises", "comprises" or any other variation thereof is intended to cover a non-exclusive inclusion such that a process, method, article, or apparatus comprising a set of elements includes not only those elements, but also includes Other elements not expressly listed, or elements inherent in the process, method, commodity, or apparatus are also included. Without further limitations, an element defined by the phrase "comprising a ..." does not exclude the presence of additional identical elements in the process, method, article or apparatus comprising said element.

The foregoing describes specific embodiments of this specification. Other implementations are within the scope of the following claims. In some cases, the actions or steps recited in the claims can be performed in an order different from that in the embodiments and still achieve desirable results. In addition, the processes depicted in the accompanying figures do not necessarily require the particular order shown, or sequential order, to achieve desirable results. Multitasking and parallel processing are also possible or may be advantageous in certain embodiments.

Terms used in one or more embodiments of the present specification are for the purpose of describing specific embodiments only, and are not intended to limit the one or more embodiments of the present specification. As used in one or more embodiments of this specification and the appended claims, the singular forms "a", "the", and "the" are also intended to include the plural forms unless the context clearly dictates otherwise. It should also be understood that the term "and/or" as used herein refers to and includes any and all possible combinations of one or more of the associated listed items.

The user information (including but not limited to user equipment information, user personal information, etc.) and data (including but not limited to data used for analysis, stored data, displayed data, etc.) involved in this application are authorized by the user or Information and data that have been fully authorized by all parties, and the collection, use and processing of relevant data must comply with relevant laws, regulations and standards of relevant countries and regions, and provide corresponding operation portals for users to choose to authorize or refuse.

It should be understood that although the terms first, second, third, etc. may be used in one or more embodiments of the present specification to describe various information, the information should not be limited to these terms. These terms are only used to distinguish information of the same type from one another. For example, without departing from the scope of one or more embodiments of the present specification, first information may also be called second information, and similarly, second information may also be called first information. Depending on the context, the word "if" as used herein may be interpreted as "at" or "when" or "in response to a determination."

The above descriptions are only preferred embodiments of one or more embodiments of this specification, and are not intended to limit one or more embodiments of this specification. Within the spirit and principles of one or more embodiments of this specification, Any modification, equivalent replacement, improvement, etc. should be included in the scope of protection of one or more embodiments of this specification.

Claims (16)

1. A method of data synchronization, the method comprising:

creating a trigger, wherein the trigger is used for synchronizing the data change of a source table to a target table and recording the identification of deleted data in the synchronization process in a deletion log;

synchronizing data within the source table to the target table;

and processing the data in the target table according to the identification in the deletion log.

2. The data synchronization method of claim 1, the creating a trigger comprising:

an Insert trigger, a Delete trigger and an Update trigger are created, wherein the Insert trigger is used for synchronizing a data insertion event occurring in the source table to the target table, the Delete trigger is used for synchronizing a data deletion event occurring in the source table to the target table, and the Update trigger is used for synchronizing a data Update event occurring in the source table to the target table.

3. The data synchronization method of claim 2, the Insert trigger further for deleting an identification of data inserted in the data insertion event within the deletion log.

4. The data synchronization method of claim 2, the Delete trigger further configured to add an identification of data deleted in the data deletion event within the deletion log.

5. The data synchronization method according to claim 2, wherein the Update trigger is further configured to add, in the deletion log, an identifier of data before Update in the data Update event, and delete an identifier of data after Update in the data Update event.

6. The data synchronization method according to claim 1, wherein the processing the data in the target table according to the identification of the data in the deletion log includes:

deleting the data in the target table identified as the identification in the deletion log.

7. The data synchronization method of claim 1, the synchronizing data within the source table to the target table comprising:

dividing the data in the source table according to the preset size of the data synchronization blocks to obtain a plurality of data synchronization blocks;

And constructing synchronous tasks synchronized to the target table aiming at each data synchronous block in the plurality of data synchronous blocks, and generating a task queue for the plurality of obtained synchronous tasks.

8. The data synchronization method of claim 7, the source table comprising a plurality of partition tables, each partition table stored in a partition of a distributed database;

dividing the data in the source table according to the preset size of the data synchronization block to obtain a plurality of data synchronization blocks, including:

respectively determining an upper mark limit and a lower mark limit of data in each partition table;

and dividing the data in each partition table according to the upper limit and the lower limit of the identification of the partition table and the preset size to obtain at least one data synchronization block corresponding to the partition table.

9. The data synchronization method of claim 7, the target table comprising a plurality of partition tables, each partition table stored in a partition of a distributed database;

the method further comprises the steps of:

dividing each data synchronization block according to a partition table corresponding to each data in the data synchronization block in the target table to obtain at least one data sub-synchronization block corresponding to the data synchronization block;

The constructing a synchronization task for each of the plurality of data synchronization blocks to synchronize to the target table includes:

and constructing a synchronous task which is synchronous to the target table aiming at each data sub-synchronous block in the plurality of data sub-synchronous blocks.

10. The data synchronization method of claim 1, the method further comprising:

and creating a shadow table of the source table according to a table structure change instruction, and carrying out structural modification on the shadow table to obtain the target table.

11. The data synchronization method according to claim 1 or 10, the method further comprising:

and switching table names of the source table and the target table in response to the data in the target table meeting a synchronization condition relative to the data in the source table.

12. The data synchronization method of claim 11, the switching table names of the source table and the target table, comprising:

determining the number of sessions between a service layer and the source table in response to the fact that the duration of the current time relative to the initial time or the last session statistics time reaches a preset duration, wherein the initial time comprises the time when the data in the target table meet the synchronization condition relative to the data in the source table;

And in response to the number of sessions between the service layer and the source table being less than a number threshold, killing all sessions between the service layer and the source table and switching table names of the source table and the target table.

13. The data synchronization method of claim 12, the method further comprising:

and responding to the times of the session statistics reaching a time threshold, and determining that the change of the table structure fails when the number of the sessions between the service layer and the source table in the result of each session statistics is not smaller than the number threshold.

14. A data synchronization apparatus, the apparatus comprising:

the incremental synchronization module is used for creating a trigger, wherein the trigger is used for synchronizing the data change of the source table to the target table and recording the identification of the deleted data in the synchronization process in a deletion log;

the full synchronization module is used for synchronizing the data in the source table to the target table;

and the processing module is used for processing the data in the target table according to the identification in the deletion log.

15. An electronic device, comprising:

a processor;

a memory for storing processor-executable instructions;

wherein the processor is configured to implement the method of any one of claims 1-13 by executing the executable instructions.

16. A computer readable storage medium having stored thereon computer instructions which, when executed by a processor, implement the steps of the method of any of claims 1-13.