A Same-Source Binlog Message Routing Method

Abstract

A method for routing homologous Binlog messages belongs to the technical field of database management. The method includes configuring a message source, which includes applications running in different application deployment environments; all applications are connected to the same database through user names associated with the application deployment environments; configuring a database, which routes Binlog messages under different user names to corresponding message queues or topics; configuring a consumer client; the application of the consumer client can automatically monitor the corresponding message queue or topic according to the application deployment environment in which it runs, and consume the Binlog messages in the message queue or topic. This solution ensures that messages can be accurately classified and delivered to the corresponding message queue or topic, effectively avoiding cross-environment message interference and improving the accuracy and efficiency of message processing.

Description

Technical Field

[0001] The invention belongs to the technical field of database management, and in particular relates to a homologous Binlog message routing method. Background Art

[0002] In the actual production application of software technology, it is common to see multiple application deployment environments share a data source (such as using a MySQL database). This approach effectively saves hardware costs and human resources, and eliminates the tediousness of setting up and maintaining a database for each environment. In addition, it also facilitates the implementation of parallel verification of multiple program functions: for example, in the testing phase, two different teams can simultaneously develop two independent functions in the same application, and these functions may partially or completely share the same data set; for another example, in the function deployment phase, new code is often pushed to the pre-release environment first, sharing some or all of the data with the production environment, as an important means of verifying the stability and reliability of the new code.

[0003] Binlog is a log file in the MySQL database that records all changes to the database. It contains detailed information about all modifications to the database, such as insert, update, and delete. Because different application deployment environments share the same data source, this causes mutual interference in Binlog message processing: In the traditional database Binlog message processing mechanism, the Binlog messages generated by application deployment environment A and application deployment environment B are mixed and stored in the same Binlog file, making it impossible to effectively distinguish which specific application deployment environment each message originated from. In the actual production process, we expect to implement such processing logic: the application code corresponding to application deployment environment A is used for processing and consumption to avoid being processed and consumed by the application code corresponding to other application deployment environments, otherwise the message processing results that do not meet expectations will be generated due to inconsistent codes.

[0004] Figure 1 This is a Binlog message transmission diagram for the traditional application deployment scenario with the same data source. Figure 1As shown in the figure, in the traditional application deployment scenario with the same data source, since each application deployment environment (such as development, testing, production, etc.) shares the same database, the Binlog messages generated by all application deployment environments are recorded in the same Binlog file. When using Binlog processing programs such as Canal to parse these Binlog files, there is a significant problem: it is impossible to effectively distinguish which application deployment environment's application generated the specific message body in the Binlog file. This limitation makes programs such as Canal only adopt a "one-size-fits-all" approach when delivering messages to downstream message queues or topics, and deliver all messages to the same message queue or topic. This processing method undoubtedly increases the risk of mutual interference between Binlog messages. For example, the Binlog message of application deployment environment A may be consumed incorrectly by application deployment environment B, which may lead to data confusion or business logic errors. Summary of the invention

[0005] In view of the above-mentioned deficiencies in the prior art, an object of the present invention is to provide a same-source Binlog message routing method to achieve isolation of Binlog messages between the same data source and different application deployment environments.

[0006] In order to achieve the above object, the present invention adopts the following technical solutions.

[0007] A method for routing messages from the same source Binlog.

[0008] Configuring message sources, the message sources include applications running in different application deployment environments; all applications are connected to the same database through user names associated with the application deployment environments to achieve shared access to some or all of the data in the database;

[0009] Configure a database, wherein the database routes Binlog messages under different user names to message queues or topics corresponding to the user names;

[0010] Configure the consumer client. The application of the consumer client can automatically monitor the corresponding message queue or topic according to the application deployment environment in which it runs, and consume the Binlog messages in the message queue or topic.

[0011] A method for routing same-source Binlog messages, comprising the following steps:

[0012] Step S1, Binlog message generation mechanism: each application in the message source is configured with a user name different from other applications, and is connected to the same database to access and operate data. All applications in the application deployment environment can generate Binlog messages with their own user names on the same database server;

[0013] Step S2: Binlog message data parsing, identification and storage process: Use the Binlog database incremental log parsing tool to parse Binlog messages and identify user names; establish and save the mapping relationship between user names and their corresponding database server thread IDs; after the identification is completed, record these identification results, and classify and store Binlog messages according to the identified user names;

