mysqldump 사용법

mysqldump 의 사용법

백업뿐만 아니라, 데이터의 이관이나 복제등과 같이 어떤 MySQL 서버에서 다른 서버로 데이터를 옮기는 경우

MySQL 패키지에 기본적으로 포함되어 있는 mysqldump라는 도구를 자주 사용하게 된다.

mysqldump 명령과 옵션의 기본 구성

mysqldump [DB 접속 정보] [dump 옵션] [dump 대상 옵션] > /tmp/dump_data.sql

mysqldump는 기본적으로 모든 출력물을 화면(stdout)으로 보내게 된다. 

그래서 이 결과를 백업용이나 데이터 이관용으로 사용하기 위해서는 표준 출력을 파일로 리다이렉션해서 저장해야 한다.

DB 접속 정보

• --host : 데이터베이스 호스트 정보
• --user : 접속 데이터베이스 계정
• --password : 접속 데이터베이스 계정의 비밀번호
• --port : 접속 포트 번호
• --socket : 접속시 사용할 소켓 파일의 경로

dump 옵션

• --single-transaction : 
  dump를 하나의 트랜잭션을 이용해서 실행함
  (InnoDB 스토리지 엔진을 사용하는 테이블에 대해서는 Lock없이 일관된 덤프를 받을 수 있음).
  자세한 내용은 아래 "일관된 데이터 덤프 받기" 참조
• --flush-logs : dump를 시작하는 시점에 binary log를 rotation 시키도록 한다.
  Binary log를 증분 백업용으로 사용하는 경우, 풀백업과 증분 백업의 구분을 명확히 할 수 있다.
• --master-data : 
  이 옵션이 명시되면, dump 파일의 헤더 부분에 CHANGE MASTER TO 구문을 포함시키며,
  이 구문에는 덤프 시작 시점의 Binary log 파일명과 위치 정보 및 호스트 정보를 포함하고 있다.
  이 값을 1로 설정하면 CHANGE MASTER TO 구문이 실제 실행 가능한 형태로 포함되며,
  2로 설정되면 SQL 코멘트 형태로 참조만 할 수 있도록 포함된다.
  가끔 Binary log가 활성화되지 않은 서버에서 실행 시 에러를 유발하기도 하므로
  반드시 먼저 테스트를 해볼 것을 권장한다.
• --opt | --skip-opt : 
  opt 옵션은 여러 개의 옵션들을 묶어 놓은 별명과 같은 옵션이며
  (--add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert,
  --lock-tables, --quick, --set-charset) 옵션들을 포함한다.
  때때로(create db, table등을 포함할지 말지 제어시) mysqldump의 옵션을 상세하게
  제어하고자 할 경우에는 skip-opt 옵션으로 비활성화시킨 후, 필요한 것들만 직접 명시해주는 것이 좋다.
• --quick : 
  일반적으로 mysqldump는 테이블의 데이터들을 모두 Client의 메모리에 모두 로딩한 후,
  파일에 쓰기를 시작하게 되는데, 이 옵션이 활성화되면 Client의 메모리에 버퍼링하지 않고
  바로 파일로 쓰거나 화면으로 출력하게 된다. 이 옵션은 opt 옵션에 포함되어서 자동으로 활성화되기 때문에,
  (큰 테이블이 있는 경우) skip-opt를 사용하는 경우에는 quick 옵션을 별도로 명시해줘야 한다.
• --extended-insert : 
  mysqldump는 테이블의 데이터를 INSERT 문장 형태로 출력하게 되는데,
  이 옵션을 사용하면 확장 형태의 INSERT문장으로 데이터를 덤프하게 된다.
  이런 형태의 덤프는 다시 로드하는 작업을 빠르게 해줄 수 있다.
  (확장 형태의 INSERT 문장이라는 것은
  "INSERT INTO tab VALUES ('1','2'),('2','3'),('3','4');"와 같은 형태의 INSERT 문장을 의미한다.)
• --add-drop-database : 
  덤프 파일의 내용에 "DROP DATABASE ..." 명령을 포함하지 않도록 한다.
• --add-drop-table : 
  덤프 파일의 내용에 "DROP TABLE ..." 명령을 포함하지 않도록 한다.
• --no-create-db : 
  mysqldump 의 옵션에 --databases 또는 --all-databases 옵션이 명시되지 않으면,
  기본적으로 "CREATE DATABASE ..." 명령이 포함되는데,
  종종 이 명령이 필요치 않은 경우가 있을 수 있으며 이 때에는 이 옵션을 활성화 해주면 된다.
• --no-create-info : 
  덤프 파일의 내용에 "CREATE TABLE ..." 명령을 포함하지 않도록 한다.
• --create-options : 
  덤프 파일에 "CREATE TABLE ..." 명령이 출력되는 경우,
  초기 테이블 생성시 사용했던 테이블 생성 옵션들을 모두 포함하도록 한다.
