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.rpmInstalling 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 lvm2Now, 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.rpmUse this command to review Docker version:
# docker --version
Docker version 19.03.4, build 9013bf583aThen, start Docker service:
# 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.sockAnd 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 enabledChanging Docker root directory
# service docker stopFirst, stop the Docker service:
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 startConfirm 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 totalInstalling 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:2List the image for review:
# docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
percona/pmm-server 2 2725f3f00609 5 days ago 1.51GBThen, 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.gzCopy 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.gzNow, 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.51GBStart 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/trueNotice 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-dataNow 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:2This 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-serverOnce 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 YESIn 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 ACCEPTAfter 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.195You 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.rpmNow 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>:8443The –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-5e02b564b685Finally, 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-28ba6654274cConclusion
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!