fastread homefastrread library fastread menu

MariaDB : Find Users logged into MariaDB

Tutorial by:      Date: 2016-04-18 05:47:58

❰ Previous Next ❱

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

 

❰ Previous Next ❱


MariaDB

Submit Your Thought, Tutorial, Articls etc.

Submit Your Information India's Number one online promotion website