Registering a Database Collector

Overview — what it does

Administration ▸ Collectors is InfraGuard's collector registry (the infr_collector table). It lifts hardcoded credentials and host maps out of code and stores them as data. Collectors are abstracted by access method (access).

This article covers the access=database collector. The InfraGuard app periodically connects to a registered DB and reads SHOW GLOBAL STATUS · information_schema.processlist · information_schema.tables, writing a time series to infr_db_metric. It never reads or writes your data rows — only load and structure metrics.

DB collectors are the source for the DB load monitoring screen and Alerts. Register one DB here and it appears on both automatically.

When to use

• You want to monitor connection saturation, throughput, slow queries, and schema size of live MySQL / MariaDB instances from a single console.
• You are onboarding a new DB server into monitoring.
• You want to reuse DB credentials already present in .env without re-entering the secret (cred_ref=env:PREFIX).
• You manage several DBs at once (shared defaults + item array) — use the grouped editor.

Layout

The collector list renders cards grouped by access method. access=database rows are folded into one "Database" group card (to avoid card sprawl). The group card's [Edit] opens the grouped editor (2 tabs).

Grouped editor tabs

Although the UI shows a single group card, saving upserts each item as its own db@{alias} row (access=database). The collection pipeline, Service ▸ Database, and alert targeting all operate per collector. Removing an item deletes its db@ row.

Per-collector fields (infr_collector)

How to use

1. Register a new DB (grouped editor)

1. Go to Administration ▸ Collectors.
2. On the "Database" group card, click [Edit] → Basic tab and fill shared values.
   • driver: mysql or mariadb
   • port: default 3306
   • database: just the default connection schema — leave empty to connect to information_schema (the collection scope is decided by the account's grants)
   • username: the monitoring account (e.g. infra_ro)
   • waiting threshold (slow_time): count queries running longer than this many seconds as "waiting/backed up" (1–60 s, default 2)
   • cred_ref: env:PREFIX to reuse .env credentials
3. Databases tab → [Add DB] → per item: alias (required) · host/IP · optional password.
4. [Save]. Each item is created/updated as a db@{alias} row.

2. Handling the secret — two paths

(A) Enter the password directly — typed into the password field, it is stored to secret_enc via encryption. The list and API responses never expose the plaintext; only has_secret/has_db_pw booleans are returned.

The password is write‑only. On edit, leaving the password field blank preserves the existing value (no overwrite). To remove it explicitly, use the clear option.

(B) Reuse .env credentials (cred_ref=env:PREFIX) — do not store the password in the DB; reference a credential set already in .env. DbCollectorService::resolveConn() reads these keys:

Values filled into config take precedence; empty ones are filled from PREFIX_* env (host/port/database/username/password).

3. Server-wide waiting queries — the PROCESS grant

The two core metrics (connection saturation = Threads_running/Threads_connected/max_connections, and QPS · slow rate = ΔQuestions/ΔSlow_queries) are collectible with a plain (USAGE) account. However, server-wide waiting-query aggregation and whole-schema size analysis require extra grants.

• Without PROCESS → only your own connection's processlist is visible (waiting_scope=0).
• With PROCESS → all sessions are aggregated (waiting_scope=1, server-wide).

A least-privilege account script to run once as root on each target DB host: sql/infr_db_monitor_grant.sql.

-- Dedicated read-only account (narrow the source host to the InfraGuard app server IP)
CREATE USER 'infra_ro'@'%' IDENTIFIED BY 'CHANGE_ME_TO_A_STRONG_SECRET';

-- Global observation grants (NOT data access)
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'infra_ro'@'%';

-- Schema size analysis (all DBs · read-only). For one DB only, narrow to design.*
GRANT SELECT ON *.* TO 'infra_ro'@'%';

-- Blast-radius isolation — prevent connection monopolization (lesson from the 2026-08-06 [1040] incident)
ALTER USER 'infra_ro'@'%' WITH MAX_USER_CONNECTIONS 5;
FLUSH PRIVILEGES;

MariaDB 10.5+ can use BINLOG MONITOR instead of REPLICATION CLIENT. PROCESS and SELECT are identical.

4. Run and verify collection

Collection is performed by the infr:collect-db artisan command. The dev launcher (composer dev) scheduler pane calls it every minute.

# Sample one collector once immediately (bypass the ops-policy gate)
php artisan infr:collect-db --key=db@blue --force

# Collect all registered DBs (is_run=1) subject to policy
php artisan infr:collect-db

Collection gating is decided by Operations Policy.

Use --key to test a specific database collector. The [Test] / [Sync code] buttons are for access=web collectors only and do not apply to database collectors — verify with the command above.

5. Status thresholds

DbCollectorService::statusOf() judges up/degraded against thresholds from Operations Policy.

Data sources

No connection leaks. DbCollectorService::withConn() creates a runtime connection per sample and closes it immediately in finally via DB::purge. A PDO timeout (default 5 s) keeps web requests/schedules from blocking on remote latency. Never spawn an infinite-loop daemon from the scheduler — it exhausts the shared MySQL connection pool (the 2026‑08‑06 [1040] Too many connections incident).

Access

• Screen access and all CRUD: admin (level ≥ 10) — CollectorController::isAdmin() checks session infr_user_level >= 10 (demo=1 and staff=5 cannot enter).
• On failure, 403. Save, delete, and fill are all audit-logged to infr_audit_log/logs.
• fill/{key} (injecting config into an Inventory ▸ Tools mashup) is a secret-injection exception (documented §Option B) — admin only + audited.

See RBAC levels for the full scheme.

Troubleshooting & FAQ

• Waiting queries only show my own connection — no PROCESS grant, so waiting_scope=0. Grant it via sql/infr_db_monitor_grant.sql.
• Schema size shows only some DBs — you need a global SELECT grant (or SELECT on the specific DB).
• Changed the password but it didn't take — an empty password field preserves the existing value on edit. Enter a new value to replace it.
• Card briefly shows down (red) in economy mode — collection lagged because no console user is present. Entering the screen triggers an on-demand sample (ensureFresh), or switch to always mode (see Service ▸ Database false-red avoidance).
• I want to change the key — the db@{alias} key is immutable on edit (for alert-targeting stability). Add an item with a new alias to mint a new key.
• I'm level 5 but access is denied — some copy reads "level 5+", but the actual gate is admin (level ≥ 10).

Related

• Service ▸ Database — the DB load/structure monitoring screen
• DB tuning education — lock/cache/index concepts
• Infra ▸ Alerts — DB-condition alerts (waiting queue · slow queries)
• Operations Policy — collection mode, interval, status thresholds
• RBAC levels — the admin level gate
• Audit log — collector change history