CN118733673A - A database synchronization method and terminal - Google Patents

Abstract

The present invention discloses a database synchronization method and terminal, which monitors the log file of the original database, determines whether it is necessary to obtain historical data, and if so, extracts the historical data in the original database as a historical data snapshot, synchronizes the historical data to the target database through the historical data snapshot, and then executes the step of obtaining the latest incremental point information; otherwise, the latest incremental point information and the stored historical point information in the original database are obtained, and the incremental log data is obtained according to the difference between the historical point information and the latest incremental point information, and the target database is updated according to the incremental log data. The present invention updates the target database according to the incremental log data in the log, and the log records the operation data, which reduces the data transmission amount and determines whether the update operation needs to be performed by monitoring the log file, thereby ensuring the real-time update of the target database.

Description

A database synchronization method and terminal

Technical Field

The present invention relates to the field of database management, and in particular to a database synchronization method and a terminal.

Background Art

Traditional databases (such as MySQL, Oracle, SQL Server, Postgre, MongoDB, OceanBase, etc.) usually adopt the ACID (atomicity, consistency, isolation, durability) transaction model to ensure the integrity and consistency of transactions. They are highly reliable and support multiple data models and query languages. They are highly flexible and are therefore widely used as backend databases for various business systems. However, when processing large-scale data, traditional databases are limited in performance and scalability, making it difficult to meet the needs of big data processing and analysis, and have single points of failure. In response to the digital age, the amount of data in databases is increasing. Some traditional database queries take a long time or even time out, and the concurrency and stability of the database are reduced, affecting the use of production services. Therefore, in the process of changing production business requirements, it is necessary to migrate data from traditional databases to new databases. However, due to the different characteristics of the two, the existing data migration method cannot meet the needs.

Summary of the invention

The technical problem to be solved by the present invention is to provide a database synchronization method and a terminal to achieve data synchronization between different types of databases.

In order to solve the above technical problems, a technical solution adopted by the present invention is:

A database synchronization method comprises the steps of:

Monitor the log file of the original database to determine whether historical data needs to be obtained. If so, extract the historical data in the original database as a historical data snapshot, synchronize the historical data to the target database through the historical data snapshot, and then execute the step of obtaining the latest incremental point information;

Otherwise, the incremental latest point information and the stored historical point information in the original database are obtained, the incremental log data is obtained according to the difference between the historical point information and the incremental latest point information, and the target database is updated according to the incremental log data.

In order to solve the above technical problems, another technical solution adopted by the present invention is:

A database synchronization terminal comprises a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the following steps when executing the computer program:

Monitor the log file of the original database to determine whether historical data needs to be obtained. If so, extract the historical data in the original database as a historical data snapshot, synchronize the historical data to the target database through the historical data snapshot, and then execute the step of obtaining the latest incremental point information;

Otherwise, the incremental latest point information and the stored historical point information in the original database are obtained, the incremental log data is obtained according to the difference between the historical point information and the incremental latest point information, and the target database is updated according to the incremental log data.

The beneficial effects of the present invention are as follows: in the process of data synchronization, if historical data needs to be obtained, the historical data is extracted as a historical data snapshot, and the snapshot is synchronized, thereby reducing the access pressure to the original database, and after the historical data is synchronized, it is automatically switched to synchronize incremental data, so that the original database can continue to be used after data migration and automatically synchronized to the newly set target database, meeting the requirements of read-write separation and ensuring data security, that is, when the historical data is synchronized, the target database is updated according to the incremental log data in the log, and the log records the operation data, thereby reducing the amount of data transmission and judging whether an update operation is needed by monitoring the log file, thereby ensuring the real-time update of the target database.

BRIEF DESCRIPTION OF THE DRAWINGS

FIG1 is a flowchart of a data synchronization method according to an embodiment of the present invention;

FIG2 is a flowchart of another step of a data synchronization method according to an embodiment of the present invention;

