show all open connections in mysql database

How to Show All Open Connections to MySQL Database

Sometimes database administrators may need to view all open connections to their database, to ensure that their server does not get overloaded. When MySQL server has too many open connections, it stops accepting new connections and even query execution slows down. So it is important to keep an eye on open database connections from time to time. In this article, we will learn how to show all open connections to MySQL database.


How to Show All Open Connections to MySQL Database

You can easily view open connections to your database server with the following command, after logging into MySQL.

mysql> show processlist;

The above command will show open connections to all databases on the server. Unfortunately, there is not filter for this command, such as WHERE clause.

If you need to view open connections to specific database, you can pass the output of processlist command to grep command as shown below. In this case, you will need to call processlist after mysqladmin command from terminal or command prompt. Replace <database-name> with the name of your database.

$ mysqladmin processlist | grep <database-name>

If you want to refresh the output every 10 seconds, use the -i option as shown below.

$ mysqladmin -i 10 processlist

If you need to authenticate MySQL login, you can pass username & password in the above command as shown.

$ mysqladmin --user=[USERNAME] --password=[PASSWORD] -i 10 processlist

If you are already logged into MySQL, you can use the following command instead.

mysql> show status like '%onn%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Aborted_connects         | 0     |
| Connections              | 403   |
| Max_used_connections     | 227   |
| Ssl_client_connects      | 0     |
| Ssl_connect_renegotiates | 0     |
| Ssl_finished_connects    | 0     |
| Threads_connected        | 227   |
+--------------------------+-------+

That’s it. In this short article, we have learnt how to view number of open connections in MySQL.

Also read:

How to Revoke User Privileges in MySQL
How to Show Users With User Access to MySQL Database
How to Show User Permissions in MySQL
How to Force Git Pull to Overwrite Local Changes
How to Check Remote SSL Certificate in Linux

Leave a Reply

Your email address will not be published. Required fields are marked *