Cross-library data real-time synchronization method based on CDC technology

Abstract

The invention relates to the technical field of multi-source data synchronization, in particular to a cross-library data real-time synchronization method based on a CDC technology. Data change is synchronized to a target system in real time through code-free graphical configuration, so that synchronization from a source database to a data warehouse, a data analysis platform or other business systems is realized, secondary code development is not needed, business personnel with low technical threshold and non-technical background can realize cross-database real-time synchronization of data through interface configuration, and the data synchronization efficiency is improved. Communication and cooperation among different departments are enhanced, and the working efficiency is improved.

Description

Technical Field

[0001] The present invention relates to the technical field of multi-source data synchronization, and in particular to a cross-database real-time synchronization method based on CDC technology. Background Art

[0002] There are currently a variety of CDC data synchronization components on the market, including Flink, Debezium, DataX, Otter, Canal, and Sqoop. In terms of incremental quasi-real-time data synchronization processing, Flink, Debezium, Otter, and Canal projects are widely used, because DataX and Sqoop are mainly used for offline data synchronization and do not support log mode, so they cannot be used for real-time synchronization; Sqoop is mainly aimed at the big data technology ecosystem; Otter and Canal open source versions do not support PostGreSQL for the time being; and Flink CDC and Debezium both have preset connectors and plug-ins, and also support common database types, including MySQL, PostgreSQL, MongoDB, etc., which use database logs or transaction logs (such as MySQL's binlog, PostgreSQL's WAL) to track data, but they cannot be customized and developed, which is not conducive to product expansion and requires secondary code development. In summary, the existing open source products on the market have limitations. Summary of the invention

[0003] The present invention aims at the problem that the existing CDC data synchronization method needs code secondary development and has limitations, and proposes a cross-database real-time data synchronization method based on CDC technology; through code-free graphical configuration, data changes are synchronized to the target system in real time, thereby synchronizing from the source database to the data warehouse, data analysis platform or other business systems, without the need for secondary development of code, with a low technical threshold, and business personnel with non-technical backgrounds can realize cross-database real-time synchronization of data through interface configuration, thereby enhancing communication and collaboration between departments and improving work efficiency.

[0004] The specific implementation contents of the present invention are as follows: A cross-database real-time data synchronization method based on CDC technology specifically includes the following steps: Step S1: Build a CDC environment according to the selected data synchronization component, and build and start a CDC connector according to the data source configuration and CDC task configuration information; Step S2: Monitor multi-source database logs according to the constructed CDC connector to obtain database change events; Step S3: Convert the database change event into Json format; Step S4: Initialize the thread pool, subscribe to the Kafka topic according to the CDC task configuration information, generate SQL statements in batches, and call the commitSync() method to obtain the offset of the Kafka consumer; Step S5: Capture abnormal data according to the offset of the Kafka consumer and synchronize it to the target system according to the set retry mechanism.

[0005] In order to better implement the present invention, further, the step S1 specifically includes the following steps: Step S11: Build a CDC environment based on the Debezium data synchronization component, the Kafka data synchronization component, the Debezium Connector data synchronization component, and the Flink CDC data synchronization component; Step S12: Build and start a CDC connector and instantiate the CDC connector according to the data source configuration, CDC task configuration information, and data synchronization components.

[0006] In order to better implement the present invention, further, the step S11 specifically includes the following steps: Step S111: Install the Zookeeper cluster according to the Debezium component, manage the configuration of the Kafka cluster, elect the leader, and handle the rebalance of the consumer group; Step S112: Install Debezium Connector and deploy it in KafkaConnect as a source connector in the form of a JAR package to capture change events of a specific database and send them to the Kafka cluster; Step S113: Install Apache Flink according to the Flink CDC component and add the Flink CDC connector dependency.

[0007] In order to better implement the present invention, further, the step S12 specifically includes the following steps: Step S121: Send a POST request to Kafka Connect according to the Debezium component to instantiate and start the Debezium Connector; Step S122: According to the Flink CDC component, in the Flink SQL environment, dynamically configure the MySQL CDC Source through the programming API, and instantiate and start the Flink CDC task.

[0008] In order to better implement the present invention, further, the step S2 specifically includes the following steps: Step S21: Capture data changes by subscribing to the binary log binlog of the MySQL database according to the CDC connector; Step S22: Capture data changes according to the logical replication of the PostgreSQL database, and obtain database change events according to the application program's subscription to the database WAL log; Step S23: Parse the online and archived redo log files Redo Logs of the Oracle database according to the CDC connector.

[0009] In order to better implement the present invention, further, the step S4 specifically includes the following steps: Step S41: Call ThreadPoolTaskExecutor to initialize the thread pool according to the configured thread pool parameters; Step S42: according to the CDC task configuration synchronization table information, create a Kafka consumer and submit it to the initialized thread pool; Step S43: Call the commitSync() method to commit the offset of the Kafka consumer.

