Method, system and electronic equipment for synchronizing openGauss database data to heterogeneous databases

Abstract

The application discloses a method and system for synchronizing data of an openGauss database to a heterogeneous database and electronic equipment, and relates to the technical field of databases, wherein a logical replication slot and a publication and subscription mechanism are created in the openGauss database; a source database connector is configured; a Kafka message middleware is configured; a target database connector is configured; the source database connector and the target database connector are started; source database change events are continuously monitored; and the change events are transmitted to the target database through the Kafka. The application adopts real-time data synchronization based on openGauss logical replication + Kafka message bus + heterogeneous database adaptation, realizes automatic capture of change events, relies on a WAL calibration control layer and Kafka partition buffering, realizes cross-process log sequencing and rhythm matching, maintains millisecond-level end-to-end delay, and provides low conversion efficiency.

Description

Technical Field

[0001] This invention relates to the field of database technology, specifically to a method, system, and electronic device for synchronizing data from an OpenGauss database to a heterogeneous database. Background Technology

[0002] Commercial or community-based relational databases based on the openGauss open-source database kernel have become an important part of information technology application innovation. More and more applications are starting to use these databases. Currently, relational databases based on the openGauss kernel face the following problems when performing data synchronization and backup on heterogeneous databases (such as MySQL):

[0003] First, MySQL employs a single-process, multi-threaded architecture, with all storage engines sharing the same server-layer thread pool. In contrast, openGauss is based on a PostgreSQL-based multi-process architecture, where each session operates as an independent process and relies on shared memory for communication. MySQL's single-process model uses group commit and sequential binlog writing for transaction commits, while openGauss's multi-process model requires coordinating multiple background processes to write to the Write Ahead Log (WAL) during logical replication. This architectural difference leads to a natural misalignment in timing and state management between openGauss's log generation and acknowledgment order and MySQL's binlog consumption model. Simply migrating by exporting and importing files will result in delays and verification difficulties.

[0004] Secondly, while MySQL allows for custom data storage structures through pluggable storage engines (such as InnoDB and MyISAM), openGauss employs a single storage engine and integrates row-column hybrid storage and Multi-Version Concurrency Control (MVCC). OpenGauss's WAL decoding requires a precise understanding of its internal column completion, UNDO / REDO, and other semantics, which traditional MySQL-based binlog tools cannot directly parse. This difference in storage engines can easily lead to problems such as missing fields and misaligned timestamps when performing data type mapping and transaction boundary restoration.

[0005] Currently, the main method for solving the data synchronization problem of heterogeneous databases is for database administrators to manually convert SQL statements between different databases by exporting a dump file from the source OpenGauss database and then synchronizing it to the MySQL database. This method suffers from problems such as long data latency, easy data loss during synchronization, low efficiency of manual conversion, and difficulty in monitoring and scaling. Summary of the Invention

[0006] The purpose of this invention is to provide a method, system, and electronic device for synchronizing data from an OpenGauss database to a heterogeneous database. This invention addresses the problems of low conversion efficiency, long data latency, and data loss, as well as the difficulty in monitoring and scaling data synchronization, which are caused by differences in process models and storage engines when synchronizing data from an OpenGauss database to a heterogeneous database.

[0007] The present invention solves the above problems through the following technical solution:

[0008] A method for synchronizing data from an OpenGauss database to a heterogeneous database includes:

[0009] Logical replication slots and a publish-subscribe mechanism are created in the openGauss database to capture change events;

[0010] Configure the source database connector to continuously monitor change events in the OpenGauss database and send these change events to a Kafka topic;

[0011] Configure the Kafka message middleware to act as the central bus for change events, and use the topic partitioning mechanism to ensure partition routing and order of change events.

[0012] Configure the target database connector so that it consumes change events from the Kafka message middleware, performs database adaptation and translation, and then writes them to the target database.

[0013] Start the source database connector and target database connector, begin continuous monitoring of source database change events, and pass the change events to the target database through the Kafka message middleware.

[0014] This invention is based on openGauss logical publishing and Kafka as the CDC event bus and sequence hub. Changes are decoded into structured events and routed to topic partitions by database table / primary key. It solves the problems of long data synchronization delays, easy data loss, low efficiency of manual conversion, and difficulty in monitoring and scaling when synchronizing data from openGauss-based relational databases to MySQL databases through SQL dialect translation.

[0015] Furthermore, methods for creating logical replication slots and publish-subscribe mechanisms in the openGauss database include:

[0016] Create a publication object, which defines which tables need to be replicated. The publication object supports the full database table mode ALL_TABLES and the specified table filtering mode FILTERED_TABLES. Preferably, the publication object is created using the openGauss CREATEPUBLICATION command.

[0017] A logical replication slot is created. The replication slot uses a decoding plugin to perform structured decoding of the WAL log. The replication slot will capture change events based on the published object and persistently store the replication progress object. The decoding plugins include pgoutput, wal2json, and the custom ogoutput / ogproto plugins. It also includes converting captured change events into structured ChangeEvent objects, which contain information such as table name, operation type (INSERT / UPDATE / DELETE), data before change, and data after change.

[0018] Install and enable the WAL calibration extension in the openGauss database. The WAL calibration extension wal_calibrator is used to collect WAL log fragments from multiple background processes and write them to the calibration buffer, as well as enable calibration for specified replication slots. It reorders the WAL fragments generated by different processes according to timestamps and log sequence numbers (LSNs) to generate a monotonically increasing locus sequence.

[0019] The system achieves non-intrusive monitoring of the source database through a logical replication mechanism, and uses the WAL calibration control layer to unify the output of multiple processes into a monotonically increasing sequence of sites, ensuring the integrity, temporal consistency and real-time performance of data capture. It also supports flexible table filtering configuration to optimize performance and resource consumption.

[0020] Furthermore, configuring the source database connector includes:

[0021] Connector basic configuration: Set the source database connector name, specify the connector class as the openGauss connector implementation class, which is responsible for establishing a logical replication connection with the openGauss database and reading change events from the replication slot;

