数据库监控体系搭建：从 Exporter 到告警规则的全链路实战

┌──────────────────────────────────────────────────┐
│              业务应用层                            │
│  核心接口响应时间 | 业务操作成功率 | 业务吞吐量     │
│  → 这些指标直接关联用户体验                        │
├──────────────────────────────────────────────────┤
│              数据库内核层                          │
│  连接数 | QPS/TPS | 慢查询 | 锁等待 | 缓存命中率   │
│  复制延迟 | 死锁 | WAL写入 | 事务回滚率           │
│  → 这些指标反映数据库内部运行状态                  │
├──────────────────────────────────────────────────┤
│              服务器资源层                          │
│  CPU | 内存 | 磁盘IO | 网络 | 文件系统             │
│  → 这些指标是数据库运行的物理基础                  │
└──────────────────────────────────────────────────┘

-- 创建专用监控账号，最小权限原则
CREATE USER 'mysqld_exporter'@'localhost' IDENTIFIED BY 'StrongMonitorPass123!';

-- 授予必要权限
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'mysqld_exporter'@'localhost';

-- 如果需要采集 performance_schema 中的详细指标（推荐）
GRANT SELECT ON performance_schema.* TO 'mysqld_exporter'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

# 下载最新版本（截至 2026 年 7 月，v0.18.0）
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.18.0/mysqld_exporter-0.18.0.linux-amd64.tar.gz

# 解压
tar xzf mysqld_exporter-0.18.0.linux-amd64.tar.gz
mv mysqld_exporter-0.18.0.linux-amd64 /usr/local/mysqld_exporter

# 创建配置文件（存储连接信息）
cat > /usr/local/mysqld_exporter/.my.cnf << 'EOF'
[client]
user=mysqld_exporter
password=StrongMonitorPass123!
EOF

# 修改文件权限（密码安全）
chmod 600 /usr/local/mysqld_exporter/.my.cnf

cat > /etc/systemd/system/mysqld_exporter.service << 'EOF'
[Unit]
Description=MySQL Exporter for Prometheus
After=network.target mysql.service

[Service]
Type=simple
User=mysql
ExecStart=/usr/local/mysqld_exporter/mysqld_exporter \
  --config.my-cnf=/usr/local/mysqld_exporter/.my.cnf \
  --web.listen-address=:9104 \
  --collect.perf_schema.eventsstatements \
  --collect.perf_schema.eventswaits \
  --collect.perf_schema.file_instances \
  --collect.global_status \
  --collect.global_variables \
  --collect.info_schema.innodb_metrics \
  --collect.info_schema.innodb_tablespaces \
  --collect.info_schema.processlist \
  --collect.info_schema.tables \
  --collect.auto_increment.columns
Restart=on-failure
RestartSec=5

[Install]
WantedBy=multi-user.target
EOF

systemctl daemon-reload
systemctl enable mysqld_exporter
systemctl start mysqld_exporter

# 验证
curl -s http://localhost:9104/metrics | head -20

# QPS（每秒查询数）
rate(mysql_global_status_questions[1m])

# 慢查询增长率
rate(mysql_global_status_slow_queries[5m])

# 连接使用率
mysql_global_status_threads_connected / mysql_global_variables_max_connections * 100

# InnoDB 缓冲池命中率
1 - (rate(mysql_global_status_innodb_buffer_pool_reads[5m]) /
     rate(mysql_global_status_innodb_buffer_pool_read_requests[5m]))

# 行锁等待次数（每分钟）
rate(mysql_global_status_innodb_row_lock_waits[1m])

# 主从复制延迟
mysql_slave_status_seconds_behind_master

# 运行线程数
mysql_global_status_threads_running

-- 创建监控用户
CREATE USER monitor WITH PASSWORD 'StrongMonitorPass123!';

-- 设置搜索路径
ALTER USER monitor SET search_path TO monitor, pg_catalog;

-- PostgreSQL 10+ 使用 pg_monitor 角色（推荐）
GRANT pg_monitor TO monitor;

