使用Logstash将MySQL数据同步到Elasticsearch

男娘i 2023-07-13 03:49 101阅读 0赞

**附：[logstash官网下载地址][logstash]**

### 1.准备环境 ###

**jdk8;Logstash\-7.4.2;Elasticsearch-7.4.2.**

![20200310115726382.png][]

### 2、数据同步配置 ###

**1.解压Logstash压缩包，并在Logstash文件夹中创建sync文件夹。**

![20200310150332886.png][]

**2.创建logstash-db-sync.conf，foodie-items.sql空文件。**

**3.配置logstash-db-sync.conf：同****步配置**

**logstash-db-sync.conf：**

input {
        jdbc {
            # 设置 MySql/MariaDB 数据库url以及数据库名称
            jdbc_connection_string => "jdbc:mysql://192.168.160.130:3306/foodie-shop?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = Asia/Shanghai"
            # 用户名和密码
            jdbc_user => "root"
            jdbc_password => "123456"
            # 数据库驱动所在位置，可以是绝对路径或者相对路径
            jdbc_driver_library => "/usr/local/logstash-7.4.2/sync/mysql-connector-java-8.0.19.jar"
            # 驱动类名
            jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
            # 开启分页
            jdbc_paging_enabled => "true"
            # 分页每页数量，可以自定义
            jdbc_page_size => "1000"
            # 执行的sql文件路径
            statement_filepath => "/usr/local/logstash-7.4.2/sync/foodie-items.sql"
            # 设置定时任务间隔  含义：分、时、天、月、年，全部为*默认含义为每分钟跑一次任务
            schedule => "* * * * *"
            # 索引类型
            type => "_doc"
            # 是否开启记录上次追踪的结果，也就是上次更新的时间，这个会记录到 last_run_metadata_path 的文件
            use_column_value => true
            # 记录上一次追踪的结果值
            last_run_metadata_path => "/usr/local/logstash-7.4.2/sync/track_time"
            # 如果 use_column_value 为true， 配置本参数，追踪的 column 名，可以是自增id或者时间
            tracking_column => "updated_time"
            # tracking_column 对应字段的类型
            tracking_column_type => "timestamp"
            # 是否清除 last_run_metadata_path 的记录，true则每次都从头开始查询所有的数据库记录
            clean_run => false
            # 数据库字段名称大写转小写
            lowercase_column_names => false
        }
    }
    output {
        elasticsearch {
            # es地址
            hosts => ["192.168.160.130:9200","192.168.160.133:9200","192.168.160.134:9200"]
            # 同步的索引名
            index => "foodie-items"
            # 设置_docID和数据相同
            #document_id => "%{id}"
            document_id => "%{itemId}"
        }
        # 日志输出
        stdout {
            codec => json_lines
        }
    }

**ps: foodie-items 索引名 需要先在es中创建好。hosts ：这里是数组，如果是单节点，只写一个即可。itemId是数据库表id的别名，如果起了别名，这里需要改为对应的别名。**

**4：写sql语句：要执行的sql语句**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDk1Nzg2_size_16_color_FFFFFF_t_70][]

** foodie-items.sql：**

SELECT 
    	id as itemId,
    	item_name as itemName,
    	sell_counts as sellCounts
     FROM `items` where updated_time >= :sql_last_value

**ps: sql\_last\_value是个变量，主要是给Logstash使用的。sql语句不能使用;结尾。因为Logstash会对你的sql外面再包一层，然后count统计下。**

**下面语句可以将items表中的updated\_time时间，统一调大几个月，便于测试。**

update items set updated_time=DATE_ADD(updated_time,INTERVAL 3 MONTH);

**配置完成后，执行如下命令： **

[root@localhost bin]# ./logstash -f /usr/local/logstash-7.4.2/sync/logstash-db-sync.conf

**报错信息：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDk1Nzg2_size_16_color_FFFFFF_t_70 1][]

**ps:看上图可以发现Logstash\-7.4.2的logstash-input-jdbc是4.3.16版本的。**

**附：GitHub：[com.mysql.jdbc.Driver not loaded. Are you sure you've included the correct jdbc driver in :jdbc\_driver\_library][com.mysql.jdbc.Driver not loaded. Are you sure you_ve included the correct jdbc driver in _jdbc_driver_library]**

**GitHub上给的建议是将logstash-input-jdbc升级到4.3.18，我此处直接升级到目前最新版本4.3.19。**

**参考：**

**       [logstash-input-jdbc插件安装][logstash-input-jdbc]**

**       [logstash-input-jdbc 下载][logstash-input-jdbc 1]**

[root@localhost opt]# wget https://rubygems.org/downloads/logstash-input-jdbc-4.3.18.gem
    [root@localhost opt]# cd /usr/local/logstash-7.4.2/bin
    [root@localhost bin]# ./logstash-plugin install /opt/logstash-input-jdbc-4.3.19.gem

** 耐心等待一会儿。出现Installing logstash-input-jdbc   Installation successful  代表安装成功。**

**注：使用jps查看运行进程，如果logstash已经运行，则kill -9 进程号，然后重新启动logstash。**

**执行命令：**

[root@localhost bin]# ./logstash -f /usr/local/logstash-7.4.2/sync/logstash-db-sync.conf

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDk1Nzg2_size_16_color_FFFFFF_t_70 2][]

**使用elasticsearch-head查看，数据已经成功同步到了es中了。**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDk1Nzg2_size_16_color_FFFFFF_t_70 3][]

[logstash]: https://www.elastic.co/cn/downloads/past-releases#logstash
[20200310115726382.png]: https://img-blog.csdnimg.cn/20200310115726382.png
[20200310150332886.png]: https://img-blog.csdnimg.cn/20200310150332886.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDk1Nzg2_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2020031014592782.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDk1Nzg2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDk1Nzg2_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200310124700651.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDk1Nzg2,size_16,color_FFFFFF,t_70
[com.mysql.jdbc.Driver not loaded. Are you sure you_ve included the correct jdbc driver in _jdbc_driver_library]: https://github.com/elastic/logstash/issues/11147
[logstash-input-jdbc]: https://www.jianshu.com/p/a923b13977e6
[logstash-input-jdbc 1]: https://rubygems.org/gems/logstash-input-jdbc
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDk1Nzg2_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200310150418869.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDk1Nzg2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDk1Nzg2_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200310150633166.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDk1Nzg2,size_16,color_FFFFFF,t_70