[MySQL] (백업/복구2️⃣) mysqldump 백업과 복원 방법 완벽 가이드

MySQL의 기본 백업 도구인 mysqldump는 가장 많이 사용되는 논리 백업 툴이다.
SQL 구문 형식으로 데이터를 덤프하여 이식성과 가독성이 좋고, 간편하게 복원할 수 있다.

✅ mysqldump란?

mysqldump는 MySQL에서 제공하는 CLI(Command Line Interface) 백업 유틸리티다.
데이터베이스나 테이블을 SQL 파일로 백업해 저장하며, 해당 파일을 다시 실행하여 데이터를 복원할 수 있다.

mysqldump -u [사용자] -p [옵션] [DB명] > backup.sql

파일 형식은 SQL이므로, 사람이 읽기 쉽고, 다른 MySQL 서버에도 쉽게 이식할 수 있다는 장점이 있다.

반응형

🧪 가장 기본적인 백업 예제

전체 데이터베이스 백업

mysqldump -u root -p --all-databases > full_backup.sql

특정 DB만 백업

mysqldump -u root -p mydb > mydb_backup.sql

특정 테이블만 백업

mysqldump -u root -p mydb table1 table2 > mydb_tables.sql

⚙️ 자주 쓰는 주요 옵션 정리

옵션	설명
--all-databases	전체 데이터베이스 백업
--databases	복수 DB 백업 시 사용 (--databases db1 db2)
--no-data	스키마만 백업 (데이터 제외)
--add-drop-table	DROP TABLE IF EXISTS 포함
--single-transaction	InnoDB 백업 시 락 없이 안정적인 덤프
--routines	프로시저, 함수 포함
--events	이벤트 스케줄러 포함
--set-gtid-purged=OFF	GTID 설정 제거 (복원 시 충돌 방지)

예시

mysqldump -u root -p mydb --routines --events --single-transaction > mydb_all.sql

반응형

🔁 백업 파일 복원 방법

기본 복원 방법

mysql -u root -p mydb < mydb_backup.sql

주의: mydb는 백업 전에 미리 생성되어 있어야 한다. 아래와 같이 복원 전 DB를 생성하자.

CREATE DATABASE mydb;

복수 DB 또는 전체 DB를 복원할 경우

mysql -u root -p < full_backup.sql

🧠 실무에서 자주 쓰는 백업 스크립트 예제

#!/bin/bash

DATE=$(date +%F)
BACKUP_DIR="/home/backup/mysql"
DB_USER="root"
DB_PASS="password"
DB_NAME="mydb"

mkdir -p ${BACKUP_DIR}
mysqldump -u ${DB_USER} -p${DB_PASS} --routines --events --single-transaction ${DB_NAME} > ${BACKUP_DIR}/${DB_NAME}_${DATE}.sql

이 스크립트를 crontab에 등록하면 매일 자동 백업도 가능하다.

0 3 * * * /home/backup/backup.sh

⚠️ 실무에서 자주 발생하는 문제와 주의사항

1. 대용량 백업 시 커넥션 끊김 현상
   → --single-transaction을 꼭 붙이고, 네트워크 문제 방지 필요
2. GTID 활성화 환경에서 복원 오류
   → --set-gtid-purged=OFF 옵션을 추가
3. 인코딩 문제로 한글 깨짐 발생
   → 백업 시 --default-character-set=utf8mb4 사용
4. Trigger, Procedure 누락
   → --routines, --triggers 옵션 포함 여부 확인
5. Binary Log 연계 안됨
   → --master-data=2 옵션으로 binary log 위치 정보 삽입 가능 (복제 구성용)

반응형

📋 실제 운영 환경 예시

고객정보 DB는 매일 새벽 4시 mysqldump로 백업되고, 백업파일은 NAS와 AWS S3에 업로드된다.
백업 스크립트는 --single-transaction, --routines, --events 옵션을 포함하며, 백업 파일 이름에는 날짜가 붙는다.
복원 테스트는 주기적으로 별도 Dev 서버에서 수행된다.

🧩 mysqldump vs 다른 백업 도구 비교

도구	장점	단점
mysqldump	사용 간편, SQL 파일로 이식성 높음	속도 느림, 대용량 부적합
xtrabackup	빠른 물리 백업, 온라인 백업 가능	설정 복잡, 도구 설치 필요
mysqlpump	병렬 백업 지원	아직 널리 쓰이지 않음

mysqldump는 속도보다는 간편함과 이식성 중심의 백업 도구로 이해하면 된다.

✅ 정리

• mysqldump는 SQL 기반의 논리 백업 도구로 실무에서 가장 널리 사용된다.
• 단순한 명령어로 전체 DB, 특정 DB, 특정 테이블을 백업할 수 있다.
• 옵션 조합에 따라 프로시저, 이벤트, 트리거, 스키마만 등 다양한 백업이 가능하다.
• 복원은 mysql < backup.sql 형태로 수행하며, 데이터베이스가 미리 생성되어 있어야 한다.
• 스크립트 자동화와 주기적 복원 테스트를 병행해야 진짜 백업 전략이 완성된다.

🔗 공식 문서 참고
MySQL 8.0 Reference Manual - mysqldump — A Database Backup Program