Complete MySQL dashboard with Grafana & Prometheus

11.8K

Whether you are a system administrator or a database administrator, monitoring your MySQL server is an essential step towards diagnosing issues.

You want real time monitoring on active connections, locks or queries that are running on your database.

You also want to monitor active users, what they are running, as well as average query times.

To monitor those metrics, we are going to build a complete MySQL dashboard using modern tools such as Grafana and Prometheus.

Following this tutorial, you will build this dashboard and be presented with a set of 10+ MySQL dashboards created by Percona. You can bet that there will be a dashboard for your needs!

I – What You Will Learn

If you follow this tutorial until the end, you will learn the following points:

• How to install and configure a Prometheus server on your Linux machine;
• How to setup the MySQL server exporter and how to bind it to Prometheus;
• What a basic Prometheus monitoring architecture looks like;
• How to configure Grafana to import Percona’s MySQL dashboards in minutes.

Ready?

II – MySQL, Grafana & Prometheus Architecture

Before starting out, it is essential that you have a clear view of what a Prometheus monitoring architecture looks like.

If you followed my definitive guide on Prometheus, you already know that Prometheus works with exporters. Exporters are meant to bind to existing datasources to retrieve metrics from them.

Examples of exporters would be the MongoDB exporter, the ElasticSearch exporter or in our case the MySQL exporter.

Exporters are exposed as Docker images or as standalone binaries that you can run as background tasks or services.

In our case, we are going to use the MySQL exporter that is available on Prometheus official github page.

The MySQL exporter binds to our MySQL instance and exposes metrics directly for Prometheus to consume.

As part of its configuration, Prometheus is going to bind to it and scrape metrics from it. From there, they will be visible on Grafana.

Pretty simple, right?

Now that you better understand how we are going to build it, let’s install all the tools that you need to create your MySQL dashboard with Grafana.

III – Installing The Different Tools

a – Installing Prometheus

For those who are new to Prometheus, we covered Prometheus installation in our previous tutorial.

From this point, your Prometheus instance should be up and running with your configuration files on /etc/prometheus/prometheus.yml.

To verify it, head over to http://localhost:9090. You should see Prometheus web interface running. If not, something is definitely wrong in your installation.

For now, no metrics are currently stored in your Prometheus instance because we did not set the MySQL exporter.

That’s what we are going to do next.

b – Installing the MySQL exporter

As detailed before, the MySQL exporter is available here. It is actually an official exporter created by Prometheus itself.

The MySQL exporter comes as a standalone binary, but we are going to configure it as a service.

First, create a Prometheus user on your instance if it is not already existing.

> sudo useradd -rs /bin/false prometheus

As a quick reminder, with this command, you will create a system (-r) user named prometheus with a no shell access (-s) This is the user you will use to create your MySQL exporter service.

First, download the latest MySQL exporter binaries on your instance.

All distributions are available here. Find the one that suits your needs and run:

> wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.11.0/mysqld_exporter-0.11.0.linux-amd64.tar.gz

Now that your binaries are download, extract them in your current folder.

> tar xvzf mysqld_exporter-0.11.0.linux-amd64.tar.gz

Move the binaries to the /usr/local/bin folder that we are going to build a service out of it. You need sudo rights to perform this operation.

> cd mysqld_exporter-0.11.0.linux-amd64/
> sudo mv mysqld_exporter /usr/local/bin/

From there, you should be able to create a user for the exporter on your MySQL database.

Run the mysql shell, and configure your database as follows (you should have the rights to grant permissions on your database).

> sudo mysql
> CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'password' WITH MAX_USER_CONNECTIONS 3;
> GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';

Running those commands, you will create an exporter user with a ‘password’ password on mysql. Now, you are going to set those variables in a configuration file.

In your /etc folder, create a new configuration file named .exporter.cnf and write the following credentials to it.

> cd /etc
> sudo vi .exporter.cnf

Set the credentials in the configuration file as follows:

[client]
user=exporter
password=password

(If you set different users or passwords in the step before, you need to reflect the changes in the configuration file.)

c – Creating a MySQL exporter service

Now that everything is ready, it is time to create a service for your MySQL exporter. Head over to /lib/systemd/system and create a new service file.

> sudo vi /lib/systemd/system/mysql_exporter.service

Paste the following configuration into it:

[Unit]
Description=MySQL Exporter
User=prometheus

