Backup and monitoring method and device for MySQL database and medium

Abstract

The invention discloses a MySQL database backup and monitoring method and device and a medium, belongs to the technical field of cloud native database operation and maintenance, and is used for solving the technical problems that a traditional backup scheme needs manual intervention, is complex in operation, is difficult to be independently operated and completed by a common user, is high in time cost and low in efficiency, and also has the lack of a monitoring mechanism. The method comprises the following steps: carrying out resource configuration processing under a related custom resource mechanism on a Kubernetes cluster, and generating and deploying a resource definition module; state change monitoring is carried out on self-defined resources in the resource definition module, backup scheduling and backup recovery processing in a related self-defined resource state is carried out on a MySQL database needing to be backed up in a kubernetes cluster, and a backup and recovery logic control strategy based on an Operator controller is generated; and carrying out monitoring processing on the MySQL database and the backup and recovery logic control strategy under related database abnormal states and backup and recovery task abnormal indexes, and determining early warning information.

Description

Technical Field

[0001] This application relates to the field of cloud-native database operation and maintenance, and in particular to a method, device and medium for backup and monitoring of MySQL database. Background Technology

[0002] Traditional backup solutions (such as manual export and scheduled CronJob) require manual intervention, have low automation, are complex to operate, require high technical skills from personnel, have unobservable backup status, lack monitoring and alarms, lack automatic retry for failures, and have complex recovery processes.

[0003] In a Kubernetes cluster environment, traditional database backup management methods often require users to write complex scripts and perform tedious manual configurations. The process is too complicated, requiring not only professional technicians to perform tedious configuration and maintenance, but also operators to have a deep understanding of the system environment. Ordinary users cannot complete this independently, which consumes a lot of time and energy and is prone to configuration errors and omissions, resulting in unsatisfactory backup results.

[0004] Furthermore, community-developed open-source tools focus more on database deployment and management. While they offer basic backup functionality, manual intervention is required for scheduling, and monitoring mechanisms are generally lacking. Most existing backup systems only provide basic execution result feedback, lacking comprehensive monitoring and analysis capabilities. When errors or anomalies occur during the backup process, the system often fails to issue timely alerts, leading to problems going undetected and unaddressed. For long-running backup tasks, there is a lack of historical statistical analysis and trend prediction functions, making it difficult to assess the effectiveness of backup strategies and identify areas for optimization.

[0005] In addition, Kubernetes lacks native fine-grained backup management capabilities for stateful applications (such as MySQL), making it difficult to develop custom scripts and lacking version control, state persistence, and native Kubernetes integration. Summary of the Invention

[0006] This application provides a method, device, and medium for backing up and monitoring a MySQL database, which addresses the following technical problems: traditional backup solutions require manual intervention, are complex to operate, are difficult for ordinary users to complete independently, have high time costs and low efficiency, and also lack monitoring mechanisms.

[0007] The embodiments of this application adopt the following technical solutions:

[0008] On one hand, this application provides a method for backing up and monitoring a MySQL database, including: performing resource configuration processing on a Kubernetes cluster under a custom resource mechanism according to the MySQL database backup strategy and recovery task strategy, generating and deploying a resource definition module; monitoring the state changes of custom resources in the resource definition module through a preset Operator controller, and performing backup scheduling and backup recovery processing on the MySQL database that needs to be backed up in the Kubernetes cluster under the custom resource state, generating a backup and recovery logic control strategy based on the Operator controller; monitoring the MySQL database and the backup and recovery logic control strategy under abnormal database states and abnormal indicators of backup and recovery tasks through a predefined monitoring and alarm module, and determining early warning information; and completing the backup and monitoring management of the MySQL database that needs to be backed up based on the backup and recovery logic control strategy and the early warning information.

[0009] This application embodiment, through the designed resource definition module, Operator controller module, and monitoring module, and based on the Kubernetes Operator, implements a full-process system for automatic discovery and backup of MySQL databases within a Kubernetes cluster, supporting one-click recovery, real-time monitoring of backup status, and anomaly alarms. This system not only solves the problems of complex operation, unobservable backup status, lack of monitoring and alarm system, and complex recovery process in traditional MySQL database backup methods, but also enables more convenient and flexible configuration, backup data persistence, real-time monitoring and alarms, one-click recovery, and other functions, significantly improving the reliability of the MySQL database system within the cluster.

[0010] In one feasible implementation, before performing resource configuration processing on the Kubernetes cluster under a custom resource mechanism based on the backup strategy and recovery task strategy of the MySQL database, and generating and deploying the resource definition module, the method further includes: using a YAML file template in the Kubernetes resource orchestration specification and based on a tag selector, automatically discovering and configuring backup data for the MySQL pod in the Kubernetes cluster, defining and obtaining the backup strategy for the MySQL database; wherein, the backup data includes: backup cycle, number of backups to retain, backup storage information, MySQL Cluster information, and backup type; performing data identification processing on the network address, port number, authentication information, and instance name of the target MySQL cluster in the target cluster for the recovery task to obtain target cluster configuration information; performing data identification processing on the local file system path, storage location, backup file name, backup set version information, and decryption key of the encrypted backup in the MySQL database for the recovery task to obtain backup source information; and defining and generating the recovery task strategy for the MySQL database based on the target cluster configuration information and the backup source information.

