on 3/27/03 10:09 AM, Ronan Chilvers at ronan@thelittledot.com appended the
following bits to my mbox:
> However I have a little problem which someone might have come across or be
> able to shed some light on. I want to pipe logs directly from apache into
> mysql. At the moment I can do this using a perl script that does this sort of
> thing:-
Not sure if this helps you (or others on the list), but this is how I get
logs into MySQL, via direct piping to the command line client, no php or
perl involved:
In MySQL, I have the following table setup:
CREATE TABLE `access_log_archive` (
`id` int(11) NOT NULL auto_increment,
`remote_ip` varchar(15) NOT NULL default '',
`remote_host` varchar(255) NOT NULL default '',
`remote_domain` varchar(10) NOT NULL default '',
`remote_log_name` varchar(20) NOT NULL default '',
`remote_user` varchar(20) NOT NULL default '',
`server_name` varchar(255) NOT NULL default '',
`request_uri` varchar(255) NOT NULL default '',
`request_date` datetime NOT NULL default '0000-00-00 00:00:00',
`request_status` int(11) NOT NULL default '0',
`request_bytes_sent` int(11) NOT NULL default '0',
`request_content_type` varchar(50) NOT NULL default '',
`request_referer` varchar(255) NOT NULL default '',
`request_user_agent` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`),
) TYPE=MyISAM COMMENT='Apache Logging Table'
In the httpd.conf file, I have the following log format line:
LogFormat "INSERT INTO access_log
(remote_ip,remote_log_name,remote_user,server_name,request_uri,request_date,
request_status,request_bytes_sent,request_content_type,request_referer,reque
st_user_agent) VALUES
('%a','%l','%u','%v','%U%q','%{%Y%m%d%H%M%S}t',%>s,'%B','%{Content-Type}o','
%{Referer}i','%{User-Agent}i');" mysql
On my devel machine, I just use one log for all vhosts, so I have
CustomLog "|mysql -u apache -p{PASSWORD REMOVED} apache" mysql
CustomLog "/private/var/log/httpd/access_log" combined
On a production server with separate logs, I have the CustomLog command in
each vhost.
You still need to log to a file as well, just in case the MySQL server isn't
available. Apache doesn't like it when it can't write it's log files. Bad
things might happen.
Using the above, apache seamlessly logs the request data to the MySQL table.
I'm currently working on a nice front end query tool. Any query help would
be welcome. :-)
This gives the top servers in order of hits:
select count(*) as hits,server_name from access_log_archive group by
server_name order by hits desc;
Note: If your server is busy, you should query from another "mirrored" table
that isn't being accessed all the time. If not, you get index corruption
that shows up in things like this:
ERROR 1030: Got error 127 from table handler
You could also add indexes for that table to speed up queries and setup a
script to automatically migrate from one table to the other, etc.
Hope that helps. YMMV. TMTOWTDI.
Sincerely,
Paul Burney
<http://paulburney.com/>
<?php
while ($self != "asleep") {
$sheep_count++;
}
?>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
