Business processing method and system based on database data change driving
By combining Debezium and Kafka, the problems of poor real-time performance, high cost, and low security of existing data synchronization technologies are solved, achieving low-cost, highly controllable data synchronization with millisecond-level latency. It supports multiple database types and is suitable for e-commerce inventory synchronization scenarios.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-12-31
- Publication Date
- 2026-04-10
AI Technical Summary
Existing data synchronization technologies suffer from poor real-time performance, high costs, low security, and high maintenance costs. They are particularly prone to database overload in flash sale scenarios and rely on the stability of third-party platforms.
The system employs an open-source Debezium + Kafka combination. Debezium retrieves data change information from database logs, while Kafka handles event message reception, persistence, and traffic shaping. This enables internal data flow, avoids third-party dependencies, ensures security, and reduces operational costs.
It achieves millisecond-level data capture latency, avoids instantaneous pressure, reduces operation and maintenance costs by more than 60%, ensures data security and controllability, and supports mainstream relational and NoSQL databases.
Smart Images
Figure CN121833846A_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the field of data processing technology, and in particular to a business processing method and system based on database data change-driven processes. Background Technology
[0002] Existing data synchronization technologies commonly employ the following two methods: 1. Scheduled task polling solution: Query external data sources (such as other databases) at fixed intervals to pull incremental data for synchronization (e.g., e-commerce order services periodically query the inventory database).
[0003] 2. Commercial big data platform solution: Purchase mature data products (such as AWS Glue, Alibaba Cloud DataWorks) and achieve cross-system data synchronization through ETL.
[0004] The two implementation methods described above have the following technical drawbacks: 1. Timed polling solution: poor real-time performance (minute / hour level delay), batch queries cause instantaneous high pressure on downstream services (such as database crashing in a flash sale scenario).
[0005] 2. Commercial platform solution: High cost (charged based on data volume), high data security risk (data flows through third-party platforms), and dependent on platform stability.
[0006] The above problems urgently need to be addressed. Summary of the Invention
[0007] To address the problems existing in the background technology, the present invention provides a business processing method based on database data change-driven processing.
[0008] The advantages of this invention are as follows: This application provides a business processing method driven by database data changes, based on the combination of open-source Debezium and Kafka. Data flows internally without third-party dependencies, eliminating the risk of leakage, ensuring high security, low cost, and high controllability, and reducing long-term operation and maintenance costs by more than 60%. Latency is reduced from minutes to milliseconds. Change capture and Kafka traffic shaping avoid instantaneous pressure and overselling issues. Debezium supports mainstream relational databases and NoSQL. Attached Figure Description
[0009] Figure 1 This is a flowchart of a business processing method based on database data change-driven according to the present invention; Figure 2 This is a flowchart of the steps of the present invention, which involves a smart router distributing the event messages processed by Kafka to the downstream business layer according to preset configuration rules; Figure 3This is a flowchart illustrating the method of the present invention for simultaneously processing data from multiple databases; Figure 4 This is a flowchart of the anomaly detection steps of the present invention. Detailed Implementation
[0010] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.
[0011] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this application pertains; the terminology used herein in the specification of the application is for the purpose of describing particular embodiments only and is not intended to limit the application; the terms “comprising” and “having”, and any variations thereof, in the specification, claims, and foregoing description of the drawings of this application are intended to cover non-exclusive inclusion.
[0012] In this document, the term "embodiment" means that a particular feature, structure, or characteristic described in connection with an embodiment may be included in at least one embodiment of this application. The appearance of this phrase in various places throughout the specification does not necessarily refer to the same embodiment, nor is it a separate or alternative embodiment mutually exclusive with other embodiments. It will be explicitly and implicitly understood by those skilled in the art that the embodiments described herein can be combined with other embodiments.
[0013] The following detailed description, with reference to the accompanying drawings, illustrates a business processing method, system, computer equipment, and storage medium based on database data change-driven principles according to the present invention.
[0014] Example 1 This embodiment provides a business processing method based on database data change-driven operations, which includes the following steps: Debezium is used to obtain data change information from the database logs and parse the data change information into structured event messages; specifically, the database can be MySQL, PostgreSQL or Oracle. Kafka (i.e., Apache Kafka cluster) is used to receive and persist event messages and handle traffic peaking. Intelligent routers are used to distribute and push event messages processed by Kafka to downstream business layers, such as the Kafka queue for order services, according to preset configurable rules. The downstream business layer receives the event message and performs the corresponding business processing.
[0015] This invention discloses a business processing method driven by database data changes. Based on the combination of open-source Debezium and Kafka, data flows internally without third-party dependencies, eliminating the risk of data leakage, ensuring high security, low cost, and high controllability, and reducing long-term operation and maintenance costs by more than 60%. The latency is reduced from minutes to milliseconds. Change capture and Kafka traffic shaping avoid instantaneous pressure and overselling issues. Debezium supports mainstream relational databases and NoSQL.
[0016] In some preferred embodiments, such as Figure 2 As shown, the steps for obtaining data change information from database logs using Debezium include: Continuously read the database logs; Parse row-level change events in the database log on a transaction-by-transaction basis to obtain the parsed message event headers; Specifically, the parsing relationship is as follows: WRITE_ROW_EVENT → Insertion operation ('c'); UPDATE_ROW_EVENT → Update operation ('u'); DELETE_ROW_EVENT → Delete operation ('d').
[0017] Match the parsed message event header with preset filtering conditions; If the match is successful, the data format will be standardized according to the format conversion control, that is, the database log format will be converted into the Debezium general event structure. If a match fails, it is logged and / or discarded.
[0018] Specifically, the database log format is converted to Debezium's general event structure, such as JSON format. The implementation code is as follows: { "before": { "id": 1001, "product_name": "Product A", "quantity": 50, "update_time": "2025-12-02 10:00:00" }, "after": { "id": 1001, "product_name": "Product A", "quantity": 45, "update_time": "2025-12-02 10:05:00" }, "source": { "version": "2.3.0", "connector": "mysql", "name": "mysql-inventory-server", "ts_ms": 1733137410000, "snapshot": "false", "db": "inventory", "table": "product_stock", "server_id": 223344, "gtid": null, "file": "mysql-bin.000003", "pos": 10567, "row": 0, "thread": 7, "query": null }, "op": "u", "ts_ms": 1733137420500.
[0019] Key field descriptions: before: The state of the data row before the change (existed during the update / delete operation); after: The status of the data row after the change (existing during insert / update operations); Source: Event metadata (database, table name, Binlog location, etc.); op: Operation type (c=create, u=update, d=delete); ts_ms: Event handling timestamp.
[0020] In some preferred embodiments, it also includes: Establish a JDBC connection with the source database; specifically, establish a JDBC connection with the source database through the Debezium Connector. Read the complete data status of the current table (e.g., execute SELECT * FROM product_stock); Each record is emitted as an INSERT event of type "snapshot"; Record the log position when the snapshot is completed; Start reading the database logs from the log position.
[0021] In some preferred embodiments, the structured event messages are in Avro or JSON format, and the event messages include at least the table name, primary key, and change type. Preferably, the format is Avro, which saves bandwidth and supports schema evolution.
[0022] In some preferred implementations, the steps of receiving and persisting event messages using Kafka include: Name the event message; Format: {server-name}.{database-name}.{table-name}; Example: mysql-inventory-server.inventory.product_stock.
[0023] To configure event messages to be persistent, you can use the following code: # Kafka Topic Configuration replication.factor=3 min.insync.replicas=2 retention.ms=604800000 # Messages are retained for 7 days cleanup.policy=delete.
[0024] In some preferred implementations, the steps of using Kafka to perform traffic shaping for event messages include: During peak database write times, Kafka is used for buffering, and downstream business layers consume data according to their capacity. Specifically, the consumption control strategy code is as follows: / / Kafka Consumer configuration example Properties props = new Properties(); props.put("max.poll.records", 500); / / Maximum number of records to retrieve in a single batch props.put("fetch.max.bytes", 52428800); / / Maximum number of bytes to fetch in a single batch props.put("max.partition.fetch.bytes", 1048576); / / Maximum number of bytes per partition.
[0025] In some preferred implementations, the steps of receiving and persisting event messages using Kafka and performing traffic shaping also include saving copies of the event messages.
[0026] Specifically, the replication mechanism: each partition has replicas stored on multiple brokers. Preferably, it further includes: ACK mechanism: The producer configures acks=all to ensure that messages are written to all replicas; Displacement commit: Consumers manually commit displacements to avoid message loss.
[0027] In some preferred implementations, the steps of receiving and persisting event messages using Kafka and handling traffic peaking also include monitoring the Kafka cluster's lag (consumption latency) and determining whether a consumption bottleneck has been reached based on a preset threshold. That is, setting lag threshold alarms allows for timely detection of consumption bottlenecks.
[0028] In some preferred embodiments, such as Figure 2 As shown, the steps for using a smart router to distribute Kafka-processed event messages to downstream business layers according to preset configuration rules include: Configure routes with a priority given to configurable routes, followed by plug-in routes.
[0029] Specifically, the configured routing is directly routed to the target service Kafka queue according to YAML rules (such as table name product-stock-order service).
[0030] Plug-in routing uses Java SPI plugins to filter fields and perform other custom routing logic before pushing the data to the target service's Kafka queue.
[0031] The data transmitted via event messages contains complete information about database changes. During downstream transmission, the unfiltered transmission of large amounts of data can put pressure on downstream services. Customizable filtering configurations allow for the selection of business data based on data characteristics, ensuring that the data messages delivered downstream are only valid data relevant to those services, thus reducing network bandwidth usage and server access pressure. Configurable filtering capabilities also better address future service customization needs.
[0032] Specifically, the filtering conditions include: 1. A whitelist or blacklist of database table names, allowing access to tables on the whitelist or filtering and blocking tables on the blacklist. 2. Based on the type of database operation, such as allowing insert and update operations while blocking delete operations.
[0033] Specifically, it adapts to the database characteristics of various vendors to process database messages, converting data messages from various databases into a standard format defined by the enterprise. Downstream services can access message content transparently through standardization without having to worry about message format differences.
[0034] Specifically, the Java code for retrieving MQ messages is as follows: / / Subscribe to all original CDC topics consumer.subscribe(Pattern.compile("mysql-inventory-server\\.inventory\\..*")); / / Message consumption logic while (running) { ConsumerRecords<String, String> records = consumer.poll(Duration.ofMillis(1000)); for (ConsumerRecord<String, String> record : records) { processDebeziumEvent(record.value()); } }
[0035] Specifically, this step is implemented based on routing rule management in YAML / configuration center, and message processors are used to filter, transform, and enrich messages.
[0036] Specifically, the routing rule configuration code is as follows (Yaml example): # routing-rules.yaml rules: - ruleId: "stock_rule_001" source: database: "inventory" table: "product_stock" destination: "business.order-service-events" filters: - operation: ["u", "c", "d"]# Listen for all operations transform: type: "simple" # Directly forward the original message - ruleId: "order_rule_002" source: database: "inventory" table: "orders" destination: "business.order-service-events" filters: - operation: ["c", "u"]# Only listen for creation and update operations - condition: "after.status IN ('PAID','SHIPPED')" # Status filtering transform: type: "custom" class: "com.company.OrderEventTransformer" # Custom conversion logic - ruleId: "user_rule_003" source: database: "inventory" table: "users".
[0037] Preferably, such as Figure 3 As shown, it also includes a Java SPI extension implementation, configurable routing + plug-in extension, adaptable to multiple business scenarios, and no code modification is required to add new services.
[0038] The specific implementation code is as follows: / / Router plugin interface definition public interface RoutePlugin { String determineDestination(DebeziumEvent event, RouteRule rule); boolean shouldProcess(DebeziumEvent event); String transformMessage(DebeziumEvent event); } / / SPI configuration file: META-INF / services / com.company.RoutePlugin com.company.AdvancedUserRouter.
[0039] In some preferred embodiments, the downstream business layer receives event messages and performs corresponding business processing steps, including updating the local cache and triggering business processes.
[0040] Specifically, message consumption implementation includes consumer configuration, cache update strategy, business logic triggering, and idempotency guarantee mechanism.
[0041] The consumer configuration code is as follows: @KafkaListener(topics = "business.order-service-events", groupId = "order-service") public void handleOrderEvent(String message) { try { OrderEvent event = objectMapper.readValue(message,OrderEvent.class); processOrderEvent(event); } catch (Exception e) { / / Exception handling: Log the exception, retry, or add the exception to the dead letter queue. log.error("Failed to process order event: {}", message, e); }
[0042] The cache update strategy implementation code is as follows: Scenario: Updating Redis cache based on changes to the product_stock table @Service public class ProductStockCacheService { @Autowired private RedisTemplate<String, Object> redisTemplate; public void updateStockCache(DebeziumEvent event) { String op = event.getOp(); String productId = event.getAfter().get("id").toString(); switch (op) { case "c": / / Insert case "u": / / Update / / Update the cache directly using the after data redisTemplate.opsForValue().set( "product:stock:" + productId, event.getAfter(), Duration of Hours (2) ); break case "d": / / Delete redisTemplate.delete("product:stock:" + productId); break } log.info("Update product {} inventory cache, operation type: {}", productId, op); } }
[0043] The business logic trigger implementation code is as follows: Scenario: Automatically trigger order shipment checks when inventory decreases. @Service public class OrderAutoShipService { public void checkAutoShip(ProductStockEvent event) { / / 1. Check if this is an update indicating a decrease in inventory. if (!isStockDecreased(event)) { return; } / / 2. Check pending orders List <order>pendingOrders = orderMapper.selectPendingOrdersByProduct( event.getProductId()); / / 3. Sort and process orders by creation time pendingOrders.sort(Comparator.comparing(Order::getCreateTime)); for (Order order : pendingOrders) { if (canAutoShip(order, event.getCurrentStock())) { triggerShipment(order); / / Update inventory count event.setCurrentStock(event.getCurrentStock() -order.getQuantity()); } } } private boolean isStockDecreased(ProductStockEvent event) { return event.getOp().equals("u")&& event.getBeforeStock()>event.getAfterStock(); } }
[0044] The code for implementing the idempotency guarantee mechanism is as follows: @Component public class IdempotentMessageProcessor { @Autowired private MessageRecordMapper messageRecordMapper; public boolean isMessageProcessed(String messageId) { return messageRecordMapper.existsById(messageId); } @Transactional public void markMessageProcessed(String messageId, StringbusinessId) { MessageRecord record = new MessageRecord(); record.setMessageId(messageId); record.setBusinessId(businessId); record.setProcessTime(LocalDateTime.now()); messageRecordMapper.insert(record); } Public void processWithIdempotentCheck(DebeziumEvent event,Consumer <debeziumevent>processor) { String messageId = generateMessageId(event); if (!isMessageProcessed(messageId)) { processor.accept(event); markMessageProcessed(messageId,event.getAfter().get("id").toString()); } else { log.warn("Message processed, skipped: {}", messageId); } } private String generateMessageId(DebeziumEvent event) { return event.getSource().getFile() + ":" + event.getSource().getPos() + ":" + event.getSource().getRow(); } }
[0045] In some preferred embodiments, such as Figure 4 As shown, it also includes an anomaly detection step, which includes determining the anomaly type and processing it according to the anomaly type.
[0046] Specifically, the exception types include message backlog, service downtime, parsing failure, and downstream non-response.
[0047] in, The message backlog is handled by horizontal scaling of Prometheus alerts and service A.
[0048] The service outage handling methods are: Kafka offset reset and resuming interrupted downloads to a new instance.
[0049] The handling method for parsing failure is as follows: ELK logs the failure and posts it to the dead letter queue DLQ.
[0050] The downstream unresponsive handling methods are: message retry (3 times) and manual DLQ troubleshooting.
[0051] The following describes the method of this invention with a practical application example (e-commerce inventory synchronization scenario): Environment Deployment: - Database: MySQL 8.0 (with ROW format Binlog enabled); - Debezium: Deploy the MySQL Connector container and configure connections to the MySQL and Kafka clusters; - Kafka: Cluster deployment, create the inventory_change_topic; Service A: A Spring Boot application that integrates a Kafka Consumer and routing logic; Downstream services: Order service integrates with Kafka Consumer to consume messages.
[0052] 1. Inventory changes in the MySQL product_stock table → 2. Debezium parses the Binlog to generate a JSON message → 3. Kafka writes to inventory_change_topic → 4. Service A distributes the message to the order service according to routing rules → 5. The order service updates its cache and checks the order status.
[0053] Exception handling: 1. Message backlog: Kafka monitoring alerts, service A is horizontally scaled up; 2. Service downtime: Kafka consumer offsets are automatically committed, and interrupted downloads can be resumed after recovery; 3. Parsing failure: Log the error and send it to the Dead Letter Queue (DLQ) for manual processing.
[0054] Example 2 This embodiment provides a business processing system driven by database data changes, which includes: The data change acquisition module is used to obtain data change information from the database logs using Debezium and parse the data change information into structured event messages; The message relay module is used to receive and persist event messages using Kafka and to handle traffic peak shaping. The business distribution module is used to distribute the event messages processed by Kafka to downstream business layer modules according to preset configuration rules using an intelligent router. The downstream business layer module is used to receive event messages and perform corresponding business processing.
[0055] Example 3 This embodiment provides a computer device, including a memory and a processor. The memory stores a computer program, and the processor executes the computer program to implement the steps of the method of Embodiment 1.
[0056] Example 4 This embodiment provides a computer storage medium storing instructions that, when executed on a computer, cause the computer to perform the method as described in Embodiment 1.< / debeziumevent> < / order>
Claims
1. A business processing method based on database data change-driven processing, characterized in that, Includes the following steps: Debezium is used to obtain data change information from database logs, and the data change information is parsed into structured event messages; Kafka is used to receive and persist the event messages and to perform traffic shaping. The intelligent router distributes the event messages processed by Kafka to the downstream business layer according to preset configuration rules; The downstream service layer receives the event message and performs corresponding service processing.
2. The business processing method based on database data change-driven according to claim 1, characterized in that, The steps for obtaining data change information from database logs using Debezium include: Continuously read the database logs; The database logs are parsed in units of transactions to obtain the parsed message event headers; Match the parsed message event header with preset filtering conditions; If the match is successful, then according to the format conversion control, the data format will be converted and standardized. If a match fails, it is logged and / or discarded.
3. The business processing method based on database data change-driven according to claim 2, characterized in that, Also includes: Establish a JDBC connection to the source database; Read the complete data status of the current table; Each record is emitted as an INSERT event of type "snapshot"; Record the log position when the snapshot is completed; The database logs are read starting from the specified log position.
4. The business processing method based on database data change-driven according to claim 1, characterized in that, The structured event message is in Avro or JSON format, and the event message includes at least the table name, primary key, and change type.
5. The business processing method based on database data change-driven according to claim 1, characterized in that, The steps of receiving and persisting the event messages using Kafka include: Name the event message; Configure the event messages to be persistent.
6. The business processing method based on database data change-driven according to claim 1, characterized in that, The steps of using Kafka to perform traffic shaping on the event messages include: During peak database write times, Kafka is used for buffering, and downstream business layers consume data according to their capacity.
7. The business processing method based on database data change-driven according to claim 1, characterized in that, The steps of receiving and persisting the event messages using Kafka and performing traffic shaping also include saving copies of the event messages.
8. The business processing method based on database data change-driven according to claim 1, characterized in that, The steps of receiving and persisting the event messages using Kafka and performing traffic shaping also include monitoring the Lag of the Kafka cluster and determining whether a consumption bottleneck has been reached based on a preset threshold.
9. The business processing method based on database data change-driven according to claim 1, characterized in that, The step of using a smart router to distribute the event messages processed by Kafka to the downstream business layer according to preset configuration rules includes: Configure routes with a priority given to configurable routes, followed by plug-in routes.
10. The business processing method based on database data change-driven according to claim 1, characterized in that, The downstream business layer receives the event message and performs corresponding business processing steps, including updating the local cache and triggering the business process.
11. A business processing system driven by database data changes, characterized in that, include: The data change acquisition module is used to obtain data change information from the database log using Debezium and parse the data change information into structured event messages; The message relay module is used to receive and persist the event messages using Kafka and to perform traffic shaping. The business distribution module is used to distribute the event messages processed by Kafka to downstream business layer modules according to preset configuration rules using an intelligent router; The downstream business layer module is used to receive the event message and perform corresponding business processing.
12. A computer device comprising a memory and a processor, wherein the memory stores a computer program, characterized in that, When the processor executes the computer program, it implements the steps of the method according to any one of claims 1 to 10.
13. A computer storage medium, characterized in that, The computer storage medium stores instructions that, when executed on the computer, cause the computer to perform the method as described in any one of claims 1 to 10.