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.

Credit :http://blog.shlomoid.com/2011/08/how-to-easily-see-whos-connected-to.html