[0011] In one feasible implementation, based on the backup and recovery strategies of the MySQL database, resource configuration processing under a custom resource mechanism is performed on the Kubernetes cluster to generate and deploy a resource definition module. Specifically, this includes: determining the storage system based on the backup requirements of the MySQL database; wherein the storage system includes any one or more of the following: PVC, MinIO, and NFS; configuring various recorded storage parameters with the backup and recovery strategies through the storage system to obtain configuration completion information; wherein the various storage parameters include at least: PVC name, storage endpoint, bucket name, access key, and encryption key; based on the configuration completion information and according to a YAML file template under the custom resource mechanism, modifying the actual environment information of the file to be backed up; and applying the YAML configuration file corresponding to the file to be backed up to the Kubernetes cluster according to the actual environment information to generate the resource definition module.

[0012] In one feasible implementation, a preset Operator controller is used to monitor state changes of custom resources in the resource definition module. Specifically, this includes: deploying the Operator controller to a Kubernetes cluster; running the Operator controller as a deployment; using the Operator controller and based on the Kubernetes cluster's Watch mechanism to monitor state changes of custom resources in the resource definition module; distributing resource change events in the custom resource row state changes to the corresponding processing modules according to type, and generating an event distribution strategy; automatically retrying backup processing failures, supporting configuration of the number of retryes and intervals, and generating a backup error handling strategy; and completing the monitoring of state changes of the custom resources based on the event distribution strategy and the backup error handling strategy.

[0013] In one feasible implementation, backup scheduling and recovery processing are performed on the MySQL databases requiring backup in the Kubernetes cluster under the aforementioned custom resource states. A backup and recovery logic control strategy based on the Operator controller is generated. Specifically, this includes: after monitoring the state changes of the custom resources in the resource definition module, the custom resource state of the MySQL databases requiring backup is detected according to the Operator controller; if the custom resource state is a creation or update event, the backup strategy in the resource definition module is parsed; and based on the parsed backup strategy and scheduled tasks, a backup task operation is triggered; the type of the backup task is determined; if the backup task is a full backup task, the mysqldump tool is called to generate a full SQL backup file, and an encryption algorithm is used to encrypt it. The full SQL backup file is encrypted. If the backup task is an incremental backup task, the mysqlbinlog tool is called to generate an incremental SQL backup file, and the incremental SQL backup file is encrypted using an encryption algorithm. When the backup task operation fails, the backup is automatically retried, and by default, it ends after 3 failures, and a backup failure alarm indicator is pushed. The backup file after the backup task operation is successful is uploaded to the execution storage location through the storage system, and the corresponding backup log and backup indicator data are recorded. The files in the backup storage are checked regularly, and expired backup files are deleted according to the backup retention policy. Based on the above backup scheduling steps, a backup logic control policy based on the Operator controller is generated. The backup logic control policy and the recovery logic control policy are combined to obtain the backup and recovery logic control policy.

[0014] In one feasible implementation, before combining the backup logic control strategy and the recovery logic control strategy to obtain the backup and recovery logic control strategy, the method further includes: if the custom resource status is a creation or update event and a database recovery task needs to be executed, then parsing the recovery task strategy in the resource definition module; and triggering a recovery task operation according to the parsed recovery task strategy and the task being used; performing verification and detection on the source data backup file under the conditions of integrity and availability to obtain a first detection result; and performing file permission checks, target database network checks, and user password checks on the source data backup file to obtain a second detection result; if both the first detection result and the second detection result are passing, then decompressing and decrypting the data backup file in the storage system through the Operator controller, and restoring the decompressed and decrypted data backup file to the target database cluster; when the recovery task fails, automatic backup retry processing is performed, and by default, the process ends after 3 failures, and a recovery failure alarm indicator is pushed; based on the above recovery scheduling steps, the recovery logic control strategy based on the Operator controller is generated.

[0015] In one feasible implementation, a predefined monitoring and alarm module is used to monitor the MySQL database and the backup and recovery logic control strategy for abnormal database states and abnormal backup and recovery task indicators, and to determine early warning information. Specifically, this includes: obtaining the status indicators of the MySQL database through mysqld-exporter; wherein the status indicators include kernel indicators and key performance indicators; collecting custom backup indicators of the Operator controller used to execute the backup and recovery logic control strategy; and monitoring the status indicators and custom backup indicators for thresholds related to abnormal database states and abnormal backup and recovery task indicators to determine the early warning information.

[0016] In one feasible implementation, the status indicators and the custom backup indicators are monitored for thresholds related to database anomalies and backup recovery task anomalies to determine the early warning information. Specifically, this includes: collecting and storing the status indicators and the custom backup indicators via a Prometheus server; performing anomaly threshold judgment on the status indicators and the indicators corresponding to the database anomalies to obtain first early warning information at the database layer; performing anomaly threshold judgment on the custom backup indicators and the backup recovery task anomalies to obtain first early warning information at the backup indicator layer; wherein the early warning information includes: the first early warning information and the second early warning information; and sending the early warning information to a designated receiving end via an early warning notification component.

[0017] Secondly, embodiments of this application also provide a backup and monitoring device for a MySQL database, the device comprising: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor to enable the at least one processor to execute a backup and monitoring method for a MySQL database as described in any of the above embodiments.

[0018] Thirdly, embodiments of this application also provide a non-volatile computer storage medium, which is a non-volatile computer-readable storage medium storing at least one program. Each program includes instructions, which, when executed by a terminal, cause the terminal to execute a MySQL database backup and monitoring method as described in any of the above embodiments.

