Unlocking Database Insights: Monitoring MySQL with Prometheus and Grafana

Learn how to set up a robust monitoring system for MySQL using Prometheus and Grafana. By integrating Prometheus, a powerful monitoring and alerting toolkit, with Grafana, a flexible visualization and dashboarding platform, you can gain valuable insights into your MySQL database’s performance, health, and resource utilization. This guide will walk you through the process of collecting metrics from MySQL, configuring Prometheus to scrape and store the data, and creating custom dashboards in Grafana to visualize and analyze the collected metrics. With this monitoring setup, you can proactively identify issues, optimize database performance, and ensure the smooth operation of your MySQL environment.

1. Login to AWS & Launch one Ubuntu t2.micro instance with default value and also make sure open port 9090 i.e. for “Prometheus ”and 9104 i.e. for “MySQL” exporter & 3000 i.e. for “Grafana”

2. After Launch the instance login with the help of Putty or Session Manger and update the system before we start

sudo apt update

3. So because we are monitoring MySQL so we need to install on server if you already have then you can skip this step

sudo apt install mysql-server -y
sudo systemctl status mysql

4. After verifying our MySQL service running properly let’s install “Prometheus” & “Grafana”

##################Prometheus############################
#Download the latest version of Prometheus using the following command:
wget https://github.com/prometheus/prometheus/releases/download/v2.30.0/prometheus-2.30.0.linux-amd64.tar.gz
#Extract the downloaded archive:
tar xvfz prometheus-2.30.0.linux-amd64.tar.gz
#Move into the extracted directory:
cd prometheus-2.30.0.linux-amd64

5. After Installing lets create a service of Prometheus

cd /etc/systemd/system/

sudo vi prometheus.service

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

[Service]
ExecStart=/path/to/prometheus --config.file=/path/to/prometheus.yml
Restart=always
 
[Install]
WantedBy=default.target

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

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

7. If all things work fine you will see you service is running properly and after that copy the Public IP of your Instance and hit with port 9090 “PublicIP:9090” you will see Prometheus UI

8. After Successfully installing Prometheus lets install Grafana also

#####################Grafana############################\
#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
#Install Grafana:
sudo apt install grafana
#Start the Grafana service:
sudo systemctl start grafana-server
#Enable the Grafana service to start on system boot:
sudo systemctl enable grafana-server

9. If all things work fine you will see you service is running properly and after that copy the Public IP of your Instance and hit with port 3000 “PublicIP:3000” you will see Grafana UI (Note: While Installing Grafana if you see any “Warning” you can ignore it)

10. The Initial Username and Password for Grafana is “admin/admin” after login you can change it.

11. After Installing Grafana and Prometheus let create User Group and User for Prometheus

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

12. After adding user download and install 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 -
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

13. Confirm Installtion by checking version of mysql exporter

mysqld_exporter  --version

14. As we install MySQL in first step so lets login into MySQL using root and create a user to collect the matrices of MySQL

mysql -u root -p

CREATE USER 'mysqld_exporter'@'localhost' IDENTIFIED BY 'StrongPassword';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'mysqld_exporter'@'localhost';
FLUSH PRIVILEGES;
EXIT

15. Now create database credentials file

sudo vim /etc/.mysqld_exporter.cnf

[client]
user=mysqld_exporter
password=StrongPassword

as we create a user in db

16. Set the ownership permission

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

17. Now create a service for mysql exporter to gather data

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

[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

Replace Private IP with 0.0.0.0

18. After save this file reload the daemon service to verify our configuration file is correct and also enable and start the mysql exporter service

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

19. Now configure the endpoint in “Prometheus.yaml” file

vi prometheus.yml

- job_name: "mysqld"

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

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

20. Now save this file and restart Prometheus service

sudo systemctl restart prometheus.service

21. Now go to Prometheus dashboard “Staus → Targets” go to targets you can see our exporter are up and running and you can verify also with click on mysqld exporter endpoint you will see data

22. Now to to Grafana and click on “Home → Administration → Data Source”

23. Click on “Add Data Source”

24. Select “Prometheus” as data source and give IP of prometheus along with server

25. Scroll down and click on “Save & Test” button if you see successful message that means your data source added successfully

26. Now Click on “+” icon in right top corner

27. and select “Import Dashboard” option and Give “7362” number for MySQL dashboard if you want customize your dashboard you can or also find more dashboard on Dashboards | Grafana Labs and click on “Load”

28. Select Data source “Prometheus” as we configure and click on “Import”

29. Here you can see full metrics of your MySQL