关于logstash从mysql同步到mysql报错

Question

老师您好，我想用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]"]
}
}

Answer 1

提问者 zuo2020 2020-01-03 11:58:55

如果把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]"]

    }

}

Answer 2

提问者 zuo2020 2020-01-03 11:49:14

经过测试，同时使用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

    }

}

Answer 3

rockybean 2020-01-02 21:51:00

你引用的 jar path 为什么会不一样？

• 提问者 zuo2020 #1

  因为报错是NoClassDefFoundError，网上说错误是因为在运行时类加载器在classpath下找不到需要加载的类，开始我使用的是同一个jar，以为是冲突了，所以换成了不同的jar，所以看到配置文件里面是两个jar，后来我又换成了8.03的mysql包，直接连不上数据库了= =