[0022] Configure database connection parameters to ensure that the source database connector is connected to the source database;

[0023] Replication configuration parameters: Specify the replication slot name, publication object name, and decoding plugin name that are consistent with those in the creation of logical replication slots and the publish-subscribe mechanism, respectively, to ensure that the source database connector uses the correct replication slot and publication object to capture change events;

[0024] WAL calibration configuration: Enable WAL calibration function, specify the calibration channel name to be consistent with the WAL calibration extension wal_calibrator. After the calibration function is enabled, the source database connector will prioritize reading the calibration results output by wal_calibrator and writing them to the calibration buffer, instead of directly reading unsorted WAL fragments from the copy slot.

[0025] Site mapping configuration: Sets the cache file path for mapping the log sequence number (LSN) of the openGauss database to the global transaction identifier (GTID) of the target database.

[0026] Further, configuring the Kafka message middleware includes:

[0027] In the Kafka cluster configuration environment, start the Zookeeper service and Kafka Broker service to realize the coordination management and message broker node functions of the Kafka cluster;

[0028] Create topics in the Kafka cluster. Topics are used to store change events, ensuring the orderly delivery and persistent storage of data.

[0029] Furthermore, the topic includes a topic name, number of partitions, and replication factor to ensure the ability to handle a large number of concurrent change events, while guaranteeing message reliability through a replication mechanism.

[0030] Using Apache Kafka as the central bus for CDC events, and employing a topic partitioning mechanism to achieve efficient event routing and order guarantees, ensures the reliability and scalability of data synchronization, including:

[0031] Dynamically create Kafka topics based on source table name and schema information, supporting custom topic prefix configuration;

[0032] Partition routing is performed based on the primary key field of the table to ensure that change events for the same record arrive in sequence, thereby improving data consistency;

[0033] Use Avro or JSON format to serialize change events, and support compression to reduce network transmission overhead;

[0034] It supports parallel processing by multiple consumers, and automatically achieves load balancing and failover within the consumer group.

[0035] Kafka, acting as an event bus, decouples the data source and target system, improving the scalability and fault tolerance of the entire synchronization system. By leveraging Kafka partition buffering, breakpoint resumption, and transaction position verification, millisecond-level data synchronization latency is achieved, ensuring smooth traffic and high reliability even during bursts of openGauss multi-process logs and limited write operations in MySQL single-process systems. This is reflected in:

[0036] 1) Multi-threaded architecture: Adopting the producer-consumer pattern, multiple worker threads process synchronization tasks of different tables in parallel.

[0037] 2) Resume interrupted downloads: Based on the Kafka offset mechanism, the synchronization progress is persisted and supports automatic recovery after system restart.

[0038] 3) Error handling: Implement a comprehensive exception handling mechanism, including automatic retries for scenarios such as network errors, database connection failures, and SQL execution errors.

[0039] 4) Data monitoring: Provides real-time monitoring metrics, including synchronization latency, processing speed, error statistics, etc., and supports custom alarm rules.

[0040] Furthermore, the method for configuring the target database connector is as follows:

[0041] Specify the Kafka topic to be consumed for the target database connector;

[0042] Configure schema mapping relationships, which are used to convert schema names in the openGauss database into table names in the target database;

[0043] Batch processing and retry configuration;

[0044] Configure the target database translation engine, which is used to translate openGauss SQL statements into SQL statements of the target database;

[0045] Semantic mapping configuration is used to handle the semantic differences between the openGauss column-oriented storage engine and the MySQL row-oriented storage engine, restoring the openGauss column-oriented fields, MVCC snapshots and constraint information into write statements that can be recognized by the MySQL row-oriented storage engine.

[0046] The MVCC replay strategy configuration is used to instruct the connector to acquire the necessary row locks before writing to MySQL;

[0047] GTID alignment configuration sets the GTID alignment file path. After consuming Kafka change events, the target database connector confirms the transaction order based on the LSN-GTID mapping in the GTID alignment file, ensuring that the execution order of transactions in the target database is consistent with that in the source database.

[0048] Furthermore, the method for configuring the target database translation engine is as follows: create a translation configuration file from the openGauss database to the target database and specify the path of the translation configuration file. The translation configuration file includes data type mapping rules, function mapping rules, keyword mapping rules, and syntax mapping rules.

[0049] This invention addresses the differences between the single column-oriented storage engine (openGauss) and the pluggable row-oriented storage engine (MySQL) in terms of data types, MVCC visibility, and constraint semantics. It designs a flexible SQL conversion and semantic mapping engine to achieve data type mapping, transaction compensation, and syntax conversion from openGauss to heterogeneous databases such as MySQL. This includes:

[0050] 1) Data type mapping: Establish a data type mapping table between openGauss and the target database, supporting automatic type conversion (such as timestamp processing, character set conversion, etc.).

[0051] 2) SQL syntax conversion: Implement dialect conversion for operations such as INSERT, UPDATE, and DELETE, and handle special syntax differences (such as converting openGauss's timestamp function to the standard MySQL format).

[0052] 3) SQL statement translation engine:

[0053] a) DDL statement conversion: Convert openGauss's CREATE TABLE statement into the target database's DDL, handling differences in field types, constraints, indexes, etc.

[0054] b) DML statement conversion: Convert openGauss INSERT / UPDATE / DELETE statements into DML of the target database, handling syntax differences and function conversions.

[0055] c) Function mapping: Establish a mapping table from openGauss functions to target database functions, such as the conversion of time functions like NOW() and CURRENT_TIMESTAMP.

[0056] d) Keyword processing: Handle the differences in reserved keywords between different databases to ensure the compatibility of SQL statements.

[0057] e) MVCC semantic compensation: Based on the snapshot information in the openGauss CDC event, deduce the row-level locks and version flags required by MySQL InnoDB to avoid data loss or dirty writes caused by visibility differences.

[0058] 4) Target database connection: Supports multiple JDBC drivers, including MySQL, Oracle, SQL Server, etc., to achieve a unified database operation interface.