[Service]
Type=simple
Restart=always
ExecStart=/usr/local/bin/mysqld_exporter \
--config.my-cnf /etc/.exporter.cnf \
--collect.auto_increment.columns \
--collect.binlog_size \
--collect.engine_innodb_status \
--collect.engine_tokudb_status \
--collect.global_status \
--web.listen-address=0.0.0.0:9104

[Install]
WantedBy=multi-user.target

Restart your system daemon and start your service.

> sudo systemctl daemon-reload
> sudo systemctl status mysql _exporter.service

Check that your service is running by issuing the following command:

> sudo systemctl status mysql_exporter
● mysql_exporter.service - MySQL Exporter
   Loaded: loaded (/lib/systemd/system/mysql_exporter.service; disabled; vendor preset: enabled)
   Active: active (running) since Sat 2019-06-08 15:11:12 UTC; 5min ago
 Main PID: 3136 (mysqld_exporter)
    Tasks: 8 (limit: 4704)
   CGroup: /system.slice/mysql_exporter.service
           └─3136 /usr/local/bin/mysqld_exporter --config.my-cnf /etc/.exporter.cnf

Congratulations!

Your MySQL exporter is all set. Now it is time to configure Prometheus to scrape it.

Quick note : your MySQL exporter runs on port 9104 and MySQL runs on port 3306 by default.

d – Configuring Prometheus

Prometheus scrapes targets via its configuration file. As we added a new exporter, let’s add it to Prometheus configuration.

Head over to the location of your Prometheus configuration file and edit it as follows:

# A scrape configuration containing exactly one endpoint to scrape:
# Here it's Prometheus itself.
scrape_configs:
  # The job name is added as a label `job=<job_name>` to any timeseries scraped from this config.
  - job_name: 'prometheus'
    static_configs:
            - targets: ['localhost:9090', 'localhost:9104']

Restart Prometheus, and head over to Prometheus Web Interface (http://localhost:9090).

Go to the targets tab, and make sure that Prometheus is correctly scrapping the MySQL exporter target.

Back to the ‘graph’ tab, in the expression text field, type ‘mysql_exporter_scrapes_total‘. If you see a result there, it means that your Prometheus instance is correctly configured to extract metrics from the exporter.

Congratulations! The entire configuration is now done!

e – Installing Grafana

Grafana will be used to display our metrics. The steps to install Grafana were detailed in one of our articles already, make sure to read it before you continue.

If you are looking for a tutorial to install it on Ubuntu 18.04, I wrote a detailed Grafana installation guide for Ubuntu users.

IV – Create the MySQL dashboard with Grafana

In this case, you have essentially two choices:

• Create your own customized Grafana dashboard: tailored to your needs, you can choose what metrics you want to display and how you want to display them.
• Use existing community dashboards and save some time.

In this tutorial, we are going to go for option two. We are going to use awesome Percona MySQL dashboards and import them right into our Grafana.

a – Configuring Prometheus data source

Before starting out, and if you did not do it already, you need to configure Prometheus as a Grafana data source.

Create a new datasource, and configure it as follows:

If your Prometheus instance is not running on port 9090 by default, make sure to write the correct port in the configuration.

Now that Prometheus is configured, we can browse Percona existing dashboards and import one of the existing MySQL dashboard in Grafana.

Percona dashboards are available here. You can also play with the existing dashboards on Percona’s own Grafana instance here.

In the dashboards folder of Percona’s github, download the json file that you are interested in.

> wget https://github.com/percona/grafana-dashboards/blob/master/dashboards/MySQL_Overview.json

Now that your dashboard is downloaded, in Grafana, go to Dashboards > Import > Upload .json file.

If you press “Import”, you entire MySQL dashboard will be created automatically and it will start displaying data immediately!

This is the MySQL overview dashboard, but there are more than 20+ dashboards for you to choose from.

Here the complete list of dashboards created by Percona:

V – Going Further with MySQL and Grafana

If you want to dig a little bit more into the subject, there are many videos that can help you having an in-depth understanding on how companies are building dashboards, especially MySQL dashboards with Grafana.

Very recently, in February 2019, Peter Zaitsev (CEO at Percona) made a very great talk about it. The entire business model of Percona is built on monitoring MySQL, MongoDB and MariaDB at scale.

A second use-case is Verizon using Grafana and MySQL and demonstrated how it can optimize automation and self-service in a very practical way.

I hope that you learned something new today. If you did, make sure to leave us a comment on how you plan on using this knowledge to your own specific needs.

Until then, have fun, as always.