How to view and manage active connections in MySQL or MariaDB

Checking active connections on a MySQL or MariaDB server shows who is connected, what each session is doing, and how long it has been doing it. Connection storms rarely announce themselves politely, so a fast view of live sessions is often the quickest way to explain timeouts, slowdowns, and lock pileups.

Each client connection maps to a server thread, and the server exposes thread metadata through SHOW PROCESSLIST and the INFORMATION_SCHEMA.PROCESSLIST view. The Command, Time, State, and Info columns highlight idle sessions, busy queries, and long-running work, while SHOW FULL PROCESSLIST avoids truncating query text.

Visibility and impact depend on privileges and caution. Without the PROCESS privilege (or an equivalent DBA role), only the current account’s threads are visible, and the Info column can expose sensitive SQL text. Terminating work with KILL can disrupt applications and roll back uncommitted transactions, so query cancellation should precede connection termination.

Steps to view active connections in MySQL or MariaDB:

Start an interactive session using the mysql client.

$ mysql --user=root --password
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 92
Server version: 8.0.44 MySQL Community Server - GPL
##### snipped #####
mysql>

On systems using auth_socket for local root access, sudo mysql can open a session without prompting for a password.

Display the full process list for all visible sessions.

mysql> SHOW FULL PROCESSLIST;
+----+-----------------+-----------------+------+---------+-------+------------------------+-----------------------+
| Id | User            | Host            | db   | Command | Time  | State                  | Info                  |
+----+-----------------+-----------------+------+---------+-------+------------------------+-----------------------+
|  5 | event_scheduler | localhost       | NULL | Daemon  | 33692 | Waiting on empty queue | NULL                  |
| 89 | app_user        | 192.0.2.40:34986 | NULL | Query   |    27 | User sleep             | SELECT SLEEP(300)     |
| 90 | report_user     | 192.0.2.41:35000 | NULL | Query   |    21 | User sleep             | SELECT SLEEP(300)     |
| 92 | root            | localhost       | NULL | Query   |     0 | init                   | SHOW FULL PROCESSLIST |
+----+-----------------+-----------------+------+---------+-------+------------------------+-----------------------+
4 rows in set, 1 warning (0.00 sec)

Seeing other users’ sessions requires the PROCESS privilege (or an equivalent DBA role); otherwise only the current account’s threads appear.

Show the same data in vertical format when query text or states are hard to read in a table.

mysql> SHOW FULL PROCESSLIST\G
*************************** 1. row ***************************
     Id: 5
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 33692
  State: Waiting on empty queue
   Info: NULL
*************************** 2. row ***************************
     Id: 89
   User: app_user
   Host: 192.0.2.40:34986
     db: NULL
Command: Query
   Time: 27
  State: User sleep
   Info: SELECT SLEEP(300)
*************************** 3. row ***************************
     Id: 90
   User: report_user
   Host: 192.0.2.41:35000
     db: NULL
Command: Query
   Time: 21
  State: User sleep
   Info: SELECT SLEEP(300)
*************************** 4. row ***************************
     Id: 92
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: init
   Info: SHOW FULL PROCESSLIST
4 rows in set, 1 warning (0.00 sec)

Plain SHOW PROCESSLIST can truncate Info, while SHOW FULL PROCESSLIST prints the full statement when available.

Count current connections using the Threads_connected status variable.

mysql> SHOW GLOBAL STATUS LIKE 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 3     |
+-------------------+-------+

Threads_connected includes the current session, so the count typically looks one higher than an external connection pool view.

List only non-idle work by filtering out sessions in the Sleep command state.

mysql> SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO
    -> FROM information_schema.PROCESSLIST
    -> WHERE COMMAND &lt;&gt; 'Sleep'
    -> ORDER BY TIME DESC;
+----+-----------------+-----------------+------+---------+-------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------+
| ID | USER            | HOST            | DB   | COMMAND | TIME  | STATE                  | INFO                                                                                                                                        |
+----+-----------------+-----------------+------+---------+-------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------+
|  5 | event_scheduler | localhost       | NULL | Daemon  | 33692 | Waiting on empty queue | NULL                                                                                                                                        |
| 89 | app_user        | 192.0.2.40:34986 | NULL | Query   |    27 | User sleep             | SELECT SLEEP(300)                                                                                                                           |
| 90 | report_user     | 192.0.2.41:35000 | NULL | Query   |    21 | User sleep             | SELECT SLEEP(300)                                                                                                                           |
| 92 | root            | localhost       | NULL | Query   |     0 | executing              | SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO
  FROM information_schema.PROCESSLIST
  WHERE COMMAND <> 'Sleep'
  ORDER BY TIME DESC |
+----+-----------------+-----------------+------+---------+-------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)

Summarize connections by user and client host to spot noisy clients or pool leaks.

mysql> SELECT USER,
    ->        SUBSTRING_INDEX(HOST, ':', 1) AS host,
    ->        COUNT(*) AS connections
    -> FROM information_schema.PROCESSLIST
    -> GROUP BY USER, host
    -> ORDER BY connections DESC, USER, host;
+-----------------+-----------+-------------+
| USER            | host      | connections |
+-----------------+-----------+-------------+
| app_user        | 192.0.2.40 |           1 |
| event_scheduler | localhost |           1 |
| report_user     | 192.0.2.41 |           1 |
| root            | localhost |           1 |
+-----------------+-----------+-------------+
4 rows in set, 2 warnings (0.00 sec)

Inspect the configured connection cap using the max_connections server variable.

mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

Check the historical peak using the Max_used_connections status variable.

mysql> SHOW GLOBAL STATUS LIKE 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 3     |
+----------------------+-------+

A consistently rising Max_used_connections approaching max_connections is a common signal of undersized pools or an unexpected client burst.

Cancel a single long-running statement by killing only its query using the Id from the process list.
```
mysql> KILL QUERY 89;
Query OK, 0 rows affected (0.00 sec)
```
The cancelled session typically receives an interruption error for the running statement, while the connection remains established.
Terminate the entire session by killing its connection when query cancellation is insufficient.
```
mysql> KILL CONNECTION 90;
Query OK, 0 rows affected (0.00 sec)
```
Killing a connection aborts the session and can roll back uncommitted work, which may surface as application errors and retries.

Re-check active sessions to confirm the targeted Id is gone.

mysql> SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
| Id | User            | Host      | db   | Command | Time  | State                  | Info             |
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
|  5 | event_scheduler | localhost | NULL | Daemon  | 33692 | Waiting on empty queue | NULL             |
| 92 | root            | localhost | NULL | Query   |     0 | init                   | SHOW PROCESSLIST |
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
2 rows in set, 1 warning (0.00 sec)

Exit the mysql client when finished.
```
mysql> exit
Bye
```

Author: Mohd Shakir Zakaria
Mohd Shakir Zakaria is a cloud architect with deep roots in software development and open-source advocacy. Certified in AWS, Red Hat, VMware, ITIL, and Linux, he specializes in designing and managing robust cloud and on-premises infrastructures.