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.

1. 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 41
   Server version: 8.0.36 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.

2. Display the full process list for all visible sessions.

   mysql> SHOW FULL PROCESSLIST;
   +----+------------+-------------------+------+---------+------+------------------------+----------------------------------------------+
   | Id | User       | Host              | db   | Command | Time | State                  | Info                                         |
   +----+------------+-------------------+------+---------+------+------------------------+----------------------------------------------+
   | 41 | root       | localhost         | NULL | Query   |    0 | init                   | SHOW FULL PROCESSLIST                        |
   | 52 | app_user   | 10.20.0.15:51522  | app  | Sleep   |  214 |                        | NULL                                         |
   | 67 | report_usr | 10.20.0.33:49810  | app  | Query   |   12 | Sending data           | SELECT id,total FROM orders ORDER BY id DESC |
   +----+------------+-------------------+------+---------+------+------------------------+----------------------------------------------+

   

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

3. 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: 67
      User: report_usr
      Host: 10.20.0.33:49810
        db: app
   Command: Query
      Time: 12
     State: Sending data
      Info: SELECT id,total FROM orders ORDER BY id DESC
   *************************** 2. row ***************************
        Id: 52
      User: app_user
      Host: 10.20.0.15:51522
        db: app
   Command: Sleep
      Time: 214
     State:
      Info: NULL

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

4. 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.

5. 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 <> 'Sleep'
       -> ORDER BY TIME DESC;
   +----+------------+-------------------+------+---------+------+--------------+----------------------------------------------+
   | ID | USER       | HOST              | DB   | COMMAND | TIME | STATE        | INFO                                         |
   +----+------------+-------------------+------+---------+------+--------------+----------------------------------------------+
   | 67 | report_usr | 10.20.0.33:49810  | app  | Query   |   12 | Sending data | SELECT id,total FROM orders ORDER BY id DESC |
   +----+------------+-------------------+------+---------+------+--------------+----------------------------------------------+

6. 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   | 10.20.0.15 | 1           |
   | report_usr | 10.20.0.33 | 1           |
   | root       | localhost  | 1           |
   +------------+------------+-------------+

7. Inspect the configured connection cap using the max_connections server variable.

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

8. 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 | 79    |
   +----------------------+-------+

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

9. Cancel a single long-running statement by killing only its query using the Id from the process list.

   mysql> KILL QUERY 67;
   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.

10. Terminate the entire session by killing its connection when query cancellation is insufficient.

    mysql> KILL CONNECTION 67;
    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.

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

    mysql> SHOW PROCESSLIST;
    +----+----------+-----------+------+---------+------+-------+----------------------+
    | Id | User     | Host      | db   | Command | Time | State | Info                 |
    +----+----------+-----------+------+---------+------+-------+----------------------+
    | 41 | root     | localhost | NULL | Query   |    0 | init  | SHOW PROCESSLIST     |
    | 52 | app_user | 10.20.0.15:51522 | app | Sleep |  219 |       | NULL                 |
    +----+----------+-----------+------+---------+------+-------+----------------------+

    

12. Exit the mysql client when finished.

    mysql> exit
    Bye