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!

2 Answers 2


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
    SELECT user usr,LEFT(host,LOCATE(':',host) - 1) hst
    FROM information_schema.processlist
    WHERE user NOT IN ('system user','root')

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

I hope everybody is not logging in as root

  • Impressive. In a nut shell
    – Umair Aziz
    Sep 29, 2021 at 4:32

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';


SHOW VARIABLES LIKE 'max_user_connections';


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.

  • I do not want to change the number of max_user_connections or view the max_user_connections, I know how to do that. I want to view how many of those connections a specific user has active. Sep 17, 2015 at 3:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.