import existing log files to mysql

[inlandpac]

Hello,

I've been trying to import our entire history of log files to mysql, but am having a hard time.

There are about 20 2GB files to start, but the actual imported data should be much smaller.

My log format is
[tt]211.75.91.2 - - [22/Jul/2005:09:28:50 -0400] "GET /reviews/season2soundtrack.php HTTP/1.0" 200 8105 "

http://somedomain.com/news/somepage.php"

"Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)"[/tt]

I am only interested in importing the IP address, Date/Time, requested page, and return code. I am only interested in importing actual pages and not images (so, in this case, it would be php, htm, html, cgi, and pl

So, for this log entry, I would only import
211.75.91.2
22/Jul/2005:09:28:50 -0400
/reviews/season2soundtrack.php
200

Anyone know the best process to get this done? This wouldn't be such a problem if the log files weren't so large.

 

[feherke]

Hi

For such huge files a [tt]perl[/tt] script could be a better solution, but [tt]awk[/tt] can do this also. Of course, [tt]php[/tt] is also a good option.

But first a question : why the size is the problem ? You have a solution which works for smaller files ?

And a hint. By keeping the requested file names in separate table, the database will be smaller :
[tt]
--- log table ---
IP | Date | FileId | Status
211.75.91.2 | 2005-07-22 09:28:50-04 | 1 | 200

--- file table ---
Id | File
1 | /reviews/season2soundtrack.php
[/tt]

Feherke.

http://rootshell.be/~feherke/