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

Abstract

The invention discloses a method and a system for synchronizing openGauss database data to a heterogeneous database and electronic equipment, and relates to the technical field of databases. A logic replication slot and a publishing and subscribing mechanism are created in an openGauss database; configuring a source database connector; a Kafka message middleware is configured; configuring a target database connector; and starting the source database connector and the target database connector, continuously monitoring a source database change event, and transmitting the change event to the target database through Kafka. According to the method, real-time data synchronization based on openGauss logic replication, a Kafka message bus and heterogeneous database adaptation is adopted, change events are automatically captured, cross-process log sequentialization and rhythm matching are achieved depending on a WAL calibration control layer and Kafka partition buffering, millisecond-level end-to-end delay is kept, and the conversion efficiency is low.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database, in particular, it is a kind of openGauss database data synchronization to heterogeneous database method, system and electronic equipment. BACKGROUND

[0002] Based on the secondary development of openGauss open source database kernel, commercial or community version relational database has become an important part of information technology application innovation field. More and more applications begin to use this kind of database (such as CHDB of Changhong database). At present, when the relational database based on the secondary development of openGauss kernel carries out data synchronization backup to heterogeneous database (for example, Mysql), the following problems exist:

[0003] Firstly, MySQL adopts single-process multi-thread architecture, and all storage engines share the same Server layer thread pool; while openGauss is based on the multi-process architecture of PostgreSQL, each session independent process and relies on shared memory to realize communication. MySQL adopts group commit and binlog sequential writing under single-process model for transaction submission, while openGauss multi-process model needs to coordinate multiple background processes to write WAL (Write Ahead Log) when executing logical replication. The difference in architecture between the two leads to natural misalignment in the sequence of log generation and confirmation of openGauss and the binlog consumption model of MySQL in timing and state management. If simple file export and import migration is carried out, there will be delay and difficulty in checking.

[0004] Secondly, MySQL allows customizing data storage structure through plug-in storage engine (such as InnoDB, MyISAM), but openGauss adopts single storage engine and integrates row-column hybrid storage and multi-version concurrency control (MVCC) mechanism. The WAL decoding of openGauss needs to accurately understand its internal column storage padding, UNDO / REDO, etc. The traditional binlog tool based on MySQL cannot directly parse these information. The difference in storage engine leads to problems such as field missing and timestamp misalignment when data type mapping and transaction boundary restoration are carried out.

[0005] At present, the main way to solve the problem of data synchronization of heterogeneous databases is that the database operation and maintenance personnel export dump file in the source openGauss database, and then convert the sql statements between different databases manually, and then synchronize to Mysql database. This method has the problems of long data delay time, easy data loss, low efficiency of manual conversion, difficult monitoring and expansion. SUMMARY

[0006] The application aims to provide a method, system and electronic device for synchronizing openGauss database data to a heterogeneous database, to solve the problems of low conversion efficiency, long data delay time, easy loss of data synchronization, and difficulty in monitoring and expansion due to the need for manual conversion of SQL statements between databases caused by differences in process models and storage engines.

[0007] The application solves the above problems through the following technical solutions:

[0008] A method for synchronizing openGauss database data to a heterogeneous database, comprising:

[0009] Creating a logical replication slot and a publication and subscription mechanism in the openGauss database to capture change events;

[0010] Configuring a source database connector to continuously monitor change events of the openGauss database and send the change events to a Kafka topic;

[0011] Configuring a Kafka message middleware to serve as a change event hub bus and achieve partition routing and sequence guarantee of change events through a topic partition mechanism;

[0012] Configuring a target database connector to consume change events from the Kafka message middleware, perform database adaptation and translation, and then write to the target database;

[0013] Starting the source database connector and the target database connector to continuously monitor source database change events and deliver the change events to the target database through the Kafka message middleware.

[0014] The application is based on openGauss logical publication + Kafka as a CDC event bus and sequence hub, change is decoded into structured events, and routed to topic partitions by library table / main key. Through SQL dialect translation, the problems of long data synchronization delay time, easy loss of data synchronization, low efficiency of manual conversion, difficulty in monitoring and expansion of relational databases based on openGauss kernel in the process of synchronizing data to Mysql database are solved.