[0019] This application provides a method, device, and medium for backing up and monitoring a MySQL database. Compared with the prior art, the embodiments of this application have the following beneficial technical effects:

[0020] 1. Implement closed-loop management of the entire process of MySQL database backup, recovery, monitoring, and alerting within a Kubernetes cluster:

[0021] By combining automated backup processes with intelligent monitoring systems, manual intervention is reduced, significantly lowering operational complexity and labor costs. Simultaneously, rapid data recovery is supported, minimizing business downtime, reducing the risk of data loss, and providing strong guarantees for business continuity and data security.

[0022] 2. Provides declarative backup policy management:

[0023] It provides highly flexible and customizable declarative backup policy management capabilities, allowing for fine-grained definition of backup plans through custom resource definitions (CRDs). Users can configure backup frequency, backup time windows, backup data ranges, and backup retention policies in detail within the CRD according to actual business needs, thereby achieving comprehensive and precise control over backup plans. This declarative management approach greatly simplifies the backup policy configuration process, reduces the possibility of human error, and improves the efficiency and accuracy of backup policy management.

[0024] 3. Implement an end-to-end automated backup process:

[0025] It covers the entire process from data backup, compression, encryption to upload and storage, and combines storage services to complete the persistent storage and version management of backup data.

[0026] 4. Establish a real-time monitoring indicator system:

[0027] This system is used for real-time monitoring of key metrics and database performance during the backup process. Regarding backup metrics, it records the time taken for each backup, calculates the size of the backup data, monitors the backup status, and promptly detects any anomalies that may occur during the backup process, such as backup failures or interruptions, providing appropriate warnings and handling. Simultaneously, it monitors database performance in real-time, including metrics such as database response time, throughput, and concurrent connections, to promptly identify database performance bottlenecks and ensure the normal operation of the database. Through real-time monitoring and analysis of these metrics, operations and maintenance personnel can promptly understand the operational status of the database and backups, making appropriate decisions and adjustments.

[0028] 5. Supports one-click recovery:

[0029] Provides users with a convenient and efficient data recovery experience. In the event of database anomalies, data loss, corruption, or accidental operations, users can trigger a rapid database recovery from backup sets via a declarative API. The entire recovery process requires no complex steps or cumbersome configuration, significantly reducing data recovery time, improving efficiency and success rate, and minimizing the impact on business operations.

[0030] 6. Achieve non-invasiveness independently:

[0031] Backup and monitoring are deployed independently, without intrusion into the MySQL database within the cluster, and have minimal impact on business systems. Attached Figure Description

[0032] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments recorded in this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort. In the drawings:

[0033] Figure 1 A flowchart illustrating a method for backing up and monitoring a MySQL database, as provided in this application embodiment;

[0034] Figure 2 A framework diagram of a MySQL database backup and monitoring system provided in this application embodiment;

[0035] Figure 3 A schematic diagram of a backup task provided in an embodiment of this application;

[0036] Figure 4 This is a schematic diagram of a one-click recovery task provided in an embodiment of this application;

[0037] Figure 5 A schematic diagram of a monitoring process provided in an embodiment of this application;

[0038] Figure 6 A schematic diagram of a resource definition module provided in an embodiment of this application;

[0039] Figure 7 This is a schematic diagram of the structure of a MySQL database backup and monitoring device provided in an embodiment of this application. Detailed Implementation

[0040] To enable those skilled in the art to better understand the technical solutions in this application, the technical solutions in the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. Based on the embodiments of this specification, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of this application.

[0041] This application provides a method for backing up and monitoring a MySQL database, such as... Figure 1 As shown, the backup and monitoring method for a MySQL database specifically includes steps S101-S104:

[0042] S101. Based on the backup and recovery strategies of the MySQL database, perform resource configuration processing on the Kubernetes cluster under the relevant custom resource mechanism, and generate and deploy the resource definition module.

[0043] Specifically, using the YAML file template in the Kubernetes resource orchestration specification and based on a tag selector, the backup data of the MySQL pod in the Kubernetes cluster is automatically discovered and configured, defining and obtaining the backup strategy for the MySQL database. The backup data includes: backup cycle, number of backups to retain, backup storage information, MySQL Cluster information, and backup type.

[0044] In one embodiment, Figure 6 This is a schematic diagram of a resource definition module provided in an embodiment of this application, such as... Figure 6 As shown, in order to break the limitations of traditional database backup management methods, the custom resource (CRD) mechanism in the Kubernetes ecosystem is adopted. Following the Kubernetes resource orchestration specification, a standard YAML file template has been developed. Visual configuration is performed through YAML files to realize the declarative definition of MySQL database instance backup and monitoring strategies.

[0045] Specifically, the MySQLBackup (database backup tool) CRD defines the backup strategy for the MySQL database, including the backup cycle (set by hour, day, week, etc.), the number of backups to retain, backup storage information (such as PVC, minio, NFS, etc.), MySQLCluster information, and backup type (full backup, incremental backup). A tag selector is defined to automatically discover MySQL pods within the Kubernetes cluster. A key configuration example of MySQLBackup-template.yaml is shown below:

[0046]

[0047]

[0048] Furthermore, the network address, port number, authentication information, and instance name of the target MySQL cluster in the target cluster are processed by the recovery task to obtain the target cluster configuration information.

[0049] Furthermore, the local file system path, storage location, backup file name, backup set version information, and decryption key of the encrypted backup in the MySQL database are processed for data identification of the recovery task to obtain backup source information.

