几天前我问过 如何将 php 和 nginx 日志记录到集中式 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"));
};
用于测试目的的 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 ]
我尝试创建一个文件目标并且一切正常,然后我尝试用以下方法替换我的目标:
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 日志格式完全相同的格式写入内容。
我认为我遗漏了一些非常简单的东西,或者我需要在源和目标之间进行一些解析。
任何帮助都感激不尽 :)
答案1
我可以帮您将 nginx 访问日志写入 postgres。开始吧 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_response_time';
access_log /var/log/nginx/access_logtodb.log logtodb;
我们走吧。
配置 syslog-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
配置 db-postgres 创建数据库 nginx_logs。在 pg_hba.conf 中授予访问权限