跳转至

14.监控慢日志

1. 修改filebeat配置文件

# 10.0.8.5

vim /etc/filebeat/filebeat.yml

#1.监控mysql慢日志,添加15-19行,把其他的都注释!
filebeat.inputs:
- type: log
  enabled: True
  paths:
    - /usr/local/mysql/data/web-slow.log

#2.关闭输出到elasticsearch的内容

#3.打开logstash输出的注释,修改logstash所在的主机!
output.elasticsearch:
  hosts: ["10.0.8.5:9200"]

1. 监控MySQL慢日志

 13 # ============================== Filebeat inputs ===============================
 14
 15 filebeat.inputs:
 16 - type: log
 17   enabled: True
 18   paths:
 19     - /usr/local/mysql/data/web-slow.log

2. 注释elasticsearch输出

134 # ---------------------------- Elasticsearch Output ----------------------------
135 #setup.template.name: "10.0.8.5"
136 #setup.template.pattern: "10.0.8.5-"
137 #setup.ilm.enabled: false
138 #output.elasticsearch:
139   # Array of hosts to connect to.
140   # hosts: ["10.0.8.5:9200"]
141   #index: "10.0.8.5-syslog-chupeng_%{+yyyy.MM.dd}"
142   # Protocol - either `http` (default) or `https`.
143   #protocol: "https"
144
145   # Authentication credentials - either API key or username/password.
146   #api_key: "id:api_key"
147   #username: "elastic"
148   #password: "changeme"

3. 开启logstash输出

150 # ------------------------------ Logstash Output -------------------------------
151 output.logstash:
152   # The Logstash hosts
153   hosts: ["10.0.8.5:5044"]
154   # Optional SSL. By default is off.
155   # List of root certificates for HTTPS server verifications
156   #ssl.certificate_authorities: ["/etc/pki/root/ca.pem"]
157
158   # Certificate for SSL client authentication
159   #ssl.certificate: "/etc/pki/client/cert.pem"
160
161   # Client Certificate Key
162   #ssl.key: "/etc/pki/client/cert.key"

2. 开启logstash

# 窗口1
/usr/share/logstash/bin/logstash -f /etc/logstash/conf.d/logstash-to-elasticsearch.conf

3. 重启filebeat

# 窗口2
systemctl restart filebeat

4. 生成慢日志测试

# 窗口2
mysql -uroot -p123456

select * from db1.t1 where id=18;

5. 查看窗口1:传到logstash的日志

