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*#\s+User@Host:\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 => ["http://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: http://localhost:5601/path
  # IPv6 addresses should always be defined as: https://[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 (https://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