How To Check Connected Users in MySQL

dbtut

5 years ago

To check connected users in MySQL database and what operations are performed, we can use mysql cli or we can obtain same information through queries.

Check Connected Users in mysql cli

[root@mysqldb1 /]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 73254
Server version: 5.6.31-ndb-7.4.12-cluster-gpl MySQL Cluster Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

[root@mysqldb1 /]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 73254

Server version: 5.6.31-ndb-7.4.12-cluster-gpl MySQL Cluster Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

You can find the list of active transactions with the “show processlist” command.

mysql> show processlist;
+-------+-------------+-----------+------+---------+------+-----------------------------------+------------------+
| Id    | User        | Host      | db   | Command | Time | State                             | Info             |
+-------+-------------+-----------+------+---------+------+-----------------------------------+------------------+
|     1 | system user |           |      | Daemon  |    0 | Waiting for event from ndbcluster | NULL             |
| 73254 | root        | localhost | mysql| Query   |    0 | init                              | show processlist |
+-------+-------------+-----------+------+---------+------+-----------------------------------+------------------+
2 rows in set (0.00 sec)

mysql> show processlist;

+-------+-------------+-----------+------+---------+------+-----------------------------------+------------------+

+-------+-------------+-----------+------+---------+------+-----------------------------------+------------------+

+-------+-------------+-----------+------+---------+------+-----------------------------------+------------------+

2 rows in set (0.00 sec)

Total Connections in MySQL

The Connections value in the result set of the script below indicates how many people have connected so far.

mysql> show status like 'Conn%';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| Connection_errors_accept          | 0     |
| Connection_errors_internal        | 0     |
| Connection_errors_max_connections | 0     |
| Connection_errors_peer_address    | 0     |
| Connection_errors_select          | 0     |
| Connection_errors_tcpwrap         | 0     |
| Connections                       | 73255 |
+-----------------------------------+-------+
7 rows in set (0.00 sec)

mysql> show status like 'Conn%';

+-----------------------------------+-------+

| Variable_name | Value |

+-----------------------------------+-------+

| Connection_errors_accept | 0 |

| Connection_errors_internal | 0 |

| Connection_errors_max_connections | 0 |

| Connection_errors_peer_address | 0 |

| Connection_errors_select | 0 |

| Connection_errors_tcpwrap | 0 |

| Connections | 73255 |

+-----------------------------------+-------+

7 rows in set (0.00 sec)

Find Active Connection Count in MySQL

For more detailed information, you can query as follows. The Threads_connected value in the result set of the script below indicates active connections.

mysql> show status like '%onn%'; 
+-----------------------------------------------+-------+
| Variable_name                                 | Value |
+-----------------------------------------------+-------+
| Aborted_connects                              | 2006  |
| Connection_errors_accept                      | 0     |
| Connection_errors_internal                    | 0     |
| Connection_errors_max_connections             | 0     |
| Connection_errors_peer_address                | 0     |
| Connection_errors_select                      | 0     |
| Connection_errors_tcpwrap                     | 0     |
| Connections                                   | 73255 |
| Max_used_connections                          | 29    |
| Ndb_connect_count                             | 0     |
| Performance_schema_session_connect_attrs_lost | 0     |
| Ssl_client_connects                           | 0     |
| Ssl_connect_renegotiates                      | 0     |
| Ssl_finished_connects                         | 0     |
| Threads_connected                             | 1     |
+-----------------------------------------------+-------+
15 rows in set (0.00 sec)

mysql> show status like '%onn%';

+-----------------------------------------------+-------+

| Variable_name | Value |

+-----------------------------------------------+-------+

| Aborted_connects | 2006 |

| Connection_errors_accept | 0 |

| Connection_errors_internal | 0 |

| Connection_errors_max_connections | 0 |

| Connection_errors_peer_address | 0 |

| Connection_errors_select | 0 |

| Connection_errors_tcpwrap | 0 |

| Connections | 73255 |

| Max_used_connections | 29 |

| Ndb_connect_count | 0 |

| Performance_schema_session_connect_attrs_lost | 0 |

| Ssl_client_connects | 0 |

| Ssl_connect_renegotiates | 0 |

| Ssl_finished_connects | 0 |

| Threads_connected | 1 |

+-----------------------------------------------+-------+

15 rows in set (0.00 sec)

MySQL Process List

It is also possible to access the process list with a query as follows.

select * from information_schema.processlist

1	select * from information_schema.processlist