MySQL 백업과 복원 방법 (mysqldump 사용법과 자동 백업)

데이터베이스는 서버에서 가장 잃어버리면 안 되는 자산이다. 파일은 다시 올리면 되지만, 쌓인 회원 정보나 주문 내역은 백업이 없으면 되살릴 방법이 없다. 이 글에서는 MySQL에 기본으로 들어 있는 mysqldump 백업 방법과 복원, 그리고 매일 자동으로 백업해 다른 서버에 보관하는 과정까지 정리한다. MySQL 초기 설정이 아직이라면 MySQL 설치 후 꼭 필요한 설정을 먼저 보자.

mysqldump란

mysqldump는 데이터베이스의 구조(CREATE TABLE)와 데이터(INSERT)를 SQL 문장으로 뽑아 텍스트 파일로 저장하는 도구다. 결과가 평범한 SQL 파일이라 다른 서버나 다른 버전의 MySQL, MariaDB로 옮기기 쉽다. 수 GB 정도까지의 데이터베이스라면 가장 간단하고 확실한 백업 방법이다.

mysqldump 백업 기본 명령어

# 데이터베이스 하나 백업
mysqldump -u root -p mydb > mydb.sql

# 여러 데이터베이스 (CREATE DATABASE 문까지 포함)
mysqldump -u root -p --databases mydb shopdb > dbs.sql

# 전체 데이터베이스
mysqldump -u root -p --all-databases > all.sql

# 특정 테이블만
mysqldump -u root -p mydb users orders > mydb_tables.sql

# 데이터베이스 하나 백업
mysqldump -u root -p mydb > mydb.sql

# 여러 데이터베이스 (CREATE DATABASE 문까지 포함)
mysqldump -u root -p --databases mydb shopdb > dbs.sql

# 전체 데이터베이스
mysqldump -u root -p --all-databases > all.sql

# 특정 테이블만
mysqldump -u root -p mydb users orders > mydb_tables.sql

꼭 같이 쓰는 옵션

mysqldump -u root -p --single-transaction --routines --triggers --events \
  --default-character-set=utf8mb4 mydb > mydb_$(date +%Y%m%d).sql

mysqldump -u root -p --single-transaction --routines --triggers --events \
  --default-character-set=utf8mb4 mydb > mydb_$(date +%Y%m%d).sql

gzip으로 바로 압축하기

SQL 파일은 텍스트라 압축 효율이 매우 좋다. 파이프로 gzip에 넘기면 디스크를 거의 쓰지 않고 바로 압축 파일을 만든다. 압축 개념은 tar 압축과 해제 방법에서 다뤘다.

mysqldump -u root -p --single-transaction mydb | gzip > mydb_$(date +%Y%m%d).sql.gz

mysqldump -u root -p --single-transaction mydb | gzip > mydb_$(date +%Y%m%d).sql.gz

mysqldump 백업 파일로 복원하기

# 대상 데이터베이스가 없다면 먼저 만든다
mysql -u root -p -e "CREATE DATABASE mydb DEFAULT CHARACTER SET utf8mb4;"

# SQL 파일 복원
mysql -u root -p mydb < mydb_20261006.sql

# gzip 압축 파일 복원
gunzip < mydb_20261006.sql.gz | mysql -u root -p mydb

# --databases / --all-databases 로 만든 파일은 DB 이름 없이
mysql -u root -p < all.sql

# 대상 데이터베이스가 없다면 먼저 만든다
mysql -u root -p -e "CREATE DATABASE mydb DEFAULT CHARACTER SET utf8mb4;"

# SQL 파일 복원
mysql -u root -p mydb < mydb_20261006.sql

# gzip 압축 파일 복원
gunzip < mydb_20261006.sql.gz | mysql -u root -p mydb

# --databases / --all-databases 로 만든 파일은 DB 이름 없이
mysql -u root -p < all.sql

복원은 기존 테이블을 지우고 다시 만드는 방식(DROP TABLE IF EXISTS)이라 운영 DB에 바로 부으면 현재 데이터가 덮어써진다. 먼저 새 이름의 데이터베이스에 복원해서 내용을 확인한 뒤 바꾸는 것이 안전하다.

비밀번호를 스크립트에 넣지 않는 방법

-p비밀번호처럼 명령줄에 비밀번호를 쓰면 ps 명령이나 셸 기록에 그대로 남는다. 자동화할 때는 백업 전용 계정을 만들고, 접속 정보를 권한 600인 설정 파일에 따로 둔다.

-- 백업 전용 계정 (필요한 최소 권한만)
CREATE USER 'backup'@'localhost' IDENTIFIED BY '강력한_비밀번호';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, PROCESS ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;

-- 백업 전용 계정 (필요한 최소 권한만)
CREATE USER 'backup'@'localhost' IDENTIFIED BY '강력한_비밀번호';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, PROCESS ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;

# ~/.my.cnf (백업을 실행할 리눅스 사용자의 홈)
[client]
user=backup
password=강력한_비밀번호

# 다른 사용자가 읽지 못하게
chmod 600 ~/.my.cnf

# 이제 -u, -p 없이 실행된다
mysqldump --single-transaction mydb > mydb.sql

# ~/.my.cnf (백업을 실행할 리눅스 사용자의 홈)
[client]
user=backup
password=강력한_비밀번호

