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