Setup MySQL Database Monitoring
Video Lecture
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.