[0059] 5) Batch processing optimization: Implement batch writing and transaction control, optimize synchronization performance and ensure data consistency.

[0060] By abstracting the SQL tool layer, the differences between heterogeneous databases are shielded, providing a unified synchronization interface for upper-layer applications.

[0061] Furthermore, it also includes data synchronization verification, and the methods for data synchronization verification include one or more of the following:

[0062] a. Check the source database connector status to confirm that the source database connector is started normally and is capturing change events;

[0063] b. Check the target database connector status to confirm that the target database connector is started normally and is consuming Kafka messages and writing to the target database;

[0064] c. Check the Kafka topic data to verify whether the openGauss change events have been successfully sent to the Kafka topic and whether the message format is correct;

[0065] d. Check the target database data to verify whether the data has been successfully synchronized to the target database and whether the data content is correct;

[0066] e. Test data change synchronization to verify whether data changes in the source database can be captured and synchronized to the target database in real time;

[0067] f. Test the SQL translation function to verify whether the SQL translation engine can correctly translate openGauss functions into their corresponding MySQL functions.

[0068] A system for synchronizing data from an OpenGauss database to a heterogeneous database includes:

[0069] The openGauss database is configured to capture change events by creating logical replication slots and using a publish-subscribe mechanism;

[0070] The source database connector is configured to continuously monitor change events in the openGauss database and send these change events to a Kafka topic.

[0071] The Kafka message middleware is configured to receive change events sent by the source database connector and uses a topic partitioning mechanism to ensure partition routing and ordering of change events.

[0072] The target database connector is configured to consume change events from the Kafka message middleware, perform database adaptation and translation, and then send them to the target database.

[0073] The target database is configured to receive and write data sent by the target database connector.

[0074] An electronic device includes a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement a method for synchronizing openGauss database data to a heterogeneous database.

[0075] Compared with the prior art, the present invention has the following advantages and beneficial effects:

[0076] (1) This invention adopts real-time data synchronization based on openGauss logical replication + Kafka message bus + heterogeneous database adaptation to achieve automatic capture of change events. Relying on WAL calibration control layer and Kafka partition buffer, it realizes cross-process log sequencing and rhythm matching, maintains millisecond-level end-to-end latency, and solves the problems of long data synchronization delay and low efficiency of manual conversion in traditional manual intervention methods.

[0077] (2) The present invention adopts a multi-level fault tolerance mechanism including breakpoint resume, automatic retry, and failover, and uses the LSN-GTID lookup table as the core to ensure that the transaction order is not disordered after the abnormal recovery, which has high availability and fault tolerance, and solves the problem of easy data loss during synchronization.

[0078] (3) The present invention has strong heterogeneous database compatibility: it supports synchronization of openGauss to multiple target databases such as MySQL, Oracle, and SQL Server, and handles column storage / row storage differences, MVCC semantics and function mapping through SQL dialect conversion and semantic mapping engine.

[0079] (4) Scalability and flexibility of the present invention: It supports two modes: full database synchronization and filter table synchronization. The synchronization range can be flexibly configured according to business needs. The multi-threaded architecture supports high concurrency processing.

[0080] (5) The present invention provides convenient monitoring and maintenance: it provides real-time monitoring indicators, detailed log records and custom alarm rules, which greatly reduces the complexity of operation and maintenance and improves the efficiency of problem location.

[0081] (6) Data consistency guarantee of the present invention: the progress management based on Kafka offset ensures the accuracy and consistency of data synchronization, avoiding data loss and duplicate synchronization problems.

[0082] (7) The present invention has high resource utilization efficiency: the system resource utilization is optimized by batch processing and connection pool technology, reducing the performance impact on the source database and the target database. Attached Figure Description

[0083] Figure 1 This is a schematic diagram of the system architecture according to an embodiment of the present invention;

[0084] Figure 2 This is a flowchart of an embodiment of the present invention;

[0085] Figure 3 This is a schematic diagram of the SQL conversion engine logic in an embodiment of the present invention. Detailed Implementation

[0086] The present invention will be further described in detail below with reference to embodiments, but the implementation of the present invention is not limited thereto.

[0087] Example 1:

[0088] Combined with appendix Figure 2 As shown, a method for synchronizing data from an OpenGauss database to a heterogeneous database includes:

[0089] Environmental preparation:

[0090] 1) Source database: openGauss 6.0.0, running on the openEuler-2203-sp3 system, with IP address ***.***.*.*** and port 15400.

[0091] 2) Target database: MySQL 8.0.25, running on Ubuntu 20.04 system, IP address ***.***.*.***, port 3306.

[0092] 3) Message middleware: Apache Kafka 2.8.0 cluster, containing 3 Broker nodes, and Zookeeper cluster with 3 nodes.

[0093] Step 1: Configure the openGauss source database

[0094] 1) Enable Logical Replication: In the openGauss database configuration, first set the WAL log level to logical replication mode. This setting enables the database to output logical change records, not just physical change records. Simultaneously, configure the maximum number of WAL senders (e.g., 10). This maximum number of WAL senders limits the number of connections performing logical replication simultaneously, ensuring reasonable allocation of system resources. After configuration, reload the database configuration for the above settings to take effect.

[0095] 2) Create a replication user: Create a dedicated user account for logical replication in the openGauss database. This user needs to have replication privileges (REPLICATION privileges). Then, grant this user connection privileges (CONNECT privileges) to the target database and usage privileges (USAGE privileges) to the target schema, ensuring that the user can access the database objects that need to be replicated.

[0096] 3) Create a Publication: Create a publication object in the openGauss database to define which tables need to be replicated. The publication object can be set to include all tables (ALL_TABLES mode) or only specified tables (specifying table names via the TABLE clause, such as users, orders, products, etc.). The publication object serves as the logical unit for replication; subsequent replication slots will capture change events based on this publication object.

[0097] 4) Create a logical replication slot: Create a logical replication slot in the openGauss database. The replication slot uses a decoding plugin to perform structured decoding of the WAL log. The logical replication slot is an object used to persistently store the replication progress. It records the position of the WAL logs sent downstream, ensuring that replication progress is not lost even if the system restarts. The name of the replication slot and the plugin type need to be referenced in the subsequent source database connector configuration.