[0010] In order to better implement the present invention, further, the step S42 specifically includes the following steps: Step S421: obtaining table information to be synchronized according to the CDC task configuration, and creating a task instance according to the table information to be synchronized; the task instance encapsulates the logic of creating a Kafka consumer, subscribing to a specific topic, and processing received messages; Step S422: Submit the created task instance to the initialized thread pool, and dynamically allocate execution resources to the task instance according to the number of core threads, the maximum number of threads, and the task queue capacity of the thread pool, and execute the task instance in parallel.

[0011] In order to better implement the present invention, further, the step S43 specifically includes the following steps: Step S431: Create an independent KafkaConsumer instance in each thread synchronization table instance and set configuration parameters; Step S432: Call the poll() method in the KafkaConsumer instance to obtain ConsumerRecords, and parse it according to Jackson to obtain JSON data; Step S433: preprocessing the JSON data according to the set preprocessing rules; Step S434: Determine the target database table and field mapping according to the table correspondence in the CDC task configuration, call the FreeMarker template engine to batch generate SQL statements according to the operation type and the preprocessed JSON data; Step S435: Send the batch-generated SQL statements in batches to the SQL execution client, and call the commitSync() method to commit the offset of the Kafka consumer.

[0012] In order to better implement the present invention, further, the specific operation of step S435 is: send the batch-generated SQL statements in batches to the SQL execution client, execute all SQL statements in sequence in the same transaction, capture and handle exceptions during the execution process, and commit the transaction if all SQL statements are successfully executed, otherwise roll back the SQL transaction; after successfully processing a batch of messages and committing the database transaction, call the commitSync() method to commit the offset of the Kafka consumer.

[0013] In order to better implement the present invention, further, the specific operation of step S5 is: Step S51: According to the offset of the Kafka consumer, the abnormal data is captured and written into Elasticsearch in the form of logs; Step S52: Determine the exception type of the exception data. If the exception data is a retryable exception, call a loop statement to try multiple times, implement a monitoring and alarm mechanism, and notify the operation and maintenance personnel in time when the number of retries exceeds a certain threshold or a specific type of error occurs frequently; if multiple retries still cannot be successfully processed, forward it to the dead letter queue.

[0014] The present invention has the following beneficial effects: (1) The present invention does not require secondary development of code, and can achieve the cross-database custom synchronization configuration function through interface configuration. This can lower the technical threshold, and business personnel with non-technical backgrounds can also directly participate in system configuration, enhancing communication and collaboration between departments and improving efficiency.

[0015] (2) The present invention has high integration and can integrate different data synchronization components such as Debezium and Flink CDC. It can be seamlessly integrated with different systems and applications and supports output to a variety of database systems, including relational databases and NoSQL databases, as well as message queues, ensuring compatibility with various environments. This enables real-time migration and synchronization of data. BRIEF DESCRIPTION OF THE DRAWINGS

[0016] Figure 1 This is a schematic block diagram of the process of the cross-database real-time synchronization method based on CDC technology provided by the present invention.

[0017] Figure 2 The instantiation flow chart of the CDC task provided by the present invention.

[0018] Figure 3 This is a schematic diagram of the CDC task configuration format provided by the present invention.

[0019] Figure 4 This is a schematic diagram of the data format provided by the present invention.

[0020] Figure 5 This is a schematic diagram of the process of event consumption into a target library provided by the present invention.

[0021] Figure 6 A schematic diagram of the Kafka subscription topic provided by the present invention.

[0022] Figure 7 This is a message processing and warehousing flow chart provided by the present invention.

[0023] Figure 8 This is a schematic diagram of the error handling and retry mechanism provided by the present invention.

[0024] Fig. 9 A schematic diagram of the process flow of the real-time cross-database data method based on CDC technology provided by the present invention.

[0025] Fig.10 A schematic diagram of a cross-database real-time data collection device based on CDC technology provided in an embodiment of the present invention.

[0026] Fig.11 A schematic diagram of the relationship between modules of a cross-database real-time data collection device based on CDC technology provided in an embodiment of the present invention. DETAILED DESCRIPTION

[0027] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. It should be understood that the described embodiments are only part of the embodiments of the present invention, not all of the embodiments, and therefore should not be regarded as limiting the scope of protection. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technical personnel in this field without making creative work are within the scope of protection of the present invention.

[0028] In the description of the present invention, it should be noted that, unless otherwise clearly specified and limited, the terms "disposed", "connected", and "connected" should be understood in a broad sense, for example, it can be a fixed connection, a detachable connection, or an integral connection; it can be a mechanical connection or an electrical connection; it can be a direct connection, or it can be an indirect connection through an intermediate medium, or it can be the internal communication of two elements. For ordinary technicians in this field, the specific meanings of the above terms in the present invention can be understood according to specific circumstances.

[0029] Embodiment 1: This embodiment proposes a cross-database real-time data synchronization method based on CDC technology, which specifically includes the following steps: Step S1: Build a CDC environment based on the selected data synchronization component, and build and start a CDC connector based on the data source configuration and CDC task configuration information.

