Find Users logged into MariaDB
Question:Is there a query to run that will return all Users that are currently logged into MariaDB?
Answer: In MariaDB, 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 MariaDB database.
To retrieve all Users logged into MariaDB, 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.
TIP: You must have PROCESS privileges to see threads owned by other users. Otherwise, you will only see your own threads.
The information_schema.processlist table contains the following columns:
Column |
Explanation |
ID |
Unique identifier |
USER |
User name (ie: root, fastread.aitechtonic, 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: 301, 325) |
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) |
TIME_MS |
Number of milliseconds that thread has been running (ie: 301471.887, 325595.370) |
STAGE |
Stage that the thread is currently in |
MAX_STAGE |
Maximum number of stages |
PROGRESS |
Progress of the current stage (ie: 10%, 95%, etc.) |
MEMORY_USED |
Amount of memory used by the thread (ie: 50200, 82920, etc.) |
EXAMINED_ROWS |
The number of rows examined by the thread |
QUERY_ID |
ID of the query |