Programster's Blog

Tutorials focusing on Linux, programming, and open-source

Zabbix - Monitor MySQL / MariaDB Database

Table of Contents

About

Previously, we learned how to configure monitoring on a Debian 12 instance using the Zabbix Agent 2 package with a generic Linux by Zabbix agent or Linux by Zabbix agent active template. This time we are going to take it a step further, and monitor the MySQL / MariaDB server that is running on that Debian 12 instance as well.

Steps

1. Update Zabbix Agent Configuration

First we need to prepare the host that is being monitored, to give the Zabbix agent the details it requires to be able to know about the MySQL/MariaDB server, and be able to login etc.

SSH into the server and use the following command to create the necessary config file:

2. Create Zabbix Database Monitoring User

Now we need to create a database user for the Zabbix agent to use to perform commands to retrieve metrics.

Log into your database as an administrative user:

sudo mysql

Run the following commands to create the Zabbix monitoring user, that can only be used when logging in from the server itself (not remotely), and grant the user the permissions it will need in order to perform its job:

CREATE USER 'zbx_monitor'@'localhost' IDENTIFIED BY 'passwordGoesHere';
GRANT USAGE,REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zbx_monitor'@'localhost';

Make sure to change the value for the password!

Now we need to create an authentication file for the Zabbix agent to use to automatically authenticate into the MySQL server.

sudo mkdir -p /var/lib/zabbix
sudo editor /var/lib/zabbix/.my.cnf

The directory will not exist unless you already created it, hence we need to mkdir first.

Fill it with the following content, setting the password as appropriate.

[client]
user='zbx_monitor'
password='passwordGoesHere'

Make sure to provide the same password as you set when creating the user earlier.

3. Restart Zabbix Agent

Now we need to restart the Zabbix agent so the changes take effect.

sudo service zabbix-agent2 restart

4. Configure Zabbix Server

Go to Montoring > Hosts (1) and click on the name of your MySQL/MariaDB server (2), before clicking on Configuration.


Go to the Templates part and click on Select (1).


Scroll through the list and find MySQL by Zabbix agent 2 and select it, before clicking Select (2).


You should now see the template you just selected (1), as well as your original Linux by Zabbix agent template (2).

Now click on the Macros tab (3).


Click on the Inherited and host macros toggle button.


Scroll down and find the variables {MYSQL.DSN}, {MYSQL.PASSWORD}, and {MYSQL.USER} (1). For each one click on Change (2) and then fill in a value (3).


For the {MYSQL.DSN}` (1), you want to put in tcp://localhost, although connecting over the socket directly should work in theory (not tested)

For the {MYSQL.PASSWORD} (2) you want to specify the password you set earlier for the agent.

For the {MYSQL.USER} (3) you want to set zbx_monitor, unless you used a different username.

When you have set all of those, press the Update button.


You should get a green bar at the top (1) stating that the Host updated, and be able to see that there is a lot more Latest data metrics (2). As always, wait a bit for them to actually come through.


That's it!

References

Last updated: 13th July 2023
First published: 13th July 2023