Logstash input jdbc error

다음은 Mysql 7.0 연결을 위한 로그스태시 파이프라인.

input {
 jdbc {
  jdbc_driver_library => "D:/mysql-connector-java-5.1.42/mysql-connector-java-5.1.42-bin.jar"
  jdbc_driver_class => "com.mysql.jdbc.Driver"
  jdbc_connection_string => "jdbc:mysql://localhost:3306/snort"
  jdbc_user => "user"
  jdbc_password => "password"
  schedule => "* * * * *"
  statement => "
   SELECT a.timestamp, b.sig_name
   from event a, signature b
   where a.signature = b.sig_id
  "
 }
}

output {
 stdout {}
}

로그스태시 7.13 버전까지는 다음 에러가 종종 발생했다.

[2021-12-26T22:01:04,043][INFO ][logstash.agent           ] Pipelines running {:count=>1, :running_pipelines=>[:main], :non_running_pipelines=>[]}
C:/ELK/logstash-7.16.2/vendor/bundle/jruby/2.5.0/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/cronline.rb:77: warning: constant ::Fixnum is deprecated
Sun Dec 26 22:02:00 KST 2021 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
[2021-12-26T22:02:00,905][ERROR][logstash.inputs.jdbc     ][main][2b0917f0c27ad6a3e45d66144fbaf2caa18cd6b0078964bf6c881a78326c55de]
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

[2021-12-26T22:02:00,964][ERROR][logstash.inputs.jdbc     ][main][2b0917f0c27ad6a3e45d66144fbaf2caa18cd6b0078964bf6c881a78326c55de] Unable to connect to database. Tried 1 times {:message=>"Java::ComMysqlJdbcExceptionsJdbc4::CommunicationsException: Communications link failure\n\nThe last packet successfully received from the server was 184 milliseconds ago.  The last packet sent successfully to the server was 161 milliseconds ago.", :exception=>Sequel::DatabaseConnectionError, :cause=>com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

물론 그럼에도 데이터 연동은 됐음. 그런데 7.14 버전부터는 아예 DB 연결이 안 된다. SSL 통신이 기본이 된 모양. 다행히 에러 메시지에 답이 다 나와 있다. 다음은 useSSL 파라미터를 추가한 로그스태시 파이프라인. (jdbc_connection_string 옵션)

input {
 jdbc {
  jdbc_driver_library => "D:/mysql-connector-java-5.1.42/mysql-connector-java-5.1.42-bin.jar"
  jdbc_driver_class => "com.mysql.jdbc.Driver"
  jdbc_connection_string => "jdbc:mysql://localhost:3306/snort?useSSL=false"
  jdbc_user => "user"
  jdbc_password => "password"
  schedule => "* * * * *"
  statement => "
   SELECT a.timestamp, b.sig_name
   from event a, signature b
   where a.signature = b.sig_id
  "
 }
}

output {
 stdout {}
}

참고로 Mysql 버전을 8.0으로 올리면 또 연결이 안 되는데, 이때는 mysql-connector 버전을 최신으로 올리면 된다.

[2021-12-26T23:10:00,032][ERROR][logstash.inputs.jdbc     ][main][2f29d70e4767233744c98fa9d6f1f67e66c51c16b9283cb8e3425e6622d037c3] Unable to connect to database. Tried 1 times {:message=>"Java::JavaSql::SQLException: Unknown system variable 'query_cache_size'"

관련 글

• mysql-connector-java 8 버그
• Logstash input 플러그인 jdbc 버그?
• Elasticsearch 활용(Snort 데이터베이스 연동)