시스템 관리_38 MySQL 및 MariaDB Error 1040 Too many connections 커넥션 초과 오류 원인 분석 및 긴급 해결 가이드

# 1. 클라이언트 관점: mysql 접속 시 Error 1040 발생 확인
$ mysql -u appuser -p -h 127.0.0.1
# Enter password:
# ERROR 1040 (HY000): Too many connections

# 2. OS 관점: 3306 포트에 연결된 ESTABLISHED 상태의 클라이언트 커넥션 수 카운트
$ ss -tan state established '( sport = :3306 )' | wc -l
# 152
# -> 현재 DB 포트에 맺어져 있는 활성 TCP 세션 수 확인

# 3. 관리자 전용 계정으로 로컬 소켓 또는 관리 포트를 통한 긴급 접속 시도
# MySQL은 일반 사용자가 max_connections에 도달해도 CONNECTION_ADMIN(SUPER) 권한을 가진 계정을 위해 1개의 추가 연결을 보장함
$ mysql -u root -p -S /var/run/mysqld/mysqld.sock

# 4. MySQL 내부 현재 커넥션 설정치 및 상태 통계 확인
mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

mysql> SHOW GLOBAL STATUS LIKE 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 151   |
+-------------------+-------+

# 5. 장시간 Sleep 상태로 커넥션을 점유하고 있는 좀비 세션 식별
mysql> SELECT id, user, host, db, command, time, state 
       FROM information_schema.processlist 
       WHERE command = 'Sleep' AND time > 60 
       ORDER BY time DESC LIMIT 10;

#!/bin/bash
# check_mysql_connections.sh - Diagnose MySQL/MariaDB Error 1040 and connection pool state

MYSQL_USER=${1:-"root"}
MYSQL_PASS=${2:-""}
SOCKET_PATH=${3:-"/var/run/mysqld/mysqld.sock"}

echo "=========================================="
echo " [MySQL/MariaDB 커넥션 고갈 진단 (Error 1040)]"
echo "=========================================="

# MySQL 실행 확인
if ! pgrep -x "mysqld" &>/dev/null && ! pgrep -x "mariadbd" &>/dev/null; then
    echo "  [오류] mysqld 또는 mariadbd 데몬이 실행 중이지 않습니다."
    exit 1
fi

MYSQL_CMD="mysql -u$MYSQL_USER"
[ -n "$MYSQL_PASS" ] && MYSQL_CMD="$MYSQL_CMD -p$MYSQL_PASS"
[ -S "$SOCKET_PATH" ] && MYSQL_CMD="$MYSQL_CMD -S $SOCKET_PATH"

# 1. 커넥션 변수 및 상태 조회
CONN_DATA=$($MYSQL_CMD -N -B -e "
SELECT 
    (SELECT variable_value FROM performance_schema.global_variables WHERE variable_name = 'max_connections'),
    (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Threads_connected'),
    (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Max_used_connections'),
    (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Connection_errors_max_connections');
" 2>/dev/null)

if [ -z "$CONN_DATA" ]; then
    echo "  [주의] performance_schema 조회가 실패하여 information_schema/SHOW 명령어로 재시도합니다."
    MAX_CONN=$($MYSQL_CMD -N -B -e "SHOW VARIABLES LIKE 'max_connections';" 2>/dev/null | awk '{print $2}')
    CURR_CONN=$($MYSQL_CMD -N -B -e "SHOW STATUS LIKE 'Threads_connected';" 2>/dev/null | awk '{print $2}')
    PEAK_CONN=$($MYSQL_CMD -N -B -e "SHOW STATUS LIKE 'Max_used_connections';" 2>/dev/null | awk '{print $2}')
    ERR_CONN="N/A"
else
    MAX_CONN=$(echo "$CONN_DATA" | awk '{print $1}')
    CURR_CONN=$(echo "$CONN_DATA" | awk '{print $2}')
    PEAK_CONN=$(echo "$CONN_DATA" | awk '{print $3}')
    ERR_CONN=$(echo "$CONN_DATA" | awk '{print $4}')
fi

if [ -z "$MAX_CONN" ]; then
    echo "  [오류] MySQL에 접속할 수 없습니다. 비밀번호 또는 소켓 경로를 확인하세요."
    exit 1
fi

echo -e "\n[1] 커넥션 수치 분석:"
echo "  - 최대 허용 커넥션 (max_connections) : ${MAX_CONN}"
echo "  - 현재 활성 커넥션 (Threads_connected) : ${CURR_CONN}"
echo "  - 가동 중 최대 피크치 (Max_used_connections): ${PEAK_CONN}"
echo "  - 1040 에러 발생 누적 횟수            : ${ERR_CONN}"

USAGE_PCT=$(awk "BEGIN {printf \"%.2f\", ($CURR_CONN / $MAX_CONN) * 100}")
echo "  - 현재 커넥션 점유율                 : ${USAGE_PCT}%"

if [ "$CURR_CONN" -ge "$MAX_CONN" ]; then
    echo "  [치명적 위험] 커넥션이 한계에 도달했습니다! 일반 사용자의 모든 신규 접속이 거부됩니다."
elif [ $(echo "$USAGE_PCT > 85.0" | bc -l) -eq 1 ]; then
    echo "  [경고] 커넥션 점유율이 85%를 초과하여 임계치에 도달했습니다."
else
    echo "  [정상] 커넥션 풀 여유 공간이 확보되어 있습니다."
fi

# 2. Sleep 상태의 유휴 세션 분석
echo -e "\n[2] 유휴(Sleep) 방치 커넥션 현황:"
SLEEP_COUNT=$($MYSQL_CMD -N -B -e "
SELECT count(*) FROM information_schema.processlist WHERE command = 'Sleep';
" 2>/dev/null)
echo "  - 총 Sleep 상태 커넥션 수: ${SLEEP_COUNT}개"

LONG_SLEEP=$($MYSQL_CMD -N -B -e "
SELECT count(*) FROM information_schema.processlist WHERE command = 'Sleep' AND time > 60;
" 2>/dev/null)
echo "  - 60초 이상 방치된 Sleep 커넥션 수: ${LONG_SLEEP}개"

# 3. 호스트별 커넥션 점유 상위 5개 추출
echo -e "\n[3] 클라이언트 호스트(IP)별 커넥션 점유 순위 Top 5:"
$MYSQL_CMD -e "
SELECT SUBSTRING_INDEX(host, ':', 1) AS client_ip, count(*) AS connection_count 
FROM information_schema.processlist 
GROUP BY client_ip 
ORDER BY connection_count DESC 
LIMIT 5;
" 2>/dev/null | sed 's/^/  /'

echo -e "\n=========================================="
echo " [진단 완료]"
echo "=========================================="