No data MySQL dashboard - version mismatch?

Elastic Stack Beats
1

Hello,

Somehow we ended up with a slightly newer version of metricbeats vs everything else. We are running on Centos 7, version 6.3.1 while metricbeats is 6.3.2, we do see some data such as system metrics but after configuring MySQL dashboard and checking and rechecking the configuration and scouring forums for others with similar issues we have found anything, the MySQL dashboard has no data at all.

There are no errors in any logs we have found and we have tried monitoring multiple mysql servers for data, we've verified connectivity, permissions etc.

We're guessing the version mismatch is our issue. Also wondering if we can add multiple hosts line in the MySQL.yml file which is below.

Any advice would be appreciated on a safe way to remove metricbeats version we have and install the correct matching version without breaking others things. We recently upgraded to the version we are on, 6.3.1 and nearly broke our install and had to start over. All components are on the same server.

Thanks in advance.

metricbeat.modules:

  • module: mysql
    metricsets: ["status"]
    period: 10s

    Host DSN should be defined as "user:pass@tcp(127.0.0.1:3306)/"

    The username and password can either be set in the DSN or using the username

    and password config options. Those specified in the DSN take precedence.

    hosts: ["mysqluser:xxxxxx(a.b.c.d:3306)/"]

    Username of hosts. Empty by default.

    #username: root

    Password of hosts. Empty by default.

    #password: secret

    By setting raw to true, all raw fields from the status metricset will be added to the event.

    raw: false

2

Clarification question: what software is "version 6.3.1" referring to?

Not multiple lines per se but you could add multiple elements to the array like this:

hosts: ["mysqluser:xxxxxx(a.b.c.d:3306)/", "mysqluser:xxxxxx(b.c.d.e:3306)/"]

Alternatively, this should work too:

hosts:
  - "mysqluser:xxxxxx(a.b.c.d:3306)/"
  - "mysqluser:xxxxxx(b.c.d.e:3306)/"
3

ok great thanks for the response - we are running 6.3.1 of elasticsearch, kibana, logstash, filebeat

4

Thanks. So it sounds like you want to downgrade metricbeat to 6.3.1 as well, from 6.3.2.

What was the command you used to install metricbeat 6.3.2 on your centos 7 machine(s)?

5

i believe it was a yum install, i of course can just remove it with rpm and install the matching version, but there were various other steps we followed including enabling modules, configuriing the kibana connecction, elastic connection, setting up the templates, etc we wanted to check before just ripping out the existing and installing 6.3.1, maybe that's all there is to it but wanted to ask especially due to the issues we had upgrading the stack earlier and the fact that mysql data isn't showing up.

Thanks for the help.

6

enabling modules

You'll need to re-enable the same ones in the new install. I'd suggest running metricbeat modules list with your current install to get a list of which modules are enabled and which ones are disabled so you can replicate the same setup later with the downgraded metricbeat.

You might also want to back up /etc/metricbeat/modules.d/, particularly the files for modules that are enabled. You should be able to just drop these in to the same place after you've downgraded. Reading through the release notes for Metricbeat 6.3.2, I don't expect any issues with using 6.3.2 module config files with 6.3.1.

You'll need to configure these again in /etc/metricbeat/metricbeat.yml. As with the module configuration files above, I'd suggest backing up your 6.3.2 metricbeat.yml. After downgrading to 6.3.1, I'd edit the downgraded /etc/metricbeat/metricbeat.yml and paste back in the bits you changed for configuring the kibana and elasticsearch connections in 6.3.2.

I just checked and the templates are created for specific versions (down to the patch level). So you'll need to set these up again for 6.3.1. In general, I'd just run metricbeat setup after downgrading and setting up the kibana and elasticsearch connections.

Hope that helps,

Shaunak

7

ok great I've done all that and metricbeat appears to work and is now the same version as the rest of the stack.

I have data in hosts overview (from localhost) but still no data in the mysql dashboard despite the configuration I have set, I tried with two hosts in the format you suggested as well as just one with the original syntax I posted of the mysql.yml file.

So my question now is how to troubleshoot this and determine where the problem is - I have verified I can connect to the hosts in question using the mysql client with the credentials I've specified.

