Getting a List of Users From the MySQL General Log

Oct 29, 2011 / By Sheeri Cabral

Tags: ,

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 genlog_users.pl:

#!/usr/bin/perl

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”);

$,=”\n”;

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.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>