#!/bin/bash
# mysql_connection_manager.sh

MYSQL_USER="root"
LOG_FILE="/var/log/mysql_connection_manager.log"
MAX_IDLE_TIME=300    # 5分钟
MAX_TOTAL_CONNECTIONS=50
CHECK_INTERVAL=60    # 60秒检查一次

log_message() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" >> $LOG_FILE
}

monitor_connections() {
    while true; do
        # 获取连接统计
        STATS=$(mysql -u$MYSQL_USER -e "
            SELECT 
                COUNT(*) as total,
                SUM(CASE WHEN command = 'Sleep' THEN 1 ELSE 0 END) as idle,
                SUM(CASE WHEN command != 'Sleep' THEN 1 ELSE 0 END) as active,
                MAX(time) as max_idle
            FROM information_schema.processlist
            WHERE user = 'jak_db';
        " -s -N 2>/dev/null)
        
        if [ $? -eq 0 ]; then
            TOTAL=$(echo $STATS | awk '{print $1}')
            IDLE=$(echo $STATS | awk '{print $2}')
            ACTIVE=$(echo $STATS | awk '{print $3}')
            MAX_IDLE=$(echo $STATS | awk '{print $4}')
            
            log_message "监控统计: 总=$TOTAL, 空闲=$IDLE, 活动=$ACTIVE, 最长空闲=${MAX_IDLE}秒"
            
            # 检查是否需要清理
            if [ $TOTAL -gt $MAX_TOTAL_CONNECTIONS ]; then
                log_message "警告: 连接数超过阈值 ($MAX_TOTAL_CONNECTIONS)"
                cleanup_idle_connections
            fi
            
            # 记录详细连接信息
            if [ $TOTAL -gt 10 ]; then
                mysql -u$MYSQL_USER -e "
                    SELECT 
                        user,
                        host,
                        db,
                        command,
                        time,
                        state
                    FROM information_schema.processlist
                    WHERE user = 'jak_db'
                    ORDER BY time DESC
                    LIMIT 5;
                " >> $LOG_FILE 2>/dev/null
            fi
        else
            log_message "错误: 无法连接MySQL"
        fi
        
        sleep $CHECK_INTERVAL
    done
}

cleanup_idle_connections() {
    log_message "开始清理空闲连接..."
    
    # 获取需要清理的连接ID
    CONNECTION_IDS=$(mysql -u$MYSQL_USER -e "
        SELECT GROUP_CONCAT(id SEPARATOR ' ')
        FROM information_schema.processlist
        WHERE user = 'jak_db'
        AND command = 'Sleep'
        AND time > $MAX_IDLE_TIME
        ORDER BY time DESC
        LIMIT 10
    " -s -N 2>/dev/null)
    
    if [ -n "$CONNECTION_IDS" ]; then
        for CONN_ID in $CONNECTION_IDS; do
            mysql -u$MYSQL_USER -p$MYSQL_PASS -e "KILL $CONN_ID;" 2>/dev/null
            log_message "已清理连接: $CONN_ID"
        done
    else
        log_message "没有需要清理的空闲连接"
    fi
}

# 启动监控
log_message "=== MySQL连接监控启动 ==="
monitor_connections
