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

2024. 8. 12. 10:20·🌥️Cloud Study🌥️/Etc

 

 

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

출판사와 출판물류기업 간 주문·배송 네트워킹 솔루션 제공업체 모아시스에서 8월 1일 랜섬웨어 공격을 받은 것으로 드러났다. 이번 공격으로 해당 솔루션을 사용하는 최소 700개에서 최대 1,000

m.boannews.com

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

 


로그 파일 임시 생성

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차 백업 경로에 적재(인스턴스 디스크 부담을 줄이기 위해)

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

[ SQL ] INSERT와 BULK INSERT의 차이, Infile Load 방식에 대해  (0) 2024.08.12
[ 모니터링 ] Prometheus Federation 설정  (0) 2024.08.12
[ Logging ] 서버 내 로그 관리 정책 수립 및 구현  (0) 2024.08.09
[ Prometheus ] 프로메테우스 설치 및 서비스 설정  (0) 2024.08.09
[ 모니터링 ] 인스턴스 정지 감지를 위한 Prometheus 설정  (0) 2024.08.09
'🌥️Cloud Study🌥️/Etc' 카테고리의 다른 글
  • [ SQL ] INSERT와 BULK INSERT의 차이, Infile Load 방식에 대해
  • [ 모니터링 ] Prometheus Federation 설정
  • [ Logging ] 서버 내 로그 관리 정책 수립 및 구현
  • [ Prometheus ] 프로메테우스 설치 및 서비스 설정
L_Chae
L_Chae
🎮😻🤓🖥✨
  • L_Chae
    Cherish
    L_Chae
  • 전체
    오늘
    어제
    • 분류 전체보기
      • 🌥️Cloud Study🌥️
        • AWS
        • NHN
        • KT
        • Microsoft (Azure 900)
        • TroubleShooting
        • IaC
        • Etc
      • STUDY
        • English
        • Japanese
        • 개인정보보호 관련
      • 초등학교 AI 교육 진행
        • 2024학년도 1학년-2학년
        • 2025학년도 4학년
      • 2024 KDT 📕
        • Lecture
        • Study 📗
        • Study-JAVA
        • Project
        • etc
      • INTERLUDE ✦
        • 2022 SYSTEM STUDY
        • 2022 Winter Study
        • 2023 AutoMobility STUDY
        • 2023 Summer Study (CPPG)
        • 2023 Reversing STUDY
        • etc
      • Private🔒
        • MacBook 💻
        • Screenshot 🩷
        • Photo 🖼️
        • FFXIV - Backup 🎮
        • Tistory
      • PBL 📗
        • 논문 스터디
        • Backup
      • Project 🖤
        • 2022-2) 개인정보보호 소학회
        • 2022-2) winter GURU2 - iOS
        • 2023-1) PBL 3
        • 2023-2) PBL 4
        • 2024-1) SW-AI교육 실습 일지
      • CTF 🚩
        • 2022
        • 2023
      • News scrap 📰
        • 2022
        • 2023
      • 2022 공부 로그 🐯
      • 2023 공부 로그🐰
  • 블로그 메뉴

    • 글쓰기
    • 홈
    • 태그
    • 방명록
  • 링크

  • 공지사항

  • 인기 글

  • 태그

    jlpt n1 도전기
    JLPT N1
    ansible galaxy
    JLPT
    N1
    멱등성
    deprecation warning
    content collection
    ansible
  • 최근 댓글

  • 최근 글

  • hELLO· Designed By정상우.v4.10.3
L_Chae
[ RDS ] 쉘 스크립트에서 MySQL 연결하여 DB에 데이터 입력하기
상단으로

티스토리툴바