Spring 어플리케이션에 접속하면 자동으로 로그가 생성되는데, 앞선 글에서 특정 행위에 대한 로그만 filtering 및 merged 해서 어플리케이션 인스턴스 상에 저장해두었다.

이번에는 필터링 및 병합 된 어플리케이션 로그 파일을 DB에 적재해보도록 하자.

 

# Private 인스턴스에 MySQL 클라이언트 설치 (Ubuntu)
sudo apt-get update
sudo apt-get install mysql-client -y

# MySQL 서버 설정 변경
sudo nano /etc/mysql/my.cnf

[mysql]
local_infile=1

로그 형식 확인

# 예시 로그
l       2024-08-12T01:15:57.757428060   /products/      GET     -       -       42      -       -       -       192.168.1.20    Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/127.0.0.0 Safari/537.36null

먼저 로그의 예시는 다음과 같다.

 

로그의 필드 구성은 다음과 같다 :

로그 유형(예: list) / 타임스탬프 / 엔드포인트 / HTTP 메소드 / User ID / Session ID / 응답 시간 / HTTP 상태코드 / 응답 길이 / Source IP / User-Agent

 

해당 로그는 private 인스턴스의 /home/ubuntu/filter_logs 폴더에 로그가 생성된 날짜를 기준으로 YYYY-MM-DD 형식의 폴더가 위치, 그 하위에 YYYY-MM-DD_merged.log 라는 병합된 파일이 위치하는 구조이다.

DB 테이블 생성

CREATE TABLE application_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    log_level VARCHAR(10),              -- 'l'
    timestamp DATETIME(6),              -- '2024-08-12T01:15:57.757428060'
    endpoint VARCHAR(255),              -- '/products/'
    http_method VARCHAR(10),            -- 'GET'
    user_id VARCHAR(50),                -- '-' (NULL 허용 가능)
    session_id VARCHAR(50),             -- '-' (NULL 허용 가능)
    response_time INT,                  -- '42'
    status_code INT,                    -- '-' (NULL 허용 가능)
    content_length INT,                 -- '-' (NULL 허용 가능)
    source_ip VARCHAR(45),              -- '192.168.1.20'
    user_agent VARCHAR(1024),           -- 'Mozilla/5.0...'
    INDEX (timestamp),                  -- 성능 향상을 위해 인덱싱
    INDEX (endpoint),                   -- 성능 향상을 위해 인덱싱
    UNIQUE (timestamp, endpoint, http_method, source_ip)  -- 고유 조합으로 중복 방지
);

일단은 위 로그 구성 내용을 포함하는 DB 테이블을 만들어보자.

 

`user_id`, `session_id`, `status_code`, `content_length`는 로그에 값이 없을 수 있으므로 NULL을 허용하도록 설계했다.

`timestamp`, `endpoint` 필드에 인덱스를 설정하여 조회 성능을 향상시키려고 하였고,

`DATETIME(6)`을 통해 초 단위까지 정밀 기록이 가능하다. 

 

2024. 09. 13 수정 : `UNIQUE`를 이용하여 같은 시간(`timestamp`)에 같은 엔드포인트(`endpoint`), HTTP 메소드(`http_method`), 그리고 같은 클라이언트(`source_ip`)에서 온 요청은 중복되지 않도록 수정

DB 쿼리 쉘스크립트 코드 작성

앞선 글에서 사용했었던 Insert, Bulk Insert, Infile Load 방식의 쉘 스크립트 코드를 변경된 로그 형식에 맞게 조금씩 수정해주었다.

(각 인스턴스에 mysql 클라이언트가 설치되어 있어야 한다)

 

 

  • `LOG_FILE="$LOG_DIR/$LOG_DATE/${LOG_DATE}_merged.log"`: 날짜별로 생성된 파일 경로를 구성
  • if [[ -f "$LOG_FILE" ]]; then: 로그 파일이 존재하는지 확인한 후 작업을 진행
  • INSERT 방식: 각 레코드를 개별적으로 삽입함
  • Bulk INSERT 방식: 일정 수의 레코드를 모아서 한 번에 삽입 (대량의 데이터를 처리할 때 유리)
  • Infile Load 방식: 로그 파일을 MySQL에 빠르게 로드하는 효율적인 방법. (대량의 로그 데이터를 처리하는 데 최적)

 

Insert 방식

#!/bin/bash

# MySQL 접속 정보
MYSQL_HOST="your_mysql_endpoint"  # MySQL 서버의 엔드포인트 또는 IP 주소
MYSQL_USER="your_mysql_user"      # MySQL 사용자명
MYSQL_PASSWORD="your_mysql_password"  # MySQL 비밀번호
MYSQL_DATABASE="your_database_name"   # 스키마명 (데이터베이스 이름)
MYSQL_TABLE="your_table_name"      # 삽입할 테이블명

# 로그 파일 디렉터리 및 파일 경로
LOG_DIR="/home/ubuntu/filter_logs"
LOG_DATE=$(date '+%Y-%m-%d')
LOG_FILE="$LOG_DIR/$LOG_DATE/${LOG_DATE}_merged.log"

# 파일이 존재하는지 확인
if [[ -f "$LOG_FILE" ]]; then
    while IFS=$'\t' read -r log_level timestamp endpoint http_method user_id session_id response_time status_code content_length source_ip user_agent; do
        # INSERT 쿼리 생성 및 실행
        mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -P 3306 -p"$MYSQL_PASSWORD" "$MYSQL_DATABASE" -e "
        INSERT INTO $MYSQL_TABLE(log_level, timestamp, endpoint, http_method, user_id, session_id, response_time, status_code, content_length, source_ip, user_agent)
        VALUES ('$log_level', '$timestamp', '$endpoint', '$http_method', '$user_id', '$session_id', '$response_time', '$status_code', '$content_length', '$source_ip', '$user_agent');
        "
    done < "$LOG_FILE"
