Monitor MySQL performance 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

Create MySQL monitoring user

Create a dedicated MySQL user for the Prometheus exporter with minimal required privileges for security.

mysql -u root -p

Run these SQL commands to create the monitoring user and grant necessary permissions:

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

Install MySQL Exporter

Download and install the latest MySQL Exporter binary from the official GitHub releases.

cd /tmp
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.1/mysqld_exporter-0.15.1.linux-amd64.tar.gz
tar xzf mysqld_exporter-0.15.1.linux-amd64.tar.gz
sudo mv mysqld_exporter-0.15.1.linux-amd64/mysqld_exporter /usr/local/bin/
sudo chmod +x /usr/local/bin/mysqld_exporter

Create MySQL Exporter user and directories

Create a system user and directories for the MySQL Exporter service with proper security isolation.

sudo useradd --no-create-home --shell /bin/false mysqld_exporter
sudo mkdir -p /etc/mysqld_exporter
sudo chown mysqld_exporter:mysqld_exporter /etc/mysqld_exporter

Configure MySQL Exporter credentials

Create a secure configuration file with MySQL connection details for the exporter.

[client]
host=localhost
port=3306
user=mysqld_exporter
password=StrongExporterPassword123!

Set proper permissions to protect the credentials file:

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

Create MySQL Exporter systemd service

Configure MySQL Exporter as a systemd service for automatic startup and proper process management.

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

[Service]
User=mysqld_exporter
Group=mysqld_exporter
Type=simple
ExecStart=/usr/local/bin/mysqld_exporter --config.my-cnf=/etc/mysqld_exporter/.my.cnf --collect.global_status --collect.info_schema.innodb_metrics --collect.auto_increment.columns --collect.info_schema.processlist --collect.binlog_size --collect.info_schema.tablestats --collect.global_variables --collect.info_schema.query_response_time --collect.info_schema.userstats --collect.info_schema.tables --collect.perf_schema.tablelocks --collect.perf_schema.file_events --collect.perf_schema.eventswaits --collect.perf_schema.indexiowaits --collect.perf_schema.tableiowaits --collect.slave_status --web.listen-address=0.0.0.0:9104
Restart=always

[Install]
WantedBy=multi-user.target

Start and enable MySQL Exporter

Enable the MySQL Exporter service to start on boot and verify it's running correctly.

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

Install Prometheus

Install Prometheus to collect metrics from the MySQL Exporter and other system components.

cd /tmp
wget https://github.com/prometheus/prometheus/releases/download/v2.47.2/prometheus-2.47.2.linux-amd64.tar.gz
tar xzf prometheus-2.47.2.linux-amd64.tar.gz
sudo mv prometheus-2.47.2.linux-amd64/prometheus /usr/local/bin/
sudo mv prometheus-2.47.2.linux-amd64/promtool /usr/local/bin/
sudo chmod +x /usr/local/bin/prometheus /usr/local/bin/promtool

cd /tmp
wget https://github.com/prometheus/prometheus/releases/download/v2.47.2/prometheus-2.47.2.linux-amd64.tar.gz
tar xzf prometheus-2.47.2.linux-amd64.tar.gz
sudo mv prometheus-2.47.2.linux-amd64/prometheus /usr/local/bin/
sudo mv prometheus-2.47.2.linux-amd64/promtool /usr/local/bin/
sudo chmod +x /usr/local/bin/prometheus /usr/local/bin/promtool

Create Prometheus user and directories

Set up dedicated user and directories for Prometheus with proper permissions for data storage and configuration.

sudo useradd --no-create-home --shell /bin/false prometheus
sudo mkdir -p /etc/prometheus /var/lib/prometheus
sudo chown prometheus:prometheus /etc/prometheus /var/lib/prometheus

Configure Prometheus to scrape MySQL metrics

Create Prometheus configuration to collect metrics from MySQL Exporter and system node exporter.

global:
  scrape_interval: 15s
  evaluation_interval: 15s

rule_files:
  - "/etc/prometheus/mysql_rules.yml"

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

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

  - job_name: 'mysql'
    static_configs:
      - targets: ['localhost:9104']
    scrape_interval: 5s
    metrics_path: /metrics

  - job_name: 'node'
    static_configs:
      - targets: ['localhost:9100']

Set proper ownership for the configuration file:

sudo chown prometheus:prometheus /etc/prometheus/prometheus.yml

Create MySQL alerting rules