[0030] The step S1 specifically includes the following steps: Step S11: Build a CDC environment based on the Debezium data synchronization component, the Kafka data synchronization component, the Debezium Connector data synchronization component, and the Flink CDC data synchronization component; Furthermore, the step S11 specifically includes the following steps: Step S111: Install the Zookeeper cluster according to the Debezium component, manage the configuration of the Kafka cluster, elect the leader, and handle the rebalance of the consumer group; Step S112: Install Debezium Connector and deploy it in KafkaConnect as a source connector in the form of a JAR package to capture change events of a specific database and send them to the Kafka cluster; Step S113: Install Apache Flink according to the Flink CDC component and add the Flink CDC connector dependency.

[0031] Step S12: Build and start a CDC connector and instantiate the CDC connector according to the data source configuration, CDC task configuration information, and data synchronization components.

[0032] Furthermore, the step S12 specifically includes the following steps: Step S121: Send a POST request to Kafka Connect according to the Debezium component to instantiate and start the Debezium Connector; Step S122: According to the Flink CDC component, in the Flink SQL environment, dynamically configure the MySQL CDC Source through the programming API, and instantiate and start the Flink CDC task.

[0033] Step S2: Monitor multi-source database logs according to the constructed CDC connector to obtain database change events.

[0034] Furthermore, the step S2 specifically includes the following steps: Step S21: Capture data changes by subscribing to the binary log binlog of the MySQL database according to the CDC connector; Step S22: Capture data changes according to the logical replication of the PostgreSQL database, and obtain database change events according to the application program's subscription to the database WAL log; Step S23: Parse the online and archived redo log files Redo Logs of the Oracle database according to the CDC connector.

[0035] Step S3: Convert the database change event into Json format.

[0036] Step S4: Initialize the thread pool, subscribe to the Kafka topic according to the CDC task configuration information, generate SQL statements in batches, and call the commitSync() method to obtain the offset of the Kafka consumer.

[0037] Furthermore, the step S4 specifically includes the following steps: Step S41: Call ThreadPoolTaskExecutor to initialize the thread pool according to the configured thread pool parameters; Step S42: according to the CDC task configuration synchronization table information, create a Kafka consumer and submit it to the initialized thread pool; Furthermore, the step S42 specifically includes the following steps: Step S421: obtaining table information to be synchronized according to the CDC task configuration, and creating a task instance according to the table information to be synchronized; the task instance encapsulates the logic of creating a Kafka consumer, subscribing to a specific topic, and processing received messages; Step S422: Submit the created task instance to the initialized thread pool, and dynamically allocate execution resources to the task instance according to the number of core threads, the maximum number of threads, and the task queue capacity of the thread pool, and execute the task instance in parallel.

[0038] Step S43: Call the commitSync() method to commit the offset of the Kafka consumer.

[0039] The step S43 specifically includes the following steps: Step S431: Create an independent KafkaConsumer instance in each thread synchronization table instance and set configuration parameters; Step S432: Call the poll() method in the KafkaConsumer instance to obtain ConsumerRecords, and parse it according to Jackson to obtain JSON data; Step S433: preprocessing the JSON data according to the set preprocessing rules; Step S434: Determine the target database table and field mapping according to the table correspondence in the CDC task configuration, call the FreeMarker template engine to batch generate SQL statements according to the operation type and the preprocessed JSON data; Step S435: Send the batch-generated SQL statements in batches to the SQL execution client, and call the commitSync() method to commit the offset of the Kafka consumer.

[0040] The specific operation of step S435 is: sending the batch-generated SQL statements in batches to the SQL execution client, executing all SQL statements in sequence in the same transaction, capturing and handling exceptions during the execution process, and committing the transaction if all SQL statements are successfully executed, otherwise rolling back the SQL transaction; after successfully processing a batch of messages and committing the database transaction, calling the commitSync() method to commit the offset of the Kafka consumer.

[0041] Step S5: Capture abnormal data according to the offset of the Kafka consumer and synchronize it to the target system according to the set retry mechanism.

[0042] The specific operations of step S5 are: Step S51: According to the offset of the Kafka consumer, the abnormal data is captured and written into Elasticsearch in the form of logs; Step S52: Determine the exception type of the exception data. If the exception data is a retryable exception, call a loop statement to try multiple times, implement a monitoring and alarm mechanism, and notify the operation and maintenance personnel in time when the number of retries exceeds a certain threshold or a specific type of error occurs frequently; if multiple retries still cannot be successfully processed, forward it to the dead letter queue.

[0043] Working principle: This embodiment uses codeless graphical configuration to synchronize data changes to the target system in real time, thereby synchronizing from the source database to the data warehouse, data analysis platform or other business systems. No secondary development code is required, and the technical threshold is low. Business personnel with non-technical backgrounds can achieve real-time synchronization of data across databases through interface configuration, which enhances communication and collaboration across departments and improves work efficiency.

[0044] Embodiment 2: This embodiment is based on the above embodiment 1. Figure 1 , Figure 2 , Figure 3 , Figure 4 , Figure 5 , Figure 6 , Figure 7 , Figure 8 As shown, a specific embodiment is described in detail.