# 다른 사용자가 읽지 못하게
chmod 600 ~/.my.cnf

# 이제 -u, -p 없이 실행된다
mysqldump --single-transaction mydb > mydb.sql

MySQL 8.0.21 이후에는 테이블스페이스 정보를 읽기 위해 PROCESS 권한이 필요하다. 권한을 주고 싶지 않다면 –no-tablespaces 옵션을 붙이면 된다.

mysqldump 백업 자동화 스크립트

이제 쉘 스크립트 기초에서 만든 백업 스크립트 형태로 DB 백업을 자동화해 보자. 날짜별로 압축해 저장하고, 14일이 지난 파일은 지우고, 다른 서버로도 복사한다.

#!/bin/bash
set -euo pipefail

DB="mydb"
DEST="/backup/mysql"
KEEP_DAYS=14
TODAY=$(date +%Y%m%d_%H%M)
LOG="/backup/mysql/backup.log"

mkdir -p "$DEST"

mysqldump --single-transaction --routines --triggers --events \
  --default-character-set=utf8mb4 "$DB" | gzip > "$DEST/${DB}_${TODAY}.sql.gz"

echo "[$(date '+%F %T')] 백업 완료: ${DB}_${TODAY}.sql.gz" >> "$LOG"

find "$DEST" -name "${DB}_*.sql.gz" -mtime +"$KEEP_DAYS" -delete

# 다른 서버에 한 벌 더 보관
rsync -az "$DEST/" backup-server:/backup/mysql/

#!/bin/bash
set -euo pipefail

DB="mydb"
DEST="/backup/mysql"
KEEP_DAYS=14
TODAY=$(date +%Y%m%d_%H%M)
LOG="/backup/mysql/backup.log"

mkdir -p "$DEST"

mysqldump --single-transaction --routines --triggers --events \
  --default-character-set=utf8mb4 "$DB" | gzip > "$DEST/${DB}_${TODAY}.sql.gz"

echo "[$(date '+%F %T')] 백업 완료: ${DB}_${TODAY}.sql.gz" >> "$LOG"

find "$DEST" -name "${DB}_*.sql.gz" -mtime +"$KEEP_DAYS" -delete

# 다른 서버에 한 벌 더 보관
rsync -az "$DEST/" backup-server:/backup/mysql/

마지막 rsync 줄은 비밀번호 없이 접속할 수 있어야 동작한다. SSH 키 인증 설정으로 키를 등록해 두고, rsync 옵션은 rsync 백업과 파일 동기화를 참고하자. 파이프 중간의 mysqldump가 실패해도 set -o pipefail 덕분에 스크립트가 멈춰서 빈 백업 파일을 정상으로 착각하는 일을 막는다.

# crontab -e : 매일 새벽 2시 실행
0 2 * * * /home/admin/scripts/mysql_backup.sh >> /backup/mysql/cron.log 2>&1

# crontab -e : 매일 새벽 2시 실행
0 2 * * * /home/admin/scripts/mysql_backup.sh >> /backup/mysql/cron.log 2>&1

crontab 형식은 리눅스 crontab 사용법에 정리해 두었다.

메모리가 작은 서버에서는 덤프나 복원 중에 MySQL이 메모리 부족으로 꺼지기도 한다. 이럴 때는 리눅스 스왑 메모리 설정으로 여유를 확보해 두자.

백업은 복원해 봐야 백업이다

• 한 달에 한 번은 테스트용 DB에 실제로 복원해 본다.
• 복원 후 주요 테이블의 행 수(SELECT COUNT(*))를 원본과 비교한다.
• 백업 파일 크기가 갑자기 작아지면 실패를 의심한다. 로그에 크기를 함께 남기면 좋다.
• 백업은 같은 서버, 다른 서버, 가능하면 다른 장소의 세 곳에 둔다.

워드프레스라면 WP-CLI로도 가능하다

워드프레스 사이트는 WP-CLI의 wp db export 명령으로 wp-config.php의 접속 정보를 그대로 써서 덤프를 만들 수 있다. 내부적으로 mysqldump를 사용한다. 자세한 옵션은 WP-CLI db export 문서에 있다. 업데이트 전에 한 번 받아 두면 워드프레스 치명적인 오류 같은 상황에서도 마음이 편하다.

cd /var/www/html
wp db export /backup/wp_$(date +%Y%m%d).sql

cd /var/www/html
wp db export /backup/wp_$(date +%Y%m%d).sql

큰 DB를 수동으로 덤프하거나 복원할 때는 tmux 세션 안에서 실행하면 SSH가 끊겨도 작업이 중단되지 않는다. 백업 파일이 쌓여 디스크가 가득 찼다면 리눅스 디스크 용량 부족 해결을 참고하자.

mysqldump 백업 정리

mysqldump는 –single-transaction으로 잠금 없이 뜨고, gzip으로 줄이고, 설정 파일로 비밀번호를 숨기고, crontab과 rsync로 자동화하면 충분히 믿을 만한 백업 체계가 된다. 데이터가 수십 GB 이상으로 커지면 덤프와 복원 시간이 길어지므로 물리 백업 도구(Percona XtraBackup 등)를 검토하자. 옵션 전체는 MySQL 공식 mysqldump 문서에서 볼 수 있다. MySQL이 아예 동작하지 않는다면 MySQL이 안될 때 재설치 전 확인할 것도 참고하자.