Getting a List of Users From the MySQL General Log

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.

Comments

Thanx for all the replies and clarifications, folks -- they are all correct and helpful.

Sheeri Cabral
Mon, 10/31/2011 - 12:35

Shlomi -- even better!

Sheeri Cabral
Mon, 10/31/2011 - 12:34

I noticed a few quirks in this script. The most damaging is probably "..users.txt", which may not be what you had in mind.

If you want your script to be readable, I suggest the following (longer than your file, because I added some defensive code):


#!/usr/bin/perl
use strict;
use warnings;
use English qw( -no_match_vars);
my $infile=shift
or die "general log file required\n";
my $users_file= '../users.txt';
my %seen=();
open my $INPUT, '<', $infile
or die "can't open $infile ($OS_ERROR)";
while (my $line = <$INPUT>)
{
if ($line=~/Connect\s*(\S+@\S+)/)
{
$seen{$1}++;
}
}
close $INPUT;
open my $OUTPUT, '>>', $users_file
or die "can't open $users_file";
for my $user (keys %seen)
{
print $OUTPUT "$user\n";
}
close $OUTPUT;

(Code formatting in this page is awful. Better seen at http://pastebin.com/mA6UUPZn)

If you are looking for a shorter solution, native Perl speakers can tell you that you may accomplish the same result with a one-liner:

perl -nle 'print $1 if /Connect\s*(\S+\@\S+)/' general.log >> users.txt

There is another problem, though. If you run this application several times, "users.txt" will contain duplicates. You should get the list of users with sort users.txt |uniq

Sun, 10/30/2011 - 02:23

Or, you can use <a href="http://openarkkit.googlecode.com/svn/trunk/openarkkit/doc/html/oak-hook-general-log.html">oak-hook-general-log</a> to audit the general log and dump it to standard output.To just audit users, use:<ul><li>oak-hook-general-log --user=... --socket=... --filter-connection</li></ul> 

Sun, 10/30/2011 - 01:18

Reply

The content of this field is kept private and will not be shown publicly.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

More information about formatting options

By submitting this form, you accept the Mollom privacy policy.
Website by Digital Loom