Easy Installation But Complete MySQL Monitoring

[Razique]

Hi hi all,
After two days of hard work, I'm coming with a new template for MySQL monitoring.
the one provided on the wiki (http://www.zabbix.com/wiki/howto/mon...ng_replication) contains too many datas, then hard to "read", I've noticed that I was barely able to understand them.

So I've decided to create a shinny new template, which get only the critical values, (54 items) and I've created a set of nice triggers and graphs.
Here is the final result :


Here are the graphs :


Installation
1- Download the template, which replaces the current Template_App_MySQL one.
2- Download the bash script (rename it to .sh) and make it executable
3- At line 22 and 23 set the correct MySQL credentials (You should create a dedicated user)
4- Put this script on a server you want to monitor
5- Cron it! (Mine is launched every 5 minutes)


How does it work
The bash script connects one time to the MySQL server, gets all the datas, and exit
Then the values are formatted, and sent to the Zabbix Server, eg :
HOSTNAME KEY VALUE
A temporary file is generated, but deleted when the send is complete.
If you need extra values (but remember, I've tried to get only the representatives values (around 54 keys of hundreds that MySQL provides)

My triggers try to be the most representatives ones, wich "good" ratios, but I could be wrong on some triggers. When the turn to true, it mainly means that you could optimize your database.

Enjoy

[Razique]

Updated Template !

You will find in this update two new triggers, and two new graphs, and 4 new items !
I've also changed some graphs, and change their names (Memory VS Cache)

Here are all the triggers :


And the Graphs :



How to Update :
- Simply Update your Template, there is no need to update the bash script

[username_taken]

I was getting errors with the script and was finding it hard to read/debug. Knowing what it does I was able to rewrite it in perl and skip all the temp file writing etc. I'm also push the data to stdout so I can pipe the script to the zabbix_sender binary. like so

./mysql.pl | ./zabbix_sender -z zabbix-server -c /etc/zabbix/zabbix_agentd.conf -i -

perl file is attached.

[Razique]

Awesome
You did a nice job. How the template's working for you ?
You should implement a debug param, so the users are able to understand where it fails, when it does Too bad I don't really know how to code in Perl!
Best Regards,
Razique

[lgc78]

Great script. Just set it up and it seems to be working. I'm noticing that not all items are being processed though. See output below.

Info from server: "Processed 8 Failed 31 Total 39 Seconds spent 0.000443"
sent: 39; skipped: 0; total: 39
Info from server: "Processed 49 Failed 29 Total 78 Seconds spent 0.000933"
sent: 78; skipped: 0; total: 78

Also, i was wondering if there's a way to have the zabbix agent run this - instead of having to setup a cron job on each mysql server.

Thanks again for the script!

[username_taken]

You can set up a custom item to run it from on the agent ... something like ...


UserParameter=custom.mysql.activate,/path/to/mysql.pl | /path/to/zabbix_sender -z zabbix-server -c /etc/zabbix/zabbix_agentd.conf -i -

[lgc78]

hmm doesn't seem to work. I would need to create a new item in the mysql template to match, correct? And what type should it be... zabbix_sender or zabbix agent(active) or other? Thanks for the help.

[Zete]

I'm kinda stuck with this.

I imported the template and linked it to mysql hsot, copied and edited mysql.pl file on the mysql host, added a line

UserParameter=custom.mysql.activate,/usr/local/sbin/mysql.pl | /usr/local/sbin/zabbix_sender -z zabbixserv -c /etc/zabbix/zabbix_agentd.conf -i -

And I see it's not working. I've just started on zabbix and have no experience on creating User parameters. I believe the "custom.mysql.activate" part is the key. Do I have to make an additional check with this key at the server side to make this work?

If i run the "/usr/local/sbin/mysql.pl | /usr/local/sbin/zabbix_sender -z zabbixserv -c /etc/zabbix/zabbix_agentd.conf -i -" command manually, I get

Info from server: "Processed 0 Failed 109 Total 109 Seconds spent 0.002408"
sent: 109; skipped: 3; total: 112

What could be the reason for that?


UPD: Resolved!!

The thing is that I have a failover solution and I needed to send the data to a host with a different name in zabbix. Slighty edited the script, so it does not provide hostname and included -s parameter in the command.

[efrain02]

Would you be nice and let me know if i understand right?

You put the sh file on the server you need to minitor.
On zabbix you import the template.

I don't undestad what else do you do in order to see if it's working.
Hope you could clarify this a little bit more.

Thanks!

[jeannaquila]

Thanks for this one. Looks really easy

[jdehnert]

Store Values set correctly?

I have the perl version of this installed and my system is gathering data, but I think some of the graph store value types are set incorrectly.

For instance the store value for MySQL Queries Per Second was set to store value As Is, not Delta (speed per second)

I'm going through all of these values, but I am wondering if anyone has looked at these and knows the correct disposition for various items.

[Kedare]

Hello,

I tried to make this script work, but it looks like it can't execute the zabbix_sender, here is what I get when I run the script by hand :

zabbix@ikgf:/opt/zabbix$ ./monitoring_mysql.sh 
usage: zabbix_sender [-Vhv] {[-zpsI] -ko | [-zpI] -i <file>} [-c <file>]
usage: zabbix_sender [-Vhv] {[-zpsI] -ko | [-zpI] -i <file>} [-c <file>]
zabbix@ikgf:/opt/zabbix$

Zabbix 1.8.2 on Debian 6.0

Any idea of how can I fix this ?

Thank you

[nima0102]

Hi
Thanks for your efforts and any one that contributes on this community.
After some googling, I found another mysql template:

http://code.google.com/p/appaloosa-zabbix-templates/

I have not tested yet. Does anyone test this template? if so, Is it better than template that has been introduced on this topic?

Thanks for any help or guidance

[infernoltu]

Hi all.

Your template imported to zabbix server 1.8.6 successfully.
i also downloaded monitoring_mysql.sh, changed needed info and lunched.
And it hangs up. Nothing happens.

Tried anonther mysl.pl perl script, but it says there is error on line 37, undefined variable "@variables" smth like that.

I am using Ubuntu TLS 10.04.01 server.

all modules are installed.
Everything is running, except that.

Any ideas?

[AndreyHammer]

Line 33 must be: $ZABBIX_HOSTNAME=$config{'Hostname'};