-- 如果需要 pg_stat_statements 扩展（查询性能分析）
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
GRANT SELECT ON pg_stat_statements TO monitor;

-- 如果需要自定义查询
CREATE SCHEMA IF NOT EXISTS monitor AUTHORIZATION monitor;

# 下载
wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.15.0/postgres_exporter-0.15.0.linux-amd64.tar.gz

tar xzf postgres_exporter-0.15.0.linux-amd64.tar.gz
mv postgres_exporter-0.15.0.linux-amd64 /usr/local/postgres_exporter

# 通过环境变量传递连接信息
cat > /usr/local/postgres_exporter/postgres_exporter.env << 'EOF'
DATA_SOURCE_NAME=postgresql://monitor:StrongMonitorPass123!@localhost:5432/postgres?sslmode=disable
EOF

cat > /etc/systemd/system/postgres_exporter.service << 'EOF'
[Unit]
Description=PostgreSQL Exporter for Prometheus
After=network.target postgresql.service

[Service]
Type=simple
User=postgres
EnvironmentFile=/usr/local/postgres_exporter/postgres_exporter.env
ExecStart=/usr/local/postgres_exporter/postgres_exporter \
  --web.listen-address=:9187 \
  --extend.query-path=/usr/local/postgres_exporter/queries.yaml \
  --auto-discover-databases
Restart=on-failure
RestartSec=5

[Install]
WantedBy=multi-user.target
EOF

systemctl daemon-reload
systemctl enable postgres_exporter
systemctl start postgres_exporter

# 验证
curl -s http://localhost:9187/metrics | head -20

# /usr/local/postgres_exporter/queries.yaml

# 表膨胀检测
pg_table_bloat:
  query: >
    SELECT
      schemaname AS schema,
      relname AS table_name,
      n_live_tup AS live_tuples,
      n_dead_tup AS dead_tuples,
      ROUND(n_dead_tup::FLOAT / GREATEST(n_live_tup, 1) * 100, 2) AS dead_ratio
    FROM pg_stat_user_tables
    WHERE n_live_tup > 1000
    ORDER BY dead_ratio DESC
    LIMIT 20    
  metrics:
    - schema:
        usage: "LABEL"
        description: "Schema name"
    - table_name:
        usage: "LABEL"
        description: "Table name"
    - live_tuples:
        usage: "GAUGE"
        description: "Number of live tuples"
    - dead_tuples:
        usage: "GAUGE"
        description: "Number of dead tuples"
    - dead_ratio:
        usage: "GAUGE"
        description: "Dead tuple ratio percentage"

# 长事务检测
pg_long_running_transactions:
  query: >
    SELECT
      pid,
      extract(EPOCH FROM (now() - xact_start)) AS duration_seconds,
      state,
      query
    FROM pg_stat_activity
    WHERE xact_start IS NOT NULL
      AND now() - xact_start > interval '5 minutes'    
  metrics:
    - pid:
        usage: "LABEL"
        description: "Process ID"
    - duration_seconds:
        usage: "GAUGE"
        description: "Transaction duration in seconds"
    - state:
        usage: "LABEL"
        description: "Transaction state"
    - query:
        usage: "LABEL"
        description: "Query text"

# 连接池使用情况
pg_connection_pool:
  query: >
    SELECT
      datname AS database,
      count(*) AS total_connections,
      count(*) FILTER (WHERE state = 'active') AS active_connections,
      count(*) FILTER (WHERE state = 'idle') AS idle_connections,
      count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_transaction
    FROM pg_stat_activity
    GROUP BY datname    
  metrics:
    - database:
        usage: "LABEL"
        description: "Database name"
    - total_connections:
        usage: "GAUGE"
        description: "Total connections"
    - active_connections:
        usage: "GAUGE"
        description: "Active connections"
    - idle_connections:
        usage: "GAUGE"
        description: "Idle connections"
    - idle_in_transaction:
        usage: "GAUGE"
        description: "Idle in transaction connections"

