Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

In the previous article, we introduced the basic concepts of Grafana and Prometheus and how to monitor Spring Boot applications. In this article, we will introduce Prometheus + Grafana to monitor MySQL, providing a quick and easy way to see the connection count, lock, memory, and network indicators, which can be used to detect MySQL bottlenecks, deadlocks, and other problems.

1. Prometheus

Prometheus is an open source monitoring system. Compared with other monitoring systems, Prometheus has many functions, such as ease of management, internal running status of monitoring services, powerful data model, powerful query language PromQL, high efficiency, scalability, easy integration, visualization, and openness.

1.1 Exporter

Any program that can provide monitoring sample data to Prometheus can be referred to as a Exporter. An instance of My Exporter is called Target, from which Prometheus regularly obtains sample data through polling:

At least half can be a community provider, including databases (MySQL, Redis, etc.), message queues (Kafka, RabbitMQ, etc.), storage, HTTP services, and logs. Monitoring services, etc. Another is a user – defined Exporter, where users can create their own Exporter programs based on the Client Library provided by Prometheus.

1.2 MySQLD Exporter

Prometheus provides MySQLD, which monitors and measures MySQL database performance and resource utilization.

Docker-comemess. yml: docker-comemess. yml: Docker-comemess. yml: docker-comemess. yml

version: '3'
services:
  mysqlexporter:
    image: prom/mysqld-exporter
    ports:
      - "9104:9104"
    environment:
      - DATA_SOURCE_NAME=root:password@(mysql:3306)/database
Copy the code

Run the docker-compose up -d command to start my Exporter.

1.3 configurationprometheus.yml

In addition to enabling MySQLD, the Prometheus configuration file must be modified to add the following configuration to the promethe. yml file to be added to the monitor.

- job_name: jobName
  static_configs:
  - targets:
    - localhost:9104
Copy the code

Then restart Prometheus with the following name:

docker-compose down
docker-compose up -d
Copy the code

And then, you can enter http://127.0.01:9090/targets in your browser, we see Tragets has been added successfully.

Click Graph to see the monitored MySQL metrics:

The next step is to add the data source in Grafana and import the dashboard template.

2. Grafana

The introduction and installation of Grafana will not be detailed here, but you can read the next article if you are not clear. In general, the main task is to install Grafana, create data sources, and introduce dashboard templates. Let’s start by creating the data source directly.

2.1 Creating a Data Source

First, open grafana’s address in your browser and click Add Data Source.

Choose the Prometheus.

Enter the data source name, data source address, and click Add.

2.2 Creating a Dashboard

Click “Create” on the left menu bar, click “Import”, of course, you can also choose to create dashboard here, select your own components and custom layout to complete.

Here we need to fill in the dashboard components we need to import. Go to the Grafana dashboard template site in your browser and search for MySQL.

Choose a template that we like, here we choose the first one and click in.

Duplicate the dashboard ID. This is 7362.

Enter the name of the dashboard, select the data source, and click Import to successfully create the dashboard.

The resulting dashboard looks like this:

Reference 3.

  1. What is the Exporter