How to use Prometheus to monitor MySQL - Managed Service for Prometheus

Prerequisites

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

Enable MySQL monitoring

In the left navigation pane, click Access Center.
On the Access Center page, in the Database section, click MySQL.

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

Parameter	Description
Select the environment type.	Select one of the following environment types: 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 a region for data storage.
MySQL address	The connection address of the MySQL instance. Note Managed Service for Prometheus supports MySQL instances that are deployed in Container Service for Kubernetes (ACK), ECS, or ApsaraDB RDS.
MySQL service port	The port number of the MySQL instance. Example: 3306.
MySQL username and MySQL Password	The username and password for the MySQL account. Important To prevent data leaks and other security risks, we recommend that you do not use an administrator account. Instead, create a dedicated MySQL account for the mysqld_exporter and grant it only the minimum required permissions. For more information, see Create a MySQL user for Managed Service for Prometheus.

Configure alerting for MySQL

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 target region, and then click the name of the target cluster.
In the left navigation pane, click Alert Rules to view the Prometheus alert rules for MySQL.
Managed Service for Prometheus provides multiple default Prometheus alert rules for key MySQL metrics. You can also add new alert rules based on your business needs. For more information, see Create a Prometheus alert rule.
Note
- For more information about key MySQL metrics, see the Key metrics section below.
- For details about the preset alert rules that Managed Service for Prometheus provides for key MySQL metrics, see the Build a metric and alerting system section below.

View the MySQL dashboard

The monitoring dashboard displays metrics for availability, database queries, network traffic, connections, and memory. Follow these steps:

Log on to the Cloud Monitor console.
In the left navigation pane, click Data Import Management.
On the Data Import Management page, click the Integrated Environments tab. Select the target environment and click its name in the list to open its details page.

The Integrated Environments tab includes three categories: Kubernetes Environment, ECS Environment, and Cloud Service Region Environment. Select the appropriate category.
On the Component Management tab, in the Addon Type section, click MySQL, and then click Dashboards to view all dashboard names.
Click a dashboard name to view the corresponding Grafana dashboard.
- Availability, QPS, and database connections.
- Database queries.
- Network traffic and memory usage.
- File monitoring data.

Key metrics

Type	Metric	Description
Availability	mysql_up	Indicates whether the MySQL instance is up and running. A value of 1 means the instance is available, while a value of 0 indicates it is down.
Availability	mysql_global_status_uptime	The uptime of the database instance. You can use this metric to create an alert that triggers if an instance has been running for less than 30 minutes, which may indicate a recent restart.
Database connection	mysql_global_status_connection_errors_total	Connection errors are one of the major types of database errors. This metric helps you view details about specific connection errors and their frequency.
	mysql_global_status_threads_connected	The number of currently open connections to the MySQL instance.
	mysql_global_status_threads_running	The number of threads that are actively processing requests.
	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 concurrent client connections allowed. New connection requests are refused 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 closed 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. This can be used to calculate queries per second (QPS).
Network traffic	mysql_global_status_bytes_received	Inbound network traffic in bytes.
Network traffic	mysql_global_status_bytes_sent	Outbound network traffic in bytes.
File	mysql_global_status_opened_files	The total number of files that have been opened.
	mysql_global_status_open_files	The number of files that are currently open.
	mysql_global_variables_open_files_limit	The maximum number of file descriptors available to the MySQL server.
	mysql_global_status_innodb_num_open_files	The number of files currently held open by the InnoDB storage engine.

Build a metric and alerting system

Setting up a self-managed Prometheus system for MySQL monitoring is a complex process. This process requires deploying an exporter, providing MySQL instance connection details, configuring service discovery, and building dashboards. Managed Service for Prometheus simplifies this by integrating the MySQL exporter with a one-click setup. It also provides ready-to-use monitoring dashboards and alert rules through a visual console, significantly reducing the configuration workload.

Managed Service for Prometheus includes preset alert rule templates for common MySQL metrics to help you quickly build a monitoring and alerting system. The following preset rules are available:

MySQL Availability: If the value of this metric is 0, the database is not running correctly. A value of 1 indicates normal operation. You can use ${instance} to target a specific instance for alerting.
```
mysql_up{${instance}} != 1
```
MySQL Slow Queries: This metric helps you identify if there are any SQL statements in the database that require optimization.
```
rate(mysql_global_status_slow_queries{${instance}}[5m]) > 0
```
MySQL connection errors: Connection errors are a major type of database error. With the alert rules provided by Managed Service for Prometheus, you can receive alert information, such as the error type and the number of queries, when an alert is triggered.
```
rate(mysql_global_status_connection_errors_total{${instance}}[5m]) > 0
```
MySQL Connection Usage: High connection usage can cause connection errors. This alert helps you monitor usage to troubleshoot these issues.
```
100 * mysql_global_status_threads_connected{${instance}} 
      / mysql_global_variables_max_connections{${instance}} > 90
```
When the usage rate reaches a certain threshold, the MySQL instance starts rejecting new connections. You can resolve this by increasing the connection limit. Before you increase the connection limit, run the following statement to check the number of available file descriptors.
```
mysql_global_variables_open_files_limit - mysql_global_variables_innodb_open_files
```
MySQL InnoDB Log Wait Time: Monitors the delay on log writes. You can configure the alert threshold.
```
rate(mysql_global_status_innodb_log_waits{${instance}}[5m])
```