How to Monitor MySQL Deployments with Prometheus & Grafana at ScaleGrid
Tags: mysql, database, monitoring, alerts
Published: 2020-01-03 20:56:19

---
title: How to Monitor MySQL Deployments with Prometheus & Grafana at ScaleGrid
published: true
description: In this post, we discuss how to set up open source Prometheus event monitoring with Grafana dashboard to visualize your MySQL database performance at ScaleGrid.
tags: MySQL, Database, Monitoring, Alerts,
canonical_url: https://scalegrid.io/blog/how-to-monitor-mysql-deployments-with-prometheus-and-grafana-at-scalegrid/
---

<link rel="canonical" href="https://scalegrid.io/blog/how-to-monitor-mysql-deployments-with-prometheus-and-grafana-at-scalegrid/" />

<a href="https://scalegrid.io/blog/how-to-monitor-mysql-deployments-with-prometheus-and-grafana-at-scalegrid/"><img class="aligncenter wp-image-5040 size-full" src="https://scalegrid.io/blog/wp-content/uploads/2020/01/How-to-Monitor-MySQL-Deployments-with-Prometheus-and-Grafana-at-ScaleGrid.jpg" alt="How to Monitor MySQL Deployments with Prometheus & Grafana at ScaleGrid" width="100%" height="auto" /></a>

<p style="text-align: justify;">Monitoring your <a style="color: #2da964;" href="https://scalegrid.io/mysql.html" target="_blank">MySQL database</a> performance in real-time helps you immediately identify problems and other factors that could be causing issues now or in the future. It's also a good way to determine which components of the database can be enhanced or optimized to increase your efficiency and performance. This is usually done through monitoring software and tools either built-in to the database management software or installed from third-party providers.</p>
<p style="text-align: justify;"><a style="color: #2da964;" href="https://prometheus.io/" target="_blank">Prometheus</a> is an open-source software application used for event monitoring and alerting. It can be used along with a visualization tool like <a style="color: #2da964;" href="https://grafana.com/" target="_blank">Grafana</a> to easily create and edit dashboards, query, visualize, alert on, and understand your metrics. ScaleGrid provides full admin access to your MySQL deployments - this makes it easier to integrate the existing MySQL ecosystem of tools with your ScaleGrid MySQL deployments on <a title="MySQL Hosting on AWS" style="color: #2da964;" href="https://scalegrid.io/mysql/aws.html" target="_blank">AWS</a> or <a title="MySQL Hosting on Azure" style="color: #2da964;" href="https://scalegrid.io/mysql/azure.html" target="_blank">Azure</a>. Prometheus works well for recording any purely numeric time series, and also offers support for multi-dimensional data collection and querying. Grafana can be used with it to build dashboards that help visualize this data in a way that is easy to interpret and utilize. These tools will provide additional insight to your metrics, usage patterns, and datasets along with your ScaleGrid <a style="color: #2da964;" href="https://help.scalegrid.io/docs/mysql-monitoring-console" target="_blank">MySQL monitoring</a>, <a style="color: #2da964;" title="MySQL Slow Query Analyzer" href="https://help.scalegrid.io/docs/mysql-monitoring-slow-query-analyzer" target="_blank">query analysis</a>, and <a style="color: #2da964;" title="MySQL Alerts &amp; Rules" href="https://help.scalegrid.io/docs/mysql-alerts-rules" target="_blank">alerts</a>. In this blog post, we discuss how you can set up and use <a href="https://scalegrid.io/blog/how-to-monitor-mysql-deployments-with-prometheus-and-grafana-at-scalegrid/">Prometheus and Grafana with your ScaleGrid MySQL deployments</a> for advanced database monitoring and alerting.</p>