Much appreciated

8

In your Kibana > Dev Tools > Console application, could you run the following query and post the results here?

GET metricbeat-*/_search?q=metricset.module:mysql&size=1

This might give us some idea of what documents the Metricbeat mysql module is indexing into Elasticsearch and possibly provide us some hints as to why you are not seeing data on the MySQL dashboard.

9

ok great, here are the results:

{
"took": 26,
"timed_out": false,
"_shards": {
"total": 22,
"successful": 22,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 0,
"max_score": null,
"hits": []
}
}

10

Alright, as a next step for debugging could you try the following on the host where you are running metricbeat:

  1. Run metricbeat modules list and post the output here.
  2. Run metricbeat -e -d 'publish,module' for < 5 seconds and post the output here (it might be large).
11

here's metricbeats modules list:

Enabled:
mysql
system

Disabled:
aerospike
apache
ceph
couchbase
docker
dropwizard
elasticsearch
etcd
golang
graphite
haproxy
http
jolokia
kafka
kibana
kubernetes
kvm
logstash
memcached
mongodb
munin
mysql
nginx
php_fpm
postgresql
prometheus
rabbitmq
redis
uwsgi
vsphere
windows
zookeeper

12

the next command has server specific data which I would need to remove before I would want to post it, at a glance it's mostly the server's hostname, is there anything else I should be concerned about? any credentials or other system specific data embedded in this output?

thanks

13

could I send this to you as a private message?

thanks

14

I'd prefer if you could post it publicly so someone else might be able to jump in and help too. Looking through output on my end, hostnames/ports are the only things to mask from what I can tell. You might want to make sure you look for those in any error messages too.

Really the parts I'm most interested in are any log messages with [module] and mysql in them as well as the multiline log messages with Publish event: in them. Oh and also any log messages with errors or warnings, of course. If you can, you might want to temporarily disable the system module while we debug, using metricbeat modules disable system.

15

ok thanks interestingly enough mysql does not show in the output anywhere -

metricbeat -e -d 'publish,module'

