mysql logstash 同步 实例

7 elasticSearch 与mysql 同步

7.1 logstash

7.1.1 介绍

Logstash是一款轻量级的日志搜集处理框架，可以方便的把分散的、多样化的日志搜集
起来，并进行自定义的处理，然后传输到指定的位置，比如某个服务器或者文件。

企业中的黄精搭档。

7.1.2 Logstash安装与测试

解压缩 logstash ，然后到bin目录下进行测试。通过代码验证安装成功。

Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。

D:\elasticsearch\logstash-5.6.8\bin>logstash -e 'input {stdin {}} output {stdout {}}'
Sending Logstash's logs to D:/elasticsearch/logstash-5.6.8/logs which is now configured via log4j2.properties
[2019-08-20T10:27:34,224][INFO ][logstash.modules.scaffold] Initializing module {:module_name=>"fb_apache", :directory=>"D:/elasticsearch/logstash-5.6.8/modules/fb_apache/configuration"}
[2019-08-20T10:27:34,228][INFO ][logstash.modules.scaffold] Initializing module {:module_name=>"netflow", :directory=>"D:/elasticsearch/logstash-5.6.8/modules/netflow/configuration"}
[2019-08-20T10:27:34,232][INFO ][logstash.setting.writabledirectory] Creating directory {:setting=>"path.queue", :path=>"D:/elasticsearch/logstash-5.6.8/data/queue"}
[2019-08-20T10:27:34,234][INFO ][logstash.setting.writabledirectory] Creating directory {:setting=>"path.dead_letter_queue", :path=>"D:/elasticsearch/logstash-5.6.8/data/dead_letter_queue"}
[2019-08-20T10:27:34,245][INFO ][logstash.agent           ] No persistent UUID file found. Generating new UUID {:uuid=>"7e6b95b2-3318-4a77-a02a-dcedf0028914", :path=>"D:/elasticsearch/logstash-5.6.8/data/uuid"}
[2019-08-20T10:27:34,357][INFO ][logstash.pipeline        ] Starting pipeline {"id"=>"main", "pipeline.workers"=>4, "pipeline.batch.size"=>125, "pipeline.batch.delay"=>5, "pipeline.max_inflight"=>500}
[2019-08-20T10:27:34,378][INFO ][logstash.pipeline        ] Pipeline main started
The stdin plugin is now waiting for input:
[2019-08-20T10:27:34,429][INFO ][logstash.agent           ] Successfully started Logstash API endpoint {:port=>9600}
ing
2019-08-20T02:27:41.127Z 7DXOSPRVGV8O21O ing
out pag
2019-08-20T02:27:46.199Z 7DXOSPRVGV8O21O out pag
hellowrld
2019-08-20T02:27:55.814Z 7DXOSPRVGV8O21O hellowrld

7.1.3 命令

命令参数： -e 代表执行语句

​ -f 表示执行文件，文件内放我们需要执行的语句，实际生产环境大多采用该方式

7.1.4 创建目录和准备驱动包

[外链图片转存失败(img-kzVF8bVM-1566271905028)(C:\Users\Administrator\Desktop\images\el\1566270365472.png)]

7.1.5 配置文件

input {
  jdbc {
	  # mysql jdbc connection string to our backup databse
	  jdbc_connection_string => "jdbc:mysql://192.168.85.198:3306/tensquare_article?characterEncoding=utf-8&&useSSL=false&&serverTimezone=Asia/Shanghai"
	  # 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 => "D:\elasticsearch\logstash-5.6.8\mysqletc\mysql-connector-java-5.1.46.jar"
	  # the name of the driver class for mysql
	  jdbc_driver_class => "com.mysql.jdbc.Driver"
	  jdbc_paging_enabled => "true"
	  jdbc_page_size => "50"
	  #以下对应着要执行的sql的绝对路径。
	  #statement_filepath => ""
	  statement => "SELECT id ,title,content,state from tb_article"
	  #定时字段 各字段含义（由左至右）分、时、天、月、年，全部为*默认含义为每分钟都更新（测试结果，不同的话请留言指出）
      schedule => "* * * * *"
  }
}



output {
  elasticsearch {
	  #ESIP地址与端口
	  hosts => "127.0.0.1:9200" 
	  #ES索引名称（自己定义的）
	  index => "search"
	  #自增ID编号
	  document_id => "%{id}"
	  document_type => "article"
  }
  stdout {
      #以JSON格式输出
      codec => json_lines
  }
}

7.1.6 启动同步命令