[0014] Step S3, differentiated processing and forwarding mechanism of Binlog messages: according to the user name information carried in the Binlog message, the Binlog message is delivered to the message queue or topic corresponding to the user name under the same message queue cluster.

[0015] Further, in step S1, the generated Binlog message includes a database transaction ID and a database server thread ID;

[0016] In step S2, by utilizing the globally ordered nature of the Binlog file, when any Binlog message header is read, the message header containing the database transaction ID and the database server thread ID is cached into the local thread variable ThreadLocal;

[0017] After obtaining the message body and message header, use the database server thread ID to request the database server, query the user name to which the thread belongs, and obtain the user name of each Binlog message.

[0018] Further, in step S2, the query uses the following SQL: SELECT USER,HOST,DB FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ID=#{threadId}; wherein INFORMATION_SCHEMA.PROCESSLIST is the database system table to be queried.

[0019] Furthermore, a homologous Binlog message routing method also includes step S4, a notification result return and anomaly judgment process: returning the parsing result and forwarding result of the Binlog message data to the designated processor and judging whether the parsing result and forwarding result are normal: if normal, continuing to execute subsequent operations, including consuming and submitting messages in the Canal queue; otherwise, starting the exception interception mechanism, recording the exception information in the exception data management table, and at the same time, triggering the exception processor to automatically perform compensation and retry operations according to the preset logic.

[0020] Furthermore, a homologous Binlog message routing method also includes: applying to monitor and consume Binlog messages: modifying the consumer client and adjusting the access mechanism of the application to monitor the message queue or topic; the modified consumer client automatically adds a suffix corresponding to the application deployment environment to the monitoring behavior of the message queue or topic, thereby generating a unique identifier; the consumer application automatically confirms the association between the unique identifier and the user name of the message queue or topic according to the application deployment environment, and then automatically monitors and consumes the Binlog messages in the message queue or topic corresponding to the unique identifier.

[0021] This solution provides an efficient, reliable and low-cost Binlog message routing mechanism, which is specifically reflected in the following aspects:

[0022] 1. Precise isolation of environment identifiers and messages: In the message generation process, the present invention cleverly introduces the environment identifier (i.e., user name) and uses the thread ID in Binlog as the only "fingerprint" to accurately distinguish messages from different environment sources. This innovative design ensures that messages can be accurately classified and delivered to the corresponding message queue or topic, effectively avoiding cross-environment message interference and improving the accuracy and efficiency of message processing.

[0023] 2. Automatic monitoring and adaptive matching of the environment: Consumer applications can automatically monitor the corresponding message queue topic according to the application deployment environment in which they are currently running. This feature ensures that messages only circulate within the same environment, achieves strict isolation of messages, and further reduces confusion and errors in the message processing process.

[0024] 3. Memory caching technology optimizes performance: This solution uses advanced memory caching technology, which not only significantly reduces the pressure of database requests, but also effectively solves the problem of thread ID loss when Canal processes the same transaction message in batches. This technological innovation greatly improves the response speed and stability of the system.

[0025] 4. Low cost, high transparency and easy implementation: This method significantly improves the reliability and maintainability of the system while ensuring efficient software development; its implementation cost is extremely low, it hardly causes any invasive impact on the source code, and it is completely transparent to developers. This feature greatly reduces the difficulty of implementation and promotion of the present invention, making it easy to integrate into various application scenarios and bring real benefits to users. BRIEF DESCRIPTION OF THE DRAWINGS

[0026] Figure 1 This is a Binlog message transmission diagram for the traditional application deployment scenario with the same data source;

[0027] Figure 2 This is the Binlog message passing diagram of this solution. DETAILED DESCRIPTION

[0028] The technical solutions in the embodiments of the present invention will be described clearly and completely below in conjunction with the accompanying drawings in the embodiments of the present invention.

[0029] Terminology explanation:

[0030] TrxId: Database transaction ID is a unique identifier used by the database to identify each transaction. For example, in a banking system, a transfer operation consists of two steps: deducting money from one account and depositing money into another account. These two steps are either successful or failed, so that there is no situation where money is deducted from one account but not deposited into another account. Whenever the database starts a new transaction, a unique database transaction ID is generated, which helps the database track and manage transactions.

