syslog-ng and nging logs to mysql
- by Katafalkas
So couple of days ago I asked
how to log php and nginx logs to centralized MySQL database, and m0ntassar gave a perfect answer :) cheer !
The problem I am facing now is that I can not seem to get it working.
syslog-ng version:
# syslog-ng --version
syslog-ng 3.2.5
This is my nginx log format:
log_format main '$remote_addr - $remote_user [$time_local] "$request" '
'$status $body_bytes_sent "$http_referer" '
'"$http_user_agent" "$http_x_forwarded_for"';
syslog-ng source:
source nginx { file( "/var/log/nginx/tg-test-3.access.log"
follow_freq(1)
flags(no-parse)
);
};
syslog-ng destination:
destination d_sql {
sql(type(mysql)
host("127.0.0.1") username("syslog") password("superpasswd")
database("syslog")
table("nginx")
columns("remote_addr","remote_user","time_local","request","status","body_bytes_sent","http_ referer","http_user_agent","http_x_forwarded_for")
values("$REMOTE_ADDR", "$REMOTE_USER", "$TIME_LOCAL", "$REQUEST", "$STATUS","$BODY_BYTES_SENT", "$HTTP_REFERER", "$HTTP_USER_AGENT", "$HTTP_X_FORWARDED_FOR"));
};
MySQL table for testing purposes:
CREATE TABLE `nginx` (
`remote_addr` varchar(100) DEFAULT NULL,
`remote_user` varchar(100) DEFAULT NULL,
`time` varchar(100) DEFAULT NULL,
`request` varchar(100) DEFAULT NULL,
`status` varchar(100) DEFAULT NULL,
`body_bytes_sent` varchar(100) DEFAULT NULL,
`http_referer` varchar(100) DEFAULT NULL,
`http_user_agent` varchar(100) DEFAULT NULL,
`http_x_forwarded_for` varchar(100) DEFAULT NULL,
`time_local` text,
`datetime` text,
`host` text,
`program` text,
`pid` text,
`message` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Now first thing that goes wrong is when I restart syslog-ng:
# /etc/init.d/syslog-ng restart
Stopping syslog-ng: [ OK ]
Starting syslog-ng: WARNING: You are using the default values for columns(), indexes() or values(), please specify these explicitly as the default will be dropped in the future;
[ OK ]
I have tried creating a file destination and it all works fine, and then I have tried replacing my destination with:
destination d_sql {
sql(type(mysql)
host("127.0.0.1") username("syslog") password("kosmodromas")
database("syslog")
table("nginx")
columns("datetime", "host", "program", "pid", "message")
values("$R_DATE", "$HOST", "$PROGRAM", "$PID", "$MSGONLY")
indexes("datetime", "host", "program", "pid", "message"));
};
Which did work and it was writing stuff to mysql,
The problem is that I want to write stuff to in exact format as nginx log format is.
I assume that I am missing something really simple or I need to do some parsing between source and destination.
Any help will be much appreciated :)