[0015] Further, the method for creating a logical replication slot and a publication and subscription mechanism in the openGauss database comprises:

[0016] A publication object is created, and the publication object is used to define which tables need to be replicated, and the publication object supports the ALL_TABLES full library table mode and the FILTERED_TABLES specified table filtering mode; preferably, the CREATE PUBLICATION command of openGauss is used to create the publication object;

[0017] A logical replication slot is created, the replication slot is implemented by a decoding plug-in to realize the structured decoding of the WAL log, and the replication slot will capture the change event based on the publication object and persistently store the replication progress object; the decoding plug-in includes pgoutput, wal2json, and a custom ogoutput / ogproto plug-in; and the captured change event is converted into a structured ChangeEvent object, including the table name, the operation type (INSERT / UPDATE / DELETE), the pre-change data, the post-change data, and the like;

[0018] The WAL calibration extension wal_calibrator is installed and enabled in the openGauss database, the WAL calibration extension wal_calibrator is used to collect and write the WAL log fragments from multiple background processes into a calibration buffer, and the calibration function is enabled for the specified replication slot, and the WAL fragments generated by different processes are reordered according to the timestamp and log sequence number LSN to generate a monotonically increasing site sequence.

[0019] The non-intrusive monitoring of the source database is realized through the logical replication mechanism, and the multi-process output is unified into a monotonically increasing site sequence by means of the WAL calibration control layer, so as to ensure the completeness, time sequence consistency, and real-time performance of the data capture, and meanwhile, flexible table filtering configuration is supported to optimize the performance and resource occupation.

[0020] Further, the source database connector is configured, and the source database connector includes:

[0021] The connector basic configuration: the source database connector name is set, the connector class is specified as the openGauss connector implementation class, the openGauss connector implementation class is responsible for establishing a logical replication connection with the openGauss database, and the change event is read from the replication slot;

[0022] The database connection parameter configuration ensures that the source database connector is connected to the source database;

[0023] The replication configuration parameter configuration: the replication slot name, the publication object name, and the decoding plug-in name are specified to be consistent with the replication slot, the publication object, and the decoding plug-in in the logical replication slot and the publication subscription mechanism, respectively, which are consistent with those in the logical replication slot and the publication subscription mechanism, so as to ensure that the source database connector captures the change event using the correct replication slot and publication object;

[0024] WAL calibration configuration: enable WAL calibration function, specify calibration channel name consistent with WAL calibration extension wal_calibrator, after enabling calibration function, source database connector preferentially reads calibration results output by wal_calibrator and writes into calibration buffer zone instead of directly reading unsorted WAL fragments from replication slot;

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

[0026] Further, the configuration of the Kafka message middleware includes:

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

[0028] Create a topic in the Kafka cluster, which is used to store change events and ensure the ordered delivery and persistent storage of data.

[0029] Further, the topic includes a topic name, a partition number and a replica factor, which ensures that a large number of concurrent change events can be processed, and the reliability of messages is guaranteed through the replica mechanism.

[0030] Use Apache Kafka as the central bus of CDC events, and realize efficient routing and order guarantee of events through the topic partition mechanism to ensure the reliability and scalability of data synchronization, which includes:

[0031] Dynamically create a Kafka topic according to the source table name and schema information, and support custom topic prefix configuration;

[0032] Partition routing based on the primary key field of the table ensures that change events of the same record arrive in order, improving data consistency;

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

[0034] Support multiple consumers for parallel processing, and automatically realize load balancing and failover within the consumer group.

[0035] Kafka as an event bus decouples data sources and target systems, improving the scalability and fault tolerance of the entire synchronization system. By using Kafka partition buffering, resume and transaction site verification, millisecond-level data synchronization delay is achieved, ensuring smooth traffic and high reliability when openGauss multi-process log bursts or MySQL single-process write limitations occur. This is reflected in:

[0036] 1)Multi-threaded architecture: Adopting a producer-consumer model, multiple worker threads handle different table synchronization tasks in parallel.

