Method and device for automated operation and maintenance of a MySQL database
By automating the generation of parameter lists and batch scripts, and providing parallel execution and forced interruption functions, the system solves the problems of low efficiency and insufficient flexibility in batch operation and maintenance of MySQL database management platforms, achieving efficient and flexible automated operation and maintenance.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- INDUSTRIAL AND COMMERCIAL BANK OF CHINA
- Filing Date
- 2023-02-28
- Publication Date
- 2026-05-19
AI Technical Summary
Existing MySQL database management platforms suffer from problems during batch operation and maintenance, such as the large workload and error-prone nature of manually obtaining parameter lists, cumbersome script generation, low execution efficiency, and inability to flexibly terminate batch scripts.
By using database ledger information and parameterized lists, the system can automatically generate parameter lists and batch scripts, and call the DMP interface for parallel execution. It also adds parallel task location control functions, including forced interruption.
It improves the automation level of MySQL database operation and maintenance, realizes high concurrency and high efficiency operation and maintenance methods, reduces manual steps, lowers the error rate, and enhances the flexibility of task control.
Smart Images
Figure CN116340286B_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the field of database operation and maintenance technology, and more particularly to an automated operation and maintenance method and apparatus for a MySQL database. Background Technology
[0002] This section is intended to provide background or context for the embodiments of the invention set forth in the claims. The description herein is not an admission that it is prior art simply because it is included in this section.
[0003] Currently, large commercial banks typically use a MySQL Management Platform (DMP) for the overall management and maintenance of their MySQL databases. In daily use, a web-based visual single-point operation is employed for single nodes, while the DMP is used to collect distribution information for multiple nodes. Batch scripts are prepared for different functions within the DMP, and during implementation, the "batch processing" function is used to execute batch JSON scripts to achieve parallel operations.
[0004] In the process of batch database operation and maintenance, the main steps include obtaining parameter lists, generating batch scripts for each function, and executing batch scripts. The existing solution is mainly divided into the following three steps:
[0005] Obtaining the parameters required for the script: Manually obtain the information of each DMP ledger, and according to different DMPs and different functions, create a list of parameters required by the main and backup scripts in an Excel sheet.
[0006] Batch script generation: Use Excel macro commands to generate parallel script content sheet by sheet;
[0007] Batch script execution: Manually log in to DMP, use the "Batch Processing" function, copy the script content to the function page, fill in the concurrency level, and then execute. The page provides a batch processing progress display. The script execution cannot be terminated before all scripts have been executed.
[0008] However, existing technologies have at least the following drawbacks during implementation:
[0009] Since different functions require different parameters, and DMPs typically manage more than 800 database nodes on a single platform, manually obtaining the parameter list for each function is labor-intensive and prone to errors.
[0010] Using Excel macro commands to generate scripts requires preparing separate lists for different parameters needed for each function. The manual steps are tedious, and the generated scripts are difficult to identify when there are abnormal data formats.
[0011] Executing the script requires logging into each DMP individually and executing the relevant scripts for each node managed by that DMP. When there are many nodes and DMPs, manual implementation is inefficient. In addition, the implementation results are not automatically collected and saved.
[0012] In DMP, when executing batch scripts that result in errors or need to be stopped, there is no forced termination mechanism. The only option is to wait for all batch scripts to finish executing, which makes the process inflexible.
[0013] In summary, there is an urgent need for a technical solution that can overcome the above-mentioned shortcomings and improve the efficiency and processing methods of MySQL database operation and maintenance. Summary of the Invention
[0014] To address the problems existing in the prior art, this invention proposes an automated operation and maintenance method and apparatus for MySQL databases. Based on database ledger information, parameterized lists, and functional JSON templates, this invention automates the generation of parameter lists and batch scripts. It also enables parallel processing of batch scripts by calling the DMP interface and adds a function for controlling the location of parallel tasks (e.g., forced interruption), effectively improving the efficiency and flexibility of MySQL database operation and maintenance.
[0015] In a first aspect of this invention, an automated operation and maintenance method for a MySQL database is proposed, comprising:
[0016] Based on the server IP information and ledger data, a list of parameters for the MySQL database management platform is obtained;
[0017] The JSON template string is determined based on the parameter list, and a batch script is generated based on the parameter values corresponding to the parameter list and the JSON template string.
[0018] Deploy the batch script on the server corresponding to the MySQL database management platform;
[0019] The batch script is parsed to obtain a list of tasks to be executed. Concurrent tasks in the task list are executed according to their concurrency level, and a unique identifier for each concurrent task is generated.
[0020] The concurrent task is located based on the unique identifier, and the concurrent task is controlled.
[0021] In a second aspect of the present invention, an automated operation and maintenance device for a MySQL database is provided, comprising:
[0022] The parameter list generation module is used to generate a parameter list for the MySQL database management platform based on server IP information and ledger data.
[0023] The batch script generation module is used to determine the JSON template string based on the parameter list, and generate batch scripts based on the parameter values corresponding to the parameter list and the JSON template string.
[0024] The script deployment module is used to deploy the batch scripts on the server corresponding to the MySQL database management platform.
[0025] The task execution module is used to parse the batch script to obtain a list of tasks to be executed, execute concurrent tasks in the task list according to the concurrency level, and generate a unique identifier for the concurrent task;
[0026] The task control module is used to locate the concurrent task based on the unique identifier and control the concurrent task.
[0027] In a third aspect of the present invention, a computer device is provided, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement an automated operation and maintenance method for a MySQL database.
[0028] In a fourth aspect of the present invention, a computer-readable storage medium is provided, the computer-readable storage medium storing a computer program, which, when executed by a processor, implements an automated operation and maintenance method for a MySQL database.
[0029] In a fifth aspect of the present invention, a computer program product is provided, the computer program product comprising a computer program that, when executed by a processor, implements an automated operation and maintenance method for a MySQL database.
[0030] The automated operation and maintenance method and apparatus for MySQL databases proposed in this invention obtains a parameter list for the MySQL database management platform based on server IP information and ledger data; determines a JSON template string based on the parameter list; generates batch scripts based on the parameter values corresponding to the parameter list and the JSON template string; deploys the batch scripts on the server corresponding to the MySQL database management platform; parses the batch scripts to obtain a list of tasks to be executed; executes concurrent tasks in the task list according to concurrency level and generates a unique identifier for each concurrent task; locates the concurrent task based on the unique identifier and controls the concurrent task. The overall solution can effectively improve the degree of automated operation and maintenance, achieve high concurrency and high efficiency automated operation and maintenance, reduce manual steps, lower the error rate, and also forcefully control the executing tasks, improving the flexibility of task control. The automated operation and maintenance method and apparatus for MySQL databases proposed in this invention provide strong technical support for database operation and maintenance. Attached Figure Description
[0031] To more clearly illustrate the technical solutions of the embodiments of this application, the drawings used in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.
[0032] Figure 1 This is a schematic diagram of an automated operation and maintenance method for a MySQL database according to an embodiment of the present invention.
[0033] Figure 2 This is a schematic diagram of the process for generating a parameter list according to a specific embodiment of the present invention.
[0034] Figure 3 This is a schematic diagram of the process for generating batch scripts according to a specific embodiment of the present invention.
[0035] Figure 4 This is a flowchart illustrating the task execution process according to a specific embodiment of the present invention.
[0036] Figure 5 This is a schematic diagram of the architecture of an automated operation and maintenance device for a MySQL database according to an embodiment of the present invention.
[0037] Figure 6 This is a schematic diagram of a computer device structure according to an embodiment of the present invention.
[0038] Figure 7 This is a schematic diagram illustrating the tool function assistance of a specific embodiment of the present invention.
[0039] Figure 8 This is a schematic diagram illustrating the parameter list, functional parameters, and default value help of a specific embodiment of the present invention. Detailed Implementation
[0040] The principles and spirit of the invention will now be described with reference to several exemplary embodiments. It should be understood that these embodiments are given merely to enable those skilled in the art to better understand and implement the invention, and are not intended to limit the scope of the invention in any way. Rather, these embodiments are provided to make this disclosure more thorough and complete, and to fully convey the scope of this disclosure to those skilled in the art.
[0041] Those skilled in the art will recognize that embodiments of the present invention can be implemented as a system, apparatus, device, method, or computer program product. Therefore, this disclosure can be specifically implemented in the following forms: entirely hardware, entirely software (including firmware, resident software, microcode, etc.), or a combination of hardware and software.
[0042] According to an embodiment of the present invention, an automated operation and maintenance method and apparatus for a MySQL database are proposed, relating to the field of database operation and maintenance technology.
[0043] In the embodiments of the present invention, the following terms need to be explained:
[0044] DMP: MySQL Database Management Platform; The MySQL Database Management Platform is used to manage MySQL databases and provides functions such as high availability, daily operation and maintenance, performance monitoring, and emergency response for the managed databases. It also provides a MySQL ledger export function.
[0045] SSER: Cloud Management Platform; the Cloud Management Platform can call some interfaces of DMP to obtain ledger information from multiple platforms and update it once a day.
[0046] The principles and spirit of the present invention will be explained in detail below with reference to several representative embodiments.
[0047] Figure 1 This is a schematic diagram of an automated operation and maintenance method for a MySQL database according to an embodiment of the present invention. Figure 1 As shown, the method includes:
[0048] S101. Based on the server IP information and ledger data, obtain the parameter list of the MySQL database management platform;
[0049] S102, determine the JSON template string according to the parameter list, and generate a batch script according to the parameter values corresponding to the parameter list and the JSON template string;
[0050] S103, Deploy the batch script on the server corresponding to the MySQL database management platform;
[0051] S104, parse the batch script to obtain a list of tasks to be executed, execute the concurrent tasks in the task list according to the concurrency level, and generate a unique identifier for the concurrent task;
[0052] S105, locate the concurrent task according to the unique identifier, and control the concurrent task.
[0053] This invention can effectively improve the level of automated operation and maintenance, realize a high-concurrency and high-efficiency automated operation and maintenance method, reduce manual steps, reduce error rate, and can force control of tasks in execution (e.g., forced interruption), improving the flexibility of task control. This method provides strong technical support for database operation and maintenance.
[0054] To provide a clearer explanation of the automated operation and maintenance methods for MySQL databases described above, each step will be explained in detail below.
[0055] In S101, based on the server IP information and ledger data, a parameter list for the MySQL database management platform is obtained. The specific process is as follows:
[0056] Parse the ledger data. If the ledger file directory and ledger files exist, and the file format and file permissions are checked and passed, read the file content.
[0057] Based on the server IP information, obtain the parameter values corresponding to each MySQL database management platform in the file content, generate a full parameter list, and a parameter list corresponding to each MySQL database management platform.
[0058] The ledger data includes DMP ledger information and / or SSER ledger information; wherein...
[0059] The DMP ledger information is the ledger information of the MySQL database management platform, and it is updated in real time.
[0060] The SSER ledger information is the full ledger information in the MySQL database of the cloud management platform, and it is updated once a day.
[0061] In practical application scenarios, the system mainly obtains the parameter list required for batch script generation based on the demand IP, MySQL database management platform (DMP) ledger information, and full ledger information of MySQL database in cloud management platform (SSER), and generates parameter lists in units of DMP based on the DMP information.
[0062] Considering the timeliness of ledger information (SSER ledger information is updated daily; DMP ledger information is updated in real time, with the highest timeliness), the parameter list can be generated based solely on DMP ledger information, solely on SSER ledger information, or both. However, if both ledger information are provided, the DMP ledger information should be the primary source, with the SSER ledger information serving as a secondary source. During the list generation process, the log will display the program's execution progress.
[0063] refer to Figure 2 This is a schematic diagram illustrating the process of generating a parameter list according to a specific embodiment of the present invention. Figure 2 As shown, the specific process is as follows:
[0064] S201, Obtain IP Information List.
[0065] S202, determine whether to provide SSER ledger information. If yes, proceed to S203; otherwise, proceed to S211.
[0066] S203, Parse SSER ledger information.
[0067] S204, determine whether the SSER ledger file directory has been read. If yes, execute S205. If no, prompt that the file does not exist.
[0068] S205 checks the file format and permissions. If the check passes, proceed to S206. If the check fails, a file format error message will be displayed.
[0069] S206, Read SSER ledger information.
[0070] S207, determine whether the IP information in the SSER ledger information matches the IP information list in S201. If they match, proceed to S208; otherwise, proceed to S211.
[0071] S208, obtain parameter information.
[0072] S209, determine whether DMP ledger information is provided; if yes, proceed to S212, otherwise proceed to S210; it should be noted that the subsequent process of this step is different from S211, so they cannot be combined.
[0073] S210, Automatically generate parameter list; This step is used when only SSER ledger information is provided (DMP ledger information is not provided) to generate the parameter list.
[0074] S211, determine whether DMP ledger information is provided. If yes, execute S212; otherwise, execute S220.
[0075] S212, parse DMP ledger information.
[0076] S213, determine whether the DMP ledger file directory has been read. If yes, execute S214; otherwise, prompt that the file does not exist.
[0077] S214 checks the file format and permissions. If the check passes, proceed to S215. If the check fails, a file format error message will be displayed.
[0078] S215, Read DMP ledger information.
[0079] S216, determine whether the IP information in the DMP ledger information matches the IP information list in S201. If they match, proceed to S217; otherwise, proceed to S218.
[0080] S217, Add or update parameter information. When this step is executed, if the execution process goes through S208, it will combine the parameter information from S208. That is, the application scenario is to generate a parameter list based on two types of ledger information (DMP ledger information as the main one, and SSER as the secondary one). If the execution process goes through S211, that is, the application scenario is to generate a parameter list by only providing DMP ledger information (without providing SSER ledger information).
[0081] S218: Determine whether the parameter information from S208 has been read. If it has, execute S219; otherwise, execute S220.
[0082] S219, Automatically generate a parameter list based on the parameter information. This step summarizes several methods for generating parameter lists:
[0083] (1) After S208 and S217, that is, the parameter list is generated through two kinds of ledger information.
[0084] (2) After S211 and S217, that is, a parameter list is generated through a ledger information (DMP ledger information).
[0085] (3) After S208 and S218, that is, although DMP ledger information is provided, the IP information does not match, and the parameter list is generated only based on the SSER ledger information.
[0086] S220 prompts that at least one ledger information must be provided or the ledger information must be provided again. This step is performed after S202 (No) and 211 (No), or after S207 (No) and S211 (No), or after S218 (No).
[0087] In practical applications, at least one of the DMP ledger and SSER ledger should be provided. The parameter list should be obtained primarily from the DMP ledger and secondarily from the SSER ledger. If the records in the DMP and SSER are inconsistent, the DMP ledger shall prevail.
[0088] If the IP list involves multiple DMPs, and only the DMP list is provided, the result will output two files: an overall list with a custom output filename via the 'l' parameter, and a file named "DMP address not obtained_getlist.csv". If the SSER ledger is provided, an overall list with a custom output filename via the 'l' parameter will be generated, and multiple files will be generated based on the DMP address. The output files can be directly used in the subsequent JSON generation.
[0089] In S102, a JSON template string is determined based on the parameter list. A batch script is then generated based on the parameter values corresponding to the parameter list and the JSON template string. The specific process is as follows:
[0090] Parse the full parameter list to obtain a JSON template file. After checking that the file permissions and format are correct, parse the JSON template file to obtain a JSON template string; or,
[0091] Retrieve an existing JSON template string;
[0092] The JSON template string is parsed to obtain parameter items. The parameter values corresponding to the parameter items are obtained according to the full parameter list. Batch scripts are automatically generated and then split into separate files according to the parameter list corresponding to each MySQL database management platform.
[0093] refer to Figure 3 This is a schematic diagram illustrating the process of generating batch scripts according to a specific embodiment of the present invention. Figure 3 As shown, the specific process is as follows:
[0094] S301: Does the JSON template string exist? If yes, proceed to S307; otherwise, proceed to S302.
[0095] S302: Is there a JSON template file? If yes, proceed to S303. If not, prompt that a JSON template file is required.
[0096] S303, parses JSON template files.
[0097] S304, check the file directory. If the check passes, proceed to S305. If the check fails, prompt that the template file is not acceptable.
[0098] S305 checks file permissions and file format. If the check passes, proceed to S306. If the check fails, a format error message will be displayed.
[0099] S306, parse the template file and obtain the JSON template string.
[0100] S307, parse the string and obtain the required parameters.
[0101] S308, Read the parameter list and obtain the parameter value corresponding to the parameter item.
[0102] S309, Generate batch scripts.
[0103] S310, format JSON files.
[0104] In practical applications, batch script generation is based on a parameter list. The process involves reading and parsing the parameters to obtain a functional JSON file. After checking file permissions and format, the file content is read and parsed to obtain a JSON template string, or an existing JSON template string can be directly retrieved. After parsing the JSON template string to obtain the necessary parameter information, the parameter list generated by module one is read to obtain the corresponding parameter values. Batch scripts are then automatically generated and automatically split into separate files according to the DMP (Data Management Platform) architecture, with the filename named "DMP_ip_json_script_function.json".
[0105] In S103, the batch script is deployed on the server corresponding to the MySQL database management platform.
[0106] Specifically, batch scripts can be deployed on any server that enables firewalls between different DMPs.
[0107] In S104, the batch script is parsed to obtain a list of tasks to be executed. Concurrent tasks in the task list are executed according to their concurrency level, and a unique identifier for each concurrent task is generated. The specific process is as follows:
[0108] Read the parameter values of the first MySQL database management platform, parse the first server IP information corresponding to the first MySQL database management platform, and obtain the login address of the first MySQL database management platform based on the first server IP information;
[0109] Verify the username and password for accessing the first MySQL database management platform, check user permissions, and after the username and password verification and user permission check are passed, log in to the first MySQL database management platform according to the login address of the first MySQL database management platform, read the content of the batch script and parse it to obtain the list of tasks to be executed;
[0110] Execute concurrent tasks in the task list according to the concurrency level, and generate a unique identifier for each concurrent task;
[0111] Output the unique identifier of the concurrent task and the real-time task execution result;
[0112] Based on the parameter values of each MySQL database management platform, concurrent tasks corresponding to multiple MySQL database management platforms are executed concurrently.
[0113] refer to Figure 4 This is a schematic diagram of the task execution process according to a specific embodiment of the present invention. Figure 4 As shown, the specific process is as follows:
[0114] S401, retrieve DMP parameters. If not retrieved, indicate a parameter error; if retrieved, execute S402.
[0115] S402, parse DMP parameters and obtain DMP address.
[0116] S403, retrieve username parameter. If not retrieved, prompt parameter error; if retrieved, execute S404.
[0117] S404, parse the username parameter to obtain the login username.
[0118] S405, retrieve password parameters. If not retrieved, prompt "parameter error"; if retrieved, execute S406.
[0119] S406, Parse password parameters to obtain login password.
[0120] S407: Verify username and password, check user permissions. If username or password is incorrect, or permissions are insufficient, issue a prompt message. If username and password verification and permission check pass, proceed to S408.
[0121] S408: Get the concurrency parameter. If it is not obtained, use the default concurrency of 10. If it is obtained, execute S409.
[0122] S409, parse the concurrency parameter and obtain the concurrency level.
[0123] S410, read batch scripts.
[0124] S411 executes scripts in batches based on concurrency.
[0125] S412 outputs the script's unique identifier (TaskID) and the real-time task execution results.
[0126] S413, when it is necessary to interrupt a task, locates the concurrent task based on the TaskID and forcibly interrupts the task.
[0127] Specifically, by executing commands and providing the correct parameters, the DMP interface can be directly invoked, enabling concurrent execution of batch scripts. Further, the parameters are read and parsed to obtain the DMP's IP information, automatically retrieving the DMP login address; after reading and parsing the username and password information, the DMP address is accessed, and the correctness and permissions of the username, password, and password are verified. Then, the batch script content is read and parsed to obtain a list of tasks to be executed. The concurrency parameter is parsed to set the concurrency level, and concurrent tasks are executed in batches, with a unique TaskID assigned to each concurrent task.
[0128] By modifying the DMP address parameter values and batch script parameter values, concurrent tasks in multiple DMPs can be executed concurrently.
[0129] In S105, the concurrent task is located based on the unique identifier, and the concurrent task is controlled.
[0130] Upon receiving an interrupt instruction, the concurrent task is located based on the unique identifier, the execution of the concurrent task is interrupted, and the task execution result is statistically analyzed and output.
[0131] Specifically, a detailed embodiment of S105 can be found in [reference needed]. Figure 4 Step S413 in the code can supplement the lack of batch script interruption function in the conventional solution. By using the unique TaskID set when concurrently executing batch scripts in S104, concurrent tasks can be quickly and accurately located. When batch script execution errors occur or other needs arise, batch execution can be forcibly interrupted, and the overall batch script implementation results can be statistically analyzed and output.
[0132] This invention addresses scenarios involving a large number of MySQL nodes and MySQL Database Management Platforms (DMPs) in production environments. Leveraging the diverse operation and management functions offered by DMPs, each requiring different parameters, this invention proposes an automated operation and maintenance method for MySQL databases. This method automates the generation of parameter lists and batch scripts based on database ledger information, standardized parameterized lists, and functional JSON templates. Furthermore, it enables parallel processing of batch scripts from different DMPs and parallel processing of the same set of DMP scripts through tool calls to DMP interfaces. A batch interruption function is also added, ultimately improving the efficiency of automated MySQL database operation and maintenance.
[0133] It should be noted that although the operation of the method of the present invention has been described in a specific order in the above embodiments and figures, this does not require or imply that the operations must be performed in that specific order, or that all the operations shown must be performed to achieve the desired result. Additionally or alternatively, certain steps may be omitted, multiple steps may be combined into one step, and / or one step may be broken down into multiple steps.
[0134] After introducing the method of exemplary embodiments of the present invention, the following references are made. Figure 5 An automated operation and maintenance device for a MySQL database according to an exemplary embodiment of the present invention will be introduced.
[0135] The implementation of the automated operation and maintenance device for MySQL databases can refer to the implementation of the methods described above, and repeated details will not be elaborated further. The terms "module" or "unit" used below can refer to a combination of software and / or hardware that implements a predetermined function. Although the device described in the following embodiments is preferably implemented in software, hardware implementation, or a combination of software and hardware, is also possible and contemplated.
[0136] Based on the same inventive concept, this invention also proposes an automated operation and maintenance device for MySQL databases, such as... Figure 5 As shown, the device includes:
[0137] The parameter list generation module 510 is used to obtain the parameter list of the MySQL database management platform based on the server IP information and ledger data.
[0138] The batch script generation module 520 is used to determine the JSON template string according to the parameter list, and generate batch scripts according to the parameter values corresponding to the parameter list and the JSON template string.
[0139] Script deployment module 530 is used to deploy the batch scripts on the server corresponding to the MySQL database management platform;
[0140] The task execution module 540 is used to parse the batch script to obtain a list of tasks to be executed, execute concurrent tasks in the task list according to the concurrency level, and generate a unique identifier for the concurrent task.
[0141] The task control module 550 is used to locate the concurrent task based on the unique identifier and control the concurrent task.
[0142] In one embodiment, the parameter list generation module 510 is specifically used for:
[0143] Parse the ledger data. If the ledger file directory and ledger files exist, and the file format and file permissions are checked and passed, read the file content.
[0144] Based on the server IP information, obtain the parameter values corresponding to each MySQL database management platform in the file content, generate a full parameter list, and a parameter list corresponding to each MySQL database management platform.
[0145] The ledger data includes DMP ledger information and / or SSER ledger information; wherein,
[0146] The DMP ledger information is the ledger information of the MySQL database management platform, and it is updated in real time.
[0147] The SSER ledger information is the full ledger information in the MySQL database of the cloud management platform, and it is updated once a day.
[0148] In one embodiment, the batch script generation module 520 is specifically used for:
[0149] Parse the full parameter list to obtain a JSON template file. After checking that the file permissions and format are correct, parse the JSON template file to obtain a JSON template string; or,
[0150] Retrieve an existing JSON template string;
[0151] The JSON template string is parsed to obtain parameter items. The parameter values corresponding to the parameter items are obtained according to the full parameter list. Batch scripts are automatically generated and then split into separate files according to the parameter list corresponding to each MySQL database management platform.
[0152] In one embodiment, the task execution module 540 is specifically used for:
[0153] Read the parameter values of the first MySQL database management platform, parse the first server IP information corresponding to the first MySQL database management platform, and obtain the login address of the first MySQL database management platform based on the first server IP information;
[0154] Verify the username and password for accessing the first MySQL database management platform, check user permissions, and after the username and password verification and user permission check are passed, log in to the first MySQL database management platform according to the login address of the first MySQL database management platform, read the content of the batch script and parse it to obtain the list of tasks to be executed;
[0155] Execute concurrent tasks in the task list according to the concurrency level, and generate a unique identifier for each concurrent task;
[0156] Output the unique identifier of the concurrent task and the real-time task execution result;
[0157] Based on the parameter values of each MySQL database management platform, concurrent tasks corresponding to multiple MySQL database management platforms are executed concurrently.
[0158] In one embodiment, the task control module 550 is specifically used for:
[0159] Upon receiving an interrupt instruction, the concurrent task is located based on the unique identifier, the execution of the concurrent task is interrupted, and the task execution result is statistically analyzed and output.
[0160] It should be noted that although several modules of the automated operation and maintenance device for MySQL databases have been mentioned in the detailed description above, this division is merely exemplary and not mandatory. In fact, according to embodiments of the present invention, the features and functions of two or more modules described above can be embodied in a single module. Conversely, the features and functions of a single module described above can be further divided and embodied by multiple modules.
[0161] Based on the aforementioned inventive concept, such as Figure 6 As shown, the present invention also proposes a computer device 600, including a memory 610, a processor 620, and a computer program 630 stored in the memory 610 and executable on the processor 620. When the processor 620 executes the computer program 630, it implements the aforementioned automated operation and maintenance method for the MySQL database.
[0162] Based on the aforementioned inventive concept, the present invention proposes a computer-readable storage medium storing a computer program, which, when executed by a processor, implements the aforementioned automated operation and maintenance method for a MySQL database.
[0163] Based on the aforementioned inventive concept, this invention proposes a computer program product, which includes a computer program that, when executed by a processor, implements an automated operation and maintenance method for a MySQL database.
[0164] This invention overcomes the problems of existing technical solutions, such as numerous manual steps, complex parameters leading to tedious manual parameter list preparation, high error rate, and inability to verify; low concurrency due to the need to log in to different DMPs and perform batch operations only on the database nodes of that DMP when executing scripts in batches; and low fault tolerance and poor efficiency due to the inability to forcibly terminate batch scripts when scripts are selected incorrectly or during batch execution. This invention can effectively improve the degree of automated operation and maintenance, realize a highly concurrent, efficient, and fault-tolerant automated operation and maintenance method, automatically output parameter lists, reduce manual steps, lower error rate, and flexibly control tasks in execution.
[0165] The following detailed description of the automated operation and maintenance method and apparatus for MySQL database, using a specific embodiment as an example.
[0166] In practical applications, the program for automating the operation and maintenance of the MySQL database according to this invention can be written in Go language. It has no platform requirements and is compatible with both Linux and Windows platforms. This invention is simple to deploy, has no dependency packages, and can be used directly after the tool is deployed to the server. It also provides comprehensive help information to facilitate understanding how to use it.
[0167] A variety of tools are available for use. For example, references. Figure 7 As shown, using the --help function can get help information about the tool, including its functions, parameters, and default values.
[0168] For specific functions, please refer to Figure 8 As shown, using --help can directly obtain help information related to parameter definitions and default values.
[0169] Combination Figure 5 The automated operation and maintenance device for the MySQL database shown will be described in detail below:
[0170] The parameter list generation module 510 is used to obtain the parameter list of the MySQL database management platform based on the server IP information and ledger data.
[0171] Implementation plan for generating the inventory:
[0172] .\json.exe ListGen -L [IP list] -D [DMP export instance list] -S [SSER dmpinstance ledger] -1 [Generate overall list name, parameters are optional].
[0173] Output result:
[0174] The automatically generated parameter list is a CSV file. After execution, N+2 files are generated, including: getlist.csv, containing all information; N [IP]_getlist.csv files, each with parameter information separately split according to the DMP address; and a list of database nodes for which the DMP address was not obtained, containing the parameter files for those database nodes. Each file contains the following parameter information:
[0175] Server IP, VIP, database group name, database ID, instance primary / standby roles, server ID, DMP address.
[0176] For example:
[0177] Example 1: Provide both DMP and SSER ledgers (assuming ip.csv contains a list of IPs under the second protection, other DMPs, and unmanaged DMPs):
[0178] .\json.exe ListGen-L.\ip.csv-D.\20220316_Chongbao2.csv-S.\dmpinsD220318.csv-l.\getlist.csv;
[0179] After execution, four files are generated: getlist.csv, containing full information; 76.110.23.207_getlist.csv, 76.110.23.210_getlist.csv, and a list of DMP addresses not obtained (_getlist.csv). Among these, 76.110.23.207_getlist.csv and 76.110.23.210_getlist.csv can be directly used to generate the JSON script input list. The other files need to be verified to ensure they belong to the same DMP system and that no IP addresses were retrieved before use.
[0180] Example 2, providing only SSER ledger:
[0181] .\json.exe ListGen-L.\ip.csv-S.\dmpinsD220318.csv-l.\getlist.csv;
[0182] The file input after execution is the same as in Example 1.
[0183] Example 3, providing only the DMP ledger (assuming ip.csv contains a list of IPs for Security Protection 2, other DMPs, and unmanaged DMPs):
[0184] .\json.exe ListGen-L.\ip.csv-D.\20220316_Chongbao2.csv.\getlist.csv;
[0185] After execution, a file named getlist.csv is generated, containing all information; a list of DMP addresses was not obtained in _getlist.csv, and the contents of the two lists are identical.
[0186] The batch script generation module 520 is used to determine the JSON template string based on the parameter list, and generate batch scripts based on the parameter values corresponding to the parameter list and the JSON template string.
[0187] Implementation plan for batch script automatic generation function:
[0188] .\json.exe jsonGen json -s [json template file] -v [list of JSON files to be generated] --pretty -t [directory and filename of the generated JSON file];
[0189] or:
[0190] .\json.exe jsonGen json -S [json string] -v [list of JSON files to be generated] --pretty -t [directory and filename of the generated JSON files];
[0191] It should be noted that there is only one JSON template file and one JSON string.
[0192] The script deployment module 530 is used to deploy the batch scripts on the server corresponding to the MySQL database management platform.
[0193] Deploy the script on any server that enables firewalls between the DMPs.
[0194] The task execution module 540 is used to parse the batch script to obtain a list of tasks to be executed, execute concurrent tasks in the task list according to the concurrency level, and generate a unique identifier for the concurrent task.
[0195] Implementation plan:
[0196] .\json.exe batchJson[json to be executed] -i(umc main IP) -P[concurrency level] -p[DMP password] -u[DMP username];
[0197] It should be noted that:
[0198] (1) The -P parameter can be used to set the concurrency of script execution in the same DMP;
[0199] (2) By modifying the -i, -p, and -u parameters, the batch execution of scripts between different DMPs can be achieved in parallel;
[0200] (3) When the script is executed, it will output the TaskID of the current batch execution. This TaskID can be used as a parameter to force the batch execution to terminate.
[0201] The task control module 550 is used to locate the concurrent task based on the unique identifier and control the concurrent task.
[0202] Implementation plan:
[0203] .\json.exe stoptask[TaskID]-i(umc main IP)-P[concurrency]-p[DMP password]-u[DMP username];
[0204] During batch script execution, a unique TaskID is output, and the script progress is displayed in real time, including cancellation, success, failure, and total count. Upon forced interruption of concurrent tasks, the concurrent task corresponding to the TaskID immediately terminates, and the batch task execution results are tallied.
[0205] This invention addresses the challenges of MySQL Database Management Platform (DMP) environments with multiple MySQL database nodes and a single DMP capable of executing batch scripts related to the managed database nodes. It includes functions such as manifest generation, script generation, script execution, and script interruption, achieving one-click automation of the entire process of batch maintenance script preparation, implementation, and interruption. Detailed logs are recorded for process execution, facilitating progress tracking and troubleshooting. Compared to existing methods, this invention only requires providing IP addresses to automatically generate independent parameter lists for each DMP with a single click. Based on the parameter lists and JSON function templates, batch scripts for each function can be generated with a single click. On any server with a firewall enabled, this invention can execute batch scripts from different DMPs in parallel, achieving concurrent execution between different DMPs and between different database nodes within the same DMP, significantly improving parallel efficiency. Real-time script execution status is also provided. A new forced interruption batch function allows for the forced interruption of currently executing scripts, with automatic feedback on execution status.
[0206] The automated operation and maintenance method and apparatus for MySQL databases proposed in this invention obtains a parameter list for the MySQL database management platform based on server IP information and ledger data; determines a JSON template string based on the parameter list; generates batch scripts based on the parameter values corresponding to the parameter list and the JSON template string; deploys the batch scripts on the server corresponding to the MySQL database management platform; parses the batch scripts to obtain a list of tasks to be executed; executes concurrent tasks in the task list according to concurrency level and generates a unique identifier for each concurrent task; locates the concurrent task based on the unique identifier and controls the concurrent task. The overall solution can effectively improve the degree of automated operation and maintenance, achieve high concurrency and high efficiency automated operation and maintenance, reduce manual steps, lower the error rate, and also forcefully control the executing tasks, improving the flexibility of task control. The automated operation and maintenance method and apparatus for MySQL databases proposed in this invention provide strong technical support for database operation and maintenance.
[0207] The acquisition, storage, use, and processing of data in this application comply with relevant laws and regulations.
[0208] Those skilled in the art will understand that embodiments of the present invention can be provided as methods, apparatus, or computer program products. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention can take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.
[0209] This invention is described with reference to flowchart illustrations and / or block diagrams of methods and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.
[0210] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.
[0211] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.
[0212] Finally, it should be noted that the above-described embodiments are merely specific implementations of the present invention, used to illustrate the technical solutions of the present invention, and not to limit it. The scope of protection of the present invention is not limited thereto. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that any person skilled in the art can still modify or easily conceive of changes to the technical solutions described in the foregoing embodiments within the technical scope disclosed in the present invention, or make equivalent substitutions for some of the technical features; and these modifications, changes, or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present invention, and should all be covered within the scope of protection of the present invention. Therefore, the scope of protection of the present invention should be determined by the scope of the claims.
Claims
1. An automated operation and maintenance method for a MySQL database, characterized in that, include: Based on the server IP information and ledger data, a list of parameters for the MySQL database management platform is obtained; The JSON template string is determined based on the parameter list, and a batch script is generated based on the parameter values corresponding to the parameter list and the JSON template string. Deploy the batch script on the server corresponding to the MySQL database management platform; The batch script is parsed to obtain a list of tasks to be executed. Concurrent tasks in the task list are executed according to their concurrency level, and a unique identifier for each concurrent task is generated. The concurrent task is located based on the unique identifier, and the concurrent task is controlled. Based on server IP information and ledger data, a list of parameters for the MySQL database management platform was obtained, including: Parse the ledger data. If the ledger file directory and ledger files exist, and the file format and file permissions are checked and passed, read the file content. Based on the server IP information, obtain the parameter values corresponding to each MySQL database management platform in the file content, generate a full parameter list, and a parameter list corresponding to each MySQL database management platform.
2. The method according to claim 1, characterized in that, The ledger data includes DMP ledger information and / or SSER ledger information; wherein... The DMP ledger information is the ledger information of the MySQL database management platform, and it is updated in real time. The SSER ledger information is the full ledger information in the MySQL database of the cloud management platform, and it is updated once a day.
3. The method according to claim 1, characterized in that, Based on the parameter list, a JSON template string is determined. Based on the parameter values corresponding to the parameter list and the JSON template string, a batch script is generated, including: Check if a JSON template string exists; If it does not exist, the full list of parameters is parsed to obtain a JSON template file. After checking that the file permissions and format are correct, the JSON template file is parsed to obtain a JSON template string. If it exists, retrieve the existing JSON template string; The JSON template string is parsed to obtain parameter items. The parameter values corresponding to the parameter items are obtained according to the full parameter list. Batch scripts are automatically generated and then split into separate files according to the parameter list corresponding to each MySQL database management platform.
4. The method according to claim 1, characterized in that, The batch script is parsed to obtain a list of tasks to be executed. Concurrent tasks in the task list are executed according to their concurrency level, and a unique identifier for each concurrent task is generated, including: Read the parameter values of the first MySQL database management platform, parse the first server IP information corresponding to the first MySQL database management platform, and obtain the login address of the first MySQL database management platform based on the first server IP information; Verify the username and password for accessing the first MySQL database management platform, check user permissions, and after the username and password verification and user permission check are passed, log in to the first MySQL database management platform according to the login address of the first MySQL database management platform, read the content of the batch script and parse it to obtain the list of tasks to be executed; Execute concurrent tasks in the task list according to the concurrency level, and generate a unique identifier for each concurrent task; Output the unique identifier of the concurrent task and the real-time task execution result; Based on the parameter values of each MySQL database management platform, concurrent tasks corresponding to multiple MySQL database management platforms are executed concurrently.
5. The method according to claim 1, characterized in that, Locating the concurrent task based on the unique identifier and controlling the concurrent task includes: Upon receiving an interrupt instruction, the concurrent task is located based on the unique identifier, the execution of the concurrent task is interrupted, and the task execution result is statistically analyzed and output.
6. An automated operation and maintenance device for a MySQL database, characterized in that, include: The parameter list generation module is used to generate a parameter list for the MySQL database management platform based on server IP information and ledger data. The batch script generation module is used to determine the JSON template string based on the parameter list, and generate batch scripts based on the parameter values corresponding to the parameter list and the JSON template string. The script deployment module is used to deploy the batch scripts on the server corresponding to the MySQL database management platform. The task execution module is used to parse the batch script to obtain a list of tasks to be executed, execute concurrent tasks in the task list according to the concurrency level, and generate a unique identifier for the concurrent task; The task control module is used to locate the concurrent task based on the unique identifier and control the concurrent task. The parameter list generation module is specifically used to parse the ledger data. If the ledger file directory and ledger file both exist, and the file format and file permissions are checked and passed, the file content is read. Based on the server IP information, the parameter values corresponding to each MySQL database management platform in the file content are obtained, and a full parameter list and a parameter list corresponding to each MySQL database management platform are generated.
7. A computer device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the method of any one of claims 1 to 5.
8. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program that, when executed by a processor, implements the method of any one of claims 1 to 5.
9. A computer program product, characterized in that, The computer program product includes a computer program that, when executed by a processor, implements the method of any one of claims 1 to 5.