要点二：安装Logstash 连接mysql与elasticsearch
下载
1. 下载logstash地址（不建议下载低版本，对jdk1.7支持不够好）本次下载为最新版
https://www.elastic.co/cn/downloads/logstash
解压到/usr/local/src目录下
下载mysql-connector-java-5.1.46:
https://cdn.mysql.com//Downloads/Connector-J/mysql-connector-java-5.1.46.tar.gz
将下载好的mysql-connector-java-5.1.46解压到logstash安装根目录下/usr/local/src/logstash-5.1.11/
安装logstash
Elasticsearch-jdbc工具包（废弃）,虽然是官方推荐的，但是已经几年不更新了。所以选择安装logstash-input-jdbc，由于，logstash-input-jdbc是基于 logstash 的，而logstash又是基于 ruby的，所以，要依次安装，命令如下：
安装Jruby:
JRuby是一个纯Java实现的Ruby解释器。通过JRuby，你可以在JVM上直接运行Ruby程序，调用Java的类库。很多Java编写的Ruby IDE都是使用JRuby来解释语法的。
JRuby，JVM下的一个开源Ruby解释器，能够在Java里面使用Ruby类库。就像标准的Ruby解释器一样，除开使用Ruby调用本地方法（C代码）或者Java类库以外，Ruby代码都能够在JRuby里面正确执行。
下载地址：http://jruby.org/download
[root@localhost src]#wget https://repo1.maven.org/maven2/org/jruby/jruby-dist/9.1.16.0/jruby-dist-9.1.16.0-bin.tar.gz[root@localhost src]# tar -xvf jruby-dist-9.1.16.0-bin.tar.gz[root@localhost src]# mkdir -p /usr/local/src/java[root@localhost src]# mv jruby-9.1.16.0/usr/local/src/java/jruby-9.1.16.0
设置环境变量：
vi /etc/profile加入
#!/bin/bash
export JRUBY_HOME=/usr/local/src/java/jruby-9.1.16.0
exportPATH=$JRUBY_HOME/bin:$PATH
应用并生效
source /etc/profile
source /etc/bashrc
查看版本
jruby -v输出类似如下信息，即安装成功
jruby 9.1.16.0 (2.3.3) 2018-02-21 8f3f95a Java HotSpot(TM) 64-Bit ServerVM 25.171-b11 on 1.8.0_171-b11 +jit [linux-x86_64]
部署logstash
添加jdbc配置文件：
创建conf目录：mkdir conf
创建配置文件(使用时注意要把中文注释全部删除)：vi jdbc-es.conf
input {
stdin {
}
jdbc {
type => "course" #数据库表名
jdbc_connection_string => "jdbc:mysql://127.0.0.1:3306/数据库名?characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false"
jdbc_user => "root"
jdbc_password => "123456"
record_last_run => "true"
use_column_value => "true"
tracking_column => "id"
last_run_metadata_path =>"/usr/local/src/logstash-5.1.1/bin/logstash_course/course_info"
clean_run => "false"
jdbc_driver_library =>"/usr/local/src/logstash-5.1.1/mysql-connector-java-5.1.46/mysql-connector-java-5.1.46.jar"
jdbc_driver_class =>"com.mysql.jdbc.Driver"
jdbc_paging_enabled => "true"
jdbc_page_size => "500"
statement => "select * from coursewhere id > :sql_last_value"
schedule => "* * * * *"
}
jdbc {
type => "category"
jdbc_connection_string => "jdbc:mysql://127.0.0.1:3306/xueyin?characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false"
jdbc_user => "root"
jdbc_password => "123456"
record_last_run => "true"
use_column_value => "true"
tracking_column => "id"
last_run_metadata_path =>"/usr/local/src/logstash-5.1.1/bin/logstash_category/category_info"
clean_run => "false"
jdbc_driver_library =>"/usr/local/src/logstash-5.1.1/mysql-connector-java-5.1.46/mysql-connector-java-5.1.46.jar"
jdbc_driver_class =>"com.mysql.jdbc.Driver"
jdbc_paging_enabled => "true"
jdbc_page_size => "500"
statement => "select * from categorywhere id > :sql_last_value"
schedule => "* * * * *"
}
}
filter {
mutate {
convert => [ "publish_time","string" ]
}
date {
timezone => "Europe/Berlin"
match => ["publish_time" ,"ISO8601", "yyyy-MM-dd HH:mm:ss"]
}
#date {
# match => [ "publish_time","yyyy-MM-dd HH:mm:ss,SSS" ]
# remove_field => ["publish_time" ]
# }
json {
source => "message"
remove_field => ["message"]
}
}
output {
if[type]=="course" {
elasticsearch {
hosts => "127.0.0.1:9200" # 导出到的elasticsearch配置信息
index => "course"
template_overwrite => true # 导出到的elasticsearch配置信息
template =>"/usr/local/src/logstash-5.1.1/template/logstash.json"
}
}
if[type]=="category" {
elasticsearch {
hosts => "127.0.0.1:9200"
index => "category"
template_overwrite => true # 导出到的elasticsearch配置信息
template => "/usr/local/src/logstash-5.1.1/template/logstash.json"
}
}
}
添加ik配置文件
创建conf目录：mkdir template
创建配置文件(使用时注意要把中文注释全部删除)：vi logstash.json
{
"template" : "*",
"version" : 50001,
"settings" : {
"index.refresh_interval" :"5s",
"index": {
"analysis": {
"analyzer": {
"by_smart": {
"type":"custom",
"tokenizer":"ik_smart",
"filter":["by_tfr","by_sfr"],
"char_filter":["by_cfr"]
},
"by_max_word": {
"type":"custom",
"tokenizer":"ik_max_word",
"filter":["by_tfr","by_sfr"],
"char_filter":["by_cfr"]
}
},
"filter": {
"by_tfr": {
"type":"stop",
"stopwords": [""]
},
"by_sfr": {
"type":"synonym",
"synonyms_path":"analysis/synonyms.txt"
}
},
"char_filter": {
"by_cfr": {
"type":"mapping",
"mappings": ["|=> |"]
}
}
}
}
},
"mappings" : {
"_default_" : {
"_all" : {
"enabled" : true,
"norms" : false
},
"dynamic_templates" : [
{
"message_field" :{
"path_match": "message",
"match_mapping_type" : "string",
"mapping" : {
"type" :"text",
"norms" :false
}}
},
{
"string_fields" : {
"match" :"*",
"match_mapping_type" : "string",
"mapping" : {
"type" :"text",
"norms" :false,
"analyzer" : "by_max_word",
"fields": {
"keyword" : {
"type" : "keyword"
}
}
}
}
}
],
"properties" : {
"@timestamp" : {
"type" :"date",
"include_in_all": false
},
"@version" : {
"type" :"keyword",
"include_in_all": false
}
}
}
}
}
启动logstash
首先启动elasticsearch
在elasticsearch根目录下的bin目录下执行：
sh elasticsearch -d 或 ./elasticsearch
启动logstash:
在logstash-5.1.1目录下的conf目录下执行：
../bin/logstash -f jdbc-es.sh
注意：在使用JDBC操作mysql时遇到下面的异常信息：
The last packet sent successfully to the server was 0 millisecondsago. The driver has not received any packets from the server.
at com.tomymap.galaxy.virgo.util.DbService.getConnection(DbService.java:66)
atcom.tomymap.galaxy.virgo.util.DbService.getConnection(DbService.java:46)
at com.tomymap.galaxy.virgo.dao.PNNDao.getConnection(PNNDao.java:51)
at com.tomymap.galaxy.virgo.dao.DaoBase.executeUpdate(DaoBase.java:69)
at com.tomymap.galaxy.virgo.dao.PNNDao.updatePNNRelation(PNNDao.java:161)
atcom.tomymap.galaxy.virgo.pnn.PyramidNeuralNetwork.buildPNNRelations(PyramidNeuralNetwork.java:400)
解决方法：
（1）使用JDBC URL中使用autoReconnect属性，url添加
1. &autoReconnect=true&failOverReadOnly=false
jdbc_connection_string =>"jdbc:mysql://127.0.0.1:3306/xueyin?characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false"
（2） 修改MySQL的参数 vi. /etc/my.cnf 添加
1. wait_timeout=31536000
2. interactive_timeout=31536000
（3）重启mysql
1. service mysql restart
163
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
