New MySQL monitoring recipe provided

[mikecrowe]

Hi folks,

I've just entered a new cookbook recipe http://www.zabbix.com/wiki/doku.php?id=extensive_mysql_monitoring_including_r eplication

Please submit questions here, or ping me on the #zabbix IRC channel (I'm drmikecrowe).

[mikecrowe]

Template update

Found an error in the template with Change_read_rnd_buffer_size. Corrected and updated wiki.

[bbrendon]

Wolly CRAP that took a lot of work. I didn't try it, but it looks very nice.

I think a thorough extension this comprehensive in terms of code, templates, and especially documentation should be commended!!

Very few people step up with contributions like this.

I should update the wiki install guide for Debian now that 1.6 is out. I look like a slacker!

[ufocek]

Hi,

I'm create to test example item: mysql.Innodb_log_file_size_total, before I import Template_MySQL_Server.xml

Description: mysql.innodb.test
Type:Zabbix trapper
Key: mysql.Innodb_log_file_size_total
Type of information: Numeric (integer 64bit)
Units: B

zabbix_agentd.conf

UserParameter=mysql.live,/usr/bin/php /usr/local/zabbix/sbin/mysql.php live foo password
UserParameter=mysql.daily,/usr/bin/php /usr/local/zabbix/sbin/mysql.php daily foo password


zabbix@noc:/usr/local/zabbix/sbin$ /usr/bin/php /usr/local/zabbix/sbin/mysql.php live foo password |grep Innodb_log
/usr/local/zabbix/sbin/zabbix_sender -z 192.168.1.97 -p 10051 -s noc -k mysql-debug.Innodb_log_file_size_total -o 10485760
zabbix@noc:/usr/local/zabbix/sbin$

But When I looking on latest data mysql.innodb.test it's no data.

[mfburgo]

Had similar problems you need to turn debug off before it will populate to the zabbix database. in the mysql.php file set debug off and it will populate......

[mikecrowe]

Here's your problem:

Hi,

Key: mysql.Innodb_log_file_size_total

[snip]

/usr/local/zabbix/sbin/zabbix_sender -z 192.168.1.97 -p 10051 -s noc -k mysql-debug.Innodb_log_file_size_total -o 10485760

[ufocek]

Ok, I'm setting debug=false, but I still no have any data

[mikecrowe]

OK, here's some thoughts:

1. First, show us the output of the script for the line which doesn't have any data
2. Check the "Configuration of Items" for that entry and make sure it is enabled
3. Check the server zabbix log to for errors sending that item. You may also want simply manually send that item until you get the server configured to accept it.
4. Post as much info as you can for us to review

[mfburgo]

Ok, I'm setting debug=false, but I still no have any data

id the verify that the hostname is set properly in the zabbix_agentd.conf. If localhost then the data will be sent to a machine in zabbix the is named localhost.

if it is named zabbix-server.<your domain> then change the hostname in the zabbix_agentd.conf file to the name you have for your zabbix server. Found this all out via trial and error when I worked to get the script working for my systems also......

[mikecrowe]

New version updated

Hi folks,

Just updated both the template and the mysql.php. Check the changelog section of the wiki at to see if it affects you.

[cstackpole]

First, I have to thank you for this. This is pulling back really good data for me to have.

I have just now started to explore everything you have done with this. I got it installed and tested but I still have a bunch of questions and problems.

The first 'problem' was that when I downloaded the php file off of the wiki, it has debug already set to false contrary to what the wiki says. I thought about changing it for you, but then thought better about it as I didn't know if that was supposed to happen or not (documentation says not).

The next one is that the live portion of the script runs perfectly. The daily...not so much. When I run the script manually or have Zabbix do it, now information shows up. If I run it with debug on and I manually run the zabbix_sender (removing the debug bit) it inserts the value. That tells me the script is running fine. I thought it was something with the caching so I deleted all of the /tmp/.zabbix_files. Still I get no data back.

Lastly, When I first got all of the values inserted (did the daily ones by hand once to test the triggers) I had 6 Averages, 8 Warnings, and 16 informations. So I just started at the top and am working my way down trying to read up on as much as I can from the links you have posted in the wiki. However, I have lots of questions. I don't want to dump a ton of mysql messages on you so I am trying to solve the ones I can first, but would it be ok if I asked about several of these? A few of them I am not finding helpful information on.

Thanks!

[Edit] Doh! Almost forgot. I am getting some odd results with the following from the /tmp/zabbix_mysql.dat file:

Myhost Myhost 10051 mysql.Joins_without_indexes_per_day 2.13E-5

Do you know why?

[mikecrowe]

cstackpole: Thanks, and feel free to join in editing and making this tool better.

Sorry about the debug. I try to remember to flip it, and I missed that revision.

Regarding Joins_without_indexes_per_day, I just changed it to:

    'Joins_without_indexes_per_day' => round(($Select_range_check + $Select_full_join)/$days,0),

To that it rounds your entry to 0. Uploaded to wiki.

Send me a private message to get my IM info, or hit me on the IRC channel to ask all the questions.

[cstackpole]

Thanks for the fix!

[lostmarbles]

Hello,

To be clear (sorry for being new a this), to install this extension we:

• Upload the mysql.php file to the directory of choice
• Modify the zabbix_agentd.conf file
• Restart the zabbix agent
• Import the provided template in the server control panel
• ... and that is the entire installation from start to finish?

[mikecrowe]

Hi Lost ... ,

I would reverse the order and import the template before restarting Zabbix. It won't hurt anything to leave it as you did, but I prefer to have Zabbix ready to receive the new info before I start sending it.

Otherwise, looks good.

I install the script in the /etc/zabbix directory for consistency, but there's no restriction on that.

Mike