# 创建 exporter 用户
sudo useradd -r -s /usr/sbin/nologin prometheus

# 下载 mysqld_exporter
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-*.tar.gz
sudo cp mysqld_exporter-*/mysqld_exporter /usr/local/bin/

# 创建 MySQL 监控用户
mysql -u root -e "CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'secure_password';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';"

# 启动 exporter
cat > /etc/systemd/system/mysqld-exporter.service << 'EOF'
[Unit]
Description=MySQL Exporter
After=network.target

[Service]
User=prometheus
ExecStart=/usr/local/bin/mysqld_exporter \
  --mysq.address=localhost \
  --config.my-cnf=/etc/mysql/exporter.cnf

[Install]
WantedBy=multi-user.target
EOF

sudo systemctl enable --now mysqld-exporter

# /etc/prometheus/prometheus.yml
global:
  scrape_interval: 15s

scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['localhost:9104']
    metrics_path: /metrics

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

wget https://github.com/prometheus/node_exporter/releases/download/v1.7.0/node_exporter-1.7.0.linux-amd64.tar.gz
tar xzf node_exporter-*.tar.gz
sudo cp node_exporter-*/node_exporter /usr/local/bin/

sudo systemctl enable --now node_exporter

# /etc/mysql/conf.d/slow_query.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1
log_queries_not_using_indexes = 1

sudo systemctl restart mysql
# 或
sudo systemctl restart postgresql

# 安装 Ollama
curl -fsSL https://ollama.com/install.sh | sh

# 拉取适合调优的模型（qwen2.5 对 SQL 理解较好）
ollama pull qwen2.5:14b

# 验证
ollama run qwen2.5:14b "解释 EXPLAIN ANALYZE 的输出含义"

pip install openai requests

# /opt/db-optimizer/config.py
OPENROUTER_API_KEY = "sk-or-v1-xxxxx"
MODEL = "anthropic/claude-3.5-sonnet"
ENDPOINT = "https://openrouter.ai/api/v1/chat/completions"

#!/usr/bin/env python3
"""AI-powered database slow query analyzer."""

import subprocess
import json
import os
from datetime import datetime, timedelta
import sqlite3

class SlowQueryAnalyzer:
    def __init__(self, db_type="mysql"):
        self.db_type = db_type
        self.query_history = []

    def extract_slow_queries(self, hours=24):
        """从慢查询日志提取最近 N 小时的慢查询。"""
        if self.db_type == "mysql":
            log_file = "/var/log/mysql/slow-query.log"
        else:
            log_file = "/var/log/postgresql/postgresql-slow.log"

        if not os.path.exists(log_file):
            return []

        cutoff = datetime.now() - timedelta(hours=hours)
        queries = []

        with open(log_file, 'r') as f:
            content = f.read()

        # 解析慢查询日志格式
        # MySQL 格式示例：
        # # Time: 2026-07-03T10:30:00.000000Z
        # # User@Host: app_user[app_user] @ localhost []
        # # Query_time: 3.456789  Lock_time: 0.000123 Rows_sent: 100
        # SET timestamp=1720000000;
        # SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at;

        import re
        pattern = r'# Query_time:\s+([\d.]+).*?SET timestamp=\d+;\s*\n(.+?)(?=# Time:|$)'
        matches = re.finditer(pattern, content, re.DOTALL)

        for match in matches:
            query_time = float(match.group(1))
            sql = match.group(2).strip().rstrip(';')

            if query_time > 1.0:  # 只关注超过 1 秒的查询
                queries.append({
                    "query_time": query_time,
                    "sql": sql,
                    "timestamp": datetime.now().isoformat()
                })

        return queries

    def get_explain_plan(self, sql):
        """获取 EXPLAIN ANALYZE 结果。"""
        if self.db_type == "mysql":
            cmd = ["mysql", "-u", "root", "-e", f"EXPLAIN ANALYZE {sql}"]
        else:
            cmd = ["psql", "-U", "postgres", "-c", f"EXPLAIN ANALYZE {sql}"]

        try:
            result = subprocess.run(cmd, capture_output=True, text=True, timeout=30)
            return result.stdout
        except subprocess.TimeoutExpired:
            return "EXPLAIN timed out"

    def generate_ai_report(self, slow_queries):
        """将慢查询发送给 AI 模型进行分析。"""
        if not slow_queries:
            return "未发现慢查询。"

        # 采样前 10 个最慢的查询
        top_queries = sorted(slow_queries, key=lambda x: x["query_time"], reverse=True)[:10]

        prompt = f"""你是一个专业的数据库性能优化专家。请分析以下慢查询并提供优化建议。

数据库类型: {self.db_type}

慢查询列表（按执行时间降序）:
"""
        for i, q in enumerate(top_queries, 1):
            prompt += f"\n--- 查询 #{i} ---\n"
            prompt += f"执行时间: {q['query_time']}秒\n"
            prompt += f"SQL: {q['sql']}\n"

        prompt += """
请提供以下建议：
1. 每个查询的性能瓶颈分析
2. 具体的索引优化建议（CREATE INDEX 语句）
3. 查询改写建议（如果适用）
4. 数据库配置参数调整建议
5. 预期的性能提升幅度

请以 JSON 格式返回，包含 "analysis"（数组）和 "summary" 字段。
"""

        return self._call_llm(prompt)

    def _call_llm(self, prompt):
        """调用本地 Ollama 或 OpenRouter API。"""
        # 优先尝试本地 Ollama
        try:
            result = subprocess.run(
                ["ollama", "run", "qwen2.5:14b", prompt],
                capture_output=True, text=True, timeout=120
            )
            return result.stdout
        except (subprocess.TimeoutExpired, FileNotFoundError):
            pass

        # 回退到 OpenRouter API
        try:
            import requests
            headers = {
                "Authorization": f"Bearer {os.environ.get('OPENROUTER_API_KEY', '')}",
                "Content-Type": "application/json",
                "HTTP-Referer": "https://selfvps.net",
            }
            payload = {
                "model": "anthropic/claude-3.5-sonnet",
                "messages": [{"role": "user", "content": prompt}]
            }
            resp = requests.post(
                "https://openrouter.ai/api/v1/chat/completions",
                headers=headers, json=payload, timeout=60
            )
            return resp.json()["choices"][0]["message"]["content"]
        except Exception as e:
            return f"LLM call failed: {e}"

    def generate_index_recommendations(self, slow_queries):
        """生成具体的索引优化建议。"""
        report = self.generate_ai_report(slow_queries)
        return report