Define alert rules for common MySQL performance and availability issues.

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

      - alert: MySQLTooManyConnections
        expr: max_over_time(mysql_global_status_threads_connected[1m]) / mysql_global_variables_max_connections * 100 > 80
        for: 2m
        labels:
          severity: warning
        annotations:
          summary: "MySQL connection usage is high"
          description: "More than 80% of MySQL connections are in use on {{ $labels.instance }}"

      - alert: MySQLHighQPS
        expr: rate(mysql_global_status_questions[5m]) > 1000
        for: 2m
        labels:
          severity: warning
        annotations:
          summary: "MySQL high queries per second"
          description: "MySQL is executing {{ $value }} queries per second on {{ $labels.instance }}"

      - alert: MySQLSlowQueries
        expr: increase(mysql_global_status_slow_queries[1m]) > 0
        for: 2m
        labels:
          severity: warning
        annotations:
          summary: "MySQL slow queries detected"
          description: "MySQL has {{ $value }} slow queries in the last minute on {{ $labels.instance }}"

      - alert: MySQLInnoDBLogWaits
        expr: rate(mysql_global_status_innodb_log_waits[15m]) > 10
        for: 0m
        labels:
          severity: warning
        annotations:
          summary: "MySQL InnoDB log writes are stalling"
          description: "MySQL InnoDB log writes are waiting for disk at a rate of {{ $value }} per second on {{ $labels.instance }}"

      - alert: MySQLReplicationLag
        expr: mysql_slave_lag_seconds > 30
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "MySQL replication lag is high"
          description: "MySQL slave is {{ $value }} seconds behind master on {{ $labels.instance }}"

      - alert: MySQLReplicationSQLThreadNotRunning
        expr: mysql_slave_sql_running == 0
        for: 0m
        labels:
          severity: critical
        annotations:
          summary: "MySQL replication SQL thread is not running"
          description: "MySQL replication SQL thread is not running on {{ $labels.instance }}"

      - alert: MySQLReplicationIOThreadNotRunning
        expr: mysql_slave_io_running == 0
        for: 0m
        labels:
          severity: critical
        annotations:
          summary: "MySQL replication IO thread is not running"
          description: "MySQL replication IO thread is not running on {{ $labels.instance }}"

      - alert: MySQLTableLockWaitsHigh
        expr: rate(mysql_global_status_table_locks_waited[15m]) / rate(mysql_global_status_table_locks_immediate[15m]) > 0.02
        for: 1m
        labels:
          severity: warning
        annotations:
          summary: "MySQL table lock waits are high"
          description: "MySQL table lock wait rate is {{ $value }} on {{ $labels.instance }}"

      - alert: MySQLInnoDBBufferPoolEfficiency
        expr: mysql_global_status_innodb_buffer_pool_reads / mysql_global_status_innodb_buffer_pool_read_requests > 0.02
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "MySQL InnoDB buffer pool efficiency is low"
          description: "MySQL InnoDB buffer pool read miss ratio is {{ $value }} on {{ $labels.instance }}"

Set ownership for the rules file:

sudo chown prometheus:prometheus /etc/prometheus/mysql_rules.yml

Install Node Exporter for system metrics

Install Node Exporter to collect system-level metrics that complement MySQL monitoring.

cd /tmp
wget https://github.com/prometheus/node_exporter/releases/download/v1.6.1/node_exporter-1.6.1.linux-amd64.tar.gz
tar xzf node_exporter-1.6.1.linux-amd64.tar.gz
sudo mv node_exporter-1.6.1.linux-amd64/node_exporter /usr/local/bin/
sudo chmod +x /usr/local/bin/node_exporter

Create Node Exporter systemd service

Configure Node Exporter as a systemd service for system metrics collection.

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

[Service]
User=node_exporter
Group=node_exporter
Type=simple
ExecStart=/usr/local/bin/node_exporter --web.listen-address=0.0.0.0:9100
Restart=always

[Install]
WantedBy=multi-user.target

Create the node_exporter user and start the service:

sudo useradd --no-create-home --shell /bin/false node_exporter
sudo systemctl daemon-reload
sudo systemctl enable --now node_exporter

Create Prometheus systemd service

Configure Prometheus as a systemd service for metrics collection and alerting.

[Unit]
Description=Prometheus
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=15d
Restart=always

[Install]
WantedBy=multi-user.target

Start Prometheus service

Enable and start Prometheus to begin collecting MySQL and system metrics.

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

Install Grafana

Install Grafana for visualizing MySQL performance metrics with professional dashboards.

sudo apt install -y software-properties-common
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

sudo tee /etc/yum.repos.d/grafana.repo<