[0045] Step S1: The establishment of the CDC environment does depend on the selected data synchronization components. Different components have different installation and configuration requirements.

[0046] The Debezium component installs the Zookeeper cluster to manage the configuration of the Kafka cluster, elect leaders, and handle the rebalance of consumer groups; installs the Kafka cluster to store database change events captured by the Debezium component; installs the Debezium Connector component, which is deployed in the Kafka Connect component as a JAR package, and is used as a source connector sourceconnector component to capture change events of specific databases (such as MySQL, PostgreSQL, Oracle, etc.) and send these events to the Kafka cluster, such as debezium-connector-mysql, debezium-connector-oracle and other JAR packages. The Flink CDC component requires the installation of the open source distributed stream processing framework Apache Flink system and the addition of the Flink CDC connector dependency.

[0047] Building and starting a CDC connector means instantiating and starting the CDC connector based on the data source configuration (data source type, version, host name, port, database name, user name, and password), CDC task configuration information (source end information, Kafka topic information, synchronization table information, synchronization mode), and then based on the selected data synchronization component.

[0048] The Debezium component instantiates and starts the Debezium connector by sending a POST request to the Kafka connector Kafka Connect. The Flink CDC component needs to dynamically configure the change data capture source CDC Source component through the programming API in the Flink SQL environment to instantiate and start the Flink CDC task. The CDC task configuration format is as follows: Figure 3 shown.

[0049] Step S2: Source database log monitoring. Debezium components and Flink CDC synchronization components can support various database systems, such as MySQL, PostgreSQL, Oracle, etc., and capture data addition, deletion, modification and query operations in real time by monitoring their respective log mechanisms. The CDC connector captures data changes by subscribing to the binary log (binlog) of the MySQL database; for the PostgreSQL database, the CDC connector uses its logical replication function to capture data changes, allowing applications to subscribe to the database WAL log to obtain database change events; for the Oracle database, the CDC connector parses the database's online and archived redo log files Redo Logs.

[0050] Step S3: Data is serialized and sent.

[0051] The CDC connector monitors database change events and serializes the data into JSON format for easy transmission and storage. Before the message is written to the Kafka topic, its value will be converted into a unified JSON format string, which contains the old data (before) and the new data row (after) as well as the original data information (source). The u in op indicates the update operation identifier (the values ​​of the op field c, u, d, and r correspond to create, update, delete, and read respectively), and ts_ms indicates the synchronization timestamp. The data format is as follows: Figure 4 shown.

[0052] Step S4: Data consumption into the target database. Downstream systems such as data warehouses, data analysis tools or other databases can receive these events by subscribing to Kafka topics, thereby achieving real-time data synchronization and processing. Through self-developed distributed synchronization tasks, data is consumed from the corresponding Kafka topic and written to the corresponding table field of the target database. The steps of data consumption into the target database include: thread pool initialization, Kafka topic, message processing and storage. The flowchart is as follows: Figure 5 shown.

[0053] Step S41: thread pool initialization.

[0054] This embodiment uses ThreadPoolTaskExecutor to initialize the thread pool. By configuring parameters such as the number of core threads, the maximum number of threads, and the thread idle time of the thread pool, the threads in the thread pool are not destroyed after completing a task, but return to the thread pool to wait for the next task, which helps to improve the throughput and response speed of message processing, especially in high-load situations. A Kafka consumer client is started in each thread.

[0055] In this way, the ThreadPoolTaskExecutor thread pool effectively manages the consumption and processing of Kafka messages, improving the performance and stability of the system.

[0056] Step S42: Kafka subscribes to the topic. The schematic diagram of Kafka subscribing to the topic is as follows Figure 6 shown.

[0057] In the event consumption module, according to the CDC task configuration synchronization table information, the module will create one or more task instances for each table that needs to be synchronized. Each task instance encapsulates the logic of creating a Kafka consumer, subscribing to a specific topic, and processing received messages. The key point here is that instances of the same table will be assigned the same group_id, which means that they will share the same set of consumer offsets, thereby ensuring consistency in data processing.

[0058] The created task instance is then submitted to the pre-configured ThreadPoolTaskExecutor thread pool, which dynamically allocates execution resources to each task based on its configuration parameters such as the number of core threads, the maximum number of threads, and the task queue capacity. This mechanism ensures that the system can run smoothly even in high-concurrency scenarios and avoids performance bottlenecks caused by resource contention.

[0059] Since each table synchronization instance is executed through an independent thread, they are isolated from each other. This isolation means that the failure of a task, such as the disconnection of the Kafka consumer due to network problems or the exception thrown by the message processing logic, will not affect the execution of other tasks. In this way, even if a local failure occurs, the system as a whole can still remain stable, greatly improving the robustness and fault tolerance of the system.

[0060] Step S43: message processing and storage. The message processing and storage flow chart is as follows: Figure 7 shown.

