How to View Active Connections Per User in MySQL

By default, every user has a maximum number of connections allowed in MySQL. If they hit this limit, they won’t be able to make any new connections, unless some of their existing connections are closed. Database administrators need to frequently check the active connections per user to ensure that no one is about to hit their limit. In this article, we will learn how to view active connections per user in MySQL.

How to View Active Connections Per User in MySQL

Here is the SQL query to get breakdown of active connections by username and host.

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;

If the above query does not give you the desired result, you can use the following two queries for this purpose. Replace db_user with the username and localhost with the user’s host.

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

and

SHOW VARIABLES LIKE 'max_user_connections';

In this article, we have seen how to get open active connections per user in MySQL.

Also read:

How to Show All Open Connections to MySQL Database
How to Revoke Privileges for Users in MySQL
How to Show Users With Access to MySQL Database
How to Show User Permission in MySQL
How to Force Git Pull to Overwrite Local Changes