Logstash创建索引，从mysql自动同步到Elasticearch

1.介绍

Logstash是ES下的一款开源软件，它能够同时 从多个来源采集数据、转换数据，然后将数据发送到Eleasticsearch中创建索引。

2.下载和安装

• 下载Logstash版本和Elasticsearch版本必须一致
• logstash-input-jdbc 是ruby开发的，先下载ruby并安装
• 安装完成查看是否安装成功 ruby -v
• 
• Logstash5.x以上版本本身自带有logstash-input-jdbc，6.x版本本身不带logstash-input-jdbc插件，需要手动安装
  

3.创建模板文件

3.1 数据库设计

3.2 book.json

D:\ES\logstash-6.2.1\config下的book.json

{
   "mappings" : {
      "doc" : {
         "properties" : {
            "name" : {
               "type" : "text",
			    "analyzer" : "ik_max_word",
               "search_analyzer" : "ik_smart",
            },
            "price" : {
               "type" : "keyword"
            },
            "createtime" : {
               "format" : ""yyyy-MM-dd HH:mm:ss",
               "type" : "date"
            },
            "desc" : {
               "analyzer" : "ik_max_word",
               "search_analyzer" : "ik_smart",
               "type" : "text"
            }
         }
      }
   },
   "template" : "book"
}

3.3 mysql.conf

D:\ES\logstash-6.2.1\config下的mysql.conf

input {
  stdin {
  }
  jdbc {
  jdbc_connection_string => "jdbc:mysql://localhost:3306/qf?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC"
  # the user we wish to excute our statement as
  jdbc_user => "root"
  jdbc_password => "123456"
  # the path to our downloaded jdbc driver
  #jdbc_driver_library => "D:/repository/mysql/mysql-connector-java/5.1.40/mysql-connector-java-5.1.40.jar"
   jdbc_driver_library => "D:/maven/repository/mysql/mysql-connector-java/5.1.44/mysql-connector-java-5.1.44.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 => "/conf/course.sql"
  statement => "select * from book where createtime > date_add(:sql_last_value,INTERVAL 8 HOUR)"
  #定时配置
  schedule => "* * * * *"
  #是否转为小写
  lowercase_column_names => false
  record_last_run => false
  last_run_metadata_path => "D:/ES/logstash-6.2.1/config/logstash_metadata"
  }
}


output {
  elasticsearch {
  #ES的ip地址和端口
  hosts => "localhost:9200"
  #hosts => ["localhost:9200","localhost:9202","localhost:9203"]
  #ES索引库名称
  index => "wang-003"
  document_id => "%{id}"
  document_type => "doc"
  template =>"D:/ES/logstash-6.2.1/config/book.json"
  template_name =>"book"
  template_overwrite =>"true"
  }
  stdout {
 #日志输出
  codec => json_lines
  }
}

说明：

1、ES采用UTC时区问题

ES采用UTC 时区，比北京时间早8小时，所以ES读取数据时让最后更新时间加8小时

where createtime > date_add(:sql_last_value,INTERVAL 8 HOUR)
2、logstash每个执行完成会在D:/Es/logstash-6.2.1/config/logstash_metadata记录执行时间下次以此时间为基准进行增量同步数据到索引库。

4.测试

启动logstash.bat：

.\logstash.bat -f ..\config\mysql.conf

启动后会自动创建索引库和映射，并且同步数据库createtime时间大于现在时间的记录到ES