所以前两天我问如何将php和nginx日志logging到集中的MySQL数据库 ，并且m0ntassar给了一个完美的答案:)加油！
我现在面临的问题是，我似乎无法得到它的工作。
syslog-ng版本：
# syslog-ng --version syslog-ng 3.2.5 这是我的nginx日志格式：
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 nginx { file( "/var/log/nginx/tg-test-3.access.log" follow_freq(1) flags(no-parse) ); };
syslog-ng目的地：
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")); };
用于testing目的的MySQL表：
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
现在，首先出现错误的是当我重新启动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 ]
我已经尝试创build一个文件目的地，它一切工作正常，然后我已经尝试更换我的目的地：
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")); };
哪些工作，它正在写东西到MySQL，
问题是，我想写的东西，确切格式为Nginx的日志格式是。
我假设我缺less一些非常简单的东西，或者我需要在源和目标之间进行一些parsing。
任何帮助都感激不尽 ：）
我可以帮你写PostgreSQL的nginx访问日志。 让我们去Nginx的日志格式
log_format logtodb '$time_iso8601;$http_host;$remote_addr;$http_x_forwarded_for;$request_method;$request;$status;$body_bytes_sent;$http_referer;$request_time;$upstream_http_x_cache;$uri;$upstream_addr;$host;$upstream_response_length;$upstream_status;$server_name;$newurl;$upstream_respons e_time';
access_log /var/log/nginx/access_logtodb.log logto db; 我们走吧。
configurationsyslog-ng
nano /etc/syslog-ng/syslog.ng ######################################################################### ###### tailing nginx accesslog and sending to syslog-ng################## source nginx_acceess_log { file( "/var/log/nginx/access_logtodb.log" follow_freq(1) flags(no-parse) ); }; parser p_nginx_acceess_log { csv-parser(columns("NGINX_TIME", "NGINX_http_host", "NGINX_remote_addr", "NGINX_http_x_forwarded_for", "NGINX_request", "NGINX_request_method","NGINX_status", "NGINX_body_bytes_sent", "NGINX_http_referer", "NGINX_request_time", "NGINX_upstream_http_x_cache", "NGINX_uri", "NGINX_upstream_addr", "NGINX_host", "NGINX_upstream_response_length", "NGINX_upstream_status", "NGINX_server_name", "NGINX_newurl", "NGINX_upstream_response_time") flags(escape-double-char,strip-whitespace) delimiters(";") quote-pairs('""[]') ); }; destination d_postgres_nginx_acceess_log{ sql(type(pgsql) host("10.12.1.1") port("5432") username("postgres") password("A1s2gdfgdfgdgdfgd") database("nginx_logs") table("access_log_nginx_223") columns("NGINX_TIME text", "NGINX_http_host text", "NGINX_remote_addr text", "NGINX_http_x_forwarded_for text", "NGINX_request_method text", "NGINX_request text", "NGINX_status varchar(3)", "NGINX_body_bytes_sent text", "NGINX_http_referer text", "NGINX_request_time text", "NGINX_upstream_http_x_cache text", "NGINX_uri text", "NGINX_upstream_addr text", "NGINX_host text", "NGINX_upstream_response_length text", "NGINX_upstream_status varchar(3)", "NGINX_server_name text", "NGINX_newurl text", "NGINX_upstream_response_time text") values("${NGINX_TIME}", "${NGINX_http_host}", "${NGINX_remote_addr}", "${NGINX_http_x_forwarded_for}", "${NGINX_request_method}", "${NGINX_request}", "${NGINX_status}", "${NGINX_body_bytes_sent}", "${NGINX_http_referer}", "${NGINX_request_time}", "${NGINX_upstream_http_x_cache}", "${NGINX_uri}", "${NGINX_upstream_addr}", "${NGINX_host }", "${NGINX_upstream_response_length}", "${NGINX_upstream_status}", "${NGINX_server_name}", "${NGINX_newurl}", "${NGINX_upstream_response_time}") indexes("NGINX_request", "NGINX_uri", "NGINX_server_name")); }; log {source(nginx_acceess_log); parser(p_nginx_acceess_log); destination(d_postgres_nginx_acceess_log); };
安装数据包发送到数据库
apt-get install libdbd-pgsql -y
configurationdb-postgres创build数据库nginx_logs。 授予pg_hba.conf的权限