[0031] ThreadId: Database server thread ID. A database server thread is a small "work unit" or "task executor" in the database server that is responsible for executing specific tasks. These threads handle requests from different users, such as reading data, writing data, updating data, etc. All operations of the same database transaction are completed by the same thread.

[0032] Topic: A topic is the basic unit used by message queues to store and distribute messages. A topic is like a label or category. For example, if we have an online shopping website, we may have different topics to classify different types of messages: one topic stores information about orders, one topic stores information about user registration, and another topic stores information about product inventory.

[0033] Canal: An open source data synchronization tool. Its main function is to capture and process data changes in Binlog messages in the database and synchronize these changes to other systems.

[0034] Figure 2 This is the Binlog message passing diagram of this solution. Figure 2SQL explanation: SELECT USER,HOST FROMINFORMATION_SCHEMA.PROCESSLIST WHERE ID=1. The purpose of this SQL query is to query the user name (USER) and host (HOST) of the thread with ID 1 from the INFORMATION_SCHEMA.PROCESSLIST table. In short, this query is designed to obtain the user and source host details of a specific thread. The INFORMATION_SCHEMA.PROCESSLIST table is a special MySQL system table that contains information about all threads currently connected to the database. This table has many columns, including user name (USER), user client host name or IP (HOST), database, command type, thread status, thread ID, etc. Every active connection from the database client to the database will be recorded in this table.

[0035] A method for routing same-source Binlog messages, comprising the following steps:

[0036] Step S1: Binlog message generation mechanism: Message sources, including applications running in different application deployment environments; these applications are all connected to the same database. Specifically, these applications can be Java applications or computer applications written in other languages. Each application is configured with a user name different from other applications, such as env_A, env_B, etc.; and is connected to the same database to access and operate data. Therefore, all applications in the application deployment environment can generate Binlog messages with their own user names on the same database server, realizing effective tracking of the source of Binlog messages.

[0037] The generated Binlog message includes the database transaction ID (TrxId), the database server thread ID (ThreadId), and the data changes after the database transaction is committed (log information generated by operations such as UPDATE / DELETE / INSERT).

[0038] The Binlog message body generated by the same transaction may contain multiple changes.

[0039] Step S2: Binlog message data parsing, identification and storage process: Use the Binlog database incremental log parsing tool (such as Canal) to parse the Binlog message. The database incremental log parsing tool first reads and parses the Binlog message through the pseudo-slave node technology. Since there is an upper limit on the number of Binlog messages read by Canal at one time (usually adjustable, generally recommended to be set to a positive integer within 100), the Binlog message read out in this step may not contain the message header (database transaction ID and database server thread ID). In this application, the global ordered nature of the Binlog file is utilized, and when any Binlog message header is read, the message header containing the database transaction ID and the database server thread ID is cached to the local thread variable ThreadLocal, which is convenient for Canal to use in the next batch reading to avoid the situation where some messages do not have the database transaction ID and the database server thread ID.

[0040] After obtaining the message body and message header, use the database server thread ID to request the database server, query the user name (USER) to which the thread belongs, and obtain the user name of each Binlog message (for example, identified as user names such as env_A and env_B). Specifically, the query uses the following SQL: SELECT USER, HOST, DB FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ID=#{threadId}.

[0041] The database system table queried is: INFORMATION_SCHEMA.PROCESSLIST.

[0042] After the identification is completed, the identification results are recorded, and the Binlog messages are classified and stored according to the identified user names.

[0043] The mapping relationship between the user name and its corresponding database server thread ID is established and saved. This is to avoid unnecessary performance loss of the database and instability in the business when making large requests to the database when consuming Binlog messages in large quantities.

[0044] Step S3, differentiated processing and forwarding mechanism of Binlog messages: according to the user name information carried in the Binlog message, the Binlog message is delivered to the message queue or topic corresponding to the user name under the same message queue cluster. For example, for the distributed message queue system RocketMQ or the distributed publish-subscribe message system Kafka, it is delivered to different topics. For example, the Binlog message generated by the application with the user name env_A is delivered to the message queue or topic named Binlog_env_A, and the same applies to the applications in the remaining message sources. In this way, application servers in different application deployment environments access the same message queue cluster, but listen to different message queues or topics, so that the application server with the user name env_A only consumes the Binlog messages generated by the same application deployment environment, thereby effectively avoiding the Binlog messages generated for other application deployment environments, and realizing the isolation and independence between different application deployment environments.

