logstash同步mysql至es数据库脚本

相关mysql数据库脚本文件

mysql.conf配置文件

input {
  jdbc {
    jdbc_driver_library => "F:/Install/repository/mysql/mysql-connector-java/8.0.20/mysql-connector-java-8.0.20.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"    # 8.0以上版本：一定要把serverTimezone=UTC天加上
    jdbc_connection_string => "jdbc:mysql://ip:port/database?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true"
    jdbc_user => "root"
    jdbc_password => "password"
    schedule => "* * * * *"
    # statement_filepath => "filename.sql"
	#解决相差8小时 时差
    statement => "SELECT * FROM table WHERE acct_date  > convert_tz(:sql_last_value, '+00:00','-08:00') order by acct_date asc"
    
    jdbc_paging_enabled => "true"     
    jdbc_page_size => "5000"
	use_column_value => true
	tracking_column => "acct_date"
	tracking_column_type => "timestamp"
	clean_run => false
	last_run_metadata_path => "D:/software/elasticsearch/03.elasticsearch/logstash-7.13.1/config/logstash_metadata15"
	lowercase_column_names => true
	jdbc_default_timezone => "Asia/Shanghai"
	plugin_timezone => "local"
	type => "pufa"
  }


output {
	if[type] == "pfflow" {
        elasticsearch {
            hosts => ["localhost:9200"]
            index => "pfflow_0611"
            document_id => "%{id}"
        }
    }
    if[type] == "kpinfo" {
        elasticsearch {
            hosts => ["localhost:9200"]
            index => "kpinfo_0615"
            #document_id => "%{id}"
        }
    }
	if[type] == "pufa" {
        elasticsearch {
            hosts => ["localhost:9200"]
            index => "pufa_0615"
            document_id => "%{id}"
        }
    }
   
    stdout {
        # JSON格式输出
        codec => json_lines
    }
}

启动：进入logstash bin目录文件 cmd命令执行logstash -f “mysql.conf”