[0050] Furthermore, based on the target cluster configuration information and backup source information, a recovery task strategy for the MySQL database is defined and generated.

[0051] In one embodiment, such as Figure 6 As shown, the MySQLRestore CRD can also be used to define the target cluster and backup source for the recovery task, that is, to define and generate the recovery task strategy for the MySQL database. The target cluster includes the network address, port number, authentication information (such as username and password), and the specific database instance name of the target MySQL cluster. The backup source includes the local file system path, storage location, backup file name, backup set version information, and decryption key for encrypted backups, etc. A key configuration example of MySQLRestore-template.yaml is as follows:

[0052]

[0053]

[0054] Furthermore, based on the backup requirements of the MySQL database, the storage system is determined; the storage system includes any one or more of the following: PVC, MinIO, and NFS.

[0055] Furthermore, the storage system configures various recorded storage parameters, backup policies, and recovery task policies to obtain configuration completion information. These storage parameters include at least: PVC name, storage endpoint, bucket name, access key, and encryption key.

[0056] Furthermore, based on the configuration completion information and according to the YAML file template under the custom resource mechanism, the actual environment information of the file to be backed up is modified.

[0057] Furthermore, based on the actual environment information, the YAML configuration file corresponding to the file to be backed up is applied to the Kubernetes cluster to generate a resource definition module.

[0058] In one embodiment, Figure 2 A framework diagram of a MySQL database backup and monitoring system provided in this application embodiment is shown below. Figure 2 as well as Figure 6 As shown, first, prepare the storage system according to backup requirements, such as PVC, MinIO, or NFS, and record information such as PVC name, storage endpoint, bucket name, access key, and encryption key for configuring the backup strategy. Then, modify the actual environment information in the CRD resource YAML file template developed in this application. Finally, the resource definition module deployment can be completed by executing `kubectl apply -f *-template.yaml`.

[0059] S102. Through the preset Operator controller, the status change of the custom resources in the resource definition module is monitored, and the backup scheduling and backup recovery processing of the MySQL database that needs to be backed up in the Kubernetes cluster are performed on the relevant custom resource status, and a backup and recovery logic control strategy based on the Operator controller is generated.

[0060] It should be noted that, Figure 2 A framework diagram of a MySQL database backup and monitoring system provided in this application embodiment is shown below. Figure 2As shown, the Operator controller is a core management component based on the Kubernetes ecosystem. Through an event-driven architecture and declarative configuration, it enables automated management of the entire lifecycle of MySQL database backup, monitoring, and recovery strategies within the cluster. Based on the official Kubernetes Operator SDK development framework, a fully functional Operator controller was completely built and implemented, with the corresponding business logic encapsulated in a custom controller, the MySQL Backup Operator controller (hereinafter referred to as the Operator controller).

[0061] The Operator controller is maintained through the Helm package manager. After the controller is deployed to the Kubernetes cluster, the controller module will run as a deployment and start listening for changes to MySQLBackup CRD and MySQLRestore CRD resources.

[0062] The core main logic code is as follows:

[0063]

[0064] Specifically, the Operator controller is first deployed to the Kubernetes cluster. The Operator controller runs as a deployment.

[0065] Furthermore, through the Operator controller and based on the Watch mechanism of the Kubernetes cluster, state changes of custom resources in the resource definition module are monitored:

[0066] Furthermore, resource change events in the state changes of custom resource rows are distributed to the corresponding processing modules according to their types, generating an event distribution strategy.

[0067] Furthermore, it automatically retryes backup tasks that fail, and supports configuring the number of retryes and the interval, as well as generating backup error handling strategies.

[0068] Furthermore, based on event distribution strategies and backup error handling strategies, the system can monitor changes in the state of custom resources.

[0069] In one embodiment, such as Figure 2As shown, the Operator controller includes resource coordination, that is, the Operator controller listens to the status changes (creation, update, deletion) of custom resources (CRD) in real time through Kubernetes' Watch mechanism, and realizes asynchronous processing and concurrent scheduling of tasks through work queues. Its core mechanisms include: (1) Event distribution: Distributing resource change events to the corresponding processing modules according to type (such as backup policy update, alarm policy, recovery policy update, etc.) to achieve functional decoupling. (2) Error handling: Automatically retrying tasks that fail to process, supporting configuration of the number of retries and intervals to avoid task interruption due to temporary failures.

[0070] Furthermore, after monitoring the status changes of custom resources in the resource definition module, the status of custom resources of the MySQL database that needs to be backed up is detected and processed according to the Operator controller.

[0071] If the custom resource status is a creation or update event, then the backup policy in the resource definition module is parsed; and based on the parsed backup policy and the scheduled task, the backup task operation is triggered.

[0072] Furthermore, the backup task type is determined:

[0073] If the backup task is a full backup, the mysqldump tool is invoked to generate a full SQL backup file, which is then encrypted using an encryption algorithm. If the backup task is an incremental backup, the mysqlbinlog tool is invoked to generate an incremental SQL backup file, which is then encrypted using an encryption algorithm.

[0074] Furthermore, when a backup task fails, it automatically performs a backup retry, and by default, it terminates after 3 failed attempts and pushes a backup failure alarm indicator.

