New MySQL monitoring recipe provided

[nieuwhier]

Looks all very promising but I get this:
Warning: system() has been disabled for security reasons in /etc/zabbix/mysql.php on line 625

No way that I am gonna enable system() on my shared hosting servers ;-)

[nepto]

Unable to use Master/Slave without "core" template

First of all thank you for this wonderfull monitoring tool. (with this I mean Zabbix itself but also this MySQL part)

I think I understand well how it works. These two mysql.daily and mysql.live "start" PHP script, which will send data to server. This is working pretty well for us.

However there might be some users, who would like to use just Master/Slave monitoring. In fact this was how we get to this page ;-)

We cannot afford to use "MySQL" template, since it collects huge amount of data and we are interested in Master/Slave data only. But without that "MySQL" template this Master/Slave monitoring is not started, since UserParameters mysql.live and mysql.daily are missing in our Zabbix.

What is the solution for this? I can think of creating simple XML template called "MySQL simple" with just these two parameters mysql.live and mysql.daily. Or include these two into Master/Slave templates. Or another solution, what you can came with.

Before I start with solving this problem, I would like to hear your opinion about this issue. It may be helpful for us. Thanks you.

[nepto]

No way that I am gonna enable system() on my shared hosting servers ;-)

You can enable it for CLI and disable it for use in Apache2. It's simple, I'm sure you know how to do that.

[nieuwhier]

You can enable it for CLI and disable it for use in Apache2. It's simple, I'm sure you know how to do that.

No, we will not enable it for CLI. per user basis would be interesting but that is not possible with our setup.

[mw46d]

A Ruby version of the PHP script

OK, we don't really have PHP installed on our MySQL hosts. So I translated the original PHP script into Ruby.

More info why and the first (?) version of the source can be found here

Thanks for your great info.
-- mw46d

[brian]

It took me a few hard hours to get this to work, but now I am getting data from the mysql server. I would like to share one thing, which could probably help others:

The script identifies the server and the current host by checking zabbix agent configuration file, and it gets the first line where it founds the Server= and Host= lines, but in my config lines there was an "empty" line

#Server=

before the real line, so the zabbix_sender could never send the data to zabbix database...

[tof233]

Hello,
First of All, thank you mikecrowe for your work
I had a problem when trying to import the template (even after deleting the olt template):

Trigger MySQL: Change the read_rnd_buffer_size on {HOSTNAME} already exists
Trigger [MySQL: Change the read_rnd_buffer_size on {HOSTNAME} ]: cannot create

By looking in the template, I found two entries :

<trigger>
<description>MySQL: Change the read_rnd_buffer_size on {HOSTNAME}</description>
<type>0</type>
<expression>{{HOSTNAME}:mysql.Change_read_rnd_buff er_size.last(0)}=1</expression>
<url></url>
<status>0</status>
<priority>1</priority>
<comments>Review Suggested_innodb_buffer_pool_size to determine the best setting</comments>
</trigger>

So I removed one, and did the same for:
MySQL: Root remote login enabled on {HOSTNAME}
MySQL: Queries require tmp sort table on {HOSTNAME}

And now the import work.
But I don't know why there are duplicate entries?

[tof233]

I would like to suggest to add something in the documentation:
For activating the script, you need to modify
define('DEBUG',true); -> define('DEBUG',false); in mysql.php.

I know it may seems evident when you say: By default, DEBUG is enabled.

But I didn't find a place where you mentioned how to disable debug.

[tof233]

Hello,

I have modified a bit mysql.php for my use so it could remotely monitor databases.

Usage: php mysql.php live mysqlusername mysqlpassword mysqlserveraddress hostname

Note that some variables retrieved by shell won't be available but you can still retrieve them by snmp (so template should be modified).

I think it would still need more improvements.

[Nerfux]

Sending failed. Use option -vv for more detailed output.

After 4 days testing and configuration, i finaly got this to function in zabbix 1.8.4, now my problem is,

in my /tmp/zabbix_mysql.log have plenty of:

Sending failed. Use option -vv for more detailed output.

in my /tmp/zabbix_myslq.dat i have correct data,

in my /tmp/zabbix_mysql-debug.log i have the correct data.

Why will not my zabbix server collect the info from the script?

[Nerfux]

After 4 days testing and configuration, i finaly got this to function in zabbix 1.8.4, now my problem is,

in my /tmp/zabbix_mysql.log have plenty of:

Sending failed. Use option -vv for more detailed output.

in my /tmp/zabbix_myslq.dat i have correct data,

in my /tmp/zabbix_mysql-debug.log i have the correct data.

Why will not my zabbix server collect the info from the script?

okei, found out i can send the values to zabbix server manualy,
but, in the /tmp/zabbix_mysql.dat i see that host name stand like this:
system.hostname

