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