[0037] 2)Resume: Based on Kafka offset mechanism to realize the persistence of synchronization progress, support automatic recovery after system restart.

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

[0039] 4)Data monitoring: Provide real-time monitoring indicators, including synchronization delay, processing speed, error statistics, etc., support custom alarm rules.

[0040] Further, the method for configuring the target database connector is:

[0041] Specify the Kafka topic that the target database connector needs to consume;

[0042] Configure Schema mapping relationship, Schema mapping relationship is used to convert Schema name in openGauss database to table name in target database;

[0043] Batch processing and retry configuration;

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

[0045] Semantic mapping configuration, used to handle the semantic differences between openGauss column store engine and MySQL row store engine, restore openGauss column store fields, MVCC snapshots and constraint information to MySQL row store recognizable write statements;

[0046] MVCC replay strategy configuration, used to indicate the connector to apply necessary row locks before writing to MySQL;

[0047] GTID alignment configuration, set GTID alignment file path, target database connector confirms transaction order according to LSN-GTID mapping in the GTID alignment file after consuming Kafka change event, ensures that the execution order of the transaction in the target database is consistent with the source database.

[0048] Further, the method for configuring the target database translation engine is: creating a translation configuration file from the openGauss database to the target database and specifying the translation configuration file path, and the translation configuration file includes data type mapping rules, function mapping rules, keyword mapping rules and syntax mapping rules.

[0049] The present application aims at the gap between single column storage engine (openGauss) and plug-in row storage engine (MySQL) in data type, MVCC visibility and constraint semantics, designs flexible SQL conversion and semantic mapping engine, realizes data type mapping, transaction compensation and syntax conversion of openGauss to MySQL and other heterogeneous databases. Including:

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

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

[0052] 3) SQL statement translation engine:

[0053] a) DDL statement conversion: convert the CREATE TABLE statement of openGauss to the DDL of target database, handle the differences of field type, constraint, index, etc.

[0054] b) DML statement conversion: convert the INSERT / UPDATE / DELETE statement of openGauss to the DML of target database, handle syntax differences and function conversion.

[0055] c) Function mapping: establish the mapping table of openGauss function to target database function, such as the conversion of time functions such as NOW()、CURRENT_TIMESTAMP.

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

[0057] e) MVCC semantic compensation: based on the snapshot information in the openGauss CDC event, derive the row-level lock and version tag required by MySQL InnoDB, avoid data loss or dirty write caused by visibility difference.

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

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

[0060] Through the abstract SQL tool layer, the differences of heterogeneous databases are shielded, and a unified synchronization interface is provided for the upper layer application.

[0061] Further, it also includes data synchronization verification, and the method of data synchronization verification includes one or more of the following:

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

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

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

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

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

[0067] f. Test the SQL translation function, verify whether the SQL translation engine can correctly convert the function of openGauss to the corresponding function of MySQL.

[0068] An openGauss database data synchronization system to heterogeneous databases, comprising:

[0069] openGauss database, configured to capture change events by creating a logical replication slot and a publication and subscription mechanism;

[0070] Source database connector, configured to continuously monitor the change events of openGauss database, and send the change events to Kafka topic;

[0071] Kafka message middleware is configured to receive change events sent by the source database connector, and the partition routing and sequence guarantee of the change events are realized through the topic partition mechanism;

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