When i tryed; zabbix_sender -z 10.80.145.50 -p 10051 -s system.hostname -k mysql.Open_tables -o 513, nothing happend, but after replaceing "system.hostname" with the correct client name my zabbix server got the values.

Is that a problem in mysql.php, that it write the wrong host name in the .dat file?

Also, when im trying to do this:
zabbix_agentd -t mysql.daily
-mysql.daily [t|1]

Okei that was a successful one, now look at this,
zabbix_agentd -t mysql.live
sh: montering_produkter: command not found <- (montering_produkter is a table name)
sh: inaktiv: command not found
sh: montering_produkter: command not found
sh: produkt_id: command not found
mysql.live [t|1]

so.... when i now look in my /tmp/zabbix_mysql.dat, in the key: mysql.Last_Error "Error 'Unknown column 'inaktiv' in 'field list'' on query. Default database: 'nett'. Query: 'UPDATE `montering_produkter` SET `inaktiv` = '1' WHERE `montering_produkter`.`produkt_id` = 47 LIMIT 1'"

Can this be the problem, thinking that, mysql.php can understand this string, with all the quotes?

[Nerfux]

okei, found out i can send the values to zabbix server manualy,
but, in the /tmp/zabbix_mysql.dat i see that host name stand like this:
system.hostname

When i tryed; zabbix_sender -z 10.80.145.50 -p 10051 -s system.hostname -k mysql.Open_tables -o 513, nothing happend, but after replaceing "system.hostname" with the correct client name my zabbix server got the values.

Is that a problem in mysql.php, that it write the wrong host name in the .dat file?

Also, when im trying to do this:
zabbix_agentd -t mysql.daily
-mysql.daily [t|1]

Okei that was a successful one, now look at this,
zabbix_agentd -t mysql.live
sh: montering_produkter: command not found <- (montering_produkter is a table name)
sh: inaktiv: command not found
sh: montering_produkter: command not found
sh: produkt_id: command not found
mysql.live [t|1]

so.... when i now look in my /tmp/zabbix_mysql.dat, in the key: mysql.Last_Error "Error 'Unknown column 'inaktiv' in 'field list'' on query. Default database: 'nett'. Query: 'UPDATE `montering_produkter` SET `inaktiv` = '1' WHERE `montering_produkter`.`produkt_id` = 47 LIMIT 1'"

Can this be the problem, thinking that, mysql.php can understand this string, with all the quotes?

Okey, a few step forward, the mysql.php script reads the zabbix_agend.conf,
it CAN NOT see the differens on disabled words, like this

#Hostname= system.name
Hostname=myslqserver
So, you need to remove the example that is in the config file by default.

just found out in my /tmp/zabbix_agentd.log have som strange lines the same line i was talking about above;
28970:20110128:151837.053 zabbix_agentd active check started [10.80.145.50:10051]
sh: montering_produkter: command not found
sh: inaktiv: command not found
sh: montering_produkter: command not found
sh: produkt_id: command not found

[Nerfux]

Okey, a few step forward, the mysql.php script reads the zabbix_agend.conf,
it CAN NOT see the differens on disabled words, like this

#Hostname= system.name
Hostname=myslqserver
So, you need to remove the example that is in the config file by default.

just found out in my /tmp/zabbix_agentd.log have som strange lines the same line i was talking about above;
28970:20110128:151837.053 zabbix_agentd active check started [10.80.145.50:10051]
sh: montering_produkter: command not found
sh: inaktiv: command not found
sh: montering_produkter: command not found
sh: produkt_id: command not found

well i edited the mysql.php
with this

//'Last_Error' => isset($Last_Error) ? $Last_Error : "",

1 problem "solved"

Next, of curse a laaaaaaaaaaaaame thing in the config file again.

### Option: Server
# List of comma delimited IP addresses (or hostnames) of Zabbix servers.
# No spaces allowed. First entry is used for receiving list of and sending active checks.
# If IPv6 support is enabled then '127.0.0.1', '::127.0.0.1', '::ffff:127.0.0.1' are treated $
#
# Mandatory: yes
# Default:
# Server=
Server=10.80.145.50

so this php scipt have not been written in "anti-dummy".
My script was thinking and trying to send data to, Server=10.80.145.50,
and not only, 10.80.145.50.

You need to remove the default examples on hostname and server(ip/name)

hope this will be helpful to everyone

[lgc78]

When i import the Standalone mysql server template it says successfully imported. However, when i look thru my list of available templates i do not see it.

I'm running version 1.8.4

Anyone else run into this problem?

[Nerfux]

Yeah i had that problem my self, are sure u deleted the default mysql template? Needed to play around for a bit, like i tried to change the name on the template and the template group. But have you tried to do a simple reboot? I dont know if that will do any good but just a idea that you can try