Base cntr next 1 1
|

Installation Guide for Percona Monitoring and Management

Introduction

Percona Monitoring and Management (PMM) is an open-source tool used for managing and monitoring MySQL and MongodB, as well as PostgreSQL and ProxySQL. It is efficient, easy to use and, best of all, it is free. As its name informs, it is developed by Percona, in collaboration with experts in the field of database services, support, consulting, and management.

PMM can run in your own environment for maximum security and reliability. It provides thorough time-based analysis for the databases and their respective servers, this is to ensure that your data works as efficiently as possible.

Installing Docker

The recommended method for installing and running PMM Server is through Docker Engine, which in this article we’ll use the Community version. To do that, you would need to install Docker and pull the PMM Server image, which is stored at the percona/pmm-server repository.

Note that the host must be able to run Docker 1.12.6 or newer.

If for any reason you cannot use the yum repository to install Docker, you can download the .rpm files for manual installation. You would need to download a new file each time you want to upgrade Docker Engine – Community.

OS requirements

To install Docker Engine – Community, you need a maintained version of CentOS 7. Archived versions haven’t been tested, and so aren’t supported. Check your OS before trying to install Docker.

Also, the centos-extras repository, which contains packages that provide CentOS with additional functionality, must be enabled. This repository is enabled by default, but it is always a good advice to check just in case you have disabled it, then you would need to re-enable it.

ealim000@ZLIN7131:~ $ cat 
/etc/redhat-release CentOS Linux release 
7.7.1908 (Core)

RPMs to PMM Server

# wget
https://download.docker.com/linux/centos/7/x86_64/stable/Packages/contai nerd.io-1.2.10-3.2.el7.x86_64.rpm

# wget
https://download.docker.com/linux/centos/7/x86_64/stable/Packages/docker -ce-19.03.4-3.el7.x86_64.rpm

# wget
https://download.docker.com/linux/centos/7/x86_64/stable/Packages/docker -ce-cli-19.03.4-3.el7.x86_64.rpm

# wget
http://mirror.centos.org/centos/7/extras/x86_64/Packages/container-selin ux-2.107-3.el7.noarch.rpm

Installing Docker RPMs

After downloading the .rpm files, you need to install the required packages. By using the yum command as a root user you can install yum-utils, which provides the yum-config-manager utility, also device-mapper-persistent-data and lvm2, all of them required by the Device Mapper storage driver.

Once you are a root user, run the following commands:

sudo su -

yum install -y yum-utils device-mapper-persistent-data lvm2

Now, install Docker RPMs:

yum install container-selinux-2.107-3.el7.noarch.rpm

 # yum install 
containerd.io-1.2.10-3.2.el7.x86_64.rpm 
docker-ce-19.03.4-3.el7.x86_64.rpm 
docker-ce-cli-19.03.4-3.el7.x86_64.rpm

Use this command to review Docker version:

# docker --version
Docker version 19.03.4, build 9013bf583a

Then, start Docker service:

# sudo systemctl start docker

To review the status use:

# systemctl status docker

docker.service - Docker Application Container Engine

Loaded: loaded (/usr/lib/systemd/system/docker.service; disabled;

vendor preset: disabled)

Active: active (running) since Thu 2019-11-07 19:30:32 CET; 39s ago

Docs: https://docs.docker.com

Main PID: 79585 (dockerd)

Tasks: 9

Memory: 44.1M

CGroup: /system.slice/docker.service

└─79585 /usr/bin/dockerd -H fd://

--containerd=/run/containerd/containerd.sock

And to enable/disable Docker service at system startup, type the following command:

# systemctl list-unit-files --type service | grep -i docker

docker.service                            	disabled

systemctl enable docker.service Created symlink from /etc/systemd/system/multi-user.target.wants/docker.service to /usr/lib/systemd/system/docker.service.

systemctl list-unit-files --type service | grep -i docker

docker.service	                                 enabled

Changing Docker root directory

First, stop the Docker service:

# service docker stop

Use the following setup config to change to the Docker root directory:

cat /etc/docker/daemon.json


{
"data-root": "/opt/docker"
}

Now, start the Docker service to apply the changes:

# service docker start

Confirm that change was applied:

docker info

...

Server: Containers: 0 Running: 0 Paused: 0 Stopped: 0 Images: 0

Server Version: 19.03.4

Storage Driver: overlay2 Backing Filesystem: xfs
...

Kernel Version: 3.10.0-1062.4.1.el7.x86_64

Operating System: CentOS Linux 7 (Core)

OSType: linux

Architecture: x86_64

CPUs: 2

Total Memory: 3.682GiB

Name: ZLIN7131
…

Docker Root Dir: /opt/docker
…

du -sch /opt/docker/ 136K /opt/docker/ 136K total

Installing PMM Server

Download PMM Server Docker Image

If you don’t have Internet access on the PMM Server, then you would need to download the percona/pmm-server docker image on a different server, export it, and then copy it to the PMM Server.