[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 on the memory and executable on the processor, wherein the processor executes the computer program to implement the method of openGauss database data synchronization to a heterogeneous database.

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

[0076] (1) The present 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 WAL calibration control layer and Kafka partition buffer, realizes cross-process log sequencing and rhythm matching, maintains millisecond-level end-to-end delay, and solves the problems of long data synchronization delay time and low manual conversion efficiency in traditional manual intervention method.

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

[0078] (3) The present application has strong compatibility with heterogeneous databases: supports synchronization of openGauss to MySQL, Oracle, SQL Server and other target databases, and processes column storage / row storage differences, MVCC semantics and function mapping through SQL dialect conversion and semantic mapping engine.

[0079] (4) The present application has scalability and flexibility: supports full library synchronization and filtered table synchronization modes, and can flexibly configure the synchronization range according to business needs; the multi-threaded architecture supports high concurrency processing.

[0080] (5) The present application is convenient for monitoring and operation: provides real-time monitoring indicators, detailed log records and custom alarm rules, greatly reduces the operation complexity and improves the problem positioning efficiency.

[0081] (6) The data consistency guarantee of the application: the progress management based on Kafka offset ensures the accuracy and consistency of data synchronization, avoiding data loss and repeated synchronization problems.

[0082] (7) The application has high resource utilization efficiency: the system resource utilization is optimized through batch processing and connection pool technology, reducing the performance impact on the source database and the target database. BRIEF DESCRIPTION OF DRAWINGS

[0083] Figure 1 The system architecture diagram of the embodiment of the application is shown in the figure.

[0084] Figure 2 The flowchart of the embodiment of the application is shown in the figure.

[0085] Figure 3 The logic diagram of the SQL conversion engine in the embodiment of the application is shown in the figure. DETAILED DESCRIPTION

[0086] The application will be further described in detail below in conjunction with the embodiments, but the embodiments of the application are not limited thereto.

[0087] Embodiment 1:

[0088] In conjunction with the attached Figure 2 The figure shows a method for synchronizing openGauss database data to a heterogeneous database, comprising:

[0089] Environment preparation:

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

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

[0092] 3) Message middleware: Apache Kafka 2.8.0 cluster, containing 3 Broker nodes, Zookeeper cluster 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 (logical level), which enables the database to output logical change records instead of only physical change records. At the same time, configure the maximum number of WAL senders (for example, 10), which limits the number of connections for logical replication at the same time, ensuring reasonable allocation of system resources. After configuration, reload the database configuration to take effect.

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

[0096] 3) Create 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 include specified tables (specify specific table names through the TABLE clause, such as users, orders, products, etc.). The publication object serves as the publication unit for logical replication, and subsequent replication slots will capture change events based on this publication object.

[0097] 4) Create logical replication slot: Create a logical replication slot in the openGauss database, and the replication slot uses a decoding plug-in to implement structured decoding of WAL logs. The logical replication slot is an object used to persistently store replication progress, which can record the WAL position that has been sent to the downstream, ensuring that the replication progress is not lost even if the system restarts. The name and plug-in type of the replication slot 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 and write WAL log fragments from multiple background processes into a calibration buffer through custom hook functions. Then call the enable_slot function of the extension to enable calibration for the specified replication slot. The calibration module will reorder the WAL fragments generated by different processes according to timestamp and LSN (log sequence number), generating a monotonically increasing sequence of sites, thereby solving the problem of inconsistent log order in a multi-process architecture.

[0099] Step 1 configures the logical replication function of the openGauss database, enabling it to capture data change events and send them to the downstream. The replication slot combines the wal_calibrator extension to uniformly sort and write the WAL segments generated by multiple background processes into the calibration buffer, providing a basis for subsequent LSN-GTID comparison to avoid data loss and site drift.

[0100] The present application aims at the site misalignment and delay problem caused by the process model difference of heterogeneous databases in the prior art: the multi-process WAL generation mechanism of openGauss and the single-process binlog consumption model of MySQL are incompatible in log order and transaction confirmation strategy, which is easy to cause site drift and replay blocking. The present application needs to build a "WAL calibration control layer" to uniformly sort the WAL segments output by multiple processes, generate an LSN-GTID comparison table, and realize cross-system transaction order alignment with Kafka partition buffering, thereby solving the risk of synchronization delay and event loss. The present application is based on the logical publication and special log alignment algorithm of openGauss, realizing real-time synchronization across architectures.

[0101] Step 2: Configure Kafka message middleware

[0102] To configure the Kafka cluster, the following operations need to be completed:

[0103] 1) Start the Zookeeper service: In the configuration environment of the Kafka cluster, first start the Zookeeper service, which is the coordination service of the Kafka cluster, responsible for managing the metadata, configuration information and leader election of the Kafka cluster. When starting, the configuration file path of Zookeeper needs to be specified, and the configuration file contains parameters such as 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. Kafka Broker is the message proxy node of Kafka cluster, responsible for message storage, forwarding and consumption. When starting, the configuration file path of Broker needs to be specified, and the configuration file contains key parameters such as Broker ID, listening port, log storage path and Zookeeper connection address.

