List logged-in MySQL users

Question

I'd like to have a script that shows the users who are logged in to my server through MySQL. Right now I use

show processlist;

and manually open each of the BLOBs to see which entries correspond to the server itself, which correspond to robots, which correspond to inactive users, and so forth. But there has to be a better way!

I was imagining a Perl script, but perhaps there's a better way. Surely it is not rare to wonder who is logged in -- is there some standard tool?

Answer 1

Starting with MySQL 5.1.7 you can just use the INFORMATION_SCHEMA PROCESSLIST table:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST

• Charles: This is a good answer, and +1 for that. But I'm stuck on 5.0 for reasons outside my control (legacy system).
• BluesRockAddict: In that case, this might help: 2bits.com/articles/…. By tweaking the processlist fields in egrep expression, you should be able to get close to what you need.

Answer 2

Does something like this help?

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my $user = "root";
my $pass = "password";
my $db = "information_schema";
my $dbh = DBI->connect("DBI:mysql:$db", $user, $pass)
 or die "Connection error: $DBI::errstr\n";
my $sql = "show processlist";
my $sth = $dbh->prepare($sql);
$sth->execute() or die "SQL error: $DBI::errstr\n";
my %user;
while (my @row = $sth->fetchrow_array()) {
 $user{$row[1]}++;
}
print "MySQL - Logged in users\n";
print "=" x 15, "\n";
for my $x (keys %user) {
 print "$x - $user{$x} connections\n";
}

Result:

MySQL - Logged in users
===============
ndoutils - 6 connections
root - 1 connections
nagiosql - 5 connections

• Charles: I've modified it to suit my needs. This was very helpful.

Answer 3

Innotop is a great tool for that and even showing what the user is doing, with configurable refresh rate and a bunch more features. It s in perl, but already written for you :)