A data synchronization method, system, medium and device based on Xugu database

Abstract

The present invention discloses a data synchronization method, system, medium and equipment based on Xugu database, which belongs to the field of database synchronization and solves the problem of low synchronous warehousing efficiency. The method mainly includes: using multi-channel reading threads to detect and capture the transaction operation binlog log produced by the server in real time; parsing the captured binlog log in combination with the metadata information of the corresponding table; based on the breakpoint resume mechanism, the parsed data is written to the homogeneous or heterogeneous target end in batch synchronization through the corresponding multi-channel writing threads. The present invention packages the synchronization tool into the form of a task flow, while optimizing the concurrent logic of the writing thread, and improving the synchronous warehousing efficiency.

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, natively distributed database, a database software product that has been independently iterated since 2002. It is fully compatible with traditional relational databases, provides a standard object and process management model, and is compatible with various database interfaces and management tools. The database's core engine utilizes a full-link (a combination of centralized and distributed) architecture, with data stored in physical slices across data nodes, and computations performed simultaneously by each compute node.

[0003] Xugu Database Data Synchronization Software uses the Xugu distributed database as a data source for full and incremental data synchronization and distribution, providing user-friendly configuration and startup. Faced with ever-increasing data volumes, the Xugu Database Data Synchronization Software flexibly synchronizes and distributes the massive amounts of data stored in the Xugu distributed database on demand, meeting users' diverse needs for refined data services based on massive amounts of data, such as establishing thematic libraries, archiving and backing up important data, and archiving and backing up full data. Xugu Database Data Synchronization Software's incremental synchronization is based on the Xugu distributed database log architecture and is completely independent of stored procedures, functions, materialized views, triggers, etc. It does not require any agents, is non-invasive to the source database, and has no impact on the source database application system.

[0004] However, Xugu Database currently has issues with data synchronization. First, in the event of a network outage or packet loss, DDL changes may not reach all write threads, causing some write threads to remain in a waiting state and unable to wake up. Furthermore, synchronized data may be lost or duplicated after a software restart. Second, a single synchronization tool currently synchronizes the entire database, using relatively few resources. For scenarios requiring large amounts of data synchronization, multiple synchronization tools must be enabled and bound to multiple subscriptions, which is a cumbersome process. 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 object of the present invention is achieved through the following technical solutions:

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

[0008] Use multi-channel 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 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, 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 end 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 writer thread, the value of the atomic type parameter is reduced by one, and the current writer thread is set to a waiting state. After the last writer thread receives a DDL command, only the last thread executes the DDL command and wakes up the previous waiting writer threads.

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

[0018] When the heterogeneous target is an open-source stream processing platform, upon restart, the last successfully sent message is pulled from the open-source stream processing platform. The corresponding offset in the server's binlog is extracted from the message. The synchronization program combines the savepoint and the offset and ignores the binlog items that have already 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 end through corresponding multi-channel writing threads further includes:

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

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

[0023] If the heterogeneous target end is an open source stream processing platform, the binary information is directly sent to the corresponding topic table of 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 the Xugu database is provided, comprising:

[0025] Server;

[0026] Homogeneous or heterogeneous target end;

[0027] The synchronization tool includes a synchronization writer and a binlog log reader and parser. The binlog log reader and parser uses multiple reading threads 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 uses a breakpoint resume mechanism to write the parsed data in batches to the homogeneous or heterogeneous target end through the corresponding multi-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 that can be executed on the processor, and when the processor executes the computer instructions, any one of the data synchronization methods described above is executed.

