MySQL Monitoring with Grafana and Alloy
A step-by-step guide to monitoring MySQL with Grafana Alloy and Mimir: a least-privilege user, the right collectors, key queries and a ready-made dashboard.
Once the database is in production, the questions change. It is no longer "does it start?" but "how close are we to max connections?", "which queries are eating the time?" and "is the buffer pool big enough?". This post adds MySQL to the same Grafana stack I built in the earlier posts, so those answers sit next to the server and application metrics.
By the end you will have:
- A dedicated, read-only MySQL user for monitoring
- Alloy's built-in MySQL exporter shipping metrics to Mimir
- A set of queries worth knowing when the dashboard isn't enough
- The MySQL Overview dashboard imported into Grafana
Requirements
Before we start, you need two things in place:
- A running MySQL instance - see MySQL Production Setup for how to get there.
- Alloy installed and configured - see Grafana Monitoring with OSS Tools for that setup.
Alloy needs to run on the same server as MySQL for the configuration below, since it connects over 127.0.0.1. If your database lives elsewhere, change the host in the connection string and the user's host part to match.
Create a dedicated MySQL user for Alloy
It's good practice to give Alloy its own read-only monitoring user rather than reusing an application account. If the monitoring credentials leak, the worst someone can do is read status counters, not your data. Log in to MySQL:
sudo mysql -u root -p
Then create the user and grant it the necessary permissions:
CREATE USER 'alloy_monitor'@'localhost' IDENTIFIED BY 'StrongPassword123!' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'alloy_monitor'@'localhost';
GRANT SELECT ON performance_schema.* TO 'alloy_monitor'@'localhost';
FLUSH PRIVILEGES;
Use your own password, not the one from this post. Here is what each part is for:
| Grant | Why the exporter needs it |
|---|---|
PROCESS |
Reading the process list and InnoDB status |
REPLICATION CLIENT |
Reading replication status, so you can see replica lag |
SELECT ON *.* |
Reading information_schema tables, such as table sizes |
SELECT ON performance_schema.* |
Reading statement, file and memory statistics |
MAX_USER_CONNECTIONS 3 |
A cap, so a misbehaving scraper can never use up your connection pool |
The localhost host part means the user can only log in from the database server itself, which is all Alloy needs here.
Add MySQL configuration to Alloy
Open the Alloy config file:
sudo nano /etc/alloy/config.alloy
Append the following at the end:
prometheus.exporter.mysql "mysql" {
data_source_name = "alloy_monitor:StrongPassword123!@(127.0.0.1:3306)/"
enable_collectors = [
"global_status",
"global_variables",
"slave_status",
"info_schema.innodb_metrics",
"info_schema.processlist",
"info_schema.tables",
"perf_schema.eventsstatements",
"perf_schema.file_instances",
"perf_schema.memory_events",
]
}
prometheus.scrape "mysql" {
targets = prometheus.exporter.mysql.mysql.targets
forward_to = [prometheus.remote_write.mimir.receiver]
job_name = "mysql"
}
The first block runs the exporter inside Alloy, so there is no separate mysqld_exporter process to install. The second block scrapes it and forwards the samples to the prometheus.remote_write.mimir component from the base Alloy setup.
What the collectors give you
The exporter enables a small default set of collectors. The list above adds the ones I find useful on a single production server:
| Collector | What you get |
|---|---|
global_status |
The counters from SHOW GLOBAL STATUS: queries, connections, threads, slow queries, buffer pool reads |
global_variables |
Configuration values like max_connections, so you can compare usage against limits |
slave_status |
Replication state and lag. On a server without replicas it simply returns nothing |
info_schema.innodb_metrics |
InnoDB internals from INNODB_METRICS |
info_schema.processlist |
How many threads sit in each state, useful for spotting lock waits |
info_schema.tables |
Size and row estimates per table |
perf_schema.eventsstatements |
Per-query-digest statistics: how often a query runs and how long it takes |
perf_schema.file_instances |
I/O per file |
perf_schema.memory_events |
Memory usage per instrument |
Two of these deserve a warning. info_schema.tables queries metadata for every table, which gets slow on schemas with thousands of tables. perf_schema.eventsstatements creates a series per query digest. The exporter caps it to the top statements, but on an application with lots of distinct queries it is still the biggest source of series. If Mimir storage or scrape time becomes a problem, these are the first two to drop.
Keep the password out of the config file
Hardcoding the password works, but it puts the credential in a file that tends to get copied into backups and repos. Alloy can read it from an environment variable instead. Change the connection string to:
data_source_name = sys.env("MYSQL_EXPORTER_DSN")
Then add the variable to the environment file the Alloy service loads, on Debian and Ubuntu that is /etc/default/alloy:
MYSQL_EXPORTER_DSN="alloy_monitor:StrongPassword123!@(127.0.0.1:3306)/"
After saving, restart Alloy for the changes to take effect:
sudo service alloy restart
Check that metrics arrive
Before opening dashboards, confirm the pipeline works end to end. First, check that Alloy started without config errors:
sudo journalctl -u alloy -n 50 --no-pager
Errors about the MySQL component usually mean a wrong password or a missing grant. Then open Explore in Grafana, pick the Mimir data source and run:
mysql_up{job="mysql"}
A value of 1 means the exporter can log in to MySQL. A 0 means Alloy is running but the database login fails, so recheck the user and password. No result at all means the samples aren't reaching Mimir, so check the scrape and remote write blocks.
Alloy is now shipping MySQL metrics to Mimir, ready to be visualized in Grafana.
Queries worth knowing
The dashboard covers most needs, but a few queries are worth having at hand in Explore, and they are the base for alerts later:
| Question | Query |
|---|---|
| How busy is the server? | rate(mysql_global_status_queries[5m]) |
| How close are we to the connection limit? | mysql_global_status_threads_connected / mysql_global_variables_max_connections |
| Are slow queries increasing? | rate(mysql_global_status_slow_queries[5m]) |
| Is the buffer pool big enough? | rate(mysql_global_status_innodb_buffer_pool_reads[5m]) / rate(mysql_global_status_innodb_buffer_pool_read_requests[5m]) |
The last one is the share of reads that had to go to disk instead of memory. It should stay close to zero. If it climbs and stays up, the working set no longer fits in innodb_buffer_pool_size.
Grafana Dashboard
Rather than building a dashboard from scratch, you can import the official MySQL Overview dashboard directly from the Grafana dashboard library.
In Grafana, go to Dashboards → New → Import, enter the ID 7362 and click Load. Pick your Mimir data source when asked and import it. The host selector at the top lists every MySQL instance that reports metrics, so the same dashboard works if you add more servers later.
Some panels may stay empty. That usually means they rely on a collector that isn't enabled in the config above, not that something is broken.
Wrapping Up
This guide builds on the two previous posts and gives you a working MySQL monitoring setup in just a few steps: a least-privilege user, the exporter running inside Alloy, and a dashboard on top. From here, the natural next move is to set up some alerts so you get notified before small problems become big ones - Grafana Alerting for Your Production Stack walks through exactly that, including a ready-made MySQL max connections alert built on the same ratio as above. If you want to go further and add distributed tracing to complete the full observability picture, check out Completing the LGTM Stack with Grafana Tempo.
Thank you for reading, and let's connect!