# TPS（每秒事务数）
rate(pg_stat_database_xact_commit[1m]) + rate(pg_stat_database_xact_rollback[1m])

# 事务回滚率
rate(pg_stat_database_xact_rollback[5m]) /
(rate(pg_stat_database_xact_commit[5m]) + rate(pg_stat_database_xact_rollback[5m])) * 100

# 缓存命中率
rate(pg_stat_database_blks_hit[5m]) /
(rate(pg_stat_database_blks_hit[5m]) + rate(pg_stat_database_blks_read[5m])) * 100

# 活动连接数
pg_stat_database_numbackends

# 复制延迟（字节）
pg_stat_replication_pg_wal_lsn_diff

# 死锁累计次数
pg_stat_database_deadlocks

# prometheus.yml

scrape_configs:
  # MySQL 监控
  - job_name: 'mysql'
    scrape_interval: 15s
    static_configs:
      - targets: ['mysql-prod-01:9104', 'mysql-prod-02:9104']
        labels:
          env: 'production'
          cluster: 'mysql-prod'
      - targets: ['mysql-test-01:9104']
        labels:
          env: 'staging'
          cluster: 'mysql-test'

  # PostgreSQL 监控
  - job_name: 'postgresql'
    scrape_interval: 15s
    static_configs:
      - targets: ['pg-prod-01:9187', 'pg-prod-02:9187']
        labels:
          env: 'production'
          cluster: 'pg-prod'
      - targets: ['pg-test-01:9187']
        labels:
          env: 'staging'
          cluster: 'pg-test'

scrape_configs:
  # 使用 Consul 服务发现
  - job_name: 'mysql-consul'
    consul_sd_configs:
      - server: 'consul.internal:8500'
        services: ['mysqld-exporter']
    relabel_configs:
      - source_labels: [__meta_consul_tags]
        regex: '.*,production,.*'
        target_label: env
        replacement: 'production'

  # 使用文件服务发现（适合 VM 环境）
  - job_name: 'postgresql-file'
    file_sd_configs:
      - files: ['/etc/prometheus/targets/pg-*.yml']
        refresh_interval: 30s

# /etc/prometheus/targets/pg-prod.yml
- targets:
    - 10.0.1.10:9187
    - 10.0.1.11:9187
  labels:
    env: production
    cluster: pg-prod
    role: primary

# /etc/prometheus/rules/mysql-alerts.yml

