Configure MariaDB 11.6 monitoring with Prometheus and Grafana dashboards

Update system packages

Start by updating your package manager to ensure you get the latest versions of all components.

sudo apt update && sudo apt upgrade -y

sudo dnf update -y

Install MariaDB 11.6

Install MariaDB server with the performance schema enabled for detailed monitoring capabilities.

sudo apt install -y mariadb-server mariadb-client
sudo systemctl enable --now mariadb

sudo dnf install -y mariadb-server mariadb
sudo systemctl enable --now mariadb

Secure MariaDB installation

Run the security script to set root password and remove test databases that could interfere with monitoring.

sudo mysql_secure_installation

Configure MariaDB for monitoring

Enable performance schema and slow query logging to provide detailed metrics for Prometheus collection.

[mysqld]
performance_schema = ON
performance-schema-instrument = 'stage/%=ON'
performance-schema-consumer-events-stages-current = ON
performance-schema-consumer-events-stages-history = ON
performance-schema-consumer-events-stages-history-long = ON
performance-schema-consumer-statements-digest = ON

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

Create monitoring database user

Create a dedicated MariaDB user for Prometheus mysqld_exporter with minimal privileges needed for metrics collection.

sudo mysql -u root -p

CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'StrongExporterPass123!';
GRANT PROCESS, REPLICATION CLIENT ON . TO 'exporter'@'localhost';
GRANT SELECT ON performance_schema.* TO 'exporter'@'localhost';
GRANT SELECT ON information_schema.* TO 'exporter'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Restart MariaDB service

Restart MariaDB to apply the performance schema and logging configuration changes.

sudo systemctl restart mariadb
sudo systemctl status mariadb

Install Prometheus

Download and install Prometheus server for metrics collection and storage.

cd /tmp
wget https://github.com/prometheus/prometheus/releases/download/v2.47.0/prometheus-2.47.0.linux-amd64.tar.gz
tar xzf prometheus-2.47.0.linux-amd64.tar.gz
sudo cp prometheus-2.47.0.linux-amd64/prometheus /usr/local/bin/
sudo cp prometheus-2.47.0.linux-amd64/promtool /usr/local/bin/
sudo mkdir -p /etc/prometheus /var/lib/prometheus
sudo cp -r prometheus-2.47.0.linux-amd64/consoles /etc/prometheus/
sudo cp -r prometheus-2.47.0.linux-amd64/console_libraries /etc/prometheus/

Create Prometheus user and directories

Set up a dedicated system user for Prometheus with correct ownership of data directories.

sudo useradd --system --no-create-home --shell /bin/false prometheus
sudo chown -R prometheus:prometheus /etc/prometheus /var/lib/prometheus
sudo chmod 755 /etc/prometheus /var/lib/prometheus

Install mysqld_exporter

Download and install the MySQL/MariaDB exporter that will collect database metrics for Prometheus.

cd /tmp
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.0/mysqld_exporter-0.15.0.linux-amd64.tar.gz
tar xzf mysqld_exporter-0.15.0.linux-amd64.tar.gz
sudo cp mysqld_exporter-0.15.0.linux-amd64/mysqld_exporter /usr/local/bin/
sudo chown prometheus:prometheus /usr/local/bin/mysqld_exporter
sudo chmod 755 /usr/local/bin/mysqld_exporter

Configure mysqld_exporter credentials

Create a configuration file with database connection details for the mysqld_exporter.

[client]
user=exporter
password=StrongExporterPass123!
host=localhost
port=3306

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

Create mysqld_exporter systemd service

Set up the mysqld_exporter as a systemd service for automatic startup and management.

[Unit]
Description=MariaDB Exporter
Wants=network-online.target
After=network-online.target
Requires=mariadb.service
After=mariadb.service

[Service]
User=prometheus
Group=prometheus
Type=simple
ExecStart=/usr/local/bin/mysqld_exporter --config.my-cnf=/etc/prometheus/mysqld_exporter.cnf --collect.info_schema.innodb_metrics --collect.info_schema.innodb_tablespaces --collect.info_schema.processlist --collect.perf_schema.tablelocks --collect.perf_schema.file_events --collect.perf_schema.eventswaits --collect.perf_schema.indexiowaits --collect.perf_schema.tableiowaits --collect.slave_lag_seconds
Restart=always
RestartSec=5

[Install]
WantedBy=multi-user.target

Configure Prometheus

Set up the main Prometheus configuration to scrape metrics from mysqld_exporter and define alerting rules.

