Identify number of Mysql connections open

Sometimes DBA or Developer need to identify which connections are open in background so they can identify through below script.

Execute below script on mysql shell.

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;

Second option to identify the running / open connection through below script in mysql shell.

show full processlist;
SHOW GLOBAL STATUS LIKE 'Threads_running'; 
show status where `variable_name` = 'Threads_connected';

Identify number of Mysql connections open

Tags