2018-08-31T12:51:57.544-0700 INFO instance/beat.go:492 Home path: [/usr/share/metricbeat] Config path: [/etc/metricbeat] Data path: [/var/lib/metricbeat] Logs path: [/var/log/metricbeat]
2018-08-31T12:51:57.544-0700 INFO instance/beat.go:499 Beat UUID: ead199a8-27ea-4bf3-8f83-e6a1cfddd12d
2018-08-31T12:51:57.544-0700 INFO [beat] instance/beat.go:716 Beat info {"system_info": {"beat": {"path": {"config": "/etc/metricbeat", "data": "/var/lib/metricbeat", "home": "/usr/share/metricbeat", "logs": "/var/log/metricbeat"}, "type": "metricbeat", "uuid": "ead199a8-27ea-4bf3-8f83-e6a1cfddd12d"}}}
2018-08-31T12:51:57.544-0700 INFO [beat] instance/beat.go:725 Build info {"system_info": {"build": {"commit": "ed42bb85e72ae58cc09748dc1825159713e0ffd4", "libbeat": "6.3.1", "time": "2018-06-29T21:14:09.000Z", "version": "6.3.1"}}}
2018-08-31T12:51:57.544-0700 INFO [beat] instance/beat.go:728 Go runtime info {"system_info": {"go": {"os":"linux","arch":"amd64","max_procs":8,"version":"go1.9.4"}}}
2018-08-31T12:51:57.545-0700 INFO [beat] instance/beat.go:732 Host info {"system_info": {"host": {"architecture":"x86_64","boot_time":"2018-08-11T13:04:45-07:00","containerized":true,"hostname":"xy.xyz.ext","ips":["127.0.0.1/8","a.b.c.d"],"kernel_version":"3.10.0-514.26.2.el7.x86_64","mac_addresses":["aa:bb:cc:dd:ee:ff"],"os":{"family":"redhat","platform":"centos","name":"CentOS Linux","version":"7 (Core)","major":7,"minor":3,"patch":1611,"codename":"Core"},"timezone":"PDT","timezone_offset_sec":-25200,"id":"..."}}}
2018-08-31T12:51:57.546-0700 INFO [beat] instance/beat.go:761 Process info {"system_info": {"process": {"capabilities": {"inheritable":null,"permitted":["chown","dac_override","dac_read_search","fowner","fsetid","kill","setgid","setuid","setpcap","linux_immutable","net_bind_service","net_broadcast","net_admin","net_raw","ipc_lock","ipc_owner","sys_module","sys_rawio","sys_chroot","sys_ptrace","sys_pacct","sys_admin","sys_boot","sys_nice","sys_resource","sys_time","sys_tty_config","mknod","lease","audit_write","audit_control","setfcap","mac_override","mac_admin","syslog","wake_alarm","block_suspend"],"effective":["chown","dac_override","dac_read_search","fowner","fsetid","kill","setgid","setuid","setpcap","linux_immutable","net_bind_service","net_broadcast","net_admin","net_raw","ipc_lock","ipc_owner","sys_module","sys_rawio","sys_chroot","sys_ptrace","sys_pacct","sys_admin","sys_boot","sys_nice","sys_resource","sys_time","sys_tty_config","mknod","lease","audit_write","audit_control","setfcap","mac_override","mac_admin","syslog","wake_alarm","block_suspend"],"bounding":["chown","dac_override","dac_read_search","fowner","fsetid","kill","setgid","setuid","setpcap","linux_immutable","net_bind_service","net_broadcast","net_admin","net_raw","ipc_lock","ipc_owner","sys_module","sys_rawio","sys_chroot","sys_ptrace","sys_pacct","sys_admin","sys_boot","sys_nice","sys_resource","sys_time","sys_tty_config","mknod","lease","audit_write","audit_control","setfcap","mac_override","mac_admin","syslog","wake_alarm","block_suspend"],"ambient":null}, "cwd": "/root", "exe": "/usr/share/metricbeat/bin/metricbeat", "name": "metricbeat", "pid": 18278, "ppid": 17020, "seccomp": {"mode":"disabled"}, "start_time": "2018-08-31T12:51:56.860-0700"}}}
2018-08-31T12:51:57.546-0700 INFO instance/beat.go:225 Setup Beat: metricbeat; Version: 6.3.1
2018-08-31T12:51:57.546-0700 INFO elasticsearch/client.go:145 Elasticsearch url: http://localhost:9200
2018-08-31T12:51:57.547-0700 DEBUG [publish] pipeline/consumer.go:120start pipeline event consumer
2018-08-31T12:51:57.547-0700 INFO pipeline/module.go:81 Beat name: xy.xyz.ext
2018-08-31T12:51:57.547-0700 INFO [monitoring] log/log.go:97 Starting metrics logging every 30s
2018-08-31T12:51:57.547-0700 INFO instance/beat.go:315 metricbeat start running.
2018-08-31T12:51:57.548-0700 INFO cfgfile/reload.go:122 Config reloader started
2018-08-31T12:51:57.548-0700 INFO cfgfile/reload.go:214 Loading of config files completed.
^C2018-08-31T12:52:07.834-0700 INFO cfgfile/reload.go:217 Dynamic config reloader stopped
2018-08-31T12:52:07.838-0700 INFO [monitoring] log/log.go:132 Total non-zero metrics {"monitoring": {"metrics": {"beat":{"cpu":{"system":{"ticks":60,"time":{"ms":69}},"total":{"ticks":100,"time":{"ms":111},"value":100},"user":{"ticks":40,"time":{"ms":42}}},"info":{"ephemeral_id":"150b2ec6-7f4b--ba52-eec2361c549c","uptime":{"ms":10349}},"memstats":{"gc_next":4194304,"memory_alloc":2699224,"memory_total":4227024,"rss":20025344}},"libbeat":{"config":{"module":{"running":0},"reloads":1},"output":{"type":"elasticsearch"},"pipeline":{"clients":0,"events":{"active":0}}},"system":{"cpu":{"cores":8},"load":{"1":0.68,"15":0.33,"5":0.35,"norm":{"1":0.085,"15":0.0413,"5":0.0438}}}}}}
2018-08-31T12:52:07.839-0700 INFO [monitoring] log/log.go:133 Uptime: 10.353973604s
2018-08-31T12:52:07.839-0700 INFO [monitoring] log/log.go:110 Stopping metrics logging.
2018-08-31T12:52:07.839-0700 INFO instance/beat.go:321 metricbeat stopped.

