Real-time synchronous data caching and transcoding method and system between databases

Abstract

The invention discloses a data caching transcoding method and system for real-time synchronization between databases, and the method comprises the steps: storing log file data fragments of a source database into a specified file, i.e., a source data fragment file, and providing the source data fragment file to a plurality of target database groups which synchronize the current source database at the same time, according to the method, source data fragment file data is converted into a uniform key / value format, data optimization and filtering are carried out, and then the data are converted into a data format of a target database. According to the method, efficient and real-time data synchronization among various types of databases can be realized, and seamless circulation and compatibility of data among different systems are ensured through an innovative cache mechanism and a flexible and extensible transcoding technology.

Description

Technical Field

[0001] The present invention relates to the technical field of real-time synchronization of data processing and numerical control libraries, and in particular to a data cache transcoding method and system for real-time synchronization between databases. Background Art

[0002] With the development of Internet technology, databases, as core components of data storage and management, play a vital role in various application systems. In practical applications, it is often necessary to synchronize data from one database to another to achieve data sharing and exchange. In the prior art, in order to achieve data synchronization between different database systems, data complexity, log subscription or dedicated ETL tools are usually used.

[0003] Especially when supporting real-time synchronization between multiple types of databases, complex protocol conversion and data processing are required during data synchronization due to the different log protocols of different databases. In addition, existing data synchronization solutions often lack effective caching mechanisms and cannot cope with data synchronization requirements in large data volumes and high concurrency scenarios.

[0004] Specifically, the existing technology has certain limitations in practical applications:

[0005] 1. Compatibility limitations: Most existing database synchronization tools are limited to supporting limited database types, such as MySQL, Oracle, etc., and cannot meet the data synchronization requirements in heterogeneous database environments.

[0006] 2. Insufficient caching mechanism: The lack of an effective caching mechanism cannot cope with the data synchronization requirements in large data volumes and high concurrency scenarios.

[0007] 3. Complex data format conversion: Different database systems support different data formats and protocols. Existing synchronization tools are often not flexible enough in data format conversion and cannot adapt well to the data exchange requirements between various databases.

[0008] 4. Customized requirements are difficult to meet: When it comes to transmitting data within a specified range in certain scenarios, existing technical solutions often lack flexibility and are difficult to meet users' customized requirements. Summary of the invention

[0009] Purpose of the invention: The present invention provides a data cache transcoding method and system for real-time synchronization between databases, which realizes efficient and real-time data synchronization between various types of databases, and ensures seamless flow and compatibility of data between different systems through innovative caching mechanism and flexible transcoding technology.

[0010] Technical solution: A data cache transcoding method for real-time synchronization between databases described in the present invention comprises the following steps:

[0011] Step 1: Slice the source database log file data into specified files according to the algorithm, which are the source data slicing files;

[0012] Step 2: The source data shard files are provided to multiple target database groups that synchronize with the current source database in real time. When the cache space is full or the system is idle, garbage collection of the shard files is triggered.

[0013] Step 3: Convert the source data shard file data into a unified key / value format;

[0014] Step 4: Optimize and filter the data, and then convert the data into the data format of the target database.

[0015] Furthermore, in step 1, the source database log file data is allocated and stored in the specified file according to the size of a shard file of 128MB. Each file has a specified source file ID, which is obtained by dividing the log sequence number by 128MB. The metadata metadata will update the current shard file number and the last write offset.

[0016] Furthermore, it is characterized in that in step 2, when the new log file data of the source database is stored in the source data shard file, all synchronization task threads that need the source database log file data will be notified at the same time. When each synchronization task thread receives the notification event, it reads the shard file mapping data in parallel and adds a new reference count. Each synchronization task completes data reading and synchronization, reduces the reference count, and when the reference count returns to 0, the metadata will record the largest used shard file number.

[0017] Furthermore, in step 2, garbage collection GC is triggered every 7*24 hours, and the used shard files will be deleted according to the maximum used shard file number recorded, that is, the used shard file number recorded in the metadata. In addition, if the cache space is full within 7*24 hours and writing cannot continue, shard file recycling will also be triggered; if file recycling fails, the source database will be notified to suspend the source database log file synchronization and trigger an alarm.

[0018] Furthermore, in step 3, the log data of the source database is converted into a unified key / value format, where the key refers to the unique identifier of the data item and the value refers to the corresponding data content.

[0019] Furthermore, in the key / value data model, each data item is represented as a key-value pair, where the key is a unique identifier, which can be a composite key composed of a database table name, a record ID, etc.; the value contains all the fields of the record and their values.

[0020] Furthermore, in step 4, a unified interface is designed to implement encoding and decoding of each database log file format, and a unified interface framework is built in, which defines the standard interface for core operations of data parsing, encoding, and transcoding.