[0075] Furthermore, the backup files after a successful backup task are uploaded to the execution storage location via the storage system, and corresponding backup logs and backup metric data are recorded. It is also necessary to periodically check the files in the backup storage and delete expired backup files according to the backup retention policy. Finally, based on the above backup scheduling steps, a backup logic control policy based on the Operator controller is generated.

[0076] In one embodiment, Figure 3 A schematic diagram of a backup task provided in an embodiment of this application, such as... Figure 3 as well as Figure 2As shown, for MySQL databases in a Kubernetes cluster that need to be backed up, the first step is to add a label to the MySQL pod in the StatefulSet / deployment that matches the label defined in the MySQLBackup CRD. This allows the Operator controller module to automatically discover the relevant MySQL pods. The Operator controller will then periodically trigger backup tasks to automatically back up the MySQL databases with the labels, based on the backup strategy defined in the MySQLBackup CRD.

[0077] The core logic of backup scheduling is as follows:

[0078]

[0079]

[0080] Among them, the backup logic control strategy also has the following features for the corresponding execution module: 1. Automatic discovery: Automatically discover MySQL pods with the same label in the cluster through the label defined by MySQLBackupCRD, and then execute the backup strategy;

[0081] 2. Strategy Analysis: Converts Cron expressions into scheduled tasks, supports time zone configuration (e.g., execute at 23:00 Beijing time), and is compatible with mixed strategies of full backup and incremental backup (e.g., two full backups per week and three incremental backups per day);

[0082] 3. Task execution: (1) Full backup: Call the MySQL backup tool (mysqldump) to back up the entire database. It supports single transaction mode and ensures data consistency during backup; (2) Incremental backup: Perform incremental data backup based on binary log (Binlog). Only back up the changed data since the last backup.

[0083] 4. Storage Management: Supports multiple storage systems such as PVC, MinIO, NFS, and S3. Backup files are compressed and encrypted before being uploaded, and expired files are automatically deleted through retention policies (the number of files to be retained can be set according to type, such as retaining 7 full backups and 14 incremental backups).

[0084] 5. Fault tolerance mechanism: Automatically retry when backup fails (3 times by default), record detailed error logs and notify maintenance personnel through events to ensure the reliability of backup tasks.

[0085] 6. Log recording: Record backup logs, including backup start time, end time, backup status, backup file size, etc., and push them to the monitoring module as indicator data.

[0086] As a possible implementation method, such as Figure 3 As shown, in the Operator controller, when a creation or update event of a MySQLBackup CRD is detected, the backup policy is parsed, and a scheduled task (CronJob) is used to trigger the backup operation based on the backup type and cycle. The backup task is executed as follows: for full backups, the mysqldump tool is called to generate an SQL backup file, which is then encrypted using an encryption algorithm; for incremental backups, the mysqlbinlog tool is called to generate an SQL backup file, which is also encrypted using an encryption algorithm. Backup tasks automatically retry upon failure, ending after a default of three failures and pushing an alert metric. The encrypted backup file is uploaded to the specified storage location using the storage system's SDK, and backup logs and backup metric data are recorded. Files in the backup storage are periodically checked, and expired backup files are deleted according to the backup retention policy.

[0087] Furthermore, if a custom resource is in a creation or update event state and a database recovery task needs to be executed, the recovery task strategy in the resource definition module is parsed. Based on the parsed recovery task strategy and the task being used, the recovery task operation is triggered.

[0088] Furthermore, the source data backup file undergoes verification tests regarding integrity and availability, yielding the first test result. Then, file permission checks, target database network checks, and user password checks are performed on the source data backup file, resulting in the second test result.

[0089] If both the first and second detection results are passed, the Operator controller will decompress and decrypt the data backup files in the storage system and restore the decompressed and decrypted data backup files to the target database cluster.

[0090] Furthermore, when a recovery task fails, a backup retry is automatically initiated, and by default, the process terminates after three failed attempts, triggering a recovery failure alarm. Finally, based on the above recovery scheduling steps, a recovery logic control strategy based on the Operator controller is generated.

[0091] Furthermore, the backup logic control strategy and the recovery logic control strategy are combined to obtain the backup and recovery logic control strategy.

[0092] In one embodiment, Figure 4 This is a schematic diagram of a one-click recovery task provided in an embodiment of this application, such as... Figure 4 as well as Figure 2As shown, after a user creates a custom resource MySQLRestore CRD according to the template, the controller will immediately start a continuous monitoring mechanism for the resource. Once a change event that meets the triggering conditions is detected, the controller will automatically orchestrate and execute the database recovery task according to the recovery strategy defined in the CRD.

[0093] The core logic of the recovery task is as follows:

[0094]

[0095]

[0096] The execution module corresponding to the backup and recovery logic control strategy has the following features: 1. Confirmation information: Based on the relevant information defined in the MySQLRestore CRD, the database file to be restored and the target pod.

[0097] 2. Locate and verify files: The Operator controller verifies the integrity and availability of the source data backup files, ensuring that the backup data files are not corrupted and are version compatible.

[0098] 3. Permission verification: The Operator controller establishes a secure connection with the target cluster and performs necessary authentication and permission checks.

[0099] 4. Restore task execution: According to the policy configuration, the Operator controller will perform a full or incremental recovery through MySQL commands. At the same time, during data transmission, the system will implement a verification mechanism to ensure data consistency. After the task is completed, the system will automatically perform necessary verification checks.

[0100] 5. Result Update: Update the task status based on the result of the recovery task execution. If it fails, push an alarm metric. If it succeeds, reset the recovery flag to false.

