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.
Archives
- May 2012
- April 2012
- March 2012
- February 2012
- January 2012
- December 2011
- November 2011
- October 2011
- September 2011
- August 2011
- July 2011
- June 2011
- May 2011
- April 2011
- March 2011
- February 2011
- January 2011
- December 2010
- November 2010
- October 2010
- September 2010
- November 2009
- March 2008
- November 2007
- October 2007
Comments
Thanx for all the replies and clarifications, folks -- they are all correct and helpful.
Shlomi -- even better!
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.txtThere 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 |uniqOr, 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>
Reply