ELK学习实践(四)--Logstash连接SQLServer并同步数据

安装环境

软件环境	版本
操作系统	Windows 10 prefessional
容器工具	Docker Desktop for Windows
容器可视化工具	Kitematic
命令行工具	PowerShell

承接上篇文章：ELK学习实践(二)–基于Docker安装配置Logstash

---

1.解决logstash-input-jdbc安装失败的问题

一般情况下logstash的jdbc插件通过Docker内的命令行交互界面即可完成安装，但也会出现安装失败的情况的。在实际操作中，出现了多次执行“logstash-plugin install logstash-input-jdbc”命令无响应的情况，下面是一种通过DockerFile解决上述问题的方案。

在指定目录新建文件并命名为Dockerfile，如在目录D:/DockerShare/Logstash下新建文本文件，并将文件重命名为Dockerfile。（去掉文件名后缀）。
编辑Dockerfile脚本文件，添加命令

FROM elastic/logstash:6.8.2
MAINTAINER myname<myemail@hotmail.com>
#安装input插件
RUN logstash-plugin install logstash-input-jdbc
#安装output插件
RUN logstash-plugin install logstash-output-elasticsearch

Logstash支持多种类型的输入输出，其他input/output插件的安装同上。

保存Dockerfile脚本文件。在其所在文件夹上右键打开PowerShell。输入命令构建自定义镜像。

doocker build -t myname/mylogstash:1.0 .

注意：-t 表示为新镜像设置仓库和名称，其中myname为仓库名，mylogstash为镜像名，1.0为版本号（不写默认为latest）;最后有一个点“.”，表示Dockerfile文件在当前目录下。

构建完成后查看所有镜像，看到镜像构建成功。

为了方便日后使用，可以将新镜像上传到个人仓库里。

docker push myname/mylogstash:1.0

使用上述镜像创建容器

docker run -it -d --name logstash -p 5044:5044 -p 9600:9600 -v /d/DockerShare/Logstash/Config/:/usr/share/logstash/config/ myname/mylogstash:1.0

2.SQLServer数据库同步

logstash提供了灵活的数据采集同步策略，下面以一个增量同步日志的示例来说明如何使用logstash数据同步。

将数据库表中的日志记录通过logstash同步到Elasticsearch中，定时执行并将新增的日志记录同步。

首先在管道配置文件pipelines.yml中新增管道

 - pipeline.id: logs
   path.config: "/usr/share/logstash/config/logstash-log.conf"

注意：logstash支持多个数据管道，每个管道都可配置独立的输入输出。

创建并编辑配置文件logstash-log.confg

#输入
input {
  #从数据库输入
  jdbc {
    #数据库驱动
	jdbc_driver_library => "/usr/share/logstash/config/mssql-jdbc-7.0.0.jre8.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    #数据连接字符串
	jdbc_connection_string => "jdbc:sqlserver://192.168.199.203:1433;DatabaseName=RunGoZero;"
    jdbc_user => "admin"
    jdbc_password => "Admin123"
    # schedule => 分 时 天 月 年  ，每分钟执行一次
    schedule => "* * * * *"
    jdbc_paging_enabled => true
    jdbc_page_size => 100
    clean_run => false
    use_column_value => true
    #设置跟踪字段
    tracking_column => Id
	#是否启用最后运行记录跟踪
    record_last_run => true
	#最后运行记录存储路径
    last_run_metadata_path => "/usr/share/logstash/config/data-log.txt"
    #是否设置字段小写
    lowercase_column_names => false
	#数据查询脚本路径
    statement_filepath => "/usr/share/logstash/config/query-log.sql"
    #索引的类型
    type => "jdbc"
    }
}
#输出
output {
  #输出到elasticsearch
  elasticsearch {
    hosts => ["http://192.168.199.147:9200"]
    #索引名称
	index => "logs"
    #user => "elastic"
    #password => "changeme"
  }
}

注意：由于在创建容器时已经将容器的目录映射到本地主机目录，所以上述/usr/share/logstash/config/目录实际上时本地主机的D:/DockerShare/Logstash/Config目录，后面将继续使用这个目录。

配置项	说明
schedule => “分 时 天 月 年”	默认表示每分钟执行一次
tracking_column => 字段名	表示对特定字段进行跟踪，以判断其值是否发生变化
record_last_run => true/false	表示是否对tracking_column字段的最后值进行跟踪
last_run_metadata_path => 路径	表示将tracking_column字段的最后值存储在何处
statement_filepath => 路径	表示数据库查询脚本存储在何处
statement => SQL语句	表示查询数据的SQL语句，statement_filepath和statement不能同时存在

根据上述配置文件分别创建文件data-log.txt和query-log.sql，其中data-log.txt为空即可，对query-log.sql进行编辑。

SELECT Id,BrowserInfo,ClientIpAddress,ClientName,CustomData,ExecutionDuration,ExecutionTime,MethodName,ServiceName FROM AbpAuditLogs where Id > :sql_last_value

实际上就是SQL查询语句，如果查询语句比较简单可以直接写在statement配置项中，如果查询语句比较复杂就写在单独的SQL脚本文件中，然后将SQL脚本文件的路径写在statement_filepath配置项中。

上述关于数据库配置的运转过程大体如下：系统开始运行后，加载数据库脚本query-log.sql，连接数据库，执行数据库脚本查询数据，将查询结果同步到elasticsearch中名称为logs的索引中。同时将跟踪字段Id的最大值存入data-log.txt。一分钟后，加载并执行数据库脚本query-log.sql，由于跟踪字段Id本身是自增长的，所以要查询的就是Id大于data-log.txt中存储的sql_last_value的记录。如果有新增的记录就增加到elasticsearch。如此循环往复，每分钟执行一次。

配置文件编辑完成后，重启logstash，通过运行日志，可以观察到数据库中的数据被查询并输入到logstash。

通过Kibana或者Elasticsearch-Head的图形化界面，也可以看到数据已被输出到elasticsearch。