Monitoring MySQL databases

[Humulus]

Hi all,

I'm trying to get my head around how Zabbix works, but am stumped at how I can solve this problem:

I have a server with MySQL with ~50 databases. The number/names of databases change, because of the developers working on them.
When a database contains a table named "checks", I would like to monitor the values in this table.

So I'm trying to find out if I can create a template that discovers which databases there are with a table named "checks" in it, and if so then create items of each record in the table.
I know that the template "MySQL by Zabbix agent 2" discovers all databases, but how can I discover the tables in these databases. I only find descriptions where you need to create an ODBC connection if you want to monitor a database...

I'm using Zabbix 7.0 LTS with the latest Zabbix Agent 2 by the way.

[bbonno]

The first thing to do is to establish a connection to your database. Any of the available options here can help you with that. https://www.zabbix.com/integrations/mysql

The templates the options come with are not exactly what you are asking for, but they are full of good examples of how Zabbix mechanics work.

" MySQL by Zabbix agent" comes with these macros that limit which databases is will discover, and if you look at how the discovery uses these I'm sure you can fluff the config to suit your needs.

[DoItAgainSteelyDan]

Create a test script in your test environment and try it out

Try creating a script to discover databases with a checks table
##create script file
sudo nano /usr/lib/zabbix/externalscripts/discover_checks_dbs.sh
##Insert Script

#!/bin/bash
mysql -u zabbix -pzabbixpass -N -e \
"SELECT table_schema FROM information_schema.tables WHERE table_name='checks';" | \
awk '{printf("{\"{#DBNAME}\":\"%s\"},", $1)}' | \
sed 's/,$//; s/^/[/' | sed '$a\]'
##save script

##You can open up MySQL and allow access

mysql -u root -p

GRANT SELECT ON information_schema.* TO 'zabbix'@'zabbix_server_ip' IDENTIFIED BY 'zabbixpass';
FLUSH PRIVILEGES;

Exit;

You can test access from the Zabbix server with:
mysql -u zabbix -pzabbixpass -h <ip.address.here> -e "SHOW DATABASES;"

The script is executable: chmod +x discover_checks_dbs.sh
The Zabbix server can connect to MySQL over the network
The zabbix MySQL user has at least SELECT on information_schema.tables

[Humulus]

Thanks for the hints. I'll see if I can cobble something up.