How to Monitor MySQL with Prometheus and Grafana

In this article, we will learn how to effectively monitor MySQL databases using Prometheus and Grafana, two powerful tools that, when combined, provide comprehensive insights into database performance, health, and resource utilization. Monitoring databases is crucial for identifying potential issues, optimizing performance, and ensuring the smooth operation of your database environment. By integrating Prometheus for data collection and Grafana for visualization and analysis, you can create custom dashboards that offer real-time visibility into key metrics and facilitate proactive management of your MySQL infrastructure.

Prerequisites

• AWS Account with Ubuntu 22.04 LTS EC2 Instance.
• Basic knowledge of AWS services, Prometheus and Grafana.

Step #1:Install MySQL on Ubuntu

Update the system.

sudo apt update 

now install the mysql using following command.

sudo apt install mysql-server -y

check the status to verify the mysql is running properly on your system.

sudo systemctl status mysql

Step #2:Install Prometheus on Ubuntu

Download the latest version of Prometheus from the github repo using the following command.

wget https://github.com/prometheus/prometheus/releases/download/v2.30.0/prometheus-2.30.0.linux-amd64.tar.gz

After download is complete extract the downloaded archives.

tar xvfz prometheus-2.30.0.linux-amd64.tar.gz

The command extracts the contents of the file prometheus-2.30.0.linux-amd64.tar.gz

now let’s move into the extracted directory using following command.

cd prometheus-2.30.0.linux-amd64

navigate to the /etc/systemd/system, this is where typically systemd unit files are located, which are used for managing services on Linux systems.

cd /etc/systemd/system

now lets create a service for Prometheus named prometheus.service.

sudo vi prometheus.service

add the following content into it.

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

[Service]
ExecStart=/home/ubuntu/prometheus-2.30.0.linux-amd64/prometheus --config.file=/home/ubuntu/prometheus-2.30.0.linux-amd64/prometheus.yml
Restart=always

[Install]
WantedBy=default.target

Now our prometheus.service is created.

After this reload the daemon service to verify our configuration file is correct and also enable the Prometheus service and after that start the service.

sudo systemctl daemon-reload
sudo systemctl enable prometheus.service
sudo systemctl start prometheus.service

now check the status to see if the Prometheus service is running properly by running following command:

sudo systemctl status prometheus.service

If your service is running properly then you can run prometheus by running your public ip address with port 9090 which is default port for prometheus in url.

Step #3:Install Grafana on Ubuntu

First import the GPG key used by the Grafana package.

wget -q -O - https://packages.grafana.com/gpg.key | sudo apt-key add -

Add the Grafana repository to the APT sources.

sudo add-apt-repository "deb https://packages.grafana.com/oss/deb stable main"

Update the package lists

sudo apt update

then install the grafana

sudo apt install grafana

Start the Grafana service

sudo systemctl start grafana-server

then enable the grafana service.

sudo systemctl enable grafana-server

and if everything works fine and your service is running properly then run grafana by running your public ip address and port number 3000 which is default port of grafana in searchbar.

you will see the login page of grafana (UI) user interface

Grafana has admin as default username and password

it will ask for changing the password you can change it or skip it.

then you will see the welcome page of grafana.

Step #4:Create User Group and User for Prometheus

Now after the installation of Prometheus and grafana is done lets create User Group and User for Prometheus.

sudo groupadd --system prometheus
sudo useradd -s /sbin/nologin --system -g prometheus prometheus

Monitor MySQL with Prometheus and Grafana

Step #5:Download and install MySQL exporter

Download the MySQL exporter.

curl -s https://api.github.com/repos/prometheus/mysqld_exporter/releases/latest | grep browser_download_url | grep linux-amd64 | cut -d '"' -f 4 | wget -qi -

extract the contents of it.

tar xvf mysqld_exporter*.tar.gz

Move the binary from the extracted directory to the directory /usr/local/bin/ using following command

sudo mv mysqld_exporter-*.linux-amd64/mysqld_exporter /usr/local/bin/

make the executable by running the following command.

sudo chmod +x /usr/local/bin/mysqld_exporter

confirm the installation of mysql exporter by checking its version.

mysqld_exporter --version

Step #6:Configuring MySQL Exporter Service.

After the installation, login using root.

sudo mysql -u root

Create a user to collect the matrices using following commands.

CREATE USER 'mysqld_exporter'@'localhost' IDENTIFIED BY 'StrongPassword';

GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'mysqld_exporter'@'localhost';

FLUSH PRIVILEGES;

EXIT

Now lets create a credentials file for database.

sudo vim /etc/.mysqld_exporter.cnf

add the following credentials into it like user, password.

[client]
user=mysqld_exporter
password=StrongPassword

Set the ownership permission to the user root and group prometheus.

sudo chown root:prometheus /etc/.mysqld_exporter.cnf

Now create a service to gather data.

sudo vim /etc/systemd/system/mysql_exporter.service

Add the following content into it.

[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

save the file and to verify our configuration file is correct and reload the daemon service.

Then enable and start the service.

sudo systemctl daemon-reload
sudo systemctl enable mysql_exporter
sudo systemctl start mysql_exporter

Check it’s status to see if it’s running properly.

sudo systemctl status mysql_exporter

Step #7:Configuring MySQL Exporter Endpoint in Prometheus Configuration

Go to the directory where prometheus.yml file is present.

cd prometheus-2.30.0.linux-amd64

Now lets configure the endpoints into the prometheus.yml file

nano prometheus.yml

add the mysqld job block into it as shown below.

# 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=<job_name>` to any timeseries scraped from this config.
  - job_name: "prometheus"

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

    static_configs:
      - targets: ["localhost:9090"]
  - job_name: "mysqld"

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

    static_configs:
      - targets: ["localhost:9104"]

Now save the file and restart the prometheus.service

sudo systemctl restart prometheus.service

telnet localhost 9104

Now go to Prometheus dashboard and click on status, select target, you can see our exporter are up and running.

click on mysqld exporter endpoint to verify. You will see the data.

Step #8:Setting up Grafana Dashboards for MySQL Metrics

Now go back to the Grafana welcome page. Go to the Home in the left top corner.

Then go to the Connections and select the Data sources option.

Search for Prometheus in the search bar and select it.

In connection, in Prometheus server URL, give the server url on which our prometheus is running.

after this click on save and test button. You will see the message for prometheus being successfully queried.

now go to the ‘+‘ icon from up-right corner and select the New dashboard option

Here you can start your own new dashboard by adding a visualization.

So click on + Add visualization option button.

Here you can also import dashboards.

Select the Prometheus as Data source.

You will see the edit panel page

Now in the Query section add the A query

• Metric: mysql_global_status_threads_connected
• instance: localhost:9104

then add the B query

• Metric: mysql_global_status_max_used_connections
• job: mysqld

Click on run queries You will see the following output.

It’s in the statistics visualization, but there are many visualization option like time series, gauge, bar graph etc.

Click ctrl+s to save the file. Give the appropriate Title for your dashboard the save it.

Conclusion:

In conclusion, implementing a monitoring solution for MySQL databases using Prometheus and Grafana is essential for maintaining the health, performance, and reliability of your database environment. By following the steps outlined in this guide, you can effectively set up and configure Prometheus to collect metrics from MySQL instances, visualize these metrics in Grafana dashboards, and proactively manage your MySQL infrastructure.

Related Articles:

How to Integrate AWS CloudWatch with Grafana

Reference:

Grafana Installation official page