Connection Check Failed when adding MySQL database with "pmm-admin config..."

Percona Monitoring and Management (PMM) PMM 2.x
,
1

I’ve got my PMM server [2.43] installed and running [created from the .OVA file]
I have installed and registered 2 PMM clients on our 2 Ubuntu Linux MySql servers and they are reporting to the PMM server, I see OS level statistics about them in PMM.

I’m now trying to add the MySQL database using Performance Schema on each server, but always get the following error: “Connection check failed: dial tcp 127.0.0.1:3306: connect: connection refused.”

The command I’m using is: sudo pmm-admin add mysql --query-source=perfschema --username=pmm --password=passwordhere

I’ve also tried several variations of that including adding the service name and --server-insecure-tls

I’m following the QuickStart Guide Quickstart guide - Percona Monitoring and Management and Set up PMM Client - Percona Monitoring and Management

I have verified the accounts were created correctly in MySQL and has the correct Grants. I have tested the logins/passwords using “mysql -h localhost -P 3306 -u pmm -p” and that works for both accounts on both MySQL servers.

I have checked and/or added the variables: performance_schema=ON performance-schema-instrument=‘statement/%=ON’ performance-schema-consumer-statements-digest=ON innodb_monitor_enable = all

pmm-admin status:
Agent ID : /agent_id/defe5a46-a181-4d3a-90be-3f2455a195df
Node ID : /node_id/488f2fd8-2522-421f-b49e-65b079cbf4e0
Node name: WEBDB01TEST

PMM Server:
URL : https://ip.address/
Version: 2.43.0

PMM Client:
Connected : true
Time drift : -542.453µs
Latency : 651.3µs
Connection uptime: 100
pmm-admin version: 2.43.0
pmm-agent version: 2.43.0
Agents:
/agent_id/df5c7811-9d73-4832-ae51-a6ecee43eaa0 node_exporter Running 42000
/agent_id/f75caf8f-6386-4eae-875c-c885211fa3e2 vmagent Running 42001

Is there a way to see why my connection is being refused?

I’m not sure what else to try. I’m told our MySQL instance requires TLS, so I’m not sure if that’s part of the problem. I’m really a Windows admin, but I fiddle in Linux, and have been asked to setup PMM.

2

Is your MySQL bound to a port or socket? You can check in your [mysqld] section of the /etc/my.cnf file to see if it specifies socket or bind address with port.

This is also a bit of a linuxism where using ‘localhost’ on the command line typically defaults to a socket connection and even though you specified -P3306 it’s likely not being used. Typically if you want to force TCP use 127.0.0.1 (or I think you can specify TCP protocol on mysql command line with --protocol=TCP)

try your mysql command again as mysql -h 127.0.0.1 -P 3306 -u pmm -p (I expect this to fail one of two ways… 1) you’ll get the same connection refused pmm-admin gets and/or 2) you could also get a failed login as the GRANT you used probably specified ‘pmm’@‘localhost’ which won’t match ‘pmm’@'127.0.0.1`)

you can specify the socket in your pmm-admin add command with --socket=/var/run/mysqld/mysqld.sock or whtever is specified in the my.cnf.

3

it’s also very possible that my.cnf specifies a particular IP address (i.e. 192.168.0.2) and port so localhost/127.0.0.1 over TCP won’t work in either event and localhost would default to a local socket and your apps connect to the NIC IP address.

4

Thanks for your suggestions. ‘mysql -h 127.0.0.1 -P 3306 -u pmm -p’ failed with ‘ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1:3306’ (111)’ so I was able to determine that the login/password only work with localhost. The my.cnf file does not specify either socket or bind address with port.

So, I added the ‘–socket=/var/run/mysqld/mysqld.sock’ to my pmm-admin add command and it worked on both servers. I now see the 2 MySQL instances reporting into PMM.

Thank you very much for your help!

1 Like