<h2 style="padding-top: 15px;">How to Set Up Your MySQL Monitoring</h2>
<p style="text-align: justify;">Let’s walk through the steps involved in installing and configuring the Prometheus server to store and display the metrics, an exporter (MySQL Exporter in this case) to collect the metrics and relay them to the Prometheus server, and Grafana to create dashboards. The <a style="color: #2da964;" href="https://github.com/braedon/prometheus-mysql-exporter" target="_blank">MySQL Exporter</a> tool can be installed locally on a MySQL server or centrally on the Prometheus server. In the use case below, we will explain how to set up and start using Prometheus, MySQL Exporter, and Grafana from a central host running on <a style="color: #2da964;" href="https://ubuntu.com/" target="_blank">Ubuntu</a> to monitor multiple MySQL servers. You can also review step-by-step instructions in our <a title="Prometheus and Grafana for MySQL" style="color: #2da964;" href="https://help.scalegrid.io/docs/mysql-connecting-to-prometheus-and-grafana" target="_blank">Prometheus and Grafana for MySQL</a> help doc.</p>
<p style="text-align: justify;">The block diagram below shows the setup of a master-slave-quorum MySQL deployment that includes two data-bearing nodes (master and slave) and one voting member (quorum) using the MySQL Exporter, Prometheus host, and Grafana:</p>
<a href="https://scalegrid.io/blog/wp-content/uploads/2020/01/prometheus-block-diagram.jpg"><img class="aligncenter wp-image-5040 size-full" src="https://scalegrid.io/blog/wp-content/uploads/2020/01/prometheus-block-diagram.jpg" alt="Prometheus block diagram - host for MySQL master slave quorum deployment with Grafana" width="100%" height="auto" /></a>
<h3 style="padding-top: 15px;">Installing &amp; Configuring the Prometheus Server</h3>
<p style="text-align: justify;">Prometheus is the tool we will be using to centralize and store your MySQL metrics. It scrapes the metrics from one or several exporters at regular intervals and displays it on its UI. Below are the steps to install and configure Prometheus on a central Ubuntu host. For more details, you can refer this <a style="color: #2da964;" href="https://computingforgeeks.com/install-prometheus-server-on-centos-7/" target="_blank">article</a>.</p>

<h4 style="padding-top: 15px;">1. Create a Prometheus System Group &amp; User</h4>
<pre>$sudo groupadd --system prometheus
$sudo useradd -s /sbin/nologin --system -g prometheus prometheus</pre>
<h4 style="padding-top: 15px;">2. Create a Data Directory for Prometheus</h4>
<pre>$sudo mkdir /var/lib/prometheus</pre>
<h4 style="padding-top: 15px;">3. Create Configuration Directories for Prometheus</h4>
<pre>$for i in rules rules.d files_sd; do sudo mkdir -p /etc/prometheus/${i}; done</pre>
<h4 style="padding-top: 15px;">4. Download the Prometheus Archive &amp; Extract the File</h4>
<p style="text-align: justify;">To download the latest binary archive for Prometheus:</p>

<pre>$mkdir -p /tmp/prometheus &amp;&amp; cd /tmp/Prometheus
$curl -s https://api.github.com/repos/prometheus/prometheus/releases/latest \
| grep browser_download_url \
| grep linux-amd64 \
| cut -d '"' -f 4 \
| wget -qi -</pre>
<p style="text-align: justify;">To extract the file:</p>

<pre>$tar xvf prometheus*.tar.gz
$cd prometheus*/</pre>
<h4 style="padding-top: 15px;">5. Move the Prometheus Files to Standard Locations</h4>
<p style="text-align: justify;">Move Prometheus binary files to /usr/local/bin:</p>

<pre>$sudo mv prometheus promtool /usr/local/bin/</pre>
<p style="text-align: justify;">Move Prometheus configuration template to /etc directory:</p>

<pre>$sudo mv prometheus.yml  /etc/prometheus/prometheus.yml</pre>
<p style="text-align: justify;">Also move consoles and console_libraries to /etc/prometheus directory:</p>

