Database monitoring: MySQL and PostgreSQL

Everything runs on web01 (192.168.56.81, 1 GB RAM): MySQL 8.4 and PostgreSQL from the Ubuntu archive, plus agent 2 which is already there. The frontend steps happen on zbx01. If memory is tight, do the MySQL part, stop MySQL, then do PostgreSQL.

Outputs from the database clients were captured from MySQL 8.0 and PostgreSQL 16 in a test container; the messages are the same on MySQL 8.4 and on PostgreSQL 18, which Ubuntu 26.04 ships.

1Install MySQL on web01

Run this on web01:

sudo apt install -y mysql-server
sudo systemctl status mysql --no-pager | head -3
sudo mysql -e "select version();"

You should see this (your times will differ):

● mysql.service - MySQL Community Server
     Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; preset: enabled)
     Active: active (running) since Sun 2026-11-08 15:02:11 IST; 8s ago
version()
8.4.11-0ubuntu0.26.04.1

Ubuntu’s MySQL listens on 127.0.0.1:3306 and on the socket /var/run/mysqld/mysqld.sock, and root logs in through the socket with sudo mysql, without a password. That’s all you need, because agent 2 runs on the same host.

✅ Checkpoint: sudo mysql on web01 shows a MySQL 8.4.x version.

2Create the zbx_monitor user with minimal grants

Run this on web01:

sudo mysql <<'EOF'
CREATE USER 'zbx_monitor'@'localhost' IDENTIFIED BY 'Zbx@Mon2026';
CREATE USER 'zbx_monitor'@'127.0.0.1' IDENTIFIED BY 'Zbx@Mon2026';
GRANT REPLICATION CLIENT, PROCESS, SHOW DATABASES, SHOW VIEW ON *.* TO 'zbx_monitor'@'localhost';
GRANT REPLICATION CLIENT, PROCESS, SHOW DATABASES, SHOW VIEW ON *.* TO 'zbx_monitor'@'127.0.0.1';
SHOW GRANTS FOR 'zbx_monitor'@'127.0.0.1';
EOF
mysql -uzbx_monitor -p'Zbx@Mon2026' -h127.0.0.1 -e "show global status like 'Threads_connected'"

You should see:

Grants for zbx_monitor@127.0.0.1
GRANT PROCESS, SHOW DATABASES, REPLICATION CLIENT, SHOW VIEW ON *.* TO `zbx_monitor`@`127.0.0.1`
mysql: [Warning] Using a password on the command line interface can be insecure.
Variable_name	Value
Threads_connected	2

These four privileges are what the official template README asks for, and none of them can read or change your application data. Two accounts are needed because MySQL treats a socket login (localhost) and a TCP login from 127.0.0.1 as different hosts. The official README uses 'zbx_monitor'@'%' for remote agents; on a real server, limit it to the hosts that will connect.

✅ Checkpoint: mysql as zbx_monitor over 127.0.0.1 returns Threads_connected.

3Test the plugin from the agent

Before touching the frontend, ask agent 2 directly. zabbix_agent2 -t runs one key locally with the real plugin.

Run this on web01:

sudo zabbix_agent2 -t 'mysql.ping[tcp://127.0.0.1:3306,zbx_monitor,Zbx@Mon2026]'
sudo zabbix_agent2 -t 'mysql.version[tcp://127.0.0.1:3306,zbx_monitor,Zbx@Mon2026]'

You should see:

mysql.ping[tcp://127.0.0.1:3306,zbx_monitor,Zbx@Mon2026][s|1]
mysql.version[tcp://127.0.0.1:3306,zbx_monitor,Zbx@Mon2026][s|8.4.11-0ubuntu0.26.04.1]

The [s|1] means a string result of 1, so the plugin connected and ran SELECT 1. Testing this way takes the server, proxy and template out of the picture, so if it fails here the problem is on web01.

✅ Checkpoint: zabbix_agent2 -t returns [s|1] for mysql.ping and the MySQL version for mysql.version.

4Link the MySQL template and set macros

In Data collection > Hosts open web01, Templates: link MySQL by Zabbix agent 2. On the Macros tab add:

Click Update. The template uses passive agent items, so web01’s host needs its agent interface (it has one from Lesson 4). If you’d rather not keep passwords in Zabbix at all, define a named session in agent 2 instead, put the session name in {$MYSQL.DSN} and leave user and password empty.

Optional named session, on web01:

sudo tee /etc/zabbix/zabbix_agent2.d/plugins.d/mysql-lab.conf >/dev/null <<'EOF'
Plugins.Mysql.Sessions.Lab.Uri=tcp://127.0.0.1:3306
Plugins.Mysql.Sessions.Lab.User=zbx_monitor
Plugins.Mysql.Sessions.Lab.Password=Zbx@Mon2026
EOF
sudo chown root:zabbix /etc/zabbix/zabbix_agent2.d/plugins.d/mysql-lab.conf
sudo chmod 640 /etc/zabbix/zabbix_agent2.d/plugins.d/mysql-lab.conf
sudo systemctl restart zabbix-agent2

✅ Checkpoint: web01 has “MySQL by Zabbix agent 2” linked and the three MySQL macros set.

5Read the MySQL numbers in Latest data

After two or three minutes open Monitoring > Latest data and set Hosts to web01. In 8.0 the template’s item names have no “MySQL:” prefix, so type a name such as Threads or Status in the Name filter, or sort by Name and look for these:

The “Size of database” lines come from Database discovery, so they appear a little later. Buffer pool utilization is low on a lab VM because there is almost no data; on a real server it should sit well above the 50% that {$MYSQL.BUFF_UTIL.MIN.WARN} checks.

✅ Checkpoint: Latest data shows Status Up (1) and Threads connected for web01.

6Make a slow query and watch it

Set the slow query threshold low and run a few slow queries so the counter moves.

Run this on web01:

sudo mysql -e "SET GLOBAL long_query_time = 1;"
for i in 1 2 3 4 5; do sudo mysql -e "SELECT SLEEP(2);" >/dev/null; done
mysql -uzbx_monitor -p'Zbx@Mon2026' -h127.0.0.1 -e "show global status like 'Slow_queries'" 2>/dev/null

You should see:

Variable_name	Value
Slow_queries	5

In Latest data “Slow queries per second” shows a small positive rate for one interval. The template’s trigger “MySQL: Server has slow queries” needs more than {$MYSQL.SLOW_QUERIES.MAX.WARN} (3) per second for 5 minutes, so five queries won’t fire it, which is how you want a production trigger to behave. Set long_query_time back to 10 afterwards.

✅ Checkpoint: Slow_queries is 5 or more and the Slow queries per second graph shows a small spike.

7PostgreSQL: install, plugin and monitoring role

Run this on web01:

sudo apt install -y postgresql zabbix-agent2-plugin-postgresql
sudo -u postgres psql <<'EOF'
CREATE USER zbx_monitor WITH PASSWORD 'Zbx@Mon2026' INHERIT;
GRANT pg_monitor TO zbx_monitor;
EOF
sudo systemctl restart zabbix-agent2
PGPASSWORD='Zbx@Mon2026' psql -h 127.0.0.1 -U zbx_monitor -d postgres -tAc "select count(*) from pg_stat_activity; select pg_is_in_recovery();"
sudo zabbix_agent2 -t 'pgsql.ping[tcp://localhost:5432,zbx_monitor,Zbx@Mon2026,postgres]'

You should see this (the connection count will differ):

CREATE ROLE
GRANT ROLE
6
f
pgsql.ping[tcp://localhost:5432,zbx_monitor,Zbx@Mon2026,postgres][s|1]

pg_monitor is a built-in role that can read all pg_stat_* views and settings without touching table data. Ubuntu’s default pg_hba.conf already allows password (scram-sha-256) logins over 127.0.0.1, so no edit is needed here. The f means this server is not a standby.

Then check what the plugin package installed. Run this on web01:

apt-cache policy zabbix-agent2-plugin-postgresql | head -3
dpkg -L zabbix-agent2-plugin-postgresql | grep -E '\.conf$|libexec/zabbix/'
sudo grep -v '^#' /etc/zabbix/zabbix_agent2.d/plugins.d/postgresql.conf | grep .

You should see:

zabbix-agent2-plugin-postgresql:
  Installed: 1:8.0.0-1+ubuntu26.04
  Candidate: 1:8.0.0-1+ubuntu26.04
/etc/zabbix/zabbix_agent2.d/plugins.d/postgresql.conf
/usr/libexec/zabbix/zabbix-agent2-plugin-postgresql
Plugins.PostgreSQL.System.Path=/usr/libexec/zabbix/zabbix-agent2-plugin-postgresql

The package comes from the Zabbix 8.0 stable repository for Ubuntu 26.04 and adds two files that matter: the plugin binary in /usr/libexec/zabbix/ and its settings file in plugins.d, which agent 2 already reads through its Include line. The only active line in that file tells agent 2 where the binary is; everything else in it is a commented default you can change for timeouts or named sessions.

✅ Checkpoint: psql as zbx_monitor works and zabbix_agent2 -t returns [s|1] for pgsql.ping.

8Link the PostgreSQL template

On web01 link PostgreSQL by Zabbix agent 2 and add macro {$PG.PASSWORD} = Zbx@Mon2026 as Secret text. The template’s defaults already use tcp://localhost:5432, user zbx_monitor and database postgres. After a few minutes check Latest data for web01 and look for items such as Ping and Connections sum: Total (no “PostgreSQL:” prefix in 8.0).

✅ Checkpoint: Latest data for web01 shows Ping Up (1) and Connections sum: Total from the PostgreSQL template.