关于logstash从mysql同步到mysql报错

来源：12-10 output插件简介

zuo2020

2020-01-02

老师您好，我想用logstash做数据割接，从mysql迁移数据处理数据到另外一个mysql，
使用使用过程中报错：
[2020-01-02T20:47:09,791][ERROR][org.logstash.Logstash ] java.lang.NoClassDefFoundError: Could not initialize class com.mysql.jdbc.PreparedStatement
我用http输入json的方式测试了jdbc-out-put插件，数据可以正常入库
单独测试了input-jdbc可以从数据库中读取数据
请问两个一起使用怎么就报错了呢？
我的config文件：
input {
jdbc {
jdbc_connection_string => "jdbc:mysql://192.168.14.197:3306/logstash197"
jdbc_user => "root"
jdbc_password => "root"
jdbc_driver_library => "E:\maven_jars\mysql\mysql-connector-java\5.1.16\mysql-connector-java-5.1.16.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
statement => “select user_id,user_name,age,money,DATE_FORMAT(create_time, ‘%Y-%m-%d %H:%i:%s’) as create_time from user”
}
}

filter {

  mutate {
  add_field => {"temp_ts" => "%{create_time}"}
  remove_field => ["@timestamp"]
    remove_field => ["@version","message","host","path","@timestamp"]
}
date {
  match => ["temp_ts", "yyyy-MM-dd HH:mm:ss"]
  target => ["temp_ts"]
  remove_field => ["create_time"]
}

}

output {
stdout {
codec => rubydebug
}
jdbc{
driver_jar_path => “C:\Users\Administrator.m2\repository\mysql\mysql-connector-java\5.1.27\mysql-connector-java-5.1.27.jar"
driver_class => “com.mysql.jdbc.Driver"
connection_string => “jdbc:mysql://192.168.14.197:3306/logstash197?user=root&password=root"
statement => [“replace into user_copy(user_id, user_name, age, money,create_time) values(?,?,?,?,?)”,”[user_id]”, “[user_name]”, “[age]”, “[money]”,”[temp_ts]"]
}
}

写回答

3回答

zuo2020

提问者

2020-01-03

如果把input插件使用的mysql包也换成mysql-connector-java-8.0.16.jar，则会报错：

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.

[2020-01-03T11:51:38,853][INFO ][logstash.inputs.jdbc ] (0.019774s) select user_id,user_name,age,money,DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%s') as create_time from user

[2020-01-03T11:51:39,107][INFO ][com.zaxxer.hikari.HikariDataSource] HikariPool-1 - Shutdown initiated...

[2020-01-03T11:51:39,116][INFO ][com.zaxxer.hikari.HikariDataSource] HikariPool-1 - Shutdown completed.

[2020-01-03T11:51:39,119][INFO ][logstash.pipeline ] Pipeline has terminated {:pipeline_id=>"main", :thread=>"#<Thread:0x91cb2 run>"}

[2020-01-03T11:51:39,283][ERROR][org.logstash.Logstash ]java.lang.IllegalAccessError: tried to access class com.mysql.cj.jdbc.EscapeProcessor from class com.mysql.cj.jdbc.ConnectionImpl

这里已经把jdbc_driver_class => "com.mysql.cj.jdbc.Driver"修改，还是不可用，

所以测试成功的配置是 input插件是用5.1*版本的包，output使用8.0.16的包，参数需要加上时区

如果单独使用jdbc-output插件，使用5.1.*版本的包可以正常使用，老师如果有空，请解答下原因，多谢

单独使用的配置：

input {

http{

port => 7474

}

filter {

json {

source => "message"

}

date {

match => ["logdate", "yyyy-MM-dd HH:mm:ss"]

target => "create_time"

}

mutate{

#删除无效的字段

remove_field => ["@version","message","host","path","@timestamp"]

remove_field => "headers"

}

output {

stdout {

codec => rubydebug

}

jdbc{

driver_jar_path => "C:\Users\Administrator\.m2\repository\mysql\mysql-connector-java\5.1.27\mysql-connector-java-5.1.27.jar"

driver_class => "com.mysql.jdbc.Driver"

connection_string => "jdbc:mysql://192.168.14.197:3306/logstash197?user=root&password=root"

statement => ["replace into user(user_id, user_name, age, money,create_time) values(?,?,?,?,?)","[user_id]", "[user_name]", "[age]", "[money]","[create_time]"]

}

zuo2020

提问者

2020-01-03

经过测试，同时使用jdbc的input和output插件，

outbut插件需要mysql-connector-java-8.0.16.jar这个，input使用5.1.*不受影响，并且在connection_string配置中必须加上时区配置，如:serverTimezone=Asia/Shanghai,driver_class需要把“com.mysql.cj.jdbc.Driver”加上cj，=>"com.mysql.cj.jdbc.Driver"

配置文件如下，测试可用

input {

jdbc {

jdbc_connection_string => "jdbc:mysql://192.168.14.197:3306/logstash197?serverTimezone=Asia/Shanghai&characterEncoding=utf8&userSSL=false&allowMultiQueries=true"

jdbc_user => "root"

jdbc_password => "root"

jdbc_driver_library => "E:\maven_jars\mysql\mysql-connector-java\5.1.16\mysql-connector-java-5.1.16.jar"

jdbc_driver_class => "com.mysql.jdbc.Driver"

statement => "select user_id,user_name,age,money,DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%s') as create_time from user"

}

filter {

mutate {

add_field => {"temp_ts" => "%{create_time}"}

remove_field => ["@version","message","host","path","@timestamp"]

}

date {

match => ["temp_ts", "yyyy-MM-dd HH:mm:ss"]

target => ["temp_ts"]

remove_field => ["create_time"]

}

output {

jdbc{

connection_string => "jdbc:mysql://192.168.14.197:3306/logstash197?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&userSSL=false&allowMultiQueries=true"

username => "root"

password => "root"

driver_jar_path => "D:\logstash-6.5.0\logstash-6.5.0\vendor\jar\jdbc\mysql-connector-java-8.0.16.jar"

driver_class => "com.mysql.cj.jdbc.Driver"

statement => ["replace into user_copy(user_id, user_name, age, money,create_time) values(?,?,?,?,?)","[user_id]", "[user_name]", "[age]", "[money]","[temp_ts]"]

}

stdout {

codec => json_lines

}