# 由于1条慢日志是5行,传到logstash后,也是5个大括号的5条内容!!!
# 这不是我们想要的,我们需要处理一下!
[WARN ] 2022-01-01 22:22:52.656 [defaultEventExecutorGroup-4-1] plain - Relying on default value of `pipeline.ecs_compatibility`, which may change in a future major release of Logstash. To avoid unexpected changes when upgrading Logstash, please explicitly declare your desired ECS Compatibility mode.
#1.第一条
{
           "ecs" => {
        "version" => "1.12.0"
    },
          "host" => {
         "architecture" => "x86_64",
                   "id" => "b3856b2bce5c47ab962ede7e592b054c",
                   "ip" => [
            [ 0] "10.0.16.15",
            [ 1] "fe80::5054:ff:fe3c:125f",
            [ 2] "172.17.0.1",
            [ 3] "fe80::42:e8ff:fe51:6b3d",
            [ 4] "10.0.8.5",
            [ 5] "10.244.75.128",
            [ 6] "192.168.250.1",
            [ 7] "fe80::42:f0ff:fe96:2200",
            [ 8] "172.18.0.1",
            [ 9] "fe80::42:aeff:fe33:d359",
            [10] "fe80::40d0:ff:fe10:12eb",
            [11] "fe80::b82e:bdff:fe27:16c5",
            [12] "fe80::c9b:5ff:fe8b:48de",
            [13] "fe80::ecee:eeff:feee:eeee",
            [14] "fe80::ecee:eeff:feee:eeee",
            [15] "fe80::f47c:4aff:fee2:37c",
            [16] "fe80::ecee:eeff:feee:eeee",
            [17] "fe80::f409:3aff:fe34:31e5",
            [18] "fe80::ecee:eeff:feee:eeee",
            [19] "fe80::ecee:eeff:feee:eeee",
            [20] "fe80::a8c6:5ff:fe0a:f9c8",
            [21] "fe80::cc6b:23ff:fe9b:4ac2",
            [22] "fe80::706b:1aff:fe93:c09c",
            [23] "fe80::f8e0:abff:fec5:9520",
            [24] "fe80::da:89ff:fe5b:2491",
            [25] "fe80::642c:c8ff:fe3b:bedc",
            [26] "fe80::481d:16ff:fe20:266"
        ],
                   "os" => {
            "platform" => "ubuntu",
            "codename" => "focal",
              "kernel" => "5.4.0-90-generic",
                "name" => "Ubuntu",
                "type" => "linux",
              "family" => "debian",
             "version" => "20.04 LTS (Focal Fossa)"
        },
             "hostname" => "4c16g",
                 "name" => "4c16g",
        "containerized" => false,
                  "mac" => [
            [ 0] "52:54:00:3c:12:5f",
            [ 1] "02:42:e8:51:6b:3d",
            [ 2] "02:42:f0:96:22:00",
            [ 3] "02:42:ae:33:d3:59",
            [ 4] "42:d0:00:10:12:eb",
            [ 5] "ba:2e:bd:27:16:c5",
            [ 6] "0e:9b:05:8b:48:de",
            [ 7] "ee:ee:ee:ee:ee:ee",
            [ 8] "ee:ee:ee:ee:ee:ee",
            [ 9] "f6:7c:4a:e2:03:7c",
            [10] "ee:ee:ee:ee:ee:ee",
            [11] "f6:09:3a:34:31:e5",
            [12] "ee:ee:ee:ee:ee:ee",
            [13] "ee:ee:ee:ee:ee:ee",
            [14] "aa:c6:05:0a:f9:c8",
            [15] "ce:6b:23:9b:4a:c2",
            [16] "72:6b:1a:93:c0:9c",
            [17] "fa:e0:ab:c5:95:20",
            [18] "02:da:89:5b:24:91",
            [19] "66:2c:c8:3b:be:dc",
            [20] "4a:1d:16:20:02:66"
        ]
    },
           "log" => {
        "offset" => 684,
          "file" => {
            "path" => "/usr/local/mysql/data/web-slow.log"
        }
    },
         "input" => {
        "type" => "log"
    },
    "@timestamp" => 2022-01-01T14:22:51.598Z,
       "message" => "# User@Host: root[root] @ localhost []  Id:     4",
          "tags" => [
        [0] "beats_input_codec_plain_applied"
    ],
      "@version" => "1",
         "agent" => {
        "ephemeral_id" => "656b041d-4302-4e9a-89d8-774e9ee09a87",
                  "id" => "edca8f85-bc52-4365-b94b-bdf17eb8209f",
            "hostname" => "4c16g",
                "name" => "4c16g",
                "type" => "filebeat",
             "version" => "7.16.2"
    },
     "container" => {
        "id" => "web-slow.log"
    }
}
#2.第二条
{
           "ecs" => {
        "version" => "1.12.0"
    },
          "host" => {
         "architecture" => "x86_64",
                   "id" => "b3856b2bce5c47ab962ede7e592b054c",
                   "os" => {
            "platform" => "ubuntu",
            "codename" => "focal",
              "kernel" => "5.4.0-90-generic",
                "name" => "Ubuntu",
                "type" => "linux",
              "family" => "debian",
             "version" => "20.04 LTS (Focal Fossa)"
        },
                   "ip" => [
            [ 0] "10.0.16.15",
            [ 1] "fe80::5054:ff:fe3c:125f",
            [ 2] "172.17.0.1",
            [ 3] "fe80::42:e8ff:fe51:6b3d",
            [ 4] "10.0.8.5",
            [ 5] "10.244.75.128",
            [ 6] "192.168.250.1",
            [ 7] "fe80::42:f0ff:fe96:2200",
            [ 8] "172.18.0.1",
            [ 9] "fe80::42:aeff:fe33:d359",
            [10] "fe80::40d0:ff:fe10:12eb",
            [11] "fe80::b82e:bdff:fe27:16c5",
            [12] "fe80::c9b:5ff:fe8b:48de",
            [13] "fe80::ecee:eeff:feee:eeee",
            [14] "fe80::ecee:eeff:feee:eeee",
            [15] "fe80::f47c:4aff:fee2:37c",
            [16] "fe80::ecee:eeff:feee:eeee",
            [17] "fe80::f409:3aff:fe34:31e5",
            [18] "fe80::ecee:eeff:feee:eeee",
            [19] "fe80::ecee:eeff:feee:eeee",
            [20] "fe80::a8c6:5ff:fe0a:f9c8",
            [21] "fe80::cc6b:23ff:fe9b:4ac2",
            [22] "fe80::706b:1aff:fe93:c09c",
            [23] "fe80::f8e0:abff:fec5:9520",
            [24] "fe80::da:89ff:fe5b:2491",
            [25] "fe80::642c:c8ff:fe3b:bedc",
            [26] "fe80::481d:16ff:fe20:266"
        ],
             "hostname" => "4c16g",
                 "name" => "4c16g",
        "containerized" => false,
                  "mac" => [
            [ 0] "52:54:00:3c:12:5f",
            [ 1] "02:42:e8:51:6b:3d",
            [ 2] "02:42:f0:96:22:00",
            [ 3] "02:42:ae:33:d3:59",
            [ 4] "42:d0:00:10:12:eb",
            [ 5] "ba:2e:bd:27:16:c5",
            [ 6] "0e:9b:05:8b:48:de",
            [ 7] "ee:ee:ee:ee:ee:ee",
            [ 8] "ee:ee:ee:ee:ee:ee",
            [ 9] "f6:7c:4a:e2:03:7c",
            [10] "ee:ee:ee:ee:ee:ee",
            [11] "f6:09:3a:34:31:e5",
            [12] "ee:ee:ee:ee:ee:ee",
            [13] "ee:ee:ee:ee:ee:ee",
            [14] "aa:c6:05:0a:f9:c8",
            [15] "ce:6b:23:9b:4a:c2",
            [16] "72:6b:1a:93:c0:9c",
            [17] "fa:e0:ab:c5:95:20",
            [18] "02:da:89:5b:24:91",
            [19] "66:2c:c8:3b:be:dc",
            [20] "4a:1d:16:20:02:66"
        ]
    },
           "log" => {
        "offset" => 648,
          "file" => {
            "path" => "/usr/local/mysql/data/web-slow.log"
        }
    },
         "input" => {
        "type" => "log"
    },
    "@timestamp" => 2022-01-01T14:22:51.598Z,
       "message" => "# Time: 2022-01-01T14:22:46.474341Z",
          "tags" => [
        [0] "beats_input_codec_plain_applied"
    ],
      "@version" => "1",
         "agent" => {
             "version" => "7.16.2",
                  "id" => "edca8f85-bc52-4365-b94b-bdf17eb8209f",
            "hostname" => "4c16g",
                "name" => "4c16g",
                "type" => "filebeat",
        "ephemeral_id" => "656b041d-4302-4e9a-89d8-774e9ee09a87"
    },
     "container" => {
        "id" => "web-slow.log"
    }
}
#3.第三条
{
           "ecs" => {
        "version" => "1.12.0"
    },
           "log" => {
        "offset" => 843,
          "file" => {
            "path" => "/usr/local/mysql/data/web-slow.log"
        }
    },
          "host" => {
         "architecture" => "x86_64",
                   "id" => "b3856b2bce5c47ab962ede7e592b054c",
                   "ip" => [
            [ 0] "10.0.16.15",
            [ 1] "fe80::5054:ff:fe3c:125f",
            [ 2] "172.17.0.1",
            [ 3] "fe80::42:e8ff:fe51:6b3d",
            [ 4] "10.0.8.5",
            [ 5] "10.244.75.128",
            [ 6] "192.168.250.1",
            [ 7] "fe80::42:f0ff:fe96:2200",
            [ 8] "172.18.0.1",
            [ 9] "fe80::42:aeff:fe33:d359",
            [10] "fe80::40d0:ff:fe10:12eb",
            [11] "fe80::b82e:bdff:fe27:16c5",
            [12] "fe80::c9b:5ff:fe8b:48de",
            [13] "fe80::ecee:eeff:feee:eeee",
            [14] "fe80::ecee:eeff:feee:eeee",
            [15] "fe80::f47c:4aff:fee2:37c",
            [16] "fe80::ecee:eeff:feee:eeee",
            [17] "fe80::f409:3aff:fe34:31e5",
            [18] "fe80::ecee:eeff:feee:eeee",
            [19] "fe80::ecee:eeff:feee:eeee",
            [20] "fe80::a8c6:5ff:fe0a:f9c8",
            [21] "fe80::cc6b:23ff:fe9b:4ac2",
            [22] "fe80::706b:1aff:fe93:c09c",
            [23] "fe80::f8e0:abff:fec5:9520",
            [24] "fe80::da:89ff:fe5b:2491",
            [25] "fe80::642c:c8ff:fe3b:bedc",
            [26] "fe80::481d:16ff:fe20:266"
        ],
                   "os" => {
            "platform" => "ubuntu",
            "codename" => "focal",
              "kernel" => "5.4.0-90-generic",
                "name" => "Ubuntu",
                "type" => "linux",
              "family" => "debian",
             "version" => "20.04 LTS (Focal Fossa)"
        },
                 "name" => "4c16g",
        "containerized" => false,
             "hostname" => "4c16g",
                  "mac" => [
            [ 0] "52:54:00:3c:12:5f",
            [ 1] "02:42:e8:51:6b:3d",
            [ 2] "02:42:f0:96:22:00",
            [ 3] "02:42:ae:33:d3:59",
            [ 4] "42:d0:00:10:12:eb",
            [ 5] "ba:2e:bd:27:16:c5",
            [ 6] "0e:9b:05:8b:48:de",
            [ 7] "ee:ee:ee:ee:ee:ee",
            [ 8] "ee:ee:ee:ee:ee:ee",
            [ 9] "f6:7c:4a:e2:03:7c",
            [10] "ee:ee:ee:ee:ee:ee",
            [11] "f6:09:3a:34:31:e5",
            [12] "ee:ee:ee:ee:ee:ee",
            [13] "ee:ee:ee:ee:ee:ee",
            [14] "aa:c6:05:0a:f9:c8",
            [15] "ce:6b:23:9b:4a:c2",
            [16] "72:6b:1a:93:c0:9c",
            [17] "fa:e0:ab:c5:95:20",
            [18] "02:da:89:5b:24:91",
            [19] "66:2c:c8:3b:be:dc",
            [20] "4a:1d:16:20:02:66"
        ]
    },
         "input" => {
        "type" => "log"
    },
    "@timestamp" => 2022-01-01T14:22:51.598Z,
       "message" => "select * from db1.t1 where id=18;",
          "tags" => [
        [0] "beats_input_codec_plain_applied"
    ],
      "@version" => "1",
         "agent" => {
             "version" => "7.16.2",
                  "id" => "edca8f85-bc52-4365-b94b-bdf17eb8209f",
            "hostname" => "4c16g",
                "name" => "4c16g",
                "type" => "filebeat",
        "ephemeral_id" => "656b041d-4302-4e9a-89d8-774e9ee09a87"
    },
     "container" => {
        "id" => "web-slow.log"
    }
}
#4.第四条
{
           "ecs" => {
        "version" => "1.12.0"
    },
          "host" => {
         "architecture" => "x86_64",
                   "id" => "b3856b2bce5c47ab962ede7e592b054c",
                   "os" => {
            "platform" => "ubuntu",
            "codename" => "focal",
              "kernel" => "5.4.0-90-generic",
                "name" => "Ubuntu",
                "type" => "linux",
              "family" => "debian",
             "version" => "20.04 LTS (Focal Fossa)"
        },
                   "ip" => [
            [ 0] "10.0.16.15",
            [ 1] "fe80::5054:ff:fe3c:125f",
            [ 2] "172.17.0.1",
            [ 3] "fe80::42:e8ff:fe51:6b3d",
            [ 4] "10.0.8.5",
            [ 5] "10.244.75.128",
            [ 6] "192.168.250.1",
            [ 7] "fe80::42:f0ff:fe96:2200",
            [ 8] "172.18.0.1",
            [ 9] "fe80::42:aeff:fe33:d359",
            [10] "fe80::40d0:ff:fe10:12eb",
            [11] "fe80::b82e:bdff:fe27:16c5",
            [12] "fe80::c9b:5ff:fe8b:48de",
            [13] "fe80::ecee:eeff:feee:eeee",
            [14] "fe80::ecee:eeff:feee:eeee",
            [15] "fe80::f47c:4aff:fee2:37c",
            [16] "fe80::ecee:eeff:feee:eeee",
            [17] "fe80::f409:3aff:fe34:31e5",
            [18] "fe80::ecee:eeff:feee:eeee",
            [19] "fe80::ecee:eeff:feee:eeee",
            [20] "fe80::a8c6:5ff:fe0a:f9c8",
            [21] "fe80::cc6b:23ff:fe9b:4ac2",
            [22] "fe80::706b:1aff:fe93:c09c",
            [23] "fe80::f8e0:abff:fec5:9520",
            [24] "fe80::da:89ff:fe5b:2491",
            [25] "fe80::642c:c8ff:fe3b:bedc",
            [26] "fe80::481d:16ff:fe20:266"
        ],
                 "name" => "4c16g",
        "containerized" => false,
             "hostname" => "4c16g",
                  "mac" => [
            [ 0] "52:54:00:3c:12:5f",
            [ 1] "02:42:e8:51:6b:3d",
            [ 2] "02:42:f0:96:22:00",
            [ 3] "02:42:ae:33:d3:59",
            [ 4] "42:d0:00:10:12:eb",
            [ 5] "ba:2e:bd:27:16:c5",
            [ 6] "0e:9b:05:8b:48:de",
            [ 7] "ee:ee:ee:ee:ee:ee",
            [ 8] "ee:ee:ee:ee:ee:ee",
            [ 9] "f6:7c:4a:e2:03:7c",
            [10] "ee:ee:ee:ee:ee:ee",
            [11] "f6:09:3a:34:31:e5",
            [12] "ee:ee:ee:ee:ee:ee",
            [13] "ee:ee:ee:ee:ee:ee",
            [14] "aa:c6:05:0a:f9:c8",
            [15] "ce:6b:23:9b:4a:c2",
            [16] "72:6b:1a:93:c0:9c",
            [17] "fa:e0:ab:c5:95:20",
            [18] "02:da:89:5b:24:91",
            [19] "66:2c:c8:3b:be:dc",
            [20] "4a:1d:16:20:02:66"
        ]
    },
           "log" => {
        "offset" => 817,
          "file" => {
            "path" => "/usr/local/mysql/data/web-slow.log"
        }
    },
    "@timestamp" => 2022-01-01T14:22:51.598Z,
       "message" => "SET timestamp=1641046966;",
         "input" => {
        "type" => "log"
    },
          "tags" => [
        [0] "beats_input_codec_plain_applied"
    ],
      "@version" => "1",
         "agent" => {
             "version" => "7.16.2",
                  "id" => "edca8f85-bc52-4365-b94b-bdf17eb8209f",
            "hostname" => "4c16g",
                "name" => "4c16g",
                "type" => "filebeat",
        "ephemeral_id" => "656b041d-4302-4e9a-89d8-774e9ee09a87"
    },
     "container" => {
        "id" => "web-slow.log"
    }
}
#5.第五条
{
           "ecs" => {
        "version" => "1.12.0"
    },
          "host" => {
         "architecture" => "x86_64",
                   "id" => "b3856b2bce5c47ab962ede7e592b054c",
                   "ip" => [
            [ 0] "10.0.16.15",
            [ 1] "fe80::5054:ff:fe3c:125f",
            [ 2] "172.17.0.1",
            [ 3] "fe80::42:e8ff:fe51:6b3d",
            [ 4] "10.0.8.5",
            [ 5] "10.244.75.128",
            [ 6] "192.168.250.1",
            [ 7] "fe80::42:f0ff:fe96:2200",
            [ 8] "172.18.0.1",
            [ 9] "fe80::42:aeff:fe33:d359",
            [10] "fe80::40d0:ff:fe10:12eb",
            [11] "fe80::b82e:bdff:fe27:16c5",
            [12] "fe80::c9b:5ff:fe8b:48de",
            [13] "fe80::ecee:eeff:feee:eeee",
            [14] "fe80::ecee:eeff:feee:eeee",
            [15] "fe80::f47c:4aff:fee2:37c",
            [16] "fe80::ecee:eeff:feee:eeee",
            [17] "fe80::f409:3aff:fe34:31e5",
            [18] "fe80::ecee:eeff:feee:eeee",
            [19] "fe80::ecee:eeff:feee:eeee",
            [20] "fe80::a8c6:5ff:fe0a:f9c8",
            [21] "fe80::cc6b:23ff:fe9b:4ac2",
            [22] "fe80::706b:1aff:fe93:c09c",
            [23] "fe80::f8e0:abff:fec5:9520",
            [24] "fe80::da:89ff:fe5b:2491",
            [25] "fe80::642c:c8ff:fe3b:bedc",
            [26] "fe80::481d:16ff:fe20:266"
        ],
                   "os" => {
            "platform" => "ubuntu",
            "codename" => "focal",
              "kernel" => "5.4.0-90-generic",
                "name" => "Ubuntu",
                "type" => "linux",
              "family" => "debian",
             "version" => "20.04 LTS (Focal Fossa)"
        },
             "hostname" => "4c16g",
                 "name" => "4c16g",
        "containerized" => false,
                  "mac" => [
            [ 0] "52:54:00:3c:12:5f",
            [ 1] "02:42:e8:51:6b:3d",
            [ 2] "02:42:f0:96:22:00",
            [ 3] "02:42:ae:33:d3:59",
            [ 4] "42:d0:00:10:12:eb",
            [ 5] "ba:2e:bd:27:16:c5",
            [ 6] "0e:9b:05:8b:48:de",
            [ 7] "ee:ee:ee:ee:ee:ee",
            [ 8] "ee:ee:ee:ee:ee:ee",
            [ 9] "f6:7c:4a:e2:03:7c",
            [10] "ee:ee:ee:ee:ee:ee",
            [11] "f6:09:3a:34:31:e5",
            [12] "ee:ee:ee:ee:ee:ee",
            [13] "ee:ee:ee:ee:ee:ee",
            [14] "aa:c6:05:0a:f9:c8",
            [15] "ce:6b:23:9b:4a:c2",
            [16] "72:6b:1a:93:c0:9c",
            [17] "fa:e0:ab:c5:95:20",
            [18] "02:da:89:5b:24:91",
            [19] "66:2c:c8:3b:be:dc",
            [20] "4a:1d:16:20:02:66"
        ]
    },
           "log" => {
        "offset" => 734,
          "file" => {
            "path" => "/usr/local/mysql/data/web-slow.log"
        }
    },
    "@timestamp" => 2022-01-01T14:22:51.598Z,
       "message" => "# Query_time: 10.757798  Lock_time: 0.000189 Rows_sent: 1  Rows_examined: 19999999",
         "input" => {
        "type" => "log"
    },
          "tags" => [
        [0] "beats_input_codec_plain_applied"
    ],
      "@version" => "1",
         "agent" => {
             "version" => "7.16.2",
                  "id" => "edca8f85-bc52-4365-b94b-bdf17eb8209f",
            "hostname" => "4c16g",
                "name" => "4c16g",
                "type" => "filebeat",
        "ephemeral_id" => "656b041d-4302-4e9a-89d8-774e9ee09a87"
    },
     "container" => {
        "id" => "web-slow.log"
    }
}

最后更新: 2022-02-19 13:05:46