MySQL日志收集之Filebeat和Logstsh的一键安装配置(ELK架构)

关于ELK是什么、做什么用,我们不在此讨论。本文重点在如何实现快速方便地安装logstash和filebeat组件,特别是在近千台DB Server的环境下(为了安全保守,公司DB Server 目前尚未部署saltstack一类的管控软件)。在尽可能标准化的条件下,希望可以实现一键化安装。下面是我们功能实现的一些尝试,我们把手动一步步操作打包提炼到一个sh文档中,安装部署时只要执行sh文件即可。部署安装logstash和filebeat组件由原来的10分钟缩减到目前的1分钟左右,并且减少了因手动部署带来的误操作。

1.logstash和filebeat安装包所在指定路径下

logstash的安装包logstash-7.6.0.zip所在路径

/data/logstash/logstash-7.6.0.zip

 

filebeat的安装包filebeat-7.4.2-linux-x86_64.tar.gz所在路径

 2.上传经过标准化的程序配置文件

上传程序的配置文件filebeat.service、filebeat.yml、logstash.conf、startup.options到指定位置,这些文件是格式化后的,不是解压的默认文件,目的是方便替换安装。

如何想直接使用disposelogcollectot.sh文件,上传的路径一定要是/tmp/

3.编写一键安装的可执行文件disposelogcollectot.sh

#!/bin/bash
# The version is defined V.001
# Version   ModifyTime                ModifyBy              Desc
# Ver001    2018-03-25            Carson.Xu             Create the Scripts File
# Desc: This file is used to despose filebeat \ logstash in order to  collect slow log and error log from mysqld.
#### step 1 判断 需要上传的文件是否已上传
cd /tmp/
if [ -f "filebeat.service" -a -f "filebeat.yml" -a -f "logstash.conf" -a -f "startup.options" ]
then
echo 'step 1 安装过程需要的文件已到位,上传文件项检查通过....'
else
echo "step 1 安装过程中需要的filebeat.service、 filebeat.yml、 logstash.conf、 startup.options,没有到位,不能继续安装,安装进程退出!!!"
exit
fi
#### step 2 解压指定文件
cd /data/logstash/
unzip logstash-7.6.0.zip
echo 'step 2 解压logstash项工作完成....'
sleep 3
#### step 3 删除解压后的指定文件
cd logstash-7.6.0/config/
rm -rf startup.options
echo 'step 3 删除解压后的指定文件startup.options工作完成....'
sleep 3
#### step 4 转移上传的文件
mv /tmp/logstash.conf /tmp/startup.options -t /data/logstash/logstash-7.6.0/config/
echo 'step 4 转移文件logstash.conf的工作完成....'
sleep 2
#### step 5 修改log上传的ES 索引[必做 建议用业务名称替换,例如qq/weixin/rewu]
read  -p "请输入业务名称:" product
echo -e "\n"
echo "用户名为:$product"
sed -i "s/qqweixinface/$product/" /data/logstash/logstash-7.6.0/config/logstash.conf
echo 'step 5 删除解压后的指定文件startup.options工作完成....'
sleep 2
##### step 6 安装logstash 服务
/data/logstash/logstash-7.6.0/bin/system-install
echo 'step 6 安装logstash 服务工作完成....'
sleep 3
##### step 7 解压缩filebeat文件
cd /data/filebeat/
tar -zxvf filebeat-7.4.2-linux-x86_64.tar.gz
echo 'step 7 解压缩filebeat文件工作完成....'
sleep 3
#### step 8 转移上传的filebeat.yml,允许覆盖掉生成默认配置文件
rm -rf /data/filebeat/filebeat-7.4.2-linux-x86_64/filebeat.yml
mv /tmp/filebeat.yml /data/filebeat/filebeat-7.4.2-linux-x86_64/
echo 'step 8 转移上传的filebeat.yml,允许覆盖掉生成默认配置文件工作完成....'
sleep 2
#### step 9 权限调整
cd /data/filebeat/filebeat-7.4.2-linux-x86_64
chown -R root:root filebeat.yml
chmod 600 filebeat.yml
echo 'step 9 调整filebeat文件权限的工作完成....'
sleep 2
##### step 10 获取 Server IP
ip=$(ip a|awk -F "inet|/"  '/inet.*brd/ {print $2}'|head -n 1)
serverid=$(echo $ip) #去除左右空格
echo $serverid
echo 'step 10 获取Server IP的工作完成....'
echo '获取Server IP的为:' $serverid
sleep 1
###### step 11 调整host配置[必做 IP替换]
sed -i "s/119.119.119.119/$serverid/" /data/filebeat/filebeat-7.4.2-linux-x86_64/filebeat.yml
echo 'step 11 替换配置文件中的Server IP工作完成....'
sleep 1
###### step 12 转移服务文件
mv /tmp/filebeat.service /etc/systemd/system/
echo 'step 12 将filebeat服务的文件移动到指定位置的工作完成....'
sleep 1
###### step 13 服务设置及启动
systemctl enable logstash.service
systemctl enable filebeat.service
echo 'step 13 将服务设置为自启动的工作完成....'
systemctl start logstash.service
sleep 20
systemctl start filebeat.service
sleep 10
##### step 14 检查服务是否已正常启动
logstashservice_check_result=`systemctl status logstash.service | grep "active (running)"| wc -l`
if [ "$logstashservice_check_result" == "1" ]
then
echo 'step 14 检查logstash.service已启动....'
else
echo "step 14 检查logstash.service未正常启动....,安装进程退出!!!"
exit
fi
sleep 3
filebeatservice_check_result=`systemctl status filebeat.service | grep "active (running)"| wc -l`
if [ "$filebeatservice_check_result" == "1" ]
then
echo 'step 14 检查filebeat.service已启动....'
else
echo "step 14 检查filebeat.service未正常启动....,安装进程退出!!!"
exit
fi
###### step 15 安装过程结束
echo 'step 15 安装过程结束'