[0105] 3) Create a synchronization topic: Create a topic in the Kafka cluster for storing openGauss change events. The topic creation needs to specify the topic name, the number of partitions (e.g. 3 partitions), and the replica factor (e.g. 1 replica). The number of partitions determines the parallel processing capability of messages, and the replica factor determines the redundancy level of messages. After the topic is created, the topic will be used to store the structured conversion of the change event captured from openGauss (CDC refers to the structured addition, deletion, and modification record captured by the Change Data Capture mechanism, usually including table name, primary key, operation type, field value before and after change, and bit position information. The invention takes CDC event as the smallest data unit for cross-system transmission).

[0106] Step 2 configures the Kafka message middleware to provide high-throughput, fault-tolerant message delivery mechanism for data synchronization. Kafka topic is used to store openGauss change events to ensure ordered delivery and persistent storage of data. Through the distributed architecture and partition mechanism of Kafka, a large number of concurrent change events can be efficiently processed, and the reliability of messages is guaranteed through the replica mechanism.

[0107] The invention provides an "automatic monitoring and fault tolerance mechanism" for continuous detection of bit positions, delays and abnormalities, realizing a real-time synchronization closed loop solution to the operation risk caused by manual dump.

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

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

[0110] 1) Connector basic configuration: Set the connector name to opengauss-connector, and specify the connector class as the openGauss connector implementation class of Debezium. This connector class is responsible for establishing a logical replication connection with the openGauss database and reading change events from the replication slot.

[0111] 2) Database connection parameters: Set the host address of the openGauss database to 192.168.1.100, the port to 5432, and the 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 (consistent with the replication slot name created in step 1), the publication name as opengauss_publication (consistent with 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 for capturing change events.

[0113] 4) WAL calibration configuration: enable the WAL calibration function and specify the calibration channel name as wal_calibrator. After enabling the calibration function, the connector will preferentially read the calibration results output by the wal_calibrator extension, rather than directly reading the unsorted WAL segments from the replication slot. This calibration mechanism can solve the problem of inconsistent log order in a multi-process architecture.

[0114] 5) Site mapping configuration: set the LSN-to-GTID mapping cache file path as / 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 accurate recovery of the replication site after restart and guaranteeing the consistency of transaction order.

[0115] 6) Other configuration parameters: set the marker for delete operations to false, indicating that no messages will be sent to Kafka when deleting data. Set the heartbeat interval to 5000 milliseconds, which is used to periodically send heartbeat messages to the database to detect connection status and replication delay.

[0116] Step 3 deploys the Debezium openGauss connector, which captures change events from the openGauss database and sends them to Kafka. By enabling the wal.calibration parameter, the connector will preferentially read the calibration results output by wal_calibrator, generating messages containing source LSN, transaction number, and CDC event payload. The lsn.gtid.mapping.cache.path is used to persistently store the LSN-GTID mapping, ensuring traceability of the site and thus ensuring real-time and consistency. After the connector starts, it will continuously monitor the changes in the openGauss database and convert the change events into structured CDC events and send them to the Kafka topic.

[0117] Step 4: Configure the target database connector, including configuring the message end connector and 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 openGauss Sink connector implementation class of Debezium. This connector is responsible for consuming CDC events from Kafka and writing to the target database. Set the target database connection URL to jdbc:mysql: / / 192.168.1.200:3306 / target_db, the database driver to 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, which contain the captured change events from openGauss. Configure the Schema mapping relationship as public.users mapped to users and public.orders mapped to orders, which is used to convert the Schema name in openGauss to the table name in MySQL.

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

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

[0122] / opt / debezium / config / opengauss-mysql-translation.json. The SQL translation engine is responsible for converting openGauss SQL statements to MySQL SQL statements, handling data types, functions, keywords, and syntax differences.

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

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

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

