mysql: see all open connections to a given database?

With administ开发者_开发问答rative permissions im mysql, how can I see all the open connections to a specific db in my server?

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

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

That should do the trick for the newest MySQL versions:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE DB like "%DBName%";

You can invoke MySQL show status command

show status like 'Conn%';

For more info read Show open database connections

SQL: show full processlist;

This is what the MySQL Workbench does.

In MySql,the following query shall show the total number of open connections:

show status like 'Threads_connected';

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.

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;

In query browser right click on database and select processlist