# 主入口
if __name__ == "__main__":
    analyzer = SlowQueryAnalyzer("mysql")
    queries = analyzer.extract_slow_queries(hours=24)

    if queries:
        print(f"发现 {len(queries)} 个慢查询")
        report = analyzer.generate_ai_report(queries)
        print(report)

        # 保存到报告目录
        os.makedirs("/var/log/db-optimizer/reports", exist_ok=True)
        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
        with open(f"/var/log/db-optimizer/reports/report_{timestamp}.md", "w") as f:
            f.write(f"# 数据库优化报告\n")
            f.write(f"生成时间: {datetime.now().isoformat()}\n\n")
            f.write(f"发现慢查询数量: {len(queries)}\n\n")
            f.write(f"## AI 分析报告\n{report}\n")
    else:
        print("未发现慢查询，数据库运行良好。")

#!/usr/bin/env python3
"""AI-powered database configuration optimizer."""

import subprocess
import os

def get_current_config(db_type="mysql"):
    """获取当前数据库配置。"""
    if db_type == "mysql":
        result = subprocess.run(
            ["mysql", "-u", "root", "-e", "SHOW VARIABLES;"],
            capture_output=True, text=True
        )
        return result.stdout
    else:
        result = subprocess.run(
            ["psql", "-U", "postgres", "-c", "SHOW ALL;"],
            capture_output=True, text=True
        )
        return result.stdout

def get_server_info():
    """获取服务器资源信息。"""
    info = {}

    # 总内存
    with open('/proc/meminfo', 'r') as f:
        meminfo = f.read()
        for line in meminfo.split('\n'):
            if line.startswith('MemTotal'):
                info['total_memory_mb'] = int(line.split()[1]) // 1024
            elif line.startswith('MemAvailable'):
                info['available_memory_mb'] = int(line.split()[1]) // 1024

    # CPU 核心数
    info['cpu_cores'] = os.cpu_count() or 1

    # 磁盘空间
    stat = os.statvfs('/')
    info['disk_total_gb'] = stat.f_blocks * stat.f_bsize // (1024**3)
    info['disk_free_gb'] = stat.f_bavail * stat.f_bsize // (1024**3)

    return info

