ELK - logstash 的安装

logstash 的安装
环境：
系统：centos 7.5
数据库：mysql 5.7.27 [注：需 yum 安装]
服务：logstash-7.3.1
功能：将数据从 本地文件 --> mysql

安装步骤：
1. 下载 logstash
wget https://artifacts.elastic.co/downloads/logstash/logstash-7.3.1.tar.gz

2. 解压安装 logstash
tar xf logstash-7.3.0.tar.gz -C /data/elk

3. 安装 logstash-output-jdbc 插件，连接 mysql 时需要
联网版的插件安装：
cd /data/elk/logstash-7.3.0
./bin/logstash-plugin install logstash-output-jdbc

无网版的插件安装：
/*先找有网环境使用 logstash-plugin 安装 logstash-output-jdbc */
cd /data/elk/logstash-7.3.0
./bin/logstash-plugin install logstash-output-jdbc

/*在有网环境将 logstash-output-jdbc 打包*/
cd /data/elk/logstash-7.3.0
./bin/logstash-plugin prepare-offline-pack --overwrite --output /root/logstash-output-jdbc.zip logstash-output-jdbc

/*登陆无网环境，进行安装*/
cd /data/elk/logstash-7.3.0
./bin/logstash-plugin install file:///root/logstash-output-jdbc.zip

4. 下载 JDBC 插件依赖 mysql-connector-java-5.7.27 包
注：由于我们没有找到 mysql-connector-java-5.7.27-bin.jar 包的下载地址，所以这里我们使用 yum 的方式下载 mysql-connector-java.jar 的包
前提：我们使用的数据库是 yum 安装的
安装 "mysql-connector-java" 的包：
/*使用 yum 查找相关 "mysql-connector-java" 的包*/
# yum search mysql-connector-java
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
=================================================== N/S matched: mysql-connector-java ====================================================
mysql-connector-java.noarch : Official JDBC driver for MySQL

/*使用 yum 安装我们找到 mysql-connector-java 的包*/
yum -y install mysql-connector-java.noarch

/*查找关于我们安装的 mysql-connector-java 的包*/
# find /usr/ -name "mysql-connector-java*"
/usr/share/java/mysql-connector-java.jar

5. 进入 mysql 数据库授权，创库，创表
授权：
/*开启远程访问限制*/
mysql> grant all privileges on *.* to 'root'@'%' identified by 'Aa@1234!' with grant optiion;
Query OK, 0 rows affected, 1 warning (0.00 sec)

/*刷新mysql的相关权限表*/
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

创建数据库：
create database Logstash;

创建表：
CREATE TABLE `Mazu_sashimi` (
  `id` int NOT NULL AUTO_INCREMENT,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `guid` varchar(34) DEFAULT NULL,
  `protocol` int(11) DEFAULT NULL,
  `reqsharkseqNo` int(11) DEFAULT NULL,
  `elapsedtime` int(11) DEFAULT NULL,
  `client_ip` varchar(20) DEFAULT NULL,
  `Interface` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `Mazu_sashimi_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

5. 配置 logstash 过滤数据的配置文件
# vim /data/elk/logstash-7.3.0/config/logstash.conf
input {
    #从 file 文件中读取数据
      file {
        path => "/data/soft/logstash-7.3.0/log/TMF.Mazu_sashimi_20190817.log"
        start_position => "beginning"
        sincedb_path => "/dev/null"
      }
}

filter {
    #使用竖线 '|' 分割(
    mutate {
        split => ["message","|"]
    }

    #分割后，字段命名与赋值
    mutate {
        add_field => {
            "DATE" => "%{[message][0]}"
            "GUID" => "%{[message][1]}"
            "protocol" => "%{[message][2]}"
            "reqshark-seqNo" => "%{[message][3]}"
            "elapsedtime" => "%{[message][11]}"
            "client_ip" => "%{[message][12]}"
            "Interface" => "%{[message][19]}"
        }
    }
    #删除无用字段
    mutate {
         remove_field => ["@version","@timestamp","path","host","message"]
   }
}

output {
      #codec 输出到控制台
      stdout { 
        codec => rubydebug 
      }

      #输出到 mysql
      jdbc {
        driver_jar_path => "/usr/share/java/mysql-connector-java.jar"
        driver_class => "com.mysql.jdbc.Driver"
        connection_string => "jdbc:mysql://192.168.1.136:3306/Logstash?user=root&password=Aa@1234!"
        statement => ["insert into Mazu_sashimi(date,guid,protocol,reqsharkseqNo,elapsedtime,client_ip,Interface) values(?,?,?,?,?,?,?)","DATE","GUID","protocol","reqshark-seqNo","elapsedtime","client_ip","Interface"]
      }
}

7. 创建测试数据
# cat /data/soft/logstash-7.3.0/log/TMF.Mazu_sashimi_20190817.log
2019-08-17 00:00:22|03025619081214383611306130234249|2|171|20|0|155|17|0|9|0|4|182.119.80.193|b7970|1|0|0|10000|10000|SY2002|P4DF2S3GE6RQ|0

8. 启动 logstash
# cd /data/elk/logstash-7.3.0/
# bin/logstash -f config/logstash.conf

9. 进入数据库查询表中是否有数据
mysql> select * from Logstash.Mazu_sashimi;
+--------+---------------------+----------------------------------+----------+---------------+-------------+----------------+-----------+
| id     | date                | guid                             | protocol | reqsharkseqNo | elapsedtime | client_ip      | Interface |
+--------+---------------------+----------------------------------+----------+---------------+-------------+----------------+-----------+
| 1      | 2019-08-17 00:00:22 | 03025619081214383611306130234249 |        2 |           171 |           4 | 182.119.80.193 | SY2002    |
+--------+---------------------+----------------------------------+----------+---------------+-------------+----------------+-----------+
1 row in set (0.28 sec)