<pre>$sudo mv consoles/ console_libraries/ /etc/prometheus/</pre>
<h4 style="padding-top: 15px;">6. Create/Edit a Prometheus Configuration File</h4>
<pre>$sudo vim /etc/prometheus/prometheus.yml</pre>
<p style="text-align: justify;">The template configurations should look similar to below:</p>

<pre> #my global config
 global:
 scrape_interval: 15s # Set the scrape interval to every 15 seconds. Default is every 1 minute.
 evaluation_interval: 15s # Evaluate rules every 15 seconds. The default is every 1 minute.
 #scrape_timeout is set to the global default (10s).

 #Alertmanager configuration
 alerting:
 alertmanagers:
 - static_configs:
 - targets:
 #- alertmanager:9093

 #Load rules once and periodically evaluate them according to the global 'evaluation_interval'.
 rule_files:
 #- "first_rules.yml"
 #- "second_rules.yml"

 #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=&lt;job_name&gt;` to any timeseries scraped from this config.
 - job_name: 'prometheus'

 #metrics_path defaults to '/metrics'
 #scheme defaults to 'http'.

 static_configs:
 - targets: ['localhost:9090']</pre>
<h4 style="padding-top: 15px;">7. Create a Prometheus systemd Service Unit File</h4>
<pre>$cat /etc/systemd/system/prometheus.service 

 [Unit]
 Description=Prometheus
 Documentation=https://prometheus.io/docs/introduction/overview/
 Wants=network-online.target
 After=network-online.target

 [Service]
 Type=simple
 Environment="GOMAXPROCS=1"
 User=prometheus
 Group=prometheus
 ExecReload=/bin/kill -HUP $MAINPID
 ExecStart=/usr/local/bin/prometheus \
 --config.file=/etc/prometheus/prometheus.yml \
 --storage.tsdb.path=/var/lib/prometheus \
 --web.console.templates=/etc/prometheus/consoles \
 --web.console.libraries=/etc/prometheus/console_libraries \
 --web.listen-address=0.0.0.0:9090 \
 --web.external-url=

 SyslogIdentifier=prometheus
 Restart=always

 [Install]
 WantedBy=multi-user.target</pre>
<p style="text-align: justify;">Remember to edit the line: <em><strong>Environment="GOMAXPROCS=1</strong></em> by replacing 1 with the number of  **vcpus** on your server.</p>

<h4 style="padding-top: 15px;">8. Change Directory Permissions</h4>
<p style="text-align: justify;">Change the ownership of these directories to Prometheus user and group:</p>

<pre>$for i in rules rules.d files_sd; do sudo chown -R prometheus:prometheus /etc/prometheus/${i}; done
$for i in rules rules.d files_sd; do sudo chmod -R 775 /etc/prometheus/${i}; done
$sudo chown -R prometheus:prometheus /var/lib/prometheus/</pre>
<h4 style="padding-top: 15px;">9. Reload systemd Daemon &amp; Start the Service</h4>
<pre>$sudo systemctl daemon-reload
$sudo systemctl start prometheus
$sudo systemctl enable prometheus</pre>
<p style="text-align: justify;">Check status using <em><strong>systemctl status prometheus</strong></em> command:</p>
<a href="https://scalegrid.io/blog/wp-content/uploads/2020/01/prometheus-running.jpg"><img class="alignnone size-full wp-image-5060" src="https://scalegrid.io/blog/wp-content/uploads/2020/01/prometheus-running.jpg" alt="Installing &amp; Configuring the Prometheus Server for MySQL - Reload systemd Daemon &amp; Start the Service" width="100%" height="auto" /></a>
<h4 style="padding-top: 15px;">10.  Configure a Firewall to Open Port 9090</h4>
<pre>$sudo firewall-cmd --add-port=9090/tcp --permanent
$sudo firewall-cmd --reload</pre>
<p style="text-align: justify;">Once the setup is complete, you can access the Prometheus UI by logging in to http://&lt;PrometheusHostIP&gt;:9090</p>
<a href="https://scalegrid.io/blog/wp-content/uploads/2020/01/prometheus-ui.png"><img class="alignnone size-full wp-image-5061" src="https://scalegrid.io/blog/wp-content/uploads/2020/01/prometheus-ui.png" alt="Installing &amp; Configuring the Prometheus Server for MySQL - Access Prometheus UI" width="100%" height="auto" /></a>