[0021] Accordingly, a data cache transcoding system for real-time synchronization between databases includes: a data cache module and a data transcoding module; the data cache module will ensure that the source database log file data is cached, and the data transcoding module will convert the log files of different source databases into a unified key / value format, and then convert these data into the data format of the target database.

[0022] Furthermore, the source database log file data is allocated and stored in the specified file according to the size of a shard file of 128MB. Each file has a specified source file ID, which is obtained by dividing the log sequence number by 128MB. The metadata metadata will update the current shard file number and the last write offset.

[0023] Furthermore, the log data of the source database is converted into a unified key / value format. In the key / value data model, each data item is represented as a key-value pair, where the key is a unique identifier, which is a composite key composed of the database table name, record ID, etc.; the value contains all the fields of the record and their values.

[0024] Beneficial effects: Compared with the prior art, the present invention has the following significant advantages: (1) The data sharding storage method of the present invention improves the efficiency of data access and facilitates management and maintenance. Compared with the single cache method in the prior art, the sharding storage of the present invention is more efficient and flexible; (2) The universal key / value data format is adopted, which simplifies the complexity of data synchronization and improves the maintainability of the system; (3) The unified interface and modular implementation method are designed to flexibly support various types of databases, improving the versatility, compatibility and applicability of the system. BRIEF DESCRIPTION OF THE DRAWINGS

[0025] Figure 1 It is a schematic diagram of the source database log file caching process of the present invention.

[0026] Figure 2It is a schematic diagram of the database real-time data synchronization process of the present invention.

[0027] Figure 3 The figure is a schematic diagram of the database encoding and decoding process of the present invention. DETAILED DESCRIPTION

[0028] A data cache transcoding method for real-time synchronization between databases includes the following steps:

[0029] Step 1: Slice the source database log file data into specified files according to the algorithm, which are the source data slicing files;

[0030] Step 2: The source data shard files are provided to multiple target database groups that synchronize with the current source database in real time. When the cache space is full or the system is idle, garbage collection of the shard files is triggered.

[0031] Step 3: Convert the source data shard file data into a unified key / value format;

[0032] Step 4: Optimize and filter the data, and then convert the data into the data format of the target database.

[0033] Accordingly, a data cache transcoding system for real-time synchronization between databases includes: a data cache module and a data transcoding module; the data cache module will ensure that the source database log file data is cached, and the data transcoding module will convert the log files of different source databases into a unified key / value format, and then convert these data into the data format of the target database.

[0034] The professional terms involved in the present invention include: metadata, segment file, log file, log sequence number, big-little endian, source file ID, last write offset, garbage collection (Garbage Collection / GC) and codec, etc. The log file generally refers to the value log file in the database, because database products of different manufacturers also refer to this type of data file as redo log file or archive log file, etc., so for the convenience of description, this type of data file is collectively referred to as log file in the present invention.

[0035] The data cache module is one of the core technologies of the present invention, and its main purpose is to ensure the timeliness and integrity of the source database log file data.

[0036] The metadata file stores the source database log file source information, page size, big and small endian, log sequence number (logsequence number), reference technology and data checksum (chunksum) and other information. The metadata file has a total of two pages (8KB) ​​of space, and each page stores a copy of metadata information. Metadata information is updated alternately to prevent program exceptions, physical power outages, etc. from triggering write interrupts, resulting in metadata damage, and improving the reliability of the data cache system.

[0037] In order to improve the efficiency and performance of data synchronization, the present invention introduces a shard cache mechanism. This mechanism allocates and stores the source database log file data into a specified file according to the size of a shard file of 128MB. Each file has a specified source file ID, which is obtained by dividing the log sequence number by 128MB. Each file contains basic information such as the source file ID and the last write offset of the source file. When adding a new file, writing a file, or deleting a file, the information is updated in real time to the metadata storage file.

[0038] The source data shard file supports sharing mode and can be provided to multiple target database groups that synchronize the current source database at the same time. The source data shard file is managed through reference technology. Specifically, when the source data shard file stores new log file data, it will notify all synchronization task threads that need the source database log file data at the same time. When each synchronization task thread receives the notification event, it reads the shard file mapping data in parallel and adds a new reference count. Each synchronization task completes data reading synchronization and reduces the reference count.

[0039] The source data shard file recycling mechanism triggers garbage collection (GC) when the cache space is full or the system is idle. The metadata shard files will be recycled if the reference count is 0 and they have not been used for more than 7*24 hours. In addition, if the cache space is full within 7*24 hours and writing cannot continue, shard file recycling will also be triggered. Shard files with a reference count of 0 will be recycled in a time-decrease manner.

