mysql: see all open connections to a given database?
MysqlDatabaseMysql Problem Overview
With administrative permissions im mysql, how can I see all the open connections to a specific db in my server?
Mysql Solutions
Solution 1 - Mysql
The command is
SHOW PROCESSLIST
Unfortunately, it has no narrowing parameters. If you need them you can do it from the command line:
mysqladmin processlist | grep database-name
Solution 2 - Mysql
As well you can use:
mysql> show status like '%onn%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Aborted_connects | 0 |
| Connections | 303 |
| Max_used_connections | 127 |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_finished_connects | 0 |
| Threads_connected | 127 |
+--------------------------+-------+
7 rows in set (0.01 sec)
Feel free to use Mysql-server-status-variables or Too-many-connections-problem
Solution 3 - Mysql
That should do the trick for the newest MySQL versions:
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE DB = "elstream_development";
Solution 4 - Mysql
You can invoke MySQL show status command
>show status like 'Conn%';
For more info read Show open database connections
Solution 5 - Mysql
In MySql,the following query shall show the total number of open connections:
show status like 'Threads_connected';
Solution 6 - Mysql
SQL: show full processlist;
This is what the MySQL Workbench does.
Solution 7 - Mysql
If you're running a *nix system, also consider mytop.
To limit the results to one database, press "d" when it's running then type in the database name.
Solution 8 - Mysql
From the monitoring context here is how you can easily view the connections to all databases sorted by database. With that data easily monitor.
SELECT DB,USER,HOST,STATE FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY DB DESC;
+------+-------+---------------------+-----------+
| DB | USER | HOST | STATE |
+------+-------+---------------------+-----------+
| web | tommy | 201.29.120.10:41146 | executing |
+------+-------+---------------------+-----------+
If we encounter any hosts hots max connections and then not able to connect, then we can reset host tables by flushing it and is as follows:
FLUSH HOSTS;
Solution 9 - Mysql
In query browser right click on database and select processlist