Hi ,
I using filebeat to send mysql data to elasticsearch, but i notice there are no result for mysql.error data in elasticsearch even though mysql.slowlog data are coming correctly. I check the data using kibana discover
i'm using filebeat 7.4 and elasticsearch 7.4
filebeat module conf:
# cat /etc/filebeat/modules.d/mysql.yml
# Module: mysql
# Docs: https://www.elastic.co/guide/en/beats/filebeat/7.4/filebeat-module-mysql.html
- module: mysql
# Error logs
error:
enabled: true
# Set custom paths for the log files. If left empty,
# Filebeat will choose the paths depending on your OS.
var.paths: ["/var/log/mariadb/mariadb.log"]
# Slow logs
slowlog:
enabled: true
# Set custom paths for the log files. If left empty,
# Filebeat will choose the paths depending on your OS.
var.paths: ["/var/log/mariadb/mariadb-slow.log"]
I have check filebeat stdout using command: filebeat -e -d "*" ... no error found. The stdout show that the mysql.error and mysql.slowlog data has been posted to elasticsearch. But still there is nothing from elasticsearch. The only data appear in elasticsearch is mysql.slowlog.
filebeat -e -d "*" output:
2019-10-14T02:19:23.785+0800 DEBUG [modules] fileset/pipelines.go:67 Required processors: []
2019-10-14T02:19:23.785+0800 DEBUG [elasticsearch] elasticsearch/client.go:761 GET http://localhost:9200/_ingest/pipeline/filebeat-7.4.0-mysql-error-pipeline <nil>
2019-10-14T02:19:23.786+0800 DEBUG [modules] fileset/pipelines.go:120 Pipeline filebeat-7.4.0-mysql-error-pipeline already loaded
2019-10-14T02:19:23.786+0800 DEBUG [modules] fileset/pipelines.go:67 Required processors: []
2019-10-14T02:19:23.786+0800 DEBUG [elasticsearch] elasticsearch/client.go:761 GET http://localhost:9200/_ingest/pipeline/filebeat-7.4.0-mysql-slowlog-pipeline <nil>
2019-10-14T02:19:23.788+0800 DEBUG [modules] fileset/pipelines.go:120 Pipeline filebeat-7.4.0-mysql-slowlog-pipeline already loaded
2019-10-14T02:19:23.788+0800 INFO input/input.go:114 Starting input of type: log; ID: 1124530994825458434
2019-10-14T02:19:23.788+0800 INFO input/input.go:114 Starting input of type: log; ID: 7616194281951100578
2019-10-14T02:19:23.788+0800 DEBUG [input] log/input.go:191 Start next scan
2019-10-14T02:19:23.788+0800 DEBUG [input] log/input.go:191 Start next scan
2019-10-14T02:19:23.789+0800 DEBUG [input] log/input.go:421 Check file for harvesting: /var/log/mariadb/mariadb.log
2019-10-14T02:19:23.789+0800 DEBUG [input] log/input.go:511 Update existing file for harvesting: /var/log/mariadb/mariadb.log, offset: 411223
2019-10-14T02:19:23.789+0800 DEBUG [input] log/input.go:520 Resuming harvesting of file: /var/log/mariadb/mariadb.log, offset: 411223, new size: 412093
2019-10-14T02:19:23.789+0800 DEBUG [input] log/input.go:421 Check file for harvesting: /var/log/mariadb/mariadb-slow.log
2019-10-14T02:19:23.789+0800 DEBUG [input] log/input.go:511 Update existing file for harvesting: /var/log/mariadb/mariadb-slow.log, offset: 4600
2019-10-14T02:19:23.789+0800 DEBUG [input] log/input.go:520 Resuming harvesting of file: /var/log/mariadb/mariadb-slow.log, offset: 4600, new size: 4861
2019-10-14T02:19:23.789+0800 DEBUG [harvester] log/harvester.go:489 Set previous offset for file: /var/log/mariadb/mariadb.log. Offset: 411223
2019-10-14T02:19:23.789+0800 DEBUG [harvester] log/harvester.go:480 Setting offset for file: /var/log/mariadb/mariadb.log. Offset: 411223
2019-10-14T02:19:23.789+0800 DEBUG [harvester] log/harvester.go:182 Harvester setup successful. Line terminator: 1
2019-10-14T02:19:23.790+0800 DEBUG [publisher] pipeline/client.go:220 Pipeline client receives callback 'onFilteredOut' for event: {Timestamp:0001-01-01 00:00:00 +0000 UTC Meta:null Fields:null Private:{Id: Finished:false Fileinfo:0xc0007564e0 Source:/var/log/mariadb/mariadb.log Offset:411223 Timestamp:2019-10-14 02:19:23.78908156 +0800 +08 m=+3.366934399 TTL:-1ns Type:log Meta:map[] FileStateOS:415273452-64768} TimeSeries:false}
i also tried to post the mysql.error data manually using curl to elasticsearch, found out that the data coming out correctly
# curl -u elastic:elasticpwd -H 'Content-Type: application/json' -X POST "localhost:9200/filebeat-7.4.0-2019.10.11-000001/_doc/" -d '{"@timestamp":"2019-10-13T00:18:35.510Z","@metadata":{"beat":"filebeat","type":"_doc","version":"7.4.0","pipeline":"filebeat-7.4.0-mysql-error-pipeline"},"input":{"type":"log"},"message":"191013 8:18:32\t 8 Query\tSHOW /*!50002 GLOBAL */ STATUS","event":{"dataset":"mysql.error","module":"mysql"},"fileset":{"name":"error"},"agent":{"type":"filebeat","ephemeral_id":"16835ee0-9321-476a-802b-c61f0640c370","hostname":"nttdisbdb02","id":"97520869-259a-4d60-bb46-5eecf412f37d","version":"7.4.0"},"ecs":{"version":"1.1.0"},"log":{"file":{"path":"/var/log/mariadb/mariadb.log"},"offset":11778},"service":{"type":"mysql"},"host":{"id":"9d4ba8aa77aa49ccba4f96e8d89eb7c3","name":"nttdisbdb02","containerized":false,"hostname":"nttdisbdb02","architecture":"x86_64","os":{"kernel":"3.10.0-862.3.2.el7.x86_64","codename":"Core","platform":"centos","version":"7 (Core)","family":"redhat","name":"CentOS Linux"}}}'
{"_index":"filebeat-7.4.0-2019.10.11-000001","_type":"_doc","_id":"BDTTyW0BJGi3-aJ4nxKF","_version":1,"result":"created","_shards":{"total":2,"successful":1,"failed":0},"_seq_no":7348502,"_primary_term":1}
i check the mysql.error data in elasticsearch the data came out normally as expected.
# curl -u elastic:elasticpwd -X GET "nttdisblb01:9200/filebeat-7.4.0-2019.10.11-000001/_source/BDTTyW0BJGi3-aJ4nxKF"
{"@timestamp":"2019-10-13T00:18:35.510Z","@metadata":{"beat":"filebeat","type":"_doc","version":"7.4.0","pipeline":"filebeat-7.4.0-mysql-error-pipeline"},"input":{"type":"log"},"message":"191013 8:18:32\t 8 Query\tSHOW /*!50002 GLOBAL */ STATUS","event":{"dataset":"mysql.error","module":"mysql"},"fileset":{"name":"error"},"agent":{"type":"filebeat","ephemeral_id":"16835ee0-9321-476a-802b-c61f0640c370","hostname":"nttdisbdb02","id":"97520869-259a-4d60-bb46-5eecf412f37d","version":"7.4.0"},"ecs":{"version":"1.1.0"},"log":{"file":{"path":"/var/log/mariadb/mariadb.log"},"offset":11778},"service":{"type":"mysql"},"host":{"id":"9d4ba8aa77aa49ccba4f96e8d89eb7c3","name":"nttdisbdb02","containerized":false,"hostname":"nttdisbdb02","architecture":"x86_64","os":{"kernel":"3.10.0-862.3.2.el7.x86_64","codename":"Core","platform":"centos","version":"7 (Core)","family":"redhat","name":"CentOS Linux"}}}
