[Shell Script] mysqldump를 사용한 MySQL 백업 및 자동 정리 셸 스크립트 작성하기

JaehyoJJAng2024년 02월 11일

개요

집에서만 사용하는 MySQL 데이터베이스가 있는데, 요즘 쌓이는 데이터들이 많아지다보니 외부 저장소나 NAS 등으로 백업을 해야겠다는 생각이 불현듯 들었다.

DB 운영 초기에는 삭제되도 상관 없다는 마인드였는데, 파싱된 데이터가 제법 쌓이다보니 삭제되면 좀 아까운? 그런 느낌이 든 것 같다.

그래서 이번 포스팅에서는 MySQL 데이터를 mysqldump를 사용해 백업하고, 30일 이상 이상 지난 백업 파일을 자동으로 삭제하는 셸 스크립트를 작성해보려고 한다.

백업 데이터가 저장될 경로

DB 서버의 /mnt/synology 경로가 시놀로지 나스의 /volume1/mysql 공유 폴더와 마운트 되어 있으므로,

백업 경로는 /mnt/synology로 설정된다.

그리고 현재 MySQL는 서버에 바이너리로 설치된 것이 아니라 도커 컨테이너 형태로 띄워져 있으므로, 도커의 exec 명령어를 활용할 것이다.

mysqldump

mysqldump는 MySQL 백업 방식 중 하나이며, 논리적 백업을 수행한다.

논리적 백업의 경우 원본 데이터베이스의 개체 정의 및 테이블의 데이터를 재생성하는 SQL문을 생성하는 방식이라고 보면 된다.

여기서 SQL문의 파일 뿐만 아니라 CSV, XML 형태의 파일도 생성이 가능하다.

사용법

mysqldump의 기본 사용법은 다음과 같다.

# 특정 데이터베이스 백업
mysqldump -u {사용자 계정} -p --databases {원본 데이터베이스명1} {원본 데이터베이스명2} .. > {생성할 백업 데이터베이스명}.sql

# 모든 데이터베이스 백업
mysqldump -u {사용자 계정} -p --all-databases > {생성할 백업 데이터베이스명}.sql

# 원격지 백업: 원격지 {host_ip}의 데이터베이스를 백업
mysqldump -u {사용자 계정} -p -h {host_ip} --all-databases > {생성할 백업 데이터베이스명}.sql

복구 방법

mysqldump로 생성된 sql 파일은 기본적으로 mysql Command-Line Client를 사용하여 복구한다.

정확히는 복구가 아닌 sql 파일 실행이라고 보면 된다.

mysql Command-Line은 mysql에 접속하기 위해 콘솔창에 입력하는 다음과 같은 명령어이다.

mysql -u root -p

이 문법이 바로 mysql command line 이다.

1. 전체 데이터베이스 복구 방법

mysql -u {사용자 계정} -p < {백업된 데이터베이스파일명}.sql

전체 데이터베이스 백업 파일을 전체 복구 할 경우 mysql DB와 같은 메타정보가 덮어씌워질 수 있다.
백업 파일 sql문에 있는 전체를 실행하여 복구한다.
- Create Database 문이 포함되어 있기 때문에 따로 지정한 데이터베이스 명이 없을 경우 생성한다.

2. 하나의 데이터베이스 복구

mysql -u {사용자 계정} -p --one-database {복구할 데이터베이스명} < {백업된 데이터베이스파일명}.sql

백업 파일의 sql문 중 하나의 데이터베이스만 복구할 경우 사용한다.
{복구할 데이터베이스명}에 해당하는 데이터베이스는 미리 생성해줘야 한다.
- 다른 이름의 데이터베이스로도 복구 가능하다.

3. 원격지 데이터베이스에 복구

mysql -u {사용자 계정} -p -h {host_ip} < {백업된 데이터베이스파일명}.sql

{host_ip}로 원격지 정보를 입력 후 해당 데이터베이스에 복구한다.

스크립트 작성

다음 스크립트는 mysqldump를 사용하여 MySQL 데이터를 백업하고, 1주일 이상 된 백업 파일을 삭제하며, 작업 로그를 출력한다.

#!/usr/bin/bash

# 백업 설정
BACKUP_DIR="/mnt/synology" # 백업 파일을 저장할 디렉토리
DB_USER="jaehyo"           # MySQL 사용자명
DB_PASSWORD="jaehyo"       # MySQL 비밀번호
DB_NAME="homedb"           # 백업할 데이터베이스 이름
LOG_FILE="$BACKUP_DIR/backup_log.txt" # 로그 파일 경로

