使用软件版本
es:7.0.1
logstash:7.0.1
mysql5.7.1
数据库插件:mysql-connector-java-5.1.18.jar
同步方式按照数据库的时间字段来进行同步
遇到了太多的坑,这里记录一下配置文件
input {
jdbc {
jdbc_driver_library => "/root/logstash7.0.1/mysql-connector-java-5.1.18.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_connection_string => "jdbc:mysql://域名或ip:端口号/数据库"
jdbc_user => "username"
jdbc_password => "password"
tracking_column => "publish_time"
tracking_column_type => "timestamp"
use_column_value => true
schedule => "*/5 * * * * *"
jdbc_paging_enabled => "true"
jdbc_page_size => "500"
statement => "SELECT *, UNIX_TIMESTAMP(publish_time) AS unix_ts_in_secs FROM article_lib WHERE (UNIX_TIMESTAMP(publish_time) > :sql_last_value AND publish_time < NOW()) ORDER BY publish_time ASC"
}
}
output {
# stdout { codec => "rubydebug"}
elasticsearch {
hosts => "127.0.0.1:9201"
index => "idx_article"
document_id => "%{article_id}"
}
}
启动logstash的时候遇到以下问题:说什么int类型不能转换为string类型
[ERROR][logstash.javapipeline ] Pipeline aborted due to error {:pipeline_id=>"main", :exception=>#<TypeError: no implicit conversion of Integer into String>, :backtrace=>["uri:classloader:/META-INF/jruby.home/lib/ruby/stdlib/date/format.rb:335:in `_parse'", "uri:classloader:/META-INF/jruby.home/lib/ruby/stdlib/date.rb:734:in `parse'", "/nas/logstash-7.3.0/vendor/bundle/jruby/2.5.0/gems/logstash-input-jdbc-4.3.13/lib/logstash/plugin_mixins/jdbc/value_tracking.rb:87:in `set_value'", "/nas/logstash-7.3.0/vendor/bundle/jruby/2.5.0/gems/logstash-input-jdbc-4.3.13/lib/logstash/plugin_mixins/jdbc/value_tracking.rb:36:in `initialize'", "/nas/logstash-7.3.0/vendor/bundle/jruby/2.5.0/gems/logstash-input-jdbc-4.3.13/lib/logstash/plugin_mixins/jdbc/value_tracking.rb:29:in `build_last_value_tracker'", "/nas/logstash-7.3.0/vendor/bundle/jruby/2.5.0/gems/logstash-input-jdbc-4.3.13/lib/logstash/inputs/jdbc.rb:216:in `register'", "/nas/logstash-7.3.0/logstash-core/lib/logstash/java_pipeline.rb:192:in `block in register_plugins'", "org/jruby/RubyArray.java:1792:in `each'", "/nas/logstash-7.3.0/logstash-core/lib/logstash/java_pipeline.rb:191:in `register_plugins'", "/nas/logstash-7.3.0/logstash-core/lib/logstash/java_pipeline.rb:292:in `start_inputs'", "/nas/logstash-7.3.0/logstash-core/lib/logstash/java_pipeline.rb:248:in `start_workers'", "/nas/logstash-7.3.0/logstash-core/lib/logstash/java_pipeline.rb:146:in `run'", "/nas/logstash-7.3.0/logstash-core/lib/logstash/java_pipeline.rb:105:in `block in start'"], :thread=>"#<Thread:0x673f5fa3 run>"}
[2020-09-10T23:48:51,350][ERROR][logstash.agent ] Failed to execute action {:id=>:main, :action_type=>LogStash::ConvergeResult::FailedAction, :message=>"Could not execute action: PipelineAction::Create<main>, action_result: false", :backtrace=>nil}
找了一整天发现原因
1、这有可能是因为 tracking_column 之前按照 numeric 类型运行,后来改成 timestamp ,检查last_run_metadata_path,默认路径 $HOME/.logstash_jdbc_last_run,如果是因为以上原因,删除重新创建即可。或者手动更改配置文件内容。
进入logstash的bin文件夹输入以下命令进行修改。
vi $HOME/.logstash_jdbc_last_run
tracking_column 之前按照 numeric 类型运行
--- 0
修改为下面这种方式,时间可自定义
--- 2010-08-17 06:34:00.784150000 Z
如果嫌修改太麻烦,也可直接将改文件删除然后再次启动logstash,它会自动创建
2、配置文件的参数或者格式有问题
3、缺少关键配置字段
还有其它要注意的地方,用户名密码最好都要加上双引号,如果用户名和密码种有@符号,这时候如果不用双引号将他们引起来,logstash读的时候会把@当成连接符,就会出现问题。
还有另一个问题,有的时候在下方日志的上一行会有其他的具体错误输出,比如tracking_column未设置啥的
但是我的就比较独特了,啥都没有就来个这,从深夜至凌晨,发现是mysql连接的驱动版本不对,我之前使用的是mysql-connector-java-5.1.18.jar。改成mysql-connector-java-8.0.16.jar就没问题了,但是之前用的就是5.1.8为啥可以呢,百思不得其姐…
2012 rufus-scheduler intercepted an error:
2012 job:
2012 Rufus::Scheduler::CronJob "*/5 * * * * *" {}
最终版配置文件
input {
jdbc {
jdbc_driver_library => "/root/logstash-7.0.1/config/mysql-connector-java-8.0.16.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_connection_string => "jdbc:mysql://192.168.120.11:53306/cmsdb?useUnicode=true&characterEncoding=utf-8&useSSL=false"
jdbc_user => "root"
jdbc_password => "Root@20201028"
jdbc_default_timezone =>"Asia/Shanghai"
tracking_column => "publish_time"
tracking_column_type => "timestamp"
use_column_value => true
schedule => "*/5 * * * * *"
jdbc_paging_enabled => "true"
jdbc_page_size => "500"
statement => "SELECT *, UNIX_TIMESTAMP(publish_time) AS unix_ts_in_secs FROM article_lib WHERE (UNIX_TIMESTAMP(publish_time) > :sql_last_value AND publish_time < NOW()) ORDER BY publish_time ASC"
}
}
output {
#stdout { codec => "rubydebug"}
elasticsearch {
hosts => "192.168.120.11:9201"
index => "idx_article"
document_id => "%{article_id}"
}
}
$HOME/.logstash_jdbc_last_run文件中的数据为
vi $HOME/.logstash_jdbc_last_run
--- 2010-08-17 06:34:00.784150000 Z
最终版mysql.conf,测试环境没问题,但是客户的环境一直出现各种各样的问题
input {
jdbc {
jdbc_driver_library => "/root/logstash-7.0.1/config/mysql-connector-java-8.0.16.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_connection_string => "jdbc:mysql://111.168.120.11:53306/cmsdb"
jdbc_user => "username"
jdbc_password => "password"
tracking_column => "publish_time"
tracking_column_type => "timestamp"
use_column_value => true
#开启增量同步,必须配置下面这个变量
clean_run => false
jdbc_paging_enabled => "true"
jdbc_page_size => "2500"
#有的服务器能够读取到这个文件,有的只能读取默认的配置文件vi $HOME/.logstash_jdbc_last_run
last_run_metadata_path => "/root/logstash7.0.1/config/last_time.txt"
schedule => "*/5 * * * * *"
statement => "SELECT *, UNIX_TIMESTAMP(publish_time) AS unix_ts_in_secs FROM article_lib WHERE (UNIX_TIMESTAMP(publish_time) > :sql_last_value AND publish_time < NOW()) ORDER BY publish_time ASC"
}
}
output {
# stdout { codec => "rubydebug"}
elasticsearch {
hosts => "111.168.120.11:9201"
index => "idx_article"
document_id => "%{article_id}"
}
}
启动logstash的时候还遇到了一个找不到驱动的问题
一般有两种情况:
1、mysql的驱动jar包版本不对应
2、配置文件格式不正确
我就比较幸运了,跑着跑着发现我的驱动jar包还在,但是大小变成了0字节,,,呵呵,上天待我果然不薄。重新上传一个jar就可以了
2016 Rufus::Scheduler::CronJob "* * * * *" {}
2016 error:
2016 2016
2016 LogStash::ConfigurationError
2016 Java::oracle.jdbc.driver.OracleDriver not loaded. Are you sure you've included the correct jdbc driver in :jdbc_driver_library?
以上配置完毕后,logstash运行时,有时会按时间戳来进行查询,有些时候,打印的sql语句明明是按时间来过滤的,但是还是从数据库的第一条数据进行过滤。方法修改时间戳,并添加时间记录文件。这样就可以按时间来进行增量查询并入库到es了。
时间戳配置文件
--- 2010-08-17 06:34:00.784150000 Z
所需软件下载
logstash:7.0.1
链接:https://pan.baidu.com/s/1vmzLx0nsLiNlGcDeXpeaqg
提取码:uhu7
mysql:5.7.1
elasticsearch:7.0.1
链接:https://pan.baidu.com/s/11n90KE724xzSDLiZi7EaEg
提取码:364y
7.0.0ik分词器
链接:https://pan.baidu.com/s/1nWycNaEni9GcxhHDghg-lw
提取码:pc2p
mysql插件:mysql-connector-java-5.1.18.jar
链接:https://pan.baidu.com/s/1N0Eo0cgNvrxWcYDgPfvh0w
提取码:sy6d
mysql插件:mysql-connector-java-8.0.16
链接:https://pan.baidu.com/s/1WO6HZbNwNxCyMdpQ-RrP1Q
提取码:7zed
复制这段内容后打开百度网盘手机App,操作更方便哦