Traditional MySQL monitoring often starts with infrastructure metrics (CPU, RAM, disk) and a dashboard full of charts. That helps, but it doesn’t always answer the question you actually need in the moment: Is something important changing in my data or database behavior right now?
Query-based alerting solves this by turning read-only SQL queries into scheduled checks. Instead of staring at graphs, you get a notification only when a query result indicates a meaningful condition: replication lag passed a limit, deadlocks spiked, an orders table stopped receiving rows, or a critical job failed.
This guide explains how to monitor MySQL with query alerts using practical, low-noise patterns you can adapt to your environment.
Why query-based MySQL monitoring works
Metrics-based monitoring is excellent for capacity and infrastructure health. But database incidents frequently show up first as data symptoms:
- Background jobs are “green” but the output table hasn’t been updated.
- CPU looks fine, yet user requests are timing out due to lock contention.
- Replication is “running,” but lag is silently growing.
- Rows are accumulating in an error queue table with no one noticing.
Query alerts are a direct way to express these conditions. You define a query, decide what “bad” looks like (non-zero count, threshold exceeded, last update older than N minutes), and schedule it. Done well, this creates alerts that are:
- Actionable: tied to a specific, explainable SQL result.
- Low-noise: fewer false positives than generic thresholds.
- Closer to the user experience: they reflect real system outcomes.
Good alerts don’t describe everything that changed; they describe what you should do next.
Principles for low-noise MySQL query alerts
1) Prefer “symptom queries” over raw counters
Instead of alerting on “threads_running > 50,” alert on “requests are blocked on locks” or “p95 query time exceeded,” when possible.
2) Use counts and timestamps as alert triggers
Two patterns are especially robust across schemas:
- Count of bad rows: alert when count > 0 (or above a threshold).
- Staleness: alert when MAX(updated_at) is older than expected.
3) Add guardrails to reduce false positives
- Use time windows (last 5–15 minutes) rather than “all time.”
- Ignore expected maintenance windows or known batch hours.
- Alert on sustained issues (e.g., two consecutive checks) if your tooling supports it.
4) Keep queries read-only and least-privileged
Create a dedicated MySQL user for monitoring with minimal permissions, and avoid any query that can lock or scan massive tables unintentionally.
Core MySQL areas to cover with query alerts
Below are practical checks grouped by outcome. You can start with a handful and expand as you learn what signals matter most in your environment.
A) Replication lag and replica health
If you use replicas, the most user-visible failures often come from stale reads or delayed reporting. A common query-alert trigger is “seconds behind source” on a replica.
-- Run on a replica
SHOW SLAVE STATUS;Because SHOW SLAVE STATUS returns a row with many columns, your alerting logic typically checks:
Seconds_Behind_Masteris not null and > a threshold (e.g., 60).Slave_IO_RunningorSlave_SQL_Runningis not 'Yes'.
In newer MySQL versions with replication terminology updates, also consider SHOW REPLICA STATUS where applicable.
B) Lock contention and deadlocks
Deadlocks can be rare, spiky, and painful. A useful pattern is alerting when deadlocks appear within a short interval. If performance_schema is enabled, you can query recent events (exact tables can vary by version/config).
-- Example: detect recent deadlock-related errors in the last 10 minutes
SELECT COUNT(*) AS deadlock_like
FROM performance_schema.events_errors_summary_by_thread_by_error
WHERE ERROR_NAME LIKE '%deadlock%';If performance_schema tables aren’t available in your environment, a fallback is to alert on elevated lock waits using information_schema or InnoDB status snapshots (less precise, but still useful).
C) Slow queries that impact users
The goal is not “alert on every slow query” (that’s noisy). Instead, alert on a rate or presence of slow queries beyond a threshold.
If you have the slow query log enabled and shipped elsewhere, you can query your log store. But you can also monitor server-side indicators. For example, you can periodically check whether the slow query counter increased too fast (using SHOW GLOBAL STATUS), though implementing deltas depends on your alerting tool.
SHOW GLOBAL STATUS LIKE 'Slow_queries';When you can’t compute deltas, a pragmatic alternative is to alert on “currently running long queries” as a symptom:
SELECT COUNT(*) AS long_running
FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep'
AND TIME > 30;D) Storage growth and risk of running out of space
Infrastructure monitoring already covers disk, but query alerts can catch where growth is happening and whether it’s abnormal.
-- Top tables by size (schema-level visibility)
SELECT table_schema, table_name,
ROUND((data_length + index_length)/1024/1024, 2) AS size_mb
FROM information_schema.TABLES
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
ORDER BY (data_length + index_length) DESC
LIMIT 10;A low-noise alert: pick one or two critical tables (e.g., event logs, queues) and alert if size exceeds an expected ceiling or grows faster than normal.
E) Data freshness (staleness) for pipelines and jobs
Staleness checks are among the most actionable query alerts because they map directly to a broken workflow.
-- Example: alert if no new orders in the last 10 minutes
SELECT TIMESTAMPDIFF(MINUTE, MAX(created_at), NOW()) AS minutes_since_last_order
FROM orders;Alert when minutes_since_last_order exceeds your tolerance. This works for ingestion tables, ETL outputs, cache rebuild markers, and “last successful run” tables.
F) Error queues, retries, and failed jobs
Many systems have a table that represents failures (dead-letter queue, retries, job runs). Those tables are perfect for query alerts because “count > 0” usually means someone should act.
-- Example: failed background jobs in the last 15 minutes
SELECT COUNT(*) AS failures
FROM job_runs
WHERE status = 'failed'
AND finished_at > NOW() - INTERVAL 15 MINUTE;You can tune noise by grouping by error code and alerting only on new or high-severity types.
A practical starter set (with suggested intervals)
| Category | Query result to alert on | Typical interval | Why it helps |
|---|---|---|---|
| Replication | Lag > 60s or replica thread not running | 1–5 min | Prevents stale reads and delayed reporting |
| Locking | Long-running active sessions > threshold | 1–2 min | Finds contention before timeouts cascade |
| Performance | Long queries running > 30–60s | 1–5 min | Catches regressions and stuck queries |
| Freshness | MAX(updated_at) older than N minutes | 5–15 min | Detects broken pipelines and schedulers |
| Failures | Failed jobs count > 0 in recent window | 5 min | Turns silent failures into actionable alerts |
| Growth | Critical table size > ceiling | Daily / hourly | Prevents runaway storage and cost surprises |
Security and safety: how to run query alerts responsibly
Create a dedicated read-only user
At minimum, your monitoring user should only be able to connect and read what it needs. Exact grants depend on your checks, but a common baseline is read access to application schemas plus limited access to metadata views.
-- Example starting point (adjust to your needs)
CREATE USER 'monitor'@'%' IDENTIFIED BY 'strong_password';
GRANT SELECT ON your_app_db.* TO 'monitor'@'%';
GRANT SELECT ON information_schema.* TO 'monitor'@'%';
-- Only if needed for performance checks:
GRANT SELECT ON performance_schema.* TO 'monitor'@'%';
FLUSH PRIVILEGES;Use TLS and restrict network access
- Prefer encrypted connections (TLS) for any remote monitoring.
- Allowlist IPs/VPN egress, and avoid exposing MySQL publicly.
- Rotate credentials and audit what the monitoring user can see.
Keep queries lightweight
A monitoring query should be cheaper than the incident it’s trying to prevent. To avoid accidental load:
- Use indexed predicates and small time windows.
- Avoid full table scans on high-churn tables.
- Prefer aggregated results (COUNT/MAX) over selecting raw rows.
Designing alert messages that lead to action
Even a perfect SQL check can produce unhelpful alerts if the message lacks context. Aim to include:
- What happened: the condition and the measured value.
- Where: environment, cluster, replica name, or schema.
- What to do next: a first diagnostic query or playbook link.
For example:
- Replica lag is 185s (threshold 60s) on reporting-replica-2. Check network, IO thread, and source write load.
- No new rows in orders for 12 minutes. Check payment gateway status and order-ingestion worker.
Putting it together: a minimal rollout plan
- Start with 5–7 alerts covering freshness, failures, replication, and long-running queries.
- Route alerts to the right owner (data team vs app on-call) to prevent “someone else’s problem” noise.
- Review weekly: remove noisy checks, tune thresholds, and add one new high-value alert at a time.
- Document the response: one paragraph on what to check first saves hours during incidents.
Tooling note
You can implement query-based alerts in many ways (scheduled scripts, cron + SMTP, observability platforms). If you prefer a lightweight workflow from an iOS device—especially for read-only, scheduled checks with push/email notifications—apps like Query Alert are designed specifically around turning SQL and NoSQL queries into actionable alerts.