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