Data synchronization method and system based on virtual valley database, medium and equipment

Abstract

The invention discloses a data synchronization method and system based on a virtual valley database, a medium and equipment, belongs to the field of database synchronization, and solves the problem of low storage efficiency of synchronization. The method mainly comprises the following steps: detecting and capturing a transaction operation binlog generated by a server in real time by adopting a multi-path read thread; analyzing the captured binlog in combination with the metadata information of the corresponding table; and based on a breakpoint resuming mechanism, synchronously writing the analyzed data into an isomorphic or heterogeneous target end in batches through corresponding multi-path writing threads. According to the method, the synchronization tool is packaged into a task flow form, meanwhile, the concurrent logic of the write thread is optimized, and the storage efficiency of synchronization is improved.

Description

Technical Field

[0001] The present invention relates to the field of database synchronization, and in particular to a data synchronization method, system, medium and device based on Xugu database. Background Art

[0002] Xugu Database is an original native distributed database, a database software product that has been independently iterated since 2002. In terms of usage, it is fully compatible with traditional relational databases, provides standard object and process management models, and is equipped with various database interfaces and management tools. The database core engine adopts a full-link (centralized and distributed in one) architecture, where data is stored in data nodes in the form of physical slices, and calculations are responded to simultaneously by each computing node.

[0003] Xugu Database Data Synchronization Software uses Xugu distributed database as data source to synchronize and distribute full and incremental data, and provides interface configuration and startup. Faced with the growing amount of data, Xugu Database Data Synchronization Software flexibly synchronizes and distributes the massive data stored in Xugu distributed database on demand, meeting users' various refined data service needs based on massive data, such as the establishment of special databases, archiving and backup of important data, and archiving and backup of full data. Xugu Database Data Synchronization Software incremental synchronization is based on the log architecture of Xugu distributed database, which is completely independent of stored procedures, functions, materialized views, triggers, etc., does not require any agents, is zero-invasive to the source database, and does not affect the source database application system at all.

[0004] However, there are problems with data synchronization in Xugu Database: First, when there is a network outage or packet loss, DDL changes cannot be received by all write threads, causing some write threads to be in a waiting state and unable to wake up; at the same time, after the software is restarted, the synchronized data will be lost or duplicated. Second, the current single synchronization tool is for the synchronization of the entire library level, and the resource usage is relatively small. For scenarios that require a large amount of data synchronization, it is currently necessary to open multiple synchronization tools and bind multiple subscriptions to operate, and the steps are relatively cumbersome. Summary of the invention

[0005] The purpose of the present invention is to overcome the technical problems existing in the prior art and provide a data synchronization method, system, medium and equipment based on Xugu database to ensure the efficiency of synchronized warehousing.

[0006] The objective of the present invention is achieved through the following technical solutions:

[0007] In a first aspect, a data synchronization method based on Xugu database is provided, comprising:

[0008] Use multi-way reading threads to detect and capture the transaction operation binlog logs produced by the server in real time;

[0009] Parse the captured binlog logs in combination with the metadata information of the corresponding table;

[0010] Based on the breakpoint-resume transmission mechanism, the parsed data is synchronously written in batches to the homogeneous or heterogeneous target end through the corresponding multi-channel writing threads.

[0011] In some possible embodiments of the present application, the step of parsing the captured binlog log in combination with metadata information of the corresponding table includes:

[0012] Generates standard SQL statements or key-value data sets in reverse order based on fields.

[0013] In some possible embodiments of the present application, the binlog log is managed and recorded on the server side in units of libraries, wherein the binlog log is segmented in units of tables.

[0014] In some possible embodiments of the present application, the step of synchronously writing the parsed data in batches to a homogeneous or heterogeneous target terminal through corresponding multi-channel writing threads includes:

[0015] Record the same DDL command in all partitions of the binlog log at the same time;

