How to know if a user never connected to the MySQL server since last boot ?

Performance_Schema is used most of the time to get metrics about queries and connections. But it can also provide other very useful information.

So today, I will show you how you can see a list of users that didn’t connect to MySQL since we restarted it (since last reboot).

SELECT DISTINCT mu.user FROM mysql.user mu
LEFT JOIN performance_schema.users psu
ON mu.user = psu.user
WHERE psu.user IS NULL
AND mu.user NOT IN ('mysql.infoschema', 'mysql.session', 'mysql.sys')
ORDER BY mu.user;

Example:

mysql> SELECT DISTINCT mu.user FROM mysql.user mu
    ->       LEFT JOIN performance_schema.users psu 
    ->       ON mu.user = psu.user  
    ->       AND mu.user NOT IN ('mysql.infoschema', 'mysql.session', 'mysql.sys')
    ->       WHERE psu.user IS NULL ORDER BY mu.user;
+------------------+
| user             |
+------------------+
| fred             |
| myuser           |
+------------------+
2 rows in set (0.00 sec)

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Leave a Reply

Your email address will not be published. Required fields are marked *

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.