fluentd mysql plugin設定

nginxの設定

• /etc/nginx/conf.d/log.conf

log_format ltsv 'time:$time_iso8601\t'
                'ip:$remote_addr\t'
                'method:$request_method\t'
                'host:$host\t'
                'uri:$request_uri\t'
                'protocol:$server_protocol\t'
                'status:$status\t'
                'referer:$http_referer\t'
                'ua:$http_user_agent\t'
                'size:$bytes_sent\t'
                'request_time:$request_time\t';

fluentdの設定

• /etc/td-agent/td-agent.conf

<source>
  type tail
  format ltsv
  time_key time
  time_format %Y-%m-%dT%H:%M:%S%z
  tag nginx.access.log
  path /var/log/nginx/access.log
  pos_file /var/log/td-agent/buffer/access.log.pos
</source>

<match nginx.access.log>
  type copy
  <store>
  type mysql
  host localhost
  database access 
  username root 
  password 
  include_time_key yes
  key_names time,ip,method,host,uri,protocol,status,referer,ua,size,request_time 
  table customer_action_logs
  sql INSERT INTO access_log (time,ip,method,host,uri,protocol,status,referer,ua,size,request_time) VALUES (?,INET_ATON(?),?,?,?,?,?,?,?,?,?)
  flush_interval 10s
  </store>

  <store>
    type file
    path /var/log/td-agent/request.json
  </store>
</match>

MySQLのテーブルを作成

CREATE TABLE `access_log` (
 `id`           bigint(20)  unsigned  NOT NULL AUTO_INCREMENT,
 `time`         datetime    NOT NULL  DEFAULT '0000-00-00 00:00:00',
 `ip`           int(10)     unsigned  NOT NULL COMMENT 'リモートIPアドレス',
 `method`       enum('GET','POST','HEAD','CONNECT','PUT','DELETE','TRACE','OPTIONS') 
                                      NOT NULL DEFAULT 'GET' COMMENT 'HTTP メソッド 通常GET またはPOST',
 `host`         varchar(255) NOT NULL COMMENT '仮想ホスト名',
 `uri`          varchar(500) DEFAULT NULL,
 `protocol`     enum('HTTP/1.0','HTTP/1.1','HTTP/2.0','')
                                      NOT NULL COMMENT 'HTTPプロトコル',
 `status`       varchar(5)   DEFAULT      NULL COMMENT 'HTTPステータス',
 `referer`      varchar(255)          NOT NULL COMMENT 'リファラー',
 `ua`           varchar(255) DEFAULT      NULL COMMENT 'ユーザーエージェント',
 `size`         int(10)      unsigned NOT NULL COMMENT 'リクエストサイズ',
 `request_time` float        unsigned NOT NULL COMMENT 'リクエストタイム',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8