CODEWITHSUNDEEP
mysqldatabase
flybywire
flybywire

Reputation: 273572

mysql: see all open connections to a given database?

With administrative permissions im mysql, how can I see all the open connections to a specific db in my server?

Upvotes: 163

Views: 300834

Answers (9)

Vadym Tyemirov
Vadym Tyemirov

Reputation: 8833

That should do the trick for the newest MySQL versions:

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

Upvotes: 46

SAGAR BHOOSHAN
SAGAR BHOOSHAN

Reputation: 329

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;

Upvotes: 2

Ayan
Ayan

Reputation: 8886

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

show status like 'Threads_connected';

Upvotes: 7

justAMySQL_starter
justAMySQL_starter

Reputation: 61

SQL: show full processlist;

This is what the MySQL Workbench does.

Upvotes: 6

Pryo
Pryo

Reputation: 690

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.

Upvotes: 2

wiseland
wiseland

Reputation: 1050

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

Upvotes: 76

Kanagaraj M
Kanagaraj M

Reputation: 966

In query browser right click on database and select processlist

Upvotes: 0

KV Prajapati
KV Prajapati

Reputation: 94645

You can invoke MySQL show status command

show status like 'Conn%';

For more info read Show open database connections

Upvotes: 11

David Rabinowitz
David Rabinowitz

Reputation: 30448

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

Upvotes: 214

Related Questions