MySQL and PostgreSQL Exporters for Prometheus

Database monitoring is essential for understanding application performance and system health. The mysqld_exporter and postgres_exporter tools expose database-specific metrics to Prometheus, enabling detailed monitoring of queries, connections, performance, and replication. This guide covers both exporter installation, configuration, metric collection, Grafana dashboards, and alerting rules.

Table of Contents

Introduction

Database exporters bridge the gap between database performance and observability platforms. They expose hundreds of metrics about query performance, connections, locks, replication lag, and operational health, enabling operators to detect issues before they impact applications.

System Requirements

  • Linux (Ubuntu 20.04+, CentOS 8+)
  • MySQL 5.7+ or PostgreSQL 10+
  • At least 512MB RAM
  • Network access to databases
  • Database user with monitoring privileges
  • Prometheus for metric collection

MySQL Exporter Installation

Step 1: Create Database User

# Connect to MySQL
mysql -u root -p

# Create monitoring user
CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'exporter_password';

# Grant privileges
GRANT SELECT, PROCESS, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'exporter'@'localhost';

# For MySQL 8.0+
ALTER USER 'exporter'@'localhost' IDENTIFIED WITH mysql_native_password BY 'exporter_password';

# Flush privileges
FLUSH PRIVILEGES;

# Verify
SELECT user, host FROM mysql.user WHERE user='exporter';

Step 2: Install Exporter

# Create user
sudo useradd --no-create-home --shell /bin/false mysqld_exporter

# Download
cd /tmp
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.0/mysqld_exporter-0.15.0.linux-amd64.tar.gz
tar -xvzf mysqld_exporter-0.15.0.linux-amd64.tar.gz
cd mysqld_exporter-0.15.0.linux-amd64

# Install
sudo cp mysqld_exporter /usr/local/bin/
sudo chown mysqld_exporter:mysqld_exporter /usr/local/bin/mysqld_exporter
sudo chmod +x /usr/local/bin/mysqld_exporter

Step 3: Create Configuration

# Create .my.cnf for credentials
sudo tee /etc/mysqld_exporter/.my.cnf > /dev/null << 'EOF'
[client]
user=exporter
password=exporter_password
host=localhost
port=3306
EOF

sudo chown mysqld_exporter:mysqld_exporter /etc/mysqld_exporter/.my.cnf
sudo chmod 600 /etc/mysqld_exporter/.my.cnf

# Or use environment variable
export DATA_SOURCE_NAME="exporter:exporter_password@(localhost:3306)/"

Step 4: Systemd Service

sudo tee /etc/systemd/system/mysqld_exporter.service > /dev/null << 'EOF'
[Unit]
Description=MySQL Exporter
After=mysql.service
Wants=mysql.service

[Service]
User=mysqld_exporter
Group=mysqld_exporter
Type=simple
ExecStart=/usr/local/bin/mysqld_exporter \
  --config.my-cnf=/etc/mysqld_exporter/.my.cnf \
  --web.listen-address=0.0.0.0:9104 \
  --collect.auto_increment.columns \
  --collect.binlog_size \
  --collect.global_status \
  --collect.global_variables \
  --collect.info_schema.innodb_metrics \
  --collect.info_schema.innodb_tablespaces \
  --collect.info_schema.query_response_time

Restart=always
RestartSec=10

StandardOutput=journal
StandardError=journal
SyslogIdentifier=mysqld_exporter

[Install]
WantedBy=multi-user.target
EOF

sudo systemctl daemon-reload
sudo systemctl enable mysqld_exporter
sudo systemctl start mysqld_exporter

Step 5: Verify Installation

# Check metrics
curl http://localhost:9104/metrics | head -30

# Test connectivity
mysql -u exporter -p'exporter_password' -e "SHOW PROCESSLIST;"

PostgreSQL Exporter Installation

Step 1: Create Database User

# Connect to PostgreSQL
psql -U postgres

-- Create monitoring user
CREATE USER postgres_exporter WITH PASSWORD 'exporter_password';

-- Grant privileges
GRANT CONNECT ON DATABASE postgres TO postgres_exporter;
GRANT pg_monitor TO postgres_exporter;

