MySQL數(shù)據(jù)以全量和增量方式，同步到ES搜索引擎

本文源碼：GitHub·點(diǎn)這里 || GitEE·點(diǎn)這里

一、配置詳解

場(chǎng)景描述：MySQL數(shù)據(jù)表以全量和增量的方式向ElasticSearch搜索引擎同步。

1、下載內(nèi)容

• elasticsearch 版本 6.3.2
• logstash 版本 6.3.2
• mysql-connector-java-5.1.13.jar

2、核心配置

• 路徑：/usr/local/logstash
• 新建配置目錄：sync-config

1)、配置全文

/usr/local/logstash/sync-config/cicadaes.conf

input {
    stdin {}
    jdbc {
        jdbc_connection_string => "jdbc:mysql://127.0.0.1:3306/cicada?characterEncoding=utf8"
        jdbc_user => "root"
        jdbc_password => "root123"
        jdbc_driver_library => "/usr/local/logstash/sync-config/mysql-connector-java-5.1.13.jar"
        jdbc_driver_class => "com.mysql.jdbc.Driver"
        jdbc_paging_enabled => "true"
        jdbc_page_size => "50000"
        jdbc_default_timezone => "Asia/Shanghai"
        statement_filepath => "/usr/local/logstash/sync-config/user_sql.sql"
        schedule => "* * * * *"
        type => "User"
        lowercase_column_names => false
        record_last_run => true
        use_column_value => true
        tracking_column => "updateTime"
        tracking_column_type => "timestamp"
        last_run_metadata_path => "/usr/local/logstash/sync-config/user_last_time"
        clean_run => false
    }
    jdbc {
        jdbc_connection_string => "jdbc:mysql://127.0.0.1:3306/cicada?characterEncoding=utf8"
        jdbc_user => "root"
        jdbc_password => "root123"
        jdbc_driver_library => "/usr/local/logstash/sync-config/mysql-connector-java-5.1.13.jar"
        jdbc_driver_class => "com.mysql.jdbc.Driver"
        jdbc_paging_enabled => "true"
        jdbc_page_size => "50000"
        jdbc_default_timezone => "Asia/Shanghai"
        statement_filepath => "/usr/local/logstash/sync-config/log_sql.sql"
        schedule => "* * * * *"
        type => "Log"
        lowercase_column_names => false
        record_last_run => true
        use_column_value => true
        tracking_column => "updateTime"
        tracking_column_type => "timestamp"
        last_run_metadata_path => "/usr/local/logstash/sync-config/log_last_time"
        clean_run => false
    }
}
filter {
    json {
        source => "message"
        remove_field => ["message"]
    }
}
output {
    if [type] == "User" {
        elasticsearch {
            hosts => ["127.0.0.1:9200"]
            index => "cicada_user_search"
            document_type => "user_search_index"
        }
    }
    if [type] == "Log" {
        elasticsearch {
            hosts => ["127.0.0.1:9200"]
            index => "cicada_log_search"
            document_type => "log_search_index"
        }
    }
}

2)、SQL文件

• user_sql.sql

  SELECT
  id,
  user_name userName,
  user_phone userPhone,
  create_time createTime,
  update_time updateTime
  FROM c_user
  WHERE update_time > : sql_last_value

• log_sql.sql

  SELECT
  id,
  param_value paramValue,
  request_ip requestIp,
  create_time createTime,
  update_time updateTime
  FROM c_log
  WHERE update_time > : sql_last_value

3)、配置參數(shù)說(shuō)明

• input參數(shù)

  statement_filepath：讀取SQL語(yǔ)句位置
  schedule ：這里配置每分鐘執(zhí)行一次
  type ：類型，寫入ES的標(biāo)識(shí)
  lowercase_column_names ：字段是否轉(zhuǎn)小寫
  record_last_run ：記錄上次執(zhí)行時(shí)間
  use_column_value ：使用列的值
  tracking_column ：根據(jù)寫入ES的updateTime字段區(qū)分增量數(shù)據(jù)
  tracking_column_type ：區(qū)分的字段類型

• output參數(shù)

  hosts ：ES服務(wù)地址
  index ：Index名稱，類比理解數(shù)據(jù)庫(kù)名稱
  document_type ：Type名稱，類比理解表名稱

  3、啟動(dòng)進(jìn)程

/usr/local/logstash/bin/logstash  
-f  
/usr/local/logstash/sync-config/cicadaes.conf

二、ES客戶端工具

1、下載軟件

kibana-6.3.2-windows-x86_64

2、修改配置

kibana-6.3.2-windows-x86_64\config\kibana.yml

添加配置：

elasticsearch.url: "http://127.0.0.1:9200"

3、雙擊啟動(dòng)

kibana-6.3.2-windows-x86_64\bin\kibana.bat

4、訪問(wèn)地址

http://localhost:5601

三、源代碼地址

GitHub·地址
https://github.com/cicadasmile/linux-system-base
GitEE·地址
https://gitee.com/cicadasmile/linux-system-base