--- title: "How to Monitor MySQL and PostgreSQL with Prometheus" description: "Install mysqld_exporter and postgres_exporter on Ubuntu 24.04 with least-privilege users, scrape them with Prometheus and add alerts for database health." category: "Observability Stack" language: en source: https://cubepath.com/docs/observability-stack/mysql-postgresql-exporters-for-prometheus updated: 2026-09-25 --- # How to Monitor MySQL and PostgreSQL with Prometheus Prometheus cannot talk to a database directly, so it relies on exporters: small services that connect to the database, read its internal status and expose it as metrics over HTTP. `mysqld_exporter` does this for MySQL and MariaDB, and `postgres_exporter` for PostgreSQL. In this tutorial you will install both exporters on Ubuntu 24.04, connect them with dedicated read-only monitoring users, scrape them from Prometheus and add alert rules for the problems that matter most: a database that is down, running out of connections or lagging behind its primary. ## Prerequisites To follow this tutorial, you will need: - A server running Ubuntu 24.04 LTS, such as a CubePath VPS, with a non-root user with `sudo` privileges. - MySQL 8.0 (`sudo apt install mysql-server`) and/or PostgreSQL 16 (`sudo apt install postgresql`) running on that server. You can follow only the section for the database you use. - Prometheus installed and configured in `/etc/prometheus/prometheus.yml`. It can run on the same server or on another host that can reach the exporter ports. Each exporter runs on the database server itself and connects over the local socket, so the database never needs to listen on a public interface. ## Step 1 - Creating a MySQL monitoring user The exporter needs a MySQL account that can read server status but cannot change data. Open a MySQL shell as root: ```bash sudo mysql ``` Create the user and grant only the privileges the exporter uses. Replace `your_strong_password` with a long random password: ```sql CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'your_strong_password' WITH MAX_USER_CONNECTIONS 3; GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost'; EXIT; ``` `PROCESS` lets the exporter read the process list and InnoDB status, `REPLICATION CLIENT` gives access to replication status, and `SELECT` is needed for the `performance_schema` and `information_schema` tables. `MAX_USER_CONNECTIONS 3` keeps a misbehaving exporter from using up your connection limit. Test the account: ```bash mysql -u exporter -p -e "SHOW GLOBAL STATUS LIKE 'Uptime';" ``` ```output +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Uptime | 86231 | +---------------+-------+ ``` ## Step 2 - Installing mysqld_exporter Download the latest release from the [mysqld_exporter releases page](https://github.com/prometheus/mysqld_exporter/releases). Set the version you want in a variable (on ARM servers use `linux-arm64` instead of `linux-amd64`): ```bash VERSION=0.17.2 cd /tmp curl -LO "https://github.com/prometheus/mysqld_exporter/releases/download/v${VERSION}/mysqld_exporter-${VERSION}.linux-amd64.tar.gz" curl -LO "https://github.com/prometheus/mysqld_exporter/releases/download/v${VERSION}/sha256sums.txt" sha256sum -c --ignore-missing sha256sums.txt ``` ```output mysqld_exporter-0.17.2.linux-amd64.tar.gz: OK ``` Extract the binary, install it and create a system user for the service: ```bash tar xzf "mysqld_exporter-${VERSION}.linux-amd64.tar.gz" sudo install -m 0755 "mysqld_exporter-${VERSION}.linux-amd64/mysqld_exporter" /usr/local/bin/ sudo useradd --system --no-create-home --shell /usr/sbin/nologin mysqld_exporter ``` The exporter reads its credentials from a MySQL option file. Create it: ```bash sudo mkdir -p /etc/mysqld_exporter sudo nano /etc/mysqld_exporter/my.cnf ``` ```ini [client] user=exporter password=your_strong_password socket=/var/run/mysqld/mysqld.sock ``` Connecting through the Unix socket matches the `'exporter'@'localhost'` account and avoids TLS and authentication plugin issues that appear over TCP. Restrict the file so only the service user can read the password: ```bash sudo chown -R root:mysqld_exporter /etc/mysqld_exporter sudo chmod 0750 /etc/mysqld_exporter sudo chmod 0640 /etc/mysqld_exporter/my.cnf ``` Create the systemd unit: ```bash sudo nano /etc/systemd/system/mysqld_exporter.service ``` ```ini [Unit] Description=Prometheus MySQL Exporter Wants=network-online.target After=network-online.target mysql.service [Service] User=mysqld_exporter Group=mysqld_exporter ExecStart=/usr/local/bin/mysqld_exporter \ --config.my-cnf=/etc/mysqld_exporter/my.cnf \ --web.listen-address=:9104 Restart=on-failure NoNewPrivileges=true [Install] WantedBy=multi-user.target ``` Start the exporter and check that it is running: ```bash sudo systemctl daemon-reload sudo systemctl enable --now mysqld_exporter sudo systemctl status mysqld_exporter ``` Then confirm it can reach MySQL. The `mysql_up` metric is `1` when the last connection succeeded: ```bash curl -s http://localhost:9104/metrics | grep -E '^mysql_(up|global_status_threads_connected|global_variables_max_connections) ' ``` ```output mysql_global_status_threads_connected 3 mysql_global_variables_max_connections 151 mysql_up 1 ``` If `mysql_up` is `0`, the log explains why (wrong password, missing privilege, socket path): `sudo journalctl -u mysqld_exporter -n 30`. ## Step 3 - Creating a PostgreSQL monitoring role PostgreSQL includes a built-in `pg_monitor` role that grants read access to all statistics views without access to table data. Create a role with the same name as the system user the exporter will run as, so it can log in through the local socket with peer authentication and no password: ```bash sudo -u postgres psql -c "CREATE ROLE postgres_exporter WITH LOGIN;" sudo -u postgres psql -c "GRANT pg_monitor TO postgres_exporter;" ``` ```output CREATE ROLE GRANT ROLE ``` Ubuntu's default `pg_hba.conf` contains `local all all peer`, which lets the operating system user `postgres_exporter` connect as the database role of the same name. Create that system user now: ```bash sudo useradd --system --no-create-home --shell /usr/sbin/nologin postgres_exporter ``` Verify the login works: ```bash sudo -u postgres_exporter psql -d postgres -c "SELECT count(*) FROM pg_stat_activity;" ``` ```output count ------- 6 (1 row) ``` ## Step 4 - Installing postgres_exporter Download the latest release from the [postgres_exporter releases page](https://github.com/prometheus-community/postgres_exporter/releases): ```bash VERSION=0.17.1 cd /tmp curl -LO "https://github.com/prometheus-community/postgres_exporter/releases/download/v${VERSION}/postgres_exporter-${VERSION}.linux-amd64.tar.gz" curl -LO "https://github.com/prometheus-community/postgres_exporter/releases/download/v${VERSION}/sha256sums.txt" sha256sum -c --ignore-missing sha256sums.txt tar xzf "postgres_exporter-${VERSION}.linux-amd64.tar.gz" sudo install -m 0755 "postgres_exporter-${VERSION}.linux-amd64/postgres_exporter" /usr/local/bin/ ``` The exporter reads the connection string from the `DATA_SOURCE_NAME` environment variable. Because it uses peer authentication over the socket, the connection string contains no password. Create the unit file: ```bash sudo nano /etc/systemd/system/postgres_exporter.service ``` ```ini [Unit] Description=Prometheus PostgreSQL Exporter Wants=network-online.target After=network-online.target postgresql.service [Service] User=postgres_exporter Group=postgres_exporter Environment="DATA_SOURCE_NAME=host=/var/run/postgresql user=postgres_exporter dbname=postgres sslmode=disable" ExecStart=/usr/local/bin/postgres_exporter --web.listen-address=:9187 Restart=on-failure NoNewPrivileges=true [Install] WantedBy=multi-user.target ``` If you prefer password authentication over TCP, use a URL instead, for example `postgresql://postgres_exporter:your_strong_password@localhost:5432/postgres?sslmode=disable`, and move it to a file referenced with `EnvironmentFile=` and mode `0600` so the password does not appear in `systemctl show`. Start the service and check the metrics: ```bash sudo systemctl daemon-reload sudo systemctl enable --now postgres_exporter curl -s http://localhost:9187/metrics | grep -E '^pg_up |^pg_stat_database_numbackends\{datname="postgres"' ``` ```output pg_stat_database_numbackends{datid="5",datname="postgres"} 1 pg_up 1 ``` ## Step 5 - Opening the ports to Prometheus If Prometheus runs on the same server, skip this step. Otherwise allow the exporter ports only from the Prometheus server's IP address, replacing `prometheus_server_ip`: ```bash sudo ufw allow from prometheus_server_ip to any port 9104 proto tcp sudo ufw allow from prometheus_server_ip to any port 9187 proto tcp sudo ufw status ``` The metrics expose details about your databases (names, sizes, settings), so never leave these ports open to the whole internet. ## Step 6 - Adding the exporters to Prometheus On the Prometheus server, edit the configuration: ```bash sudo nano /etc/prometheus/prometheus.yml ``` Add two jobs under `scrape_configs:`, replacing `db_server_ip` with the database server address (or `localhost`): ```yaml - job_name: mysql static_configs: - targets: ['db_server_ip:9104'] labels: environment: production - job_name: postgres static_configs: - targets: ['db_server_ip:9187'] labels: environment: production ``` Validate the file and restart Prometheus: ```bash promtool check config /etc/prometheus/prometheus.yml sudo systemctl restart prometheus ``` In the Prometheus web interface, open **Status > Targets**: both jobs should be `UP`. Then run these queries in the expression browser to see useful values: | Query | What it shows | | --- | --- | | `rate(mysql_global_status_queries[5m])` | MySQL queries per second | | `mysql_global_status_threads_connected / mysql_global_variables_max_connections` | Fraction of MySQL connections in use | | `rate(mysql_global_status_slow_queries[5m])` | Slow queries per second | | `sum by (datname) (pg_stat_database_numbackends)` | PostgreSQL connections per database | | `rate(pg_stat_database_xact_commit{datname="your_db"}[5m])` | Committed transactions per second | | `pg_database_size_bytes` | Size of each PostgreSQL database | ## Step 7 - Creating alert rules Create a rules file on the Prometheus server: ```bash sudo mkdir -p /etc/prometheus/rules sudo nano /etc/prometheus/rules/databases.yml ``` ```yaml groups: - name: databases rules: - alert: MySQLDown expr: mysql_up == 0 for: 1m labels: severity: critical annotations: summary: "MySQL on {{ $labels.instance }} is down" - alert: MySQLTooManyConnections expr: mysql_global_status_threads_connected / mysql_global_variables_max_connections > 0.8 for: 5m labels: severity: warning annotations: summary: "MySQL on {{ $labels.instance }} uses more than 80% of max_connections" - alert: MySQLReplicationLag expr: mysql_slave_status_seconds_behind_master > 300 for: 5m labels: severity: warning annotations: summary: "MySQL replica {{ $labels.instance }} is {{ $value }}s behind" - alert: PostgreSQLDown expr: pg_up == 0 for: 1m labels: severity: critical annotations: summary: "PostgreSQL on {{ $labels.instance }} is down" - alert: PostgreSQLTooManyConnections expr: sum by (instance) (pg_stat_database_numbackends) / on (instance) pg_settings_max_connections > 0.8 for: 5m labels: severity: warning annotations: summary: "PostgreSQL on {{ $labels.instance }} uses more than 80% of max_connections" - alert: PostgreSQLDeadlocks expr: increase(pg_stat_database_deadlocks[10m]) > 0 labels: severity: warning annotations: summary: "Deadlocks detected in {{ $labels.datname }} on {{ $labels.instance }}" ``` The `mysql_up` and `pg_up` metrics come from the exporter, so they stay available when the database is down and are the right signal for a "database down" alert. The replication rule only returns data on MySQL replicas and is silent on a primary. On a replica, confirm the exact lag metric name your exporter version exposes with `curl -s http://localhost:9104/metrics | grep seconds_behind` and adjust the expression if it differs. Make sure `/etc/prometheus/prometheus.yml` loads the rules directory: ```yaml rule_files: - /etc/prometheus/rules/*.yml ``` Check and apply: ```bash promtool check rules /etc/prometheus/rules/databases.yml sudo systemctl restart prometheus ``` ```output Checking /etc/prometheus/rules/databases.yml SUCCESS: 6 rules found ``` To test the `MySQLDown` alert, stop MySQL with `sudo systemctl stop mysql`: after about a minute `mysql_up` becomes `0` and the alert fires. Start it again with `sudo systemctl start mysql`. ## Step 8 - Adding Grafana dashboards If you use Grafana with Prometheus as a data source, you do not need to build dashboards from scratch. Go to **Dashboards > New > Import**, enter dashboard ID `9628` (PostgreSQL Database) and select your Prometheus data source. For MySQL, search the [Grafana dashboard catalog](https://grafana.com/grafana/dashboards/) for `mysqld_exporter` and import one that is maintained for current exporter versions. Metric names changed between major exporter releases, so empty panels usually mean the dashboard targets an older version. ## Troubleshooting - **`mysql_up 0` with `Access denied`**: the password in `my.cnf` does not match or the user was created for a different host. Check with `sudo mysql -e "SELECT user, host FROM mysql.user WHERE user='exporter';"`. - **`pg_up 0` with `Peer authentication failed`**: the service is not running as `postgres_exporter` or the role name differs from the system user name. Both must match exactly. - **Many `permission denied` errors in the postgres_exporter log**: the role is missing `pg_monitor`. Run the `GRANT` from step 3 again. - **Target `DOWN` in Prometheus but metrics work locally**: a firewall is blocking the port. Test from the Prometheus server with `curl http://db_server_ip:9104/metrics`. ## Conclusion MySQL and PostgreSQL now expose their internal statistics to Prometheus through exporters that use least-privilege accounts, and alert rules warn you about outages, connection exhaustion, replication lag and deadlocks. The same exporters work for every additional database server: install them locally and add the targets to the existing jobs. Good next steps are enabling the MySQL slow query log to investigate what the slow query rate points to, adding the `pg_stat_statements` extension to see per-query cost in PostgreSQL, and routing these alerts through Alertmanager or Grafana alerting to email or chat.