FIG3 is a schematic diagram of synchronization of historical data and incremental data according to an embodiment of the present invention;

FIG4 is a fragmentation flow chart of an embodiment of the present invention;

FIG5 is a schematic diagram of a serialized log according to an embodiment of the present invention;

FIG6 is a flow chart of a two-stage confirmation method according to an embodiment of the present invention;

FIG7 is a schematic diagram of state storage according to an embodiment of the present invention;

FIG8 is a system data flow diagram of an embodiment of the present invention;

FIG9 is a schematic diagram of the structure of a terminal according to an embodiment of the present invention;

Description of labels:

1. A data synchronization terminal; 2. A processor; 3. A memory.

DETAILED DESCRIPTION

In order to explain the technical content, achieved objectives and effects of the present invention in detail, the following is an explanation in combination with the implementation modes and the accompanying drawings.

Please refer to FIG. 1 , a database synchronization method includes the following steps:

Monitor the log file of the original database to determine whether historical data needs to be obtained. If so, extract the historical data in the original database as a historical data snapshot, synchronize the historical data to the target database through the historical data snapshot, and then execute the step of obtaining the latest incremental point information;

Otherwise, the incremental latest point information and the stored historical point information in the original database are obtained, the incremental log data is obtained according to the difference between the historical point information and the incremental latest point information, and the target database is updated according to the incremental log data.

From the above description, it can be seen that the beneficial effects of the present invention are: in the process of data synchronization, if historical data needs to be obtained, the historical data is extracted as a historical data snapshot, and the snapshot is synchronized, which reduces the access pressure to the original database, and after the historical data is synchronized, it automatically switches to synchronize incremental data, so that the original database can continue to be used after data migration and automatically synchronize to the newly set target database, meeting the requirements of read-write separation and ensuring data security. That is, when the historical data is synchronized, the target database is updated according to the incremental log data in the log. The log records the operation data, which reduces the amount of data transmission and determines whether an update operation is needed by monitoring the log file, thereby ensuring the real-time update of the target database.

Furthermore, synchronizing the historical data to the target database through the historical data snapshot includes:

Slicing the historical data snapshot to obtain a slice snapshot;

Each of the slice snapshots is sent to a different task manager, and the different task managers synchronize the slice snapshots to the target database at the same time.

From the above description, it can be seen that after slicing the historical data snapshot, multiple task managers synchronously execute the synchronization tasks of each slice, which can make full use of the parallelism of the program to improve the synchronization efficiency of the task. That is, after the job manager (JobManager) splits the snapshot into multiple slices (split), it distributes them to the corresponding N task managers (Taskmanager), and the subsequent parallelism is increased from 1 to the specified N parallelism, thereby improving the synchronization efficiency.

Furthermore, slicing the historical data snapshot to obtain the slice snapshot includes:

Obtaining table snapshots in the historical data snapshots, and extracting the table name of each of the table snapshots;

Acquire a table structure according to the table name, and determine whether the table snapshot includes a primary key;

If yes, slice the data according to the primary key and the preset slice step to obtain a slice snapshot;

Otherwise, a slice field is received, a hash value corresponding to each slice field in the table snapshot is calculated, and slices are performed according to the hash value and a preset slice step to obtain a slice snapshot.

From the above description, it can be seen that if there is a primary key in the table, the slice snapshot is obtained by directly slicing according to the primary key with a preset step size. If there is no primary key in the table, the hash value corresponding to the field is calculated according to the selected field, and the slice is performed with a preset step size through the hash value. The preset step size can be determined according to the number of task managers that can execute synchronization tasks in parallel, and the historical data that needs to be synchronized is distributed as evenly as possible to each task manager for processing, which maximizes the efficiency of data synchronization and the utilization rate of the task manager. In addition, when the primary key cannot be used for slicing, a snapshot slicing solution is provided to improve the applicability of the solution.

Further, the acquiring of the incremental latest point information and the stored historical point information in the original database, acquiring incremental log data according to the difference between the historical point information and the incremental latest point information, and updating the target database according to the incremental log data comprises:

Record the current data operation sequence number in the original database as the incremental latest point information, and determine whether there is a difference between the historical point information and the incremental latest point information;

If so, the historical point information is increased by a preset value to obtain a synchronization operation sequence number, and the incremental log data is obtained according to the synchronization operation sequence number. After the target database is updated according to the incremental log data, the synchronization operation sequence number is used as the historical point information, and the process returns to execute the step of recording the current data operation sequence number in the original database as the incremental latest point information.

From the above description, it can be seen that if there is a difference between the historical point information and the incremental latest point information, it means that a new log has been generated and synchronization needs to continue; however, during the synchronization process, because the use of the original database is not stopped, new logs may continue to be generated during the synchronization process. At this time, according to the incremental rule of the database operation sequence number, the preset value is increased to obtain the synchronization operation sequence number, and then it is updated, and the synchronization operation sequence number is stored as the historical point information to continue to judge whether there is a difference with the incremental latest point information. The one-to-one correspondence between the data operation sequence number and the operation record in the log can be used to avoid omissions or repeated synchronization operations during the synchronization process.

Furthermore, if historical data needs to be obtained, the current data operation sequence number is recorded as historical point information when the historical data snapshot is extracted.

From the above description, it can be seen that when synchronizing historical data, the current data operation sequence number when the historical data snapshot is extracted is recorded, that is, the database synchronization before this operation is synchronized in the form of historical data snapshots, and the data synchronization thereafter continues to be synchronized in the form of increments, that is, synchronization of log data. The two steps of historical data synchronization and incremental data synchronization can be directly connected, so there is no need to establish separate processing flows to determine the starting position of incremental data synchronization.

Further, the acquiring incremental log data according to the synchronization operation sequence number includes:

Get the flag range of different log groups;

Obtain the target log group corresponding to the flag bit range described in the synchronization operation sequence number;

The log data corresponding to the synchronization operation sequence number is retrieved in the log group to obtain incremental log data.

From the above description, it can be seen that when there are many log files, they will be divided into log groups for storage, which reduces the efficiency of log query; at this time, by obtaining the flag bit ranges corresponding to different log groups, first determine the flag bit range corresponding to the synchronization operation sequence number, and then search for the log data corresponding to the synchronization operation sequence number in the log group corresponding to the flag bit range, first determine a smaller range and then traverse the log files one by one, thereby improving processing efficiency.

Further, updating the target database according to the incremental log data includes:

If the operation type of the incremental log data is a DDL operation, the DDL operation is converted into a database language format corresponding to the target database, and the target database is updated according to the converted DDL operation.

From the above description, we can see that since DDL operations generally do not involve data-related operations, such as modifications to the table structure, including adding fields and indexes, if the operation type of the log data is a DDL operation, it can be directly parsed and format converted to synchronize the operation.

Further, updating the target database according to the incremental log data includes:

If the operation type of the incremental log data is a DML operation, parsing the operation data corresponding to the DML operation and converting the operation data into a format adapted to the target database;

The DML operation is converted into a database language format corresponding to the target database, and the target database is updated according to the converted DML operation and operation data.

As can be seen from the above description, DML operations are usually accompanied by data modifications, such as inserting, updating, or deleting data. In the process of synchronizing to the target database, it is necessary not only to parse the operation itself, but also to parse the object corresponding to the operation, that is, the data of the original operation, and then convert it into the format corresponding to the target database.

Furthermore, the target database is a large-scale parallel processing database.

From the above description, it can be seen that if the target database is set as a high-performance large-scale parallel processing database, the parallel function of the database itself can be used to realize parallel reading of snapshot data after sharding, saving the cost of software construction and improving the query and concurrency capabilities of the database. If the original database is a traditional database, the historical data and incremental data therein are synchronized to the large-scale parallel processing database in real time, so that the write data can continue to be written to the original database, but the data is read through the large-scale parallel processing database to realize the read-write separation of the database. The read scenario is usually more complex than the write scenario. While the read-write separation improves security, the characteristics of the large-scale parallel processing database are used to improve the overall performance of the system.

