采用nginx收集站点请求，包括post

1.日志格式

log_format main 'remote_addr="$remote_addr" remote_user="$remote_user" time_local="$time_local" http_host="$http_host" request="$request" '
'status=$status body_bytes_sent=$body_bytes_sent http_referer="$http_referer" request_time="$request_time" '
'request_method="$request_method" request_body="$request_body" '
'http_user_agent="$http_user_agent" http_x_forwarded_for="$http_x_forwarded_for" upstream_cache_status="$upstream_cache_status"';

2.导入数据库

如果需要可以导入数据库

创建数据库，将数据先导入到log_text字段再进行分离处理；

CREATE TABLE `logs` (
`id` int DEFAULT NULL,
`log_text` text,
`remote_addr` varchar(32) DEFAULT NULL,
`remote_user` varchar(64) DEFAULT NULL,
`time_local` varchar(64) DEFAULT NULL,
`http_host` varchar(64) DEFAULT NULL,
`request` varchar(512) DEFAULT NULL,
`status` varchar(16) DEFAULT NULL,
`body_bytes_sent` varchar(24) DEFAULT NULL,
`http_referer` varchar(255) DEFAULT NULL,
`request_time` varchar(16) DEFAULT NULL,
`request_method` varchar(16) DEFAULT NULL,
`request_body` text,
`http_user_agent` varchar(255) DEFAULT NULL,
`http_x_forwarded_for` varchar(255) DEFAULT NULL,
`class_name` varchar(255) DEFAULT NULL,
`method` varchar(255) DEFAULT NULL,
`log_date` varchar(32) DEFAULT NULL,
`log_time` varchar(32) DEFAULT NULL,
KEY `idx_request_time` (`request_time`),
KEY `idx_log_date` (`log_date`),
KEY `idx_log_time` (`log_time`),
KEY `idx_class_name` (`class_name`),
KEY `idx_method` (`method`),
KEY `idx_remote_addr` (`remote_addr`),
KEY `idx_request` (`request`),
KEY `idx_sent_bytes` (`body_bytes_sent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

 

3.分离字段

update logs set
remote_addr=substr(log_text,locate("remote_addr=",log_text)+LENGTH("remote_addr="),locate("remote_user=",log_text)-locate("remote_addr=",log_text)-LENGTH("remote_addr=")),
remote_user=substr(log_text,locate("remote_user=",log_text)+LENGTH("remote_user="),locate("time_local=",log_text)-locate("remote_user=",log_text)-LENGTH("remote_user=")),
time_local=substr(log_text,locate("time_local=",log_text)+LENGTH("time_local="),locate("http_host=",log_text)-locate("time_local=",log_text)-LENGTH("time_local=")),
http_host=substr(log_text,locate("http_host=",log_text)+LENGTH("http_host="),locate("request=",log_text)-locate("http_host=",log_text)-LENGTH("http_host=")),
request=substr(log_text,locate("request=",log_text)+LENGTH("request="),locate("status=",log_text)-locate("request=",log_text)-LENGTH("request=")) ,
status=substr(log_text,locate("status=",log_text)+LENGTH("status="),locate("body_bytes_sent=",log_text)-locate("status=",log_text)-LENGTH("status=")),
body_bytes_sent=substr(log_text,locate("body_bytes_sent=",log_text)+LENGTH("body_bytes_sent="),locate("http_referer=",log_text)-locate("body_bytes_sent=",log_text)-LENGTH("body_bytes_sent=")) ,
http_referer=substr(log_text,locate("http_referer=",log_text)+LENGTH("http_referer="),locate("request_time=",log_text)-locate("http_referer=",log_text)-LENGTH("http_referer=")) ,
request_time=substr(log_text,locate("request_time=",log_text)+LENGTH("request_time="),locate("request_method=",log_text)-locate("request_time=",log_text)-LENGTH("request_time=")) ,
request_method=substr(log_text,locate("request_method=",log_text)+LENGTH("request_method="),locate("request_body=",log_text)-locate("request_method=",log_text)-LENGTH("request_method=")) ,
request_body=substr(log_text,locate("request_body=",log_text)+LENGTH("request_body="),locate("http_user_agent=",log_text)-locate("request_body=",log_text)-LENGTH("request_body=")) ,
http_user_agent=substr(log_text,locate("http_user_agent=",log_text)+LENGTH("http_user_agent="),locate("http_x_forwarded_for=",log_text)-locate("http_user_agent=",log_text)-LENGTH("http_user_agent=")) ,
http_x_forwarded_for=substr(log_text,locate("http_x_forwarded_for=",log_text)+LENGTH("http_x_forwarded_for=")),
class_name=substr(log_text,locate("<ClassName>",log_text)+LENGTH("<ClassName>"),locate("</ClassName>",log_text)-locate("<ClassName>",log_text)-LENGTH("<ClassName>")),
method=substr(log_text,locate("<MethodName>",log_text)+LENGTH("<MethodName>"),locate("</MethodName>",log_text)-locate("<MethodName>",log_text)-LENGTH("<MethodName>")),
log_date=substr(time_local,2,11),
log_time=substr(time_local,14,8)