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

728x90

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	스토어드 프로시저와 함수를 백업 파일에 포함

예시위 명령어를 입력하면 아래 사진과 같이 test_backup.sql 파일이 생성된다.
mysqldump -uroot -p1234 test > test_backup.sql

결과

3. 복원

기본 문법
mysql [options] 데이터베이스명 < 데이터베이스백업파일.sql
예시
기존에 존재하던 test 테이블을 삭제하고 백업 시켜놓은 .sql 문으로 복원을 시켜보자.
```
  mysql -uroot -p < test_backup.sql 
```
위 명령문을 치니 아래와 같은 오류가 뜬다.
--- 테이블 삭제 DROP DATABASE IF EXISTS test;

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

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

결과

나이스
굳 .

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

0. 들어가기 전에

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

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

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

백업 파일 유지 기간은 우선 14일 (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 차등 실습을 해보겠다.

+ 추가적으로 타임존을 바꿨음에도 cron에 등록한 시간에 .sh 파일이 자동 실행이 안된다면 cron 데몬을 재시작해보자

sudo systemctl restart cron

📎 참고 링크

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

'Programming > DataBase' 카테고리의 다른 글

[Database-MySQL] Select 쿼리로 테이블 정의서 만들기 (sql to 정의서) (1)	2024.09.20
[Database] 물리적 백업하기 ! , 풀백업 + 증분백업 (mariabackup) (0)	2024.06.14
[Database] 백업의 방식 - Full(전체) / Incremental(증분) / Differential(차등) (0)	2024.05.10
[Database] 물리적 백업 vs 논리적 백업 (0)	2024.05.10

안나의 전두엽 어딘가 🧠

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

0. 들어가기 전

1. 배경

2. 환경

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

1. 풀 백업 실습

1. Test 데이터베이스 생성

2. 백업

3. 복원

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

0. 들어가기 전에

1. 백업 스크립트 작성

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

'Programming > DataBase' 카테고리의 다른 글

티스토리툴바

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

0. 들어가기 전

1. 배경

2. 환경

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

1. 풀 백업 실습

1. Test 데이터베이스 생성

2. 백업

3. 복원

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

0. 들어가기 전에

1. 백업 스크립트 작성

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

'Programming > DataBase' 카테고리의 다른 글

관련글

티스토리툴바