<h3 style="padding-top: 15px;">Installing &amp; Configuring MySQL Prometheus Exporter</h3>
<p style="text-align: justify;">Prometheus requires an exporter for collecting MySQL server metrics. This exporter can be run centrally on the Prometheus server, or on the database server. For further reading, refer to the <a style="color: #2da964;" href="https://prometheus.io/docs/instrumenting/writing_exporters/#deployment" target="_blank">Prometheus documentation</a>.</p>
<p style="text-align: justify;">Follow the below steps to install and set up MySQL Prometheus Exporter on the central Prometheus host. For more details, refer to this <a style="color: #2da964;" href="https://computingforgeeks.com/monitoring-mysql-mariadb-with-prometheus-in-five-minutes/" target="_blank">article</a>.</p>

<h4 style="padding-top: 15px;">1. Download &amp; Install Prometheus MySQL Exporter</h4>
<pre>$curl -s <a href="https://api.github.com/repos/prometheus/mysqld_exporter/releases/latest" target="_blank">https://api.github.com/repos/prometheus/mysqld_exporter/releases/latest</a>   | grep browser_download_url   | grep linux-amd64 | cut -d '"' -f 4   | wget -qi -
$tar xvf mysqld_exporter*.tar.gz
$sudo mv  mysqld_exporter-*.linux-amd64/mysqld_exporter /usr/local/bin/
$sudo chmod +x /usr/local/bin/mysqld_exporter</pre>
<h4 style="padding-top: 15px;">2. Create Prometheus Exporter Database User to Access the Database, Scrape Metrics &amp; Provide Grants</h4>
<pre>CREATE USER 'mysqld_exporter'@'&lt;PrometheusHostIP&gt;' IDENTIFIED BY 'StrongPassword' WITH MAX_USER_CONNECTIONS 2;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'mysqld_exporter'@'&lt;PrometheusHostIP&gt;';
FLUSH PRIVILEGES;
EXIT</pre>
<p style="text-align: justify;"><em>WITH MAX_USER_CONNECTIONS 2</em> is used to set a max connection limit for the user to avoid overloading the server with monitoring scrapes under heavy load.</p>

<h4 style="padding-top: 15px;">3. Configure the Database Credentials</h4>
<p style="text-align: justify;">Edit the config file of the exporter:</p>

<pre>$sudo vim /etc/.mysqld_exporter.cnf</pre>
<p style="text-align: justify;">Add the username and password of the user created and the ScaleGrid MySQL server you want to monitor:</p>

<pre>$sudo vim /etc/.mysqld_exporter.cnf

[client]
user=mysqld_exporter
password=StrongPassword
host=SG-mysqltestcluster-123456.servers.mongodirector.com</pre>
<p style="text-align: justify;">Set ownership permissions:</p>

<pre>$sudo chown root:prometheus /etc/.mysqld_exporter.cnf</pre>
<h4 style="padding-top: 15px;">4. Create systemd Unit File</h4>
<p style="text-align: justify;">Create a new service file:</p>

<pre>$sudo vim /etc/systemd/system/mysql_exporter.service</pre>
<p style="text-align: justify;">Add the following content:</p>

<pre> [Unit]
 Description=Prometheus MySQL Exporter
 After=network.target
 User=prometheus
 Group=prometheus

 [Service]
 Type=simple
 Restart=always
 ExecStart=/usr/local/bin/mysqld_exporter \
 --config.my-cnf /etc/.mysqld_exporter.cnf \
 --collect.global_status \
 --collect.info_schema.innodb_metrics \
 --collect.auto_increment.columns \
 --collect.info_schema.processlist \
 --collect.binlog_size \
 --collect.info_schema.tablestats \
 --collect.global_variables \
 --collect.info_schema.query_response_time \
 --collect.info_schema.userstats \
 --collect.info_schema.tables \
 --collect.perf_schema.tablelocks \
 --collect.perf_schema.file_events \
 --collect.perf_schema.eventswaits \
 --collect.perf_schema.indexiowaits \
 --collect.perf_schema.tableiowaits \
 --collect.slave_status \
 --web.listen-address=0.0.0.0:9104
 
 [Install]
 WantedBy=multi-user.target</pre>