-- For older PostgreSQL versions
GRANT SELECT ON pg_stat_statements TO postgres_exporter;

Step 2: Install Exporter

# Create user
sudo useradd --no-create-home --shell /bin/false postgres_exporter

# Download
cd /tmp
wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.13.2/postgres_exporter-0.13.2.linux-amd64.tar.gz
tar -xvzf postgres_exporter-0.13.2.linux-amd64.tar.gz
cd postgres_exporter-0.13.2.linux-amd64

# Install
sudo cp postgres_exporter /usr/local/bin/
sudo chown postgres_exporter:postgres_exporter /usr/local/bin/postgres_exporter
sudo chmod +x /usr/local/bin/postgres_exporter

Step 3: Create Configuration

# Create connection string
sudo tee /etc/postgres_exporter/.pgpass > /dev/null << 'EOF'
localhost:5432:postgres:postgres_exporter:exporter_password
EOF

sudo chown postgres_exporter:postgres_exporter /etc/postgres_exporter/.pgpass
sudo chmod 600 /etc/postgres_exporter/.pgpass

# Or set environment variable
export DATA_SOURCE_NAME="postgresql://postgres_exporter:exporter_password@localhost:5432/postgres?sslmode=disable"

Step 4: Systemd Service

sudo tee /etc/systemd/system/postgres_exporter.service > /dev/null << 'EOF'
[Unit]
Description=PostgreSQL Exporter
After=postgresql.service
Wants=postgresql.service

[Service]
User=postgres_exporter
Group=postgres_exporter
Type=simple
Environment="DATA_SOURCE_NAME=postgresql://postgres_exporter:exporter_password@localhost:5432/postgres?sslmode=disable"
ExecStart=/usr/local/bin/postgres_exporter \
  --web.listen-address=0.0.0.0:9187 \
  --extend.query-path=/etc/postgres_exporter/queries.yaml

Restart=always
RestartSec=10

StandardOutput=journal
StandardError=journal
SyslogIdentifier=postgres_exporter

[Install]
WantedBy=multi-user.target
EOF

sudo systemctl daemon-reload
sudo systemctl enable postgres_exporter
sudo systemctl start postgres_exporter

Step 5: Verify Installation

# Check metrics
curl http://localhost:9187/metrics | head -30

Prometheus Integration

MySQL Scrape Configuration

# /etc/prometheus/prometheus.yml

scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets:
          - 'localhost:9104'
        labels:
          instance: 'primary-mysql'
          environment: 'production'
      - targets:
          - '192.168.1.20:9104'
        labels:
          instance: 'replica-mysql'
          environment: 'production'
    scrape_interval: 30s
    scrape_timeout: 10s

PostgreSQL Scrape Configuration

scrape_configs:
  - job_name: 'postgres'
    static_configs:
      - targets:
          - 'localhost:9187'
        labels:
          instance: 'primary-postgres'
          environment: 'production'
      - targets:
          - '192.168.1.30:9187'
        labels:
          instance: 'standby-postgres'
          environment: 'production'
    scrape_interval: 30s
    scrape_timeout: 10s

Reload Prometheus

curl -X POST http://localhost:9090/-/reload

Database Metrics

MySQL Key Metrics

# Connections
mysql_global_status_threads_connected

# Query performance
mysql_global_status_questions
mysql_global_status_slow_queries

# Replication
mysql_slave_status_slave_io_running
mysql_slave_status_slave_sql_running
mysql_slave_status_seconds_behind_master

# InnoDB
mysql_global_status_innodb_buffer_pool_pages_free
mysql_global_status_innodb_buffer_pool_pages_total
mysql_global_status_innodb_rows_read
mysql_global_status_innodb_rows_written

# Database size
mysql_info_schema_table_size_bytes

PostgreSQL Key Metrics

# Connections
pg_stat_activity_count

# Query statistics
pg_stat_statements_calls
pg_stat_statements_mean_exec_time
pg_stat_statements_max_exec_time

# Replication
pg_stat_replication_state
pg_stat_replication_write_lag
pg_stat_replication_flush_lag
pg_stat_replication_replay_lag