global:
  scrape_interval: 15s
  evaluation_interval: 15s

rule_files:
  - "mariadb_alerts.yml"

alerting:
  alertmanagers:
    - static_configs:
        - targets:
          - localhost:9093

scrape_configs:
  - job_name: 'prometheus'
    static_configs:
      - targets: ['localhost:9090']

  - job_name: 'mariadb'
    static_configs:
      - targets: ['localhost:9104']
    scrape_interval: 10s
    metrics_path: /metrics

Create MariaDB alerting rules

Define Prometheus alerting rules for common MariaDB issues like high connection usage and slow queries.

groups:
  - name: mariadb_alerts
    rules:
    - alert: MariaDBDown
      expr: mysql_up == 0
      for: 1m
      labels:
        severity: critical
      annotations:
        summary: "MariaDB instance is down"
        description: "MariaDB instance {{ $labels.instance }} has been down for more than 1 minute."

    - alert: MariaDBHighConnections
      expr: (mysql_global_status_threads_connected / mysql_global_variables_max_connections) * 100 > 80
      for: 2m
      labels:
        severity: warning
      annotations:
        summary: "MariaDB high connection usage"
        description: "MariaDB instance {{ $labels.instance }} is using {{ $value }}% of available connections."

    - alert: MariaDBSlowQueries
      expr: rate(mysql_global_status_slow_queries[5m]) > 5
      for: 2m
      labels:
        severity: warning
      annotations:
        summary: "MariaDB slow query rate high"
        description: "MariaDB instance {{ $labels.instance }} has {{ $value }} slow queries per second."

    - alert: MariaDBInnoDBLogWaits
      expr: rate(mysql_global_status_innodb_log_waits[5m]) > 0
      for: 1m
      labels:
        severity: warning
      annotations:
        summary: "MariaDB InnoDB log waits detected"
        description: "MariaDB instance {{ $labels.instance }} has InnoDB log waits, consider increasing innodb_log_file_size."

Create Prometheus systemd service

Configure Prometheus as a systemd service with proper resource limits and restart behavior.

[Unit]
Description=Prometheus Server
Documentation=https://prometheus.io/docs/
Wants=network-online.target
After=network-online.target

[Service]
User=prometheus
Group=prometheus
Type=simple
ExecStart=/usr/local/bin/prometheus --config.file=/etc/prometheus/prometheus.yml --storage.tsdb.path=/var/lib/prometheus/ --web.console.templates=/etc/prometheus/consoles --web.console.libraries=/etc/prometheus/console_libraries --web.listen-address=0.0.0.0:9090 --web.enable-lifecycle --storage.tsdb.retention.time=30d
Restart=always
RestartSec=5
LimitNOFILE=65536

[Install]
WantedBy=multi-user.target

Install Grafana

Add the Grafana repository and install the latest version for dashboard visualization.

sudo apt install -y software-properties-common wget
wget -q -O - https://packages.grafana.com/gpg.key | sudo apt-key add -
echo "deb https://packages.grafana.com/oss/deb stable main" | sudo tee /etc/apt/sources.list.d/grafana.list
sudo apt update
sudo apt install -y grafana

cat << EOF | sudo tee /etc/yum.repos.d/grafana.repo
[grafana]
name=grafana
baseurl=https://packages.grafana.com/oss/rpm
repo_gpgcheck=1
enabled=1
gpgcheck=1
gpgkey=https://packages.grafana.com/gpg.key
EOF
sudo dnf install -y grafana

Enable and start all services

Start mysqld_exporter, Prometheus, and Grafana services and enable them for automatic startup.

sudo systemctl daemon-reload
sudo systemctl enable --now mysqld_exporter
sudo systemctl enable --now prometheus
sudo systemctl enable --now grafana-server

Configure firewall access

Open the necessary ports for Prometheus and Grafana web interfaces.

sudo ufw allow 9090/tcp comment "Prometheus"
sudo ufw allow 3000/tcp comment "Grafana"
sudo ufw reload

sudo firewall-cmd --permanent --add-port=9090/tcp --add-port=3000/tcp
sudo firewall-cmd --reload

Configure Grafana data source

Access Grafana at http://your-server-ip:3000 with default login admin/admin. Add Prometheus as a data source.

Name: Prometheus
Type: Prometheus
URL: http://localhost:9090
Access: Server (default)
Scrape interval: 15s

Import MariaDB dashboard template

Create a comprehensive MariaDB monitoring dashboard with key performance indicators and alerts.