[0045] Step S4, notification result return and exception judgment process: return the parsing result and forwarding result of Binlog message data to the specified processor and judge whether the parsing result and forwarding result are normal: if normal, continue to perform subsequent operations, including consuming and submitting messages in the Canal queue, otherwise, start the exception interception mechanism, record the exception information in the exception data management table for subsequent analysis and tracking, and at the same time, trigger the exception processor to automatically perform compensation and retry operations according to the preset logic to ensure the stability of the system and the integrity of the data to the greatest extent. Transaction compensation and retry operations, including rollback and retry, are conventional operations in this field. This solution adopts conventional transaction compensation and retry operations, and the specific operation process will not be repeated.

[0046] Step S5, the application monitors and consumes Binlog messages: For traditional consumer clients, the accessed topics are the same, such as Binlog. In this solution, the consumer client is modified to adjust the access mechanism of the application monitoring message queue or topic. The modified consumer client (Rocket MQ) automatically adds a suffix corresponding to the application deployment environment to the monitoring behavior of the message queue or topic, thereby generating a unique identifier. For example, if the application deployment environment is A, the suffix is ​​_env_A, thereby generating a unique identifier of the message queue or topic such as Binlog_env_A. The consumer application automatically confirms the unique identifier of the message queue or topic according to the application deployment environment in which it is located, and then automatically monitors and consumes the Binlog message corresponding to the unique identifier of the message queue or topic. In this way, the application implements message isolation with the same set of code, the same data source but different application deployment environments. For example, the monitoring Topic configured in the Java business code only needs to fill in Binlog uniformly to achieve different environments monitoring different Topics, which greatly simplifies the system implementation cost and maintenance cost.

[0047] In this solution, step S4 is an abnormality compensation mechanism, which is not necessary. In the absence of abnormalities, the effect of this solution can still be achieved. At the same time, there is no specific order relationship between step S4 and step S5. Step S4 can be performed first and then step S5, or step S4 and step S5 can be performed simultaneously.

[0048] Below Figure 2 Take as an example to illustrate this solution. Figure 2 This is the Binlog message transmission diagram of this solution. Figure 1 As shown, this example includes the following steps:

[0049] In the first step, the message source classifies the applications, groups the applications in the same application deployment environment into one category, and then names the applications. For example, the user name of the application in the application deployment environment A during the development phase is env_A; the user name of the application in the application deployment environment B during the test phase is env_B. Then, all applications generate Binlog messages with their respective user names on the same database server (using the MySQL database).

[0050] The second step is to enable Binlog logging on the database server to record all statements that change data in the Binlog log; the Binlog log is a sequence of files with a base name and a numeric extension.

[0051] The third step is to use Canal to pull and parse Binlog log messages. When any Binlog message header is read, the message header containing the database transaction ID and database server thread ID is cached to the local thread variable ThreadLocal.

[0052] Step 4: Use the database server thread ID to request the database server, query the user name (USER) to which the thread belongs, and obtain the user name of each Binlog message (for example, the user name identified as env_A, env_B, etc.). The query uses the following SQL: SELECT USER,HOST,DB FROM INFORMATION_SCHEMA.PROCESSLIST WHEREID=#{threadId}. The database system table queried is: INFORMATION_SCHEMA.PROCESSLIST.

[0053] The fifth step is to deliver the Binlog message to the message queue or topic corresponding to the user name in the same message queue cluster according to the user name information carried in the Binlog message.

[0054] In the sixth step, the message consumer application automatically adds a suffix corresponding to the application deployment environment to the monitoring behavior of the message queue or topic, thereby generating a unique identifier; the consumer application automatically confirms the association between the unique identifier and the user name of the message queue or topic based on the application deployment environment, and then automatically monitors and consumes the Binlog messages in the message queue or topic corresponding to the unique identifier.

[0055] This solution has the following advantages:

[0056] 1. Diverse application deployment environments and unified data sources: In different application deployment environments, the application server connects to the database through a user name closely associated with the environment name, thereby achieving shared access to part or all of the data.

[0057] 2. Environment identification and message isolation based on Binlog thread ID: By extracting the thread ID in the Binlog message body, the system can map the specific environment (that is, the user name) that generated the message and isolate the message accordingly. Messages under different user names will be accurately routed to their corresponding message queues or topics.

[0058] 3. Automatic matching of consumer applications and message queue topics: Consumer applications can automatically monitor the corresponding message queue topics according to the application deployment environment in which they run, ensuring that producers and consumers in the same environment work together, achieving environmental isolation of messages, and effectively avoiding mutual interference between messages in different environments.

[0059] 4. Memory caching technology optimizes database performance and solves the problem of thread ID loss: This solution uses memory caching technology to reduce the request burden on the database, and specifically uses the threadLocal memory caching mechanism to solve the problem of thread ID loss encountered by Canal when batch processing all messages in the same transaction, further improving the stability and efficiency of the system.

[0060] The present invention is not limited by the above embodiments. The above embodiments and descriptions are only for illustrating the principles of the present invention. Without departing from the spirit and scope of the present invention, the present invention may be subject to various changes and improvements, and these changes and improvements shall all fall within the scope of the present invention to be protected.

Claims

1. A method for routing homologous Binlog messages, characterized in that: Configuring message sources, the message sources include applications running in different application deployment environments; all applications are connected to the same database through user names associated with the application deployment environments to achieve shared access to some or all of the data in the database; Configure a database, wherein the database routes Binlog messages under different user names to message queues or topics corresponding to the user names; Configure the consumer client. The application of the consumer client can automatically monitor the corresponding message queue or topic according to the application deployment environment in which it runs, and consume the Binlog messages in the message queue or topic. The following steps are involved: Step S1, Binlog message generation mechanism: each application in the message source is configured with a user name different from other applications, and is connected to the same database to access and operate data. All applications in the application deployment environment can generate Binlog messages with their own user names on the same database server; In step S1, the generated Binlog message includes a database transaction ID and a database server thread ID; Step S2: Binlog message data parsing, identification and storage process: Use the Binlog database incremental log parsing tool to parse Binlog messages and identify user names; establish and save the mapping relationship between user names and their corresponding database server thread IDs; after the identification is completed, record these identification results, and classify and store Binlog messages according to the identified user names; Step S3, differentiated processing and forwarding mechanism of Binlog messages: according to the user name information carried in the Binlog message, the Binlog message is delivered to the message queue or topic corresponding to the user name under the same message queue cluster; Applications monitor and consume Binlog messages: Transform the consumer client and adjust the access mechanism for the application to monitor the message queue or topic. The transformed consumer client automatically adds a suffix corresponding to the application deployment environment to the monitoring behavior of the message queue or topic, thereby generating a unique identifier. The consumer application automatically confirms the association between the unique identifier and the user name of the message queue or topic based on the application deployment environment, and then automatically monitors and consumes the Binlog messages in the message queue or topic corresponding to the unique identifier.

2. A method for routing homologous Binlog messages according to claim 1, characterized in that: In step S2, by utilizing the globally ordered nature of the Binlog file, when any Binlog message header is read, the message header containing the database transaction ID and the database server thread ID is cached into the local thread variable ThreadLocal; After obtaining the message body and message header, use the database server thread ID to request the database server, query the user name to which the thread belongs, and obtain the user name of each Binlog message.

3. According to a homologous Binlog message routing method according to claim 2, it is characterized in that: The following steps are involved: In step S2, the query uses the following SQL: SELECT USER,HOST,DB FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ID=#{threadId}; where INFORMATION_SCHEMA.PROCESSLIST is the database system table to be queried.

4. According to a homologous Binlog message routing method according to claim 1, it is characterized in that: It also includes step S4, notification result return and exception judgment process: the parsing result and forwarding result of the Binlog message data are returned to the specified processor and it is judged whether the parsing result and forwarding result are normal: if normal, continue to execute subsequent operations, including consuming and submitting messages in the Canal queue; otherwise, start the exception interception mechanism, record the exception information in the exception data management table, and at the same time, trigger the exception processor to automatically perform compensation and retry operations according to the preset logic.

Images