安装和使用 elasticSearch、LogStash、IK（三），

和通数据库htsjk.Com2019-06-09 02:09 来源:未知阅读:19663 评论 344 热度5

标签：

安装和使用 elasticSearch、LogStash、IK（三），

- 通过Logstash由MySQL向Elasticsearch导入数据
  - -安装 logstash-input-jdbc 工具包
    - 安装
  - 安装 jdbc
    - 在logstash 执行目录下创建目录和两个conf 文件
  - 执行mysql 导入 jdbc
- 测试导入结果

原文链接：https://www.leon0204.com/article/94.html

通过Logstash由MySQL向Elasticsearch导入数据

-安装 logstash-input-jdbc 工具包

安装

Elasticsearch-jdbc工具包（废弃）,虽然是官方推荐的，但是已经几年不更新了。所以选择安装logstash-input-jdbc，由于，logstash-input-jdbc是基于 logstash 的，而logstash又是基于 ruby的，所以，要依次安装，命令如下：

# 安装 ruby 1.9
$ apt-get install ruby 

# 添加 apt-key
wget -qO - https://artifacts.elastic.co/GPG-KEY-elasticsearch | sudo apt-key add - D88E42B4

sudo apt-get install apt-transport-https

#不要自作聪明改掉x 

echo "deb https://artifacts.elastic.co/packages/5.x/apt stable main" | sudo tee -a /etc/apt/sources.list.d/elastic-5.x.list

#如果错误执行之后，报重复或者http 404 等错误，可以  rm -rf /etc/apt/sources.list.d/elastic-5.x.list ，然后重新echo 

#更新apt
apt-get update 

#安装 logstash
apt-get install logstash

#安装 logstash-input-jdbc
$ /usr/share/logstash# bin/logstash-plugin install logstash-input-jdbc
Validating logstash-input-jdbc
Installing logstash-input-jdbc



Installation successful

# 验证
root@b0c170e13e44:/usr/share/logstash/vendor/bundle/jruby/1.9/gems# ls |grep jdbc
logstash-input-jdbc-4.3.1


#logstash 执行文件
/usr/share/logstash/bin/logstash

安装 jdbc

#进入es 安装跟目录 ， 安装插件
./bin/elasticsearch-plugin install http://xbib.org/repository/org/xbib/elasticsearch/plugin/elasticsearch-river-jdbc/1.5.0.5/elasticsearch-river-jdbc-1.5.0.5-plugin.zip

在logstash 执行目录下创建目录，和两个conf 文件

# 1 .jdbc.sql   路径 为：/usr/share/logstash/bin/logstash_jdbc_test
#内容是：
select * from  info where info.ID > 3035

# 2 .jdbc.conf   路径为： /usr/share/logstash/bin/logstash_jdbc_test
#内容是：
input {
  stdin {
  }
  jdbc {
  # mysql jdbc connection string to our backup databse  后面的test对应mysql中的test数据库
  jdbc_connection_string => "jdbc:mysql://你的mysql的host地址:3306/infodb"
  # the user we wish to excute our statement as
  jdbc_user => "username"
  jdbc_password => "passwd"
  # the path to our downloaded jdbc driver
  jdbc_driver_library => "/usr/local/src/elasticsearch-jdbc-2.3.4.0/lib/mysql-connector-java-5.1.38.jar"
  # the name of the driver class for mysql
  jdbc_driver_class => "com.mysql.jdbc.Driver"
  jdbc_paging_enabled => "true"
  jdbc_page_size => "50000"
#以下对应着要执行的sql的绝对路径。
  statement_filepath => "/usr/share/logstash/bin/logstash_jdbc_test/jdbc.sql"
#定时字段 各字段含义（由左至右）分、时、天、月、年，全部为*默认含义为每分钟都更新（测试结果，不同的话请留言指出）
  schedule => "* * * * *"
#设定ES索引类型
  type => "person"
  }
}

filter {
  json {
  source => "message"
  remove_field => ["message"]
  }
}

