A method and system for multimodal synchronization between MySQL and Hango database

Abstract

This invention provides a method and system for multimodal synchronization between MySQL and Highgo databases, belonging to the field of database synchronization technology. The invention includes: real-time parsing of MySQL binary logs and distributing them to multi-threaded processing based on table name hashes; converting field types through a dynamic type mapping rule base and generating Highgo-compatible SQL; and employing a two-phase commit protocol to ensure cross-database transaction consistency. It solves the problems of data consistency assurance, performance optimization, and data type compatibility in real-time synchronization of heterogeneous databases.

Description

Technical Field

[0001] This invention relates to the field of database synchronization technology, and in particular to a multimodal synchronization method and system for MySQL to Highgo database, applicable to scenarios such as migration of domestic databases and hybrid cloud data synchronization. Background Technology

[0002] 1. Limitations of full migration tools: Existing tools such as Highgo migration tools only support one-time data migration and cannot meet the real-time synchronization requirements of business continuity.

[0003] 2. Data consistency risk: In high-concurrency scenarios, dual-write solutions are prone to data inconsistency between the primary and backup databases due to network latency.

[0004] 3. Missing mapping of heterogeneous data types: MySQL's tinyint(1) and other types are incorrectly mapped to boolean values ​​in the Highgo database, resulting in data distortion.

[0005] 4. Performance bottleneck: Traditional polling synchronization methods (such as timed queries) put pressure on the source database and the synchronization delay can be as high as minutes. Summary of the Invention

[0006] To address the above technical issues, this invention provides a multimodal synchronization method from MySQL to Highgo database based on incremental log parsing.

[0007] The technical solution of this invention is: A method for synchronizing MySQL to Highgo database includes: Real-time parsing of MySQL binary logs, distributed across multiple threads for processing based on table name hashes; The field types are converted using a dynamic type mapping rule base, and Highgo compatible SQL is generated. A two-phase commit protocol is used to ensure consistency across database transactions.

[0008] Furthermore, Parallel binlog event processing is employed: events are distributed to different threads based on the table name hash.

[0009] Furthermore, The dynamic type mapping rule base supports: User-defined type conversion rules; Automatically detect numerical overflow and trigger fragmented storage.

[0010] Furthermore, The transaction consistency guarantee includes: Assign a globally unique ID (GTID) to each transaction; Failed transactions are retried asynchronously and data is compensated based on GTID.

[0011] Furthermore, this invention also provides a MySQL to Highgo database multimodal synchronization system, comprising: Incremental log parsing layer: Real-time monitoring of MySQL binlog, parsing into a standardized event stream; Data transformation engine: dynamically handles data type mapping and SQL syntax conversion; Transaction consistency controller: Ensures atomicity of cross-database transactions based on two-phase commit; Exception rollback module: Records operation logs and supports breakpoint resume and data compensation.

[0012] Furthermore, Incremental logs are parsed using multi-threading, and parallel binlog event processing is employed: events are distributed to different threads based on table name hashes.

[0013] Furthermore, Dynamic data type mapping Built-in type-compatible rule base, supporting custom mapping; Automatic data overflow repair: Dynamic sharding is triggered when bigint is detected to be out of range.

[0014] Furthermore, Transaction consistency guarantee mechanism Global transaction ID tracking: Assign a unique ID to each transaction to ensure transaction ordering and idempotency; Asynchronous confirmation retry: If a write operation fails in the Ruohan high-end system, the transaction will be replayed based on the unique ID until it succeeds.

[0015] The beneficial effects of this invention are Synchronization timeliness: End-to-end latency <500ms (90% reduction compared to traditional solutions) Data consistency: 99.999% consistency guarantee achieved through GTID and compensation mechanisms. Improved compatibility: Supports automatic conversion of 20+ MySQL-specific data types. Detailed Implementation

[0016] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly and completely described below. Obviously, the described embodiments are some embodiments of the present invention, but not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0017] 1. System Architecture Incremental log parsing layer: Real-time monitoring of MySQL binlog, parsing into a normalized event stream. Data transformation engine: dynamically handles data type mapping (e.g., tinyint(1) → smallint) and SQL syntax conversion. Transaction Consistency Controller: Ensures atomicity of cross-database transactions based on two-phase commit (2PC). Exception rollback module: Records operation logs, supports breakpoint resumption and data compensation. 2. Core Innovation Points 2.1 Multi-threaded parsing of incremental logs Parallelized binlog event processing is employed: events are distributed to different threads based on table name hashes to avoid single-thread blocking. 2.2 Dynamic Data Type Mapping Built-in type compatibility rule base, supports custom mapping (such as converting MySQL datetime to Highgo timestamp(6)). Automatic data overflow repair: Dynamic sharding is triggered when bigint is detected to be out of range. 2.3 Transaction Consistency Guarantee Mechanism Global Transaction ID (GTID) tracking: Assigns a unique ID to each transaction to ensure transaction ordering and idempotency. Asynchronous confirmation retry: If a write operation fails on the Ruohan high-end system, the transaction will be replayed according to the GTID until it succeeds. 3. Performance Optimization Strategies Batch merge write: Combines multiple DML operations into a single batch commit, reducing network round trips. Tiered Memory Caching: L1 cache: The 1000 most recent change records of the hot table. L2 caching: Asynchronous persistence to Redis to prevent data loss due to process crashes. The above description is merely a preferred embodiment of the present invention and is used only to illustrate the technical solution of the present invention, and is not intended to limit the scope of protection of the present invention. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention are included within the scope of protection of the present invention.

Claims

1. A multimodal synchronization method from MySQL to Hango database, characterized in that, Real-time parsing of MySQL binary logs, distributed across multiple threads for processing based on table name hashes; The field types are converted using a dynamic type mapping rule base, and Highgo compatible SQL is generated. A two-phase commit protocol is used to ensure consistency across database transactions.

2. The method according to claim 1, characterized in that, Parallel binlog event processing is employed: events are distributed to different threads based on the table name hash.

3. The method according to claim 1, characterized in that, The dynamic type mapping rule base supports: User-defined type conversion rules; Automatically detect numerical overflow and trigger fragmented storage.

4. The method according to claim 1, characterized in that, The transaction consistency guarantee includes: Assign a globally unique ID to each transaction; Failed transactions are retried asynchronously and data is compensated based on a unique ID.

5. A MySQL to Highgo database multimodal synchronization system, characterized in that, include: Incremental log parsing layer: Real-time monitoring of MySQL binlog, parsing into a standardized event stream; Data transformation engine: dynamically handles data type mapping and SQL syntax conversion; Transaction consistency controller: Ensures atomicity of cross-database transactions based on two-phase commit; Exception rollback module: Records operation logs and supports breakpoint resume and data compensation.

6. The system according to claim 5, characterized in that, Incremental logs are parsed using multi-threading, and parallel binlog event processing is employed: events are distributed to different threads based on table name hashes.

7. The system according to claim 5, characterized in that, Dynamic data type mapping Built-in type-compatible rule base, supporting custom mapping; Automatic data overflow repair: Dynamic sharding is triggered when bigint is detected to be out of range.

8. The system according to claim 5, characterized in that, Transaction consistency guarantee mechanism Global transaction ID tracking: Assign a unique ID to each transaction to ensure transaction ordering and idempotency; Asynchronous confirmation retry: If a write operation fails in the Ruohan high-end system, the transaction will be replayed based on the unique ID until it succeeds.