insert data strings from log file to mysql database

Question

Me and my workmate would like to fetch data from squid server via access.log to mysql server on another Debian server.

We've found sort of guide to that kind of things on the internet and made a script that will put data in mysql from string in access.log file. but this doesnt seems to be working, something might be with the insert thing, no idea here. Please help us to find out what we need to sort out.

Heres a script:

#!/bin/bash

cp /www/logs/squid/access.log /tmp/squidforparse.log
>/www/logs/squid/access.log
awk '{print "INSERT INTO squid (ip,bytes,link,trans,time) \
VALUES(\""$3"\","$5",\""$7"\",\""$9"\",from_unixtime("$1"));"};' \
< /tmp/squidforparse.log | mysql -D traffics -u root --password=my_sql_passwd
rm -f /tmp/squidforparse.log

I am not really that great at sql, though i do know most of the operators and functions at base level, still i can't figure out whats not making it work.

Instead of constructing SQL, why not just use LOAD DATA INFILE? It's much more forgiving. — tadman
– tadman, Commented Aug 27, 2013 at 18:00

beck03076 · Accepted Answer · 2013-08-27 18:55:01Z

1

If your log file looks like below,

"apple","red",1230,"Newton","Physics","Da vinci code"
"iphone","black",44500,"Jobs","Mobiles","telephone booth"
"shoe","brown",9900,"Elizabeth","Fashion","shoe shop"

My table structure looks like below,

table_name = t1
columns = topic, price, department

then I would something like below,

mysql -D traffics -u root --password=my_sql_passwd

LOAD DATA LOCAL INFILE 'file.log' INTO TABLE t1 
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'  
(@col1,@col2,@col3,@col4,@col5,@col6) set topic=@col1,price=@col3,department=@col5;

Note:

Assuming the log file is comma separated, if its pipe delimited, use FIELDS TERMINATED BY '|'.
Also, note how @col1, @col2,.. are being used to store the values of the log file.

answered Aug 27, 2013 at 18:55

beck03076

3,3182 gold badges32 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kirill Pashkov Over a year ago

Thanks for answers guys. The log file looks like this.

Kirill Pashkov Over a year ago

1369057101.497 20 192.168.0.174 TCP_DENIED/407 4046 GET babylon5.ru - HIER_NONE/- text/html 1369057101.654 91 192.168.0.174 TCP_DENIED/407 4291 GET babylon5.ru - HIER_NONE/- text/html 1369057105.705 4029 192.168.0.174 TCP_MISS/200 42463 GET babylon5.ru username.nn HIER_DIRECT/ip.189.197.111 text/html

beck03076 Over a year ago

can you edit your answer and paste 5 lines of log file there?

Kirill Pashkov Over a year ago

Somehow script i've posted worked today. Also LOAD DATA tip was very handy. Thanks for advices. Cheers

DoesEatOats · Accepted Answer · 2018-07-04 16:33:18Z

0

I created the table needed in mysql. Then created the fields required. Then I used the file import and pointed to where my log file was.

Otherwise known as: File to import

Where the Format of imported file was CSV using LOAD DATA I was able to specify the file delimiter as well as the fields.

The log file with about 160000 records was successfully loaded.

This was accomplished using XAMMP control panel.

answered Jul 4, 2018 at 16:33

DoesEatOats

6797 silver badges13 bronze badges

Comments

DoesEatOats · Accepted Answer · 2018-07-18 13:11:07Z

Create a java test program first. Then code a String using a load file example e.g.,

`LOAD DATA LOCAL INFILE 'X:\\access.log' REPLACE INTO TABLE `LogRecords`
FIELDS TERMINATED BY '|'
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'(
`startDate` ,
`IP` ,
`request` ,
`threshold` ,
`useragent`
)

`String me= "LOAD DATA LOCAL INFILE 'X:/access.log/' REPLACE INTO TABLE `logrecords"+"`\n"+
     "FIELDS TERMINATED BY \'|\'\n"+
     "ENCLOSED BY \'\"\'\n"+
     "ESCAPED BY \'\\\\\'\n"+
     "LINES TERMINATED BY \'\\r\\n\'(\n"+
     "`startDate` ,\n"+
     "`IP` ,\n"+
     "`request` ,\n"+
     "`threshold` ,\n"+
     "`useragent`\n"+
     ")";

    System.out.println("" +me);

Compile and fix your errors. Run an executeUpdate(me);

Collectives™ on Stack Overflow

insert data strings from log file to mysql database

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related