📢 개요
데이터를 관리하는 데 있어 중요한 업무 중 하나가 주기적인 데이터 백업과 복구이라 생각한다. 이러한 이유로 데이터 백업과 복구 작업을 진행해 볼 예정이다.
MySQL에서 데이터를 백업하는 방법은 다양하며, 크게는 다음과 같은 방식으로 구분할 수 있다.
-
DB IDE를 이용한 백업
DBeaver, MySQL Workbench 등과 같은 GUI 도구를 통해 손쉽게 백업 가능 -
CLI 및 스크립트를 이용한 백업
mysqldump등의 명령어를 사용한 터미널 기반 백업 방식 -
클라우드 기반 자동 백업
AWS RDS, GCP Cloud SQL 등에서 제공하는 자동 백업 기능 활용
이 중 나는 CLI(Command Line Interface)와 스크립트를 이용한 백업 방식을 직접 수행해 볼 예정이다. CLI 기반 백업은 자동화에 적합하고, 백업/복구 프로세스를 깊이 이해하는 데 도움이 되기 때문이다.
✅ CLI 기반 모든 인스턴스 백업/복구
sys 데이터베이스는 백업/복구 대상이 아니다. 때문에 별도의 관리가 필요하다.
[모든 데이터베이스 인스턴스 백업]
# --all-databases : 모든 데이터베이스(sys 제외) mysqldump -u <사용자명> -p --all-databases > <백업파일> mysqldump -u admin -p --all-databases > /backup/mysql/all/20250622/all.sql
[모든 데이터베이스 인스턴스 복구]
mysql -u <사용자명> -p < <백업파일> mysql -u admin -p < /backup/mysql/all/20250622/all.sql
✅ CLI 기반 데이터베이스 백업/복구
[특정 데이터베이스 백업]
# --databases : DB 생성 문장(DROP/CREATE DATABASE) 포함 mysqldump -u <사용자명> -p --databases <데이터베이스명> > <백업파일> mysqldump -u admin -p --databases sakila > /backup/mysql/database/20250622/sakila.sql
[특정 데이터베이스 복구]
복구하려는 DB가 이미 존재하면 내용이 덮어씌워 진다.mysql -u <사용자명> -p < <백업파일> mysql -u admin -p < /backup/mysql/database/20250622/sakila.sql
데이터베이스 백업의 경우 별도의 옵션을 추가하지 않으면 기본적으로 Tables와 Views만 백업된다.
Procedures(프로시저)와 Functions(함수) 등 다른 객체를 백업하고 싶으면 아래와 같은 옵션을 추가해야 한다.
--routines: 프로시저, 함수 백업--triggers: 트리거 백업--events: 이벤트 스케줄러 백업
# 테이블과 뷰 및 프로시저와 함수 까지 백업 mysqldump -u admin -p --databases --routines sakila > /backup/mysql/database/20250622/sakila.sql
✅ CLI 기반 데이터 베이스의 테이블 백업/복구
특정 테이블을 지정할 때 사용하는 옵션은 --tables이다. 하지마, 해당 옵션은 생략해도 동일하게 동작한다.
[특정 데이터 베이스의 테이블 백업]
mysqldump -u <사용자명> -p <데이터베이스> <테이블> > <백업파일> mysqldump -u admin -p sakila actor > /backup/mysql/table/20250629/sakila_actor.sql # --tables 옵션 사용 mysqldump -u admin -p sakila --tables actor > /backup/mysql/table/20250629/sakila_actor.sql
[특정 데이터 베이스의 테이블 복구]
복구하려는 테이블이 DB에 이미 존재하면 테이블 내용이 덮어씌워(테이블 삭제 후 생성, 테이블 정의(스키마)도 다시 생성) 진다.mysql -u <사용자명> -p <데이터베이스> < <백업파일> mysql -u admin -p sakila < /backup/mysql/table/20250629/sakila_actor.sql
✅ CLI 기반 데이터의 구조만 백업
데이터베이스를 백업할 때 데이터의 구조만을 백업해야 하는 경우가 있다.
이때는 --no-data옵션을 사용한다.
[데이터베이스 구조만 백업]
# 현재 옵션으론 테이블과 뷰 구조만 백업 mysqldump -u admin -p --no-data sakila > /backup/mysql/table/20250629/sakila_actor.sql[테이블 구조만 백업]
mysqldump -u admin -p --no-data sakila actor > /backup/mysql/table/20250629/sakila_actor.sql
⭐️ 다양한 백업 옵션 ⭐️
-
--databases
- 데이터 베이스의 생성(CREATE/DROP) 명령까지 포함하여 생성
- ex:
mysqldump -u [사용자명] -p --databases [데이터베이스명] > [백업파일]
-
--all-databases
- 서버에 있는 모든 DB 백업 (SYS데이터베이스 제외)
- ex:
mysqldump -u [사용자명] -p --all-databases > [백업파일]
-
--no-data
- 구조만 백업
- ex:
mysqldump -u [사용자명] -p --databases --no-data [데이터베이스명] > [백업파일] - ex:
mysqldump -u [사용자명] -p --no-data [데이터베이스명] [테이블명] > [백업파일]
-
--routines
- 프로시저 및 함수 백업
- ex:
mysqldump -u [사용자명] -p --databases --routines [데이터베이스명] > [백업파일]
-
--triggers
- 트리거 백업
- ex:
mysqldump -u [사용자명] -p --databases --triggers [데이터베이스명] > [백업파일]
-
--events
- 이벤트 스케줄러 백업
- ex:
mysqldump -u [사용자명] -p --databases --events [데이터베이스명] > [백업파일]
-
⭐️--single-transaction⭐️
- InnoDB 일관성 보장(하나의 트랜잭션을 이용하여 No Lock 으로 백업)
- 데이터 서버 서비스 중 백업 시 필수로 사용해야 하는 옵션
- ex:
mysqldump -u [사용자명] -p --databases --single-transaction [데이터베이스명] > [백업파일]
-
⭐️--quick⭐️
- 대용량 데이터를 백업할 때 사용되는 옵션
- mysqldump는 특정 데이터/테이블 전체를 스캔한 뒤 메모리에 모두 올리는 방식이기 때문에 데이터가 큰 경우 메모리에 부담이 간다. (데이터가 큰 경우 메모리 폭발 가능성 있음)
--quick옵션을 사용하면 데이터의 한 행(1 row)씩 메모리에 올리지 않고 바로 스토리지에 저장된다.- ex:
mysqldump -u [사용자명] -p --databases --quick [데이터베이스명] > [백업파일]
-
--no-create-info
- 데이터만 백업(INSERT 문만 있음)
- ex:
mysqldump -u [사용자명] -p --databases --no-create-info [데이터베이스명] > [백업파일]
-
--ignore-table
- 특정 테이블 제외 백업
- ex:
mysqldump -u [사용자명] -p --databases --ignore-table=[데이터베이스명].[제외 테이블명] > [백업파일]
