New MySQL monitoring recipe provided

[stelios]

Hi all,

I'm trying to test the script but getting the following errors:

[root@db1 etc]# php mysql.php live root mypassword
PHP Notice: Undefined offset: 4 in /usr/local/etc/mysql.php on line 31
Physical Memory: 15G, Swap: 15G
PHP Deprecated: Function ereg() is deprecated in /usr/local/etc/mysql.php on line 58
PHP Fatal error: Call to undefined function mysql_connect() in /usr/local/etc/mysql.php on line 73

Any help is much appreciated.

[jeenam]

What distro and php version are you using?

Centos 5.x here with the following php packages:

php-mysql-5.2.10-1
php-pdo-5.2.10-1
php-5.2.10-1
php-cli-5.2.10-1
php-common-5.2.10-1

I've attached a working mysql.php to this post. You will need to change the filename extension to .php, as the forum will not allow .php files to be uploaded.

Usage: php /etc/zabbix/scripts/mysql.php live <mysql_user> <mysql_pass>

The script assumes the connection is made to 'localhost' so be sure to create the proper grant.


This has also been tested against Centos 6.2 with the following php packages:

php-mysql-5.3.3-14.el6_3.x86_64
php-5.3.3-14.el6_3.x86_64
php-cli-5.3.3-14.el6_3.x86_64
php-common-5.3.3-14.el6_3.x86_64
php-pdo-5.3.3-14.el6_3.x86_64

Note that on Centos 6.2 with php 5.3 the following error message is displayed, and can be ignored:

PHP Deprecated: Function ereg() is deprecated in /etc/zabbix/scripts/mysql.php on line 58


Output is written to /tmp. Look for zabbix_mysql.dat and zabbix_mysql.log

[stelios]

Thanks Jeenam, a rpm was missing, now it works.

I've import the template and it done without issues but I can't see under templates the template? Where can I found it?

Thanks

[jeenam]

these templates actually work

The attachments from the OP are broken. Due to attachment size limitations I am posting links to working templates hosted by google docs. Note that these were exported from Zabbix 2.0.3.

Template_MySQL_Server - https://docs.google.com/open?id=0B2v...mNxWW5FYW1NcWc
Template_MySQL_Slave_Server - https://docs.google.com/open?id=0B2v...G1RMk9xaE12V1E

[stelios]

Thanks Jeenam, the attached xml is working fine.
One more question please as it's not clear to me via documentation.
Do I need to add any userparameters into my zabbix_agentd.conf to start collecting data?

For example to collect data for slow queries, I need to enable that in my.cnf and add it also under zabbix_agentd.conf or it somehow will collect data from mysql server itself?

Thanks in advance

[jeenam]

Here are the UserParameter lines to be added to zabbix_agentd.conf:

UserParameter=mysql.daily,php /etc/zabbix/scripts/mysql.php daily <mysql_username> <pass>
UserParameter=mysql.live,php /etc/zabbix/scripts/mysql.php live <mysql_user> <pass>

Be sure to restart the agent afterwards.

[neilb]

Hi
just reserecting this post, but i've imported the template, installed the mysql.php (from jeenam), set the appropriate lines in my agent file, but the only data i retrieve back is for MySQL: Live Controller = 1.

i can see data being collected in /tmp/zabbix_mysql.dat
eg
127.0.0.1 127.0.0.1 10051 mysql.Key_write_requests 86662
127.0.0.1 127.0.0.1 10051 mysql.Key_writes 30
127.0.0.1 127.0.0.1 10051 mysql.Open_tables 170

the /tmp/zabbix_mysql.log shows
sent: 1; skipped: 0; total: 1
sent: 1; skipped: 0; total: 1


I've possibly missed something but cant see where. Has anyone got an insight into this?

thanks in advance
Neil

[jeenam]

1) Log into the server and become the zabbix user: su - zabbix

2) run the following command as the zabbix user: php /etc/zabbix/scripts/mysql.php live <mysql_user> <mysql_pass>

3) Look in /tmp/ for zabbix_mysql.dat and zabbix_mysql.log. Both files should contain data.



Common problems to the script not working include:
a) zabbix_sender not installed. zabbix_sender must be installed to allow the script to send data to the zabbix server
b) zabbix user does not have a valid shell. This can be fixed with: usermod -s /bin/bash <zabbix_username>
c) php is not installed. Install php.

[jeenam]

Hi
just reserecting this post, but i've imported the template, installed the mysql.php (from jeenam), set the appropriate lines in my agent file, but the only data i retrieve back is for MySQL: Live Controller = 1.

i can see data being collected in /tmp/zabbix_mysql.dat
eg
127.0.0.1 127.0.0.1 10051 mysql.Key_write_requests 86662
127.0.0.1 127.0.0.1 10051 mysql.Key_writes 30
127.0.0.1 127.0.0.1 10051 mysql.Open_tables 170

the /tmp/zabbix_mysql.log shows
sent: 1; skipped: 0; total: 1
sent: 1; skipped: 0; total: 1


I've possibly missed something but cant see where. Has anyone got an insight into this?

thanks in advance
Neil

The client hostname as configured in the Zabbix web interface MUST match what is configured on the client in zabbix_agentd.conf.

Looking at your log output the problem is most likely in your agent config. The second column in zabbix_mysql.dat should contain the name of the host for which data is being sent. Is the client named '127.0.0.1' in zabbix? What is the Hostname= variable in zabbix_agentd.conf?

zabbix_mysql.dat output is ordered as follows:

<zabbix_server_ip> <client_hostname> <port> <item> <value>


An example output would be:

192.168.100.5 db01.domain.tld 10051 mysql.Select_full_join 28573

In the above example, /etc/zabbix/zabbix_agentd.conf will contain the following for the hostname variable: Hostname=db01.domain.tld

The mysql.php script extracts the client hostname on line 40. Remember to restart the zabbix agent after making changes to zabbix_agentd.conf.

[neilb]

nice one, thanks Jeenam.

[jeenam]

You're welcome. Glad I could help.

[tubik]

No data

I installed template but I don't have data for keys ->
MySQL: Queries: % Deletes of overall
MySQL: Queries: % Inserts of overall
MySQL: Queries: % Selects of overall queries

[jeenam]

I installed template but I don't have data for keys ->
MySQL: Queries: % Deletes of overall
MySQL: Queries: % Inserts of overall
MySQL: Queries: % Selects of overall queries

Tubik,

If you're able to obtain data using the template, but cannot obtain the data you mention above, unfortunately I do not know php so I can't say what may need to be changed to allow you to collect that data.

In the /tmp/ directory of the server running the mysql.php script, there will be a *dat file with a list of the items that are being collected by the script.

[tubik]

It's working.
I check PHP script and these values are Daily.

[jeenam]

Good to hear. There is also a log file in /tmp/ that reports whether data was successfully sent to the server.