[0101] As a possible implementation method, such as Figure 4 As shown, in the Operator controller, when a creation or update event of a MySQLRestore CRD is detected, the recovery policy is parsed, and a recovery operation is triggered using a job according to the policy. Then, verification is performed: verifying the integrity and availability of the source data backup file (e.g., file existence, corruption, etc.), while also checking file permissions, target database network reachability, and user password accuracy. Next, the recovery task is executed: according to the policy configuration, the Operator controller decompresses and decrypts the data backup file in the storage system and restores it to the target database cluster. After the task is completed, the system automatically performs necessary verification checks. Finally, if the backup task fails, it will automatically retry, ending after a default of 3 failures and pushing an alarm message.

[0102] In other words, the Operator controller ensures its stable operation in the cluster environment through the following mechanisms: 1. Multi-replica deployment: Supports multi-replica deployment to ensure high service availability. 2. Fault recovery: Based on the characteristics of Kubernetes clusters, it automatically triggers reconstruction or restart for abnormal instances, and the recovery process requires no manual intervention. 3. Logging and auditing: Records complete operation logs (such as backup start / end time, warning trigger reasons), which can be queried through Kubernetes events or external log systems to meet compliance auditing requirements.

[0103] S103. Through the predefined monitoring and alarm module, monitor and process the abnormal database status and abnormal indicators of backup and recovery tasks of the MySQL database and the backup and recovery logic control strategy, and determine the early warning information.

[0104] It should be noted that the predefined monitoring and alarm module integrates monitoring tools such as Prometheus and Grafana to regularly collect various monitoring metrics data of the MySQL database and custom backup metrics of the Operator controller module, thus building a three-dimensional monitoring system. Through the built-in early warning rules developed in this application, alarm information is pushed to the kubenerte cluster for abnormal status of MySQL database instances and abnormal backup and recovery tasks. In addition, a custom-developed built-in monitoring dashboard is used to visualize the running status of the MySQL database and backup tasks, thereby improving the efficiency of database backup management in a large-scale cluster environment.

[0105] Specifically, mysqld-exporter is used to obtain MySQL database status metrics. These metrics include kernel metrics and key performance metrics. Custom backup metrics are also collected from the Operator controller, which is used to execute backup and recovery logic control strategies.

[0106] In one embodiment, Figure 5 A schematic diagram of a monitoring process is provided for an embodiment of this application, such as... Figure 5 as well as Figure 2 As shown, in the database layer, kernel metrics and key performance metrics such as database status, slow query rate, peak connection count, and InnoDB buffer pool hit rate are monitored through mysqld-exporter. These status metrics are collected and stored using the standard mysqld-exporter format. In the backup metrics layer, custom backup metrics conforming to the Prometheus format are exposed through the Operator controller. Key metrics are as follows:

[0107] mysql_backup_status backup status #1=success, 0=failed, 2=scheduled

[0108] mysql_backup_duration_seconds backup usage time

[0109] mysql_backup_file_size backup file size

[0110] mysql_restore_status recovery status #1 = success, 0 = failure, 2 = scheduled

[0111] Furthermore, threshold monitoring is performed on status indicators and custom backup indicators related to database anomalies and backup / recovery task anomalies to determine early warning information. Specifically, Prometheus Server collects and stores status indicators and custom backup indicators. Anomaly thresholds are used to determine the first early warning information at the database layer by comparing the status indicators with those corresponding to database anomalies; similarly, anomaly thresholds are used to determine the first early warning information at the backup indicator layer by comparing the custom backup indicators with those corresponding to backup / recovery task anomalies. The early warning information includes both a first and a second warning. Finally, the early warning information is sent to the designated receiver via an early warning notification component.

[0112] In one embodiment, such as Figure 5 As shown, the collected status indicators and custom backup indicators in the Operator controller are processed and analyzed. Early warning thresholds are set, and when an indicator exceeds the threshold, an early warning mechanism is triggered, sending an alarm message via the Altermanager. The key early warning rules are as follows:

[0113]

[0114]

[0115] As a possible implementation method, such as Figure 5As shown, the monitoring and alerting module also supports notifications via email, webhooks, and instant messaging tools, categorized by alert level (warning / critical) to ensure timely response to critical issues. Monitoring data is also stored in the time-series database Prometheus for historical data querying and trend analysis. Furthermore, a monitoring dashboard can be built using Grafana to achieve visualized management of the MySQL database's running status and backup tasks, including a database running status monitoring panel and a backup task monitoring panel. The former displays instance status, resource usage, kernel metrics, slow queries, peak connection counts, and other key performance indicators; the latter presents backup task status, storage trends, success rates, etc., through Gantt charts, trend curves, and statistical charts.

[0116] That is, such as Figure 5 As shown, the monitoring module component is maintained through the Helm package manager. After being deployed to the Kubernetes cluster, it runs as a deployment, automatically collecting various monitoring metrics data of the MySQL database and custom backup metrics from the Operator controller module. Users can view the metric data through the monitoring dashboard. First, the Operator controller exposes custom backup metrics. Then, mysqld-exporter collects monitoring metrics of the MySQL database in the cluster. Next, the Prometheus server collects and stores the monitoring metrics data of the MySQL database instance collected by mysqld-exporter and the custom backup metrics. When a metric exceeds a threshold, an alert notification component (email, webhook, instant messaging tools, etc. API) is invoked to send an alert to the specified recipient. Finally, the database instance and backup monitoring data can be visualized in Grafana through the built-in monitoring dashboard.