def generate_config_recommendations(db_type="mysql"):
    """生成配置优化建议。"""
    config = get_current_config(db_type)
    server = get_server_info()

    prompt = f"""你是一个数据库性能优化专家。请根据以下服务器资源和当前配置，给出优化建议。

服务器信息:
- 总内存: {server['total_memory_mb']} MB
- 可用内存: {server['available_memory_mb']} MB
- CPU 核心数: {server['cpu_cores']}
- 磁盘剩余: {server['disk_free_gb']} GB

当前数据库配置（关键参数）:
{config[:3000]}

请针对以下参数给出优化建议（基于服务器资源）：
1. innodb_buffer_pool_size (MySQL) / shared_buffers (PostgreSQL)
2. innodb_log_file_size (MySQL) / wal_buffers (PostgreSQL)
3. max_connections
4. query_cache_size / effective_cache_size
5. work_mem (PostgreSQL) / sort_buffer_size (MySQL)
6. 其他关键参数

请给出具体的数值建议和理由。以 JSON 格式返回。
"""

    try:
        result = subprocess.run(
            ["ollama", "run", "qwen2.5:14b", prompt],
            capture_output=True, text=True, timeout=120
        )
        return result.stdout
    except Exception:
        return "无法调用 AI 模型进行配置分析"

if __name__ == "__main__":
    recommendations = generate_config_recommendations("mysql")
    print(recommendations)

# 每天凌晨 2 点执行慢查询分析
0 2 * * * /usr/bin/python3 /opt/db-optimizer/analyzer.py >> /var/log/db-optimizer/cron.log 2>&1

# 每周日凌晨 3 点执行配置优化分析
0 3 * * 0 /usr/bin/python3 /opt/db-optimizer/config_optimizer.py >> /var/log/db-optimizer/cron.log 2>&1

# /etc/systemd/system/db-optimizer.timer
[Unit]
Description=Daily Database Optimization Analysis

[Timer]
OnCalendar=*-*-* 02:00:00
Persistent=true

[Install]
WantedBy=timers.target

# /etc/systemd/system/db-optimizer.service
[Unit]
Description=Database Optimization Analysis Service

[Service]
Type=oneshot
ExecStart=/usr/bin/python3 /opt/db-optimizer/analyzer.py
WorkingDirectory=/opt/db-optimizer
StandardOutput=append:/var/log/db-optimizer/service.log
StandardError=append:/var/log/db-optimizer/service.log

sudo systemctl enable --now db-optimizer.timer

import matplotlib
matplotlib.use('Agg')
import matplotlib.pyplot as plt

def generate_performance_chart(queries, output_path="/var/log/db-optimizer/performance.png"):
    """生成查询性能趋势图。"""
    times = [q['query_time'] for q in queries]
    labels = [f"Q{i+1}" for i in range(len(times))]

    fig, ax = plt.subplots(figsize=(12, 6))
    ax.barh(labels, times, color='#4CAF50' if times[-1] < 2 else '#F44336')
    ax.set_xlabel('Execution Time (seconds)')
    ax.set_title('Top Slow Queries (Last 24h)')
    ax.axvline(x=1.0, color='red', linestyle='--', alpha=0.5, label='Threshold (1s)')
    ax.legend()
    plt.tight_layout()
    plt.savefig(output_path, dpi=150)
    plt.close()

import requests

def send_alert(message, chat_id=None, webhook_url=None):
    """发送告警通知。"""
    if webhook_url:
        # Slack/钉钉/Webhook
        requests.post(webhook_url, json={"text": message})
    elif chat_id:
        # Telegram Bot
        bot_token = os.environ.get("TELEGRAM_BOT_TOKEN")
        url = f"https://api.telegram.org/bot{bot_token}/sendMessage"
        requests.post(url, json={
            "chat_id": chat_id,
            "text": message,
            "parse_mode": "Markdown"
        })

-- 优化前（3.2 秒）
SELECT * FROM orders WHERE customer_id = 12345 AND status = 'pending'
ORDER BY created_at DESC LIMIT 20;

-- AI 建议的索引
CREATE INDEX idx_orders_customer_status_created
ON orders(customer_id, status, created_at DESC);

-- 优化后（12ms）— 快 267 倍

-- 优化前（循环 500 次查询，总计 4.8 秒）
SELECT * FROM users;  -- 1 次
-- 然后对每个用户执行：
SELECT * FROM orders WHERE user_id = ?;  -- 500 次

-- AI 建议改用 JOIN
SELECT u.*, o.* FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active';

-- 优化后（1 次查询，280ms）— 快 17 倍

-- 快速回滚：删除刚创建的索引
DROP INDEX idx_orders_customer_status_created ON orders;

-- 回滚配置：编辑配置文件后重启
sudo systemctl restart mysql