[0098] 5) Enable WAL calibration control: Install and enable the WAL calibration extension (wal_calibrator extension) in the openGauss database. This extension can collect WAL log fragments from multiple background processes and write them to the calibration buffer through a custom hook function. Then, the extension's enable_slot function is called to enable calibration for the specified replication slot. The calibration module will reorder the WAL fragments generated by different processes according to their timestamps and LSNs (Log Sequence Numbers), generating a monotonically increasing sequence of positions, thus resolving the issue of inconsistent log order in multi-process architectures.

[0099] Step 1 configures the logical replication function of the openGauss database, enabling it to capture data change events and send them downstream. The replication slot, combined with the wal_calibrator extension, unifies the sorting of WAL fragments generated by multiple background processes and writes them to the calibration buffer, providing a basis for subsequent LSN-GTID comparisons and avoiding data loss and site drift.

[0100] This invention addresses the issues of position misalignment and latency caused by differences in process models in heterogeneous databases in existing technologies. The multi-process WAL generation mechanism of openGauss and the single-process binlog consumption model of MySQL are incompatible in terms of log order and transaction confirmation strategies, easily leading to position drift and replay blocking. This invention requires the construction of a "WAL calibration control layer" to uniformly sort the WAL fragments output by multiple processes, generate an LSN-GTID lookup table, and, in conjunction with Kafka partitioning buffers, achieve cross-system transaction order alignment, thereby resolving the risks of synchronization latency and event loss. This invention achieves real-time synchronization across architectures based on openGauss's logical publishing and dedicated log alignment algorithm.

[0101] Step 2: Configure Kafka message middleware

[0102] Configuring a Kafka cluster requires the following steps:

[0103] 1) Start the Zookeeper service: In the Kafka cluster configuration environment, the first step is to start the Zookeeper service. Zookeeper is the coordination service for the Kafka cluster, responsible for managing the Kafka cluster's metadata, configuration information, and leader election. When starting Zookeeper, you need to specify the path to its configuration file, which contains parameters such as the data storage path and client connection port.

[0104] 2) Start the Kafka Broker service: After the Zookeeper service is running normally, start the Kafka Broker service. The Kafka Broker is the message broker node in the Kafka cluster, responsible for message storage, forwarding, and consumption. During startup, you need to specify the path to the Broker's configuration file, which contains key parameters such as the Broker ID, listening port, log storage path, and Zookeeper connection address.

[0105] 3) Create a Synchronization Topic: Create a topic in the Kafka cluster to store openGauss change events. Topic creation requires specifying the topic name, the number of partitions (e.g., 3 partitions), and the replication factor (e.g., 1 replica). The number of partitions determines the parallel processing capability of messages, and the replication factor determines the level of message redundancy. After successful topic creation, this topic will be used to store CDC events (structured CRUD events captured from openGauss) – these are the change events captured by the Change Data Capture mechanism and typically include table name, primary key, operation type, field values ​​before and after the change, and position information. This invention uses CDC events as the smallest data unit for cross-system transmission.

[0106] Step 2 configures the Kafka message middleware, providing a high-throughput, fault-tolerant message delivery mechanism for data synchronization. Kafka topics are used to store openGauss change events, ensuring ordered data delivery and persistent storage. Through Kafka's distributed architecture and partitioning mechanism, it can efficiently handle a large number of concurrent change events, while the replication mechanism ensures message reliability.

[0107] This invention provides an "automatic monitoring and fault tolerance mechanism" to continuously detect location, delay and anomalies, and realize unattended real-time synchronous closed loop to solve the operational risks caused by manual dumping.

[0108] Step 3: Deploy the Debezium openGauss connector, i.e., the source and target database connector.

[0109] The following parameters need to be set when configuring the connector:

[0110] 1) Basic connector configuration: The connector name is set to opengauss-connector, and the connector class is specified as Debezium's openGauss connector implementation class. This connector class is responsible for establishing a logical replication connection with the openGauss database and reading change events from the replication slots.

[0111] 2) Database connection parameters: Set the openGauss database host address to ***.***.*.***, port to 5432, and database name to postgres. Use the replication user (replication_user) and its password created in step 1 for connection authentication. These parameters ensure that the connector can correctly connect to the source database.

[0112] 3) Replication configuration parameters: Specify the replication slot name as opengauss_slot (the same as the replication slot name created in step 1), the publication name as opengauss_publication (the same as the publication name created in step 1), and the decoding plugin name as pgoutput. These parameters ensure that the connector uses the correct replication slot and publication object to capture change events.

[0113] 4) WAL Calibration Configuration: Enable WAL calibration and specify the calibration channel name as wal_calibrator. After enabling calibration, the connector will prioritize reading the calibration results from the extended output of wal_calibrator, instead of directly reading unsorted WAL fragments from the copy slot. This calibration mechanism can resolve the issue of inconsistent log order in multi-process architectures.

[0114] 5) Site Mapping Configuration: Set the LSN to GTID mapping cache file path to / opt / debezium / state / lsn-gtid-map.json. This mapping cache is used to persistently store the mapping relationship between openGauss's LSN (Log Sequence Number) and MySQL's GTID (Global Transaction Identifier), ensuring that the replication site can be accurately restored after a restart and guaranteeing the consistency of transaction order.

[0115] 6) Other configuration parameters: Set the delete operation flag to false, indicating that no message will be sent in Kafka when deleting data. Set the heartbeat interval to 5000 milliseconds. This parameter is used to periodically send heartbeat messages to the database to detect connection status and replication latency.

[0116] Step 3 deployed the Debezium openGauss connector, enabling the capture of change events from the openGauss database and their transmission to Kafka. By enabling the wal.calibration parameter, the connector prioritizes reading the calibration results output by wal_calibrator, generating a message containing the source LSN, transaction number, and CDC event payload. lsn.gtid.mapping.cache.path is used to persist the LSN-GTID mapping, ensuring site traceability and thus guaranteeing real-time performance and consistency. After the connector starts, it continuously monitors changes to the openGauss database and converts change events into structured CDC events, sending them to a Kafka topic.

