Mysql Find Users Logged Into Mysql
MySQL: Find Users logged into MySQL
Question:Is there a query to run that will return all Users that are currently logged into MySQL?
Answer: In MySQL, there is a system table called information_schema.processlist which shows the threads that are currently running. You can run a query against this system table that returns all of the Users that are currently have a connection running in the MySQL database.
To retrieve all Users logged into MySQL, you can execute the following SQL statement:
SELECT SUBSTRING_INDEX(host, ':', 1) AS host_short,
GROUP_CONCAT(DISTINCT user) AS users,
COUNT(*) AS threads
FROM information_schema.processlist
GROUP BY host_short ORDER BY COUNT(*), host_short;This SELECT statement will return the host, the user, and the number of threads for that host/user combination.
Note
- You must have PROCESS privileges to see threads owned by other users. Otherwise, you will only see your own threads.
- The information_schema.processlist system table was introduced in MySQL 5.1.7.
The information_schema.processlist table contains the following columns:
| Column | Explanation |
|---|---|
| ID | Unique identifier |
| USER | User name (ie: root, AODBA, etc) |
| HOST | Host for the user |
| DB | Database that thread is running in |
| COMMAND | Command that is being run (ie: Query, Sleep, etc). |
| TIME | Number of seconds that thread has been running (ie: 3, 12, 353) |
| STATE | State of thread (ie: executing) |
| INFO | Displays information about the thread. (ie: if COMMAND='Query' and STATE='executing', the SQL that the user is running will be displayed) |
