A MySQL database full-flow mirroring system and method

Abstract

The present invention discloses a MySQL database full-flow mirroring system and method, comprising an online server module, a target server module, an interception server module and a terminal display module. The present invention deploys a tcpcopy tool on the online server module to achieve real-time copying of online traffic, effectively solving the problem of timeliness. At the same time, there is no intermediate filtering, and all requests (including DML, DDL, DCL, etc.) of the online production library will be delivered, making the entire system timely and comprehensive. By arranging oneproxy in the target server module and the terminal display module, it is possible to analyze from multiple dimensions such as the requesting host, the requesting user, and the library name requested for access. The entire process is concise and clear. Compared with the slow log files of the existing system, which are saved in text files with a fixed format, it is difficult to intuitively find problem points by directly viewing the slow logs, thereby greatly improving the visualization of the entire system.

Description

Technical Field

[0001] The present invention relates to the field of MySQL database monitoring, operation and maintenance management, and specifically to a MySQL database full-flow mirroring system and method. Background Art

[0002] Currently, the industry typically analyzes system bottlenecks by analyzing MySQL database slow logs. This approach has three drawbacks: First, only SQL statements that exceed the long_query_time parameter value (default: 10 seconds) are recorded in the slow log; second, the slow log only records QUERY statements, not other SQL changes; and third, the slow log file records SQL execution details in text format, requiring visualization via other platforms. Given these three points, analyzing system bottlenecks through slow logs is time-consuming, labor-intensive, and ineffective. To address these issues, we propose a full-flow mirroring system for MySQL databases. Summary of the Invention

[0003] The object of the present invention is to provide a MySQL database full-flow mirroring system and method to solve the problems raised in the above background technology.

[0004] To achieve the above object, the present invention provides the following technical solutions:

[0005] In a first aspect, the present application provides a MySQL database full-flow mirroring system, comprising: an online server module, a target server module, an interception server module, and a terminal display module. A web application in the online server module accesses an online production database and executes SQL queries. Request information for accessing online production data is copied and transmitted to a designated port of the target server module via a tcpcopy tool in the online server module.

[0006] The Docker container in the target server module receives the request information for accessing online production data, executes the corresponding SQL, and returns the execution result to the interception server address modified by tcpcopy;

[0007] The interception server module intercepts and discards the execution result in the target server module, and copies the IP header of the return result through the intercept process in the interception server module;

[0008] The terminal display module accesses the oneproxy port of the corresponding target server module, and the front-end page provided by oneproxy displays the traffic situation of its online library.

[0009] Preferably, the target server module adopts docker containerization technology to package an image of a linux environment that deploys components such as oneproxy and mysql, and then when needed, only a separate container needs to be started and the corresponding oneproxy and mysql ports are exposed.

[0010] Preferably, the request information for accessing online production data copied by TCPCopy of the online server module points to the target end container of the target server module exposed to the port of the host, which facilitates the deployment of multiple full-flow mirroring systems on a single target server module.

[0011] Preferably, the interception server module uses an Assistant node as the IP layer to intercept and discard the execution result in the target server module.

[0012] Preferably, the online server module, target server module, interception server module and terminal display module complete the entire system deployment through scripts, and the script deployment steps are as follows:

[0013] Step 1: Install the Salt client tool on the online server module, target server module, and interception server module, and complete the connection configuration between the Salt server and client.

[0014] Step 2. Create a container on the target server module and expose the corresponding oneproxy and MySQL ports;

[0015] Step 3: Export the data dictionary of the online database and import it into the MySQL database of the target server module;

[0016] Step 4. Configure the oneproxy user list of the target server module and start oneproxy;

[0017] Step 5: Enable route forwarding on the target server module;

[0018] Step 6: Deploy the intercept tool on the interception server module and start the intercept tool;

[0019] Step 7: Deploy the TCPCopy tool on the online server module, configure the user, and start the TCPCopy tool.

[0020] Step 8. Check the traffic mirroring status through the oneproxy front-end of the terminal display module.

[0021] The second aspect of this application provides a MySQL database full traffic mirroring method, the specific implementation steps are as follows:

[0022] Step S1: The web application in the online server module accesses the online production database and executes SQL queries;

[0023] Step S2: The online server module modifies the destination and source addresses of the request information for accessing online production data in step S1 through the tcpcopy tool, and sends it to the designated port of the target server module;

[0024] Step S3: The docker container in the target server module receives the package in step S2, processes the corresponding SQL, and returns the result;

[0025] Step S4: The interception server module intercepts and discards the result returned in step S3, and copies the IP header of the returned result through the intercept process;

[0026] Step S5: The online server module receives the IP header in step S4 through the tcpcopy tool;

[0027] Step S6: The terminal display module accesses the oneproxy port of the corresponding target server module, so that the terminal display module displays the traffic situation of its online library.

[0028] Compared with the prior art, the present invention has the following beneficial effects: by deploying the tcpcopy tool on the online server module, the present invention can realize real-time copying of online traffic, which well solves the problem of timeliness. At the same time, there is no filtering in the middle, and all requests of the online production library (including DML, DDL, DCL, etc.) will be delivered, so that the entire system has timeliness and comprehensiveness; by setting oneproxy in the target server module and the terminal display module, it can be analyzed from multiple dimensions such as the requesting host, the requesting user, and the name of the library requested to be accessed. The whole process is concise, clear and understandable. Compared with the slow log files of the existing system, since they are saved in a fixed format text file, it is difficult to intuitively find the problem points by directly viewing the slow logs, and it is even impossible to achieve standardized display, thereby greatly improving the visualization of the entire system. BRIEF DESCRIPTION OF THE DRAWINGS

[0029] The accompanying drawings, which constitute part of this application, are intended to provide a further understanding of this application. The exemplary embodiments and descriptions of this application are intended to explain this application and do not constitute an improper limitation on this application. In the accompanying drawings:

[0030] Figure 1 It is a system structure diagram of the present invention;

[0031] Figure 2 It is a flowchart of the workflow of the present invention.

[0032] Legend:

[0033] 1. Online server module; 2. Target server module; 3. Interception server module; 4. Terminal display module. DETAILED DESCRIPTION

[0034] The present invention provides a MySQL database full-flow mirroring system and method. To clarify the objectives, technical solutions, and effects of the present invention, the present invention is further described below with reference to the accompanying drawings and examples. It should be understood that the specific embodiments described herein are intended only to illustrate the present invention and are not intended to limit the present invention.

[0035] It should be noted that the terms "first," "second," and the like in the specification and claims of the present invention and the accompanying drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the terms used in this manner are interchangeable under appropriate circumstances. In addition, the terms "including," "having," and any variations thereof are intended to cover non-exclusive inclusions. For example, a process, method, system, product, or apparatus that includes a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units that are not explicitly listed or that are inherent to these processes, methods, products, or apparatuses.

[0036] Example:

[0037] See also Figure 1-2 The present invention provides a technical solution: a MySQL database full-flow mirroring system and method, including an online server module 1, a target server module 2, an interception server module 3 and a terminal display module 4. The web application in the online server module 1 accesses the online production database and executes SQL queries, and the request information for accessing the online production data is copied and transmitted to the designated port of the target server module 2 through the tcpcopy tool in the online server module 1. The tool using tcpcopy can copy the online traffic in real time, which solves the timeliness problem very well. At the same time, there is no filtering in the middle, and all requests from the online production library will be delivered. It is the most comprehensive mirror and can ensure the accuracy of the entire system; the docker container in the target server module 2 receives the request information for accessing the online production data, and executes the corresponding SQL, and returns the execution result to the interception server address modified by tcpcopy; the interception server module 3 intercepts and discards the execution result in the target server module 2, and copies the IP header of the return result through the intercept process in the interception server module 3, and the IP header is sent to the tcpcopy tool of the online server module 1;