<p style="text-align: justify;"><em><strong>web.listen-address=0.0.0.0:9104</strong></em> specifies that the server is listening on port 9104. If your server has a public and private network, you may need to replace 0.0.0.0:9104 with private IP, for example – 192.168.4.5:9104.</p>
<p style="text-align: justify;">When done, reload systemd and start mysql_exporter service:</p>

<pre>$sudo systemctl daemon-reload
$sudo systemctl enable mysql_exporter
$sudo systemctl start mysql_exporter</pre>
<h4 style="padding-top: 15px;">5. Configure MySQL Endpoint to be Scraped by Prometheus</h4>
<p style="text-align: justify;">Make changes like below to the prometheus.yml file:</p>

<pre>scrape_configs:
- job_name: mysql_server1
static_configs:
- targets: ['localhost:9104']
labels:
alias: db1</pre>
<blockquote>Note: If the exporter is not running on the same host as Prometheus, provide the IP address of the server instead of localhost. 9104 refers to the port Prometheus listens to, as specified in the previous step.</blockquote>
<h3 style="padding-top: 15px;">Monitoring Multiple MySQL Hosts From a Central Prometheus Host</h3>
<p style="text-align: justify;">Multiple MySQL servers can be monitored from a central server. This can be achieved by having a separate exporter service for each server. Make sure to create .mysqld_exporter.cnf and mysql_exporter.service (with unique port numbers assigned to the --web.listen-address flag) files for each service as mentioned in steps 3 and 4 above. Add targets to the prometheus.yml file as mentioned in step 5 above. Job names should be <i>unique</i> for each target. For example:</p>

<pre>scrape_configs:
- job_name: mysql_server1
static_configs:
- targets: ['localhost:9104']
labels:
alias: db1
- job_name: mysql_server2
static_configs:
- targets: ['localhost:9105']
labels:
alias: db2
- job_name: mysql_server3
static_configs:
- targets: ['localhost:9106']
labels:
alias: db3</pre>
<blockquote>
<p style="text-align: justify;">Note: Prometheus Server should be able to reach the targets over the network. Ensure that your network/firewall configurations have been modified accordingly.</p>
</blockquote>
<h3 style="padding-top: 15px;">Installing Grafana &amp; Creating Dashboards</h3>
<p style="text-align: justify;">Grafana uses Prometheus as a data source, allowing you to create dashboards to better visualize and understand your metrics. It provides a great way to gain insight into your time series data.</p>
<p style="text-align: justify;">Follow the below steps to install Grafana on your central Prometheus host.</p>

<h4 style="padding-top: 15px;">1. Download the Latest Grafana Version</h4>
<p style="text-align: justify;">Go the the <a style="color: #2da964;" href="https://grafana.com/grafana/download?platform=linux" target="_blank">Download Grafana</a> page to download the latest version.</p>

<pre>$wget &lt;debian package url&gt;
$sudo apt-get install -y adduser libfontconfig1
$sudo dpkg -i grafana_&lt;version&gt;_amd64.deb</pre>
<h4 style="padding-top: 15px;">2. Download APT Repository &amp; Install Grafana</h4>
<p style="text-align: justify;">The command add-apt-repository isn’t a default app on Debian 9 and requires:</p>

<pre>$apt-get install -y software-properties-common</pre>
<p style="text-align: justify;">Install the repository for stable releases:</p>

