Skip to content

Setup MySQL Database Monitoring

Video Lecture

Setup MySQL Database Monitoring

Description

This video demonstrates configuring the MySQL by Zabbix agent template for a MySQL server running on Ubuntu 20.04 on the same network as my Zabbix server.

The MySQL by Zabbix agent template uses passive checks, so your Zabbix server will need to be able to query the agent on the MySQL host using default port 10050. You may need to consider any firewalls you may have in place.

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

On the host running MySQL, create a new file in the folder /etc/zabbix/zabbix_agentd.d/ and name it template_db_mysql.conf

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"

Enter the MySQL shell and create a new user called zbx_monitor and grant it the required permissions for the database server.

mysql

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 your user has been added to, you can type

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

[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.

sudo service zabbix-agent restart

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

Sample Host Setup

If you don't have a server running MySQL that you can already use, then see these sample commands.

On Ubuntu 20.04, install MySQL.

sudo apt update
sudo apt install mysql-server
sudo service mysql status

Then install and start a Zabbix agent.

sudo wget https://repo.zabbix.com/zabbix/6.0/ubuntu/pool/main/z/zabbix-release/zabbix-release_6.0-1+ubuntu20.04_all.deb
sudo dpkg -i zabbix-release_6.0-1+ubuntu20.04_all.deb
sudo apt update
sudo apt install zabbix-agent

Edit the agent's configuration.

sudo nano /etc/zabbix/zabbix_agentd.conf

Restart and check status.

sudo service zabbix-agent restart
sudo service zabbix-agent status

Add host information to the Zabbix UI. You will also need to add an interface for the Zabbix server to query the agent on port 10050. This is for any passive type checks.

You may need to allow incoming TCP to 10050 from your Zabbix servers IP address in case you have a firewall for your host.

Also allow incoming TCP to 10051 (Default) on your Zabbix servers firewall if your MySQL hosts Zabbix agent is running some active checks, and it cannot get the list from the Zabbix server because the server's firewall is blocking it.

Troubleshooting

Look for any related errors from the Zabbix agent that is running on your MySQL host.

tail -f /var/log/zabbix/zabbix_agentd.log

Look for any related errors from the Zabbix server side

tail -f /var/log/zabbix/zabbix_server.log

template_db_mysql.conf

Connect to MySQL Using ODBC from a Remote Server

How to allow Remote Access to MySQL

Comments