Check The Number Of MySQL Open Database Connections on Linux Or Unix-like Server

I‘m a new MySQL server user. My server is running on a CentOS Linux. How can I check the number of active MySQL connections on Linux based system?

You can use the following commands on Linux or Unix-like systems:[donotprint]
Tutorial details
DifficultyEasy (rss)
Root privilegesNo
RequirementsNone
Time1m
[/donotprint]

ADVERTISEMENTS

a) mysqladmin status command

b) MySQL show status command

c) netstat or ss commands

mysqladmin status command example

Open the terminal App or login to the remote server using ssh:

ssh vivek@server1.cyberciti.biz

Type the following command to get a short status message from the MySQL server:

mysqladmin status
## OR ##
mysqladmin status -u root -p 
## OR ##
mysqladmin status -h db1.cyberciti.biz -u root -p

Sample outputs:

Uptime: 691356  Threads: 5  Questions: 83237956  Slow queries: 102736  Opens: 3585  Flush tables: 1  Open tables: 1019  Queries per second avg: 120.398

MySQL show status command to see open database connections example

First, connect to the your mysql server:

mysql -u root -p

Type the following sql query to see the number of connection attempts to the MySQL server includes both failed and successful connection attempts:

mysql> show status like 'Conn%';

Sample outputs:

Fig.01: "show status like 'Conn%';" in action

Fig.01: “show status like ‘Conn%’;” in action

You can use the following sql command to see the number of currently open connections at mysql> prompt:

mysql> show status like '%onn%';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| Aborted_connects         | 7       |
| Connections              | 6304067 |
| Max_used_connections     | 85      |
| Ssl_client_connects      | 0       |
| Ssl_connect_renegotiates | 0       |
| Ssl_finished_connects    | 0       |
| Threads_connected        | 7       | <---- No of currently open connections 
+--------------------------+---------+
7 rows in set (0.00 sec)

Use show processlist sql command to see the number of open connections

Type the following sql command at mysql> prompt to see the number of currently open connections:

mysql> show processlist;
+---------+------------+-------------------+------------+---------+------+-------+------------------+
| Id      | User       | Host              | db         | Command | Time | State | Info             |
+---------+------------+-------------------+------------+---------+------+-------+------------------+
| 6297128 | root       | localhost         | NULL       | Query   |    0 | NULL  | show processlist |
| 6308321 | faqwpblogu | 10.10.29.66:42945 | lesaibkfaq | Sleep   |    1 |       | NULL             |
| 6308323 | faqwpblogu | 10.10.29.74:46993 | lesaibkfaq | Sleep   |    0 |       | NULL             |
| 6308325 | faqwpblogu | 10.10.29.74:46995 | lesaibkfaq | Sleep   |    1 |       | NULL             |
| 6308326 | faqwpblogu | 10.10.29.74:46996 | lesaibkfaq | Sleep   |    0 |       | NULL             |
+---------+------------+-------------------+------------+---------+------+-------+------------------+
5 rows in set (0.00 sec)

The above output indicates four currently open connection for user called 'faqwpblogu' from app server located at 10.10.29.66 and 10.10.29.74.

MySQL show status sql command summary

I suggest that you read the following pages for more info:

  1. SHOW STATUS Syntax
  2. Server Status Variables

Use netstat or ss (Linux only) command to list open database connections

The syntax is as follows for netstat command or ss command:

netstat -nat | grep 10.10.29.68:3306

This will just give you an overview. I suggest that you use above sql commands only.

🐧 Get the latest tutorials on SysAdmin, Linux/Unix, Open Source/DevOps topics:
CategoryList of Unix and Linux commands
File Managementcat
FirewallCentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network Utilitiesdig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searchinggrep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNCentOS 8 Debian 10 Firewall Ubuntu 20.04

ADVERTISEMENTS
3 comments… add one
  • Charles Apr 8, 2015 @ 10:52

    Nice, it really helped to me. Congrats

  • Abdi May 4, 2015 @ 12:38

    Hi,
    1.Could you explain me this :
    Uptime: 5067843 Threads: 9 Questions: 381272925 Slow queries: 5072 Opens: 3443635 Flush tables: 1 Open tables: 400 Queries per second avg: 75.233

    2. When i run this command the server asks me a password:
    mysql -u root -p

    hope to help

    Abdi

  • Morty Oct 24, 2016 @ 17:18

    Very helpful, thanks a lot :)

Leave a Reply

Your email address will not be published.

Use HTML <pre>...</pre>, <code>...</code> and <kbd>...</kbd> for code samples.