elk收集mysql数据库内的信息-id字段跟踪-时间字段跟踪

rpm -ivh http://192.168.130.150/ELK/logstash-6.8.5.rpm

wget https://cdn.mysql.com//Downloads/Connector-J/mysql-connector-java-5.1.47.tar.gz

tar xf mysql-connector-java-5.1.47.tar.gz

1.编辑数据库收集脚本

vi /root/jdbc.conf

 

input {
  jdbc {
  jdbc_connection_string => "jdbc:mysql://192.168.120.81:3306/mysql"
  jdbc_user => "root"
  jdbc_password => "123456"
  jdbc_driver_library => "/root/mysql-connector-java-5.1.47/mysql-connector-java-5.1.47.jar"
  jdbc_driver_class => "com.mysql.jdbc.Driver"
  jdbc_page_size => "50000"
  statement => "select user, host, authentication_string from user"
  schedule => "* * * * *"
  }
}
filter {
    json {
        source => "message"
        remove_field => ["message"]
    }
}
output{
   elasticsearch {
        hosts => ["192.168.130.134:9200"]
        index => "synces"
    }

  stdout{
     codec => "rubydebug"
}

}

 

如果有自增列可以在最后加这个可以防止日志重复

 document_id => "%{id}"

 

 

参考

https://www.cnblogs.com/xuwenjin/p/8987546.html

https://blog.csdn.net/laoyang360/article/details/51747266

 https://www.elastic.co/guide/en/logstash/6.8/plugins-inputs-jdbc.html

https://www.cnblogs.com/tianyamoon/p/10058495.html

https://my.oschina.net/xiaowangqiongyou/blog/1812708 #字段跟踪参数说明

 

参考说明 注意跟踪字段

input{
jdbc{
#数据库驱动路径
jdbc_driver_library=>"/data/mysql-connector-java-5.1.41-bin.jar"
jdbc_driver_class=>"com.mysql.jdbc.Driver"
#数据库连接相关配置
jdbc_connection_string=>"jdbc:mysql://10.10.10.10:3306/test"
jdbc_user=>"root"
jdbc_password=>"123456"
#任务计划，多久执行一次，在此每5分钟执行一次
schedule=>"*/5****"
#启用追踪，如果为true，则需要指定tracking_column，默认是timestamp
use_column_value=>true
#指定追踪的字段，在此我设置的追踪的字段为id
tracking_column=>id
#是否将字段名称转小写，当字段已经为小写时，不用此项
lowercase_column_names=>false
#追踪字段的类型，目前只有数字(numeric)和时间类型(timestamp)，默认是数字类型
tracking_column_type=>"numeric"
#记录最后一次运行的结果
record_last_run=>true
#上面运行结果的保存位置
last_run_metadata_path=>"/data/jdbc-position.txt"
#执行的语句，也可以通过statement_filepath指定sql的文件
statement=>"SELECT*FROMstudentwhereid>:sql_last_value"
#statement_filepath=>"/data/test.sql"
}
}
filter{
json{
source=>"message"
remove_field=>["message"]
}
}
output{
#输出到控制台进行调试，也可以写到kafka或者es中
stdout{codec=>rubydebug}
}

 自增列写法

input {
  jdbc {
  jdbc_connection_string => "jdbc:mysql://192.168.120.81:3306/mysql"
  jdbc_user => "root"
  jdbc_password => "123456"
  jdbc_driver_library => "/root/mysql-connector-java-5.1.47/mysql-connector-java-5.1.47.jar"
  jdbc_driver_class => "com.mysql.jdbc.Driver"
  jdbc_page_size => "50000"
  statement => "select user2, num1, num2 from zsl2"
  schedule => "* * * * *"
  }
}
filter {
    json {
        source => "message"
        remove_field => ["message"]
    }
}
output{
   elasticsearch {
        hosts => ["192.168.130.134:9200"]
        index => "synces"
        document_id => "%{num1}"
    }

  stdout{
     codec => "rubydebug"
}

}

 

通过跟踪时间防止日志重复 注意对比跟踪的文件/root/jdbc-position.txt 注意 查询的条件 where time > :sql_last_value 这里是大于号如果 => 最后一个日志

