Monitor MySQL with Managed Service for Prometheus-Managed Service for Prometheus(Prometheus)-阿里云帮助中心

Prerequisites

Ensure you have the connection credentials for your MySQL instance, including the address, port, username, and password.

Integrate MySQL monitoring

In the left-side navigation pane, click Integration Center.
On the Integration Center page, in the Database section, click MySQL.

On the MySQL panel, on the Start Integration tab, configure the parameters and click OK.

Parameter	Description
Select the environment type.	Select the deployment environment of your MySQL instance. Valid values: Kubernetes environment ECS (VPC) Cloud services
Select Cluster	Select the target cluster.
Select ECS (VPC)	Select the target ECS instance.
Select Region for Storage	If you set Select the environment type. to Cloud Services, you must select the region in which to store the monitoring data.
MySQL service address	The connection address of your MySQL instance. Note Supports MySQL instances deployed in Container Service for Kubernetes (ACK), on ECS instances, or in ApsaraDB RDS.
MySQL service port	The port number of your MySQL instance. For example: 3306.
MySQL username and MySQL password	The username and password for connecting to your MySQL instance. Important To prevent security risks such as data leaks, do not use an administrator account. Instead, create a dedicated MySQL account for the mysqld_exporter with the minimum required permissions. For more information, see Create a MySQL access account for Prometheus.

Configure MySQL alert rules

Log on to the Cloud Monitor console.
In the navigation pane on the left, choose Managed Service for Prometheus > Instances to open the instance list for Managed Service for Prometheus.
At the top of the page, select the region where your instance is located and click the instance name.
In the left-side navigation pane, click Alarm Rules to view the Prometheus alert rules for MySQL.
Managed Service for Prometheus provides several default alert rules for key MySQL metrics. You can also create custom alert rules based on your business needs. For more information, see Create an alert rule for Managed Service for Prometheus.
Note
- For more information about key MySQL metrics, see the Key metrics section.
- For more information about the pre-configured alert rules that Managed Service for Prometheus provides for key MySQL metrics, see the Pre-configured metric monitoring and alerting system section.

View MySQL dashboards

You can view monitoring data on the dashboards, such as availability, database queries, network traffic, connections, and memory. To do so, perform the following steps:

Log on to the Cloud Monitor console.
In the left-side navigation pane, click Integration Management.
On the Integration Management page, click the Integrated Environments tab, select the target environment, and then click the name of the target environment in the list to open its details page.

Environments are classified into Kubernetes Environment, ECS Environment, and Cloud Services Region Environment.
On the Component Management tab, in the Addon Type section, click MySQL, and then click Dashboard to view all dashboard names.
Click a dashboard name to view the corresponding Grafana dashboard.
- Availability, queries per second (QPS), and database connections.
- Database queries.
- Traffic and memory usage.
- File monitoring data.

Key metrics

Type	Metric	Description
Availability	mysql_up	Indicates whether the MySQL instance is running. A value of 1 indicates the instance is up; 0 indicates it is down.
Availability	mysql_global_status_uptime	The duration, in seconds, that the database instance has been running. This metric is often used to configure an alert for instances that have been running for less than a specified duration, such as 30 minutes.
Database connections	mysql_global_status_connection_errors_total	The total number of connection errors. This metric helps you track the occurrence of connection errors, which are a common type of database issue.
	mysql_global_status_threads_connected	The number of currently connected client threads.
	mysql_global_status_threads_running	The number of threads that are not sleeping.
	mysql_global_status_max_used_connections	The peak number of connections that have been in use simultaneously since the server started.
	mysql_global_variables_max_connections	The maximum number of permitted client connections. New connection requests are rejected if this limit is reached.
	mysql_global_status_aborted_connects	The number of failed connection attempts to the MySQL server.
	mysql_global_status_aborted_clients	The number of connections that were aborted because the client died without closing the connection properly.
Query	mysql_global_status_slow_queries	The number of queries that have taken more than `long_query_time` seconds.
Query	mysql_global_status_queries	The total number of statements executed by the server.
Traffic	mysql_global_status_bytes_received	The number of bytes received from all clients.
Traffic	mysql_global_status_bytes_sent	The number of bytes sent to all clients.
File	mysql_global_status_opened_files	The total number of files opened by the server since startup.
	mysql_global_status_open_files	The number of files that are currently open.
	mysql_global_variables_open_files_limit	The maximum number of files that the operating system allows the mysqld server to open.
	mysql_global_status_innodb_num_open_files	The number of files that InnoDB currently holds open.

Pre-configured metric monitoring and alerting system

Setting up a self-managed Prometheus system for MySQL monitoring is a complex process that requires you to deploy an exporter, provide MySQL connection details, configure service discovery, and build dashboards. Managed Service for Prometheus simplifies this by integrating the MySQL exporter with a one-click setup. It provides out-of-the-box dashboards and pre-configured alerts through a web-based console.

Managed Service for Prometheus provides pre-configured templates for common MySQL alert rules to help you quickly establish a robust monitoring and alerting system. The pre-configured MySQL alert rules are as follows:

MySQL down: Triggers an alert if a MySQL instance is down. A value of 0 indicates that the instance is down, and a value of 1 indicates that the instance is running. You can use ${instance} to target a specific instance for the alert.
```
mysql_up{${instance}} != 1
```
MySQL slow query: This metric helps identify SQL statements that require optimization.
```
rate(mysql_global_status_slow_queries{${instance}}[5m]) > 0
```
MySQL connection errors: Connection errors are a common type of database issue. With the alert rules provided by Managed Service for Prometheus, you receive alerts that contain information such as the error type and query count when an alert is triggered.
```
rate(mysql_global_status_connection_errors_total{${instance}}[5m]) > 0
```
MySQL Connection Usage: High connection usage can be caused by an insufficient number of available connections. You can check the MySQL connection usage to further troubleshoot the issue.
```
100 * mysql_global_status_threads_connected{${instance}} 
      / mysql_global_variables_max_connections{${instance}} > 90
```
When the connection usage reaches the specified threshold, the MySQL instance starts to reject new connections. You can resolve this issue by increasing the maximum number of connections. However, before you increase the connection limit, we recommend that you run the following statement to check the number of files that the system can open:
```
mysql_global_variables_open_files_limit - mysql_global_variables_innodb_open_files
```
MySQL InnoDB log waits: The rate of waits for the InnoDB log buffer to be available.
```
rate(mysql_global_status_innodb_log_waits{${instance}}[5m])
```