[0126] Keyword mapping: openGauss reserved words that may be used as table names or column names (such as user, order, group, index) are mapped to MySQL backtick enclosed form (such as `user`, `order`, `group`, `index`) to avoid conflicts with MySQL reserved keywords.

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

[0128] As Figure 3 shown, the SQL translation engine includes a message parser, SQL building, SQL conversion, and a MYSQL adapter, realizing data type mapping, transaction compensation, and syntax conversion of OpenGauss to MySQL and other heterogeneous databases, wherein: the message parser is used to identify event types, extract fields, and parse metadata from Kafka messages; the SQL building converts OpenGauss INSERT / UPDATE / DELETE statements into DML of the target database through DML statement conversion, processes syntax differences and function conversion; the SQL conversion realizes data type mapping, function conversion, keyword processing, and syntax conversion; and the MYSQL adapter connects the target database through a jdbc driver, performs connector management and data writing.

[0129] The SQL translation engine can automatically convert SQL statements of openGauss into SQL statements of the target database, including data type mapping, function conversion, keyword processing, and syntax conversion. Batch processing, retry mechanisms, and other parameters are configured to ensure the reliability and performance of data synchronization. MySQL, Oracle, SQL Server, and other target databases are supported.

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

[0131] 5) Semantic mapping configuration: enable semantic mapping function and specify the semantic mapping configuration file as columnar-to-rowstore. This configuration is used to handle the semantic differences between the openGauss columnar storage engine and the MySQL row storage engine, and restore the columnar field, MVCC snapshot, and constraint information of openGauss to MySQL row storage recognizable write statements.

[0132] 6) MVCC replay policy configuration: set the MVCC replay policy to lock_safe (lock-safe mode), which instructs the connector to apply necessary row locks before writing to MySQL to avoid concurrent dirty writing and ensure the consistency of the semantics of the two storage engines.

[0133] 7) GTID alignment configuration: set the GTID alignment file path to:

[0134] / opt / debezium / state / lsn-gtid-map.json, which contains the LSN-to-GTID mapping relationship. After consuming Kafka CDC events, the connector confirms the transaction order according to the LSN-GTID mapping in the file to ensure that the execution order of transactions in the target database is consistent with that of the source database.

[0135] The present application aims at the semantic loss problem in the prior art caused by the difference in storage engine mechanism of heterogeneous databases: openGauss adopts a single fusion storage engine, and there are columnar storage completion, MVCC visibility and other semantics; MySQL adopts a plug-in row storage engine, and requires accurate row-level change replay. The traditional dump method cannot restore these semantics, resulting in missing fields or inconsistent constraints. The present application designs a SQL translation engine to realize dialect conversion and semantic mapping, normalizes the types of CDC events, rewrites DDL / DML, and compensates constraints to ensure consistency of row-level data in the target database.

[0136] The target database connection and SQL translation engine are configured in step 4, realizing the function of consuming change events from Kafka and writing to multiple heterogeneous databases. The target connector confirms the transaction order according to the LSN-GTID mapping in gtid.alignment.file after consuming Kafka CDC events. According to the semantic mapping configuration file columnar-to-rowstore, openGauss columnar storage fields, MVCC snapshots and constraint information are restored to MySQL row storage recognizable write statements. mvcc.replay.strategy=lock_safe indicates that the connector applies necessary row locks before writing to avoid concurrent dirty writing and ensure consistency of the semantics of the two storage engines.

[0137] Step 5: Start data synchronization

[0138] Starting data synchronization requires the following operations:

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

[0140] 2) Start the MySQL target database connector: send an interface request to the Kafka Connect service, and the request body is 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 start consuming CDC events from the Kafka topic and writing to the MySQL target database.

[0141] 3) Check connector status: Query the running status of the two connectors. The status query result includes the status of the connector (RUNNING, FAILED, PAUSED, etc.), the number of tasks and the task status, etc. information, used to verify whether the connector is started and running normally.