[0061] Create an independent KafkaConsumer instance in each thread synchronization table instance and set appropriate configurations, such as group.id (make sure different threads use different group IDs unless they belong to the same consumer group and want to share offsets), bootstrap.servers, fetch.min.bytes (control the minimum amount of data pulled to reduce the number of network requests), and fetch.max.bytes (control the maximum amount of data pulled at a time to avoid memory overflow).

[0062] Using a loop in each KafkaConsumer instance to periodically call the poll() method will return a batch of consumer records ConsumerRecords that may contain multiple change events. For each message in ConsumerRecords, use JSONJackson to parse its content and extract the table name, primary key, operation type (INSERT, UPDATE, DELETE), operation time, column name, and column value.

[0063] Then, the JSON data is preprocessed according to the data preprocessing rules of the table in the CDC task configuration. The preprocessing rules of the present invention include integrity verification, abnormal data filtering, missing field filling, and data structure conversion. The integrity verification rules can check whether the JSON data meets the preset structure and format requirements, such as whether all required fields exist, whether the data type is correct, etc. If the data is found to be incomplete or the format is wrong, these invalid data records are ignored to avoid subsequent processing errors; abnormal data filtering rules refer to situations where data values ​​are abnormal, such as non-numeric characters appearing in numeric fields, date field format errors, etc., these data records should be filtered out; missing field filling rules refer to missing fields that can be filled with default values ​​according to the rules defined in the configuration table; data structure conversion refers to defining a series of data models (such as dictionary values, date formatting, SQL code) to map fields in JSON data.

[0064] Finally, according to the table correspondence in the CDC task configuration, the target database table and field mapping are determined, and the FreeMarker template engine is used to generate SQL statements in batches according to the operation type and the parsed data. The generated SQL statements are sent in batches to the SQL execution client. In the same transaction, all SQL statements are executed in sequence. During the execution process, any exceptions such as SQL syntax errors and database connection problems are captured and handled. If all SQL statements are successfully executed, the transaction is committed, otherwise the SQL transaction is rolled back.

[0065] After successfully processing a batch of messages and committing the database transaction, use the commitSync() method to commit the Kafka consumer's offset. This ensures that even if the application crashes or restarts, it can continue consuming from the last successfully processed position.

[0066] Step S5: Error handling and retry mechanism. Figure 8 shown.

[0067] In this module, this embodiment captures and processes various exceptions that may occur during the consumption process, such as message parsing failure, network error, or database operation exception, and performs corresponding processing according to the exception type.

[0068] First, the module has a built-in logging function that writes log data to the Elasticsearch log server. When any exception occurs, it can record the exception information in detail, including the exception type, occurrence time, error stack, etc., to facilitate subsequent troubleshooting and problem analysis.

[0069] Then, after capturing a retryable exception (such as a network exception, a database exception), you can implement the retry logic in the code, such as using a loop statement to make multiple attempts, and implementing a monitoring and alarm mechanism to promptly notify the operation and maintenance personnel when the number of retries exceeds a certain threshold or a specific type of error occurs frequently.

[0070] Finally, for those messages that cannot be successfully processed after multiple retries, they are forwarded to a dedicated dead letter queue. These messages can be used for subsequent manual review or special processing to ensure data integrity and business continuity.

[0071] Based on the above embodiment of steps S1 to S5, a cross-database real-time data synchronization method based on CDC technology is provided. That is, through the steps of CDC task instantiation, source database log monitoring, event serialization and sending, event consumption into the target database, error handling and retry mechanism, etc., it helps to synchronize data changes to other systems in real time or quasi-real time, such as synchronizing from the source database to the data warehouse, data analysis platform or other business systems, such as Fig. 9 shown.

[0072] Working principle: This embodiment provides a light-load, efficient, stable, and reliable cross-database real-time data synchronization method based on CDC technology. It synchronizes data changes to the target system in real time through code-free graphical configuration, thereby synchronizing from the source database to application scenarios such as data warehouses, data analysis platforms, or other business systems.

[0073] In enterprise scenario applications, there is no need for secondary code development, the technical threshold is low, and business personnel with non-technical backgrounds can achieve real-time synchronization of data across databases through interface configuration, thereby enhancing communication and collaboration across departments and improving efficiency. At the same time, it is compatible with Flink CDC and Debezium as data synchronization components, building real-time data pipelines, and synchronously outputting data to message queues (such as Kafka, RabbitMQ, etc.), relational databases, and NoSQL databases (such as MongoDB, Cassandra, etc.), thereby supporting a wider range of data application scenarios. Application scenarios include synchronization from source databases to data warehouses, data analysis platforms or other business systems, data backup, and other scenarios.

[0074] This embodiment is optimized in terms of configurability, maintainability, and scalability, and has the following core advantages over other embodiments on the market: 1. Zero code: Using a graphical configuration interface to replace existing open source components that require secondary coding can effectively lower the threshold for specialization; 2. Zero-intrusion: No need to adjust the database table structure, can directly achieve incremental real-time data synchronization from source to target, improving development efficiency; 3. Traceability: Supports visual viewing of complete synchronization transaction logs to improve troubleshooting efficiency; 4. Scalability: By introducing different Debezium and Flink CDC data synchronization components, the synchronization capability of supporting multiple data sources can be expanded; 5. Integrable: It adopts functional modules and component-based development and design, which can quickly build new integration forms with other products.

