Salin dan Bagikan
MySQL Prometheus Grafana Monitoring
Setup Database Monitoring dengan Prometheus dan Grafana untuk MySQL - Panduan lengkap setup monitoring MySQL dengan Prometheus exporter dan Grafana dashboard untuk …

Setup Database Monitoring dengan Prometheus dan Grafana untuk MySQL

Setup Database Monitoring dengan Prometheus dan Grafana untuk MySQL

Monitoring modern memerlukan tools yang scalable dan visual. Prometheus dan Grafana adalah stack monitoring yang sangat popular untuk infrastruktur modern. Setelah mengimplementasikan monitoring untuk ratusan MySQL instances, saya akan berbagi setup yang terbukti reliable.

Arsitektur Monitoring

Komponen Stack

  1. Prometheus: Time-series database untuk metrics
  2. MySQL Exporter: Agent yang meng-export MySQL metrics
  3. Grafana: Visualization dan dashboard
  4. Alertmanager: Alerting system

Metrics Flow

MySQL Server → MySQL Exporter → Prometheus → Grafana (Dashboard)
                                      ↓
                                Alertmanager (Alerts)

Instalasi MySQL Exporter

1. Install MySQL Exporter

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

# Extract
tar xzf mysqld_exporter-0.15.0.linux-amd64.tar.gz
sudo mv mysqld_exporter-0.15.0.linux-amd64/mysqld_exporter /usr/local/bin/
sudo chmod +x /usr/local/bin/mysqld_exporter

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

2. Create Monitoring User di MySQL

-- Login ke MySQL
mysql -u root -p

-- Create monitoring user
CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'ExporterPass123!' WITH MAX_USER_CONNECTIONS 3;

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

-- Flush
FLUSH PRIVILEGES;

3. Configure MySQL Exporter

# Create config file
sudo nano /etc/mysqld_exporter.cnf

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

# Set permissions
sudo chown mysqld_exporter:mysqld_exporter /etc/mysqld_exporter.cnf
sudo chmod 600 /etc/mysqld_exporter.cnf

4. Create Systemd Service

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

[Unit]
Description=MySQL Exporter
After=network.target mysql.service

[Service]
Type=simple
User=mysqld_exporter
Group=mysqld_exporter
ExecStart=/usr/local/bin/mysqld_exporter \
  --config.my-cnf=/etc/mysqld_exporter.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.engine_innodb_status \
  --collect.perf_schema.file_events \
  --collect.perf_schema.replication_group_members \
  --collect.perf_schema.replication_group_member_stats \
  --collect.perf_schema.replication_applier_status

Restart=always

[Install]
WantedBy=multi-user.target

# Start service
sudo systemctl daemon-reload
sudo systemctl start mysqld_exporter
sudo systemctl enable mysqld_exporter

# Verify
sudo systemctl status mysqld_exporter

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

Instalasi Prometheus

1. Install Prometheus

# Download
cd /tmp
wget https://github.com/prometheus/prometheus/releases/download/v2.45.0/prometheus-2.45.0.linux-amd64.tar.gz

# Extract
tar xzf prometheus-2.45.0.linux-amd64.tar.gz
sudo mv prometheus-2.45.0.linux-amd64/prometheus /usr/local/bin/
sudo mv prometheus-2.45.0.linux-amd64/promtool /usr/local/bin/

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

2. Configure Prometheus

sudo nano /etc/prometheus/prometheus.yml

global:
  scrape_interval: 15s
  evaluation_interval: 15s

alerting:
  alertmanagers:
    - static_configs:
        - targets: ['localhost:9093']

rule_files:
  - /etc/prometheus/mysql_rules.yml

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

  - job_name: 'mysql'
    static_configs:
      - targets: ['localhost:9104']
    relabel_configs:
      - source_labels: [__address__]
        target_label: instance

3. Create Alert Rules

sudo nano /etc/prometheus/mysql_rules.yml

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

      - alert: MySQLHighConnections
        expr: mysql_global_status_threads_connected / mysql_global_variables_max_connections * 100 > 80
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "MySQL high connection usage on {{ $labels.instance }}"
          description: "MySQL connection usage is above 80% on {{ $labels.instance }}"

      - alert: MySQLSlowQueries
        expr: rate(mysql_global_status_slow_queries[5m]) > 1
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "MySQL slow queries detected on {{ $labels.instance }}"
          description: "MySQL is executing slow queries on {{ $labels.instance }}"

      - alert: MySQLReplicationLag
        expr: mysql_slave_lag_seconds > 300
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "MySQL replication lag on {{ $labels.instance }}"
          description: "MySQL replication lag is > 5 minutes on {{ $labels.instance }}"

4. Start Prometheus

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

[Unit]
Description=Prometheus Monitoring System
After=network.target

[Service]
Type=simple
User=prometheus
Group=prometheus
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

Restart=always

[Install]
WantedBy=multi-user.target

