List logged-in MySQL users?

MySQLMySQLi Database

To list logged-in MySQL users, you can use any of the following two methods −

First Method

Use INFORMATION_SCHEMA.PROCESSLIST

select *from INFORMATION_SCHEMA.PROCESSLIST;

Second Method

You can use SHOW PROCESSLIST command as well. Following is the syntax −

SHOW PROCESSLIST;

Let us implement the above syntaxes in order to list logged in MySQL users −

mysql> select *from information_schema.processlist;

This will produce the following output -

+----+-----------------+-----------------+------+---------+--------+-----------------------------+---------------------------------------------+
| ID | USER            | HOST            | DB   | COMMAND | TIME   | STATE                       | INFO                                        |
+----+-----------------+-----------------+------+---------+--------+-----------------------------+---------------------------------------------+
|  8 | root            | localhost:50252 | web  | Query   | 0      | executing                   | select *from information_schema.processlist |
|  4 | event_scheduler | localhost       | NULL | Daemon  | 301832 | Waiting for next activation | NULL                                        |
+----+-----------------+-----------------+------+---------+--------+-----------------------------+---------------------------------------------+
2 rows in set (0.00 sec)

Now, let us see the second query −

mysql> show processlist;

This will produce the following output -

+----+-----------------+-----------------+------+---------+--------+-----------------------------+------------------+
| Id | User            | Host            | db   | Command | Time   | State                       | Info             |
+----+-----------------+-----------------+------+---------+--------+-----------------------------+------------------+
|  4 | event_scheduler | localhost       | NULL | Daemon  | 301842 | Waiting for next activation | NULL             |
|  8 | root            | localhost:50252 | web  | Query   | 1      | starting                    | show processlist |
+----+-----------------+-----------------+------+---------+--------+-----------------------------+------------------+
2 rows in set (0.19 sec)
raja
Published on 03-Sep-2019 11:08:50
Advertisements