[0075] This embodiment can achieve efficient and smooth data flow and resource integration between various systems of the enterprise, thereby ensuring the closeness and smoothness of business connection.

[0076] This embodiment does not require secondary development of code, and can achieve the cross-database custom synchronization configuration function through interface configuration. This can lower the technical threshold, and business personnel with non-technical backgrounds can also directly participate in system configuration, enhancing communication and collaboration across departments and improving efficiency.

[0077] This embodiment has high integration, can integrate different data synchronization components such as Debezium, Flink CDC, etc., can be seamlessly integrated with different systems and applications, and supports output to a variety of database systems, including relational databases (such as MySQL, Oracle, SQL Server, etc.) and NoSQL databases (such as MongoDB, Cassandra, etc.), as well as message queues (such as Kafka, RabbitMQ, etc.), ensuring compatibility with various environments. Thus, real-time migration and synchronization of data can be achieved.

[0078] Different from existing open source components, this embodiment has self-developed technology that can respond to changes in business needs more quickly, perform function iterations and fix problems, and is easy to maintain and update in a unified manner.

[0079] The other parts of this embodiment are the same as those of the above-mentioned embodiment 1, and thus will not be described in detail.

[0080] Embodiment 3: This embodiment is based on any one of the above embodiments 1 to 2. Fig.10 , Fig.11 As shown, a cross-database real-time data synchronization device based on CDC technology is proposed, which includes three functional modules: data connection source management module, CDC synchronization task management module, and task monitoring management module.

[0081] The data connection source management module is a key component of the cross-database real-time synchronization system of CDC technology. It is responsible for centrally managing and maintaining the connection information of all business databases within the system. This module is designed to simplify the complex data source connection configuration process, improve the efficiency and security of data access, and enhance the flexibility and scalability of the system.

[0082] The main functions of the data connection source management module are: 1. Data source type support; The module supports a variety of mainstream database systems (including Oracle, MySQL, Postgresql, Hana, SQLServer, etc.), ensuring that data sources under different technology stacks can be connected and managed. This wide compatibility enables the system to easily integrate data from different sources.

[0083] 2. Connection information configuration; Service IP: IP address of the data source server, used to locate the database service.

[0084] Service port: The port number that the database service listens on, ensuring that the connection request can be correctly sent to the database service.

[0085] Database Name / SID: Unique identifier of the database, used to distinguish different database instances on the same server.

[0086] Connection username and password: used to verify the identity of the connection request and ensure that only authorized users can access the database.

[0087] Connection URL: A standardized string format that contains all the information needed to connect to the database, such as protocol, host name, port, database name, etc.

[0088] Proxy Address: Allows database connections to be made through a proxy server, which is particularly useful when you need to cross a network or bypass firewall restrictions.

[0089] Advanced information: including connection pool parameters such as the minimum number of connections, the maximum number of connections, and verification SQL (used to test whether the database connection is successful). These settings help optimize the performance and stability of database connections.

[0090] 3. Test and verification; Allows users to test the correctness of data source connection configuration before actual use to ensure that all settings work properly. This reduces connection failures caused by configuration errors.

[0091] The data connection source management module mainly functions as follows: 4. Simplify access logic; By centrally managing data source connection information, this module simplifies the logic of data access and eliminates the need for developers and database administrators to configure database connections separately in each application or service.

[0092] 5. Improve efficiency and safety; Connection pool management (such as minimum / maximum number of connections) helps reduce database connection overhead and improve data access efficiency. Encrypted storage of sensitive information such as connection passwords enhances system security.

[0093] 6. Flexibility and scalability; Support for multiple data source types and flexible connection configuration options allows the system to easily adapt to different business needs and technology changes. The modular design makes it easy to add support for new data source types or expand existing functionality in the future.

[0094] In summary, the data connection source management module is an indispensable part of the cross-database real-time synchronization system of CDC technology. It provides strong support for the stable operation of the system and efficient data access by providing comprehensive data source connection management functions.

[0095] The CDC synchronization task management module is a system component that comprehensively manages the change data capture task. This module provides operations such as creating, editing, starting, pausing, and deleting CDC tasks, ensuring the flexibility and controllability of the data synchronization process.

[0096] The main function configuration of the CDC synchronization task management module is: 1. Basic information configuration; CDC Name: Name the CDC task for easy identification and management.

[0097] CDC directory: Classify CDC tasks into specific directories to implement task classification management.

[0098] CDC task description: A detailed description of the CDC task, explaining its purpose, synchronization content, etc.

[0099] Kafka topic information: Specifies the topic information in Kafka used to receive CDC data.

[0100] CDC synchronization mode: Set the CDC synchronization mode, such as real-time synchronization, scheduled synchronization, etc.

[0101] 2. Source information configuration; Reference the data source information configured in the data connection source management module, including data source type, service IP, port, database name, connection user name and password, etc.

