一次使用logstash读取S3存储桶访问日志到mysql的实践

环境安装

首先下载logstash和jruby，并且配置环境变量

#java 
export JAVA_HOME=/usr/java/jdk1.8.0_144

#path
export PATH=$PATH:$JAVA_HOME/bin
export PATH=$PATH:/home/ubuntu/logstash/jruby-9.2.12.0/bin
export LOGSTASH_PATH=/home/ubuntu/logstash/logstash-7.8.0

安装logstash-output-jdbc插件

bin/logstash-plugin list   查看支持的插件
bin/logstash-plugin install logstash-output-jdbc   安装输出到数据库的插件

上传jdbc驱动和IP库

写logstash配置文件

input {
   s3 {
    "access_key_id" => "aaaaa"
    "secret_access_key" => "bbbbbbb"
    "region" => "eu-west-1"
    "bucket" => "bucket_name"
    "prefix" => "log_"
    "interval" => "5"
    "delete" => true
    "additional_settings" => {
      "force_path_style" => true
      "follow_redirects" => false
    }
  }
}



filter {
  grok {
    match => { "message" => '(.*) %{NOTSPACE:bucket_name} \[%{HTTPDATE:timestamp}\] %{IP:access_ip} (.*?) (.*?) (.*?) %{NOTSPACE:file_path} (.*?) (.*?) (.*?) %{INT:return_code} (.*?) %{INT:response_size} %{NOTSPACE:file_size}' }
  }

  geoip {
    database => "/home/ubuntu/logstash/logstash-7.8.0/geodb/GeoLite2-Country.mmdb"
    source => "access_ip"
    target => "geoip"
    fields => ["country_name","region_name", "city_name", "ip", "longitude", "latitude", "location"]
  }

  date {
    match => ["timestamp", "dd/MMM/yyyy:HH:mm:ss Z"]
    target => "@timestamp"
  }
}


output {
  if [return_code] == "200" or [return_code] == "206" or [return_code] == "304" or [return_code] == "412" {
    jdbc {
      driver_jar_path => "/home/ubuntu/logstash/logstash-7.8.0/jdbc/mysql-connector-java-5.1.47/mysql-connector-java-5.1.47-bin.jar"
      driver_class => "com.mysql.jdbc.Driver"
      connection_string => "jdbc:mysql://0.0.0.0:3306/logstash?user=root&password=root"
      statement => [ "insert into bucket_access_log(bucket_name,access_time,access_ip,file_path,file_size,create_time,return_code,country_name,response_size) values (?,?,?,?,?,now(),?,?,?)","%{bucket_name}" ,"%{@timestamp}","%{access_ip}","%{file_path}","%{file_size}","%{return_code}","[geoip][country_name]","%{response_size}" ]
    }
  } else {
    jdbc {
      driver_jar_path => "/home/ubuntu/logstash/logstash-7.8.0/jdbc/mysql-connector-java-5.1.47/mysql-connector-java-5.1.47-bin.jar"
      driver_class => "com.mysql.jdbc.Driver"
      connection_string => "jdbc:mysql://0.0.0.0:3306/logstash?user=root&password=root"
      statement => [ "insert into bucket_access_log(bucket_name,access_time,access_ip,file_path,file_size,create_time,return_code,country_name,response_size) values (?,?,?,?,0,now(),?,?,?)","%{bucket_name}" ,"%{@timestamp}","%{access_ip}","%{file_path}","%{return_code}","[geoip][country_name]","%{response_size}" ]
    }
  }
}

配置文件放在logstash的config目录下。

启动logstash

bin/logstash -f ./config/s3/logstash-s3.conf --config.reload.automatic &

附录

grok字段在线测试（需要翻墙）：http://grokdebug.herokuapp.com/
grok自带的正则比配仓库：https://github.com/elastic/logstash/blob/v1.4.2/patterns/grok-patterns

结束！！！