Monitoring MySQL with Zabbix using the appaloosa-zabbix-templates

The standard MySQL Templates for Zabbix are a little basic and I admit I never really got them to work properly.  Thankfully, the Appaloosa Zabbix Templates are available that are based on the excellent Percona Monitoring Plugins.

As always, this is how I got this to work.  If you have any suggestions or improvements, get in touch.

Initial Work

On Ubuntu, some additional Perl modules are required:

$ apt-get install libxml-simple-perl libdatetime-perl

You also need to create some directories

$ mkdir /usr/local/zabbix
$ mkdir /usr/local/zabbix/plugins
$ mkdir /usr/local/zabbix/agent.d
Generating the Template

Download the source from Google Project Hosting

$ wget http://appaloosa-zabbix-templates.googlecode.com/files/appaloosa-zabbix-templates-0.0.1.tgz

Extract the source by running

$ tar -zxvf appaloosa-zabbix-templates-0.0.1.tgz

Open the extracted folder and generate the template XML

$ cd appaloosa-zabbix-templates-0.0.1
$ perl tools/gen_template.pl defs/mysql.pl mysql.xml

Copy the generated file, mysql.xml, to the /usr/local/zabbix/plugins directory

$ cp mysql.xml /usr/local/zabbix/plugins
Creating the Config File

Now create the agent’s config file by editing conf/mysql_agentd.conf using the sed command.

$ sed -e 's|$ZABBIX_AGENT_PATH|/usr/local/zabbix/plugins|' conf/mysql_agentd.conf > mysql.conf

Copy the new config file to the /usr/local/zabbix/config.d directory

$ cp mysql.conf /usr/local/zabbix/config.d

Open the Zabbix Agent configuration file

$ nano /usr/local/etc/zabbix_agentd.conf

Add the following line

Include=/usr/local/zabbix/agent.d
Restart the Zabbix Agent
$ service zabbix-agent restart
Creating a MySQL User

Connect to mysql using the command: (you will be prompted for the root’s password)

$ mysql --u root -p mysql

Create the new user

mysql> CREATE USER 'zabbixmonitor'@'localhost' IDENTIFIED BY 'password';

Grant privileges to the user

mysql> GRANT SELECT, SUPER, PROCESS ON *.* TO 'zabbixmonitor'@'localhost';

Reload all privileges:

mysql> FLUSH PRIVILEGES;

Exit mysql

mysql> quit

Alternatively, you can use a UI tool like Webmin if it is installed on your server.

Configuring the PHP Script

The template requires a PHP file from the original Cacti template.  Download and extract the latest version of the Cacti Templates using

$ wget http://mysql-cacti-templates.googlecode.com/files/better-cacti-templates-1.1.8.tar.gz
$ tar -zxvf better-cacti-templates-1.1.8.tar.gz

Copy the file scripts/ss_get_mysql_stats.php to /usr/local/zabbix/plugins

$ cp better-cacti-templates-1.1.8/scripts/ss_get_mysql_stats.php /usr/local/zabbix/plugins

Open ss_get_mysql_stats.php for editing

$ nano /usr/local/zabbix/plugins/ss_get_mysql_stats.php

Change the values of $mysql_user and $mysql_pass to that of the mysql user created above

$mysql_user = 'zabbixmonitor';
$mysql_pass = '12345678';

Save and close the file

Import the Template

If you generated the mysql.xml on the Zabbix server, you need to download it to your client computer.

Once you have the file, log into Zabbix and go to Configuration > Templates

Click on the Import button in the top right

Click on Choose File to select mysql.xml and then click on Import

If successful, you should see the message Template Imported

Adding the template to a host

In Zabbix, go to Configuration > Hosts

Click on the host to add the template

Select the Templates tab

Click on Add and select template_mysql from the list

Click on Select and then Save

Troubleshooting

If you don’t get any data, check the Zabbix agent’s log.  The logfile’s path is defined in the agent’s configuration file, but the default are:

  • Linux: /tmp/zabbix_agent.log
  • Windows: c:\zabbix\zabbix_agentd.log

Don’t forget, if you make any changes to the configuration file, you will need to restart the agent for them to take effect.

References

Appaloosa Installation Notes: http://code.google.com/p/appaloosa-zabbix-templates/wiki/Installation

Buxxnt: http://buzznt.blogspot.co.uk/2011/09/install-zabbix-mysql-plugin-imported.html

 

4 thoughts on “Monitoring MySQL with Zabbix using the appaloosa-zabbix-templates

  1. George

    I appreciate you providing the MySQL work around. However, I am stuck after running the $ sed -e ‘s|$ZABBIX_AGENT_PATH|/usr/local/zabbix/plugins|’ conf/mysql_agentd.conf > mysql.conf command. I input /etc/zabbix/zabbix_agentd.conf at the ZABBIX_AGENT_PATH section. I am getting a respond of mysql.conf: Permission denied. Any assistance on what I might be missing will be apprecaited greatly…

  2. Alexander John Post author

    May sound obvious, but check that the user you’re logged in has permission to edit the mysql.conf file.

    We actually stopped using Zabbix as we couldn’t get it to monitor our windows servers reliably. We now use Icinga with NSClient++ for active monitoring and Cacti for graphing. Not an all-in-one solution, but they do work very reliably.

  3. Amitabh Roy

    Great tutorial! Was able to set up MYSQL monitoring successfully after using it. There seems to be one typo though

    $ cp mysql.conf /usr/local/zabbix/config.d should be

    $ cp mysql.conf /usr/local/zabbix/agent.d

    Thanks…Amitabh

Leave a Reply

Your email address will not be published. Required fields are marked *