Please refer to FIG. 8 , a database synchronization terminal includes a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, each step in the above-mentioned database synchronization method is implemented.

The above-mentioned database synchronization method and terminal of the present invention can be applied to the process of synchronizing a database, especially the database synchronization process when the types of the original database and the target database are different; the following is an explanation through a specific implementation method.

In the existing scenario of using traditional databases to build systems, the traditional database has a single point of failure that affects the system's service provision, cannot meet the needs of massive data processing, and cannot meet the needs of instant queries. Even if optimization methods such as sub-library, sub-table, and indexing are performed on the basis of traditional databases, the problem cannot be fundamentally solved. Therefore, this application constructs a synchronization method for real-time synchronization of historical data and incremental data, synchronizes the data in the original database to the target database, ensures the timeliness of the data in the two databases, and thus can achieve read-write separation and improve overall performance.

Please refer to Figures 1-7, embodiment 1 of the present invention is:

A database synchronization method comprises the steps of:

S1. Monitor the log files of the original database to determine whether historical data needs to be obtained. If so, execute S2; otherwise, execute S3.

The log file stores the operations on the data in the database. You can enable log archiving through the original database to generate archive logs and metadata control information, and record the database add, delete, modify operations (DML) and schema_change (changes in table structure or database structure, which belong to DDL operations) in the log file.

In the traditional synchronization method, if real-time data synchronization is required, a time field is usually added to the table to perform micro-batch JDBC (Java DataBase Connectivity) polling operations. However, this method cannot obtain DDL operations and has a large delay, making it difficult to meet the requirements of real-time synchronization. By monitoring the original database to obtain changes in log files, a millisecond-level response can be achieved. Moreover, because the log files are read instead of the database tables themselves, the query function of the original database is not affected and the query pressure of the original database is not increased. For example, real-time monitoring can be achieved through the polling method in Kafka Connect (a data handling integration tool).

Polling refers to the process in which connectors periodically obtain data from or write data to the source system or target system. Connectors continuously poll to check whether new data is available in the source system or whether the target system can receive new data, thereby achieving data transmission and synchronization. In this process, Kafka is built into our synchronization tool, and there is no need to deploy a Kafka cluster separately; the Polling process is as follows:

(1) Configure the connector: Configure the Kafka Connect connector, including specifying the information of the source system (original database) and the target system (target database), data conversion rules, etc.

(2) Start the connector: Start the Kafka Connect connector, which will obtain data from the source system according to the configuration;

(3) Regular polling: The connector will poll regularly to check whether new data is available in the source or target system. The polling frequency can be configured, and the interval can be controlled to milliseconds to improve the real-time performance of data synchronization;

(4) Data acquisition: When the connector finds that new data is available, it acquires the data and processes it accordingly (such as conversion, filtering, etc.), and then writes the data to the specified topic of the Kafka cluster;

(5) Exception handling: During data transmission, various errors and exceptions may occur. The connector will handle these situations, such as retrying, error logging, and alarming.

(6) Data consistency: Kafka Connect uses transactions to ensure the atomicity of data writing. In the event of a failure, it can correctly restore the data processing progress and ensure data consistency.

Referring to FIG. 3 , S2, extracting the historical data in the original database as a historical data snapshot, synchronizing the historical data to the target database through the historical data snapshot, and then executing S3;

Wherein, synchronizing the historical data to the target database through the historical data snapshot includes:

Referring to FIG. 4 , S21, slicing the historical data snapshot to obtain a slice snapshot includes:

S211, obtaining a table snapshot in the historical data snapshot, and extracting a table name of each of the table snapshots;

S212, obtaining a table structure according to the table name, and determining whether the table snapshot includes a primary key; if so, executing S213; otherwise, executing S214;