# Cache effectiveness
pg_stat_user_tables_heap_blks_hit
pg_stat_user_tables_heap_blks_read

# Database size
pg_database_size_bytes

Grafana Dashboards

MySQL Dashboard Panels

# Connections over time
rate(mysql_global_status_threads_created[5m])

# Query rate
rate(mysql_global_status_questions[5m])

# Slow queries
rate(mysql_global_status_slow_queries[5m])

# Replication lag
mysql_slave_status_seconds_behind_master

# Buffer pool usage
mysql_global_status_innodb_buffer_pool_pages_free / 
mysql_global_status_innodb_buffer_pool_pages_total * 100

PostgreSQL Dashboard Panels

# Active connections
pg_stat_activity_count

# Query latency p95
histogram_quantile(0.95, pg_stat_statements_mean_exec_time)

# Replication lag
pg_stat_replication_replay_lag

# Cache hit ratio
pg_stat_user_tables_heap_blks_hit / 
(pg_stat_user_tables_heap_blks_hit + pg_stat_user_tables_heap_blks_read) * 100

# Connections per application
count by (usename) (pg_stat_activity_count)

Alerting Rules

MySQL Alerts

groups:
  - name: mysql_alerts
    rules:
      - alert: MySQLDown
        expr: mysql_up == 0
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "MySQL instance {{ $labels.instance }} is down"

      - alert: MySQLTooManyConnections
        expr: mysql_global_status_threads_connected > 80
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "MySQL {{ $labels.instance }} has {{ $value }} connections"

      - alert: MySQLReplicationLag
        expr: mysql_slave_status_seconds_behind_master > 60
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "MySQL replication lag is {{ $value }} seconds"

      - alert: MySQLSlowQueries
        expr: rate(mysql_global_status_slow_queries[5m]) > 0.1
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "MySQL has {{ $value }} slow queries per second"

PostgreSQL Alerts

groups:
  - name: postgres_alerts
    rules:
      - alert: PostgreSQLDown
        expr: pg_up == 0
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "PostgreSQL instance {{ $labels.instance }} is down"

      - alert: PostgreSQLTooManyConnections
        expr: pg_stat_activity_count > 100
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL has {{ $value }} connections"

      - alert: PostgreSQLReplicationLag
        expr: pg_stat_replication_replay_lag > 10000000000
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL replication lag is {{ $value | humanize }}bytes"

      - alert: PostgreSQLSlowQueries
        expr: pg_stat_statements_max_exec_time > 30000
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL slow query detected: {{ $value }}ms"

Performance Tuning

Query Optimization

# For MySQL - disable expensive collectors if needed
--no-collector.binlog_size
--no-collector.info_schema.query_response_time

# For PostgreSQL - limit query collection
--extend.query-path=/etc/postgres_exporter/queries.yaml

Scrape Interval Tuning

# Reduce for less frequent checks
scrape_configs:
  - job_name: 'mysql'
    scrape_interval: 60s
    scrape_timeout: 30s

Troubleshooting

Check Exporter Health

# MySQL
curl http://localhost:9104/metrics | grep mysql_up

# PostgreSQL
curl http://localhost:9187/metrics | grep pg_up

# Check connectivity
mysql -u exporter -p'password' -e "SELECT VERSION();"
psql -U postgres_exporter -d postgres -c "SELECT VERSION();"

Debug Issues

# View exporter logs
journalctl -u mysqld_exporter -f
journalctl -u postgres_exporter -f

# Test exporter directly
./mysqld_exporter --collect.global_status --web.listen-address=:9999

# Check query response time
mysql -u exporter -p'password' -e "SHOW GLOBAL STATUS LIKE 'Slow_queries';"

Conclusion

Database exporters provide essential insights into application data layer performance. By following this guide, you've deployed comprehensive database monitoring for both MySQL and PostgreSQL. Focus on collecting metrics that align with your SLOs, setting alert thresholds based on operational baselines, and using Grafana dashboards to communicate database health to your team. Database observability is critical for preventing outages and maintaining application performance.