[0102] 3. Synchronous table information configuration; The data source information configured in the data connection source management module is also referenced as the target database information.

[0103] 4. Table preprocessing rule configuration; Including integrity check configuration, abnormal data filtering configuration, missing field filling configuration, and data structure conversion configuration.

[0104] 5. Configure the corresponding relationship; Source table and target table correspondence: clarify the correspondence between the source table and the target table.

[0105] Correspondence between source fields and target fields: Map the correspondence of each field in detail.

[0106] Field primary key: Specify the field used to uniquely identify the record as the primary key.

[0107] Update model: Set the update strategy when synchronizing data, such as updating existing records or appending new records.

[0108] Whether to automatically create a new table: Choose whether to automatically create a new table on the target end based on synchronization requirements.

[0109] The CDC synchronization task management module mainly functions as follows: Improve data synchronization efficiency: Through flexible task configuration and efficient synchronization mechanism, ensure that data can be synchronized to the target end in a timely and accurate manner.

[0110] Enhanced data consistency: Ensure the consistency and integrity of synchronized data through detailed field correspondence configuration and primary key settings.

[0111] Improve system flexibility: Support multiple data source types and synchronization modes to meet data synchronization needs in different business scenarios.

[0112] Simplify management operations: Provide a unified interface and operation process to reduce management complexity and improve management efficiency.

[0113] In summary, the CDC synchronization task management module plays a vital role in the cross-database real-time synchronization system of CDC technology. It not only improves the efficiency and accuracy of data synchronization, but also enhances the stability and security of the system. Users can better manage and maintain CDC tasks to ensure the smooth progress of data synchronization.

[0114] The task monitoring management module provides comprehensive monitoring of the CDC task execution process, ensuring the efficiency, reliability and traceability of the data synchronization process.

[0115] The main functions of the task monitoring management module are: 1. Data synchronization monitoring; Real-time status display: Displays the real-time running status of the CDC task, including whether the task is running, the amount of data synchronized, the synchronization speed, and other information.

[0116] Abnormal alarm: Monitor abnormal situations during task execution, such as data inconsistency, connection failure, etc., and issue alarm notifications in time so that users can respond quickly and solve problems.

[0117] 2. Logging and auditing: Logging: Record the execution log of the CDC task in detail, including the task start time, end time, specific data synchronized, error information, etc., to provide a basis for subsequent troubleshooting and data analysis.

[0118] Audit trail: Provides an audit trail function for task execution history. Users can view task execution records, modification history, etc. to ensure the traceability and security of the data synchronization process.

[0119] The task monitoring management module mainly functions as follows: Performance optimization and resource allocation: Performance monitoring: By monitoring the performance indicators of CDC tasks (such as CPU usage, memory usage, network bandwidth, etc.), users can understand the impact of tasks on system resources and optimize performance.

[0120] Resource allocation: Rationally allocate system resources according to the importance and urgency of the task to ensure the smooth execution of key tasks.

[0121] Data consistency guarantee: Data verification: During the data synchronization process, the data on the source and target ends are verified to ensure the consistency and integrity of the synchronized data.

[0122] Conflict resolution: During the data synchronization process, if data conflicts (such as primary key conflicts, data duplication, etc.) occur, corresponding conflict resolution strategies are provided to ensure the smooth progress of the synchronization process.

[0123] To sum up, the task monitoring management module also plays a vital role in the data synchronization process. It ensures the real-time, accuracy, reliability and stability of data synchronization, provides strong support for business decision-making and data analysis, and improves user experience and service quality.

[0124] Based on the above functional module description, we can derive the relationship between them, such as Fig.11 shown.

[0125] This embodiment also provides a computer device, comprising a memory, a processor and a transceiver which are communicatively connected in sequence, wherein the memory is used to store computer programs, the transceiver is used to send and receive messages, and the processor is used to read the computer program to execute the cross-database real-time synchronization method based on CDC technology as described in the first aspect or any possible design of the first aspect.

[0126] This embodiment also provides a computer-readable storage medium, on which instructions are stored. When the instructions are executed on a computer, a cross-database data real-time synchronization method based on CDC technology or any possible design of a cross-database data real-time synchronization method based on CDC technology is executed.

[0127] This embodiment also provides a computer program product comprising instructions, which, when executed on a computer, causes the computer to execute the cross-database real-time synchronization method based on CDC technology or any possible design of the cross-database real-time synchronization method based on CDC technology.

[0128] The other parts of this embodiment are the same as any one of the above-mentioned embodiments 1-2, so they will not be repeated here.

[0129] The above description is only a preferred embodiment of the present invention and does not limit the present invention in any form. Any simple modification or equivalent change made to the above embodiment based on the technical essence of the present invention shall fall within the protection scope of the present invention.

Claims

