Infile Load 방식으로 병합된 로그 MySQL에 저장하기

Infile Load 방식으로 병합된 로그 MySQL에 저장하기

 

https://gayeon-l.tistory.com/460

 

인스턴스 설정과 DB 저장을 위한 사전 설정들은 위 글에 작성했다.

 

 

현재 파일 구조

~/log_aggr: 날짜별 로그 병합 파일이 있는 디렉토리

~/log_db: log_aggr의 로그 파일들을 데이터베이스 스키마에 맞게 정렬한 로그 디렉토리

 

 

Infile Load 방식

• MySQL에서 대량의 데이터를 효율적으로 테이블에 삽입할 때 사용되는 명령어이다.
• 외부 파일에서 데이터를 읽어와서 MySQL 테이블에 직접 삽입할 수 있다.
• 데이터 로딩 작업을 빠르고 효율적으로 처리할 수 있어 많은 양의 데이터를 처리할 때 유용하다.

 

/home/ubuntu/log/log-db-store.sh

Infile Load 방식으로 DB에 로그 파일을 적재하는 스크립트

 1 #!/bin/bash
 2
 3 # MySQL 클라이언트 경로
 4 MYSQL_CMD="/usr/bin/mysql"
 5
 6 # 데이터베이스 설정
 7 DB_HOST="ip"
 8 DB_USER="db 유저명"
 9 DB_PASS="db 접속 패스워드"
10 DB_NAME="데이터베이스명"
11 MYSQL_TABLE="테이블명"
12
13 # 로그 파일 경로 설정
14 db_dir="/home/ubuntu/log_db"
15 LOG_FILE="$db_dir/$(date +%Y%m%d)_for_db.log"
16 ERROR_LOG="/tmp/mysql_error.log"
17 REMOTE_FILE="/var/lib/mysql-files/$(basename "$LOG_FILE")"
18 HOME_REMOTE_FILE="/home/ubuntu/$(basename "$LOG_FILE")"
19
20 # 로그 파일이 있는지 확인
21 if [ -f "$LOG_FILE" ]; then
22     echo "로그 파일이 발견되었습니다: $LOG_FILE"
23
24     # 파일을 원격 서버의 사용자 홈 디렉토리로 복사
25     echo "파일을 원격 서버의 사용자 홈 디렉토리로 복사 중..."
26     scp "$LOG_FILE" ubuntu@$DB_HOST:"$HOME_REMOTE_FILE"
27
28     # 원격 서버에서 파일을 MySQL 읽기 디렉토리로 이동
29     echo "파일을 MySQL 읽기 디렉토리로 이동 중..."
30     ssh ubuntu@$DB_HOST "sudo mv $HOME_REMOTE_FILE $REMOTE_FILE"
31     ssh ubuntu@$DB_HOST "sudo chown mysql:mysql $REMOTE_FILE"
32     ssh ubuntu@$DB_HOST "sudo chmod 644 $REMOTE_FILE"
33
34     # MySQL LOAD DATA INFILE 쿼리 실행
35     echo "데이터베이스에 데이터 삽입 중..."
36     INSERT_RESULT=$(ssh ubuntu@$DB_HOST "$MYSQL_CMD -h $DB_HOST -u $DB_USER -p$DB_PASS $DB_NAME -e \"
37         LOAD DATA INFILE '$REMOTE_FILE'
38         INTO TABLE $MYSQL_TABLE
39         FIELDS TERMINATED BY '    '  # 공백 또는 탭 구분자로 변경해야 할 수 있음
40         LINES TERMINATED BY '\\n'
41         (sequence_number, log_date, log_user, log_endpoint);
42     \" 2>&1")
43
44     # 삽입 결과 및 오류 로그 확인
45     if [ $? -eq 0 ]; then
46         echo "데이터 삽입 완료."
47     else
48         echo "데이터 삽입 중 오류 발생. 오류 내용:"
49         echo "$INSERT_RESULT"
50         echo "$INSERT_RESULT" >> "$ERROR_LOG"
51     fi
52
53     # 원격 서버에서 파일 삭제 (선택 사항)
54     ssh ubuntu@$DB_HOST "sudo rm -f $REMOTE_FILE"
55
56 else
57     echo "로그 파일을 찾을 수 없습니다: $LOG_FILE"
58 fi

 

로컬 시스템의 로그 파일을 원격 MySQL 서버에 전송하고 Infile 방식으로 데이터베이스에 데이터를 삽입하는 코드이다.

 

 

 1 #!/bin/bash
 2
 3 # MySQL 클라이언트 경로
 4 MYSQL_CMD="/usr/bin/mysql"
 5
 6 # 데이터베이스 설정
 7 DB_HOST="ip"
 8 DB_USER="db 유저명"
 9 DB_PASS="db 접속 패스워드"