groups:
  - name: mysql-alerts
    rules:
      # ===== P0 级别 =====

      # MySQL 实例宕机
      - alert: MySQLDown
        expr: mysql_up == 0
        for: 1m
        labels:
          severity: P0
        annotations:
          summary: "MySQL 实例宕机 {{ $labels.instance }}"
          description: "MySQL 实例 {{ $labels.instance }} 已离线超过 1 分钟"

      # 主从复制 IO 线程停止
      - alert: MySQLReplicationIOStopped
        expr: mysql_slave_status_slave_io_running == 0
        for: 1m
        labels:
          severity: P0
        annotations:
          summary: "MySQL 复制 IO 线程停止 {{ $labels.instance }}"
          description: "从库 {{ $labels.instance }} 的 IO 线程已停止"

      # 主从复制 SQL 线程停止
      - alert: MySQLReplicationSQLStopped
        expr: mysql_slave_status_slave_sql_running == 0
        for: 1m
        labels:
          severity: P0
        annotations:
          summary: "MySQL 复制 SQL 线程停止 {{ $labels.instance }}"
          description: "从库 {{ $labels.instance }} 的 SQL 线程已停止"

      # ===== P1 级别 =====

      # 连接数过高
      - alert: MySQLHighConnections
        expr: >
          mysql_global_status_threads_connected /
          mysql_global_variables_max_connections * 100 > 80          
        for: 5m
        labels:
          severity: P1
        annotations:
          summary: "MySQL 连接数过高 {{ $labels.instance }}"
          description: "连接使用率 {{ printf \"%.1f\" $value }}%，已超过 80% 持续 5 分钟"

      # 主从复制延迟过大
      - alert: MySQLReplicationLag
        expr: mysql_slave_status_seconds_behind_master > 60
        for: 5m
        labels:
          severity: P1
        annotations:
          summary: "MySQL 复制延迟 {{ $labels.instance }}"
          description: "从库 {{ $labels.instance }} 延迟 {{ $value }} 秒"

      # 慢查询突增
      - alert: MySQLSlowQueries
        expr: rate(mysql_global_status_slow_queries[5m]) > 5
        for: 10m
        labels:
          severity: P1
        annotations:
          summary: "MySQL 慢查询突增 {{ $labels.instance }}"
          description: "慢查询速率 {{ printf \"%.1f\" $value }} 次/秒"

      # InnoDB 行锁等待过多
      - alert: MySQLRowLockWaits
        expr: rate(mysql_global_status_innodb_row_lock_waits[5m]) > 10
        for: 5m
        labels:
          severity: P1
        annotations:
          summary: "MySQL 行锁等待过多 {{ $labels.instance }}"
          description: "行锁等待 {{ printf \"%.1f\" $value }} 次/秒"

      # ===== P2 级别 =====

      # InnoDB 缓冲池命中率下降
      - alert: MySQLLowBufferPoolHitRate
        expr: >
          1 - (rate(mysql_global_status_innodb_buffer_pool_reads[5m]) /
               rate(mysql_global_status_innodb_buffer_pool_read_requests[5m])) < 0.99          
        for: 15m
        labels:
          severity: P2
        annotations:
          summary: "MySQL 缓冲池命中率低 {{ $labels.instance }}"
          description: "缓冲池命中率 {{ printf \"%.2f\" (1 - $value) }}%，低于 99%"

      # 连接失败次数增加
      - alert: MySQLAbortedConnects
        expr: rate(mysql_global_status_aborted_connects[5m]) > 10
        for: 5m
        labels:
          severity: P2
        annotations:
          summary: "MySQL 连接失败增加 {{ $labels.instance }}"
          description: "连接失败 {{ printf \"%.1f\" $value }} 次/秒"

# /etc/prometheus/rules/postgresql-alerts.yml

groups:
  - name: postgresql-alerts
    rules:
      # ===== P0 级别 =====

      # PostgreSQL 实例宕机
      - alert: PostgreSQLDown
        expr: pg_up == 0
        for: 1m
        labels:
          severity: P0
        annotations:
          summary: "PostgreSQL 实例宕机 {{ $labels.instance }}"
          description: "PostgreSQL 实例 {{ $labels.instance }} 已离线"

      # ===== P1 级别 =====

      # 连接数过高
      - alert: PostgreSQLHighConnections
        expr: >
          pg_stat_database_numbackends /
          pg_settings_max_connections * 100 > 80          
        for: 5m
        labels:
          severity: P1
        annotations:
          summary: "PostgreSQL 连接数过高 {{ $labels.instance }}"
          description: "连接使用率 {{ printf \"%.1f\" $value }}%"

      # 复制延迟过大
      - alert: PostgreSQLReplicationLag
        expr: pg_stat_replication_pg_wal_lsn_diff > 1073741824
        # 1073741824 bytes = 1GB
        for: 5m
        labels:
          severity: P1
        annotations:
          summary: "PostgreSQL 复制延迟 {{ $labels.instance }}"
          description: "WAL 延迟 {{ printf \"%.2f\" ($value / 1073741824) }} GB"

      # 死锁发生
      - alert: PostgreSQLDeadlocks
        expr: rate(pg_stat_database_deadlocks[5m]) > 0
        for: 1m
        labels:
          severity: P1
        annotations:
          summary: "PostgreSQL 死锁 {{ $labels.instance }}"
          description: "检测到死锁，速率 {{ $value }} 次/秒"

      # 事务回滚率过高
      - alert: PostgreSQLHighRollbackRate
        expr: >
          rate(pg_stat_database_xact_rollback[5m]) /
          (rate(pg_stat_database_xact_commit[5m]) +
           rate(pg_stat_database_xact_rollback[5m])) * 100 > 10          
        for: 10m
        labels:
          severity: P1
        annotations:
          summary: "PostgreSQL 回滚率过高 {{ $labels.instance }}"
          description: "事务回滚率 {{ printf \"%.1f\" $value }}%"

      # ===== P2 级别 =====

      # 缓存命中率下降
      - alert: PostgreSQLLowCacheHitRate
        expr: >
          rate(pg_stat_database_blks_hit[5m]) /
          (rate(pg_stat_database_blks_hit[5m]) +
           rate(pg_stat_database_blks_read[5m])) * 100 < 95          
        for: 15m
        labels:
          severity: P2
        annotations:
          summary: "PostgreSQL 缓存命中率低 {{ $labels.instance }}"
          description: "缓存命中率 {{ printf \"%.1f\" $value }}%，低于 95%"

