MySQL数据以全量和增量方式，向ES搜索引擎同步流程

input {
    stdin {}
    jdbc {
        jdbc_connection_string => "jdbc:mysql://127.0.0.1:3306/cicada?characterEncoding=utf8"
        jdbc_user => "root"
        jdbc_password => "root123"
        jdbc_driver_library => "/usr/local/logstash/sync-config/mysql-connector-java-5.1.13.jar"
        jdbc_driver_class => "com.mysql.jdbc.Driver"
        jdbc_paging_enabled => "true"
        jdbc_page_size => "50000"
        jdbc_default_timezone => "Asia/Shanghai"
        statement_filepath => "/usr/local/logstash/sync-config/user_sql.sql"
        schedule => "* * * * *"
        type => "User"
        lowercase_column_names => false
        record_last_run => true
        use_column_value => true
        tracking_column => "updateTime"
        tracking_column_type => "timestamp"
        last_run_metadata_path => "/usr/local/logstash/sync-config/user_last_time"
        clean_run => false
    }
    jdbc {
        jdbc_connection_string => "jdbc:mysql://127.0.0.1:3306/cicada?characterEncoding=utf8"
        jdbc_user => "root"
        jdbc_password => "root123"
        jdbc_driver_library => "/usr/local/logstash/sync-config/mysql-connector-java-5.1.13.jar"
        jdbc_driver_class => "com.mysql.jdbc.Driver"
        jdbc_paging_enabled => "true"
        jdbc_page_size => "50000"
        jdbc_default_timezone => "Asia/Shanghai"
        statement_filepath => "/usr/local/logstash/sync-config/log_sql.sql"
        schedule => "* * * * *"
        type => "Log"
        lowercase_column_names => false
        record_last_run => true
        use_column_value => true
        tracking_column => "updateTime"
        tracking_column_type => "timestamp"
        last_run_metadata_path => "/usr/local/logstash/sync-config/log_last_time"
        clean_run => false
    }
}
filter {
    json {
        source => "message"
        remove_field => ["message"]
    }
}
output {
    if [type] == "User" {
        elasticsearch {
            hosts => ["127.0.0.1:9200"]
            index => "cicada_user_search"
            document_type => "user_search_index"
        }
    }
    if [type] == "Log" {
        elasticsearch {
            hosts => ["127.0.0.1:9200"]
            index => "cicada_log_search"
            document_type => "log_search_index"
        }
    }
}

SELECT 
    id,
    user_name userName,
    user_phone userPhone,
    create_time createTime,
    update_time updateTime
FROM c_user
WHERE update_time > :sql_last_value

SELECT 
    id,
    param_value paramValue,
    request_ip requestIp,
    create_time createTime,
    update_time updateTime
FROM c_log
WHERE update_time > :sql_last_value

statement_filepath：读取SQL语句位置
schedule：这里配置每分钟执行一次
type：类型，写入ES的标识
lowercase_column_names：字段是否转小写
record_last_run：记录上次执行时间
use_column_value：使用列的值
tracking_column：根据写入ES的updateTime字段区分增量数据
tracking_column_type：区分的字段类型

hosts：ES服务地址
index：Index名称，类比理解数据库名称
document_type：Type名称，类比理解表名称

/usr/local/logstash/bin/logstash -f /usr/local/logstash/sync-config/cicadaes.conf

elasticsearch.url: "http://127.0.0.1:9200"

http://localhost:5601

GitHub 地址：https://github.com/cicadasmile/linux-system-base
GitEE 地址：https://gitee.com/cicadasmile/linux-system-base