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