• --no-data : 
  데이터는 덤프하지 않고 테이블의 구조만 덤프하도록 한다.
• --hex-blob : 
  덤프 대상 테이블중에서 이진 값을 가지는 컬럼의 값들을 16진수 문자열로 출력한다.
• --routines : 
  덤프시에 스토어드 프로시져와 함수를 출력하도록 한다.
• --triggers : 
  덤프시에 트리거를 출력하도록 한다.

dump 대상 옵션

• --all-databases : 
  이 옵션을 명시하면 현재 서버의 모든 데이터베이스를 덤프하게 된다.
  이 옵션으로 덤프를 받게 되면 기본적으로 "USE <데이터베이스명>;" 명령이 덤프 내용에 포함된다.
• --databases [database1] [database2] ... : 
  이 옵션을 명시하면 현재 서버에서 명시된 데이터베이스들을 덤프하게 된다.
  이 옵션으로 덤프를 받게 되면 기본적으로 "USE <데이터베이스명>;" 명령이 덤프 내용에 포함된다.
• [database1] : 
  별도의 옵션없이 mysqldump 명령의 마지막에 특정 데이터베이스명만을 명시하면
  해당 데이터베이스의 내용만을 덤프하게 된다.
  이 경우에는 "USE <데이터베이스명>;" 명령이 덤프 파일에 포함되지 않는다.
  그래서 데이터베이스를 덤프 받아서 동일 데이터베이스에 다시 적재하고자 하는 경우
  (덤프받은 파일을 동일 서버의 다른 데이터베이스명으로 적재하고자 하는 경우) "USE <데이터베이스명>;" 때문에
  작업이 쉽지 않은데 이런 경우에는 이 옵션으로 덤프를 받으면
  쉽게 동일 MySQL 서버에 다른 데이터베이스명으로 적재할 수 있다.
• [database1] [table1] [table2] : 
  별도의 옵션없이 mysqldump 명령의 마지막에 여러개의 오브젝트명을 명시하면,
  mysqldump는 첫번째 오브젝트는 데이터베이스명, 그 다음부터는 테이블명으로 인식하게 된다.
  그래서 해당 데이터베이스에 있는 각 테이블들만을 덤프하게 된다.
• --ignore-table :
  덤프할때 해당되는 테이블을 제외, 제외할 테이블 갯수만큼 옵션을 사용해야 함
  --ignore-table=db.table1
  --ignore-table=db.table2

일관된 데이터 덤프 받기

mysqldump를 이용하여 일관된 데이터를 덤프받고자 한다면,

lock-tables 옵션 또는 single-transaction 옵션을 사용해야만 한다.

간단히 이 옵션들의 사용에 따른 데이터 잠금 여부를 확인해 본 결과이다.

일반적으로 InnoDB의 경우에는 single-transaction을 사용하며,

MyISAM 의 경우에는 lock-tables 옵션을 사용해야 한다. (물론 MyISAM의 경우 일관된 백업의 의미는 없다)

InnoDB에서 single-transaction 옵션이 사용되면, table에 대한 lock을 걸지 않고 MVCC의 Read view를 이용하기 때문에

mysqldump 도중에도 trasaction (insert, update, delete)이 허용된다.

• mysqldump --skip-add-locks --single-transaction --skip-lock-tables db1 db2 > test.dump.sql
  ==> 덤프 도중 다른 세션에서 insert, update, delete 가능
• mysqldump --single-transaction  db1 db2 > test.dump.sql
  ==> 덤프 도중 다른 세션에서 insert, update, delete 가능
• mysqldump --lock-tables  db1 db2 > test.dump.sql
  ==> 덤프 도중 다른 세션에서 insert, update, delete 불가능

위의 옵션들을 이용한 여러가지 목적 또는 용도로 mysqldump명령

MySQL 테이블 & 루틴 전부 덤프

  mysqldump --user=root --password --opt --single-transaction --hex-blob --master-data=2 --routines --triggers

               --default-character-set=utf8 --databases [데이터베이스명1] [데이터베이스명2] > `hostname`_`date '+%y%m%d'`.dump.sql

MySQL의 루틴만 덤프

  mysqldump --user=root --password --routines --no-create-info --no-data

                   --no-create-db --skip-opt [데이터베이스명] > `hostname`_`date '+%y%m%d'`.dump.sql


View의 덤프
View 는 기본적으로 Algorithm (Merge 또는 Temptable)에 관계없이 
독립적인 데이터를 가지지 않기 때문에 mysqldump로 받아도 실질적인 데이터를 가지지 않는다.
그래서 mysqldump로 --no-create-info 가 명시되지 않는 이상은 테이블과 동일하게 CREATE VIEW 
문장은 백업이 가능하다.