S213, slicing according to the primary key and the preset slicing step to obtain a slicing snapshot;

In an optional implementation, between S212 and S213, it is also included to determine whether the primary key is an int type field, if so, execute S213, otherwise, execute S214 or calculate the gradually corresponding hash value, and slice according to the hash value and the preset slice step to obtain a slice snapshot;

S214, receiving a slice field, calculating a hash value corresponding to each slice field in the table snapshot, and performing slicing according to the hash value and a preset slicing step to obtain a slice snapshot;

Since slicing by primary key has great limitations, it is necessary to configure a primary key and the primary key is an int (integer) field. Therefore, this application introduces a method of calculating hash values. An existing field in the table can be obtained as a slicing field by a custom or preset rule of the customer, and the hash value corresponding to each value in the slicing field is calculated. Slicing is performed according to the hash value, so that tables without primary keys or tables whose primary key types do not meet the requirements can also complete slicing; and because the synchronization tasks are performed by different task managers after slicing, it is sufficient to ensure that the slicing processing volume between each task manager is roughly balanced. Therefore, even non-primary keys without strict deduplication can be limited by the allocation rules of subsequent task managers to ensure that the processing tasks of each task manager are unified, so the possible duplication of non-primary keys has little impact on subsequent effects;

The largest integer value in the data is 2^64, so you can specify the field as the chunk sharding field through chunk.key-column, calculate a new hash value (64-bit integer) for the specified chunk key through the hash algorithm, and then slice the converted hash value;

S22, sending each of the slice snapshots to different task managers (taskManager) respectively, and having different task managers synchronize the slice snapshots to the target database at the same time;

In an optional implementation, please refer to FIG. 4 , after the task manager completes its own slice snapshot synchronization task, it sends an ACK (Acknowledge character) to the resource manager (Resource Manager), and after confirming that the resource manager has received the ACKs of all task managers, S3 is executed to enter the incremental real-time synchronization stage;

Please refer to FIG. 3 , S3, obtaining the incremental latest point information and the stored historical point information in the original database, obtaining incremental log data according to the difference between the historical point information and the incremental latest point information, and updating the target database according to the incremental log data, including:

S31, recording the current data operation sequence number in the original database as the latest incremental point information;

If historical data needs to be obtained, step S31 is replaced by recording the current data operation sequence number as historical point information when starting to extract the historical data snapshot;

In an optional implementation, usually in a traditional database, the database log has a corresponding data operation sequence number (for example, SCN, System Change Number in Oracle database, binlog in MySQL, SQLserver and postgreSQL), and the current data operation sequence number can be obtained through CDC (Change Data Capture) technology;

S32, determining whether there is a difference between the historical point information and the incremental latest point information; if so, executing S33; otherwise, returning to executing S1;

In an optional implementation, if there is no difference, S31 can be continuously executed, because if the target database is updated according to the incremental log data, it means that the historical data has been synchronized. If the historical data is not synchronized, the synchronization method based on the operation stored in the incremental log data will produce errors due to different data operated; therefore, the step of judging whether historical data synchronization is needed can be directly skipped, thereby improving the efficiency of step execution;

S33, after increasing the historical point information by a preset value, obtain a synchronization operation sequence number, obtain incremental log data according to the synchronization operation sequence number, update the target database according to the incremental log data, use the synchronization operation sequence number as the historical point information, and return to execute S31;

Wherein, obtaining incremental log data according to the synchronization operation sequence number includes:

S331, obtaining the flag bit range of different log groups;

S332, obtaining the target log group corresponding to the flag bit range described in the synchronization operation sequence number;

S333, searching the log data corresponding to the synchronization operation sequence number in the log group to obtain incremental log data;

Wherein, updating the target database according to the incremental log data includes:

S334. If the operation type of the incremental log data is a DDL (data definition language) operation, execute S336;