# MySQL 컨테이너 정보
CONTAINER_NAME="mysql"

# MySQL 컨테이너 실행 상테 확인
if [[ -z $(docker ps --filter "name=$CONTAINER_NAME" | awk 'NR>1') ]]; then
    echo "($CONTAINER_NAME)가 실행 중이지 않습니다!"
    exit 1
fi

# 스크립트 설명 로그 출력
echo "======================================" | tee -a $LOG_FILE
echo "MySQL 데이터 백업 스크립트" | tee -a $LOG_FILE
echo "이 스크립트는 MySQL 데이터를 백업하고, 1주일 이상 된 백업 파일을 삭제합니다." | tee -a $LOG_FILE
echo "백업 디렉토리: $BACKUP_DIR" | tee -a $LOG_FILE
echo "======================================" | tee -a $LOG_FILE
echo "" | tee -a $LOG_FILE

# 백업 파일 이름 생성 (날짜 기반)
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_$(date +'%Y-%m-%d_%H_%M_%S').sql"

# 백업 시작 로그
echo "$(date +'%Y-%m-%d %H:%M:%S') - 백업 시작" | tee -a "$LOG_FILE"

# mysqldump를 사용하여 백업
docker exec -it "$CONTAINER_NAME" mysqldump -u$DB_USER --password=$DB_PASSWORD $DB_NAME > "$BACKUP_FILE"

if [ $? -eq 0 ]; then
    echo "$(date +'%Y-%m-%d %H:%M:%S') - 백업 성공: $BACKUP_FILE" | tee -a $LOG_FILE
else
    echo "$(date +'%Y-%m-%d %H:%M:%S') - 백업 실패" | tee -a $LOG_FILE
    exit 1
fi

# 30일 이상 된 백업 파일 삭제
echo "$(date +'%Y-%m-%d %H:%M:%S') - 30일 이상 된 백업 파일 삭제 시작" | tee -a $LOG_FILE
temp=$(find $BACKUP_DIR -type f -name "*.sql" -mtime +30 -exec rm {} \;)

if [ -n "$temp" ]; then
	if [ $? -eq 0 ]; then
		find $BACKUP_DIR -type f -name "*.sql" -mtime +30 -exec rm {} \;	
		echo "$(date +'%Y-%m-%d %H:%M:%S') - 30일 이상 된 백업 파일 삭제 성공" | tee -a $LOG_FILE
	else
		echo "$(date +'%Y-%m-%d %H:%M:%S') - 30일 이상 된 백업 파일 삭제 실패" | tee -a $LOG_FILE
	fi
else
	echo "$(date +'%Y-%m-%d %H:%M:%S') - 30일 이상 된 백업 파일이 존재하지 않음" | tee -a $LOG_FILE
fi

# 스크립트 종료 로그
echo "$(date +'%Y-%m-%d %H:%M:%S') - 백업 작업 완료" | tee -a $LOG_FILE

백업 설정
- BACKUP_DIR, DB_USER, DB_PASSWORD, DB_NAME등의 설정 값을 사용자 환경에 맞게 수정
백업 파일 생성
- 날짜와 시간을 포함하여 백업 파일 이름을 생성
로그 출력
- echo 명령을 사용하여 로그를 파일과 콘솔에 동시에 출력
- tee 사용
mysqldump 실행
- mysqldump를 사용하여 MySQL 데이터베이스를 백업
오래된 백업 파일 삭제
- find 명령어를 사용하여 7일 이상 지난 백업 파일을 삭제
로그 파일
- 작업 결과는 backup_log.txt에 기록

실행

스크립트에 실행 권한 부여

chmod u+x backup.sh

스크립트 실행

$ sudo bash backup.sh

======================================
MySQL 데이터 백업 스크립트
이 스크립트는 MySQL 데이터를 백업하고, 1주일 이상 된 백업 파일을 삭제합니다.
백업 디렉토리: /mnt/synology
======================================

2024-10-04 17:11:01 - 백업 시작
2024-10-04 17:11:01 - 백업 성공: /mnt/synology/homedb_2024-10-04_17_11_01.sql
2024-10-04 17:11:01 - 30일 이상 된 백업 파일 삭제 시작
2024-10-04 17:11:01 - 30일 이상 된 백업 파일이 존재하지 않음
2024-10-04 17:11:01 - 백업 작업 완료

DB 서버의 /mnt/synology를 확인해보면 다음과 같이 백업 파일이 생성된 것을 볼 수 있다.

shell-script