[0016] Define an atomic type parameter. Every time a partition binlog log is sent to a writing thread, the value of the atomic type parameter is reduced by one, and the current writing thread is set to a waiting state. After the last writing thread receives the DDL command, only the last thread executes the DDL command and wakes up the previous waiting writing thread.

[0017] In some possible embodiments of the present application, the breakpoint resume mechanism includes:

[0018] When the heterogeneous target end is an open source stream processing platform, the last successfully sent message is pulled from the open source stream processing platform when restarting, and the corresponding offset in the server binlog log is taken from the message. The synchronization program combines the save point and the offset and ignores the binlog items that have been sent to the open source stream processing platform.

[0019] When the heterogeneous target end is a relational database, a method combining the synchronization software's own save points and the relational database's unique constraints is adopted.

[0020] In some possible embodiments of the present application, the step of synchronously writing the parsed data in batches to a homogeneous or heterogeneous target terminal through corresponding multi-channel writing threads further includes:

[0021] Add a daemon thread to determine whether there are any waiting write threads within the specified time. If there are any, wake up all of them and let the last woken-up write thread execute the DDL command.

[0022] Preferably, the write thread executes the DDL command, including:

[0023] The heterogeneous target end is an open source stream processing platform, which directly sends binary information to the topic table corresponding to the open source stream processing platform; if the target end is a relational database, the binlog information is parsed and assembled into SQL for batch storage operations.

[0024] In a second aspect, a data synchronization system based on Xugu database is provided, comprising:

[0025] Server side;

[0026] Homogeneous or heterogeneous target end;

[0027] The synchronization tool includes a synchronization writer and a binlog log reading and parsing device. The binlog log reading and parsing device uses a multi-channel reading thread to detect and capture the transaction operation binlog logs produced by the server in real time, and parses the captured binlog logs in combination with the metadata information of the corresponding table; the synchronization writer, based on the breakpoint resume mechanism, writes the parsed data to the homogeneous or heterogeneous target end in batches through the corresponding multi-channel writing threads.

[0028] According to a third aspect, a computer-readable storage medium is provided, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, any one of the data synchronization methods is implemented.

[0029] In a fourth aspect, an electronic device is provided, comprising a memory and a processor, wherein the memory stores computer instructions executable on the processor, and the processor executes any one of the data synchronization methods described above when executing the computer instructions.

[0030] It should be further explained that the technical features corresponding to the above options can be combined or replaced with each other to form a new technical solution if there is no conflict.

[0031] Compared with the prior art, the present invention has the following beneficial effects:

[0032] (1) The present invention uses multi-channel reading threads to detect and capture the transaction operation binlog logs produced by the server in real time; the captured binlog logs are parsed in combination with the metadata information of the corresponding table; based on the breakpoint resume mechanism, the parsed data is batch-synchronously written to the homogeneous or heterogeneous target end through the corresponding multi-channel writing threads. The synchronization tool is packaged in the form of a thread task flow, and the concurrent logic of the writing thread is optimized to improve the efficiency of synchronous warehousing.

[0033] (2) Taking into account the possible power outage or network disconnection failures that may occur in the synchronization tool itself and the operating environment, the present invention uses a breakpoint resume mechanism to ensure zero loss and zero duplication of synchronized data after the software is restarted.

[0034] (3) The present invention adds a daemon thread to determine whether there are any writing threads in a waiting state within a specified time. If there are any, all of them are awakened, and the last awakened writing thread is allowed to execute the DDL command. This solves the problem that when a network interruption or packet loss occurs, the DDL change cannot allow all writing threads to receive the message, causing some writing threads to remain in a waiting state and unable to be awakened, resulting in data congestion.

[0035] (4) The present invention wraps a management layer on top of the synchronization tool, packages the current version of the synchronization tool into a task flow, and subsequently creates multiple tasks for scenarios where a large amount of data needs to be synchronized, and automatically deploys multiple synchronization tools in the background or implements it in other forms. BRIEF DESCRIPTION OF THE DRAWINGS

[0036] Figure 1 A flow chart of a data synchronization method based on Xugu database shown in an embodiment of the present invention;

