logo
Apache Lounge
Webmasters

 

About Forum Index Downloads Search Register Log in RSS X


Keep Server Online

If you find the Apache Lounge, the downloads and overall help useful, please express your satisfaction with a donation.

or

Bitcoin

A donation makes a contribution towards the costs, the time and effort that's going in this site and building.

Thank You! Steffen

Your donations will help to keep this site alive and well, and continuing building binaries. Apache Lounge is not sponsored.
Post new topic   Forum Index -> Other Software View previous topic :: View next topic
Reply to topic   Topic: How to make Apache logs readable by MySQL
Author
HobbyTech



Joined: 25 Jun 2006
Posts: 31
Location: USA

PostPosted: Wed 05 Jul '06 4:06    Post subject: How to make Apache logs readable by MySQL Reply with quote

I got this bit of code from one of the regulars here, on a post here, but I can't seem to find the original post. I have questions, so I hope someone will recognize this or know the answer.

The Original Post (obviously paraphrased) wrote:
1) To make the Apache logging format readable by MySQL, put this into "httpd.conf":

LogFormat \
"\"%h\",%{%Y%m%d%H%M%S}t,%>x,\"%b\",\"%{Content-Type}o\", \
\"%U\",\"%{Referer}i\",\"%{User-Agent}i\""

2) To load such a log into an MySQL table:

LOAD DATA INFILE '/local/access_log' INTO TABLE tablename
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'

3) Create a table with columns matching those in the log file.


The problem is, "httpd.conf" has more than one line beginning with "LogFormat", and I don't know which line it is I'm supposed to replace, nor which log this is supposed to format (there appear to be 4?).
httpd.conf defaults wrote:
#
# The following directives define some format nicknames for use with
# a CustomLog directive (see below).
#
LogFormat "%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"" combined
LogFormat "%h %l %u %t \"%r\" %>s %b" common
LogFormat "%{Referer}i -> %U" referer
LogFormat "%{User-agent}i" agent

# You need to enable mod_logio.c to use %I and %O
#LogFormat "%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\" %I %O" combinedio
Back to top
James Blond
Moderator


Joined: 19 Jan 2006
Posts: 7288
Location: Germany, Next to Hamburg

PostPosted: Wed 05 Jul '06 12:06    Post subject: Reply with quote

You can't load the data from the access log direcly into SQL.
maybe if you use PHP at all pages you can set a prepend auto file in PHP.ini And then you can save the user Data in the DB.
I have postet in another forum a tutrorial. Sorry it is in german, but I think it is simly enough to understand that even without learning german Wink

http://source-center.de/forum/showthread.php?t=2121


If you don't understand that search for a solution at sourceforge.net.
Don't invent the wheel a second time!!!
Back to top
Jorge



Joined: 12 Mar 2006
Posts: 376
Location: Belgium

PostPosted: Wed 05 Jul '06 19:19    Post subject: Reply with quote

There are 2 solution:

1) use a perl script via piped logging that will write to mysql...
Pro:
- Full controle... not hard if you know perl
Cons:
- To slow IMHO
- Memory Hog!!!!!

2) Use one of the mysql log modules... there are a few out there for 2.0
Pro:
- Fast
- Hardly n owork on your part
Cons:
- Non for 2.2 atm, maybe if you self compile them
- Hard work getting it to work with 2.2
Back to top
HobbyTech



Joined: 25 Jun 2006
Posts: 31
Location: USA

PostPosted: Thu 06 Jul '06 9:16    Post subject: Reply with quote

If it's difficult, I probably don't want to bother. I was going to apply this technique because it seemed (from what the person posted) to be a simple-enough configuration option.

Maybe I'll eventually use a module, as suggested. For now, MySQL seems to work fine and Apache has no problem communicating with it...I've just got done making a table for testing and writing a few simple scripts to add records and query the DB, and that works fine.
Back to top
James Blond
Moderator


Joined: 19 Jan 2006
Posts: 7288
Location: Germany, Next to Hamburg

PostPosted: Thu 06 Jul '06 15:45    Post subject: Reply with quote

HobbyTech how does that look in httpd.conf or where did you do that and how?
Back to top
Jorge



Joined: 12 Mar 2006
Posts: 376
Location: Belgium

PostPosted: Fri 07 Jul '06 9:43    Post subject: Reply with quote

James Blond wrote:
HobbyTech how does that look in httpd.conf or where did you do that and how?


Isupose he set up the log to write a SQL file or a comma seperated file... that he then copies somewhere, emties the original file and inports the copy.

This seems slow IMHO
Back to top


Reply to topic   Topic: How to make Apache logs readable by MySQL View previous topic :: View next topic
Post new topic   Forum Index -> Other Software