10 DB_NAME="데이터베이스명"
11 MYSQL_TABLE="테이블명"

 

데이터베이스 관련 변수를 설정한다.

여기서 MYSQL_CMD는 MySQL 클라이언트 명령어의 경로다.

 

 

13 # 로그 파일 경로 설정
14 db_dir="/home/ubuntu/log_db"
15 LOG_FILE="$db_dir/$(date +%Y%m%d)_for_db.log"
16 ERROR_LOG="/tmp/mysql_error.log"
17 REMOTE_FILE="/var/lib/mysql-files/$(basename "$LOG_FILE")"
18 HOME_REMOTE_FILE="/home/ubuntu/$(basename "$LOG_FILE")"

 

 

• LOG_FILE: 로컬에서 로그 파일의 전체 경로
• ERROR_LOG: 오류 로그 파일 경로
• REMOTE_FILE: 원격 서버의 MySQL 파일 읽기 디렉토리 경로
• HOME_REMOTE_FILE: 원격 서버의 사용자 홈 디렉토리 경로

 

25 # 파일을 원격 서버의 사용자 홈 디렉토리로 복사
26 scp "$LOG_FILE" ubuntu@$DB_HOST:"$HOME_REMOTE_FILE"

29 # 원격 서버에서 파일을 MySQL 읽기 디렉토리로 이동
30 ssh ubuntu@$DB_HOST "sudo mv $HOME_REMOTE_FILE $REMOTE_FILE"
31 ssh ubuntu@$DB_HOST "sudo chown mysql:mysql $REMOTE_FILE"
32 ssh ubuntu@$DB_HOST "sudo chmod 644 $REMOTE_FILE"

 

 

로그 파일이 존재하는지 확인하고 scp로 로컬의 로그 파일을 데이터베이스 서버의 사용자 홈 디렉토리로 복사한다.

그 다음 ssh로 파일을 MySQL 읽기 디렉토리로 이동시키고 파일의 소유자와 권한을 설정했다.

 

 

35 # MySQL LOAD DATA INFILE 쿼리 실행
36 INSERT_RESULT=$(ssh ubuntu@$DB_HOST "$MYSQL_CMD -h $DB_HOST -u $DB_USER -p$DB_PASS $DB_NAME -e "
37 LOAD DATA INFILE '$REMOTE_FILE'
38 INTO TABLE $MYSQL_TABLE
39 FIELDS TERMINATED BY ' ' # 공백 또는 탭 구분자로 변경해야 할 수 있음
40 LINES TERMINATED BY '\n'
41 (sequence_number, log_date, log_user, log_endpoint);
42 " 2>&1")

 

그 다음으로 LOAD DATA INFILE 명령어로 데이터베이스 테이블에 데이터를 삽입한다.

삽입 결과를 INSERT_RESULT에 저장해서 실행한다.

 

 

45 if [ $? -eq 0 ]; then
46     echo "데이터 삽입 완료."
47 else
48     echo "데이터 삽입 중 오류 발생. 오류 내용:"
49     echo "$INSERT_RESULT"
50     echo "$INSERT_RESULT" >> "$ERROR_LOG"
53 # 원격 서버에서 파일 삭제 (선택 사항)
54 ssh ubuntu@$DB_HOST "sudo rm -f $REMOTE_FILE"

 

데이터 삽입/실패 후에는 로그 파일을 삭제한다.

 

 

하지만 바로 실행되지 않았다.

Infile Load는 사용하기 위한 사전 설정이 필요하기 때문이다.

 

클라이언트 측에서 파일을 읽어올 수 있도록 LOCAL 옵션을 사용해야 한다.

이 옵션을 사용하기 위해서는 MySQL 인스턴스의 설정 파일에서 local_infile 변수를 ON(1)으로 설정하면 된다.

 

 

설정 파일

 

 

local_infile을 ON으로 설정하기

 

 

sudo systemctl restart mysql

 

설정파일을 수정하고 나서 MySQL을 재시작한다.

 

 

./log-db-store.sh

 

그리고 스크립트를 실행하면

 

 

 

db에 로그 데이터들이 적재되었다.

 

 

 

스크립트에서 db에 적재 후 로그 파일을 삭제하도록 했기 때문에 로컬에는 파일이 남아있지 않는다.

 

 

 

db에 로그 기록이 매일 적재될 수 있도록 매일 밤 11시 29분에 db에 저장하는 스크립트를 실행하도록 크론탭에 등록했다.

 

 

 

또한 db에 적재하기 전에 db 스키마에 맞게 로그 파일들이 정리되어야 하므로 db 저장 4분 전에 정리용 스크립트도 실행하도록 했다.