Logstash7.6.0同步MySQL到Elasticsearch

环境

操作系统：centos 7
mysql：8.0
elasticsearch：7.6.0
logstash：7.6.0

安装logstash

下载地址：https://www.elastic.co/cn/downloads/past-releases#logstash

选择对应的版本号下载tar.gz，上传到/opt/software

cd /opt/software
#解压到/usr/local
tar -zxvf logstash-7.6.0.tar.gz -C /usr/local
cd /usr/local/logstash-7.6.0/

解压结果：

配置同步

cd config
#创建配置文件，文件名自定义
touch mysql.conf

配置例子如下：

input {
  stdin {
  }
  jdbc {
    jdbc_driver_library => "/opt/jar/mysql-connector-java-8.0.11.jar"
    jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://192.168.1.100:3306/testdb?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8"
    jdbc_user => "root"
    jdbc_password => "root"
    schedule => "* * * * *"
    statement => "SELECT * FROM vw_user_es WHERE modified_on >= :sql_last_value"
    use_column_value => true
    tracking_column_type => "timestamp"
    tracking_column => "modified_on"
    last_run_metadata_path => "syncpoint_table"
  }
}
output {
    elasticsearch {
        # ES集群地址（如果是单机的填一个就行）
        hosts => ["192.168.1.100:9200", "192.168.1.101:9200", "192.168.1.102:9200"]
        # 索引名称
        index => "user"
        document_id => "%{id}"
    }
    stdout {
        # JSON格式输出
        codec => json_lines
    }
}

Filter

如果需要处理数据，比如SQL取的字段跟ES的字段名不一样，可以通过filter处理，在上面配置文件加上这块就行。

filter {
    mutate {
    			#把数据库的user_id赋给es的userId
                rename => {"user_id" => "userId"}
        }
}

启动

cd /usr/local/logstash-7.6.0
./bin/logstash -f ./config/mysql.conf