16

Hmmm, that's odd indeed! Let's add a couple more debug selectors and see what we get:

./metricbeat -e -d 'cfgfile,reload,publish,module'
17

Ok here you go:

metricbeat -e -d 'cfgfile,reload,publish,module'

2018-08-31T13:11:55.534-0700 INFO instance/beat.go:492 Home path: [/usr/share/metricbeat] Config path: [/etc/metricbeat] Data path: [/var/lib/metricbeat] Logs path: [/var/log/metricbeat]
2018-08-31T13:11:55.534-0700 INFO instance/beat.go:499 Beat UUID: ead199a8-27ea-4bf3-8f83-e6a1c
2018-08-31T13:11:55.534-0700 INFO [beat] instance/beat.go:716 Beat info {"system_info": {"beat": {"path": {"config": "/etc/metricbeat", "data": "/var/lib/metricbeat", "home": "/usr/share/metricbeat", "logs": "/var/log/metricbeat"}, "type": "metricbeat", "uuid": "ead199a8-27ea-4bf3-8f83-e6a1cfddd12d"}}}
2018-08-31T13:11:55.534-0700 INFO [beat] instance/beat.go:725 Build info{"system_info": {"build": {"commit": "ed42bb85e72ae58cc0974825159713e0ffd4", "libbeat": "6.3.1", "time": "2018-06-29T21:14:09.000Z", "version": "6.3.1"}}}
2018-08-31T13:11:55.534-0700 INFO [beat] instance/beat.go:728 Go runtime info {"system_info": {"go": {"os":"linux","arch":"amd64","max_procs":8,"version":"go1.9.4"}}}
2018-08-31T13:11:55.536-0700 INFO [beat] instance/beat.go:732 Host info {"system_info": {"host": {"architecture":"x86_64","boot_time":"2018-08-11T13:04:45-07:00","containerized":true,"hostname":"x.y.ext","ips":["127.0.0.1/8","a.b.c.d"],"kernel_version":"3.10.0-514.26.2.el7.x86_64","mac_addresses":["00"],"os":{"family":"redhat","platform":"centos","name":"CentOS Linux","version":"7 (Core)","major":7,"minor":3,"patch":1611,"codename":"Core"},"timezone":"PDT","timezone_offset_sec":-25200,"id":"182f7d6abe77ba267aeb"}}}
2018-08-31T13:11:55.536-0700 INFO [beat] instance/beat.go:761 Process info {"system_info": {"process": {"capabilities": {"inheritable":null,"permitted":["chown","dac_override","dac_read_search","fowner","fsetid","kill","setgid","setuid","setpcap","linux_immutable","net_bind_service","net_broadcast","net_admin","net_raw","ipc_lock","ipc_owner","sys_module","sys_rawio","sys_chroot","sys_ptrace","sys_pacct","sys_admin","sys_boot","sys_nice","sys_resource","sys_time","sys_tty_config","mknod","lease","audit_write","audit_control","setfcap","mac_override","mac_admin","syslog","wake_alarm","block_suspend"],"effective":["chown","dac_override","dac_read_search","fowner","fsetid","kill","setgid","setuid","setpcap","linux_immutable","net_bind_service","net_broadcast","net_admin","net_raw","ipc_lock","ipc_owner","sys_module","sys_rawio","sys_chroot","sys_ptrace","sys_pacct","sys_admin","sys_boot","sys_nice","sys_resource","sys_time","sys_tty_config","mknod","lease","audit_write","audit_control","setfcap","mac_override","mac_admin","syslog","wake_alarm","block_suspend"],"bounding":["chown","dac_override","dac_read_search","fowner","fsetid","kill","setgid","setuid","setpcap","linux_immutable","net_bind_service","net_broadcast","net_admin","net_raw","ipc_lock","ipc_owner","sys_module","sys_rawio","sys_chroot","sys_ptrace","sys_pacct","sys_admin","sys_boot","sys_nice","sys_resource","sys_time","sys_tty_config","mknod","lease","audit_write","audit_control","setfcap","mac_override","mac_admin","syslog","wake_alarm","block_suspend"],"ambient":null}, "cwd": "/root", "exe": "/usr/share/metricbeat/bin/metricbeat", "name": "metricbeat", "pid": 18456, "ppid": 18438, "seccomp": {"mode":"disabled"}, "start_time": "2018-08-31T13:11:54.880-0700"}}}
2018-08-31T13:11:55.536-0700 INFO instance/beat.go:225 Setup Beat: metricbeat; Version: 6.3.1
2018-08-31T13:11:55.537-0700 INFO elasticsearch/client.go:145 Elasticsearch url: http://localhost:9200
2018-08-31T13:11:55.537-0700 DEBUG [publish] pipeline/consumer.go:120 start pipeline event consumer
2018-08-31T13:11:55.538-0700 INFO pipeline/module.go:81 Beat name: x.y.ext
2018-08-31T13:11:55.538-0700 INFO instance/beat.go:315 metricbeat start running.
2018-08-31T13:11:55.538-0700 DEBUG [cfgfile] cfgfile/reload.go:90 Checking module configs from: /etc/metricbeat/modules.d/*.yml
2018-08-31T13:11:55.538-0700 DEBUG [cfgfile] cfgfile/cfgfile.go:143 Load config from file: /etc/metricbeat/modules.d/mysql.yml
2018-08-31T13:11:55.538-0700 INFO [monitoring] log/log.go:97 Starting metrics logging every 30s
2018-08-31T13:11:55.538-0700 DEBUG [cfgfile] cfgfile/reload.go:104 Number of module configs found: 0
2018-08-31T13:11:55.538-0700 INFO cfgfile/reload.go:122 Config reloader started
2018-08-31T13:11:55.539-0700 DEBUG [cfgfile] cfgfile/reload.go:146 Scan for new config files
2018-08-31T13:11:55.539-0700 DEBUG [cfgfile] cfgfile/cfgfile.go:143 Load config from file: /etc/metricbeat/modules.d/mysql.yml
2018-08-31T13:11:55.539-0700 DEBUG [cfgfile] cfgfile/reload.go:165 Number of module configs found: 0
2018-08-31T13:11:55.539-0700 INFO cfgfile/reload.go:214 Loading of config files completed.
^C2018-08-31T13:12:06.091-0700 INFO cfgfile/reload.go:217 Dynamic config reloader stopped
2018-08-31T13:12:06.095-0700 INFO [monitoring] log/log.go:132 Total non-zero metrics {"monitoring": {"metrics": {"beat":{"cpu":{"system":{"ticks":40,"time":{"ms":45}},"total":{"ticks":60,"time":{"ms":68},"value":60},"user":{"ticks":20,"time":{"ms":23}}},"info":{"ephemeral_id":"853151a7-c005-40b0-b754-0e6a14cccb01","uptime":{"ms":10597}},"memstats":{"gc_next":4194304,"memory_alloc":2608040,"memory_total":4220944,"rss":20353024}},"libbeat":{"config":{"module":{"running":0},"reloads":1},"output":{"type":"elasticsearch"},"pipeline":{"clients":0,"events":{"active":0}}},"system":{"cpu":{"cores":8},"load":{"1":1.02,"15":0.87,"5":1.04,"norm":{"1":0.1275,"15":0.1088,"5":0.13}}}}}}
2018-08-31T13:12:06.095-0700 INFO [monitoring] log/log.go:133 Uptime: 10.600766736s
2018-08-31T13:12:06.095-0700 INFO [monitoring] log/log.go:110 Stopping metrics logging.
2018-08-31T13:12:06.095-0700 INFO instance/beat.go:321 metricbeat stopped.

18

This is an issue. It should say at least 1 (for the mysql module).

Could you post the contents of /etc/metricbeat/modules.d/mysql.yml after masking any sensitive information in it?

19

Ah interesting, the contents of that file is in my first post that started this thread.

20

I'd be interested in what's the next step on troubleshooting this. Thanks