uncategorized

通过logstash-input-jdbc将mysql数据导入es5.x

,

logstash-input-jdbc 是 logstash 的一个 jdbc 插件

1. 在 es5.x 中通过如下命令安装该插件

1
2
3
4
5
cd /Users/xy/Workspace/search/logstash/logstash-5.3.0
bin/logstash-plugin install logstash-input-jdbc
Validating logstash-input-jdbc
Installing logstash-input-jdbc
Installation successful

2. 配置一个 mysql 计划任务的 logstash config 文件

1
2
cd /Users/xy/Workspace/search/logstash/logstash-5.3.0/config
touch mysqlsync.conf

2.1 文件内容如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
input {
    stdin {
    }
    jdbc {
      # mysql jdbc connection string to our backup databse
      jdbc_connection_string => "jdbc:mysql://localhost:3306/btdb"
      # the user we wish to excute our statement as
      jdbc_user => "root"
      jdbc_password => "123456"
      # the path to our downloaded jdbc driver
      jdbc_driver_library => "/Users/xy/Workspace/search/logstash/logstash-5.3.0/bin/mysql-connector-java-5.1.41.jar"
      # the name of the driver class for mysql
      jdbc_driver_class => "com.mysql.jdbc.Driver"
      jdbc_default_timezone => "Asia/Shanghai"
      jdbc_paging_enabled => "true"
      jdbc_page_size => "50000"
      statement_filepath => "/Users/xy/Workspace/search/logstash/logstash-5.3.0/config/mysqlsync.sql"
      schedule => "* * * * *"
      type => "jdbc"
      use_column_value => true
      tracking_column => time_created
      record_last_run => true
      last_run_metadata_path => "/Users/xy/Workspace/search/logstash/logstash-5.3.0/config/last_run_metadata"
    }
}

filter {
    json {
        source => "message"
        remove_field => ["message"]
    }
}

output {
    elasticsearch {
        hosts => ["127.0.0.1:9200"]
        index => "tmtpost"
        document_type => "auction"
        document_id => "%{id}"
    }
    stdout {
        codec => json_lines
    }
}

2.2output 中的 document_id 是指定的 mysqlsync.sql 中的一个字段名，一般使用主键就行

2.3input 中的 jdbc_driver_library 从下面地址中下载

1
https://repo1.maven.org/maven2/mysql/mysql-connector-java/

2.4.statement_filepath 指向的是需要同步的 sql 语句，logstash 会监测 sql 语句中指定的 table 的 update 和 insert 操作，mysqlsync.sql 示例如下

1
select id, name as title,body as summary, time_created from a

3. 配置完成后在 logstash 根目录启动服务

1
bin/logstash -f /Users/xy/Workspace/search/logstash/logstash-5.3.0/config/mysqlsync.conf

看到如下输出则代表启动成功

试着更新 mysql 表中数据看看是否有同步到 es 中

Share