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