[0117] S104. Based on backup and recovery logic control strategies and early warning information, the backup and monitoring management of MySQL databases that need to be backed up is completed.

[0118] In other words, this application embodiment is based on backup and recovery logic control strategies and early warning information to complete the backup and monitoring management of MySQL databases that need to be backed up. At the same time, it combines the designed resource definition module, Operator controller module and monitoring module, and based on Kubernetes Operator, realizes automatic discovery and automatic backup of MySQL databases in Kubernetes clusters, and supports one-click recovery, real-time monitoring of backup status and anomaly alarms. This system can not only solve the problems of complex operation, unobservable backup status, lack of monitoring and alarm system and complex recovery process in traditional MySQL database backup methods, but also realize more convenient and flexible configuration, backup data persistence, real-time monitoring and alarms, one-click recovery and other functions, significantly improving the reliability of MySQL database system in cluster.

[0119] In addition, this application embodiment also provides a backup and monitoring device for a MySQL database, such as... Figure 7 As shown, the MySQL database backup and monitoring device 700 specifically includes:

[0120] At least one processor 701. And a memory 702 communicatively connected to the at least one processor 701. The memory 702 stores instructions executable by the at least one processor 701, enabling the at least one processor 701 to execute:

[0121] Based on the backup and recovery strategies of the MySQL database, resource configuration processing is performed on the Kubernetes cluster under the relevant custom resource mechanism, and resource definition modules are generated and deployed.

[0122] Through the preset Operator controller, the status changes of custom resources in the resource definition module are monitored, and the backup scheduling and backup recovery processing of the MySQL database that needs to be backed up in the Kubernetes cluster are performed in accordance with the status of the custom resources, generating a backup and recovery logic control strategy based on the Operator controller.

[0123] The predefined monitoring and alarm module monitors and processes abnormal database states and abnormal indicators of backup and recovery tasks for the MySQL database and backup and recovery logic control strategies, and determines early warning information.

[0124] Based on backup and recovery logic control strategies and early warning information, the backup and monitoring management of MySQL databases that need to be backed up can be completed.

[0125] The various embodiments in this application are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the device and medium embodiments are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the description of the method embodiments.

[0126] The devices and media provided in this application are one-to-one with the methods. Therefore, the devices and media also have similar beneficial technical effects as their corresponding methods. Since the beneficial technical effects of the methods have been described in detail above, the beneficial technical effects of the devices and media will not be repeated here.

[0127] Those skilled in the art will understand that embodiments of this application can be provided as methods, systems, or computer program products. Therefore, this application can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this application 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.

[0128] This application is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of this application. 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... Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0129] 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.

[0130] 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.

[0131] In a typical configuration, a computing device includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0132] Memory may include non-persistent storage in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.

[0133] Computer-readable media includes both permanent and non-permanent, removable and non-removable media that can store information using any method or technology. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transient computer-readable media, such as modulated data signals and carrier waves.

[0134] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitations, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0135] The above description is merely an embodiment of this application and is not intended to limit the scope of this application. Various modifications and variations can be made to this application by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this application should be included within the scope of this specification.

Claims

1. A method for backing up and monitoring a MySQL database, characterized in that, The method includes: Based on the backup and recovery strategies of the MySQL database, resource configuration processing is performed on the Kubernetes cluster under the relevant custom resource mechanism, and resource definition modules are generated and deployed. The preset Operator controller monitors the status changes of custom resources in the resource definition module and performs backup scheduling and backup recovery processing on the MySQL database that needs to be backed up in the Kubernetes cluster, based on the status of the custom resources, to generate a backup and recovery logic control strategy based on the Operator controller. The predefined monitoring and alarm module monitors the MySQL database and the backup and recovery logic control strategy for abnormal database states and abnormal indicators of backup and recovery tasks, and determines early warning information. Based on the backup and recovery logic control strategy and the early warning information, the backup and monitoring management of the MySQL database that needs to be backed up is completed.

2. The method for backup and monitoring of a MySQL database according to claim 1, characterized in that, Before performing resource configuration processing on the Kubernetes cluster under the custom resource mechanism based on the MySQL database backup and recovery task strategies, and generating and deploying the resource definition module, the method further includes: Using the YAML file template in the Kubernetes resource orchestration specification and based on the tag selector, the backup data of the MySQL pod in the Kubernetes cluster is automatically discovered and configured, and the backup strategy of the MySQL database is defined and obtained; wherein, the backup data includes: backup cycle, number of backups to retain, backup storage information, MySQLCluster information, and backup type; The network address, port number, authentication information, and instance name of the target MySQL cluster in the target cluster are processed by the recovery task to obtain the target cluster configuration information. The local file system path, storage location, backup file name, backup set version information, and decryption key of the encrypted backup in the MySQL database are processed to identify the data for the recovery task, thus obtaining the backup source information. Based on the target cluster configuration information and the backup source information, the recovery task strategy for the MySQL database is defined and generated.