<pre>$sudo add-apt-repository "deb https://packages.grafana.com/oss/deb stable main"</pre>
<p style="text-align: justify;">There is a separate repository if you want beta releases:</p>

<pre>$sudo add-apt-repository "deb https://packages.grafana.com/oss/deb beta main"</pre>
<p style="text-align: justify;">Use the above line even if you are on Ubuntu or another Debian version. Then add our gpg key. This allows you to install signed packages:</p>

<pre>$wget -q -O - https://packages.grafana.com/gpg.key | sudo apt-key add -</pre>
<p style="text-align: justify;">Update your Apt repositories and install Grafana:</p>

<pre>$sudo apt-get update
$sudo apt-get install grafana</pre>
<h4 style="padding-top: 15px;">3. Start the Service</h4>
<pre>$systemctl daemon-reload
$systemctl start grafana-server
$systemctl status grafana-server</pre>
<p style="text-align: justify;">Enable the systemd service so that Grafana starts at boot:</p>

<pre>$sudo systemctl enable grafana-server.service</pre>
<p style="text-align: justify;">To run Grafana, open your browser and go to *http://&lt;PrometheusHostIP&gt;:3000/*. 3000 is the http port that Grafana listens to, by default.</p>

<h4 style="padding-top: 15px;">4. Adding a Data Source</h4>
<p style="text-align: justify;">When installed, login to admin dashboard and add a data source by navigating to Configuration &gt; Data Sources.</p>
<em>Name: Prometheus
Type: Prometheus
URL: http://localhost:9090</em>
<blockquote>Note: If Prometheus server is not running on the same host as Grafana, provide the IP address of the server instead of localhost.</blockquote>
<a style="color: #2da964;" href="https://scalegrid.io/blog/wp-content/uploads/2020/01/datasource.jpg"><img class="alignnone size-full wp-image-5062" src="https://scalegrid.io/blog/wp-content/uploads/2020/01/datasource.jpg" alt="Installing Grafana &amp; Creating Dashboards for MySQL - Add a Data Source" width="100%" height="auto" /></a>

<p style="text-align: justify;">You are now all set to create and customize dashboards for your MySQL monitoring. You can create a new dashboard by clicking on the link on the right side of the dashboard picker. Once the dashboard is created, you can add panels choosing the metrics to be displayed, star the dashboard, save and share it. For detailed instructions, you can refer to Grafana's <a style="color: #2da964;" href="https://grafana.com/docs/guides/getting_started/" target="_blank">Getting Started</a> documentation.</p>
Here’s an example of a Grafana dashboard created for a MySQL deployment at ScaleGrid:

<a href="https://scalegrid.io/blog/wp-content/uploads/2020/01/grafana-mysql-1.png"><img class="alignnone size-full wp-image-5063" src="https://scalegrid.io/blog/wp-content/uploads/2020/01/grafana-mysql-1.png" alt="Grafana MySQL Dashboard - MySQL Table Locks, Temporary Objects, Sorts and Select Types" width="100%" height="auto" /></a>

<p style="text-align: justify;">The above Grafana dashboard displays MySQL Table Locks, MySQL Temporary Objects, MySQL Sorts, and MySQL Select Types metrics visualized in the charts, and the below Grafana dashboard displays MySQL Basic Command Counters and MySQL Top Command Counters Hourly.</p>

<a href="https://scalegrid.io/blog/wp-content/uploads/2020/01/grafana-mysql-2.png"><img class="alignnone size-full wp-image-5064" src="https://scalegrid.io/blog/wp-content/uploads/2020/01/grafana-mysql-2.png" alt="Grafana MySQL Dashboard - MySQL Basic Command Counters and Top Command Counters Hourly" width="100%" height="auto" /></a>
<p style="text-align: justify; padding-bottom:20px;">We are always looking to make our users lives easier, so let us know of any other tools you’d like to connect to your ScaleGrid deployments through our comments or via Twitter at <a style="color: #2da964;" href="https://twitter.com/scalegridio" target="_blank">@scalegridio</a>.</p>