sudo systemctl daemon-reload
sudo systemctl start prometheus
sudo systemctl enable prometheus

Instalasi Grafana

1. Install Grafana

# Add repository
sudo apt-get install -y apt-transport-https 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 -a /etc/apt/sources.list.d/grafana.list

# Install
sudo apt-get update
sudo apt-get install -y grafana

# Start
sudo systemctl start grafana-server
sudo systemctl enable grafana-server

2. Import MySQL Dashboard

Step 1: Login Grafana

  • Buka http://your-server:3000
  • Default login: admin/admin

Step 2: Add Prometheus Data Source

Configuration → Data Sources → Add data source
- Name: Prometheus
- Type: Prometheus
- URL: http://localhost:9090
- Save & Test

Step 3: Import Dashboard

Create → Import
- Import via grafana.com: 7362 (MySQL Overview)
- Atau upload JSON: https://raw.githubusercontent.com/percona/grafana-dashboards/master/dashboards/MySQL/MySQL_Overview.json
- Select Prometheus as data source
- Import

Key Metrics untuk Monitoring

1. Connection Metrics

# Current connections
mysql_global_status_threads_connected

# Connection utilization percentage
(mysql_global_status_threads_connected / mysql_global_variables_max_connections) * 100

# Connection rate
rate(mysql_global_status_threads_created[5m])

2. Query Performance Metrics

# Slow queries rate
rate(mysql_global_status_slow_queries[5m])

# Queries per second
rate(mysql_global_status_queries[5m])

# Select statements rate
rate(mysql_global_status_com_select[5m])

3. Replication Metrics

# Replication lag (seconds)
mysql_slave_lag_seconds

# Replication status
mysql_slave_status_master_server_id

# Seconds behind master
mysql_slave_status_seconds_behind_master

4. InnoDB Metrics

# Buffer pool hit ratio
(mysql_global_status_innodb_buffer_pool_read_requests / 
 (mysql_global_status_innodb_buffer_pool_read_requests + mysql_global_status_innodb_buffer_pool_reads)) * 100

# Buffer pool dirty pages ratio
(mysql_global_status_innodb_buffer_pool_pages_dirty / mysql_global_status_innodb_buffer_pool_pages_total) * 100

# Row lock waits
mysql_global_status_innodb_row_lock_waits

Alerting Configuration

1. Install Alertmanager

# Download
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 mv alertmanager-0.26.0.linux-amd64/alertmanager /usr/local/bin/
sudo mv alertmanager-0.26.0.linux-amd64/amtool /usr/local/bin/

# Create config
sudo nano /etc/prometheus/alertmanager.yml

global:
  smtp_smarthost: 'smtp.gmail.com:587'
  smtp_from: 'alerts@example.com'
  smtp_auth_username: 'alerts@example.com'
  smtp_auth_password: 'your-password'

route:
  receiver: 'team-mail'
  group_by: ['alertname', 'severity']
  group_wait: 10s
  group_interval: 5m
  repeat_interval: 12h

receivers:
  - name: 'team-mail'
    email_configs:
      - to: 'dba-team@example.com'
        send_resolved: true

2. Setup Alertmanager Service

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

[Unit]
Description=Alertmanager
After=network.target

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

Restart=always

[Install]
WantedBy=multi-user.target

sudo systemctl daemon-reload
sudo systemctl start alertmanager
sudo systemctl enable alertmanager

Custom Dashboard Queries

1. MySQL Overview Panel

# Uptime
(mysql_global_status_uptime) / 3600

# Version
mysql_version_info{version=~".+"}

2. QPS Panel

# Queries per second
rate(mysql_global_status_queries[1m])

3. Connection Usage Panel

# Connection usage gauge
(mysql_global_status_threads_connected / mysql_global_variables_max_connections) * 100

4. Replication Status Panel

# Replication lag
mysql_slave_lag_seconds

# IO Running
mysql_slave_status_slave_io_running

# SQL Running
mysql_slave_status_slave_sql_running

Best Practices

  1. Scrape interval: 15-30s untuk balance accuracy dan overhead
  2. Retention: Set appropriate storage duration
  3. Alerting: Start dengan critical alerts only
  4. Dashboards: Focus pada actionable metrics
  5. Security: Secure endpoints dengan authentication
  6. High Availability: Run multiple Prometheus instances untuk redundancy

Kesimpulan

Prometheus + Grafana menyediakan monitoring modern untuk MySQL:

  • Real-time metrics: Semua metrics dalam real-time
  • Flexible alerting: Custom alert rules
  • Beautiful dashboards: Visual insights
  • Scalable: Handle multiple instances
  • Open source: Free dan community-driven

Dengan setup ini, Anda memiliki visibility penuh ke dalam MySQL performance dan health.

Artikel Terkait

Link Postingan : https://www.tirinfo.com/setup-database-monitoring-prometheus-grafana-mysql/

Hendra WIjaya
Tirinfo
5 minutes.
3 February 2026