input {
  jdbc {
  jdbc_connection_string => "jdbc:mysql://192.168.120.81:3306/mysql"
  jdbc_user => "root"
  jdbc_password => "123456"
  jdbc_driver_library => "/root/mysql-connector-java-5.1.47/mysql-connector-java-5.1.47.jar"
  jdbc_driver_class => "com.mysql.jdbc.Driver"
  jdbc_paging_enabled => true
  jdbc_page_size => "50000"
  statement => "select user2, num1, num2, time from zsl2 where time > :sql_last_value"
  jdbc_default_timezone => "Asia/Shanghai"
  schedule => "* * * * *"
  use_column_value => true
  tracking_column => "time"
  tracking_column_type => "timestamp"
  #record_last_run => true
  last_run_metadata_path => "/root/jdbc-position.txt"
  clean_run => false
  }
}
filter {
    json {
        source => "message"
        remove_field => ["message"]
    }
}
output{
   elasticsearch {
        hosts => ["192.168.130.134:9200"]
        index => "synces2"
    }

  stdout{
     codec => "rubydebug"
}

}

如果使用systemctl命令启动logstash记得修改对比文件目录所有者
chown -R logstash.logstash /mysql-connector-java

 

 

下面一般用不到

mysql时间修改
https://blog.51cto.com/net881004/2375161?source=dra
FLUSH PRIVILEGES;# 刷新权限

 

报错处理

有个提示 warning

WARNING: Could not find logstash.yml which is typically located in $LS_HOME/config or /etc/logstash. You can specify the path using --path.settings. Continuing using the defaults
Could not find log4j2 configuration at path //usr/share/logstash/config/log4j2.properties. Using default config which logs errors to the console
解决办法：

mkdir -p /usr/share/logstash/config/
ln -s /etc/logstash/* /usr/share/logstash/config
chown -R logstash:logstash /usr/share/logstash/config/
bin/logstash -e 'input { stdin { } } output { stdout {} }'

 

 

多文件type 判断

vi /etc/logstash/conf.d/file.conf

input {
beats {
    host => "192.168.130.134"
    port => 5044
    type => "weifuwu"
  }
}

 filter {
    grok {
        match => {
#             "message" => "(?<datelogs>(?:.*)?) (?<levels>(?:\[[A-Z]+\])?) (?<message>(?:[\s\S]*)?)"
              "message" => "%{DYTIME:datelogs} %{DYLEVELS:levels} %{DYMESSAGE:message}"
        }
        overwrite => ["message"]
        remove_field => ["[beat][version]", "[beat][hostname]", "@version", "tags", "[beat][name]"]
    }
}


# filter {
#    mutate {
#        rename => {"message" => "new_message"}
#    }
# }

output {
    if [type] == "weifuwu" {
 elasticsearch {
 hosts => ["192.168.130.134:9200"]
 index => "dylog-%{+YYYY.MM.dd}"
 }
}
}

# output {stdout{codec => rubydebug}}

vi /etc/logstash/conf.d/jdbc-time.conf

input {
  jdbc {
  jdbc_connection_string => "jdbc:mysql://192.168.120.81:3306/mysql"
  jdbc_user => "root"
  jdbc_password => "123456"
  jdbc_driver_library => "/mysql-connector-java/mysql-connector-java-5.1.47.jar"
  jdbc_driver_class => "com.mysql.jdbc.Driver"
  jdbc_paging_enabled => true
  jdbc_page_size => "50000"
  statement => "select user2, num1, num2, time from zsl2 where time > :sql_last_value"
  jdbc_default_timezone => "Asia/Shanghai"
  schedule => "* * * * *"
  use_column_value => true
  tracking_column => "time"
  tracking_column_type => "timestamp"
  record_last_run => true
  last_run_metadata_path => "/mysql-connector-java/jdbc-position.txt"
  clean_run => false
  type => "mysql"
  }
}
filter {
    json {
        source => "message"
        remove_field => ["message"]
    }
}
output{
       if [type] == "mysql" {
   elasticsearch {
        hosts => ["192.168.130.134:9200"]
        index => "synces2"
    }
}

}

 

systemctl restart logstash