Recommanded Free YOUTUBE Lecture: <% selectedImage[1] %>

Contents

mysqldump

mysqldump 클라이언트 유틸리티는 논리 백업(logical backup)을 수행한다. 원본 데이터베이스와 테이블을 재생할 수 있는 실행 가능한 SQL 문 세트를 생성한다. 백업 혹은 다른 SQL 서버로 전송하기 위해서 주로 사용한다. mysqldump는 SQL 문 외에도 CSV, XML, 기타 구분자를 가진 텍스트 형태로 출력 할 수 있다.

사용 방법

# mysqldump -u <userid> -p <database> -h <hostname> -P <port>

사용예제.
  • 백업 데이터베이스 : customer
  • 데이터베이스 계정 : dbadmin
  • mysql 서버 호스트이름 : 172.17.0.2
  • mysql 서버 포트번호 : 3306
일 경우
# mysqldump -u dbadmin -p customer -h 172.17.0.2 -P 3306

DB 접속 옵션들

  • --host, -h : 서버 호스트 이름
  • --user, -u : 데이터베이스 접속 계정
  • --password, -p : 데이터베이스 접속 계정의 패스워드
  • --port, -P : 데이터베이스 서버 포트
  • --socket : 유닉스 도메인 소켓을 사용 할 경우 사용할 소켓 파일

주요 옵션들

  • --single-transaction : 덤프를 실행하기 전에 트랜잭션을 수행한다. 이렇게 하면 전체 데이터베이스를 잠그지 않고도 일관된 덤프를 받을 수 있다. InnooDB 엔진에서만 사용 할 수 있다.
  • --lock-tables, -l : 덤프를 하기 전에 모든 테이블을 잠근다. 데이터베이스 별로 테이블을 잠그기 때문에, 여러 데이터베이스를 덤프하는 경우 일관성을 보장하지 않는다.
  • --lock-all-tables, -x : 모든 데이터베이스의 테이블을 잠근다. 이 옵션을 사용하면 --single-transaction, --lock-tables 를 오프(off)시킨다.
  • --no-create-db, -n : databases 또는 all-databases 옵션을 사용 할 경우 덤프에 CREATE DATABASE 명령이 들어간다. 이 옵션을 이용해서 CREATE DATABASE 명령을 무력화 시킬 수 있다.
  • --no-data, -d : 테이블의 데이터를 저장하지 않는다. 데이터베이스 스키마를 추출하고자 할 때 유용하게 사용 할 수 있다.
  • --single-transaction : 데이터를 덤프하기 전에 BEGIN SQL 문을 시작한다. InnoDB와 같은 트랜잭션을 지원하는 테이블을 덤프하기에 좋다. 이 옵션을 이용하면 애플리케이션 블락(block)없이 데이터를 덤프 할 수 있다. 덤프전에 transaction isolation level을 REPEATABLE READ상태로 변경한다. 즉 1. 아직 COMMIT 되지 않은 신뢰 할 수 없는 데이터는 읽지 않으며, 2. 한 트랜잭션에서 동일한 SELECT 쿼리에 대해서 결과가 동일하며, 3. 이전의 SELECT 쿼리 결과에 없던 row 가 생기지 않는다.
  • --flush-logs, -F : dump를 하는 시점에 binary log를 rotation 한다.

예제

특정 데이터베이스 백업

wiki 데이터베이스와 테이블, 데이터를 백업한다.
# mysqldump -u root -p wiki -h 172.17.0.2 > db.sql

모든 데이터베이스 백업

모든 데이터베이스와 테이블, 데이터를 백업한다.
# mysqldump -u root -p --all-databases -h 172.17.0.2 > db.sql

스키마 백업

데이터베이스와 테이블 스키마만 백업한다. 데이터는 백업하지 않는다. wiki 데이터베이스에 있는 모든 테이블들을 백업한다.
# mysqldump --no-data -u root -p wiki -h 172.17.0.2 > db.sql
--all-databases와 함께 사용하면 전체 데이터베이스 스키마를 백업할 수 있다.
# mysqldump -u root -p --all-databases --no-data -h 172.17.0.2 > alldb.sql

참고

논리백업과 물리백업

  • 물리백업 : 파일시스템 레벨에서 백업을 한다. 즉 MySQL 데이터가 기록되는 디렉토리를 통으로 압축해서 백업한다. 속도가 빠르고 작업이 단순한다. 복구 할 때는 서비스를 중단하고, 디렉토리에 압축을 푼다.
  • 논리적백업 : mysqldump 등의 툴을 이용해서 백업한다. 물리백업에 비해서 많은 시간이 걸릴 수 있다.
AWS RDS는 스냅샷 백업기능을 제공하는데, 물리적 백업 방식이다.