[0142] Step 5 starts the complete data synchronization process, realizing real-time data synchronization from openGauss to multiple heterogeneous databases. The system starts to continuously monitor the source database changes, and transmits the change events to the target database through the Kafka message bus, ensuring data consistency and real-time performance. It supports one-to-many synchronization mode, and one openGauss source can be synchronized to multiple target databases at the same time. After the connector is started, the change events of the openGauss database are captured in real time and converted into CDC events and sent to Kafka, and then consumed by the target connector and written to the target database, realizing real-time synchronization of data.

[0143] The application decodes the WAL log in openGauss logically, subscribes to incremental changes (INSERT / UPDATE / DELETE) by table / column, serializes and transmits to middleware through plug-ins / connectors, completes type and structure mapping, transaction order and site maintenance, converts changes into executable DML and DDL of MySQL, ensures idempotency and at least once delivery, and realizes low-latency real-time synchronization of openGauss database to Mysql. The advantages are:

[0144] Kafka queue consumption: in openGauss logical publishing + Kafka as CDC event bus and sequential execution hub, changes are decoded into structured events, routed to topic partitions by library table / primary key, carrying LSN, TxId, commit_ts, schema version, and realizing millisecond-level data transmission.

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

[0146] Real-time monitoring: Based on LSN+transaction boundary, do out-of-order rearrangement and batch commit, offset+LSN dual checkpoint to realize breakpoint continuation and "at least once / accurate once", snapshot+incremental overlap check to guarantee consistency. Support primary key hash fixed routing to ensure full order across partitions, back pressure and elastic parallelism, DLQ / playback, loop and backfill protection, DDL transparent transmission and shadow publishing / gray switching, hotspot avoidance, multi-tenant isolation and audit serial number, delay / throughput / point observability and self-healing capabilities.

[0147] Further, it can also include:

[0148] Step 6: Verify synchronization effect

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

[0150] 1) Check the source connector status: Through the Kafka Connect REST API, query the running status of the openGauss source connector. The status query result includes the status of the connector (RUNNING indicates normal operation, FAILED indicates failure, PAUSED indicates pause), task quantity and task status and other information. Through this operation, it can be confirmed whether the source connector is started normally and is capturing change events.

[0151] 2) Check the target connector status: Through the Kafka Connect REST API, query the running status of each target connector. The status query result includes the status of the connector, task quantity and task status and other information. Through this operation, it can be confirmed whether the target connector is started normally and is consuming Kafka messages and writing to the target database.

[0152] 3) Check Kafka topic data: Use the Kafka command line consumer tool, connect to the Kafka Broker (bootstrap-server is localhost:9092), consume the messages of the specified topic (opengauss.public.users), and set the consumption mode to start from the beginning of the topic (from-beginning). This operation is used to verify whether the change events of openGauss 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 (host address: 192.168.1.200, username: root, database name: target_db) using the MySQL client, execute the SQL query statement, and count the total number of records in the target table users. Then execute the query statement to get the top 5 records in the target table users sorted by ID in descending order. This operation is used to verify whether the data is 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 (host address: 192.168.1.100, username: postgres, database name: postgres) using the PostgreSQL client, execute the INSERT statement to insert a test data into the users table, and the test data's name field is test_user and the email field is test@example.com. This operation is used to verify whether the data changes of 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 the MySQL client, execute the SQL query statement to query the record in the target table users with the name field test_user. Through this operation, it can be verified whether the test data inserted in step 5 has been successfully synchronized to the target database. If the query result contains the record, it means that the data change synchronization function is normal.

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

[0157] 8) Verify the SQL translation result: After waiting for 3 seconds, connect to the target database using the MySQL client, execute the SQL query statement to query the record in the target table users with the name field sql_test. Through this operation, it can be verified whether the created_at field of the test data inserted in step 7 has been correctly converted to the time format of MySQL. If the value of the created_at field is correct, it means that the time function conversion function of the SQL translation engine is normal.

