[Database] 논리적 백업, full-backup 실습 - mysqldump

안나·2024년 5월 16일
Database
0

Database

목록 보기
3/4
post-thumbnail

0. 들어가기 전

1. 배경

얼마 전 DB 보안점검을 하면서 보안 관련된 설정을 바꿨더니 db 재실행이 안되는 문제가 발생했다.

운영 전인 DB 였고 테이블 생성, 수정 쿼리문도 다 가지고 있었기 때문에 기존의 마리아디비 자체를 밀고 재설치 후 DB 구축을 다시하였다.

추후 중요 데이터나 사용자가 있었다면 데이터를 다 날리는 큰일이 일어났을 것이다.

상무님께서 데이터 백업작업을 요청하셨고 그 과정을 기록 하려고 한다.

2. 환경

  • os : Ubuntu 22.04.4 LTS
  • db : mysql Ver 15.1 Distrib 10.6.16-MariaDB

3. 논리적 백업도구로 full-backup만 하는 이유

우선 논리적 백업의 도구로 mysqldump 를 사용할 수 있다.

mysqldump 는 SQL 텍스트 형태로 백업 되는 논리적 백업 도구로 거의 모든 버전의 MySQL 및 MariaDB에서 사용할 수 있다. 그러나 크기가 큰 데이터 베이스의 경우 복구에 상당한 시간이 소요 될 수 있어 주의해야 한다.

그리고 mysqldump 에서는 증분/차등 백업을 잘 사용하지 않는 듯 싶다.

그래서 논리적 백업 실습에서는 풀 백업을 실습해보자.




1. 풀 백업 실습

1. Test 데이터베이스 생성

-- 데이터 베이스 생성
CREATE DATABASE IF NOT EXISTS test;

USE test;
-- 테이블 생성
CREATE TABLE IF NOT EXISTS test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

-- 데이터 추가
INSERT INTO test (name) VALUES
('John'),
('Alice'),
('Bob'),
('Emily'),
('Michael');

-- 데이터 확인
SELECT * FROM test;

2. 백업

DB 접속해서가 아닌 우분투 터미널 상에서 입력

  • 기본 문법
    mysqldump [options] 데이터베이스명 > 데이터베이스백업파일.sql
  • 옵션
    옵션설명
    -u, --user데이터베이스 접속할 아이디 지정
    -p, --password데이터베이스 접속에 사용할 비밀번호 지정
    -A, --all-databases모든 데이터베이스를 백업
    -B, --databases특정 데이터베이스를 백업. 이 옵션 다음에 백업할 데이터베이스 이름을 공백으로 구분하여 나열
    --no-data데이터 없이 스키마(구조)만 백업
    --routines스토어드 프로시저와 함수를 백업 파일에 포함
  • 예시
     mysqldump -uroot -p1234 test > test_backup.sql
    위 명령어를 입력하면 아래 사진과 같이 test_backup.sql 파일이 생성된다.
  • 결과

3. 복원

  • 기본 문법

    mysql [options] 데이터베이스명 < 데이터베이스백업파일.sql

  • 예시
    기존에 존재하던 test 테이블을 삭제하고 백업 시켜놓은 .sql 문으로 복원을 시켜보자.

     --- 테이블 삭제
 DROP DATABASE IF EXISTS test;
     mysql -uroot -p < test_backup.sql

    위 명령문을 치니 아래와 같은 오류가 뜬다.

    데이터베이스를 선택하지 않았다고 하길래 백업해두었던 .sql 문에서 주석 제외한 가장 첫번째 명령어에 아래 사진과 같이 USE test; 를 추가로 작성해주었다.

    저장 후 다시 명령어를 실행하고 테이블이 제대로 생성되었는지 확인해보았다.

  • 결과

    굳 .

    나이스

2. 운영서버에 백업 자동화 작업

0. 들어가기 전에

우선 현재 운영 서버 이외의 백업 서버가 존재하지 않아 백업 데이터를 같은 서버의 별도의 디렉토리에 보관 예정이다.

mysqldump 유틸리티를 사용하여 매일 자정 full-backup을 cron을 활용해 자동화를 하려고 한다.

풀백업을 데일리로 진행하는 것은 매우 비효율적이긴 하나 지금은 논리적 백업을 실습하기 위함이고 추후 물리적 백업으로 변경하여 증분/차등 백업을 적용할 예정이다.

