Getting a List of Users From the MySQL General Log

Posted in: Technical Track

From time to time, organizations want to know if there are any users that are not used. For clients using MySQL 5.1 and up, that can handle a 1% overhead for general logging, we will enable the general log for a period of time and analyze the users that connect.

Note: we have some extremely busy clients, and we very rarely have a problem turning the general log on, other than making sure we’re rotating and compressing logs so we do not run out of disk space.

Once we have the logs, I run this little perl tool I made — I call it


my $infile=$ARGV[0];

my %seen=();

my @uniq=();

open (INPUT, “<$infile”);

while (<INPUT>) {

my $line=$_;

if ($line=~/Connect/) {

if ($line=~/(\S*@\S*)/) { push(@uniq, $1) unless $seen{$1}++; }

} # end if line matches Connect


close INPUT;

open (OUTPUT, “>>..users.txt”);


print OUTPUT (keys %seen);

print OUTPUT (“\n”);

close OUTPUT;


I hope it is useful for whoever stumbles on this; I know it has been useful for me in the past — it’s just doing some string matching, and I bet if I used Python it would be done in half the lines, but it’s already fewer than 20 lines, so it’s pretty small to begin with.


Interested in working with Sheeri? Schedule a tech call.

No comments

Leave a Reply

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