output {
  elasticsearch {
#ESIP地址与端口
  hosts => "127.0.0.1:9200"
#ES索引名称（自己定义的）
  index => "accounts"
#自增ID编号
  document_id => "%{id}"
  }
  stdout {
#以JSON格式输出
  codec => json_lines
  }
}
######################

执行mysql 导入 jdbc

./logstash -f ./logstash_jdbc_test/jdbc.conf

测试导入结果

curl 'localhost:9200/accounts/person/_search’
# 查询 所有的 数据 

hit 

{
    "total": 5,
    "max_score": 1,
    "hits": [
        {
            "_index": "accounts",
            "_type": "person",
            "_id": "3038",
            "_score": 1,
            "_source": {
                "indextype": 1,
                "infotype": 1,
                "createtime": "2018-01-09T14:07:59.000Z",
                "iskol": false,
                "issend": true,
                "creatorid": 370,
                "description": "腐界鼻祖爆陈学冬此次恋情为洗直，娱乐圈还有更多你意想不到的夫夫组合…",
                "title": "这是一片测试文章",
                "type": "person",
                "isdeleted": 1,
                "ispushed": 1,
                "pushtime": null,
                "pushcontent": "",
                "keyno": "Info000003032",
                "@timestamp": "2018-01-08T06:48:00.217Z",
                "ispublish": false,
                "publishtime": "2027-09-28T12:00:00.000Z",
                "@version": "1",
                "id": 3038,
                "isquality": false,
                "staffid": null
            }
        },
        {
            "_index": "accounts",
            "_type": "person",
            "_id": "3036",
            "_score": 1,
            "_source": {
                "indextype": 1,
                "infotype": 1,
                "createtime": "2018-01-08T14:07:50.000Z",
                "iskol": false,
                "issend": true,
                "creatorid": 370,
                "description": "他曾是刘德华的上司，央视春晚缺他不可，却让吴君如恨得牙痒痒！",
                "title": "他曾是刘德华的上司，央视春晚缺他不可，却让吴君如恨得牙痒痒！",
                "type": "person",
                "isdeleted": 0,
                "ispushed": 1,
                "pushtime": null,
                "pushcontent": "",
                "keyno": "Info000003031",
                "@timestamp": "2018-01-08T06:48:00.213Z",
                "ispublish": false,
                "publishtime": "2027-09-28T12:00:00.000Z",
                "@version": "1",
                "id": 3036,
                "isquality": false,
                "staffid": null
            }
        },
        {
            "_index": "accounts",
            "_type": "person",
            "_id": "3037",
            "_score": 1,
            "_source": {
                "indextype": 1,
                "infotype": 1,
                "createtime": "2018-01-08T14:07:59.000Z",
                "iskol": false,
                "issend": true,
                "creatorid": 370,
                "description": "腐界鼻祖爆陈学冬此次恋情为洗直，娱乐圈还有更多你意想不到的夫夫组合…",
                "title": "腐界鼻祖爆陈学冬此次恋情为洗直，娱乐圈还有更多你意想不到的夫夫组合…",
                "type": "person",
                "isdeleted": 0,
                "ispushed": 1,
                "pushtime": null,
                "pushcontent": "",
                "keyno": "Info000003032",
                "@timestamp": "2018-01-08T06:48:00.215Z",
                "ispublish": false,
                "publishtime": "2027-09-28T12:00:00.000Z",
                "@version": "1",
                "id": 3037,
                "isquality": false,
                "staffid": null
            }
        },
        {
            "_index": "accounts",
            "_type": "person",
            "_id": "AWDFSjYm4DfKqktV2Xfm",
            "_score": 1,
            "_source": {
                "user": "李四",
                "title": "工程师",
                "desc": "系统管理"
            }
        },
        {
            "_index": "accounts",
            "_type": "person",
            "_id": "1",
            "_score": 1,
            "_source": {
                "user": "leon0204",
                "title": "工程师",
                "desc": "数据库管理，软件开发"
            }
        }
    ]
}

每个document 包含了 5 个部分索引名称，索引type，文档id ，score ，原数数据