Monitoring MySQL and MariaDB

MetricHarbor reads server counters through SHOW GLOBAL STATUS and SHOW GLOBAL VARIABLES, replication state through SHOW REPLICA STATUS, and — optionally — per-schema sizes through information_schema.TABLES.

That last one forces a choice MySQL gives you no way to avoid. Decide it deliberately rather than discovering it.

The access trade-off#

information_schema.TABLES only lists tables the user holds some privilege on. So a monitoring user with no SELECT gets zero rows and no error — the check reports a clean success with no schema sizes at all. Verified on MariaDB 11.8.6: a user with only the replication grant sees exactly 0 user tables there, and the query succeeds. That silent emptiness reads like a bug in MetricHarbor, which is why it is the first thing on this page.

PostgreSQL has a role that grants statistics without data access. MySQL does not. So:

Option A — no access to any data (recommended)#

MySQL:

CREATE USER 'metricharbor'@'localhost' IDENTIFIED BY 'a-long-random-password';
GRANT REPLICATION CLIENT ON *.* TO 'metricharbor'@'localhost';
FLUSH PRIVILEGES;

MariaDB — the grant is different, and REPLICATION CLIENT is not enough:

DROP USER IF EXISTS 'metricharbor'@'localhost';
CREATE USER 'metricharbor'@'localhost' IDENTIFIED BY 'a-long-random-password';
GRANT SLAVE MONITOR ON *.* TO 'metricharbor'@'localhost';
FLUSH PRIVILEGES;

MariaDB 10.5 split the old REPLICATION CLIENT privilege in two — BINLOG MONITOR for binary log status, and SLAVE MONITOR for SHOW REPLICA STATUS — and kept REPLICATION CLIENT as an alias for BINLOG MONITOR only. So granting REPLICATION CLIENT on MariaDB is accepted, shows up in SHOW GRANTS under a different name, and still leaves SHOW REPLICA STATUS refused with:

ERROR 1227 (42000): Access denied; you need (at least one of) the SLAVE MONITOR privilege(s)

Verified on MariaDB 11.8.6. The collector reports that refusal as a warning on every poll rather than failing the check, so the symptom is replication metrics that never appear while everything else looks healthy.

Then turn off "Per-schema sizes" on the check.

This user can read server counters and replication state, and nothing else: not a row of data, not a schema name. You lose per-schema size metrics.

Option B — per-schema sizes, at the cost of read access#

DROP USER IF EXISTS 'metricharbor'@'localhost';
CREATE USER 'metricharbor'@'localhost' IDENTIFIED BY 'a-long-random-password';
-- MySQL:
GRANT SELECT, REPLICATION CLIENT ON *.* TO 'metricharbor'@'localhost';
-- MariaDB:
GRANT SELECT, SLAVE MONITOR ON *.* TO 'metricharbor'@'localhost';
FLUSH PRIVILEGES;

DROP USER IF EXISTS first, on purpose. Running CREATE USER for an account that already exists fails with

ERROR 1396 (HY000): Operation CREATE USER failed for 'metricharbor'@'localhost'

which says nothing about why. Dropping first makes the whole block safe to re-run — on a second attempt, after a typo, or on a host somebody else already set up.

Not CREATE USER IF NOT EXISTS, which looks tidier and hides a worse problem: if the account exists with a different password, that form succeeds, changes nothing, and leaves you with a password that is not the one you just typed. The failure then arrives later as an authentication error with no connection to this step.

SELECT ON *.* makes information_schema.TABLES complete. It also lets that user read every row in the server. If you take this option, the argument for keeping the credential off MetricHarbor's servers gets stronger, not weaker — and it already is off them.

The replication grant — REPLICATION CLIENT on MySQL, SLAVE MONITOR on MariaDB — is needed only for SHOW REPLICA STATUS. If you turn the replication option off on the check as well, Option A needs no grant at all beyond the account existing.

Let the agent reach it#

If the agent runs on the database host:

# /etc/mysql/mysql.conf.d/mysqld.cnf
bind-address = 127.0.0.1

Prefer 'metricharbor'@'localhost' over 'metricharbor'@'%' and point the target at 127.0.0.1. A monitoring account that can log in from anywhere is a wider surface than it needs to be.

Supply the password and add the check#

runuser -u metricharbor -- metricharbor-agent cred add mysql-monitor --type database_password

Then Targets → Add target → MySQL / MariaDB database (host 127.0.0.1, port 3306, the credential, and the agent on that host), and Add a check → MySQL / MariaDB on the target page.

The database name is optional here, unlike PostgreSQL: everything this collector reads is server-wide.

What it reads#

MariaDB and older MySQL use SHOW SLAVE STATUS; the collector tries the modern name first and falls back, so you do not configure anything for this.

Options on the check#

What you should see#

success with a sample count, and counters under Latest readings. Most MySQL metrics are single-series; the per-label-set shape earns its keep on schemas, and on disks and pods elsewhere.

More in When nothing is collecting.