[0038] The terminal display module 4 accesses the oneproxy port of the corresponding target server module 2, and the front-end page provided by oneproxy displays the traffic situation of its online library. The system completes the standardized display of traffic through the oneproxy component. This improvement mainly solves the visualization problem, making it possible to monitor the online database in a timely and efficient manner. Oneprxy has its own load balancing, visual interface and other functions. The purpose and source address of the request information for accessing online production data are modified and passed to the target end oneproxy through the tcpcopy component, so fast display can be achieved. The slow log files generated by the database are saved in a fixed format text file, so it is difficult to intuitively find the problem points by directly viewing the slow logs, and it is even more impossible to achieve standardized display. This problem can be easily solved through oneproxy. It can analyze from multiple dimensions such as the requesting host, the requesting user, and the name of the library requested to be accessed. The whole process is concise, clear and understandable.

[0039] The existing full-flow mirroring system requires a target server module and an interception server. If a separate target server module and interception server are configured for each environment to be mirrored, this will cause a great waste of resources. Therefore, the target server module 2 of this system adopts docker containerization technology to package an image of a Linux environment that deploys components such as oneproxy and mysql. Then, when needed, you only need to start a separate container and expose the corresponding oneproxy and mysql ports; the request information for accessing the online production data copied by tcpcopy of the online server module 1 points to the target end container of the target server module 2 exposed to the port of the host, which facilitates the deployment of multiple full-flow mirroring systems with a single target server module.

[0040] The oneproxy component provides standardized traffic display. This improvement primarily addresses visualization issues, enabling timely and efficient monitoring of online databases. oneproxy includes built-in load balancing and a visualization interface. This system modifies the destination and source addresses of socket packets and transmits them to the target oneproxy through the tcpcopy component, enabling rapid display.

[0041] In order to solve the problem of large deployment workload and long deployment time, we rely on the salt tool to realize the reasonable arrangement of deployment tasks. Through the online server module 1, target server module 2, interception server module 3 and terminal display module 4, the entire system deployment is completed through the script. The script deployment steps are as follows:

[0042] Step 1: Install the Salt client tool on the online server module 1, target server module 2, and interception server module 3, and complete the connection configuration between the Salt server and client.

[0043] Step 2. Create a container on the target server module 2 and expose the corresponding oneproxy and MySQL ports;

[0044] Step 3: Export the data dictionary of the online database and import it into the MySQL database of the target server module 2;

[0045] Step 4. Configure the user list of oneproxy of target server module 2 and start oneproxy;

[0046] Step 5: Enable route forwarding on target server module 2.

[0047] Step 6: Deploy the intercept tool on the interception server module 3 and start the intercept tool;

[0048] Step 7: Deploy the TCPCopy tool on the online server module 1, configure the user, and start the TCPCopy tool.

[0049] Step 8: Check the traffic mirroring status through the oneproxy front-end of terminal display module 4.

[0050] A MySQL database full traffic mirroring method, the specific implementation steps are as follows:

[0051] Step S1: The web application in the online server module 1 accesses the online production database and executes SQL queries;

[0052] Step S2: The online server module 1 modifies the destination and source addresses of the request information for accessing online production data in step S1 through the tcpcopy tool, and sends it to the designated port of the target server module 2;

[0053] Step S3: The docker container in the target server module 2 receives the package in step S2, processes the corresponding SQL, and returns the result;

[0054] Step S4: The interception server module 3 intercepts and discards the result returned in step 3, and copies the IP header of the returned result through the intercept process;

[0055] Step S5: The online server module 1 receives the IP header in step S4 through the tcpcopy tool;

[0056] Step S6: The terminal display module 4 accesses the oneproxy port of the corresponding target server module 2, so that the terminal display module 4 displays the traffic situation of its online library.

[0057] After deploying the full-flow mirroring system through scripts, you can view all database requests from the online production library in real time through OneProxy, thereby promptly and efficiently discovering production problems. This invention forwards in real time, achieves near-real-world results, and has minimal impact on the online system.

