Monitoring

The MYSQL module plugs into Pigsty’s observability stack: metrics flow through mysqld_exporter into VictoriaMetrics, error logs flow through Journald/Vector into VictoriaLogs, Grafana ships 5 dashboards, and vmalert loads 68 recording rules plus 27 alert rules.

Collection Architecture

Each MySQL node runs one mysqld_exporter (port 9104) using the least-privilege monitor account (dbuser_monitor@'127.0.0.1'). Deployment writes a file-based service-discovery target on the Infra node:

/infra/targets/mysql/<instance>.yml     # e.g. my-test-1.yml

The VictoriaMetrics mysql scrape job consumes this directory. mysql_exporter_enabled: false converges the target to an empty list; only mysql-rm.yml deletes target files.

Enabled collectors include global status/variables, binlog size, InnoDB metrics, the process list, performance-schema statement digests (top 50), table/index I/O waits, and MGR membership plus replication statistics.

Label Model

All MySQL metrics carry a consistent label set:

Derived rules are named mysql:ins:* (instance level) and mysql:cls:* (cluster level); the full dictionary is in Metrics.

Grafana Dashboards

Cluster health at a glance: mysql:cls:health is 2 (healthy) / 1 (degraded but writable) / 0 (critical or unwritable) — the Overview’s Healthy Clusters stat and Cluster Health timeline are built on it.

The Group Replication dashboard is only meaningful for innodb_cluster topologies; selecting a standalone cluster legitimately shows No data on MGR panels.

Alert Rules

The 27 alert rules are tiered by severity (CRIT / WARN / INFO). The ones to page on:

Availability and Cluster State

Capacity and Performance

Connection pressure (MySQLConnectionsHigh WARN at 80% / MySQLConnectionsCritical CRIT at 95%), replication queues (MySQLGRQueueHigh WARN / MySQLGRQueueCritical CRIT), flow control (MySQLGRFlowControlHigh), InnoDB signals (MySQLBufferPoolWaits, MySQLInnoDBLogWaits, MySQLRedoCapacityHigh, MySQLDeadlocksHigh, MySQLHistoryListLarge), and INFO-level hints for slow queries, disk temp tables, full joins, buffer-pool hit ratio, and recent restarts.

Observed behavior from testing: a primary crash-failover (~20s) only produces pending alerts, no false pages; a genuine complete outage drives ClusterNoPrimary and QuorumLost to firing within 2 minutes.

Log Queries

MySQL error logs are written twice: to /var/log/mysql/error.log and via syslog → Journald → Vector → VictoriaLogs. Entries carry app=mysqld-<instance>, so the dashboard log panels work out of the box, and LogsQL queries are straightforward:

# Recent errors from one instance
curl -s http://<infra>:9428/select/logsql/query \
  -d 'query=app:mysqld-my-test-1 level:err _time:1h'

# All MySQL-related logs for a cluster, including backup runs
curl -s http://<infra>:9428/select/logsql/query \
  -d 'query=job:syslog cls:my-test (app:~"mysqld-" OR unit:mysql-backup) _time:1h | limit 100'

Note the log cls label is the node cluster name (node_cluster) — keep node_cluster aligned with mysql_cluster, as the configuration examples do, so metric and log labels agree.

Known boundaries:

• The slow query log (slow.log, 1s threshold) stays on local disk and is not shipped to VictoriaLogs — inspect it on the instance, or use the statement-digest metrics (mysql:ins:statement_latency and friends);
• Router runtime logs live in /var/log/mysqlrouter/ and are also local-only.

Verify the Pipeline

Self-check every hop after deployment:

# Exporter itself
curl -s http://<member>:9104/metrics | grep -E '^mysql_up '

# VictoriaMetrics scrape and recording rules
curl -s 'http://<infra>:8428/api/v1/query?query=mysql_up'
curl -s 'http://<infra>:8428/api/v1/query?query=mysql:cls:health'

# vmalert rule groups (expect mysql-rules and mysql-alerts)
curl -s 'http://<infra>:8880/api/v1/rules' | grep -o '"name":"mysql-[a-z]*"'

# Log ingestion
curl -s 'http://<infra>:9428/select/logsql/query' -d 'query=app:~"mysqld-" _time:1h | stats by (app) count()'