4.执行

chmod 755 disposelogcollectot.sh
sh -x disposelogcollectot.sh

5.附录

在附录这一部分,介绍刚刚上传的文件–filebeat.service、filebeat.yml、logstash.conf、startup.options

5.1 文件startup.options

这一个文件主要描述了logstash程序的启动配置

################################################################################
# These settings are ONLY used by $LS_HOME/bin/system-install to create a custom
# startup script for Logstash and is not used by Logstash itself. It should
# automagically use the init system (systemd, upstart, sysv, etc.) that your
# Linux distribution uses.
#
# After changing anything here, you need to re-run $LS_HOME/bin/system-install
# as root to push the changes to the init script.
################################################################################
# Override Java location
#JAVACMD=/usr/bin/java
# Set a home directory
LS_HOME=/data/logstash/logstash-7.6.0
# logstash settings directory, the path which contains logstash.yml
LS_SETTINGS_DIR=/data/logstash/logstash-7.6.0/config
# Arguments to pass to logstash
LS_OPTS="--path.settings ${LS_SETTINGS_DIR} -f /data/logstash/logstash-7.6.0/config/logstash.conf"
# Arguments to pass to java
LS_JAVA_OPTS=""
# pidfiles aren't used the same way for upstart and systemd; this is for sysv users.
LS_PIDFILE=/var/run/logstash.pid
# user and group id to be invoked as
LS_USER=root
LS_GROUP=root
# Enable GC logging by uncommenting the appropriate lines in the GC logging
# section in jvm.options
LS_GC_LOG_FILE=/var/log/logstash/gc.log
# Open file limit
LS_OPEN_FILES=16384
# Nice level
LS_NICE=19
# Change these to have the init script named and described differently
# This is useful when running multiple instances of Logstash on the same
# physical box or vm
SERVICE_NAME="logstash"
SERVICE_DESCRIPTION="logstash"
# If you need to run a command or script before launching Logstash, put it
# between the lines beginning with `read` and `EOM`, and uncomment those lines.
###
## read -r -d '' PRESTART << EOM
## EOM

 5.2 附件logstash.conf

这个文件主要说明的是格式化读取的数据 以及 如何保存到elasticsearch中

