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:

sudo mysql

Create the user and grant only the privileges the exporter uses. Replace your_strong_password with a long random password:

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:

mysql -u exporter -p -e "SHOW GLOBAL STATUS LIKE 'Uptime';"

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 86231 |
+---------------+-------+

Step 2 - Installing mysqld_exporter

Download the latest release from the mysqld_exporter releases page. Set the version you want in a variable (on ARM servers use linux-arm64 instead of linux-amd64):

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

mysqld_exporter-0.17.2.linux-amd64.tar.gz: OK

Extract the binary, install it and create a system user for the service:

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:

sudo mkdir -p /etc/mysqld_exporter
sudo nano /etc/mysqld_exporter/my.cnf

[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:

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:

sudo nano /etc/systemd/system/mysqld_exporter.service

[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:

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:

curl -s http://localhost:9104/metrics | grep -E '^mysql_(up|global_status_threads_connected|global_variables_max_connections) '

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:

sudo -u postgres psql -c "CREATE ROLE postgres_exporter WITH LOGIN;"
sudo -u postgres psql -c "GRANT pg_monitor TO postgres_exporter;"

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:

sudo useradd --system --no-create-home --shell /usr/sbin/nologin postgres_exporter

Verify the login works:

sudo -u postgres_exporter psql -d postgres -c "SELECT count(*) FROM pg_stat_activity;"

 count
-------
     6
(1 row)

Step 4 - Installing postgres_exporter

Download the latest release from the postgres_exporter releases page:

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:

sudo nano /etc/systemd/system/postgres_exporter.service

[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:

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"'

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:

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:

sudo nano /etc/prometheus/prometheus.yml

Add two jobs under scrape_configs:, replacing db_server_ip with the database server address (or localhost):

  - 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:

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:

Step 7 - Creating alert rules

Create a rules file on the Prometheus server:

sudo mkdir -p /etc/prometheus/rules
sudo nano /etc/prometheus/rules/databases.yml

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:

rule_files:
  - /etc/prometheus/rules/*.yml

Check and apply:

promtool check rules /etc/prometheus/rules/databases.yml
sudo systemctl restart prometheus

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 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.