Monitoring MySQL with Percona Monitoring and Management

In this blog post we will see how to monitor MySQL databases using the open source monitoring tool provided by Percona, referred as Percona Monitoring and Management (PMM).

About PMM:

Percona Monitoring and Management (PMM) is an open-source platform for managing and monitoring MySQL and MongoDB performance. It is developed by Percona in collaboration with experts in the field of managed database services, support and consulting.

PMM is a free and open-source solution that you can run in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL and MongoDB servers to ensure that your data works as efficiently as possible.

Basic Requirements:

1. Need at least one Linux server to install PMM Client and PMM Server.

2. Need Operating System - root user credentials

3. Default port's(80) have to be open if using to or more servers.

4. A MySQL user is required to capture the Queries in the Database.

5. A Docker service has to be installed on your Server to run PMM Server remotely.

Section - 1

Installing PMM Server:

Note: Here we are using two AWS EC2 Linux machines to install PMM, 1 server for PMM SERVER and 1 server for PMM CLIENT.

1. Install Docker to run PMM Server remotely and start the docker service on Server 1.

# yum install docker

Installed:
  docker.x86_64 0:1.12.6-2.19.amzn1

Dependency Installed:
  xfsprogs.x86_64 0:4.5.0-9.21.amzn1

# service docker start

Starting cgconfig service:                                 [  OK  ]
Starting docker:        .                                  [  OK  ]

2. Create a PMM Data Container:

# docker create \
>    -v /opt/prometheus/data \
>    -v /opt/consul-data \
>    -v /var/lib/mysql \
>    -v /var/lib/grafana \
>    --name pmm-data \
>    percona/pmm-server:1.1.3 /bin/true

3. Create and Run the PMM Server Container:

# docker run -d \
>    -p 80:80 \
>    --volumes-from pmm-data \
>    --name pmm-server \
>    --restart always \
>    percona/pmm-server:1.1.3

4. After successfully completing the above steps, open your Browser and type your IP of the host where the PMM Server is running. If everything went well, you will see below page.

Section - 2

Installing PMM Client:

1. Download the PMM Client software from the Percona's official website on Host 2.

$ wget https://www.percona.com/downloads/pmm-client/pmm-client-1.1.3/binary/tarball/pmm-client-1.1.3.tar.gz

2. Un-tar the Software

$ tar -xvzf pmm-client-1.1.3.tar.gz

3. Go to the PMM client software location and run install script

# cd /home/mysql/softwares/pmm-client-1.1.3/bin
# ./install

4. Now, configure the client server using below:

# ./pmm-admin config --server PMM-Server-Host-IP --bind-address PMM-Client-Server-PrivateIP

Note: If you installed both PMM Server and PMM Client on the same host, then --bind-address is not required.

5. If the above configuration is successful, you will receive below output:

OK, PMM server is alive.

PMM Server      | 10.10.10.159
Client Name     | PMM Server Host
Client Address  | 10.10.10.200

6. Now configure the MySQL with pmm-admin like below (The user which you created for minitoring)

# ./pmm-admin add mysql --user root --password thisisthepassword
[linux:metrics] OK, already monitoring this system.
[mysql:metrics] OK, now monitoring MySQL metrics using DSN root:***@unix(/tmp/mysql.sock)
[mysql:queries] OK, now monitoring MySQL queries from slowlog using DSN root:***@unix(/tmp/mysql.sock)

7. Finally, check the all services are up and running using below command.

# ./pmm-admin list
pmm-admin 1.1.3

PMM Server      | 10.10.10.159
Client Name     | PMM Server Host
Client Address  | 10.10.10.200 
Service Manager | linux-systemd

-------------- ------------------------------ ----------- -------- ------------------------------- ------------------------------------------
SERVICE TYPE   NAME                           LOCAL PORT  RUNNING  DATA SOURCE                     OPTIONS
-------------- ------------------------------ ----------- -------- ------------------------------- ------------------------------------------
mysql:queries  PMM Server Host                -           YES      root:***@unix(/tmp/mysql.sock)  query_source=slowlog, query_examples=true
linux:metrics  PMM Server Host                42000       YES      -
mysql:metrics  PMM Server Host                42002       YES      root:***@unix(/tmp/mysql.sock)

8. From now on, PMM will capture all the Linux and MySQL metrics from you sever and poll to the PMM Server host, this you can monitor from the PMM Server browser which discussed in Section 1 - Step 4.

Note: If you have multiple database hosts, the section 2 procedure has to be followed on all the nodes.