[0117] Step 4: Configure the target database connector, including configuring the messaging connector and the SQL translation engine:

[0118] 1) Configure the MySQL target database connector (consumer connector): The connector name is mysql-sink-connector, and the connector class is specified as the Debezium openGauss Sink connector implementation class. This connector is responsible for consuming CDC events from Kafka and writing them to the target database. Set the target database connection URL to jdbc:mysql: / / ***.***.*.***:3306 / target_db, the database driver is the MySQL JDBC driver, and use the root user and its password for connection authentication.

[0119] 2) Topic and Schema Mapping Configuration: Specify the Kafka topics to be consumed as opengauss.public.users and opengauss.public.orders. These topics contain change events captured from openGauss. Configure the schema mapping relationship as public.users to users and public.orders to orders. This mapping is used to translate schema names in openGauss into table names in MySQL.

[0120] 3) Batch processing and retry configuration: Set the batch write size (e.g., 1000 records). This parameter controls the number of records written in each batch, improving write performance. Configure the maximum number of retries (e.g., 3 times) and the retry backoff time (e.g., 1000 milliseconds). These parameters are used to handle temporary errors such as network anomalies and database connection failures, ensuring the reliability of data synchronization.

[0121] 4) SQL Translation Engine Configuration: Enable SQL translation, specify the translation engine type as opengauss-to-mysql, and set the translation configuration file path to:

[0122] The file ` / opt / debezium / config / opengauss-mysql-translation.json` contains the SQL translation engine, which is responsible for translating OpenGauss SQL statements into MySQL SQL statements, handling differences in data types, functions, keywords, and syntax.

[0123] This translation configuration file contains the following mapping rules:

[0124] Data type mapping: Map the openGauss timestamp type to the MySQL DATETIME type, the timestamptz type to the DATETIME type, the serial type to INT AUTO_INCREMENT, the bigserial type to BIGINT AUTO_INCREMENT, the text type to TEXT, the varchar type to VARCHAR, the boolean type to TINYINT(1), the uuid type to CHAR(36), and both json and jsonb types to JSON type.

[0125] Function mapping: The openGauss NOW() function is mapped to the MySQL NOW() function, the CURRENT_TIMESTAMP function is mapped to the CURRENT_TIMESTAMP function, the CURRENT_DATE function is mapped to the CURDATE() function, the CURRENT_TIME function is mapped to the CURTIME() function, the EXTRACT(EPOCH FROM timestamp) function is mapped to the UNIX_TIMESTAMP(timestamp) function, the to_char(timestamp, 'YYYY-MM-DD') function is mapped to the DATE_FORMAT(timestamp, '%Y-%m-%d') function, and the to_timestamp(epoch) function is mapped to the FROM_UNIXTIME(epoch) function.

[0126] Keyword mapping: Maps reserved words in openGauss that may be used as table or column names (such as user, order, group, index) to MySQL's backticked form (such as `user`, `order`, `group`, `index`) to avoid conflicts with MySQL's reserved keywords.

[0127] Syntax mapping: Map openGauss's INSERT ... RETURNING syntax to MySQL's INSERT ... SELECT LAST_INSERT_ID() syntax, UPDATE ... RETURNING syntax to UPDATE ... SELECT ROW_COUNT() syntax, and DELETE ... RETURNING syntax to DELETE ... SELECT ROW_COUNT() syntax, because MySQL does not support the RETURNING clause.

[0128] like Figure 3 As shown, the SQL translation engine includes a message parser, SQL builder, SQL converter, and MySQL adapter. It implements data type mapping, transaction compensation, and syntax conversion from OpenGauss to heterogeneous databases such as MySQL. Specifically: the message parser is used to identify event types, extract fields, and parse metadata from Kafka messages; the SQL builder converts OpenGauss INSERT / UPDATE / DELETE statements into DML statements of the target database through DML statement conversion, handling syntax differences and function conversions; the SQL converter implements data type mapping, function conversion, keyword processing, and syntax conversion; and the MySQL adapter connects to the target database through a JDBC driver, performing connector management and data writing.

[0129] The SQL translation engine automatically converts openGauss SQL statements into SQL statements for the target database, including data type mapping, function conversion, keyword processing, and syntax transformation. It is configured with parameters such as batch processing and retry mechanisms to ensure the reliability and performance of data synchronization. It supports multiple target databases including MySQL, Oracle, and SQL Server.

[0130] Through schema mapping configuration and SQL translation engine, table structure conversion and SQL statement translation between heterogeneous databases are achieved.

[0131] 5) Semantic Mapping Configuration: Enable semantic mapping by specifying the semantic mapping configuration file as column-to-rowstore. This configuration handles the semantic differences between the openGauss column-oriented storage engine and the MySQL row-oriented storage engine, restoring openGauss column-oriented fields, MVCC snapshots, and constraint information into write statements recognizable by MySQL row-oriented storage.

[0132] 6) MVCC replay strategy configuration: Set the MVCC replay strategy to lock_safe (lock-safe mode). This strategy instructs the connector to acquire the necessary row locks before writing to MySQL to avoid concurrent dirty writes and ensure the consistency of semantics between the two storage engines.

[0133] 7) GTID Alignment Configuration: Set the GTID alignment file path to:

[0134] The file ` / opt / debezium / state / lsn-gtid-map.json` contains the mapping relationship between LSNs and GTIDs. After consuming Kafka CDC events, the connector uses the LSN-GTID mapping in this file to confirm the transaction order, ensuring that the execution order of transactions in the target database is consistent with that in the source database.

