List of logged users into mysql databases

Mysql can be determined with mysql cli with the following commands to determine who is logged into the mysql database and which transactions are performed. The same information can be accessed with the help of queries.

First of all, let’s make mysql cli connection.

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

We 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)

We can find the number of links that have been made so far or how many people have been connected so far with the value of Connections in the output of the “show status like” Conn% ” command.

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)

For more detailed information, we can query like ‘% onn%’ as below.

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)

You can query the process list as below :

mysql> select * from information_schema.processlist

 

Please follow and like us:

Leave a Reply

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