Percona Monitoring and Management 2 (PMM2) is the database monitoring suite assembled and developed by Percona. It is based on standard open source components and custom-made software integrations. PMM2 helps you reduce complexity, optimize performance, and improve the security of your business-critical database environments, no matter where they are located or deployed.
This blog post will describe a method to test PMM2 using your laptop’s VirtualBox, ssh tunnels, and without installing any agents on the database servers. This is a testing and evaluation environment, not intended for production. If you want to perform a full-fledged test of PMM2, we recommend an environment as similar as possible to your final production setup: enterprise virtualization, docker containers, or AWS. We assume that your laptop doesn’t have direct connectivity to the databases, this is why we use ssh tunnels.
PMM2 architecture consists of 2+1 components:
Two components run on your infrastructure: PMM Agents and PMM Server. The agents gather the metrics at the database and operating system levels. PMM Server takes care of processing, storing, grouping, and displaying these metrics. It can also perform additional operations like capturing serverless databases metrics, backups, and sending alerts (the last two features are in technical preview as of this writing). The other component to complete the formula is the Percona Platform, which adds more features to PMM, from advisors to DBaaS. Disclaimer: Percona Platform is in preview release with limited functionality – suitable for early adopters, development, and testing. Besides the extended features added to PMM, the Percona Platform brings together distributions of MySQL, PostgreSQL, and MongoDB including a range of open-source tools for data backup, availability, and management. You can learn more about the Platform here.
To make setup easier, PMM2 Server can be run either as a docker container or importing an OVA image, executed using VMWare VSphere, VirtualBox, or any other hypervisor. If you run your infrastructure in AWS, you can deploy PMM from the AWS Marketplace.
To run the agents, you need a Linux box. We recommend running the agents and the database on the same node. PMM can also gather the metrics using a direct connection to a server-less database or running an operating system that does not support the agent.
Often, installing the agents is a stopper for some DBAs who would like to test PMM2. Also, while containers are frequent in large organizations, we find virtualization and containers relegated to development and quality assurance environments. These environments usually don’t have direct access to production databases.
TCP Forwarding Across SSH Connections
AllowTcpForwarding is the ssh daemon configuration option that allows forwarding TCP ports across the ssh connection. At first sight, this may seem a security risk, but as the ssh documentation states: “disabling TCP forwarding does not improve security unless users are also denied shell access, as they can always install their forwarders.”
If your system administrators do not allow TCP forwarding, other options available to accomplish the same results are socat or netcat. But we will not cover them here.
If your laptop has direct access to the databases, you can skip all the ssh tunnels and use the direct access method described later in this post.
Install PMM 2 Ova
Download the Open Virtualization Format compatible image from https://www.percona.com/downloads/pmm2/ or use the command line:
$ wget https://www.percona.com/downloads/pmm2/2.25.0/ova/pmm-server-2.25.0.ova
You can import the OVA file using the UI, with the import option from the file menu, or using the command line:
$ VBoxManage import pmm-server-2.25.0.ova --vsys 0 --vmname "PMM Testing"
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Interpreting /Users/pep/Downloads/pmm-server-2.25.0.ova...
OK.
Disks:
vmdisk1 42949672960 -1 http://www.vmware.com/interfaces/specifications/vmdk.html#streamOptimized PMM2-Server-2021-12-13-1012-disk001.vmdk -1 -1
vmdisk2 429496729600 -1 http://www.vmware.com/interfaces/specifications/vmdk.html#streamOptimized PMM2-Server-2021-12-13-1012-disk002.vmdk -1 -1
Virtual system 0:
0: Suggested OS type: "RedHat_64"
(change with "--vsys 0 --ostype "; use "list ostypes" to list all possible values)
1: VM name specified with --vmname: "PMM Testing"
2: Suggested VM group "/"
(change with "--vsys 0 --group ")
3: Suggested VM settings file name "/Users/Pep/VirtualBox VMs/PMM2-Server-2021-12-13-1012/PMM2-Server-2021-12-13-1012.vbox"
(change with "--vsys 0 --settingsfile ")
4: Suggested VM base folder "/Users/Pep/VirtualBox VMs"
(change with "--vsys 0 --basefolder ")
5: Product (ignored): Percona Monitoring and Management
6: Vendor (ignored): Percona
7: Version (ignored): 2021-12-13
8: ProductUrl (ignored): https://www.percona.com/software/database-tools/percona-monitoring-and-management
9: VendorUrl (ignored): https://www.percona.com
10: Description "Percona Monitoring and Management (PMM) is an open-source platform for managing and monitoring MySQL and MongoDB performance"
(change with "--vsys 0 --description ")
11: Number of CPUs: 1
(change with "--vsys 0 --cpus ")
12: Guest memory: 4096 MB
(change with "--vsys 0 --memory ")
13: Network adapter: orig NAT, config 3, extra slot=0;type=NAT
14: SCSI controller, type LsiLogic
(change with "--vsys 0 --unit 14 --scsitype {BusLogic|LsiLogic}";
disable with "--vsys 0 --unit 14 --ignore")
15: Hard disk image: source image=PMM2-Server-2021-12-13-1012-disk001.vmdk, target path=PMM2-Server-2021-12-13-1012-disk001.vmdk, controller=14;channel=0
(change target path with "--vsys 0 --unit 15 --disk path";
disable with "--vsys 0 --unit 15 --ignore")
16: Hard disk image: source image=PMM2-Server-2021-12-13-1012-disk002.vmdk, target path=PMM2-Server-2021-12-13-1012-disk002.vmdk, controller=14;channel=1
(change target path with "--vsys 0 --unit 16 --disk path";
disable with "--vsys 0 --unit 16 --ignore")
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Successfully imported the appliance.
Once the machine is imported, we will connect it to a host-only network. This network restricts network traffic only between the host and the virtual machines. But first, let’s find a suitable network:
$ VBoxManage list hostonlyifs Name: vboxnet0 GUID: 786f6276-656e-4074-8000-0a0027000000 DHCP: Disabled IPAddress: 192.168.56.1 NetworkMask: 255.255.255.0 IPV6Address: IPV6NetworkMaskPrefixLength: 0 HardwareAddress: 0a:00:27:00:00:00 MediumType: Ethernet Wireless: No Status: Up VBoxNetworkName: HostInterfaceNetworking-vboxnet0
Select the first one that has Status up, write down the name and IP address. Then make sure that there is a DHCP server assigned to that interface:
$ VBoxManage list dhcpservers NetworkName: HostInterfaceNetworking-vboxnet0 Dhcpd IP: 192.168.56.100 LowerIPAddress: 192.168.56.101 UpperIPAddress: 192.168.56.254 NetworkMask: 255.255.255.0 Enabled: Yes Global Configuration: minLeaseTime: default defaultLeaseTime: default maxLeaseTime: default Forced options: None Suppressed opts.: None 1/legacy: 255.255.255.0 Groups: None Individual Configs: None
Now we will assign two network interfaces to our PMM virtual machine. One is allocated to the internal network, and the other uses NAT to connect to the internet and, for example, check for upgrades.
$ VBoxManage modifyvm "PMM Testing" --nic1 hostonly --hostonlyadapter1 vboxnet0 $ VBoxManage modifyvm "PMM Testing" --nic2 natnetwork
Once networking is configured, we may start the virtual machine.
$ VBoxManage startvm "PMM Testing"
The next step is to retrieve the IP address assigned to our PMM box. First, we will obtain the MAC address of the network card we recently added:
$ VBoxManage showvminfo "PMM Testing" | grep -i vboxnet0 NIC 1: MAC: 08002772600D, Attachment: Host-only Interface 'vboxnet0', Cable connected: on, Trace: off (file: none), Type: 82540EM, Reported speed: 0 Mbps, Boot priority: 0, Promisc Policy: deny, Bandwidth group: none
Using the retrieved MAC address we can look for the DHCP leases:
$ VBoxManage dhcpserver findlease --interface=vboxnet0 --mac-address=08002772600D IP Address: 192.168.56.112 MAC Address: 08:00:27:72:60:0d State: acked Issued: 2021-12-21T22:15:54Z (1640124954) Expire: 2021-12-21T22:25:54Z (1640125554) TTL: 600 sec, currently 444 sec left
This is the IP address we will use to access the PMM server. Open a browser to connect to https://192.168.56.112 with the default credentials: admin/admin.
The following step configures the tunnels to connect to the databases we monitor.
Set Up the SSH Tunnels
This is the topology of our network:
We will open two ssh connections per server we want to access from PMM. Open a terminal session and execute the following command, replacing it with the username you normally use to connect to your jump host:
$ ssh -L 192.168.56.1:3306:10.0.0.1:3306 @192.168.55.100
This creates a tunnel that connects the MySQL Server port 3306 with our local internal address in the same port. If you want to connect to more than one MySQL instance, you must use different ports. To open the tunnel for the MongoDB server, use the following command:
$ ssh -L 192.168.56.1:27017:10.0.0.2:27017 @192.168.55.100
Test the tunnel connectivity to the MySQL host using netcat:
$ nc -z 192.168.56.1 3306 Connection to 192.168.56.1 port 3306 [tcp/mysql] succeeded!
And also test the connectivity to the MongoDB host:
$ nc -z 192.168.56.1 27017 Connection to 192.168.56.1 port 27017 [tcp/*] succeeded!
This is the topology of our network including the ssh tunnels.
Configure Accounts
Follow the PMM documentation and create a MySQL account (or use an already existing account) with the required privileges:
CREATE USER 'pmm'@'10.0.0.100' IDENTIFIED BY '' WITH MAX_USER_CONNECTIONS 10; GRANT SELECT, PROCESS, REPLICATION CLIENT, RELOAD, BACKUP_ADMIN ON *.* TO 'pmm'@'10.0.0.100';
Note that we need to use the internal IP address for the jump host. If you don’t know the IP address, use the wildcard ‘%’.
Add also the credentials for MongoDB, run this in a Mongo session:
db.getSiblingDB("admin").createRole({
role: "explainRole",
privileges: [{
resource: {
db: "",
collection: ""
},
actions: [
"listIndexes",
"listCollections",
"dbStats",
"dbHash",
"collStats",
"find"
]
}],
roles:[]
})
db.getSiblingDB("admin").createUser({
user: "pmm_mongodb",
pwd: "",
roles: [
{ role: "explainRole", db: "admin" },
{ role: "clusterMonitor", db: "admin" },
{ role: "read", db: "local" }
]
})
Add the Services to PMM
We can’t install the agents because we don’t have access to our PMM testing environment from the database servers. Instead, we will configure both services as remote instances. Go to the “Configuration” menu 
, select “PMM Inventory” 
, then “Add instance” 
. Then choose MySQL Add a remote instance.
Complete the following fields:
Hostname: 192.168.56.1 (This is the internal Host-Only VirtualBox address)
Service name: MySQL8
Port: 3306
Username: pmm
Password: <password>
And press the button. It will check the connectivity and, if everything is correct, the MySQL service will be added to the inventory. If there is an error, double-check that the ssh connection is still open and you entered the correct credentials. Make sure that the host that you specified to create the MySQL user is correct.
We will use a similar process for MongoDB:
These are the fields you have to complete with the correct information:
Hostname: 192.168.56.1 (Again, the internal Host-Only VirtualBox address)
Service name: MongoDB
Port: 27017
Username: pmm_mongodb
Password: <password>
And press the button. It will check the connectivity and, if everything is correct, the MongoDB service will be added to the inventory. If there is an error, double-check again that the ssh connection is open and you entered the correct credentials. You can use also the MongoDB client application to check access.
Once you added both services, you just need to wait for a few minutes to give time to collect data and start testing PMM2.
In this short blog post, we are going to review how to avoid using credentials in the 
As a DBA, it is important to monitor a database to help us troubleshoot or to understand the health of an instance. 
Thanks to everyone who attended last week’s webinar on Using Open Source Software to Optimize and Troubleshoot Your MySQL Environment; hopefully you’ve found the time we spent in 
Some of you may use MySQL’s asynchronous replication feature called 
