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

Myth丶恋晨 2022-06-02 07:54 266阅读 0赞

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

原文链接：[https://www.leon0204.com/article/94.html][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 ，原数数据

[https_www.leon0204.com_article_94.html]: https://www.leon0204.com/article/94.html