First, download the percona/pmm-server docker image:

# docker pull percona/pmm-server:2

List the image for review:

# docker images

REPOSITORY          TAG     IMAGE ID         CREATED        SIZE
percona/pmm-server   2    2725f3f00609      5 days ago     1.51GB

Then, save the image on a .tar.gz format before copying it to the server that is running PMM in a secure environment:

# docker save percona/pmm-server:2 | gzip > pmm-server_2.tar.gz

Copy the pmm-server_2.tar.gz fle to the PMM Server where Docker Engine – Community is running. Once pmm-server_2.tar.gz is copied, you can import and load the image in Docker.

# docker load < pmm-server_2.tar.gz

Now, confirm the PMM image was imported successfully in docker:

# docker images

REPOSITORY          TAG      IMAGE ID       CREATED       SIZE
percona/pmm-server   2    2725f3f00609     5 days ago    1.51GB

Start PMM Server

Now that percona/pmm-server image is available with Docker, we can create the pmm-data container for PMM Server:


docker create \ 
-v /srv \ --name 
pmm-data \

percona/pmm-server:2 /bin/true

Notice that this container does not run, it simply exists to make sure you retain all PMM data when you upgrade to a newer pmm-server image. Do not remove or re-create this container, unless you are planning on starting over and discarding all your PMM data.

Confirm that the pmm-data was created:

# docker ps -a

CONTAINER ID	IMAGE	COMMAND	CREATED STATUS	PORTS

NAME

Sb30b2bf1b786 percona/pmm-server:2 "/bin/true" 4 seconds ago Created pmm-data

Now that the PMM data container was created, you can start the PMM Server:


docker run -d \

-p 8080:80 \

-p 8443:443 \

-e SERVER_USER=pmm \

-e SERVER_PASSWORD=dkau3halDio55cH \

--volumes-from pmm-data \

--name pmm-server \

--restart always \

percona/pmm-server:2

This is what was done by the previous commands:

  • The docker run command instructs the Docker daemon to run a container from an image. The -d option starts the container in detached mode (in the background).
  • The -p option maps the port for accessing the PMM Server web UI (User Interface). For example, if port 80 is not available, you can map the landing page to port 8080 using -p 8080:80. 8080:80 (http) & 8443:443 (https)
  • The -e commands specify the server user and password.
  • The –volumes-from option mounts volumes from the pmm-data container.
  • The –name option assigns a custom name for the container that you can use to reference the container within a Docker network. In this case: pmm-server.
  • The –restart option defines the container’s restart policy. Setting it to always ensures that the Docker daemon will start the container on startup and restart it if the container exits.
  • percona/pmm-server:2 is the name and version tag of the image from which the container will be derived.

Now, confirm that pmm-server is actually running:

# docker ps

CONTAINER ID      IMAGE      COMMAND

CREATED           STATUS      PORTS

NAMES

40562d9be610     percona/pmm-server:2     "/opt/entrypoint.sh"   2

hours ago        Up 2 hours (healthy)      0.0.0.0:8080->80/tcp,


0.0.0.0:8443->443/tcp	pmm-server

Once you confirm that the pmm-server container is up and running, you can login to the web interface.  You can access the web UI using the following URL, but replace <PMM_SERVER_HOST_IP> with the IP address of the host running the PMM Server:

https://<PMM_SERVER_HOST_IP>:8443/

This will load the PMM login page, which will ask for login credentials. The default credentials are:

User: admin

Password : admin

You will need to change your password after the first login. Better make sure your password is complex enough, and keep it secure, you will need it for adding database servers for PMM monitoring.

So now that you have PMM up and running, you can work on adding Database and ProxySQL instances in PMM monitoring.

Opening ports on monitored hosts

In order to allow your database to receive data from external agents, you need to open the necessary ports:

pmm-agent check-network
….

* Connection: Client <-- Server
-------------- --------- -------------------- ------- ----------
SERVICE TYPE   NAMEREMOTE ENDPOINT   STATUS  HTTPS/TLS  PASSWORD
-------------- --------- -------------------- ------- ----------

linux:metrics    BLIN7101    172.30.50.215:42000    DOWN    YES    YES

mysql:metrics    BLIN7101    172.30.50.215:42002    DOWN    YES    YES

In case some services are down and you cannot access them, you will need to open the respective ports for each of those service marked as DOWN:

iptables -I INPUT -p tcp -s 172.30.50.195 --dport 42000 -j 
ACCEPT iptables -I INPUT -p tcp -s 172.30.50.195 --dport 42002 
-j ACCEPT

After this operation, everything should be fine and the agents will be able to send data to the PMM Server.

Adding Database/ProxySQL servers in PMM monitoring

The next step, since your PMM Server is ready for monitoring your databases and proxies, you are now installing the pmm-agents on the nodes. First, make sure that your PMM Server host is accessible, so send a ping to its IP address.

