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.
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