Data integration method for docking kafka message in combination with MySql log
By combining MySQL logs and Kafka messages, the data integration method solves the problems of latency and inconsistency in data integration during enterprise business expansion, achieving real-time data consistency and supporting personalized services and analysis.
Patent Information
- Application Number
- CN202510961710.9
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-07-11
- Publication Date
- 2025-10-31
AI Technical Summary
In the process of enterprise business expansion, existing technologies have problems such as high communication costs and delays in interface-based data integration methods, while real-time log-based access methods are time-consuming and have data inconsistency issues during the initial integration.
By creating a data collection database and connecting MySQL logs to Kafka messages, non-intrusive data integration is achieved. Changes are collected and sent to Kafka topics in real time. Middleware is used to integrate the data into the central database in real time. Data is then processed and retrieved through resource pools and search engines, and data consistency is verified periodically.
It achieves real-time data consistency, reduces communication costs, improves data processing efficiency, supports personalized search and recommendation functions, and ensures data availability and flexibility.
Smart Images
Figure CN120873023A_ABST
Abstract
Description
Technical Field
[0001] This invention belongs to the field of data integration technology, specifically a data integration method that combines MySQL logs with Kafka messages. Background Technology
[0002] As enterprises grow, expand their businesses, and diversify, various vertical businesses are rapidly developing, forming vertical data centers. To reduce technology costs, improve efficiency, and empower businesses, it is necessary to connect these data centers and build them using unified standards. The solution integrates data and provides unified services, forming a unified data system across all business lines, ensuring rapid business innovation. Data integration methods include an intrusive interface-based approach, where business users call interfaces to write data to the central database, and a non-intrusive binlog parsing approach, uniformly collecting real-time binlog from business databases.
[0003] The first known technology uses an interface-based data integration method. In the early stages of enterprise development, business lines expand slowly, and the amount of existing and new data is small. Each business line actively calls interfaces to integrate data into a central database, which is relatively low-intrusive and cost-effective. This method is feasible initially, but as the enterprise grows, business lines expand, and data volume increases, the following drawbacks become apparent: First, because it uses intrusive interface calls, integrating data from various business lines requires relevant departments to actively call interfaces and push data, increasing communication and implementation costs. Second, interface calls inevitably introduce latency issues. For data with high time requirements, this method cannot guarantee real-time performance.
[0004] The second known technology employs a data integration method based on real-time log access messages. However, with business expansion and increased data volume, interface-based data integration is no longer sufficient. Using real-time database binlogs avoids data integration logic intruding into various business units. Existing data requires scheduled task processing or triggering of the full data binlog; subsequent data can be integrated based on real-time changes. While this method reduces communication and implementation costs and ensures real-time data availability, its main drawback is the time-consuming nature of the initial full data integration, requiring scheduled task processing or triggering of the full data binlog, and also presenting inconsistencies between business databases and the central database. Therefore, this application addresses the shortcomings of the two aforementioned technologies by making targeted improvements to the data integration method. Summary of the Invention
[0005] The purpose of this invention is to provide a data integration method that combines MySQL logs with Kafka messages to solve the problems mentioned in the background.
[0006] To achieve the above objectives, the present invention provides the following technical solution: a data integration method combining MySQL logs with Kafka messages, the data integration method comprising the following steps:
[0007] Step 1: Create a data model table for the data collection library; Create a data collection library in a non-intrusive manner to serve as a data transfer station, collect the data models that need to be integrated from various business lines, and create corresponding charts in the data collection library to isolate business data from collected data;
[0008] Step 2: Extract data to the collection library; use synchronization tools to extract data from each business line to the collection library and match it with the charts created in Step 1.
[0009] Step 3: Apply for Kafka message topics; for each chart extracted from the collection database, apply to create the corresponding Kafka message topic for the main table; related sub-tables are accessed using join queries;
[0010] Step 4, Real-time data collection and transmission; The collection library collects binlog logs from the access table in real time and sends the changed data to the topic applied for in Step 3 in JSON format. The middleware features are used to integrate the data into the central database in real time.
[0011] Step 5: Integrate Kafka messages and write them to the central database; The data integration system listens to each Kafka message, integrates the data processing logic, and writes the data to the central database.
[0012] Step 6: Identify and differentiate data; assign different identifiers to each business line to distinguish data after it is written to the central database;
[0013] Step 7: Push task data to resource pools; push data from each business line to different resource pools based on whether or not the data is divided according to the business line. Separate the data sources of search and recommendation functions from statistical analysis data sources to reduce data processing complexity and improve service response time.
[0014] Step 8: Synchronize resource pool data to the search engine; synchronize the data in the resource pool used for search recommendations to the search engine in real time.
[0015] Step 9: The search engine module generates an index. The search engine module receives and parses the content in the message queue and generates an efficient index. The field attribute in the index contains the search criteria required for the page.
[0016] Step 10: Retrieve data based on conditions; use conditional keywords to search for data.
[0017] Step 11: Periodic comparison and verification; compare the data in the central database and the collection database every 5 minutes; if there is any missing or different data, this task will write the data into the central database to ensure data consistency.
[0018] Step 12: Record changes in the central database data in real time; write the changes in the central database data into the messaging system in real time, and each business line can subscribe to relevant data as needed for data analysis and business advancement.
[0019] Preferably, the data for the business lines comes from their respective vertical data centers; the data for the data integration system comes from the collection library.
[0020] Preferably, the synchronization tool in step two is, but is not limited to, CDC.
[0021] Preferably, step five specifically includes: defining a thread pool, the number of which is determined according to the number of CPU cores of different servers, and its value is the number of CPU cores + 1. Multi-threading is used to maximize the utilization of services, improve integration efficiency, and ensure data real-time performance.
[0022] Preferably, the two methods in step seven are: one is to divide according to businessId and push into the resource pools of different business lines; the other is to push into a unified resource pool without distinction.
[0023] The two approaches involve two different resource pools with different objectives; the resource pools of each business line are used to segment data for subsequent search and recommendation; the unified resource pool serves as a source of data statistics.
[0024] Preferably, the data integration logic is non-intrusive to the business logic and will not affect business functions.
[0025] Preferably, the data integration system based on the above methods includes: a data acquisition module, a real-time data writing module, a data search and recommendation module, a data comparison and verification module, and a data push and service module.
[0026] Preferably, the identification method in step six is, but is not limited to, fields, vehicle mall data, and vehicle service data.
[0027] The beneficial effects of this invention are as follows:
[0028] 1. This invention proposes to collect data from various business lines into a collection database through specific steps, achieving data integration and isolation of business logic; it utilizes binlog logs to access Kafka's features to achieve real-time data writing to the central database, ensuring data timeliness and improving the availability of time-sensitive data; the central database data is pushed into the resource pool according to business lines, supporting personalized search and recommendation, and ensuring data consistency through a periodic comparison and verification module; simultaneously, the central database data is pushed to the messaging system, allowing each business line to flexibly listen for and obtain data for personalized services and statistical analysis;
[0029] 2. This invention proposes to achieve real-time data change based on binlog logs from a collection library, replacing the latency of data integration via interfaces; at the same time, it uses multi-threaded processing to handle changing data by listening to Kafka messages, and has a fault tolerance mechanism, comparing and reprocessing inconsistent data through a missing data patching task to ensure data consistency and real-time performance as much as possible. Attached Figure Description
[0030] Figure 1 This is a functional flowchart of the data integration method of the present invention;
[0031] Figure 2 This is a partial left-hand schematic diagram of the functional flow of the data integration method of the present invention;
[0032] Figure 3 This is a partial schematic diagram of the functional flow of the data integration method of the present invention (right side).
[0033] Figure 4 This is a simplified flowchart of the data integration method of the present invention. Detailed Implementation
[0034] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.
[0035] like Figures 1 to 4 As shown in the figure, this embodiment of the invention provides a data integration method that combines MySQL logs with Kafka messages. This data integration method includes the following steps:
[0036] Step 1: Create a data model table for the data collection library; Create a data collection library in a non-intrusive manner to serve as a data transfer station, collect the data models that need to be integrated from various business lines, and create corresponding charts in the data collection library to isolate business data from collected data;
[0037] Step 2: Extract data to the collection library; use synchronization tools to extract data from each business line to the collection library and match it with the charts created in Step 1.
[0038] Step 3: Apply for Kafka message topics; for each chart extracted from the collection database, apply to create the corresponding Kafka message topic for the main table; related sub-tables are accessed using join queries;
[0039] For example, in the car service, partnersku is the main table and goods is the secondary table. Only the topic corresponding to partnersku is requested, which is binlog_breakruleslookup__partnersku, while the goods table does not need to be requested.
[0040] Step 4, Real-time data collection and transmission; The collection library collects binlog logs from the access table in real time and sends the changed data to the topic applied for in Step 3 in JSON format. The middleware features are used to integrate the data into the central database in real time.
[0041] as follows:
[0042]
[0043]
[0044] Step 5: Integrate Kafka messages and write them to the central database; The data integration system listens to each Kafka message, integrates the data processing logic, and writes the data to the central database.
[0045] Step 6: Identify and differentiate data; assign different identifiers to each business line to distinguish data after it is written to the central database;
[0046] For example, add a businessId field, where businessId=1 identifies car mall data, businessId=2 identifies car service data, and so on.
[0047] Step 7: Push task data to resource pools; push data from each business line to different resource pools based on whether or not the data is divided according to the business line. Separate the data sources of search and recommendation functions from statistical analysis data sources to reduce data processing complexity and improve service response time.
[0048] Step 8: Synchronize resource pool data to the search engine; synchronize the data in the resource pool used for search recommendations to the search engine in real time; for example, ElasticSearch.
[0049] Step 9: The search engine module generates an index. The search engine module receives and parses the content in the message queue and generates an efficient index. The field attribute in the index contains the search criteria required for the page.
[0050] Some of the attribute fields are as follows:
[0051]
[0052]
[0053] Step 10: Retrieve data based on conditions; use conditional keywords to search for data.
[0054] For example, the query condition: windshield wipers, would be used to initiate the following request:
[0055]
[0056] Step 11: Periodic comparison and verification; compare the data in the central database and the collection database every 5 minutes; if there is any missing or different data, this task will write the data into the central database to ensure data consistency.
[0057] Step 12: Record changes in the central database data in real time; write the changes in the central database data into the messaging system in real time, and each business line can subscribe to relevant data as needed for data analysis and business advancement.
[0058] The message format is:
[0059]
[0060]
[0061] The data for the business lines comes from their respective vertical data centers; the data for the data integration system comes from the collection library.
[0062] The synchronization tool used in step two is, but is not limited to, CDC.
[0063] Specifically, step five includes: defining a thread pool, the number of which is determined based on the number of CPU cores of different servers, with a value of CPU cores + 1. Multi-threading is used to maximize the utilization of services, improve integration efficiency, and ensure data real-time performance.
[0064] For example, when a message is detected in binlog_breakruleslookup__partnersku, the message object is parsed, the GoodsId is used to query the goods table in the collection database to obtain relevant sub-table information, which is then combined with the message information and written to the central database.
[0065] Specifically, the two methods in step seven are: one is to divide according to businessId and push them into the resource pools of different business lines; the other is to push them into a unified resource pool without distinction.
[0066] The two approaches involve two different resource pools with different objectives; the resource pools of each business line are used to segment data for subsequent search and recommendation; the unified resource pool serves as a source of data statistics.
[0067] The data integration logic is non-intrusive to business logic and will not affect business functions.
[0068] The data integration system based on the above methods includes: a data acquisition module, a real-time data writing module, a data search and recommendation module, a data comparison and verification module, and a data push and service module.
[0069] The identification method in step six is, but is not limited to, fields, vehicle mall data, and vehicle service data.
[0070] This application collects data from various business lines into a central database through specific steps, isolating data integration logic from business logic and ensuring that the data integration process has no impact on business functions. Secondly, by leveraging the feature of connecting database binlog logs to a Kafka message queue, data can be written to the central database in real time, ensuring data real-time performance and improving the availability of time-sensitive data. For example, the time it takes for data changes to travel from the business database to the central database is typically within tens of milliseconds.
[0071] Furthermore, data from the central database is pushed into the resource pool according to business lines, supporting personalized search and recommendation functions. The data in the resource pool is the full data, which facilitates profiling and data analysis.
[0072] In addition, the system's stability and reliability are improved by using a timed comparison and verification module, ensuring data consistency and preventing data loss and duplication.
[0073] Meanwhile, data from the central database is pushed to the messaging system, allowing various business lines to flexibly listen for and retrieve data for personalized services and statistical analysis. Data is uniformly synchronized to the collection database for processing, without interfering with business lines, thus decoupling business and data integration logic.
[0074] Based on the binlog of the data collection library, real-time data change tracking is achieved, replacing the latency caused by integrating data through interfaces. The real-time binlog changes from the database are integrated with Kafka messages, leveraging Kafka's platform advantages and convenience to reduce the difficulty of data integration.
[0075] The system listens for Kafka messages, processes changing data using multi-threading, and updates the central database according to the corresponding logic. During data integration, a fault-tolerance mechanism is implemented: a gap-filling task compares the data in the central database and the collection database every 5 minutes, reprocessing inconsistent data and sending it to the intermediate database to ensure data consistency and real-time performance as much as possible.
[0076] It should be noted that, in this document, relational terms such as "first" and "second" are used only to distinguish one entity or operation from another, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such process, method, article, or apparatus.
[0077] Although embodiments of the invention have been shown and described, it will be understood by those skilled in the art that various changes, modifications, substitutions and alterations can be made to these embodiments without departing from the principles and spirit of the invention, the scope of which is defined by the appended claims and their equivalents.
Claims
1. A data integration method combining MySQL logs with Kafka messages, characterized in that: The data integration method includes the following steps: Step 1: Create a data model table for the data collection library; Create a data collection library in a non-intrusive manner to serve as a data transfer station, collect the data models that need to be integrated from various business lines, and create corresponding charts in the data collection library to isolate business data from collected data; Step 2: Extract data to the collection library; use synchronization tools to extract data from each business line to the collection library and match it with the charts created in Step 1. Step 3: Apply for Kafka message topics; for each chart extracted from the collection database, apply to create the corresponding Kafka message topic for the main table; related sub-tables are accessed using join queries; Step 4, Real-time data collection and transmission; The collection library collects binlog logs from the access table in real time and sends the changed data to the topic applied for in Step 3 in JSON format. The middleware features are used to integrate the data into the central database in real time. Step 5: Integrate Kafka messages and write them to the central database; The data integration system listens to each Kafka message, integrates the data processing logic, and writes the data to the central database. Step 6: Identify and differentiate data; assign different identifiers to each business line to distinguish data after it is written to the central database; Step 7: Push task data to resource pools; push data from each business line to different resource pools based on whether or not the data is divided according to the business line. Separate the data sources of search and recommendation functions from statistical analysis data sources to reduce data processing complexity and improve service response time. Step 8: Synchronize resource pool data to the search engine; synchronize the data in the resource pool used for search recommendations to the search engine in real time. Step 9: The search engine module generates an index. The search engine module receives and parses the content in the message queue and generates an efficient index. The field attribute in the index contains the search criteria required for the page. Step 10: Retrieve data based on conditions; use conditional keywords to search for data. Step 11: Periodic comparison and verification; compare the data in the central database and the collection database every 5 minutes; if there is any missing or different data, this task will write the data into the central database to ensure data consistency. Step 12: Record changes in the central database data in real time; write the changes in the central database data into the messaging system in real time, and each business line can subscribe to relevant data as needed for data analysis and business advancement.
2. The data integration method for combining MySQL logs with Kafka messages according to claim 1, characterized in that: The data for the business lines comes from their respective vertical data centers; the data for the data integration system comes from the collection library.
3. The data integration method for combining MySQL logs with Kafka messages according to claim 1, characterized in that: The synchronization tool used in step two is, but is not limited to, CDC.
4. The data integration method for combining MySQL logs with Kafka messages according to claim 1, characterized in that: Step five specifically includes: defining a thread pool, the number of which is determined based on the number of CPU cores on different servers, with a value of CPU cores + 1. By using multi-threading, the service is maximized, integration efficiency is improved, and data real-time performance is guaranteed.
5. The data integration method for combining MySQL logs with Kafka messages according to claim 1, characterized in that: The two methods in step seven are: one is to divide according to businessId and push them into the resource pools of different business lines; the other is to push them into a unified resource pool without distinction. The two approaches involve two different resource pools with different objectives; the resource pools of each business line are used to segment data for subsequent search and recommendation; the unified resource pool serves as a source of data statistics.
6. The data integration method for combining MySQL logs with Kafka messages according to claim 1, characterized in that: The data integration logic is non-intrusive to business logic and will not affect business functions.
7. The data integration method for combining MySQL logs with Kafka messages according to claim 1, characterized in that: The data integration system based on the above methods includes: a data acquisition module, a real-time data writing module, a data search and recommendation module, a data comparison and verification module, and a data push and service module.
8. The data integration method for combining MySQL logs with Kafka messages according to claim 1, characterized in that: The identification methods in step six are, but are not limited to, fields, vehicle mall data, and vehicle service data.