Monitor MySQL using Zabbix Agent 2.x and MySQL App template

With Zabbix (tested with Zabbix 2.2 and 2.4), we can monitor the MySQL server performances using Zabbix Agent and the Template App MySQL included in Zabbix Server.

First of all, we have to create a MySQL user account to be used to access mysql and get the nessary stats.

Login to the client and run in command line the command above (type the MySQL root password when prompted):

mysql -uroot -p -e"GRANT USAGE ON *.* TO 'zabbix'@'127.0.0.1' IDENTIFIED BY '123456'";
mysql -uroot -p -e"GRANT USAGE ON *.* TO 'zabbix'@'localhost' IDENTIFIED BY '123456'";
mysql -uroot -p -e"flush privileges"

Note that the USAGE privilege specifier stands for “no privileges”, but change the '123456' password to something more secure: something like 'KJ37ae!$' is stronger (don't worry, we don't have to type it frequently)

Test the new zabbix mysql account to make sure it works with:

mysql -uzabbix -123456 -e"status"

If it works, the output will look something like

mysql  Ver 14.14 Distrib 5.5.40, for debian-linux-gnu (x86_64) using readline 6.2

Connection id:		20705
Current database:	
Current user:		zabbix@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.5.40-0+wheezy1 (Debian)
Protocol version:	10
Connection:		Localhost via UNIX socket
...

Now create a new .my.cnf file under the zabbix config folder /etc/zabbix. This whill allow mysqladmin to connect to the mysql database.

vim /etc/zabbix/.my.cnf

and paste this content into the file

[mysql]
user=zabbix
password=123456
[mysqladmin]
user=zabbix
password=123456

Edit the file /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf and replace HOME = /var/lib/zabbix with HOME = /etc/zabbix (should appear three times).

Finally, restart Zabbix Agent with

service zabbix-agent restart

On the Zabbix Server you can now apply the Template App MySQL template to the host you want to monitor.

[1] http://blog.themilkyway.org/2013/11/how-to-monitor-mysql-using-the-new-zabbix-template-app-mysql