[0037] Figure 2 A diagram of the software architecture of Xugu database data synchronization according to an embodiment of the present invention;

[0038] Figure 3 This is a schematic diagram of binlog splitting by table shown in an embodiment of the present invention;

[0039] Figure 4 A schematic diagram of a Redo process according to an embodiment of the present invention;

[0040] Figure 5 A schematic diagram of a read thread flow chart showing an embodiment of the present invention;

[0041] Figure 6 A schematic diagram of a write thread flow chart showing an embodiment of the present invention;

[0042] Figure 7 The figure is a schematic diagram of a dictionary process according to an embodiment of the present invention. DETAILED DESCRIPTION

[0043] The technical solution of the present invention is clearly and completely described below in conjunction with the accompanying drawings. Obviously, the described embodiments are part of the embodiments of the present invention, rather than all of the embodiments. The components of the embodiments of the present application described and shown in the accompanying drawings can be arranged and designed in various configurations. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of the present invention.

[0044] It should be noted that the defects existing in the solutions in the above-mentioned prior art are the results obtained by the inventor after practice and careful research. Therefore, the discovery process of the above-mentioned problems and the solutions proposed in the embodiments of the present application for the above-mentioned problems below should all be the contributions made by the inventor to the present application in the process of invention and creation, and should not be understood as technical contents known to technical personnel in this field.

[0045] Based on the above related statements, since this embodiment involves database-related technologies, in order to make the purpose, technical solutions and advantages of the embodiments of this application clearer, the following explains the relevant professional terms that may be involved in the present invention:

[0046] 1. Database schema definition language DDL (Data Definition Language) is a language used to describe real-world entities to be stored in the database.

[0047] 2. Kafka is an open source stream processing platform developed by the Apache Software Foundation and written in Scala and Java. Kafka is a high-throughput distributed publish-subscribe messaging system that can handle all action stream data of consumers on the website.

[0048] 3. SQL language is the abbreviation of Structured Query Language. SQL language is a database query and programming language used to access data and query, update and manage relational database systems; it is also the extension of database script files.

[0049] In view of the technical problems pointed out in the background technology, the embodiments provided by the present invention are as follows:

[0050] Example 1

[0051] In an exemplary embodiment, referring to Figure 1 , provides a data synchronization method based on Xugu database, comprising the following steps:

[0052] S1, using multi-way reading threads to detect and capture the transaction operation binlog logs produced by the server in real time;

[0053] S2. Parse the captured binlog logs in combination with the metadata information of the corresponding table;

[0054] S3. Based on the breakpoint-resume mechanism, the parsed data is synchronously written in batches to the homogeneous or heterogeneous target end through the corresponding multi-channel writing threads.

[0055] Specifically, the server is Xugu Database, and the target is an open source stream processing platform (Kafka), relational database, or other heterogeneous or homogeneous databases. The Xugu Database data synchronization software uses Xugu Database as the data source to achieve full data and log-based incremental data acquisition, parsing, synchronization, and distribution functions. The Xugu Database data synchronization software / tool ​​uses Xugu Database as the data source to achieve full data and log-based incremental data acquisition, parsing, synchronization, and distribution functions. For details of its architecture, see Figure 2 . This method uses multi-threaded real-time detection to capture the transaction operation binlog log produced by the Xugu distributed database, and combines the captured binlog log with the metadata information of the corresponding table to reversely generate standard SQL statements or key-value data sets in units of fields. The corresponding multi-channel data synchronization writer receives the data generated by the binlog log reading parser and writes it to the target database in batches according to the transaction sequence. The present invention relies on the binlog log file of the Xugu distributed database for real-time data synchronization, has zero intrusion on the source library, and realizes real-time synchronization of incremental data.

