//定义index

PUT /companysearch

{

  "mappings": {

    "properties": {

      "name": {

        "type": "text",

        "analyzer": "ik_max_word", #ik分词器

        "search_analyzer": "ik_smart"

      }

    }

  }

}

//同步logstash

input {

jdbc {

jdbc_connection_string => "jdbc:mysql://127.0.0.1:3306/sas?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&allowMultiQueries=true"

jdbc_user => "root"

jdbc_password => ""

jdbc_driver_library => "D:\soft\logstash-7.10.0-windows-x86_64\logstash-7.10.0\lib\mysql\mysql-connector-java-8.0.15.jar"

jdbc_driver_class => "com.mysql.jdbc.Driver"

jdbc_paging_enabled => "true"

jdbc_page_size => "50000"

jdbc_default_timezone => "Asia/Shanghai"

statement => "SELECT * FROM company" 

use_column_value => true

tracking_column => "id"

schedule => "* * * * *"

type => "jdbc"

}

}

output {

elasticsearch {

hosts => "127.0.0.1:9200"

index => "companysearch"

document_id => "%{id}"

}

}

//查询

GET /companysearch/_search

{

  "query": {

    "bool": {

      "must": [

        {

          "match": {

            "name": "广州有限公司"

          }

        }

      ]

    }

  }

}

--20201116重新定义index（字段完全定义，取消自动增加字段）

//添加索引index（只限定name参数） #ik分词器

PUT /companysearch

{

  "mappings": {

    "properties": {

      "name": {

        "type": "text",

        "analyzer": "ik_max_word",

        "search_analyzer": "ik_smart"

      }

    }

  }

}

//全量同步数据logstash（去掉logo,address）

input {

jdbc {

jdbc_connection_string => "jdbc:mysql://127.0.0.1:3306/sas?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&allowMultiQueries=true"

jdbc_user => "root"

jdbc_password => ""

jdbc_driver_library => "D:\soft\logstash-7.10.0-windows-x86_64\logstash-7.10.0\lib\mysql\mysql-connector-java-8.0.15.jar"

jdbc_driver_class => "com.mysql.jdbc.Driver"

jdbc_paging_enabled => "true"

jdbc_page_size => "50000"

jdbc_default_timezone => "Asia/Shanghai"

statement => "SELECT id,name,status,oper_name,regist_capi,start_date,sheng,shi,xian,country_id,phone,email,trade_id,trade FROM companysearch" 

schedule => "* * * * *"

type => "jdbc"

}

}

//增量同步数据new

input {

  jdbc {

    jdbc_connection_string => "jdbc:mysql://127.0.0.1:3306/qichacha?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true"

jdbc_user => "root"

jdbc_password => "WDELI1995"

jdbc_driver_library => "/data1/elasticsearch/logstash-7.10.0/lib/mysql/mysql-connector-java-8.0.15.jar"

jdbc_driver_class => "com.mysql.jdbc.Driver"

jdbc_paging_enabled => "true"

jdbc_page_size => "50000"

jdbc_default_timezone => "Asia/Shanghai"

    statement => "SELECT id,name,status,oper_name,regist_capi,start_date,sheng,shi,xian,country_id,phone,email,trade_id,trade FROM companysearch where id > :sql_last_value" 

    //为true时，默认会把当前UTC时间存储到last_run_metadata_path文件中, use_column_value为true时将会把上次查询的最后一条记录的tracking_column值记录下来,保存到 last_run_metadata_path 指定的文件中。

    record_last_run => true

    //是否使用最后一条数据的tracking_column值 track给sql_last_value。

    use_column_value => true

    //tracking_column_type：选用的自定义字段的类型，可为"numeric"或"timestamp"，默认数字。

    //选择的自定义字段名称，例如"modify_time"。

    tracking_column => "id"

    //sql_last_value：放在statement sql语句where后面，比如自增id或者modify_time。

    //把上次查询数据的标识放到文件里，文件的路径。

    last_run_metadata_path => "/data1/elasticsearch/logstash-7.10.0/bin/logstash_jdbc_last_run_index"

    schedule => "*/5 * * * * *"

    clean_run => false

    type => "jdbc"

  }

}

output {

elasticsearch {

hosts => "127.0.0.1:9200"

index => "companysearch"

document_id => "%{id}"

}

}

//执行

./logstash.bat -f ./mysql-to-elasticsearch.conf

//关闭elasticsearch

1.查找ES进程

ps -ef | grep elastic

2.杀掉ES进程

kill -9 2382（进程号）

3.重启ES

sh elasticsearch -d

-----------------

curl操作

curl -XGET "http://localhost:9200/_cat/indices"

//建立index以及设置分词

curl -XPUT "http://localhost:9200/companysearch?pretty" -H 'Content-Type: application/json' -d'{  "mappings": {    "properties": {      "name": {        "type": "text",        "analyzer": "ik_max_word",        "search_analyzer": "ik_smart"      }    }  }}'

curl -XPUT "http://localhost:9200/companysearch?pretty" -H 'Content-Type: application/json' -d '{

    "mappings": {

          "properties": {

                  "name": { "type": "text",        "analyzer": "ik_max_word",        "search_analyzer": "ik_smart"      },

                  "sheng": { "type": "keyword" },

                  "shi": { "type": "keyword" },

                  "xian": { "type": "keyword" }

          }

    }

}'

curl -XPUT "http://localhost:9200/companysearch?pretty" -H 'Content-Type: application/json' -d'{  "mappings": {    "properties": {      "name": {        "type": "text",        "analyzer": "ik_max_word",        "search_analyzer": "ik_smart"      }, "sheng": { "type": "keyword" }, "shi": { "type": "keyword" }, "xian": { "type": "keyword" },   }  }}'

//查询index的mapping

curl -XGET "http://localhost:9200/companysearch/_mapping?pretty"

//查询index数据

curl -XGET "http://localhost:9200/companysearch/_search?pretty" -H 'Content-Type: application/json' -d'{    "query": {    "bool": {      "must": [        {          "match": {            "name": "广州金盛"          }        }      ]    }  }}'

//结构查询

curl -XGET 'localhost:9200/companysearch/_search?pretty'  -H 'Content-Type: application/json' -d '{

    "query" : {

        "bool" : {

            "filter" : {

                "term" : { "shi" : "广州" }

            }

        }

    }

}'

//删除index

curl -XDELETE "http://localhost:9200/companysearch"

//常用查询语句

GET /_search

{

    "query": {

        "match": {

            "tweet": "elasticsearch"

        }

    }

}

{

    "bool": {

        "must": { "match":   { "email": "business opportunity" }},

        "should": [

            { "match":       { "starred": true }},

            { "bool": {

                "must":      { "match": { "folder": "inbox" }},

                "must_not":  { "match": { "spam": true }}

            }}

        ],

        "minimum_should_match": 1

    }

}