들어가며
mysqldump는 데이터베이스에서 데이터를 덤프할 때 자주 사용하는 도구다. 이 글에서는 mysqldump의 기본적인 사용법을 살펴보고, 실무에서 유용한 백업 요령 몇 가지를 정리한다.
1. mysqldump 기본 사용법
mysqldump는 MySQL에 기본 포함된 논리 백업(logical backup) 도구로, 데이터베이스를 SQL 문의 집합으로 덤프한다. 결과물은 파일로 저장할 수 있으며, CREATE DATABASE, CREATE TABLE, INSERT 등 데이터베이스를 다시 구성하는 데 필요한 SQL 명령이 담긴다. 복원이 필요할 때는 이 파일을 실행하기만 하면 된다.
기본 문법은 다음과 같다.
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump --help를 실행하거나 공식 문서를 확인해 보면 알 수 있듯이 설정 가능한 옵션은 매우 많다. 자주 사용하는 옵션 몇 가지를 아래에 정리했다.

위 표는 대표적인 mysqldump 옵션을 보여준다. 특정 옵션의 역할이 기억나지 않는다면 mysqldump --help로 확인하면 된다. 불리언 타입 옵션에는 보통 반대 옵션이 함께 존재한다. 예를 들어 --triggers는 기본으로 켜져 있으며, --skip-triggers로 끌 수 있다.
2. 백업 시 유용한 요령
mysqldump는 대용량 백업에는 그다지 적합하지 않지만, 파라미터를 상황에 맞게 조정할 수 있고 사용이 간편하다는 장점 덕분에 데이터 추출 영역에서는 여전히 널리 쓰인다.
실무 경험을 바탕으로 정리한 요령은 다음과 같다.
- 일관성 있는 백업을 얻고 테이블 잠금을 줄이려면
--single-transaction옵션을 사용하는 것이 좋다. - 필요한 데이터만 추출하도록 조건을 지정해 결과 파일의 크기를 최대한 줄인다.
- 레플리카(슬레이브) 구성을 목적으로 한다면
--master-data=2로 소스 서버의 바이너리 로그 위치 정보를 기록한다. - 스토어드 프로시저, 사용자 정의 함수, 이벤트까지 백업하려면
--routines와--events를 추가해야 한다. 두 항목은 기본적으로 꺼져 있다. - 용도를 모르는 옵션은 임의로 추가하지 말고 기본값을 유지한다.
아래는 접속 정보를 변수로 분리해 상황별로 정리한 사용 예시다.
# 접속 정보와 출력 경로를 변수로 관리
DB_USER='dba'
DB_PASS='S3cret!'
HOST='127.0.0.1'
PORT='3306'
OUT='/backup/mysql'
# 전체 데이터베이스 백업 (스토어드 프로시저, 사용자 정의 함수, 이벤트 포함)
mysqldump -u"$DB_USER" -p"$DB_PASS" --single-transaction \
--routines --events --all-databases \
> "$OUT/all_databases.sql"
# 바이너리 로그 위치까지 기록 (레플리카 구성에 사용)
mysqldump -u"$DB_USER" -p"$DB_PASS" --single-transaction \
--routines --events --all-databases --master-data=2 \
> "$OUT/all_databases_binlog.sql"
# 특정 데이터베이스만 백업
mysqldump -u"$DB_USER" -p"$DB_PASS" --single-transaction \
--routines --events --databases shop \
> "$OUT/shop.sql"
mysqldump -u"$DB_USER" -p"$DB_PASS" --single-transaction \
--routines --events --databases shop crm \
> "$OUT/shop_crm.sql"
# 특정 테이블만 백업
mysqldump -u"$DB_USER" -p"$DB_PASS" --single-transaction \
shop orders \
> "$OUT/orders.sql"
mysqldump -u"$DB_USER" -p"$DB_PASS" --single-transaction \
shop orders customers products \
> "$OUT/orders_customers_products.sql"
# 한 행당 하나의 INSERT 문으로 출력
mysqldump -u"$DB_USER" -p"$DB_PASS" --single-transaction \
--skip-extended-insert shop orders \
> "$OUT/orders_row_by_row.sql"
# 조건에 맞는 일부 데이터만 추출
mysqldump -u"$DB_USER" -p"$DB_PASS" --single-transaction \
shop orders --where="created_at >= '2021-06-01 00:00:00'" \
> "$OUT/orders_since_20210601.sql"
mysqldump -u"$DB_USER" -p"$DB_PASS" --single-transaction \
shop customers --where='id < 100' \
> "$OUT/customers_first_100.sql"
# 특정 테이블을 제외하고 백업
mysqldump -u"$DB_USER" -p"$DB_PASS" --single-transaction \
--databases shop --ignore-table=shop.logs --ignore-table=shop.audit_trail \
> "$OUT/shop_without_logs.sql"
# 구조만 추출 / 데이터만 추출
mysqldump -u"$DB_USER" -p"$DB_PASS" shop --no-data \
> "$OUT/shop_schema_only.sql"
mysqldump -u"$DB_USER" -p"$DB_PASS" shop --no-create-info \
> "$OUT/shop_data_only.sql"
# 특정 데이터베이스의 스토어드 프로시저와 사용자 정의 함수만 추출
mysqldump -u"$DB_USER" -p"$DB_PASS" \
--no-create-info --no-data --routines shop \
> "$OUT/shop_routines.sql"
# 원격 서버에서 추출 (MySQL 서버가 로컬에 없는 경우)
mysqldump -u"$DB_USER" -p"$DB_PASS" -h203.0.113.10 -P"$PORT" \
--single-transaction --databases shop \
> "$OUT/shop_remote.sql"
참고로 MySQL 8.0.26 이후 버전에서는 --master-data가 --source-data로 이름이 바뀌었으므로, 서버 버전에 맞는 옵션을 선택해 사용하면 된다.