[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 multiple 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 synchronously written in batches to the homogeneous or heterogeneous target end through the corresponding multiple 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 synchronized storage.

[0033] (2) Taking into account the possible power outages or network disconnections 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 writer threads in a waiting state within a specified time. If so, all of them are awakened, and the last awakened writer 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 writer threads to receive the message, causing some writer 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 This is a flow chart of a data synchronization method based on Xugu database according to an embodiment of the present invention;

[0037] Figure 2 This is a diagram of the Xugu database data synchronization software architecture shown in an embodiment of the present invention;

[0038] Figure 3 This is a schematic diagram of splitting binlog by table according to 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 This is a schematic diagram of a read thread flow diagram according to an embodiment of the present invention;

[0041] Figure 6 This is a schematic diagram of a write thread process according to an embodiment of the present invention;

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

[0043] The technical solutions of the present invention are described clearly and completely 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 generally described and shown in the drawings herein can be arranged and designed in various different 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 this application below for the above-mentioned problems should be the contributions made by the inventor to this 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 relevant statements, since this embodiment involves database-related technologies, in order to make the objectives, 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 the action stream data of consumers in the website.

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

[0049] In response to 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. Use multiple 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 the Xugu database, and the target is an open source stream processing platform (Kafka), a relational database, or other heterogeneous or homogeneous databases. The Xugu database data synchronization software uses the 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 the Xugu database as the data source to achieve full data and log-based incremental data acquisition, parsing, synchronization, and distribution functions. Its architecture is detailed in 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 order. The present invention relies on the binlog log file of the Xugu distributed database for real-time data synchronization, with zero intrusion to the source library, and realizes real-time synchronization of incremental data.

[0056] Furthermore, the Xugu database binlog log is managed and recorded on the server side in units of libraries, that is, the change binlog log entries of all objects under the same logical library are mixed and stored. Figure 3 As shown, to meet users' needs for table-based binlog parsing, distribution, and synchronization, the Xugu Database Data Synchronization Software splits the Xugu Database's binlog logs into tables in real time and publishes them to Kafka. Users can freely consume incremental Xugu Database data on Kafka, so the Xugu Database Data Synchronization Software can provide data services for big data integration businesses and implement 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 uses the method of recording the SQL statements of DDL operations as they are. 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 of less than 10 megabytes is supported.

[0059] Furthermore, data loss caused by synchronization software anomalies is fatal to data synchronization. Considering the potential for failures within the synchronization software itself and its operating environment (power outages, network disconnections, etc.), the synchronization software has designed a comprehensive breakpoint-resume transfer mechanism to ensure zero loss and zero duplication of synchronized data after the software is restarted. Breakpoint-resume transfer is a mechanism that creates a savepoint while the synchronization software is running. After writing to the target, the current position is recorded and used as the starting position for retrieving the database binlog after restart. It also has proprietary designs tailored to different target clients. Because Kafka itself lacks data duplication detection, upon restart, the tool pulls the last successfully sent message from Kafka and extracts its corresponding offset in the Xugu database binlog. The synchronization program combines the savepoint and this offset to ignore binlog entries already sent to Kafka, ensuring zero duplication and loss of incremental binlog data. When the target is a relational database, breakpoint-resume transfer combines the synchronization software's own savepoints with the relational database's uniqueness constraints to ensure zero data loss and zero duplication.

[0060] In order to achieve breakpoint resumability, this method also sets up a local record file for 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 waiting writer threads within a specified time. If so, all of them are awakened, and the last awakened writer thread is allowed to execute the DDL command. Specifically, the number of reader threads is consistent with the number of binlog partitions. Each reader thread pulls the incremental change records of the binlog in real time and puts them into the task queue for consumption by the writer thread. Because the writer thread uses batch submission, if the specified number of submissions is not reached and there is no data change, the reader thread sends a commit message (commit information) to the task queue to let the writer 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 corresponding topic table of 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, which obtains all table structure metadata from the source and stores it in a dictionary file for assembling the comparison table structure according to the table ID when assembling the data into the library. Figure 7 shown.

[0064] Example 2

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

[0066] Server;

[0067] Homogeneous or heterogeneous target end;

[0068] The synchronization tool includes a synchronization writer and a binlog log reader and parser. The binlog log reader and parser uses multiple reading threads 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 uses a breakpoint resume mechanism to write the parsed data in batches to the homogeneous or heterogeneous target end through the corresponding multi-writing threads.

[0069] The server is the Xugu Database, and the heterogeneous target is a heterogeneous or homogeneous database, such as an open source stream processing platform (Kafka) and a relational database. The synchronization tool utilizes the Xugu Database data synchronization software to acquire, parse, synchronize, and distribute full data and log-based incremental data. It includes a B / S-based web management interface and a console version that supports server-side 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 described in detail 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 this understanding, the technical solution of this embodiment, 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, which is stored in a storage medium and includes several instructions for enabling a computer device (which can be a personal computer, a server, or a network device, etc.) to execute 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 when the processor executes the computer instructions, the data synchronization method provided in the embodiment of the present invention is executed.

[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 thereof. 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 for execution by a data processing apparatus or to control the operation of the data processing apparatus. Alternatively or in addition, the program instructions may be encoded on an artificially generated propagated signal, such as a machine-generated electrical, optical, or electromagnetic signal, that is generated to encode and transmit information to a suitable receiver apparatus for execution by the data processing apparatus.

[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 the corresponding functions by operating on input data and generating output. The processes and logic flows can also be performed by, and apparatus can be implemented as, special purpose logic circuitry, such as an FPGA (field programmable gate array) or an ASIC (application-specific integrated circuit).

[0078] Processors suitable for executing computer programs include, for example, general-purpose and / or special-purpose microprocessors, or any other type of central processing unit. Typically, a central processing unit will receive instructions and data from a read-only memory and / or 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, a computer will also include one or more mass storage devices for storing data, such as magnetic disks, magneto-optical disks, or optical disks, or the computer will be operably coupled to such a mass storage device to receive data from it or to transmit data to it, or both. However, a computer does not necessarily have such a device. In addition, a 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, to name a few.

[0079] It should be understood that each box in the flow chart or block diagram can represent a module, program segment or a part of code, and the part of the module, program segment or code comprises one or more executable instructions for realizing the logical function of the provision. 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 consecutive 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 function or action of the provision, 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 considered 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, they can make several simple deductions and substitutions without departing from the concept of the present invention, which should be regarded as falling within the scope of protection of the present invention.

Claims

1. A data synchronization method based on Xugu database, characterized in that: include: Use multi-channel 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; The binlog log is managed and recorded on the server side in units of libraries, where the binlog log is segmented in units of tables; Based on the breakpoint-resume transmission mechanism, the parsed data is synchronously written to the homogeneous or heterogeneous target end in batches through the corresponding multi-channel writing threads; Writing the parsed data to the homogeneous or heterogeneous target end in batches synchronously 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 is sent to a writer thread, the value of the atomic type parameter is decremented by one, and the current writer thread is set to a waiting state. After the last writer thread receives a DDL command, only the last thread executes the DDL command and wakes up the previous waiting writer threads. A daemon thread is added to determine whether there are any waiting writer threads within a specified time. If so, all are awakened and the last awakened writer thread is asked to execute the DDL command. The number of reader threads is consistent with the number of binlog partitions. Each reader thread pulls incremental change records from the binlog in real time and puts them into the task queue for consumption by the writer thread. If the writer thread has not submitted the specified number of entries and there are no data changes, the reader thread sends a commit message to the task queue, instructing the writer thread to submit the data. The breakpoint resume mechanism includes: There is a local record file for the Redo synchronization position, including the record table ID, binlog partition number, file number, and offset; When the heterogeneous target is an open-source stream processing platform, upon restart, the last successfully sent message is pulled from the open-source stream processing platform. The corresponding offset in the server's binlog is extracted from the message. The synchronization program combines the savepoint and the offset and ignores the binlog items that have already 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.

2. The data synchronization method based on the 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 step of synchronously writing the parsed data in batches 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 writer threads within a specified time. If so, wake up all of them and let the last woken writer thread execute the DDL command.

4. The data synchronization method based on Xugu database according to claim 1 is characterized in that: The writer thread executes DDL commands, including: If the heterogeneous target end is an open source stream processing platform, the binary information is directly sent to the corresponding topic table of 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.

5. A data synchronization system based on Xugu database, characterized in that: include: Server; Homogeneous or heterogeneous target end; The synchronization tool includes a synchronization writer and a binlog log reader and parser. The binlog log reader and parser uses multiple reading threads to detect and capture the transaction operation binlog logs generated 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 uses a breakpoint resume mechanism to write the parsed data in batches to the homogeneous or heterogeneous target end through the corresponding multi-channel writing threads.

6. 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 according to any one of claims 1 to 4 is implemented.

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

Images