使用Logstash将Mysql数据同步到Elasticsearch

小咪咪 2023-06-23 17:55 167阅读 0赞

**使用logstash将mysql数据导入到elasticsearch过程：**

1 安装elasticsearch 网址：[https://www.elastic.co/downloads/elasticsearch][https_www.elastic.co_downloads_elasticsearch]，以下所有过程在win环境下。

2 解压启动，在win环境下进入bin目录，双击elasticsearch.bat启动，输入localhost:9200可以看到如下信息:

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjkzODI4_size_16_color_FFFFFF_t_70][]

3 安装elasticsearch-head-master插件  [https://github.com/mobz/elasticsearch-head][https_github.com_mobz_elasticsearch-head] :

在此之前需要安装node.js 然后执行npm install -g grunt-cli

4修改elasticsearch下elasticsearch.yml:

network.host: 0.0.0.0
    http.port: 9200
    # 解决elasticsearch-head 集群健康值: 未连接问题
    http.cors.enabled: true
    http.cors.allow-origin: "*"

5 启动elasticsearch-head-master可以见到如下界面:

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjkzODI4_size_16_color_FFFFFF_t_70 1][]

6安装logstash [https://www.elastic.co/cn/downloads/past-releases/logstash-6-2-4][https_www.elastic.co_cn_downloads_past-releases_logstash-6-2-4]

7 接下来是通过logstash将mysql表中数据导入到elasticsearch

进入logstash安装目录下的bin路径，新建sql文件（student.sql）用于存放执行的sql语句，

再新建data.conf文件:

input {
    
      jdbc {
    
        # 数据库驱动路径
    
        jdbc_driver_library => "D:/MavenRepository/mysql/mysql-connector-java/8.0.17/mysql-connector-java-8.0.17.jar"
    
        # 驱动名称
    
        jdbc_driver_class => "com.mysql.jdbc.Driver"
    
        # 数据库的连接字符串
    
        jdbc_connection_string => "jdbc:mysql://127.0.0.1:3306/test?serverTimezone=UTC"
    
        # 数据库用户名
    
        jdbc_user => "root"
    
        # 数据库密码
    
        jdbc_password => "a"
    
        # 重复执行导入任务的时间间隔  分-时-日-月-星期
    
        schedule => "* * * * *"
    
        # 导入的表(查询SQL，可以过滤数据)
    
         jdbc_paging_enabled => "true"
    
            jdbc_page_size => "1000"
    
        statement_filepath => "./student.sql"
    
        #  默认时区
    
        jdbc_default_timezone => "Asia/Shanghai"
    
      }
    
    }
    
    output {
    
      elasticsearch {
    
      # es地址
    
      hosts => "127.0.0.1:9200"
    
      # 索引名称
    
      index => "test"
    
      # 索引类型
    
      document_type => "student"
    
      # 类似主键，es中id对应数据库的字段
    
      document_id => "%{id}"
    
      }
    
    }

8 进入bin路径下，执行logstash -f data.conf便可以将数据同步到elasticsearch

9 此时访问localhost:9200可以看到文档：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjkzODI4_size_16_color_FFFFFF_t_70 2][]

删除全部文档：

Post  /索引名/类型名/\_delete\_by\_query?conflicts=proceed

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjkzODI4_size_16_color_FFFFFF_t_70 3][]

[https_www.elastic.co_downloads_elasticsearch]: https://www.elastic.co/downloads/elasticsearch
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjkzODI4_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2019122011363178.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjkzODI4,size_16,color_FFFFFF,t_70
[https_github.com_mobz_elasticsearch-head]: https://github.com/mobz/elasticsearch-head
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjkzODI4_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191220113916234.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjkzODI4,size_16,color_FFFFFF,t_70
[https_www.elastic.co_cn_downloads_past-releases_logstash-6-2-4]: https://www.elastic.co/cn/downloads/past-releases/logstash-6-2-4
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjkzODI4_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191220114120697.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjkzODI4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjkzODI4_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20201103165637230.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjkzODI4,size_16,color_FFFFFF,t_70