[0040] Data transcoding technology is another core technology of the present invention. Its main purpose is to convert log files of different databases into a unified key / value format, and then convert these data into the data format of the target database. This has several important benefits: first, the unified key / value format makes data processing simpler and more efficient; second, by defining a unified interface, it is easy to support the encoding and decoding of multiple database log file formats; finally, such a design helps to improve the scalability and flexibility of the system.

[0041] During the data transcoding process, we first convert the log data of the source database into a unified key / value format. The key / value data structure has a high degree of abstraction. The key refers to the unique identifier of the data item, and the value is the corresponding data content. This format was chosen based on its simplicity and versatility. Almost all database transcoders (codecs) can easily process data in this format.

[0042] To achieve this, the present invention defines a set of standardized key / value data models. Each data item is represented as a key-value pair, where the key is a unique identifier, which can be a composite key composed of a database table name, a record ID, etc.; the value contains all the fields of the record and their values.

[0043] After converting the database log file data into key / value pairs, the present invention does not directly map them to the data structure of the target database, but performs further optimization processing. The key to this step is that the key / value pairs are not only a temporary form of data conversion, but also a basis for supporting data routing and filtering.

[0044] In order to support multiple database log protocols, the transcoding engine of the present invention adopts the design concept of modularization and plug-inization, and designs a set of unified interfaces to implement the encoding and decoding of each database log file format, aiming to build a highly flexible and scalable data processing core. The engine has a built-in unified interface framework, which defines the standard interfaces for core operations such as data parsing, encoding, and transcoding, so that the encoding and decoding plug-ins of different database log formats can be easily connected and work together.

[0045] Specifically, for each supported database type (such as Mysql's binlog, MongoDB's Oplog, PostgreSQL's WAL, Redis's AOF, etc.), the present invention designs a special codec. These plug-ins follow a unified interface specification, can automatically identify and parse the log file format of the corresponding database, and convert the operations (such as INSERT, UPDATE, DELET, etc.) and data content therein into an intermediate format - key / value pairs. The choice of this intermediate format is based on its simple, universal and easy-to-handle characteristics, which helps to reduce the complexity of subsequent conversion steps.

[0046] Once the log file data is converted to a unified key / value format, the data is then optimized and filtered to convert it to the data format of the target database. This process involves reorganizing the key / value data structure into the table structure of the target database, and ensuring that details such as data types and field names match the target database. The present invention has designed a set of transcoding rule engines for this purpose, which can dynamically generate transcoding rules based on the characteristics of the target database. For example, if the target database is MongoDB, the key / value data may need to be converted to BSON format; if it is Mysql, the kennel needs to convert the data into specific SQL statements.

[0047] like Figure 1 As shown, the source database log file caching process of the present invention is shown, including the following parts.

[0048] S101, the data cache system receives the source database log file data through the GRPC protocol for real-time synchronization.

[0049] S102, when the data cache system receives the source database cluster log file, if it is the first time to receive the log file data, it will create an independent directory dabase_cluster_ID for the source database cluster according to the source database cluster ID. A metadata file and all shard files are stored in this directory.

[0050] S103, the present invention creates a new metadata file named metadata.dbf in the source database cluster directory, with a total size of 8KB and divided into 2 pages. Each page of data has a 4-byte version, initialized to 1. Each time it is updated, the version is incremented by 1, and the remainder of the version divided by 2 is taken, and the remainder is used as an index to update the corresponding page.

[0051] S104, the present invention stores the source database log file data in a 128MB size distribution in a segment file. The segment file name is named in the form of seg_file_id, and the current largest id is updated to the metadata file. The last update timestamp of the segment file can be obtained by calling the file attribute information of the system.

[0052] like Figure 2 As shown, the database real-time data synchronization process of the present invention is shown, which includes the following parts.

[0053] S201 represents all cached slice files of the source database log file data in the present invention. Whenever a new log file is cached, all related synchronization tasks will be notified.

[0054] S202 is the memory mapping space of the shard file. When a shard file is created, the memory mapping space of the file is created at the same time. Only the cache module can write to the space, and all synchronization tasks can read this memory mapping space. Each synchronization task that accesses the memory space increments the reference count by 1. When the synchronization of the file data is completed, the reference count decrements by 1. When the reference count is 0 and the data recycling conditions are met, the file can be recycled.

[0055] S203 represents the real-time synchronization task channel in the present invention. When receiving the source database log file update event, it accesses the memory mapping space of the shard file, encodes and decodes the source database log file data, and synchronizes it to the target database in real time.

[0056] like Figure 3 As shown, the specific process of data encoding and decoding performed by the synchronization task in the present invention is shown. When the synchronization task receives a new data event, it will trigger the synchronization process, which mainly includes the following parts.

