安装Elasticsearch5.5和logstash实现增量全量导入

linux除了ruby环境的安装有所差别,其余一致

需要软件包:

安装elasticsearch-head插件(需要node 8.0以上环境)

1. npm install -g grunt-cli 安装grunt
2. 修改elasticsearch.yml文件
   
   1. http.cors.enabled: true
   2. http.cors.allow-origin: "*"
   3. network.host: 0.0.0.0
3. 修改elasticsearch-head-master/Gruntfile.js
   
   1. 在92行加入hostname:'*'
4. cmd进入elasticsearch-head-master文件夹执行npm install
5. 分别启动elasticsearch和elasticsearch-head

安装logstash-5.5.2和logstash-jdbc插件

1. 安装ruby并配置好环境变量
2. gem sources –l 查看源
3. gem –-remove https://rubygems.org 删除默认源
4. gem –a http://gems.ruby-china.org/ 添加新的源
5. 更改成功,还得修改Gemfile的数据源地址。步骤如下：
   
   1. gem install bundler
6. 安装logstash-input-jdbc
   
   1. 进入logstash的bin目录执行安装命令
      .\logstash-plugin.bat install logstash-input-jdbc
7. 开始导入数据
   
   1. 相关文件在\logstash\bin\mysql\(自建)下
   2. 增量导入和全量导入
      
      1. 增量导入: bin\logstash -f fullUpdate.conf -–path.data=bin\fullUpdateData
      2. 全量导入: bin\logstash –f incrementalUpdate.conf –path.data=bin\incrementalUpdateData

注意事项

1. 安装目录中切勿出现空格
   
   1. IP绑定:
      D:\ELK\elasticsearch-5.5.2\config\elasticsearch.yml中network.host:0.0.0.0
      目前测试方便,将ip权限开放,后期应该根据实际情况进行修改
2. 同步周期问题:
   
   1. D:\ELK\logstash-5.5.2\bin\mysql\ *.conf中
      设置监听间隔 各字段含义（由左至右）分、时、天、月、年，全部为*默认含义为每分钟都更新
      schedule => "* * * * *"
      后期应该根据实际情况进行修改

配置文件

//全量导入的配置文件
input {
    stdin {
    }
    jdbc {
      # mysql 数据库链接,test为数据库名
      jdbc_connection_string => "jdbc:mysql://120.92.168.166:3306/huiyizhan"
      # 用户名和密码
      jdbc_user => "root"
      jdbc_password => "Will2017@"
      # 驱动
      jdbc_driver_library => "H:\xjn\program\ELK\logstash-5.5.2\bin\mysql\mysql-connector-java-5.1.44.jar"
      # 驱动类名
      jdbc_driver_class => "com.mysql.jdbc.Driver"
      jdbc_paging_enabled => "true"
      jdbc_page_size => "50000"
      # 执行的sql 文件路径+名称
      statement_filepath => "H:\xjn\program\ELK\logstash-5.5.2\bin\mysql\fullUpdate.sql"
      # 设置监听间隔  各字段含义（由左至右）分、时、天、月、年，全部为*默认含义为每分钟都更新
      schedule => "* * * * *"
      # 索引类型
      type => "activity"
    }
}


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


output {
    elasticsearch {
        # ES的IP地址及端口
        hosts => ["192.168.64.130:9200"]
        # 索引名称
        index => "huiyizhan"
        # 自增ID 需要关联的数据库中有有一个id字段，对应索引的id号
        document_id => "%{id}"
    }
    stdout {
       # JSON格式输出
        codec => json_lines
    }
}

#全量导入的sql
    select 
activity.id, 
activity.user_id,
activity.activity_title,
activity.activity_address,
activity.start_time,
activity.end_time,
activity.activity_description,
activity.activity_banner,
activity.activity_banner_mobile,
u.avatar,
u.company,
u.nickname as nick_name,
u.username as user_name,
type.type_name
 from activity 
 left join user as u on activity.user_id=u.id
 left join activity_type as type on type.id = activity.activity_type
 where delete_status=1 and is_open = 1 and issue_status =1

增量导入配置文件
input {
    stdin {
    }
    jdbc {
      # mysql 数据库链接,test为数据库名
      jdbc_connection_string => "jdbc:mysql://120.92.168.166:3306/huiyizhan"
      # 用户名和密码
      jdbc_user => "root"
      jdbc_password => "Will2017@"
      # 驱动
      jdbc_driver_library => "H:\xjn\program\ELK\logstash-5.5.2\bin\mysql\mysql-connector-java-5.1.44.jar"
      # 驱动类名
      jdbc_driver_class => "com.mysql.jdbc.Driver"

      #处理中文乱码问题
      codec => plain { charset => "UTF-8"}
      #使用其它字段追踪，而不是用时间
      use_column_value => true
      #追踪的字段
      tracking_column => id
      record_last_run => true
     #上一个sql_last_value值的存放文件路径, 必须要在文件中指定字段的初始值
     last_run_metadata_path => "H:\xjn\program\ELK\logstash-5.5.2\bin\mysql\station_parameter.txt"
     #开启分页查询
     jdbc_paging_enabled => true
     jdbc_page_size => 300

      # 执行的sql 文件路径+名称
      statement_filepath => "H:\xjn\program\ELK\logstash-5.5.2\bin\mysql\incrementalUpdate.sql"
      # 设置监听间隔  各字段含义（由左至右）分、时、天、月、年，全部为*默认含义为每分钟都更新
      schedule => "* * * * *"
      # 索引类型
      type => "activity"

    }
}

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

output {
    elasticsearch {
        # ES的IP地址及端口
        hosts => ["192.168.64.130:9200"]
        # 索引名称
        index => "huiyizhan"
        # 自增ID
        document_id => "%{id}"
    }
    stdout {
        # JSON格式输出
        codec => json_lines
    }
}

增量导入sql
 select 
activity.id, 
activity.user_id,
activity.activity_title,
activity.activity_address,
activity.start_time,
activity.end_time,
activity.activity_description,
activity.activity_banner,
activity.activity_banner_mobile,
u.avatar,
u.company,
u.nickname as nick_name,
u.username as user_name,
type.type_name
 from activity 
 left join user as u on activity.user_id=u.id
 left join activity_type as type on type.id = activity.activity_type
 where delete_status=1 and is_open = 1 and issue_status =1 and activity.id >= :sql_last_value

//station_parameter.txt的内容
--- 43