mysqldump를 활용한 MySQL 논리 백업과 상황별 옵션 정리

들어가며

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 옵션을 보여준다. 특정 옵션의 역할이 기억나지 않는다면 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로 이름이 바뀌었으므로, 서버 버전에 맞는 옵션을 선택해 사용하면 된다.

태그: mysqldump MySQL database-backup binlog Replication

11월 1일 04:15에 게시됨