View active MySQL connections per user

Question

I need a query that will give me the active or open number of connections to a certain database for a certain user. I have been searching for hours and have come up with nothing so far. I am close, so here is what I have tried.

1. SHOW STATUS WHERE `variable_name` = 'Threads_connected';

2. SHOW STATUS LIKE '%onn%';

3. SELECT * FROM information_schema.processlist WHERE USER='database_user';

I work for a web hosting company, and one of our clients keeps hitting his max_user_connections limit, so in troubleshooting why I need to know how many connections his user is using right now since he is no a shared server. I am currently using MySQL (InnoDB) version 5.5.36. Any help would be greatly appreciated!

Answer 1

What you need is a breakdown by user and hostname along with a total

SELECT IFNULL(usr,'All Users') user,IFNULL(hst,'All Hosts') host,COUNT(1) Connections
FROM
(
    SELECT user usr,LEFT(host,LOCATE(':',host) - 1) hst
    FROM information_schema.processlist
    WHERE user NOT IN ('system user','root')
) A GROUP BY usr,hst WITH ROLLUP;

This will handle host address that have a colon separating hostname and port number

I hope everybody is not logging in as root

Answer 2

There was a discussion in detail for this: https://dba.stackexchange.com/a/47160/385

In short, the queries you need are

SELECT max_user_connections FROM mysql.user WHERE user='db_user' AND host='localhost';

and

SHOW VARIABLES LIKE 'max_user_connections';

UPDATE

Maybe this is not direct answer to your question, but according to http://dev.mysql.com/doc/refman/5.5/en/user-resources.html MySQL allows you to limit the number of access per hour. So, maybe that answers why a certain user hits limit while the number of connections of them are small according to process list. Unfortunately, I cannot find reference on how to get the access counter.