[0056] Furthermore, the binlog logs of Xugu database are managed and recorded on the server side in units of libraries, that is, the change binlog log items of all objects under the same logical library are mixed and stored. Figure 3 As shown in the figure, in order to meet the user's needs for binlog parsing, distribution and synchronization in units of tables, the Xugu database data synchronization software splits the binlog logs recorded in the Xugu database in real time in units of tables, and then publishes them to Kafka. Users can freely consume the incremental data of the Xugu database on Kafka, so the Xugu database data synchronization software can provide data services for big data integration business and realize incremental data Kafka integration.

[0057] Furthermore, the DDL and large object synchronization design in this method is as follows:

[0058] The binlog log of Xugu database records the SQL statements of DDL operations as they are, and users can extract the SQL statements of DDL operations from the binlog log. If the target end is a relational database, the DDL statements can also be synchronized on the target end. Because the binlog recording method of DDL is to record the same DDL command in all partitions of the binlog at the same time, the synchronization adopts the definition of an atomic type parameter. Every time a partition binlog log is sent to the writing thread, the value of the atomic type parameter is reduced by one, and the current writing thread is set to a waiting state until the last writing thread receives the DDL command. Only the last thread executes the DDL command and wakes up the previous waiting writing thread. Among them, for binlog log lines containing large object (clob, blob) data, only large object synchronization less than 10 megabytes is supported.

[0059] Furthermore, data loss caused by synchronization software anomalies is fatal to data synchronization. Considering the possible failures of the synchronization software itself and the operating environment (power failure, network disconnection, etc.), the synchronization software is designed with a perfect breakpoint-resume mechanism to ensure zero loss and zero duplication of synchronized data after the software is restarted. Breakpoint-resume is a mechanism for saving points (savepoint) when the synchronization software is running. After writing to the target end, the current position is recorded and used as the starting position of the binlog log of the database after restart. At the same time, it is designed specifically for different target ends. Because Kafka itself does not have data duplication detection, the tool pulls the last successfully sent message from Kafka when restarting, and takes out the corresponding offset in the binlog log of the Xugu database from the message. The synchronization program combines the savepoint and the offset, ignores the binlog items sent to Kafka, and realizes that the incremental binlog log data is not repeated or lost. When the target end is a relational database, breakpoint-resume uses the synchronization software's own savepoint (savepoint) and the uniqueness constraint of the relational database to ensure zero data loss and zero duplication.

[0060] In order to achieve breakpoint resuming, this method also sets up a local record file of the Redo synchronization position, including the record table ID, binlog partition number, file number, and offset. The recording process is as follows Figure 4 shown.

[0061] Furthermore, this method also adds a daemon thread to determine whether there are any writing threads in a waiting state within a specified time. If so, all of them are awakened, and the last awakened writing thread is allowed to execute the DDL command. Specifically, the number of reading threads is consistent with the number of binlog partitions. Each reading thread pulls the incremental change records of binlog in real time and puts them into the task queue for consumption by the writing thread. Because the writing thread adopts batch submission, if the specified number of submissions is not reached and there is no data change, the reading thread sends a commit message (submission information) to the task queue to let the writing thread submit the data, avoiding data blocking due to insufficient number of changes. The specific process is as follows: Figure 5 shown.

[0062] Furthermore, the writing thread pulls binlog information from the task queue in real time. If the heterogeneous target end is an open source stream processing platform, the binary information is directly sent to the topic table corresponding to the open source stream processing platform; if the target end is a relational database, the binlog information is parsed and assembled into SQL for batch storage operations. The specific process is as follows Figure 6 shown.

[0063] Furthermore, the method also includes dictionary design, where the dictionary obtains all table structure metadata from the source end and stores it in a dictionary file for assembling the comparison table structure according to the table ID when assembling it into the library. Figure 7 shown.

[0064] Example 2

[0065] Based on the same inventive concept as that of Example 1, this embodiment provides a data synchronization system based on the Xugu database, including:

[0066] Server side;

[0067] Homogeneous or heterogeneous target end;