[0158] Step 6 verifies the normal operation of the data synchronization system by checking the connector status, Kafka topic data, target database data, and actual data change synchronization test. In particular, the function of the SQL translation engine is verified to ensure that the openGauss SQL statement can be correctly converted into the SQL statement of the target database, including time function, data type, keyword conversion, etc. The verification results prove the effectiveness and reliability of the heterogeneous data real-time synchronization system based on openGauss, ensuring that data can be accurately and real-time synchronized from the source database to multiple heterogeneous target databases, while maintaining the compatibility of SQL statements. Through the WAL calibration control layer, the problem of non-uniform log order under the multi-process architecture is solved; through the SQL dialect conversion and semantic mapping layer, the semantic missing problem caused by the difference of storage engines is solved, realizing real-time data synchronization across architectures.

[0159] The present application successfully realizes real-time data synchronization from the openGauss source database to MySQL and other heterogeneous target databases through the implementation of the above six steps, with a synchronization delay controlled within milliseconds. The SQL translation engine can automatically handle the syntax differences between different databases to ensure accurate data conversion. The Kafka message bus provides a high-throughput, fault-tolerant message delivery mechanism, supporting one-to-many synchronization mode. The system has perfect monitoring and alarm functions, realizing high-availability and high-performance data synchronization services, providing strong technical support for enterprise data integration, business system migration, and other scenarios.

[0160] Embodiment 2:

[0161] In combination with the accompanying Figure 1 Fig. 1 shows a system for synchronizing openGauss database data to heterogeneous databases, including:

[0162] The openGauss database is configured to capture change events by creating a logical replication slot and a publication and subscription mechanism;

[0163] The source database connector (connector cluster) is configured to continuously monitor the change events of the openGauss database and send the change events to the Kafka topic; realizing change event capture, data serialization and breakpoint resume;

[0164] The Kafka message middleware (Kafka x message bus cluster) is configured to receive the change events sent by the source database connector, realize partition routing and order guarantee of the change events through the topic partition mechanism; realizing topic partition management, message persistence and replica synchronization;

[0165] The target database connector (Mysql database adaptation and SQLz conversion (translation) engine cluster) is configured to consume change events from the Kafka message middleware, perform database adaptation, SQL conversion engine translation, and send to the target database; SQL translation, data type mapping, batch processing and transaction control are realized.

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

[0167] In the present application:

[0168] 1. The queue consumption component subscribes to the logical replication of the source end openGauss in real time, and the source end events are sequentially subscribed to the Kafka queue component in real time with millisecond-level delay, realizing the technical effect of low-delay data synchronization.

[0169] 2. The Sql dialect conversion component in the target database connector reads the data in the Kafka queue in real time, reads the corresponding sql statement, and translates the source end statement into a statement that can be executed by the Mysql database through the dialect converter. The technical effects of Sql dialect translation and rewriting are realized.

[0170] By performing logical decoding on the WAL log in openGauss, subscribing to incremental changes (INSERT / UPDATE / DELETE) by table / column, and serializing and transmitting the changes to the Kafka message middleware through the plug-in / connector, type and structure mapping, transaction order and site maintenance are completed, the changes are converted into MySQL executable DML and DDL, idempotency and at least once delivery are guaranteed, and openGauss database low-delay real-time synchronization to Mysql is realized.

[0171] Embodiment 3:

[0172] An electronic device includes a memory, a processor, and a computer program stored on the memory and executable on the processor, and the processor executes the computer program to realize the method of openGauss database data synchronization to a heterogeneous database in embodiment 1.

[0173] Although the present application has been described herein with reference to the explanatory embodiments thereof, the above-described embodiments are merely preferred embodiments of the present application, and the embodiments of the present application are not limited to the above-described embodiments, and it should be understood that those skilled in the art can design many other modifications and embodiments, which will fall within the scope and spirit of the principles disclosed in the present 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 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.

2. The method for synchronizing openGauss database data to a heterogeneous database according to claim 1, characterized in that, 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, and 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.

3. The method for synchronizing openGauss database data to a heterogeneous database according to claim 2, characterized in that, Configuring the source database connector 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; 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.

4. The method for synchronizing openGauss database data 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.

5. The method for synchronizing openGauss database data to a heterogeneous database according to claim 4, 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.

6. 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 the necessary 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.

7. The method for synchronizing openGauss database data to a heterogeneous database according to claim 6, 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.

8. 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.

9. 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.

10. 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 8.

Images