Setting Up MySQL Server Exporter and Integrating with Prometheus and Grafana

In this guide, we'll walk through the steps to set up MySQL Server Exporter, integrate it with Prometheus for scraping metrics, and visualize the data on Grafana. Additionally, we'll cover how to set up alerts for monitoring MySQL metrics.

Step 1: Setting Up MySQL Server Exporter

Download the MySQL Exporter Binary: Log in to your machine and download the relevant MySQL Exporter binary:

wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.12.1/mysqld_exporter-0.12.1.linux-amd64.tar.gz        

Replace v0.12.1.linux-amd64 with the version you’d like to install.

Unzip the Tarball and Copy the File:

tar xvfz mysqld_exporter-*.*-amd64.tar.gz
sudo cp mysqld_exporter-*.*-amd64 /usr/local/bin        

Create the MySQL User:

Log in to your MySQL database and run the following command to create a user:

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

Set Environment Variable:

export DATA_SOURCE_NAME='exporter:enter_password_here@(mysql_hostname:3306)/'        

Replace exporter with the user you created, enter_password_here with the password, and mysql_hostname with the hostname of the MySQL instance.

Run the Exporter:

./mysqld_exporter        

Test the Exporter: Open a new SSH session or background the MySQL Exporter process and use curl to test:

curl https://localhost:9104/metrics        

Create a systemd Service: Add mysqld_exporter user and grant permissions:

sudo useradd --no-create-home --shell /bin/false mysqld_exporter
sudo chown mysqld_exporter:mysqld_exporter /usr/local/bin/mysqld_exporter        

Create a service file at /etc/systemd/system/mysqld_exporter.service:

[Unit]
Description=MySQL Exporter
Wants=network-online.target
After=network-online.target

[Service]
User=mysqld_exporter
Group=mysqld_exporter
Environment=DATA_SOURCE_NAME='exporter:Password@12345@(localhost:3306)/'
ExecStart=/usr/local/bin/mysqld_exporter

[Install]
WantedBy=multi-user.target
        

Reload the system manager configuration:

sudo systemctl daemon-reload        

Start the service:

sudo systemctl start mysqld_exporter.service        

Step 2: Scraping MySQL Exporter Using Prometheus

Configure Prometheus Scrape Job: Now MySQL Exporter is up and running on your machine, you can configure a Prometheus scrape job to collect and store MySQL Exporter metrics.

scrape_configs:
  - job_name: 'mysql'
    static_configs:
    - targets: ['mysql_exporter_machine_IP_address:9104']        

Replace mysql_exporter_machine_IP_address with the IP address of the machine running MySQL Exporter.

Step 3: Shipping Metrics to Grafana

Import the Dashboard: We have already configured the Prometheus datasource on Grafana. Import the dashboard with ID 14057 and use the same Prometheus datasource.

We can see the metrics data in this dashboard now.

Step 4: Setting Up Alerts (Connection and Threads Connected Count)

1. Configure Alerts in Grafana:

After clicking on alert , we can see the option alerts. Query metrics for the specified alerts will be generated as it is using the dashboard.

click on create alert for this rule.

Alert Configuration Page will look like this:

Before configuring the alerts we can test whether the alerts have been set correctly or not by using the preview alerts option.

Now, we have to add the basic alert details like rule-name, folder and groups.

We have used templating (written in go language) for formatting the alerts. In description

the following block is written.

{{ with $values }}
{{ range $k, $v := . }}
   Current Connections: {{ $v }}
{{ end }}
{{ end }}        

This block will print the value of the legend (in our case it is connections).

folders and groups for the specific alerts will give much clearance to identify the alerts.

Labels plays an important role as the same labels should be used in the next steps (notifications policies)

We have successfully configured the alert rule, now we need to set contact policies for sending our alerts to the slack channel.

This is the template we have used for the alert formatting. The description will be called from this template.

{{ define "alert_severity_prefix_emoji" -}}
	{{- if ne .Status "firing" -}}
		:white_check_mark:
	{{- else if eq .CommonLabels.severity "critical" -}}
		:red_circle:
	{{- else if eq .CommonLabels.severity "warning" -}}
		:warning:
	{{- end -}}
{{- end -}}
{{ define "slack1" }}
  {{- range .Alerts -}}
  *Alert*: {{ .Labels.alertname }}
   Status: {{ if eq .Status "firing" }}ALERT{{ else }}RESOLVED{{ end }}
   Severity: {{ .Labels.severity }}
   Description: {{ .Annotations.description }}
  {{ end  }}
{{ end }}        

We need to enter our slack webhook token , for the authentication.

In Optional slack settings we need to call which template this alert should be used. In our case it is slack1.

The next step is to configure the notification policies. Click on create new.

Select add matcher and add the labels which we have added alert rules section.

Our Configuration is ready and we can test the alert by reducing the threshold value.

Below are some alerts which have been configured and working properly.

This guide provides a comprehensive overview of setting up MySQL Server Exporter, integrating it with Prometheus and Grafana, and configuring alerts for monitoring MySQL metrics. For detailed alert rules, refer to the Alert Rules Document.