S335, if the operation type of the incremental log data is a DML (Data Manipulation Language) operation, parsing the operation data corresponding to the DML operation, and converting the operation data into a format adapted to the target database, and then executing S336;

S336, converting the DDL operation/DML operation into a database language format corresponding to the target database;

For example, if the target database is MySQL, it can be converted into insert into TEST(PUID,ALARMTIME)values('1000000','20240224134950'). This SQL statement is only used as an example. Developers can convert it into the desired SQL format according to their own target database to complete the SQL conversion.

S337, updating the target database according to the converted DML operation and operation data/DDL operation;

Moreover, in the process of updating the target database, S336 can obtain the code execution block written to the target database; however, in some cases, especially for large amounts of data, if the original database is a traditional database, some sub-library and sub-table operations will be performed on the traditional database according to the time field to improve the performance of some business queries; however, if the target database adopts an MPP database, sub-library and sub-table operations will usually be abandoned, that is, a large table is usually used in the MPP database, and then the large table data is divided by data partitioning to improve query performance. At this time, in the process of data synchronization, multiple tables in the original database need to be written to different partitions of the same table in the target database. For example, in the example of step S336, the data belongs to the sub-table TEST202402, which will be written to the MPP database TEST, partition partition = 202402. All sub-tables of traditional MySQL databases (the table structure of the sub-tables is the same) are only different partitions of a single table in the MPP database. In this embodiment, not only the original format synchronization of single table -> single table is provided, but also the synchronization of multiple tables -> single table is provided. Taking the above table as an example, by configuring the mapping relationship of multiple tables -> single table, the partition merging of multiple tables is realized; in this embodiment, a configuration API is provided, and referring to the following Table 1, it is an example of the structure of an API:

Table 1

Then a code implementation example is:

{"sourceTableName":"TEST*",

"sinkTableName":"TEST",

"partitionKey":"ALARMTIME",

"interval":"month"}, which means that all tables starting with TEST in the original database are written into the TEST table of the MPP database by monthly partition according to the ALARMTIME field value; the data format of the TEST* table in the original database is: {"PUID":"1000000","ALARMTIME":"20240224134950"};

At the same time, please refer to Figure 6, introduce the two-stage confirmation method (2PC) writing to ensure that data will not be lost, ensure the consistency of transactions, and introduce the checkpoint+savepoint mechanism in Flink. All tasks will write their own execution intermediate state values (State) to the backend storage RocksDb in the checkpoint/savepoint stage. This not only avoids the problem of memory overflow caused by the intermediate state value being too large, but also enables task recovery through the state value. Referring to Figure 7, through the checkpoint mechanism, without the need for manual intervention, the task can complete the self-recovery of the task by obtaining the latest state value and realize breakpoint resumption of data. Normally, after the user modifies the code logic, the task needs to be restarted. This invention uses the savepoint mechanism to allow the user to modify the synchronization logic, such as adding or subtracting tables, modifying mapping relationships, modifying conversion logic and calculation logic, etc., and still be able to resume transmission from the specified location;

In an optional implementation, updating the target database according to the incremental log data further includes S300, serializing the incremental log data, parsing the corresponding SQL and data content to determine whether the incremental log data is DDL data or DML data, and obtaining operation data corresponding to the DML data; Table 2 provides a format for parsing a log file, which can be customized by the developer according to the characteristics of the original database and the target database and the requirements during the synchronization process;

Referring to Table 2, all operations of adding or deleting data can be represented by the two fields after and before, for example:

Insert: before = null, after = afterValue;

Update: before=beforeValue, after=afterValue;

Delete: before=beforeValue.after=null;

Table 2

Please refer to Figure 5, which is an example of a serialized log. It can be seen that at 1708753859000 (millisecond timestamp, converted to 2024-02-24 13:50:59 Beijing time), a data with field values of {"PUID":"1000000","ALARMTIME":"20240224134950"} was inserted into the C##TEST.TEST202402 table of the Oracle database;

In an optional implementation, the target database is a massively parallel processing database (MPP, Massively Parallel Processing);