[0135] This invention addresses the semantic loss problem caused by differences in storage engine mechanisms in heterogeneous databases in existing technologies: openGauss uses a single converged storage engine, exhibiting semantics such as column-level completion and MVCC visibility; MySQL uses a pluggable row-level storage engine, requiring precise row-level change replay. Traditional dump methods cannot restore these semantics, resulting in missing fields or inconsistent constraints. This invention designs an SQL translation engine to implement dialect conversion and semantic mapping, performs type normalization, DDL / DML rewriting, and constraint compensation for CDC events, ensuring row-level data consistency in the target database.

[0136] Step 4 configures the target database connection and SQL translation engine, enabling the consumption of change events from Kafka and writing to various heterogeneous databases. After consuming Kafka CDC events, the target connector confirms the transaction order based on the LSN-GTID mapping in gtid.alignment.file. Following the semantic mapping configuration file column-to-rowstore, it restores the OpenGauss column-oriented fields, MVCC snapshots, and constraint information into write statements recognizable by MySQL row-oriented storage. `mvcc.replay.strategy=lock_safe` instructs the connector to acquire necessary row locks before writing to avoid concurrent dirty writes, ensuring semantic consistency between the two storage engines.

[0137] Step 5: Start data synchronization

[0138] To initiate data synchronization, the following steps are required:

[0139] 1) Start the source database connector (openGauss CDC connector): Send an interface request to the Kafka Connect service with the openGauss connector configuration file (opengauss-connector.json) configured in step 3 in the request body. This request will create and start the openGauss source connector, which will begin capturing change events from the openGauss database and sending them to Kafka topics.

[0140] 2) Start the MySQL target database connector: Send an interface request to the Kafka Connect service, with the request body containing the MySQL Sink connector configuration file (mysql-sink-connector.json) configured in step 4. This request will create and start the MySQL target connector, which will then begin consuming CDC events from the Kafka topic and writing them to the MySQL target database.

[0141] 3) Check connector status: Query the running status of two connectors. The status query results include connector status (RUNNING, FAILED, PAUSED, etc.), number of tasks, and task status, which are used to verify whether the connectors have started and are running normally.

[0142] Step 5 initiates the complete data synchronization process, achieving real-time data synchronization from openGauss to various heterogeneous databases. The system continuously monitors changes in the source database and transmits change events to the target database via the Kafka message bus, ensuring data consistency and real-time performance. A one-to-many synchronization mode is supported, allowing one openGauss source to synchronize to multiple target databases simultaneously. After the connector starts, change events in the openGauss database are captured in real-time, converted into CDC events, and sent to Kafka. These events are then consumed by the target connector and written to the target database, achieving real-time data synchronization.

[0143] This invention performs logical decoding of the WAL log in openGauss, subscribes to incremental changes (INSERT / UPDATE / DELETE) by table / column, serializes and transmits them to middleware via plugins / connectors, completes type and structure mapping, maintains transaction order and position, and converts changes into executable MySQL DML and DDL, ensuring idempotency and at least one delivery, thus achieving low-latency real-time synchronization between the openGauss database and MySQL. The advantages are:

[0144] Kafka queue consumption: It uses openGauss logical publishing + Kafka as the CDC event bus and sequential execution hub. Changes are decoded into structured events, routed to topic partitions by database table / primary key, and carry LSN, TxId, commit_ts, schema version to achieve millisecond-level data transmission response.

[0145] SQL dialect conversion: Provides a SQL-oriented dialect translation and idempotent rewriting conversion layer, DML / DDL semantic mapping, type / character set / time zone / precision, auto-increment / sequence / unique constraint adaptation, and outputs MySQL executable INSERT…ONDUPLICATE KEY, REPLACE, or segmented UPDATE statements.

[0146] Real-time monitoring: Out-of-order reordering and batch commits are performed based on LSN+transaction boundaries; offset+LSN dual checkpoints enable breakpoint resumption and "at least once / near-exact once" verification; snapshots+incremental overlapping checks ensure consistency. It supports primary key hash fixed routing to ensure total order across partitions, and features backpressure and elastic parallelism, DLQ / replay, loop and backfeed protection, DDL pass-through and shadow publishing / canary switching, hotspot avoidance, multi-tenant isolation and audit serial numbers, latency / throughput / location observability, and self-healing capabilities.

[0147] Furthermore, it may also include:

[0148] Step 6: Verify the synchronization effect

[0149] To verify the synchronization effect, the following steps need to be completed:

[0150] 1) Check the source connector status: Query the running status of the openGauss source connector via the Kafka Connect REST API. The status query results include the connector status (RUNNING indicates normal operation, FAILED indicates failure, PAUSED indicates paused), the number of tasks, and the task status. This operation confirms whether the source connector has started normally and is capturing change events.

[0151] 2) Check the status of the target connectors: Query the running status of each target connector using the Kafka Connect REST API. The status query results include information such as the connector status, number of tasks, and task status. This operation confirms whether the target connectors have started normally and are consuming Kafka messages and writing to the target database.

[0152] 3) Check Kafka topic data: Using the Kafka command-line consumer tool, connect to the Kafka Broker (bootstrap-server is localhost:9092), consume messages from the specified topic (opengauss.public.users), and set the consumption mode to consume from the beginning of the topic (from-beginning). This operation is used to verify whether openGauss change events are successfully sent to the Kafka topic and whether the message format is correct.

[0153] 4) Check the data in the target MySQL database: Connect to the target database using a MySQL client (host address: ***.***.*.***, username: root, database name: target_db), and execute an SQL query to count the total number of records in the target table "users". Then execute a query to retrieve the first 5 records in the target table "users" sorted by ID in descending order. This operation verifies whether the data has been successfully synchronized to the target database and whether the data content is correct.

[0154] 5) Test Data Change Synchronization: Connect to the openGauss source database using a PostgreSQL client (host address: ***.***.*.***, username: postgres, database name: postgres). Execute an INSERT statement to insert a test record into the users table. The name field of the test record is named test_user, and the email field is named test@example.com. This operation verifies whether data changes in the source database can be captured and synchronized to the target database in real time.

