Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Is there some convenient way to see which users are accessing a MySQL database? Ideally I'd have some kind of application making this easy, but other methods would be OK.

If there isn't a standard definition, I would accept "have queried the database in the last 30 minutes".

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

How about the query:

SHOW FULL PROCESSLIST

This will show you connections to the database, what user they are logged in with, the state of the connection, and any currently running command.

share|improve this answer
    
Great. I've created a user with only select_priv and a script that uses that user to run that command so I can parse it to see if it's the only user in the result. This should work! Thanks. –  Charles Sep 17 '10 at 22:23
add comment

From the command line (assuming you have a .my.cnf folder in your home directory),

echo Total\ Connections:; mysqladmin processlist | tail -n +4 | head -n -1 | grep Sleep | wc -l ; echo ; echo Current\ Users: ; mysqladmin processlist | tail -n +4 | head -n -1 | grep Sleep | awk '{print $4}' | sort | uniq

This gives a nice printout of unique users and total connections.

share|improve this answer
    
This server is actually Windows 2003, but +1 for a comprehensive one-line solution. (Maybe I'll get to use it on one of my Linux boxen...?) –  Charles Sep 22 '10 at 6:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.