In an optional implementation, updating the target database according to the incremental log data includes: customizing a modified data format and an SQL statement format adapted to the target database through Flink (a computing framework for stream data) technology, so as to synchronize the parsed DML operation and DDL operation to the target database through Flink;

Taking Oracle as an example, there is a global identifier SCN in the Oracle database, which is used to mark the changes of each data block in the database. SCN is a monotonically increasing integer in seconds, which can control concurrent transactions, record data changes, and restore data blocks through SCN. Therefore, when the real-time synchronization program is started, it must first obtain the latest SCN value, which is expressed as currentScn (incremental latest point information). For data with SCN < currentScn, it is marked as historical data for synchronization, as described in S2. When the snapshot synchronization phase is over, we start from the currentScn value to obtain the current point information. The point information includes the global identifier SCN, the log transaction sequence number LSN, and the data file identifier DAYAFILE. NUMBER and redo sequence number RSLN. Here we use SCN. We use SCN to find the actual operation log from the redo log and parse the data operation. After each data operation is processed, the current SCN will be updated with currentScn+1 (currentScn=currentScn+1), and it will be confirmed whether it is the same as the current point information. If not, continue to use the updated SCN to find the actual operation log (incremental log data). This ensures seamless connection between historical full synchronization and incremental synchronization, and ensures that the acquired data will not be repeated.

It also includes: after the historical data synchronization is completed, the database corresponding to the query data configuration is modified to the target database in the business system, and during the query (selectdb) process, the query will be performed from the target database to achieve read-write separation;

Take Doris as an example. Doris is an MPP database that supports the JDBC protocol. There are two cases for switching from MySQL to Doris: if it is business data writing, the original database connection information remains unchanged, and CDC synchronizes data to Doris through logs. Users only need to start one CDC task; if it is business data reading, the data source to be read is modified to the Doris link information; by configuring two data source connections for data writing and data reading, the original business can be easily migrated to the new MPP database query to complete the data read and write separation.

Please refer to FIG. 8 , the second embodiment of the present invention is:

A database synchronization device includes a log acquisition module (data access module), an SQL parsing module, a data encapsulation module and an SQL conversion module;

The data access module is used to obtain the operation log of the original database through monitoring, implement the slicing process when synchronizing historical data, and write the historical data and the DDL and DML data after format conversion into the target database;

SQL parsing module, used to parse SCN and obtain operation logs through SCN during the synchronization of incremental data;

Data encapsulation module, used to obtain SQL and data content in the log;

SQL conversion module, used to convert the acquired DDL and DML into a format that is suitable for the target database;

An interface can be built for each module for developers to call, so that developers can adapt their own original database and target database to improve flexibility.

Please refer to FIG. 9 , the third embodiment of the present invention is:

A database synchronization terminal 1 includes a processor 2, a memory 3, and a computer program stored in the memory 3 and executable on the processor 2. When the processor 2 executes the computer program, each step in the first embodiment is implemented.

In summary, the present invention provides a database synchronization method and terminal, which adopts a method of synchronizing incremental data and historical data separately, and synchronizes historical data by snapshot; that is, historical data is read by snapshot + parallel processing, and incremental data is read by single line. When the historical data synchronization is completed (snapshot ends), it can automatically switch to incremental mode. If it is necessary to obtain historical data, first take a snapshot of the historical data, and slice the snapshot. By obtaining the sliced data in parallel, the data acquisition efficiency is improved, and the historical data is snapshotted to avoid the impact on the database operation during the synchronization process. And when taking a snapshot, it can be selected whether it is a database level or a table level snapshot. Since there may be tables that do not need to be synchronized in the synchronization of the entire database, the analysis of some useless data will be added to the snapshot of the entire database, and the transmission and calculation delays will be increased. Selecting a table-level snapshot can more flexibly select the data to be synchronized according to the needs of synchronization, that is, only select the tables that need to be synchronized for snapshot processing. In addition, a double confirmation mechanism is introduced in the synchronization process to further ensure the integrity of the data after synchronization.

