Blog

In this guide, we will discuss how to 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:

Next Topic : Click Here