DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Importing Apache Access Logs

10.22.2011
| 2787 views |
  • submit to reddit
        // Change the apache config file to save the access log in a specified format and then import the text file into database.

# cat /etc/httpd/conf/httpd.conf | grep LogFormat
#Old Format to be changed to the new format
#LogFormat "%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"" combined
LogFormat "\"%h\" \"%l\" \"%u\" \"%{%Y-%m-%d %H:%M:%S}t\" \"%r\" \"%>s\" \"%b\" \"%{Referer}i\" \"%{User-Agent}i\" \"%D\" \"%T\" \"%q\" \"%f\" \"%v\" " combined

use db_Name;

CREATE TABLE `access_log` (
  `ip` varchar(100) NOT NULL default '',
  `RFC` varchar(100) default NULL,
  `user` varchar(100) default NULL,
  `mytime` datetime default NULL,
  `request` varchar(100) default NULL,
  `response` int(11) NOT NULL default '0',
  `bytes` bigint(20) NOT NULL default '0',
  `referer` varchar(100) default NULL,
  `browser` varchar(500) default NULL,
  `microseconds` bigint(20) default NULL,
  `milliseconds` int(11) default NULL,
  `queryString` varchar(1000) default NULL,
  `referrer` varchar(1000) default NULL,
  `virtualHost` varchar(255) default NULL,
  UNIQUE KEY `mygroup` (`ip`,`RFC`,`user`,`mytime`,`request`)
) ENGINE=MyISAM 

mysqlimport --ignore --fields-terminated-by ' ' --fields-enclosed-by '"' test /var/log/httpd/access_log