백업 파일 유지 기간은 우선 14일 (2주)로 잡는다.

신입은 어느 기간이 적정기간인지 모르겠으니 내맘대로 잡아보고 추후 여쭤보고 변경할 예정이다 ㅎ

아무튼 그래서 작업은 크게 두가지로 나뉜다.

  1. 백업 스크립트 작성
  2. 매일 자정 백업 스크립트를 실행할 백업 스케줄링 설정

1. 백업 스크립트 작성

백업 파일 저장할 폴더 생성하자 !

보통 /root 나 /home 에 생성한다고 하는데 나는 프로젝트 관리하는 디렉토리에 생성해두었다.

mkdir db_backup
cd db_backup
mkdir backup_file
mkdir script

생성된 디렉토리의 구조는 아래와 같다

db_backup
	├ backup_file
	└ script

db_backup - 데이터베이스 백업과 관련된 파일 및 폴더

backup_file - 데이터를 백업하여 압축한 파일을 저장하는 디렉토리

script - 데이터를 백업과 관련된 스크립트 실행 파일을 저장하는 디렉토리

db_backup
	├ backup_file
	|   ├ 2024-05-15.tar.gz
	|   └ 2024-05-16.tar.gz
	└ script
	  	└ backup_start.sh

추후 스크립트(backup_start.sh) 를 작성하고 실행시키면 위와 같은 모습이 되도록 할 것이다.


백업을 실행할 스크립트 파일(backup_start.sh) 생성

vi 에디터를 활용해서 아래와 같은 내용을 입력한다

vi backup_start.sh
#!/bin/bash

# 날짜로 파일 이름을 생성하기 위한 변수
BACKUP_DATE="$(date +%Y-%m-%d_%H:%M:%S)"
# 백업 위치를 지정한 변수
BACKUP_DIR="/프로젝트 경로/db_backup/backup_file"
# 압축 파일 이름 변수
BACKUP_FILE_NAME="$(date +%Y-%m-%d).tar.gz"

# 백업
mysqldump -u계정 -p비밀번호 --databases 백업할DB1 > ${BACKUP_DIR}/백업할DB1_${BACKUP_DATE}.sql
mysqldump -u계정 -p비밀번호 --databases 백업할DB2 > ${BACKUP_DIR}/백업할DB2_${BACKUP_DATE}.sql

# 그날 백업한 파일들 압축
cd ${BACKUP_DIR}
tar -zcvf ${BACKUP_DIR}/$BACKUP_FILE_NAME *.sql
# 압축 후 그날 백업한 .sql 파일 삭제
find ${BACKUP_DIR} -name '*.sql' -exec rm -f {} \;
# # 압축 파일도 14일이 지나면 삭제
find ${BACKUP_DIR} -name '*.gz' -mtime +14 -exec rm -f {} \;

실행 권한을 할당한다.

chmod 777 backup_start.sh

스크립트 파일을 실행한다.

./backup_start.sh

제대로 tar.gz 파일이 생성되면 성공이다.



2. 매일 자정 백업 스크립트를 실행할 백업 스케줄링 설정 (crontab)

백업 스케줄링은 crontab 을 이용한다.

우선 관리자 권한으로 접근해보자

sudo su

/etc/crontab 파일을 수정하자.

vi /etc/crontab

명령어를 치면 위 사진과 같이 기본적으로 작성되어 있는 스케줄을 확인할 수 있다.

  • Crontab 규칙
    * * * * 유저 이름 명령어
┬ ┬ ┬ ┬ ┬
│ │ │ │ └─ 요일 (0 - 6) (0:일요일, 1:월요일, 2:화요일, …, 6:토요일)
│ │ │ └─ 월 (1 - 12)
│ │ └─일 (1 - 31)
│ └─ 시 (0 - 23)
└─ 분 (0 - 59)

스케줄러를 등록하기 위해서 아래 코드를 작성하였다.

우선 스케줄이 제대로 먹히는지를 확인하기 위해서 아래 처럼 작성하엿다.

# 매분, 매시간, 매일, 매월, 즉 1분만다 실행
* *     * * *   root    /프로젝트 경로/db_backup/script/backup_start.sh

