[ RDS ] 쉘 스크립트에서 MySQL 연결하여 DB에 데이터 입력하기

 

700여개 출판사와 100여개 물류사, 한꺼번에 해킹 피해? 주문·배송 솔루션 랜섬웨어 감염

(로그 백업의 중요성을 알려주는 따끈따끈한 뉴스)

 

---

로그 파일 임시 생성

l       2024-08-01      user1   /api/v1/products
v       2024-08-02      user2   /api/v1/orders
c       2024-08-03      user3   /api/v1/users
o       2024-08-04      user4   /api/v1/cart
l       2024-08-05      user5   /api/v1/login
v       2024-08-06      user6   /api/v1/logout
c       2024-08-07      user7   /api/v1/profile
o       2024-08-08      user8   /api/v1/settings

먼저, DB에 잘 삽입되어지는지 예시를 확인하기 위해 log.log 파일을 임의로 생성해주었다.

---

RDS 서비스 활성화

서비스 활성화 예시 이미지

사용하는 Cloud에서 RDS를 생성해주자.

NHN Cloud의 경우, Console 창에서 Database - RDS for MySQL로 진입하자.

만약 Console의 Database 탭에서 RDS 관련 내용이 없다면, 서비스 선택 - `Ctrl+F` RDS로 관련 서비스들을 찾을 수 있고,
항목을 클릭해서 서비스 활성화를 진행하면 된다.

 

다음과 같이 DB 인스턴스를 생성해주었다.

보안 그룹은 이미 만들어둔 것이 있었으나 DB 인스턴스로 적용이 불가능하여(일반 인스턴스에만 적용되는 듯하다)
동일한 가용 영역에 위치한 Private 인스턴스로부터의 3306 포트를 열어두었다.

초기 설정을 위해 접속 용도로 Bastion을 열어주었다.

(나중에 확인해보니 private로 접속 안 한다고 하면 private 막아두기!)

 

---

스키마 생성

하단의 DB 스키마 & 사용자 탭을 클릭하여 `log_backup`이라는 스키마를 생성해주었다.

해당 스키마는 MySQL 접속 후 `show databases;`로 확인할 수 있다.

---

Bastion 인스턴스에서 MySQL 접속

인스턴스 생성시 Bastion을 통해 접속 가능한 Private 서브넷에 RDS 인스턴스를 생성했으므로, Bastion을 통해서만 직접 접속이 가능하다.

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

# 접속 명령어
mysql -u 사용자명 -p 주소ID.mysql.rds.nhncloudservice.com

# 접속 명령어 예시 (유저명은 DB 스키마 & 사용자 창에서 찾을 수 있음)
mysql -h ad0d ... internal.kr1.mysql.rds.nhncloudservice.com -P 3306 -u log_admin_A -p

(접속을 위해서는 Bastion 서버에 MySQL 클라이언트가 설치되어 있어야 한다)

 

CREATE TABLE app_logs (
    type varchar(10),
    log_data date,
    user_name varchar(20),
    endpoint varchar(100)
);

임의로 log.log 파일을 넣어보기 위해 app_logs 라는 테이블을 생성해보자.

---

로그 삽입 쉘스크립트 작성 (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_FILE="log.log"

# 로그 파일을 한 줄씩 읽어서 처리
while IFS=',' read -r type log_data user_name endpoint; do
    mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -P 3306 -p"$MYSQL_PASSWORD" "$MYSQL_DATABASE" -e \
    "INSERT INTO $MYSQL_TABLE VALUES ('$type', '$log_data', '$user_name', '$endpoint');"
done < "$LOG_FILE"

DB에 위 로그(log.log)를 삽입하는 insert_query.sh를 작성해주었다.

 

insert_query.sh로 성공적으로 RDS에 내역이 전송된 것을 알 수 있다.

만약 실제 log 파일에서도 동일한 오류(too long ~ 및 구분자) 발생하면 log파일 구분자 알아서 지정해주도록 하는 쉘스크립트 코드 만들어서 insert_query.sh로 실행되도록 하기!!!

---

로그 삽입 쉘스크립트 작성 (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_FILE="/home/ubuntu/log.log"

# 배치 크기 설정
BATCH_SIZE=1000

# 쿼리 준비
query_prefix="INSERT INTO $MYSQL_TABLE (type, log_data, user_name, endpoint) VALUES "
query_values=""
count=0

# 로그 파일을 한 줄씩 읽어서 처리
while IFS=$'\t' read -r type log_data user_name endpoint; do
    # 각 레코드를 쿼리 형태로 추가
    query_values="$query_values('$type', '$log_data', '$user_name', '$endpoint'),"

    # 카운트 증가
    ((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

---

로그 삽입 쉘스크립트 작성 (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_FILE="/home/ubuntu/log.log"

# MySQL 명령어 실행
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'
(type, log_data, user_name, endpoint);
"

---

 

이후 할 것

- 작업 완료된 내역에 대해 로그 라인 수 등을 이메일로 전송

- 통합 완료된 로그를 1차 백업 경로에 적재(인스턴스 디스크 부담을 줄이기 위해)