CODEWITHSUNDEEP
mysql
Dorilds
Dorilds

Reputation: 448

Receive count of MySQL requests from a certain host

I want to get the count of MySQL connections on a host using, 

SELECT COUNT(*) FROM information_schema.PROCESSLIST where host='hostname';

But my host has a different number after each connection looking like port #s. This makes it impossible to match the host name. What can I do to match the hosts without taking these ports into account?

They look like:

Hostname:1212

Hostname:1214

Upvotes: 0

Views: 791

Answers (2)

Tiago Fatturi
Tiago Fatturi

Reputation: 1

Using suggestions from David Brossard, I modified this SQL to do what I believe is what the OP wanted:

SELECT SUBSTRING_INDEX(host, ":", 1) AS HOSTNAME, COUNT(*) FROM information_schema.PROCESSLIST GROUP BY HOSTNAME ORDER BY HOSTNAME;

Upvotes: 0

David Brossard
David Brossard

Reputation: 13834

You could try to create a MySQL view that would contain your data plus a new column called host. That new column's value would be based off of the host values you currently have e.g. hostname:1234 and it would use either of substring or better yet substring_index to go from host:1234 to host.

Try this SELECT statement to see whether that works

SELECT COUNT(*) FROM information_schema.PROCESSLIST where SUBSTRING_INDEX(host, ":", 1)='hostname';

You can then do GROUP BY and get the COUNT(host) correctly.

Upvotes: 1

Related Questions