# Sample Logstash configuration for creating a simple
# Beats -> Logstash -> Elasticsearch pipeline.
input {
beats {
port => 5044
}
}
filter {
if [fields][log_type] == "mysql-slow" {
grok {
match => ["message", "(?m)^#\s+Time:\s+%{TIMESTAMP_ISO8601}\s*#\[email protected]:\s+(?<user>.*)\[%{USERNAME:user}?\]\s*@\s*%{IPORHOST:client}?\s*\[%{IPORHOST:client}?\]\s+Id:\s+%{BASE10NUM}\s*#\s+Query_time:\s+%{BASE10NUM:query_time}\s+Lock_time:\s+%{BASE10NUM:lock_time}\s+Rows_sent:\s+%{BASE10NUM:rows_sent}\s+Rows_examined:\s+%{BASE10NUM:rows_examined}\s*(use\s+%{DATA:database};\s*)?SET\s+timestamp=%{BASE10NUM:timestamp};\s*%{GREEDYDATA:sql_stmt}$"]
keep_empty_captures => true
}
date {
match => ["timestamp", "UNIX"]
remove_field => ["timestamp"]
}
mutate {
convert => {
"query_time" => "float"
"lock_time" => "float"
"rows_sent" => "integer"
"rows_examined" => "integer"
}
remove_field => ["@version", "beat", "host", "input", "log", "offset", "prospector", "source", "tags"]
}
}
if [fields][log_type] == "mysql-error" {
grok {
match => ["message", "(?m)^%{TIMESTAMP_ISO8601:timestamp} %{BASE10NUM} \[%{WORD:error_level}\] %{GREEDYDATA:error_msg}$"]
}
date {
match=> ["timestamp", "ISO8601"]
remove_field => ["timestamp"]
}
mutate {
remove_field => ["@version", "beat", "host", "input", "log", "offset", "prospector", "source", "tags"]
}
}
}
output {
elasticsearch {
hosts => ["//110.110.110.110:10192"]
#index => "%{[@metadata][beat]}-%{[@metadata][version]}-%{+YYYY.MM.dd}"
index => "%{[fields][log_type]}-qqweixinface-%{+YYYY.MM.dd}"
user => "qquid_es"
password => "xiang_ni_123+yidiandian"
}
}

 5.3 附件filebeat.yml

这个文件主要说明了filebeat读取什么log,已经对读取的数据如何处理

###################### Filebeat Configuration Example #########################
# This file is an example configuration file highlighting only the most common
# options. The filebeat.reference.yml file from the same directory contains all the
# supported options with more comments. You can use it as a reference.
#
# You can find the full configuration reference here:
# https://www.elastic.co/guide/en/beats/filebeat/index.html