# 错误做法：连接数瞬间超 80% 就告警
expr: connection_usage > 80
for: 0m

# 正确做法：持续超 80% 5 分钟才告警
expr: connection_usage > 80
for: 5m

# 只对主库的复制状态告警
expr: >
  mysql_slave_status_slave_io_running == 0
  and on(instance) mysql_master_status == 1  

# Alertmanager 配置：当 MySQL 实例宕机时，抑制该实例的所有其他告警
inhibit_rules:
  - source_match:
      severity: P0
      alertname: MySQLDown
    target_match_re:
      severity: P1|P2
    equal: ['instance']

# Panel: 连接数趋势
mysql_global_status_threads_connected{cluster=~"$cluster"}

# Panel: QPS
rate(mysql_global_status_questions[1m])

# Panel: TPS
rate(mysql_global_status_com_insert[1m]) +
rate(mysql_global_status_com_update[1m]) +
rate(mysql_global_status_com_delete[1m])

# Panel: InnoDB 缓冲池命中率
(1 - rate(mysql_global_status_innodb_buffer_pool_reads[5m]) /
     rate(mysql_global_status_innodb_buffer_pool_read_requests[5m])) * 100

# Panel: 慢查询排行（需要 performance_schema）
topk(10,
  sum by (digest_text) (
    rate(mysql_perf_schema_events_statements_sum_total_timer_wait[5m])
  )
)

# Panel: 复制延迟
mysql_slave_status_seconds_behind_master{cluster=~"$cluster"}

#!/bin/bash
# 数据库监控一键部署脚本（Prometheus + Exporter）
# 适用于 RHEL/CentOS 8+，MySQL + PostgreSQL 环境

set -euo pipefail

# ===== 配置区 =====
MYSQL_EXPORTER_VERSION="0.18.0"
PG_EXPORTER_VERSION="0.15.0"
EXPORTER_USER="monitor"
EXPORTER_GROUP="monitor"

# ===== 创建用户 =====
id -u $EXPORTER_USER &>/dev/null || useradd -r -s /sbin/nologin $EXPORTER_USER

# ===== MySQL Exporter =====
echo "=== Installing mysqld_exporter ==="
cd /tmp
wget -q "https://github.com/prometheus/mysqld_exporter/releases/download/v${MYSQL_EXPORTER_VERSION}/mysqld_exporter-${MYSQL_EXPORTER_VERSION}.linux-amd64.tar.gz"
tar xzf "mysqld_exporter-${MYSQL_EXPORTER_VERSION}.linux-amd64.tar.gz"
mv "mysqld_exporter-${MYSQL_EXPORTER_VERSION}.linux-amd64" /usr/local/mysqld_exporter
chown -R $EXPORTER_USER:$EXPORTER_GROUP /usr/local/mysqld_exporter

