Monitoring MySQL/PostgreSQL table

[Capdefava]

Hello everybody:

I am interested in monitoring records from a MySQL / PostgreSQL table...

e.g. collecting the data returned by a script doing a query "SELECT value FROM TABLE"

What do you think the best way to do it? I'm watching the possibilities of doing it with Zabbix_sender, Zabbix Trapper, SNMPvX agent... but a little confused which is the most recommended way...

Does anyone know any well documented howto?

Thank you.

Regards,
CDF

[Capdefava]

FYI I am achieved this using Zabbix Sender...

I created "Zabbix_Trapper" type Items and then in the host added a cronjob script (bash) doing postgresql selects (with exec('echo "SELECT * from...) and sending the data with /usr/bin/zabbix_sender

Thank you.

[Joby]

Could you please explain it step by step I need to monitor a particular query in my MySQL server using this method.

FYI I am achieved this using Zabbix Sender...

I created "Zabbix_Trapper" type Items and then in the host added a cronjob script (bash) doing postgresql selects (with exec('echo "SELECT * from...) and sending the data with /usr/bin/zabbix_sender

Thank you.

[Capdefava]

Hi Joby,

I will try to explain you how I did it from memory... long ago...

At Zabbix Agent:

1.- Create script /etc/zabbix/mytools/sqlqueryscript.php (get data from database and send with zabbix sender to Zabbix_trappers)

<?php
		/* Include database connection PHP lines here, this is just a fast and very crappy example using exec()/system() & bash */
		exec('echo "SELECT * from table......" | psql -U userdb -h hostname databasename', $result1);
		$mydata1 = trim($result1[0]);
		//Send to the trapper
		exec("/usr/bin/zabbix_sender -z $ZABBIXSERVER -p $ZABBIXPORT  -s $ZABBIXAGENT -k \"my.key.result1\" -o $mydata1");
	?>

2.- Create script /etc/zabbix/conf/runsqlqueryscript.conf

UserParameter=my_pg_data,php /etc/zabbix/mytools/sqlqueryscript.php;echo $?

3.- Add at /etc/zabbix/zabbixd.conf the following line:

Include=/etc/zabbix/conf/

4.- Restart Zabbix Agent

At Zabbix_Server:

1.- Go to Zabbix GUI > Configuration > Add an Item in the host (or template)

Name: My data to monitor from database
Type: Zabbix_Traper
Key: my.key.result1


2.- Go to Zabbix GUI > Configuration > Add an Item in the host (or template)

Name: Run my database trappers
Type: Zabbix_agent
Key: my_pg_data
Update interval (in sec): 600 (run the script to send info to the trappers every 10 minutes)


Sure there are more elegant ways to do it, anyway hope helps you.

Also you can take a look at https://www.zabbix.com/wiki/howto/mo...ng_replication (mysql.php script)

[Joby]

'Monitoring MySQL/PostgreSQL table'

Thanks a lot for your reply. Actually I was looking for an option described in this link. https://www.zabbix.com/forum/showthr...781#post133781