# For more available modules and options, please see the filebeat.reference.yml sample
# configuration file.
#=========================== Filebeat inputs =============================
filebeat.inputs:
# Each - is an input. Most options can be set at the input level, so
# you can use different inputs for various configurations.
# Below are the input specific configurations.
- type: log
# Change to true to enable this input configuration.
#enabled: false
# Paths that should be crawled and fetched. Glob based paths.
#paths:
#- /var/log/*.log
#- c:\programdata\elasticsearch\logs\*
paths:
- /data/mysql/data/slow.log
fields:
log_type: mysql-slow
db_host: 119.119.119.119
db_port: 3306
multiline.pattern: "^# Time:"
multiline.negate: true
multiline.match: after
- type: log
paths:
- /data/mysql/data/error.log
fields:
log_type: mysql-error
db_host: 119.119.119.119
db_port: 3306
multiline.pattern: ^20\d{2}-\d{2}-\d{2}T
multiline.negate: true
multiline.match: after
# Exclude lines. A list of regular expressions to match. It drops the lines that are
# matching any regular expression from the list.
#exclude_lines: ['^DBG']
# Include lines. A list of regular expressions to match. It exports the lines that are
# matching any regular expression from the list.
#include_lines: ['^ERR', '^WARN']
# Exclude files. A list of regular expressions to match. Filebeat drops the files that
# are matching any regular expression from the list. By default, no files are dropped.
#exclude_files: ['.gz$']
# Optional additional fields. These fields can be freely picked
# to add additional information to the crawled log files for filtering
#fields:
#  level: debug
#  review: 1
### Multiline options
# Multiline can be used for log messages spanning multiple lines. This is common
# for Java Stack Traces or C-Line Continuation
# The regexp Pattern that has to be matched. The example pattern matches all lines starting with [
#multiline.pattern: ^\[
# Defines if the pattern set under pattern should be negated or not. Default is false.
#multiline.negate: false
# Match can be set to "after" or "before". It is used to define if lines should be append to a pattern
# that was (not) matched before or after or as long as a pattern is not matched based on negate.
# Note: After is the equivalent to previous and before is the equivalent to to next in Logstash
#multiline.match: after
#============================= Filebeat modules ===============================
filebeat.config.modules:
# Glob pattern for configuration loading
path: ${path.config}/modules.d/*.yml
# Set to true to enable config reloading
reload.enabled: false
# Period on which files under path should be checked for changes
#reload.period: 10s
#==================== Elasticsearch template setting ==========================
setup.template.settings:
index.number_of_shards: 1
#index.codec: best_compression
#_source.enabled: false
#================================ General =====================================
# The name of the shipper that publishes the network data. It can be used to group
# all the transactions sent by a single shipper in the web interface.
#name:
# The tags of the shipper are included in their own field with each
# transaction published.
#tags: ["service-X", "web-tier"]
# Optional fields that you can specify to add additional information to the
# output.
#fields:
#  env: staging
#============================== Dashboards =====================================
# These settings control loading the sample dashboards to the Kibana index. Loading
# the dashboards is disabled by default and can be enabled either by setting the
# options here or by using the `setup` command.
#setup.dashboards.enabled: false
# The URL from where to download the dashboards archive. By default this URL
# has a value which is computed based on the Beat name and version. For released
# versions, this URL points to the dashboard archive on the artifacts.elastic.co
# website.
#setup.dashboards.url:
#============================== Kibana =====================================
# Starting with Beats version 6.0.0, the dashboards are loaded via the Kibana API.
# This requires a Kibana endpoint configuration.
setup.kibana:
# Kibana Host
# Scheme and port can be left out and will be set to the default (http and 5601)
# In case you specify and additional path, the scheme is required: //localhost:5601/path
# IPv6 addresses should always be defined as: //[2001:db8::1]:5601
#host: "localhost:5601"
# Kibana Space ID
# ID of the Kibana Space into which the dashboards should be loaded. By default,
# the Default Space will be used.
#space.id:
#============================= Elastic Cloud ==================================
# These settings simplify using Filebeat with the Elastic Cloud (//cloud.elastic.co/).
# The cloud.id setting overwrites the `output.elasticsearch.hosts` and
# `setup.kibana.host` options.
# You can find the `cloud.id` in the Elastic Cloud web UI.
#cloud.id:
# The cloud.auth setting overwrites the `output.elasticsearch.username` and
# `output.elasticsearch.password` settings. The format is `<user>:<pass>`.
#cloud.auth:
#================================ Outputs =====================================
# Configure what output to use when sending the data collected by the beat.
#-------------------------- Elasticsearch output ------------------------------
#output.elasticsearch:
# Array of hosts to connect to.
#hosts: ["localhost:9200"]
# Optional protocol and basic auth credentials.
#protocol: "https"
#username: "elastic"
#password: "changeme"
#----------------------------- Logstash output --------------------------------
output.logstash:
# The Logstash hosts
hosts: ["localhost:5044"]
# Optional SSL. By default is off.
# List of root certificates for HTTPS server verifications
#ssl.certificate_authorities: ["/etc/pki/root/ca.pem"]
# Certificate for SSL client authentication
#ssl.certificate: "/etc/pki/client/cert.pem"
# Client Certificate Key
#ssl.key: "/etc/pki/client/cert.key"
#================================ Processors =====================================
# Configure processors to enhance or manipulate events generated by the beat.
processors:
- add_host_metadata: ~
- add_cloud_metadata: ~
#================================ Logging =====================================
# Sets log level. The default log level is info.
# Available log levels are: error, warning, info, debug
#logging.level: debug
# At debug level, you can selectively enable logging only for some components.
# To enable all selectors use ["*"]. Examples of other selectors are "beat",
# "publish", "service".
#logging.selectors: ["*"]
#============================== X-Pack Monitoring ===============================
# filebeat can export internal metrics to a central Elasticsearch monitoring
# cluster.  This requires xpack monitoring to be enabled in Elasticsearch.  The
# reporting is disabled by default.
# Set to true to enable the monitoring reporter.
#monitoring.enabled: false
# Sets the UUID of the Elasticsearch cluster under which monitoring data for this
# Filebeat instance will appear in the Stack Monitoring UI. If output.elasticsearch
# is enabled, the UUID is derived from the Elasticsearch cluster referenced by output.elasticsearch.
#monitoring.cluster_uuid:
# Uncomment to send the metrics to Elasticsearch. Most settings from the
# Elasticsearch output are accepted here as well.
# Note that the settings should point to your Elasticsearch *monitoring* cluster.
# Any setting that is not set is automatically inherited from the Elasticsearch
# output configuration, so if you have the Elasticsearch output configured such
# that it is pointing to your Elasticsearch monitoring cluster, you can simply
# uncomment the following line.
#monitoring.elasticsearch:
#================================= Migration ==================================
# This allows to enable 6.7 migration aliases
#migration.6_to_7.enabled: true

 5.4.附件filebeat.service

这个文件是关于filebeat.service的定义

[Unit]
Description=filebeat.service
[Service]
User=root
ExecStart=/data/filebeat/filebeat-7.4.2-linux-x86_64/filebeat -e -c /data/filebeat/filebeat-7.4.2-linux-x86_64/filebeat.yml
[Install]
WantedBy=multi-user.target