Read-write separation reduces the access and computing pressure of the original database while improving the query performance and concurrency of the query system composed of the original database and the target database, thereby improving the overall stability and availability of the system.

The above descriptions are merely embodiments of the present invention and are not intended to limit the patent scope of the present invention. Any equivalent transformations made using the contents of the present invention's specification and drawings, or directly or indirectly applied in related technical fields, are also included in the patent protection scope of the present invention.

Claims (10)

1. A method for database synchronization, comprising the steps of:

Monitoring a log file of an original database, judging whether historical data needs to be acquired, if so, extracting the historical data in the original database as a historical data snapshot, synchronizing the historical data to a target database through the historical data snapshot, and executing the step of acquiring incremental latest point location information;

Otherwise, obtaining the increment latest point position information and the stored historical point position information in the original database, obtaining increment log data according to the difference between the historical point position information and the increment latest point position information, and updating the target database according to the increment log data.

2. The method of claim 1, wherein synchronizing the historical data to a target database via the historical data snapshot comprises:

slicing the historical data snapshot to obtain a slice snapshot;

And respectively sending each slice snapshot to different task managers, and simultaneously synchronizing the slice snapshots to a target database by different task managers.

3. A method of database synchronization as defined in claim 2, wherein, slicing the historical data snapshot, wherein obtaining the sliced snapshot comprises:

Obtaining table snapshots in the historical data snapshots, and extracting a table name of each table snapshot;

obtaining a table structure according to the table name, and judging whether the table snapshot comprises a main key or not;

If yes, slicing according to the primary key and a preset slicing step length to obtain a slicing snapshot;

Otherwise, receiving slice fields, calculating hash values corresponding to each slice field in the table snapshot, and slicing according to the hash values and preset slice step sizes to obtain the slice snapshot.

4. The method of claim 1, wherein the obtaining incremental latest point location information and stored historical point location information in the original database, obtaining incremental log data according to a difference between the historical point location information and the incremental latest point location information, and updating the target database according to the incremental log data comprises:

recording a current data operation serial number in an original database as increment latest point position information, and judging whether a difference exists between the history point position information and the increment latest point position information;

if yes, the history point location information is increased by a preset value to obtain a synchronous operation serial number, incremental log data are obtained according to the synchronous operation serial number, after a target database is updated according to the incremental log data, the synchronous operation serial number is used as history point location information, and the step of recording the current data operation serial number in the original database as the incremental latest point location information is executed in a returning mode.

5. The method according to claim 4, wherein if the history data is required to be obtained, the current data operation sequence number is recorded as the history point information when the history data snapshot is started to be extracted.

6. The database synchronization method according to claim 4, wherein said obtaining incremental log data from said synchronization operation sequence number comprises:

Obtaining the zone bit ranges of different log groups;

acquiring a target log group corresponding to the zone bit range of the synchronous operation serial number;

and searching the log data corresponding to the synchronous operation serial number in the log group to obtain incremental log data.

7. The database synchronization method of claim 1, wherein updating the target database based on the incremental log data comprises:

If the operation type of the increment log data is DDL operation, converting the DDL operation into a database language format corresponding to the target database, and updating the target database according to the converted DDL operation.

8. The database synchronization method of claim 1, wherein updating the target database based on the incremental log data comprises:

If the operation type of the incremental log data is DML operation, analyzing operation data corresponding to the DML operation, and converting the operation data into a format of an adaptation target database;

and converting the DML operation into a database language format corresponding to the target database, and updating the target database according to the converted DML operation and operation data.

9. The method of claim 1, wherein the target database is a massively parallel processing database.

10. A database synchronization method comprising a memory, a processor and a computer program stored on said memory and executable on said processor, characterized in that said processor implements the steps of a database synchronization method according to any of claims 1-9 when said computer program is executed.