[0155] 6) Verify the data change synchronization result: After waiting for 5 seconds, connect to the target database using a MySQL client and execute an SQL query to retrieve the record in the target table `users` where the `name` field is `test_user`. This operation verifies whether the test data inserted in step 5 has been successfully synchronized to the target database. If the query result contains this record, it indicates that the data change synchronization function is working correctly.

[0156] 7) Test the SQL translation function: Connect to the openGauss source database using a PostgreSQL client, and execute an INSERT statement to insert a test record containing a special function into the users table. The name field of the test record is sql_test, the email field is sql@example.com, and the creation time field uses the NOW() function to obtain the current time. This operation is used to verify whether the SQL translation engine can correctly convert the openGauss time function to the corresponding MySQL time function.

[0157] 8) Verify the SQL translation result: After waiting for 3 seconds, connect to the target database using a MySQL client and execute an SQL query to retrieve the record in the target table `users` where the `name` field is `sql_test`. This operation verifies whether the `created_at` field of the test data inserted in step 7 has been correctly converted to the MySQL time format. If the value of the `created_at` field is correct, it indicates that the SQL translation engine's time function conversion is working properly.

[0158] Step 6 comprehensively verified the normal operation of the data synchronization system by checking the connector status, Kafka topic data, target database data, and conducting actual data change synchronization tests. The functionality of the SQL translation engine was specifically verified to ensure that openGauss SQL statements could be correctly translated into target database SQL statements, including conversions of time functions, data types, and keywords. The verification results demonstrate the effectiveness and reliability of the openGauss-based heterogeneous data real-time synchronization system, ensuring accurate and real-time data synchronization from the source database to multiple heterogeneous target databases while maintaining SQL statement compatibility. The WAL calibration control layer resolved the issue of inconsistent log order in a multi-process architecture; the SQL dialect conversion and semantic mapping layer resolved the semantic loss problem caused by storage engine differences, achieving real-time data synchronization across architectures.

[0159] This invention, through the implementation of the above six steps, successfully achieves real-time data synchronization from the openGauss source database to heterogeneous target databases such as MySQL, with synchronization latency controlled at the millisecond level. The SQL translation engine can automatically handle syntactic differences between different databases, ensuring accurate data conversion. The Kafka message bus provides a high-throughput, fault-tolerant message passing mechanism, supporting one-to-many synchronization modes. The system has comprehensive monitoring and alarm functions, achieving highly available and high-performance data synchronization services, providing strong technical support for enterprise data integration, business system migration, and other scenarios.

[0160] Example 2:

[0161] Combined with appendix Figure 1 As shown, a system for synchronizing data from an OpenGauss database to a heterogeneous database includes:

[0162] The openGauss database is configured to capture change events by creating logical replication slots and using a publish-subscribe mechanism;

[0163] The source database connector (connector cluster) is configured to continuously monitor change events in the openGauss database and send these events to a Kafka topic; it enables change event capture, data serialization, and breakpoint resume.

[0164] Kafka message middleware (Kafkax message bus cluster) is configured to receive change events sent by the source database connector, and implements partition routing and order guarantee of change events through topic partitioning mechanism; it also implements topic partition management, message persistence and replica synchronization.

[0165] The target database connector (MySQL database adaptation and SQLz transformation (translation) engine cluster) is configured to consume change events from the Kafka message middleware, perform database adaptation and SQL transformation engine translation, and then send them to the target database; it implements SQL translation, data type mapping, batch processing, and transaction control.

[0166] The target database is configured to receive and write data sent by the target database connector.

[0167] In this invention:

[0168] 1. The queue consumption component subscribes to the logical replication publication of the source openGauss in real time, and subscribes to the source events to the Kafka queue component in real time and in an orderly manner with millisecond-level latency, thus achieving the effect of low-latency data synchronization.

[0169] 2. The SQL dialect translation component in the target database connector reads data from the Kafka queue in real time. After reading the corresponding SQL statements, it translates the source statements into statements that can be executed by a MySQL database using the dialect translator. This achieves the technical effect of SQL dialect translation and rewriting.

[0170] By logically decoding the WAL logs in openGauss, subscribing to incremental changes (INSERT / UPDATE / DELETE) by table / column, and serializing and transmitting them to the Kafka message middleware via plugins / connectors, the system completes the type and structure mapping, transaction order and position maintenance, and converts the changes into executable MySQL DML and DDL, ensuring idempotency and at least one delivery, thus achieving low-latency real-time synchronization between the openGauss database and MySQL.

[0171] Example 3:

[0172] An electronic device includes a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, it implements the method for synchronizing openGauss database data to a heterogeneous database as described in Embodiment 1.

[0173] Although the present invention has been described herein with reference to illustrative embodiments, the above embodiments are merely preferred embodiments of the present invention, and the implementation of the present invention is not limited to the above embodiments. It should be understood that those skilled in the art can devise many other modifications and implementations, which will fall within the scope and spirit of the principles disclosed in this application.

Claims

1. A method for synchronizing data from an OpenGauss database to a heterogeneous database, characterized in that, include: Logical replication slots and a publish-subscribe mechanism are created in the openGauss database to capture change events; Configure the source database connector to continuously monitor change events in the openGauss database and send these change events to a Kafka topic; Configure the Kafka message middleware to act as the central bus for change events, and use the topic partitioning mechanism to ensure partition routing and order of change events. Configure the target database connector so that it consumes change events from the Kafka message middleware, performs database adaptation and translation, and then writes them to the target database. Start the source database connector and the target database connector, begin continuous monitoring of source database change events, and pass the change events to the target database through the Kafka message middleware; Methods for creating logical replication slots and publish-subscribe mechanisms in the openGauss database include: Create a publication object, which defines which tables need to be replicated. The publication object supports the full database table mode ALL_TABLES and the specified table filtering mode FILTERED_TABLES. Create a logical replication slot. The replication slot uses a decoding plugin to perform structured decoding of the WAL log. The replication slot will capture change events based on the published object and persistently store the replication progress object. Install and enable the WAL calibration extension in the openGauss database. The WAL calibration extension wal_calibrator is used to collect WAL log fragments from multiple background processes and write them to the calibration buffer, as well as to enable calibration for specified replication slots. It reorders the WAL fragments generated by different processes according to timestamps and log sequence numbers (LSNs) to generate a monotonically increasing locus sequence. Configuring the source database connector includes: WAL calibration configuration: Enable WAL calibration function, specify the calibration channel name to be consistent with the WAL calibration extension wal_calibrator. After the calibration function is enabled, the source database connector will prioritize reading the calibration results output by wal_calibrator and writing them to the calibration buffer, instead of directly reading unsorted WAL fragments from the copy slot. Site mapping configuration: Sets the cache file path for mapping the log sequence number (LSN) of the openGauss database to the global transaction identifier (GTID) of the target database.