{
  "dashboard": {
    "title": "MariaDB Monitoring",
    "panels": [
      {
        "title": "Database Status",
        "type": "stat",
        "targets": [{
          "expr": "mysql_up",
          "legendFormat": "MariaDB Up"
        }]
      },
      {
        "title": "Active Connections",
        "type": "graph",
        "targets": [{
          "expr": "mysql_global_status_threads_connected",
          "legendFormat": "Active Connections"
        }]
      },
      {
        "title": "Query Rate",
        "type": "graph",
        "targets": [{
          "expr": "rate(mysql_global_status_queries[5m])",
          "legendFormat": "Queries/sec"
        }]
      },
      {
        "title": "Slow Queries",
        "type": "graph",
        "targets": [{
          "expr": "rate(mysql_global_status_slow_queries[5m])",
          "legendFormat": "Slow Queries/sec"
        }]
      }
    ]
  }
}

Install Alertmanager

Set up Alertmanager to handle Prometheus alerts and send notifications.

cd /tmp
wget https://github.com/prometheus/alertmanager/releases/download/v0.26.0/alertmanager-0.26.0.linux-amd64.tar.gz
tar xzf alertmanager-0.26.0.linux-amd64.tar.gz
sudo cp alertmanager-0.26.0.linux-amd64/alertmanager /usr/local/bin/
sudo cp alertmanager-0.26.0.linux-amd64/amtool /usr/local/bin/
sudo mkdir -p /etc/alertmanager /var/lib/alertmanager
sudo chown -R prometheus:prometheus /etc/alertmanager /var/lib/alertmanager

Configure Alertmanager

Set up email notifications for critical MariaDB alerts like database downtime and connection issues.

global:
  smtp_smarthost: 'smtp.example.com:587'
  smtp_from: 'alerts@example.com'
  smtp_auth_username: 'alerts@example.com'
  smtp_auth_password: 'YourEmailPassword'

route:
  group_by: ['alertname']
  group_wait: 10s
  group_interval: 10s
  repeat_interval: 1h
  receiver: 'web.hook'
  routes:
  - match:
      severity: critical
    receiver: 'critical-email'

receivers:
name: 'web.hook'
  webhook_configs:
  - url: 'http://127.0.0.1:5001/'

name: 'critical-email'
  email_configs:
  - to: 'admin@example.com'
    subject: 'MariaDB Alert: {{ .GroupLabels.alertname }}'
    body: |
      {{ range .Alerts }}
      Alert: {{ .Annotations.summary }}
      Description: {{ .Annotations.description }}
      Instance: {{ .Labels.instance }}
      {{ end }}

Create Alertmanager service

Configure Alertmanager as a systemd service for reliable alert handling.

[Unit]
Description=Alertmanager
Wants=network-online.target
After=network-online.target

[Service]
User=prometheus
Group=prometheus
Type=simple
ExecStart=/usr/local/bin/alertmanager --config.file=/etc/alertmanager/alertmanager.yml --storage.path=/var/lib/alertmanager/ --web.listen-address=0.0.0.0:9093
Restart=always
RestartSec=5

[Install]
WantedBy=multi-user.target

Enable Alertmanager

Start the Alertmanager service and verify it's receiving alerts from Prometheus.

sudo systemctl daemon-reload
sudo systemctl enable --now alertmanager
sudo systemctl status alertmanager

Optimize MariaDB for monitoring

Fine-tune MariaDB settings to balance monitoring visibility with performance impact.

[mysqld]
Performance Schema optimization
performance-schema-max-table-instances = 400
performance-schema-max-table-handles = 4000
performance-schema-events-statements-history-long-size = 1000
performance-schema-events-statements-history-size = 10

Query cache for read-heavy workloads
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 2M

Connection and buffer optimization
max_connections = 200
thread_cache_size = 16
table_open_cache = 2000
innodb_buffer_pool_size = 1G

Configure Prometheus retention

Adjust data retention and storage settings based on your monitoring requirements and disk space.

sudo systemctl edit prometheus

[Service]
ExecStart=
ExecStart=/usr/local/bin/prometheus --config.file=/etc/prometheus/prometheus.yml --storage.tsdb.path=/var/lib/prometheus/ --web.console.templates=/etc/prometheus/consoles --web.console.libraries=/etc/prometheus/console_libraries --web.listen-address=0.0.0.0:9090 --web.enable-lifecycle --storage.tsdb.retention.time=30d --storage.tsdb.retention.size=10GB

sudo systemctl daemon-reload
sudo systemctl restart prometheus