else
    echo "Log file not found: $LOG_FILE"
fi

 

Bulk Insert 방식

#!/bin/bash

# MySQL 접속 정보
MYSQL_HOST="your_mysql_endpoint"  # MySQL 서버의 엔드포인트 또는 IP 주소
MYSQL_USER="your_mysql_user"      # MySQL 사용자명
MYSQL_PASSWORD="your_mysql_password"  # MySQL 비밀번호
MYSQL_DATABASE="your_database_name"   # 스키마명 (데이터베이스 이름)
MYSQL_TABLE="your_table_name"      # 삽입할 테이블명

# 로그 파일 디렉터리 및 파일 경로
LOG_DIR="/home/ubuntu/filter_logs"
LOG_DATE=$(date '+%Y-%m-%d')
LOG_FILE="$LOG_DIR/$LOG_DATE/${LOG_DATE}_merged.log"

# 배치 크기 설정
BATCH_SIZE=1000

# 파일이 존재하는지 확인
if [[ -f "$LOG_FILE" ]]; then
    # 쿼리 준비
    query_prefix="INSERT INTO $MYSQL_TABLE (log_level, timestamp, endpoint, http_method, user_id, session_id, response_time, status_code, content_length, source_ip, user_agent) VALUES "
    query_values=""
    count=0

    while IFS=$'\t' read -r log_level timestamp endpoint http_method user_id session_id response_time status_code content_length source_ip user_agent; do
        # 각 레코드를 쿼리 형태로 추가
        query_values="$query_values('$log_level', '$timestamp', '$endpoint', '$http_method', '$user_id', '$session_id', '$response_time', '$status_code', '$content_length', '$source_ip', '$user_agent'),"

        # 카운트 증가
        ((count++))

        # BATCH_SIZE만큼 모이면 쿼리 실행
        if (( count % BATCH_SIZE == 0 )); then
            # 마지막 콤마 제거 및 쿼리 실행
            query="${query_prefix}${query_values%,};"
            mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -P 3306 -p"$MYSQL_PASSWORD" "$MYSQL_DATABASE" -e "$query"

            # 변수 초기화
            query_values=""
        fi
    done < "$LOG_FILE"

    # 남아있는 레코드가 있으면 마지막으로 한 번 더 실행
    if [[ -n "$query_values" ]]; then
        # 마지막 콤마 제거 및 쿼리 실행
        query="${query_prefix}${query_values%,};"
        mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -P 3306 -p"$MYSQL_PASSWORD" "$MYSQL_DATABASE" -e "$query"
    fi
else
    echo "Log file not found: $LOG_FILE"
fi

 

Infile Load 방식

#!/bin/bash
 
# MySQL 접속 정보
MYSQL_HOST="your_mysql_endpoint"  # MySQL 서버의 엔드포인트 또는 IP 주소
MYSQL_USER="your_mysql_user"      # MySQL 사용자명
MYSQL_PASSWORD="your_mysql_password"  # MySQL 비밀번호
MYSQL_DATABASE="your_database_name"   # 스키마명 (데이터베이스 이름)
MYSQL_TABLE="your_table_name"      # 삽입할 테이블명
 
# 로그 파일 디렉터리 및 파일 경로
LOG_DIR="/home/ubuntu/filter_logs"
LOG_DATE=$(date '+%Y-%m-%d')
LOG_FILE="$LOG_DIR/$LOG_DATE/${LOG_DATE}_merged.log"
 
# 파일이 존재하는지 확인
if [[ -f "$LOG_FILE" ]]; then
    # MySQL 명령어 실행 (IGNORE 옵션 추가, 중복된 내용이 DB에 삽입되는 것을 방지)
    mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -P 3306 -p"$MYSQL_PASSWORD" "$MYSQL_DATABASE" -e "
    LOAD DATA LOCAL INFILE '$LOG_FILE'
    INTO TABLE $MYSQL_TABLE
    FIELDS TERMINATED BY '\t'
    LINES TERMINATED BY '\n'
    (log_level, timestamp, endpoint, http_method, user_id, session_id, response_time, status_code, content_length, source_ip, user_agent)
    IGNORE 1 LINES;  # 헤더가 있을 경우 첫 번째 줄은 무시
    "
    echo "Log file loaded into MySQL successfully."
else
    echo "Log file not found: $LOG_FILE"
fi

 

수정: 2024. 09. 13 - `IGNORE` 부분 추가

헤더가 있을 경우 첫 번째 줄 무시

수행 결과 확인

성공적으로 DB에 적재 된 것을 확인할 수 있다.

 

'🌥️Cloud Study🌥️ > Etc' 카테고리의 다른 글

[ 모니터링 ] Grafana의 Alerting 이용, 인스턴스 다운 시 메일 알림 전송  (0) 2024.08.13
[ Logging ] Log Reporting, 이메일로 로그 전송하기  (0) 2024.08.13
[ SQL ] INSERT와 BULK INSERT의 차이, Infile Load 방식에 대해  (0) 2024.08.12
[ 모니터링 ] Prometheus Federation 설정  (0) 2024.08.12
[ RDS ] 쉘 스크립트에서 MySQL 연결하여 DB에 데이터 입력하기  (0) 2024.08.12

티스토리툴바