[0068] The synchronization tool includes a synchronization writer and a binlog log reading and parsing device. The binlog log reading and parsing device uses a multi-channel reading thread to detect and capture the transaction operation binlog logs produced by the server in real time, and parses the captured binlog logs in combination with the metadata information of the corresponding table; the synchronization writer, based on the breakpoint resume mechanism, writes the parsed data to the homogeneous or heterogeneous target end in batches through the corresponding multi-channel writing threads.

[0069] The server is Xugu Database, and the heterogeneous target is an open source stream processing platform (Kafka), a relational database, or other heterogeneous databases or homogeneous databases. The synchronization tool uses the Xugu Database data synchronization software to achieve full data and log-based incremental data acquisition, parsing, synchronization, and distribution functions, including a web management page based on B / S and a console version that supports server backend deployment and operation, providing users with an easy-to-use, efficient, stable, and scalable data synchronization solution.

[0070] It should be noted that each part of the system implements the functions corresponding to the steps in the method embodiment, and the specific implementation process will not be repeated here.

[0071] Example 3

[0072] Based on the same inventive concept as Example 1, this embodiment provides a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the data synchronization method provided by the embodiment of the present invention is implemented. Based on such an understanding, the technical solution of this embodiment is essentially or the part that contributes to the prior art or the part of the technical solution can be embodied in the form of a software product, and the computer software product is stored in a storage medium, including a number of instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the methods of each embodiment of the present invention. The aforementioned storage medium includes various media that can store program codes, such as a U disk, a mobile hard disk, a read-only memory (ROM), a random access memory (RAM), a magnetic disk or an optical disk.

[0073] Example 4

[0074] Based on the same inventive concept as Example 1, an electronic device is provided, including a memory and a processor, wherein the memory stores computer instructions that can be executed on the processor, and the processor executes the data synchronization method provided in the embodiment of the present invention when executing the computer instructions.

[0075] The processor may be a single-core or multi-core central processing unit or a specific integrated circuit, or one or more integrated circuits configured to implement the present invention.

[0076] Embodiments of the subject matter and functional operations described in this specification may be implemented in: tangibly embodied computer software or firmware, computer hardware including the structures disclosed in this specification and their structural equivalents, or a combination of one or more of them. Embodiments of the subject matter described in this specification may be implemented as one or more computer programs, i.e., one or more modules of computer program instructions encoded on a tangible, non-transitory program carrier to be executed by a data processing device or to control the operation of the data processing device. Alternatively or additionally, the program instructions may be encoded on an artificially generated propagated signal, such as a machine-generated electrical, optical, or electromagnetic signal, which is generated to encode and transmit information to a suitable receiver device for execution by the data processing device.

[0077] The processes and logic flows described in this specification can be performed by one or more programmable computers executing one or more computer programs to perform corresponding functions by operating on input data and generating output. The processes and logic flows can also be performed by special purpose logic circuits, such as FPGAs (field programmable gate arrays) or ASICs (application-specific integrated circuits), and the apparatus can also be implemented as special purpose logic circuits.

[0078] Processors suitable for executing computer programs include, for example, general and / or special microprocessors, or any other type of central processing unit. Typically, the central processing unit will receive instructions and data from a read-only memory and / or a random access memory. The basic components of a computer include a central processing unit for implementing or executing instructions and one or more memory devices for storing instructions and data. Typically, the computer will also include one or more large-capacity storage devices for storing data, such as magnetic disks, magneto-optical disks, or optical disks, or the computer will be operably coupled to this large-capacity storage device to receive data from it or to transmit data to it, or both. However, the computer does not necessarily have such a device. In addition, the computer can be embedded in another device, such as a mobile phone, a personal digital assistant (PDA), a mobile audio or video player, a game console, a global positioning system (GPS) receiver, or a portable storage device such as a universal serial bus (USB) flash drive, just to name a few.

[0079] It should be understood that each box in the flow chart or block diagram can represent a module, a program segment or a part of a code, and the module, a program segment or a part of a code contains one or more executable instructions for realizing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the box can also occur in a sequence different from that marked in the accompanying drawings. For example, two continuous boxes can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each box in the block diagram and / or flow chart, and the combination of the boxes in the block diagram and / or flow chart can be implemented with a dedicated hardware-based system that performs the specified function or action, or can be implemented with a combination of dedicated hardware and computer instructions.