If, for example, your IP is 172.30.50.195, then write:

# ping 172.30.50.195

You will need to have root access on the database host where you will be installing PMM Client, either using sudo commands or logging in with root privileges.

Percona provides .rpm packages for 64-bit versions of Red Hat Enterprise Linux 6 (Santiago) and 7 (Maipo), including its derivatives that claim full binary compatibility, such as CentOS, Oracle Linux, Amazon Linux AMI, and so on.

Go to this link:

https://www.percona.com/downloads/pmm2/

Then, download the latest 2.x version and run the following command to install the agent on the server:

# yum install pmm2-client-2.5.0-6.el7.x86_64.rpm

Now that your server and clients are set up, you must configure each PMM Client and specify which PMM Server it should send its data to.

To connect a PMM Client, enter the pmm-admin config command, while using the –server-url parameter to enter the IP address of the PMM Server. Also use with –server-insecure-tls to allow using self-signed certificates:

# pmm-admin config --server-insecure-tls

--server-url=https://admin:<PASSWORD>@<PMM_SERVER_IP>:8443

The –server-url argument should include https:// prefix and the PMM Server credentials, which are admin/<your_complex_password> changed after first login.

It should show the following output:

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.

Very Important

On each computer where PMM Client is installed, certain ports must be open. These are default ports that you can change when adding the respective monitoring service with the pmm-admin add command:

Ports to open

monitor server → DB nodes (DB should have these ports open for monitor)

Port 22 # SSH

Port 3306 # MySQL

Port 5666 # NRPE (Nagios)

Port 42000 # node_exporter (PMM)

Port 42002 # mysql_exporter (PMM)

Port 42003 # proxysql_exporter (PMM)

DB nodes → monitor server (Monitor should have these ports open for DB Node)

Port 8443 # HTTPS

Port 5667 # NSCA

Port 8080 # QAN

To grant permission for a PMM User to collect MySQL metrics, create the following user in MySQL:

GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT, RELOAD ON *.* TO 
'pmm'@'127.0.0.1' IDENTIFIED BY 'pass' WITH MAX_USER_CONNECTIONS 
10;

GRANT SELECT, UPDATE, DELETE, DROP ON performance_schema.* TO 'pmm'@'127.0.0.1';

Now, for collecting data from PMM Clients on a PMM Server, add the MySQL services Metrics and Query Analytics.

Run the following commands as root or by using the sudo command:

pmm-admin add mysql --query-source=slowlog --username=pmm --password='pass'

MySQL Service added.

Service ID  : /service_id/4864668e-c564-4585-9a23-5e02b564b685

Service name: ALIN7101-mysql

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

You can find more information about the PMM service in this article

If you want to list the PMM services on MySQL server,  you can do by typing the following:

# pmm-admin list

Service type     Service name     Address and port  Service ID

MySQL     ALIN7101-mysql     127.0.0.1:3306

/service_id/4864668e-c564-4585-9a23-5e02b564b685

Agent type     Status     Agent ID

Service ID


pmm_agent	Connected

/agent_id/62d6423c-107e-4c1e-8ca5-ae29163da87b

node_exporter	Running

/agent_id/861c0638-867d-4799-a1f1-4dfbba632290


mysqld_exporter	Running

/agent_id/b778a831-03b1-4b1d-8924-7ba416cea834 /service_id/4864668e-c564-4585-9a23-5e02b564b685

mysql_slowlog_agent	Running

/agent_id/b1a6169d-bd7e-4324-b3ae-4da70d346656 /service_id/4864668e-c564-4585-9a23-5e02b564b685

Finally, to add ProxySQL service, use the following command:

pmm-admin add proxysql --username=admin --password='pass'

ProxySQL Service added.

Service ID	: /service_id/07e445e7-32c3-46cb-a266-28ba6654274c

Service name: BLIN7102-proxysql

# pmm-admin list

Service type   Service name	Address and port  Service ID

ProxySQL	BLIN7102-proxysql	127.0.0.1:6032

/service_id/07e445e7-32c3-46cb-a266-28ba6654274c

Agent type	Status	Agent ID

Service ID

pmm_agent	Connected

/agent_id/8d1f4a54-c582-4685-a72f-3651ab5dcd78


node_exporter	Running

/agent_id/460614c9-4cf3-44d3-9627-301f2ec70caa


proxysql_exporter	Running

/agent_id/14d6e8ee-0585-49de-a977-1bbfdc4027d1 /service_id/07e445e7-32c3-46cb-a266-28ba6654274c

Conclusion

So, there you go. It might seem like a lot of information to get the Percona Monitoring and Management (PMM) tool monitoring your databases, but by following the guidelines established through this article you shouldn’t have any problems. The steps sound tricky, and it could take you a while to set up everything, but you will see the benefits of PMM once you got it up and running.

Stick around for more useful tips and information about managing your MySQL databases. You never know when good advice is gonna be needed!