[0058] The present invention deploys the tcpcopy tool on the online server module 1, thereby realizing real-time copying of online traffic, which well solves the problem of timeliness. At the same time, there is no filtering in the middle, and all requests of the online production library (including DML, DDL, DCL, etc.) will be delivered, so that the entire system has timeliness and comprehensiveness; by setting oneproxy in the target server module 2 and the terminal display module 4, it can be analyzed from multiple dimensions such as the requesting host, the requesting user, and the name of the library requested to be accessed. The whole process is concise and clear. Compared with the slow log files of the existing system, since they are saved in a fixed format text file, it is difficult to intuitively find problem points by directly viewing the slow logs, and it is even impossible to achieve standardized display, thereby greatly improving the visualization of the entire system.

[0059] While the specific embodiments of the present invention have been described in detail above, these are merely exemplary and the present invention is not limited thereto. For those skilled in the art, any equivalent modifications and substitutions to the present invention are also within the scope of the present invention. Therefore, any equivalent changes and modifications made without departing from the spirit and scope of the present invention are intended to be encompassed within the scope of the present invention.

Claims

1. A MySQL database full-flow mirroring system, comprising an online server module (1), a target server module (2), an interception server module (3) and a terminal display module (4), characterized in that: The web application in the online server module (1) accesses the online production database and executes SQL queries, and the request information for accessing the online production data is copied and transmitted to the designated port of the target server module (2) through the tcpcopy tool in the online server module (1); The target server module (2) uses Docker containerization technology to package and deploy a Linux environment image of oneproxy and MySQL components, and starts a separate container and exposes the corresponding oneproxy and MySQL ports; the Docker container in the target server module (2) receives the request information for accessing online production data, executes the corresponding SQL, and returns the execution result to the interception server address modified by tcpcopy; The interception server module (3) intercepts and discards the execution result in the target server module (2), and copies the IP header of the return result through the intercept process in the interception server module (3); The terminal display module (4) accesses the oneproxy port of the corresponding target server module (2), and the front-end page provided by oneproxy displays the traffic situation of its online library.

2. A MySQL database full-flow mirroring system according to claim 1, characterized in that: The request information for accessing the online production data copied by the TCP copy of the online server module (1) is directed to the target end container of the target server module (2) exposed to the port of the host.

3. A MySQL database full-flow mirroring system according to claim 1, characterized in that: The interception server module (3) uses the Assistant node as the IP layer to intercept and discard the execution result in the target server module (2).

4. A MySQL database full-flow mirroring system according to claim 1, characterized in that: The online server module (1), the target server module (2), the interception server module (3) and the terminal display module (4) complete the entire system deployment through scripts, and the script deployment steps are as follows: Step 1: Install the Salt client tool on the online server module (1), target server module (2), and interception server module (3), and complete the connection configuration between the Salt server and client. Step 2. Create a container on the target server module (2) and expose the corresponding oneproxy and MySQL ports; Step 3: Export the data dictionary of the online database and import it into the MySQL database of the target server module (2); Step 4. Configure the user list of oneproxy of the target server module (2) and start oneproxy; Step 5: Enable route forwarding on the target server module (2); Step 6: Deploy the intercept tool on the interception server module (3) and start the intercept tool; Step 7: Deploy the tcpcopy tool on the online server module (1), configure the user, and start the tcpcopy tool; Step 8. Check the traffic mirroring status through the oneproxy front end of the terminal display module (4).

5. A MySQL database full-flow mirroring method, applied to the MySQL database full-flow mirroring system according to claim 1, characterized in that: The specific implementation steps are as follows: Step S1: First, access the online production database through the web application in the online server module (1) and execute SQL query; Step S2, the online server module (1) modifies the destination and source addresses of the request information for accessing online production data in step S1 through the tcpcopy tool, and sends it to the designated port of the target server module (2); Step S3: The docker container in the target server module (2) receives the data in step S2, processes the corresponding SQL, and returns the result; Step S4, the interception server module (3) intercepts and discards the result returned in step S3, and copies the IP header of the returned result through the intercept process; Step S5, the online server module (1) receives the IP header in step S4 through the tcpcopy tool; Step S6: The terminal display module (4) accesses the oneproxy port of the corresponding target server module (2) so that the terminal display module (4) displays the traffic situation of its online library.

Images