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: '[email protected]'
  smtp_auth_username: '[email protected]'
  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: '[email protected]'
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
Scrape interval: 15-30s untuk balance accuracy dan overhead
Retention: Set appropriate storage duration
Alerting: Start dengan critical alerts only
Dashboards: Focus pada actionable metrics
Security: Secure endpoints dengan authentication
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.