2011-08-08

How to Easily See Who's Connected to Your MySQL Server

I'm posting this here since it has been useful for me, and the blog is a nice place to keep public notes.
If you have servers which have multiple application servers connected to them, you often need to see things like who's connected, how many connections they have, and which users. Using SHOW PROCESSLIST doesn't work that well, since it gives you a row for each server.

What we want is an output similar to this:
+-----------------+-----------------+----------+
| host_short      | users           | count(*) |
+-----------------+-----------------+----------+
| slave1          | repl            |        1 |
| slave2          | repl            |        1 |
| localhost       | event_scheduler |        1 |
| 111.111.222.111 | root, foo       |        2 |
| 111.111.222.222 | appuser, bar    |        3 |
| 111.111.222.333 | appuser, moshe  |        9 |
+-----------------+-----------------+----------+
And it is achieved using a simple query such as this one:

SELECT SUBSTRING_INDEX(host, ':', 1) AS host_short,
       GROUP_CONCAT(DISTINCT USER)   AS users,
       COUNT(*)
FROM   information_schema.processlist
GROUP  BY host_short
ORDER  BY COUNT(*),
          host_short;

A final note: I'm not sure what version of MySQL this query needs to function, but it works great on MySQL 5.5, and should work just as well on MySQL 5.1.

7 comments:

  1. Hi Shlomo,

    Instead of doing LEFT(..,IF...LOCATE...LENGTH)

    You can simply write
    SUBSTRING_INDEX(host, ':', 1)
    This will return the correct result even if there's no ':' in the text.

    ReplyDelete
  2. You're right, that's a much simpler way :)
    Updated the query.

    Thanks!

    ReplyDelete
  3. Great query, thx.
    I'll integrate it at my admin menu.

    ReplyDelete
  4. thx usefull query , a small variation using ROLLUP is also handy

    SELECT SUBSTRING_INDEX(host, ':', 1) AS host_short,
    GROUP_CONCAT(DISTINCT USER) AS users,
    COUNT(*)
    FROM information_schema.processlist
    GROUP BY host_short WITH ROLLUP

    ReplyDelete
  5. This query is awesome, just saying.

    ReplyDelete
    Replies
    1. Excellent post. It was helpful to me today.

      Delete
  6. Hi Shlomo:
    I dont understand very well "information_schema.processlist". You said that I can see who is connected to mysql server right? I dont know what I must to do with the result of the query you posted. The users fields that your query out put are the sames as the fields of your 'username' fields of your tables, or are the connection´s username? Thanks...

    ReplyDelete