D:\elasticsearch\logstash-5.6.8\bin>logstash -f ..\mysqletc\mysql.conf
Sending Logstash's logs to D:/elasticsearch/logstash-5.6.8/logs which is now configured via log4j2.properties
[2019-08-20T10:47:29,496][INFO ][logstash.modules.scaffold] Initializing module {:module_name=>"fb_apache", :directory=>"D:/elasticsearch/logstash-5.6.8/modules/fb_apache
[2019-08-20T10:47:29,499][INFO ][logstash.modules.scaffold] Initializing module {:module_name=>"netflow", :directory=>"D:/elasticsearch/logstash-5.6.8/modules/netflow/con
[2019-08-20T10:47:30,344][INFO ][logstash.outputs.elasticsearch] Elasticsearch pool URLs updated {:changes=>{:removed=>[], :added=>[http://127.0.0.1:9200/]}}
[2019-08-20T10:47:30,346][INFO ][logstash.outputs.elasticsearch] Running health check to see if an Elasticsearch connection is working {:healthcheck_url=>http://127.0.0.1
[2019-08-20T10:47:30,447][WARN ][logstash.outputs.elasticsearch] Restored connection to ES instance {:url=>"http://127.0.0.1:9200/"}
[2019-08-20T10:47:30,530][INFO ][logstash.outputs.elasticsearch] Using mapping template from {:path=>nil}
[2019-08-20T10:47:30,537][INFO ][logstash.outputs.elasticsearch] Attempting to install template {:manage_template=>{"template"=>"logstash-*", "version"=>50001, "settings"
message_field"=>{"path_match"=>"message", "match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false}}}, {"string_fields"=>{"match"=>"*", "match_mapping_t
properties"=>{"@timestamp"=>{"type"=>"date", "include_in_all"=>false}, "@version"=>{"type"=>"keyword", "include_in_all"=>false}, "geoip"=>{"dynamic"=>true, "properties"=>
}}}}}}
[2019-08-20T10:47:30,549][INFO ][logstash.outputs.elasticsearch] Installing elasticsearch template to _template/logstash
[2019-08-20T10:47:30,672][INFO ][logstash.outputs.elasticsearch] New Elasticsearch output {:class=>"LogStash::Outputs::ElasticSearch", :hosts=>["//127.0.0.1:9200"]}
[2019-08-20T10:47:30,674][INFO ][logstash.pipeline        ] Starting pipeline {"id"=>"main", "pipeline.workers"=>4, "pipeline.batch.size"=>125, "pipeline.batch.delay"=>5,
[2019-08-20T10:47:31,001][INFO ][logstash.pipeline        ] Pipeline main started
[2019-08-20T10:47:31,119][INFO ][logstash.agent           ] Successfully started Logstash API endpoint {:port=>9600}
[2019-08-20T10:48:00,957][INFO ][logstash.inputs.jdbc     ] (0.009000s) SELECT version()
[2019-08-20T10:48:00,966][INFO ][logstash.inputs.jdbc     ] (0.004000s) SELECT count(*) AS `count` FROM (SELECT id ,title,content,state from tb_article) AS `t1` LIMIT 1
[2019-08-20T10:48:00,971][INFO ][logstash.inputs.jdbc     ] (0.003000s) SELECT * FROM (SELECT id ,title,content,state from tb_article) AS `t1` LIMIT 50 OFFSET 0
{"@version":"1","id":"1","state":"1","@timestamp":"2019-08-20T02:48:00.985Z","title":"中国","content":"我们都是中国人"}
{"@version":"1","id":"2","state":"1","@timestamp":"2019-08-20T02:48:00.989Z","title":"中国人很好","content":"滚滚滚只能是中古偶人"}
[2019-08-20T10:49:00,144][INFO ][logstash.inputs.jdbc     ] (0.001000s) SELECT version()
[2019-08-20T10:49:00,147][INFO ][logstash.inputs.jdbc     ] (0.002000s) SELECT count(*) AS `count` FROM (SELECT id ,title,content,state from tb_article) AS `t1` LIMIT 1
[2019-08-20T10:49:00,153][INFO ][logstash.inputs.jdbc     ] (0.003000s) SELECT * FROM (SELECT id ,title,content,state from tb_article) AS `t1` LIMIT 50 OFFSET 0
{"@version":"1","id":"1","state":"1","@timestamp":"2019-08-20T02:49:00.155Z","title":"中国","content":"我们都是中国人"}
{"@version":"1","id":"2","state":"1","@timestamp":"2019-08-20T02:49:00.156Z","title":"中国人很好","content":"滚滚滚只能是中古偶人"}

7.1.8 解决同步少8个小时的问题

添加过滤器，来解决。但是还是感觉有点不靠谱。后续再看吧。

filter {
  date {
    match => ["message","UNIX_MS"]
    target => "@timestamp"   
  }
 ruby { 
   code => "event.set('timestamp', event.get('@timestamp').time.localtime + 8*60*60)" 
 }
 ruby {
   code => "event.set('@timestamp',event.get('timestamp'))"
 }
 mutate {
   remove_field => ["timestamp"]
 }
}