# 创建配置文件模板
cat > /usr/local/mysqld_exporter/.my.cnf << 'EOF'
[client]
user=mysqld_exporter
password=CHANGE_ME_STRONG_PASSWORD
EOF
chmod 600 /usr/local/mysqld_exporter/.my.cnf

# systemd 服务
cat > /etc/systemd/system/mysqld_exporter.service << EOF
[Unit]
Description=MySQL Exporter
After=network.target

[Service]
Type=simple
User=$EXPORTER_USER
ExecStart=/usr/local/mysqld_exporter/mysqld_exporter \\
  --config.my-cnf=/usr/local/mysqld_exporter/.my.cnf \\
  --web.listen-address=:9104 \\
  --collect.global_status \\
  --collect.global_variables \\
  --collect.perf_schema.eventsstatements \\
  --collect.info_schema.innodb_metrics \\
  --collect.info_schema.processlist
Restart=on-failure
RestartSec=5

[Install]
WantedBy=multi-user.target
EOF

# ===== PostgreSQL Exporter =====
echo "=== Installing postgres_exporter ==="
wget -q "https://github.com/prometheus-community/postgres_exporter/releases/download/v${PG_EXPORTER_VERSION}/postgres_exporter-${PG_EXPORTER_VERSION}.linux-amd64.tar.gz"
tar xzf "postgres_exporter-${PG_EXPORTER_VERSION}.linux-amd64.tar.gz"
mv "postgres_exporter-${PG_EXPORTER_VERSION}.linux-amd64" /usr/local/postgres_exporter
chown -R $EXPORTER_USER:$EXPORTER_GROUP /usr/local/postgres_exporter

# 环境变量模板
cat > /usr/local/postgres_exporter/postgres_exporter.env << 'EOF'
DATA_SOURCE_NAME=postgresql://monitor:CHANGE_ME_STRONG_PASSWORD@localhost:5432/postgres?sslmode=disable
EOF
chmod 600 /usr/local/postgres_exporter/postgres_exporter.env

# systemd 服务
cat > /etc/systemd/system/postgres_exporter.service << EOF
[Unit]
Description=PostgreSQL Exporter
After=network.target

[Service]
Type=simple
User=$EXPORTER_USER
EnvironmentFile=/usr/local/postgres_exporter/postgres_exporter.env
ExecStart=/usr/local/postgres_exporter/postgres_exporter \\
  --web.listen-address=:9187 \\
  --auto-discover-databases
Restart=on-failure
RestartSec=5

[Install]
WantedBy=multi-user.target
EOF

# ===== 启动服务 =====
systemctl daemon-reload
systemctl enable mysqld_exporter postgres_exporter
systemctl start mysqld_exporter postgres_exporter

# ===== 验证 =====
echo "=== Verification ==="
sleep 2
echo "MySQL Exporter:"
curl -s http://localhost:9104/metrics | grep mysql_up | head -1
echo "PostgreSQL Exporter:"
curl -s http://localhost:9187/metrics | grep pg_up | head -1
echo ""
echo "=== Next Steps ==="
echo "1. Edit /usr/local/mysqld_exporter/.my.cnf with real MySQL credentials"
echo "2. Edit /usr/local/postgres_exporter/postgres_exporter.env with real PG credentials"
echo "3. Add scrape configs to prometheus.yml"
echo "4. Import Grafana dashboards: MySQL=7362, PostgreSQL=9628"
echo "5. Deploy alert rules to Prometheus"

# iptables 规则
iptables -A INPUT -p tcp --dport 9104 -s 10.0.1.100 -j ACCEPT  # Prometheus IP
iptables -A INPUT -p tcp --dport 9104 -j DROP

iptables -A INPUT -p tcp --dport 9187 -s 10.0.1.100 -j ACCEPT
iptables -A INPUT -p tcp --dport 9187 -j DROP

# exporter 启动参数
--web.config.file=/usr/local/mysqld_exporter/web.yml

# web.yml
tls_server_config:
  cert_file: /etc/ssl/exporter.crt
  key_file: /etc/ssl/exporter.key