[0080] The above specific implementation methods are detailed descriptions of the present invention. It cannot be determined that the specific implementation methods of the present invention are limited to these descriptions. For ordinary technicians in the technical field to which the present invention belongs, several simple deductions and substitutions can be made without departing from the concept of the present invention, which should be regarded as belonging to the protection scope of the present invention.

Claims

1. A data synchronization method based on Xugu database, characterized in that: include: Use multi-way reading threads to detect and capture the transaction operation binlog logs produced by the server in real time; Parse the captured binlog logs in combination with the metadata information of the corresponding table; Based on the breakpoint-resume transmission mechanism, the parsed data is synchronously written in batches to the homogeneous or heterogeneous target end through the corresponding multi-channel writing threads.

2. The data synchronization method based on Xugu database according to claim 1 is characterized in that: The captured binlog log is parsed in combination with the metadata information of the corresponding table, including: Generates standard SQL statements or key-value data sets in reverse order based on fields.

3. The data synchronization method based on Xugu database according to claim 1 is characterized in that: The binlog log is managed and recorded on the server side in units of libraries, wherein the binlog log is segmented in units of tables.

4. The data synchronization method based on Xugu database according to claim 3 is characterized in that: The step of synchronously writing the parsed data in batches to a homogeneous or heterogeneous target terminal through corresponding multi-channel writing threads includes: Record the same DDL command in all partitions of the binlog log at the same time; Define an atomic type parameter. Every time a partition binlog log is sent to a writing thread, the value of the atomic type parameter is reduced by one, and the current writing thread is set to a waiting state. After the last writing thread receives the DDL command, only the last thread executes the DDL command and wakes up the previous waiting writing thread.

5. The data synchronization method based on Xugu database according to claim 1 is characterized in that: The breakpoint-resume transmission mechanism includes: When the heterogeneous target end is an open source stream processing platform, the last successfully sent message is pulled from the open source stream processing platform when restarting, and the corresponding offset in the server binlog log is taken from the message. The synchronization program combines the save point and the offset and ignores the binlog items that have been sent to the open source stream processing platform. When the heterogeneous target end is a relational database, a method combining the synchronization software's own save points and the relational database's unique constraints is adopted.

6. The data synchronization method based on Xugu database according to claim 4 is characterized in that: The step of writing the parsed data in batches synchronously to the homogeneous or heterogeneous target end through the corresponding multi-channel writing threads also includes: Add a daemon thread to determine whether there are any waiting write threads within the specified time. If there are any, wake up all of them and let the last woken-up write thread execute the DDL command.

7. The data synchronization method based on Xugu database according to claim 6 is characterized in that: The write thread executes DDL commands, including: The heterogeneous target end is an open source stream processing platform, which directly sends binary information to the topic table corresponding to the open source stream processing platform; if the target end is a relational database, the binlog information is parsed and assembled into SQL for batch storage operations.

8. A data synchronization system based on Xugu database, characterized in that: include: Server side; Homogeneous or heterogeneous target end; The synchronization tool includes a synchronization writer and a binlog log reading and parsing device. The binlog log reading and parsing device uses a multi-channel reading thread to detect and capture the transaction operation binlog logs produced by the server in real time, and parses the captured binlog logs in combination with the metadata information of the corresponding table; the synchronization writer, based on the breakpoint resume mechanism, writes the parsed data to the homogeneous or heterogeneous target end in batches through the corresponding multi-channel writing threads.

9. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the data synchronization method described in any one of claims 1 to 7 is implemented.

10. An electronic device comprising a memory and a processor, wherein the memory stores computer instructions that can be executed on the processor, wherein: When the processor runs the computer instructions, it executes the data synchronization method described in any one of claims 1-7.

Images