1. A cross-database real-time data synchronization method based on CDC technology, characterized in that: The specific steps include: Step S1: Build a CDC environment according to the selected data synchronization component, and build and start a CDC connector according to the data source configuration and CDC task configuration information; Step S2: Monitor multi-source database logs according to the constructed CDC connector to obtain database change events; Step S3: Convert the database change event into Json format; Step S4: Initialize the thread pool, subscribe to the Kafka topic according to the CDC task configuration information, batch generate SQL statements according to the pre-processed Json format database change events, and call the commitSync() method to obtain the offset of the Kafka consumer; Step S5: Capture abnormal data according to the offset of the Kafka consumer and synchronize it to the target system according to the set retry mechanism.

2. According to the method of claim 1, the method is characterized in that: The step S1 specifically includes the following steps: Step S11: Build a CDC environment based on the Debezium data synchronization component, the Kafka data synchronization component, the Debezium Connector data synchronization component, and the Flink CDC data synchronization component; Step S12: Build and start a CDC connector according to the data source configuration, CDC task configuration information, and data synchronization components.

3. According to the CDC technology-based real-time cross-database data synchronization method of claim 2, it is characterized in that: The step S11 specifically includes the following steps: Step S111: Install the Zookeeper cluster according to the Debezium component, manage the configuration of the Kafka cluster, elect the leader, and process the rebalance mechanism of the consumer group; Step S112: Install Debezium Connector and deploy it in the Kafka Connect tool as a source connector in the form of a JAR package to capture change events of a specific database and send them to the Kafka cluster; Step S113: Install Apache Flink according to the Flink CDC component and add the Flink CDC connector dependency.

4. According to the method of claim 2, the method is characterized in that: The step S12 specifically includes the following steps: Step S121: Send a POST request to Kafka Connect according to the Debezium component to instantiate and start DebeziumConnector; Step S122: According to the Flink CDC component, in the Flink SQL environment, dynamically configure the MySQL CDCSource through the programming API, and instantiate and start the Flink CDC task.

5. According to the method of claim 1, the method is characterized in that: The step S2 specifically includes the following steps: Step S21: Capture data changes by subscribing to the binary log binlog of the MySQL database according to the CDC connector; Step S22: Capture data changes according to the logical replication of the PostgreSQL database, and obtain database change events according to the application program's subscription to the database WAL log; Step S23: Parse the online and archived redo log files Redo Logs of the Oracle database according to the CDC connector.

6. According to the method of claim 1, the method is characterized in that: The step S4 specifically comprises the following steps: Step S41: Call ThreadPoolTaskExecutor to initialize the thread pool according to the configured thread pool parameters; Step S42: according to the CDC task configuration synchronization table information, create a Kafka consumer and submit it to the initialized thread pool; Step S43: Call the commitSync() method to commit the offset of the Kafka consumer.

7. A cross-database real-time data synchronization method based on CDC technology according to claim 6, characterized in that: The step S42 specifically includes the following steps: Step S421: obtaining table information to be synchronized according to the CDC task configuration, and creating a task instance according to the table information to be synchronized; the task instance encapsulates the logic of creating a Kafka consumer, subscribing to a specific topic, and processing received messages; Step S422: Submit the created task instance to the initialized thread pool, and dynamically allocate execution resources to the task instance according to the number of core threads, the maximum number of threads, and the task queue capacity of the thread pool, and execute the task instance in parallel.

8. The cross-database real-time data synchronization method based on CDC technology according to claim 6 is characterized in that: The step S43 specifically includes the following steps: Step S431: Create an independent KafkaConsumer instance in each thread synchronization table instance and set configuration parameters; Step S432: Call the poll() method in the KafkaConsumer instance to obtain ConsumerRecords, and parse it according to Jackson to obtain JSON data; Step S433: preprocessing the JSON data according to the set preprocessing rules; Step S434: Determine the target database table and field mapping according to the table correspondence in the CDC task configuration, call the FreeMarker template engine to batch generate SQL statements according to the operation type and the preprocessed JSON data; Step S435: Send the batch-generated SQL statements in batches to the SQL execution client, and call the commitSync() method to commit the offset of the Kafka consumer.

9. The cross-database real-time data synchronization method based on CDC technology according to claim 8 is characterized in that: The specific operation of step S435 is: sending the batch-generated SQL statements to the SQL execution client in batches, executing all SQL statements in sequence in the same transaction, capturing and handling exceptions during the execution process, and committing the transaction if all SQL statements are successfully executed, otherwise rolling back the SQL transaction; After successfully processing a batch of messages and committing the database transaction, call the commitSync() method to commit the Kafka consumer's offset.

10. The cross-database real-time data synchronization method based on CDC technology according to claim 1, characterized in that: The specific operations of step S5 are: Step S51: According to the offset of the Kafka consumer, the abnormal data is captured and written into Elasticsearch in the form of logs; Step S52: The abnormal type of the abnormal data is determined. If the abnormal data is a retryable exception, a loop statement is called to try multiple times, and a monitoring and alarm mechanism is implemented. When the number of retries exceeds a certain threshold or a specific type of error occurs frequently, the operation and maintenance personnel are notified in a timely manner; If the message cannot be processed successfully after multiple retries, it will be forwarded to the dead letter queue.

Images