데이터베이스 및 이미지 자동 백업 스케줄링

1. 사전 준비

1-1. Google Drive와 rclone 연동

먼저 서버에 rclone을 설치하고 Google Drive와 연동합니다.

sudo apt update
sudo apt install rclone

설정

rclone config

설정이 완료되면 다음과 같이 Google Drive에 접근할 수 있어야 합니다.

rclone lsd gdrive:

또한 백업 폴더를 생성합니다.

rclone mkdir gdrive:blog-backup/db
rclone mkdir gdrive:blog-backup/images

---

1-2. 백업 전용 MySQL 계정 생성

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY '비밀번호';

GRANT
    SELECT,
    SHOW VIEW,
    TRIGGER
ON YEOL_POST.*
TO 'backup_user'@'localhost';

FLUSH PRIVILEGES;

---

1-3. mysql_config_editor 설정

비밀번호를 스크립트에 저장하지 않기 위해 mysql_config_editor를 사용합니다.

mysql_config_editor set \
    --login-path=blog_backup \
    --host=localhost \
    --user=backup_user \
    --password

설정 확인

mysql_config_editor print --all

접속 테스트

mysql --login-path=blog_backup -e "SELECT 1;"

---

2. 백업 스크립트 생성

/usr/local/bin/mysql_backup.sh

#!/bin/bash

set -euo pipefail

DATABASE="YEOL_POST"
LOGIN_PATH="blog_backup"

MYSQL_BACKUP_DIR="/var/backups/mysql"
IMAGE_DIR="/home/deploy/app/uploads/images"
IMAGE_BACKUP_DIR="/var/backups/images"

GDRIVE_DB_REMOTE="gdrive:blog-backup/db"
GDRIVE_IMAGE_REMOTE="gdrive:blog-backup/images"

RETENTION_DAYS=30

mkdir -p "$MYSQL_BACKUP_DIR"
mkdir -p "$IMAGE_BACKUP_DIR"

DATE=$(date +%F_%H-%M-%S)

DB_FILE_NAME="${DATABASE}_${DATE}.sql.gz"
DB_FILE_PATH="$MYSQL_BACKUP_DIR/$DB_FILE_NAME"

IMAGE_FILE_NAME="images_${DATE}.tar.gz"
IMAGE_FILE_PATH="$IMAGE_BACKUP_DIR/$IMAGE_FILE_NAME"

echo "=========================================="
echo "Backup Started : $(date)"
echo "=========================================="

# =========================
# DB 백업
# =========================
mysqldump \
    --login-path="$LOGIN_PATH" \
    --single-transaction \
    --no-tablespaces \
    "$DATABASE" \
| gzip > "$DB_FILE_PATH"

# =========================
# 이미지 백업
# =========================
if [ ! -d "$IMAGE_DIR" ]; then
    echo "ERROR: Image directory not found."
    exit 1
fi

tar -czf \
    "$IMAGE_FILE_PATH" \
    -C "$(dirname "$IMAGE_DIR")" \
    "$(basename "$IMAGE_DIR")"

# =========================
# Google Drive 업로드
# =========================
rclone copyto \
    "$DB_FILE_PATH" \
    "$GDRIVE_DB_REMOTE/$DB_FILE_NAME"

rclone copyto \
    "$IMAGE_FILE_PATH" \
    "$GDRIVE_IMAGE_REMOTE/$IMAGE_FILE_NAME"

# =========================
# 오래된 백업 삭제
# =========================
find "$MYSQL_BACKUP_DIR" -type f -mtime +"$RETENTION_DAYS" -delete
find "$IMAGE_BACKUP_DIR" -type f -mtime +"$RETENTION_DAYS" -delete

rclone delete "$GDRIVE_DB_REMOTE" --min-age "${RETENTION_DAYS}d"
rclone delete "$GDRIVE_IMAGE_REMOTE" --min-age "${RETENTION_DAYS}d"

echo "Backup Finished : $(date)"

---

3. 실행 권한 부여

chmod +x /usr/local/bin/mysql_backup.sh

---

4. 수동 테스트

/usr/local/bin/mysql_backup.sh

백업 확인

ls -lh /var/backups/mysql
ls -lh /var/backups/images

Google Drive 확인

rclone ls gdrive:blog-backup/db
rclone ls gdrive:blog-backup/images

---

5. Cron 등록 (자동 실행)

crontab -e

매일 새벽 2시 실행

0 2 * * * /usr/local/bin/mysql_backup.sh

---

6. 백업 복원

DB 복원

gunzip < YEOL_POST_2026-07-03_20-56-51.sql.gz \
| mysql -u root -p YEOL_POST

---

이미지 복원

tar -xzf images_2026-07-03_20-56-51.tar.gz \
-C /home/deploy/app/uploads

---

7. 백업 검증

DB 압축 확인

zcat /var/backups/mysql/YEOL_POST_2026-07-03_20-56-51.sql.gz | head

---

이미지 압축 확인

tar -tzf /var/backups/images/images_2026-07-03_20-56-51.tar.gz

---

Google Drive 확인

rclone ls gdrive:blog-backup/db
rclone ls gdrive:blog-backup/images