제대로 tar.gz 백업 파일이 생성되는 걸 확인 후 아래와 같이 변경하였다.

# 0분, 0시, 매일, 매월, 매일, 즉 매일 자정에 파일을 실행
0 0     * * *   root    /프로젝트 경로/db_backup/script/backup_start.sh

추가적으로 아래 코드와 같이 /var/log 디렉토리에 cron.log 파일을 생성 후 아래와 같은 코드로 변경해주면 cron 이 실행되면서 생성되는 log 들이 남게 된다.

추후 백업이 제대로 실행되었는지 확인하는 용도로 사용할 수 있다.

0 0     * * *   root    /프로젝트 경로/db_backup/script/backup_start.sh >> /var/log/cron.log









+) 마지막으로 gpt 를 이용하여 백업이 제대로 됐는지 확인하는 로그까지 추가해주면 아래와 같은 완성본 스크립트 파일이 생성된다. → 해도 되고 안해도 되는거, 근데 어디서 어떤 db에 백업이 문제가 생겼는지 나중에 확인 할 수 있도록 log 만들어 두었다.

#!/bin/bash

# 로그 파일 경로
LOG_FILE="$(dirname "$0")/backup.log"

# 로그 함수 정의
log_message() {
    local timestamp=$(date +"%Y-%m-%d %H:%M:%S")
    echo "[$timestamp] $1" >> "$LOG_FILE"
}

# 로그 파일 생성 또는 초기화
echo "" >> "$LOG_FILE"

log_message "===== Backup script started at $(date +"%Y-%m-%d %H:%M:%S"). ====="

# 날짜로 파일 이름을 생성하기 위한 변수
BACKUP_DATE="$(date +%Y-%m-%d_%H:%M:%S)"
# 백업 위치를 지정한 변수
BACKUP_DIR="/프로젝트경로/db_backup/backup_file"
# 압축 파일 이름 변수
BACKUP_FILE_NAME="$(date +%Y-%m-%d).tar.gz"

# 백업
log_message "1. Starting database backups..."
log_message "   Backing up 백업할DB1 database..."
if mysqldump -u계정 -p비밀번호 --databases 백업할DB1 > 백업할DB1_${BACKUP_DATE}.sql; then
    log_message "   Backup of 백업할DB1 database successful."
else
    log_message "   [Fail] Failed to backup 백업할DB1 database."
fi

log_message "   Backing up 백업할DB2 database..."
if mysqldump -u계정 -p비밀번호 --databases 백업할DB2 > 백업할DB2_${BACKUP_DATE}.sql; then
    log_message "   Backup of 백업할DB2 database successful."
else
    log_message "   [Fail] Failed to backup 백업할DB2 database."
fi

# 그날 백업한 파일들 압축
log_message "2. Compressing backup .sql files..."
if tar -zcvf ${BACKUP_DIR}/$BACKUP_FILE_NAME *.sql; then
    log_message "   Compression of backup .sql files successful."
else
    log_message "   [Fail] Failed to compress backup .sql files."
fi

# 압축 후 그날 백업한 .sql 파일 삭제
log_message "3. Removing backup .sql files..."
deleted_files=$(find -name '*.sql' -print -delete)
log_message "   Deleted files: $deleted_files"

# 압축 파일도 14일이 지나면 삭제
log_message "4. Removing old compressed backup files..."
deleted_files=$(find ${BACKUP_DIR} -name '*.gz' -mtime +14 -print -delete)
log_message "   Deleted files: $deleted_files"

log_message "Backup process completed !"

다음에는 논리적 백업으로 했던 방식을 물리적 백업으로 변환 후 전체/증분 or 차등 실습을 해보겠다.






📎 참고 링크

https://cloud.google.com/mysql/backup?hl=ko

https://magnuxx.tistory.com/entry/백업과-스냅샷의-정의와-차이점

https://ssongblog.tistory.com/144

https://niceman.tistory.com/60

https://blog.naver.com/ted1lee/220755228574

https://any-ting.tistory.com/87

profile
안나
내가 해냄
이전 포스트

[Database] 백업의 방식 - Full(전체) / Incremental(증분) / Differential(차등)

다음 포스트

[Database] 물리적 백업하기 ! , 풀백업 + 증분백업 (mariabackup)

0개의 댓글