Setup MySQL Database Monitoring

Video Lecture

Setup MySQL Database Monitoring Setup MySQL Database Monitoring Setup MySQL Database Monitoring

Description

Our Zabbix Server is using a MySQL database. We can monitor this database using the Template DB MySQL by Zabbix agent template.

After linking this template to your host, it will not work unless you do several more configurations.

Create a new file in the folder /etc/zabbix/zabbix_agentd.d/ and name it template_db_mysql.conf

1
sudo nano /etc/zabbix/zabbix_agentd.d/template_db_mysql.conf

Copy in this text below. See template_db_mysql.conf for official source code.

1
2
3
4
5
6
7
UserParameter=mysql.ping[*], mysqladmin -h"$1" -P"$2" ping
UserParameter=mysql.get_status_variables[*], mysql -h"$1" -P"$2" -sNX -e "show global status"
UserParameter=mysql.version[*], mysqladmin -s -h"$1" -P"$2" version
UserParameter=mysql.db.discovery[*], mysql -h"$1" -P"$2" -sN -e "show databases"
UserParameter=mysql.dbsize[*], mysql -h"$1" -P"$2" -sN -e "SELECT COALESCE(SUM(DATA_LENGTH + INDEX_LENGTH),0) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$3'"
UserParameter=mysql.replication.discovery[*], mysql -h"$1" -P"$2" -sNX -e "show slave status"
UserParameter=mysql.slave_status[*], mysql -h"$1" -P"$2" -sNX -e "show slave status"

Log into mysql and create a new user called zbx_monitor and grant it the required permissions for the database server.

1
mysql

1
2
CREATE USER 'zbx_monitor'@'localhost' IDENTIFIED BY '<password>';
GRANT USAGE,REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zbx_monitor'@'localhost';

To verify the databases that you user has been added, you can type

1
select user,host from mysql.user;

Next, create a file called .my.cnf in home directory of Zabbix agent for Linux (/var/lib/zabbix by default )

or my.cnf in c:\ in Windows.

and add the contents below

1
2
3
[client]
user='zbx_monitor'
password='<password>'

Note

On linux, you may need to create the folder /var/lib/zabbix using the command mkdir /var/lib/zabbix and then cd into it.

Save and restart the zabbix agent.

1
sudo service zabbix-agent restart

After a minute or so, your new MySQL items for your Host will start to receive data.

template_db_mysql.conf

How to allow Remote Access to MySQL