Import log file content to MySQL database using Perl or Python

Question

Hope you have some time to shed light on with my current problem.

I have a log file from a program that runs in linux(Centos) that I want to export per line to MySQL Database.

This is the log file content that I currently have

Now I want each lines to be exported in MySQL, I already know how to create a Perl that works with MySQL and I already know to create a loop to read the logs and it looks like this

#!usr/bin/perl

use strict;
use warnings;

my $file = '/var/log/fop2_audit.log';
open my $info, $file or die "Could not open $file: $!";

my $count = 0;
while( my $line = <$info>)  {

use DBI;
$dbh = DBI->connect('dbi:mysql:dbname','username','password')
or die "Connection Error: $DBI::errstr\n";

$sql = "INSERT INTO `Logs`(`logs`) VALUES ($line)";

$sth = $dbh->prepare($sql);

$sth->execute
or die "SQL Error: $DBI::errstr\n";

     }

close $info;

but my problem is that how I can remove a line of log text after it's inserted to MySQL. And how can I break down the logs by "|" and insert the breakdown string to MySQL fields.

Ex.

Log: 1385168249|200|GENERAL|QUEUEREMOVE|1000|Local/200@from-queue/n

MySQL: TimeStamp: 1385168249 EXT: 200 Type: GENERAL Action: QUEUEREMOVE Queue: 1000 OtherInfo: Local/200@from-queue/n

Or I can better do this in Python?

Thank you in advance for any help....

codonnell · Accepted Answer · 2013-11-23 03:07:58Z

1

Rather than remove lines from a live log file, a safer and more common approach is to rotate the log file, then process the old file and remove it.

For the second part of your question, parsing the log lines, you can do this in Python or Perl with equivalent effort.

Here is an (untested) Perl snippet. Requires a table with an appropriate schema.

my $sth->prepare(q/
    insert into logs (tstamp, ext, type, action, queue, otherinfo)
    values (?,?,?,?,?,?)
/);
while (my $line = <$info>) {
      $line =~ s/\s+$//; # strip trailing ws
      my @fields = split(/\|/, $line);
      $sth->execute(@fields);
}

edited Nov 23, 2013 at 3:07

answered Nov 23, 2013 at 2:38

codonnell

1,6759 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ashley Over a year ago

There is a CPAN module Log::Unrotate which helps a LOT with log processing.

Collectives™ on Stack Overflow

Import log file content to MySQL database using Perl or Python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related