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....

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

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

asked	today
viewed	18 times
active	today

Import log file content to MySQL database using Perl or Python

1 Answer

Your Answer

Not the answer you're looking for? Browse other questions tagged python mysql perl or ask your own question.

Import log file content to MySQL database using Perl or Python

1 Answer

Your Answer

Sign up or login

Post as a guest

Not the answer you're looking for? Browse other questions tagged python mysql perl or ask your own question.

Related