[0057] S301 mainly describes the log file data decoder. First, the synchronization task will read the relevant memory mapping space data. The synchronization task will select the corresponding database decoder according to the source database type to parse the log file data. For example, for MySQL Binlog, the decoder will first parse the Binlog data and extract the relevant table name, row ID and other metadata information from it.

[0058] S302 mainly describes the filter of the present invention. Different synchronization task configuration options will selectively select related filters. When the decoder parses the log file data, the filter will be triggered first. If it does not pass the filter, the current data will be abandoned, otherwise the current data can be synchronized in real time. For example, the relevant data of the MySQL Binlog decoded by filtering is obtained.

[0059] S303 mainly describes the key / value cache of the present invention. It stores the valid data after parsing and filtering. For example, it constructs the data structure of the key / value format of MySQL Binlog. For example, for an INSERT event, the following format of Table 1 will be generated:

[0060] Table 1 INSERT events converted to key / value

[0061]

[0062] S304 mainly describes the encoder of the present invention. It mainly converts the above key / value into relevant data of the target database. If the target database is redis, the above key / value is converted into SET KEY VALUE, and the following format of Table 2 is generated:

[0063] Table 2 key / value converted to SET KEY VALUE

[0064] SET"users / 123""{"id":123,"name":"kimi"}"

Claims

1. A data cache transcoding method for real-time synchronization between databases, characterized in that: The steps include: Step 1: Slice the source database log file data into specified files according to the algorithm, which are the source data slicing files; Step 2: The source data shard files are provided to multiple target database groups that synchronize with the current source database in real time. When the cache space is full or the system is idle, garbage collection of the shard files is triggered. Step 3: Convert the source data shard file data into a unified key / value format; Step 4: Optimize and filter the data, and then convert the data into the data format of the target database.

2. The data cache transcoding method for real-time synchronization between databases according to claim 1, characterized in that: In step 1, the source database log file data is allocated and stored in the specified file according to the size of a shard file of 128MB. Each file has a specified source file ID, which is obtained by dividing the log sequence number by 128MB. The metadata metadata will update the current shard file number and the last write offset.

3. The data cache transcoding method for real-time synchronization between databases according to claim 1, characterized in that: In step 2, when the new log file data of the source database is stored in the source data shard file, all synchronization task threads that need the source database log file data will be notified at the same time. When each synchronization task thread receives the notification event, it reads the shard file mapping data in parallel and adds a new reference count. Each synchronization task completes data reading and synchronization, reducing the reference count. When the reference count returns to 0, the metadata will record the largest used shard file number.

4. The data cache transcoding method for real-time synchronization between databases according to claim 1, characterized in that: In step 2, garbage collection (GC) is triggered every 7*24 hours, and the used shard files are deleted according to the maximum used shard file number recorded, that is, the used shard file number recorded in the metadata. In addition, if the cache space is full and writing cannot continue within 7*24 hours, shard file recycling will also be triggered. If file recycling fails, the source database will be notified to suspend source database log file synchronization and an alarm will be triggered.

5. The data cache transcoding method for real-time synchronization between databases according to claim 1, characterized in that: In step 3, the log data of the source database is converted into a unified key / value format, where the key refers to the unique identifier of the data item and the value refers to the corresponding data content.

6. The data cache transcoding method for real-time synchronization between databases according to claim 5, characterized in that: In the key / value data model, each data item is represented as a key-value pair, where the key is a unique identifier, which is a composite key consisting of the database table name and the record ID; the value contains all the fields of the record and their values.

7. The data cache transcoding method for real-time synchronization between databases according to claim 1, characterized in that: In step 4, a unified interface is designed to implement the encoding and decoding of each database log file format, with a built-in unified interface framework that defines the standard interface for core operations of data parsing, encoding, and transcoding.

8. A system for implementing the data cache transcoding method for real-time synchronization between databases as claimed in claim 1, characterized in that: include: Data caching module and data transcoding module; The data caching module will ensure that the source database log file data is cached, and the data transcoding module will convert the log files of different source databases into a unified key / value format, and then convert these data into the data format of the target database.

9. The data cache transcoding system for real-time synchronization between databases as claimed in claim 8, characterized in that: The source database log file data is allocated and stored in the specified file according to the size of a shard file of 128MB. Each file has a specified source file ID, which is obtained by dividing the log sequence number by 128MB. At the same time, the shard file number currently being written is updated, and the last write offset metadata is stored.

10. The data cache transcoding system for real-time synchronization between databases according to claim 8, characterized in that: Convert the log data of the source database into a unified key / value format. In the key / value data model, each data item is represented as a key-value pair, where the key is a unique identifier, which is a composite key consisting of the database table name and the record ID; the value contains all the fields of the record and their values.

Images