3. The method for backup and monitoring a MySQL database according to claim 1, characterized in that, Based on the MySQL database backup and recovery strategy, resource configuration is performed on the Kubernetes cluster using a custom resource mechanism, generating and deploying resource definition modules, specifically including: Based on the backup requirements of the MySQL database, a storage system is determined; wherein, the storage system includes any one or more of the following: PVC, MinIO, and NFS; The storage system is used to configure various recorded storage parameters, backup strategies, and recovery task strategies to obtain configuration completion information. The various storage parameters include at least: PVC name, storage endpoint, storage bucket name, access key, and encryption key. Based on the configuration completion information, and according to the YAML file template under the custom resource mechanism, modify the actual environment information of the file to be backed up; Based on the actual environment information, the YAML configuration file corresponding to the file to be backed up is applied to the Kubernetes cluster to generate the resource definition module.

4. The method for backup and monitoring a MySQL database according to claim 1, characterized in that, The system uses a pre-defined Operator controller to monitor state changes of custom resources in the resource definition module, specifically including: The Operator controller is deployed to a Kubernetes cluster; the Operator controller runs as a deployment. The Operator controller, based on the Watch mechanism of the Kubernetes cluster, monitors state changes of custom resources in the resource definition module: Distribute resource change events in the status changes of custom resource rows to the corresponding processing modules according to their types, and generate an event distribution strategy; Automatically retry tasks that fail to complete backup processing, and support configuring the number of retry times and intervals, as well as generating backup error handling strategies; Based on the event distribution strategy and the backup error handling strategy, the monitoring of state changes of the custom resource is completed.

5. The method for backup and monitoring a MySQL database according to claim 4, characterized in that, Perform backup scheduling and recovery processing on the MySQL databases requiring backup in the Kubernetes cluster under the aforementioned custom resource states, and generate backup and recovery logic control policies based on the Operator controller, specifically including: After listening to the status changes of the custom resources in the resource definition module, the status of the custom resources of the MySQL database that needs to be backed up is detected and processed according to the Operator controller. If the custom resource status is a creation or update event, then the backup strategy in the resource definition module is parsed; and based on the parsed backup strategy and the scheduled task, the backup task operation is triggered. The backup task is then classified. If the backup task is a full backup task, the mysqldump tool is called to generate a full SQL backup file, and the full SQL backup file is encrypted using an encryption algorithm. If the backup task is an incremental backup task, the mysqlbinlog tool is invoked to generate an incremental SQL backup file, and the incremental SQL backup file is encrypted using an encryption algorithm. When a backup task fails, it will automatically retry the backup process. By default, it will end after 3 failed attempts and push a backup failure alarm indicator. The storage system uploads the backup files after a successful backup task to the execution storage location and records the corresponding backup logs and backup indicator data. Regularly check the files in the backup storage and delete expired backup files according to the backup retention policy; Based on the above backup scheduling steps, a backup logic control strategy based on the Operator controller is generated; The backup logic control strategy and the recovery logic control strategy are combined to obtain the backup and recovery logic control strategy.

6. The method for backup and monitoring a MySQL database according to claim 5, characterized in that, Before combining the backup logic control strategy and the recovery logic control strategy to obtain the backup and recovery logic control strategy, the method further includes: If the custom resource status is a creation or update event and a database recovery task needs to be executed, then the recovery task strategy in the resource definition module is parsed; and the recovery task operation is triggered according to the parsed recovery task strategy and the task being used. The source data backup file is subjected to verification tests on integrity and availability to obtain a first test result; and the source data backup file is subjected to file permission checks, target database network checks, and user password checks to obtain a second test result. If both the first and second detection results are pass results, then the data backup file in the storage system is decompressed and decrypted through the Operator controller, and the decompressed and decrypted data backup file is restored to the target database cluster. When a recovery task fails, the backup will be automatically retried. By default, the process will end after 3 failed attempts and a recovery failure alarm will be pushed. Based on the above recovery scheduling steps, the recovery logic control strategy based on the Operator controller is generated.

7. The method for backup and monitoring a MySQL database according to claim 1, characterized in that, The predefined monitoring and alarm module monitors the MySQL database and the backup and recovery logic control strategy for abnormal database states and abnormal indicators of backup and recovery tasks, and determines early warning information, including: The status metrics of the MySQL database are obtained through mysqld-exporter; these status metrics include kernel metrics and key performance metrics. Collect custom backup metrics for the Operator controller used to execute the backup and recovery logic control strategy; The thresholds for database anomalies and backup / recovery task anomalies are monitored for the status indicators and the custom backup indicators to determine the early warning information.

8. A method for backing up and monitoring a MySQL database according to claim 7, characterized in that, The system monitors the threshold values ​​for database anomalies and backup / recovery task anomalies based on the status indicators and the custom backup indicators, and determines the early warning information, specifically including: The status metrics and the custom backup metrics are collected and stored through the Prometheus server. The status indicators are compared with the indicators corresponding to the abnormal state of the database to determine the abnormal threshold, and the first warning information of the database layer is obtained. The custom backup metrics and the backup and recovery task anomaly metrics are compared using an anomaly threshold to obtain a first warning message at the backup metrics layer; wherein, the warning message includes: the first warning message and the second warning message; The warning information is sent to the designated receiving end through the warning notification component.

9. A backup and monitoring device for a MySQL database, characterized in that, The device includes: At least one processor; and, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions executable by the at least one processor to enable the at least one processor to perform a backup and monitoring method for a MySQL database according to any one of claims 1-8.

10. A non-volatile computer storage medium, characterized in that, The storage medium is a non-volatile computer-readable storage medium that stores at least one program, each program including instructions that, when executed by a terminal, cause the terminal to perform a backup and monitoring method for a MySQL database according to any one of claims 1-8.

Images