How To Set Up MySQL Monitoring With PMM

Prepare your environment

Before you start setting up PostgreSQL to be connected to PMM monitoring, you need to have a PMM Client and register it on the PMM Server. Let’s do it. You may set up the PMM Client in different ways. It can be either running in a Docker container or installed by a package manager or from sources. In this article, we will use the easiest way — we will install it from a repository using a package manager. Make sure you have curl, gnupb, gwet, and sudo installed on your server. First of all, you need to add Percona repo to your repositories list.

For Debian-based OS

a. Configure repositories:

wgethttps://repo.percona.com/apt/percona-release_latest.generic_all.debdpkg -i percona-release_latest.generic_all.deb

b. Install the hpmm-client package:

apt updateapt install -y pmm2-client

c. Check if it was installed correctly:

pmm-admin --version

For RedHat-based OS

a. Configure repositories:

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

b. Install the hpmm-client package:

yum install -y pmm2-client

c. Check if it was installed correctly:

pmm-admin --version

After you have installed the PMM Client, you should register it on your server. You need an HPMM server hostname or IP address and credentials for your connection. You may find this information on your hostedpmm private cabinet.

To register the PMM client on the server, just type:

pmm-admin config --server-insecure-tls --server-url=https://admin:<password>@XXX.XXX.XXX.XXX:443 YYY.YYY.YYY.YYY generic mynode

where XXX.XXX.XXX.XXX — IP address or hostname of PMM server, YYY.YYY.YYY.YYY — IP address or hostname of PMM client, generic — client type and mynode — mane of client.

Configure MySQL

Create a database account for PMM. It is a good practice not to use a superuser to connect the PMM client to the monitored database instance:

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

There are two possible sources of metrics — Slow the query log and Performance Schema. Here are the benefits and drawbacks of Slow query log and Performance Schema metrics sources:

Benefits

Slow query log

More detail.
Lower resource impact (with query sampling feature in Percona Server for MySQL).

Performance Schema

Faster parsing.
Enabled by default on newer versions of MySQL

Drawbacks Slow query log

PMM Client must be on the same host as the database server or have access to a slow query log.
Log files grow and must be actively managed.

Performance Schema

Data source recommendations

Database server MySQL
Versions 5.1-5.5
Recommended source Slow query log

Database server MySQL
Versions 5.6+
Recommended source Performance Schema

Database server MariaDB
Versions 10.0+
Recommended source Performance Schema

Database server Percona Server for MySQL
Versions 5.7, 8.0
Recommended source Slow query log

Database server Percona XtraDB Cluster
Versions 5.6, 5.7, 8.0
Recommended source Slow query log

As far as we use MySQL version higher than 5.6 and our PMM server is located on a remote host, we will configure the Performance Schema course. Add the following lines to my.conf file:

performance_schema=ONperformance-schema-instrument='statement/%=ON'performance-schema-consumer-statements-digest=ONinnodb_monitor_enable=allquery_response_time_stats=ONuserstat=ON

Restart the mysql and create the PMM service:

pmm-admin add mysql --query-source=perfschema --username=pmm --password=pass --service-name=MYSQL_NODE —host=XXX.XXX.XXX.XXX --port=3306

You may also add the service via the PMM Server user interface in Configuration -> PMM Inventory -> Add Instance menu section. Check if the service is registered successfully:

pmm-admin inventory list services --service-type=mysql

Success!

Congratulations, you have successfully set up MySQL monitoring with PPM. Now you have access to organized, secure, and robust database management systems. Monitoring and managing your data can help your business scale, grow, and flourish.

HostedPMM Services

Now that you have set up MySQL monitoring with PMM, you can focus on bigger issues, like growing your business. HostedPMM offers hosted PMM servers for monitoring, automation, troubleshooting, and convenience for all your database needs. You can get your own PMM server, connect your instances, and focus on your project with HostedPMM. Get started for free and say hello to simpler database solutions. We offer a quick setup, backups, scalability, tech support, security updates, high availability, and no vendor restrictions.

If you have any questions or need assistance with HostedPMM, contact us at support@hostedpmm.com any time.