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