2. The method for synchronizing openGauss database data to a heterogeneous database according to claim 1, characterized in that, Configuring the source database connector also includes: Connector basic configuration: Set the source database connector name, specify the connector class as the openGauss connector implementation class, which is responsible for establishing a logical replication connection with the openGauss database and reading change events from the replication slot; Configure database connection parameters to ensure that the source database connector is connected to the source database; Replication configuration parameters: Specify the replication slot name, publication object name, and decoding plugin name that are consistent with those in the creation of logical replication slots and the publish-subscribe mechanism, respectively, to ensure that the source database connector uses the correct replication slot and publication object to capture change events.

3. The method for synchronizing data from an openGauss database to a heterogeneous database according to claim 1, characterized in that, Configuring the Kafka message middleware includes: In the Kafka cluster configuration environment, start the Zookeeper service and Kafka Broker service to realize the coordination management and message broker node functions of the Kafka cluster; Create topics in the Kafka cluster. Topics are used to store change events, ensuring the orderly delivery and persistent storage of data.

4. The method for synchronizing openGauss database data to a heterogeneous database according to claim 3, characterized in that, The topic includes a topic name, number of partitions, and replication factor, ensuring the ability to handle a large number of concurrent change events while guaranteeing message reliability through a replication mechanism.

5. The method for synchronizing openGauss database data to a heterogeneous database according to claim 1, characterized in that, The method for configuring the target database connector is as follows: Specify the Kafka topic to be consumed for the target database connector; Configure schema mapping relationships, which are used to convert schema names in the openGauss database into table names in the target database; Batch processing and retry configuration; Configure the target database translation engine, which is used to translate openGauss SQL statements into SQL statements of the target database; Semantic mapping configuration is used to handle the semantic differences between the openGauss column-oriented storage engine and the MySQL row-oriented storage engine, restoring the openGauss column-oriented fields, MVCC snapshots and constraint information into write statements that can be recognized by the MySQL row-oriented storage engine. The MVCC replay strategy configuration is used to instruct the connector to acquire row locks before writing to MySQL; GTID alignment configuration sets the GTID alignment file path. After consuming Kafka change events, the target database connector confirms the transaction order based on the LSN-GTID mapping in the GTID alignment file, ensuring that the execution order of transactions in the target database is consistent with that in the source database.

6. The method for synchronizing openGauss database data to a heterogeneous database according to claim 5, characterized in that, The method for configuring the target database translation engine is as follows: create a translation configuration file from the openGauss database to the target database and specify the path of the translation configuration file. The translation configuration file includes data type mapping rules, function mapping rules, keyword mapping rules and syntax mapping rules.

7. The method for synchronizing openGauss database data to a heterogeneous database according to claim 1, characterized in that, It also includes data synchronization verification, and the methods for data synchronization verification include one or more of the following: a. Check the source database connector status to confirm that the source database connector is started normally and is capturing change events; b. Check the target database connector status to confirm that the target database connector is started normally and is consuming Kafka messages and writing to the target database; c. Check the Kafka topic data to verify whether the openGauss change events have been successfully sent to the Kafka topic and whether the message format is correct; d. Check the target database data to verify whether the data has been successfully synchronized to the target database and whether the data content is correct; e. Test data change synchronization to verify whether data changes in the source database can be captured and synchronized to the target database in real time; f. Test the SQL translation function to verify whether the SQL translation engine can correctly translate openGauss functions into their corresponding MySQL functions.

8. A system for synchronizing data from an OpenGauss database to a heterogeneous database, characterized in that, include: The openGauss database is configured to capture change events by creating logical replication slots and using a publish-subscribe mechanism; The source database connector is configured to continuously monitor change events in the openGauss database and send these change events to a Kafka topic. The Kafka message middleware is configured to receive change events sent by the source database connector and uses a topic partitioning mechanism to ensure partition routing and ordering of change events. The target database connector is configured to consume change events from the Kafka message middleware, perform database adaptation and translation, and then send them to the target database. The target database is configured to receive and write data sent by the target database connector. Methods for creating logical replication slots and publish-subscribe mechanisms in the openGauss database include: Create a publication object, which defines which tables need to be replicated. The publication object supports the full database table mode ALL_TABLES and the specified table filtering mode FILTERED_TABLES. Create a logical replication slot. The replication slot uses a decoding plugin to perform structured decoding of the WAL log. The replication slot will capture change events based on the published object and persistently store the replication progress object. Install and enable the WAL calibration extension in the openGauss database. The WAL calibration extension wal_calibrator is used to collect WAL log fragments from multiple background processes and write them to the calibration buffer, as well as to enable calibration for specified replication slots. It reorders the WAL fragments generated by different processes according to timestamps and log sequence numbers (LSNs) to generate a monotonically increasing locus sequence. Configuring the source database connector includes: WAL calibration configuration: Enable WAL calibration function, specify the calibration channel name to be consistent with the WAL calibration extension wal_calibrator. After the calibration function is enabled, the source database connector will prioritize reading the calibration results output by wal_calibrator and writing them to the calibration buffer, instead of directly reading unsorted WAL fragments from the copy slot. Site mapping configuration: Sets the cache file path for mapping the log sequence number (LSN) of the openGauss database to the global transaction identifier (GTID) of the target database.

9. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the method for synchronizing openGauss database data to a heterogeneous database as described in any one of claims 1 to 7.

Images