Learn how to – Monitor Percona MySQL / Percona XtraDB Cluster With Prometheus & Grafana

Learn how to – Monitor Percona MySQL / Percona XtraDB Cluster With Prometheus & Grafana.

In our previous article, we discussed how to setup Percona XtraDB cluster on Rocky Linux. In this article, we shall cover how to monitor the same cluster using Prometheus and Grafana.

Percona Monitoring and Management (PMM) is a free, open-source monitoring tool based on Prometheus and Grafana which can monitor MySQL, PostgreSQL, MongoDB, and ProxySQL servers.

Percona Monitoring Management runs on the metrics server. PMM client is installed on the nodes that you wish to monitor. In our case, we shall be installing PMM client on three Percona XtraDB Cluster nodes.

PMM server runs as a Docker container. In that regard, you will need to have docker installed on your Metrics server. The guide below will highlight the end-to-end steps for setting up this kind of metrics.

In our recent article we covered the steps of installing and configuring Percona XtraDB Cluster on Rocky Linux 8:

• Install and Configure Percona XtraDB Cluster on Rocky Linux 8

Installing Percona Monitoring and Management Server

We will install the server with a script available on GitHub. The script installs Docker engine if it hasn’t been installed on your host but it is strongly recommended that you manually install Docker engine. You can check the guide below on how to install Docker-CE.

How To Install Docker CE on Linux Systems

Download the install script with the command below:

curl -fsSL -O https://raw.githubusercontent.com/percona/pmm/main/get-pmm.sh

Make the downloaded script executable then run it as root.

chmod +x ./get-pmm.sh
sudo ./get-pmm.sh

The command above downloads and runs the PMM server on your metrics host. It also exposes the container on port 443. A successful server setup will give an output similar to the one below:

Gathering/downloading required components, this may take a moment

Checking docker installation - installed.

Starting PMM server...
Created PMM Data Volume: pmm-data
Created PMM Server: pmm-server
	Use the following command if you ever need to update your container by hand:
	docker run -d -p 443:443 --volumes-from pmm-data --name pmm-server --restart always percona/pmm-server:2 

PMM Server has been successfully setup on this system!

You can access your new server using the one of following web addresses:
	https://172.17.0.1:443/
	https://192.168.100.25:443/
	https://127.0.0.1:443/

The default username is 'admin' and the password is 'admin' :)
Note: Some browsers may not trust the default SSL certificate when you first open one of the urls above.
If this is the case, Chrome users may want to type 'thisisunsafe' to bypass the warning.

Enjoy Percona Monitoring and Management!

You can access the web management console using https://<metrics-server-IP>. The default username/password is admin/admin:

You will be required to change the password when you login for the first time.

Installing Percona Monitoring and Management Client

This should be installed on all the nodes that you wish to monitor. In our case, we shall be monitoring a Percona XtraDB Cluster installed in the previous article.

Ubuntu

Install PMM client on ubuntu hosts using the steps below

• Download and install Percona repo

wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
sudo apt-get update

• Install PMM client

sudo apt-get install pmm2-client

RedHat/CentOS/Rocky Linux

For YUM packages, use the steps below:

• Download Percona repo package

sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

• Install PMM client

sudo yum install pmm2-client

Send Metrics from Clients to Monitoring Server

After a successful installation of the PMM server and Clients, we need to connect the clients to the monitoring server. On each node, run the following to connect the nodes to the metrics server:

sudo pmm-admin config --server-insecure-tls --server-url=https://admin:<password>@<metrics-server-IP>

Replace the <password> with the password you configured on the PMM web interface(the new password for the web dashboard). Example:

$ sudo pmm-admin config --server-insecure-tls --server-url=https://admin:Str0ngPassw0rd@192.168.100.25
Checking local pmm-agent status...
pmm-agent is running.
Registering pmm-agent on PMM Server...
Registered.
Configuration file /usr/local/percona/pmm2/config/pmm-agent.yaml updated.
Reloading pmm-agent configuration...
Configuration reloaded.
Checking local pmm-agent status...
pmm-agent is running.

Configure Database for Monitoring

We need to configure our database for monitoring. For Percona XtraDB Cluster, login to one of the nodes and issue the commands below using mysql CLI.

CREATE USER 'pmm'@'localhost' IDENTIFIED BY 'StrongDBPassw0rd' WITH MAX_USER_CONNECTIONS 10;
GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT, RELOAD ON *.* TO 'pmm'@'localhost';

Note that we only need to run the command above on one node since we are configuring monitoring on a cluster which means that any change that we make from any node is replicated to the other nodes in the cluster.

On each of the nodes, run the command below to register the individual hosts:

$ sudo  pmm-admin add mysql --username=pmm --password=StrongDBPassw0rd --query-source=perfschema
MySQL Service added.
Service ID  : /service_id/c720f5cf-d142-4570-b21a-b78ce34a9146
Service name: percona02-mysql

Table statistics collection enabled (the limit is 1000, the actual table count is 341).

Visualising Percona MySQL/XtraDB Cluster Metrics

We can now visualize the metrics on our PMM server dashboard. Head to the Metrics server dashboard and navigate to Dashboards. You should see an interface similar to this below:

There, we have an overview of our environment, all the nodes being monitored are listed on that page. You can scroll down to view the full list of available nodes.

Select one of the nodes to see detailed metrics of the nodes.

Database stats are also populated and you can also expand the interface to get the insights of your database cluster.

Wrap Up

Percona Monitoring and Management (PMM) is a tool that can be used to monitor your database instances and clusters. This tool is free and open-source. The advantage it has is that you don’t have to use any other metrics exporter for scrapping the metrics as the PMM client does that for you.

Feel free to reach out in case you encounter any issues when building this solution for your environment. Cheers!

More articles:

• Install and Configure Percona XtraDB Cluster on Rocky Linux 8
• Install mycli MySQL AutoCompletion on Ubuntu s& Debian