[MySQL][Backup n Recovery] mysqldump 프로그램 제대로 파해치기

MySQL에서는 데이터 베이스 백업을 위한 여러가지 방법을 지원합니다. 그중에 가장 대표적인것이 mysqldump입니다. 사용법도 쉽고 지원되는 옵션도 많아서 원하는 방법으로 백업이 가능합니다. 참고로 백업도 권한이 있어야 수행할 수 있습니다. 아무나 데이터를 컨트롤 하면 안되기 때문입니다. 또한 덤프만큼 복원시 필요한 권한도 있습니다. 그래서 백업 및 복구 수행은 많은 권한이 필요합니다. 그래서 별도로 백업 전용 계정을 만들어 사용하기도 합니다.

참고로 MySQL에서는 백업이라는 용어보단 덤프란 용어를 더 많이 씁니다. 개념은 같지만 약간의 미묘한 차이가 있습니다. 그래서 지금부터는 백업이라는 용어보다 덤프라는 용어로 사용하겠습니다.

 

■  mysqldump 소개

MySQL에서 제공하는 대표적인 덤프 프로그램으로 논리적 덤프를 지원합니다. 전체 인스턴스레벨의 덤프부터 데이터베이스레벨, 테이블레벨까지 모두 지원합니다. csv, delimeted, json형식의 데이터 형식도 지원합니다.

카테고리별로 대부분의 필요한 옵션을 지원하며 옵션을 잘 조합하면 원하는 덤프 형식으로 만들 수 있습니다. MySQL에서 사용되는 가장 대표적인 덤프 프로그램이라 할 수 있습니다.

덤프뿐만 아니라, 데이터의 이관이나 복제등과 같이 어떤 MySQL 서버에서 다른 서버로 데이터를 옮기는 경우 이 프로그램을 이용하면 손쉽게 구성할 수 있습니다.

 

 

■  덤프 아키텍쳐

MySQL은 덤프시 스토리지 엔진에 따른 덤프 방법이 약간 다릅니다. 그리고 주어지는 옵션도 약간 다릅니다. 대표적인게 MyISAM과 InnoDB 스토리엔진의  덤프 방식일 것입니다. 이 둘의 가장 큰 차이는 트랜잭션 지원의 여부입니다. 보통 데이터베이스에서 덤프를 받을 때는 온라인, 즉 서버가 수행중인 상태에서 덤프를 받습니다. 무중단으로 서비스를 제공해야 하기 때문입니다. 이런 덤프 방식을 보통 온라인 덤프이라고 합니다. 또한가지, 덤프를 수행할 때 한가지 중요한 명령을 수행하는데 바로 테이블에 Lock을 걸고 덤프를 수행합니다. 왜 그럴까요? 이유는 데이터의 정합성을 맞추기 위해서입니다. 또한가지는 덤프를 원활하게 하기 위해서 입니다.

한가지 예를 들어 얘기를 해보겠습니다. MyISAM 스토리지 엔진 타입의 테이블에 대해 덤프를 수행 합니다. 그리고 특정 데이터베이스 안의 테이블들만 덤프를 수행할 예정입니다. 이때 덤프를 수행중인데 데이터가 변경이 되면 어떻게 될까요? 해당 데이터에 Lock이 걸리게 됩니다. 특히나 MyISAM 스토리지 엔진의 경우 Table 단위의 Lock입니다. 이렇게 되면 해당 DML이나 DDL이 수행된 후 덤프가 진행됩니다. 그러면 덤프를 하는데 있어서 방해를 받게 됩니다. 즉 덤프를 진행하는데 어려움이 있을 수 있습니다. 그리고 데이터의 정합성에도 문제가 발생하게 됩니다. 즉 덤프 순간의 데이터를 모두 저장해야 하는데 데이터가 변경되고 있다면 의미가 없기 때문입니다.(이 경우는 업무 목적에 따라 틀려질 수도 있습니다.)

 

■  필요권한

select, show view, trigger, lock table(--single-transaction 옵션 이용시),alter등의 권한이 필요합니다. 생각보다 많은 권한이 필요합니다. 그래서 보통 덤프 사용자를 생성해서 수행합니다. root 유저를 이용하는 경우가 많은데 이는 가능하면 사용하지 않는것을 추천드립니다. 너무 막강한 권한의 계정이기 때문에 사소한 문제라도 크게 번질 수 있기 때문입니다.

 

■  덤프 옵션들에 대해

접속에 관련된 옵션과 SSL, 인증옵션같은 옵션들을 제외하고 대부분의 옵션을 정리했습니다. MySQL 홈페이지에 있는 내용과 마찬가지로 카테고리별로 정리했습니다. 더 많은 옵션을 보고 싶으시다면 다음 페이지를 참고 하시면 됩니다.

URL : https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

 

■  mysqldump의 기본 사용방법

+ mysqldump 명령과 옵션의 기본 구성

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

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

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

 

+ 기본적인 DB 접속 정보

--host : 데이터베이스 호스트 정보

--user : 접속 데이터베이스 계정

--password : 접속 데이터베이스 계정의 비밀번호

--port : 접속 포트 번호

--socket : 접속시 사용할 소켓 파일의 경로

 

옵션과 접속방법을 통한 실 사용 예제는 맨 아래에서 확인해 보겠습니다.

 

 

 

■   동작방식과 기능에 따른 mysqldump 옵션들과 분류

+ DDL 옵션들

--add-drop-database : 데이터베이스를 생성하는 문법을 작성하기 전 삭제문(drop database)을 추가합니다.

--add-drop-table : 테이블을 생성하는 문법을 만들기 전 삭제문법(drop table)을 생성합니다.

--add-drop-trigger : 트리거를 생성하는 문법을 만들기 전 삭제문법(drop trigger)을 생성합니다.

--all-tablespaces, -Y : NDB 테이블에서 사용하는 테이블 스페이스를 작성하는 데 필요한 모든 SQL 문을 테이블 덤프에 추가합니다. 이 정보는 mysqldump의 출력에 포함되지 않습니다. 이 옵션은 현재 MySQL 5.7에서 지원되지 않는 NDB 클러스터 테이블에만 관련됩니다.

--no-createdb : database 옵션이나 all-database 옵션이 주어지지 않으면 create database문을 추가하지 않습니다.

--no-create-info : 덤프 파일의 내용에 테이블을 생성하는 문법(CREATE TABLE )을 제외합니다.

--no-tablespaces : CREATE LOGFILE GROUP과 CREATE TABLESPACE문을 생성하지 않습니다.

--replace : Insert 작업중 Primary 키, 혹은 unique 키가 같은 값이 있다면 이전값(old)은 삭제하고 새로운 값(new)으로 insert 작업을 합니다. 이외에 모든 작업은 Insert 작업입니다.

 

+ 디버그 옵션

--allow-keywords

키워드인 열 이름을 만들 수 있습니다. 각 열 이름 앞에 테이블 이름을 붙여서 작동합니다.

 

 --comments, -i

프로그램 버전, 서버 버전 및 호스트와 같은 추가 정보를 덤프 파일에 작성합니다. 이 옵션은 기본적으로 활성화되어 있습니다. 이 추가 정보를 표시하지 않으려면 --skip-comments를 사용합니다.

 

--debug[=debug_options], -# [debug_options]

디버깅 로그를 작성합니다. 일반적인 debug_options 문자열은 d:t:o,file_name입니다. 기본값은 d:t:o,/tmp/mysqldump.trace입니다.

 

 --debug-check

프로그램이 종료 될 때 디버깅 정보를 출력합니다.

 

 --debug-info

프로그램이 종료 될 때 디버깅 정보와 메모리 및 CPU 사용 통계를 출력합니다.

 

--dump-date

--comments 옵션이 제공되면 mysqldump는 덤프가 끝날 때 다음 형식의 주석을 생성합니다.

주석 : -- Dump completed on DATE

 

그러나 날짜 데이터가 다른 경우에도 다른 시간에 찍은 덤프 파일이 다른 것처럼 보이게합니다. --dump-date 및 --skip-dump-date는 날짜를 주석에 추가할지 여부를 제어합니다. 기본값은 --dump-date입니다 (주석에 날짜 포함). --skip-dump-date는 날짜 인쇄를 하지 않습니다.

 

--force, -f

모든 오류를 무시합니다. 테이블 덤프 중에 SQL 오류가 발생하더라도 계속진행합니다.

 

이 옵션의 사용 중 하나는 삭제 된 일반 테이블을 참조하기 때문에 유효하지 않은 뷰가 발생하더라도 mysqldump가 계속 실행되도록하는 것입니다. --force가 없으면 mysqldump가 오류 메시지와 함께 종료됩니다. --force를 사용하면 mysqldump는 오류 메시지를 인쇄하지만 뷰 정의를 포함하는 SQL 주석을 덤프 출력에 기록하고 계속 실행합니다.

 

특정 오류를 무시하기 위해 --ignore-error 옵션도 제공되면 --force가 우선합니다.

 

 --log-error=file_name

명명 된 파일에 경고와 오류를 추가하여 기록합니다. 디폴트는 로깅을하지 않는 것입니다.

 

--skip-comments

--comments 옵션에 대한 설명을 참조하세요.

 

 --verbose, -v

상세 모드. 프로그램의 기능에 대한 자세한 정보를 출력합니다.

 

+ 내부 옵션(내부 시스템 옵션)

--character-sets-dir=dir_name : 캐릭터셋이 설치되어 있는 디렉토리를 표기합니다.

--default-character-set=charset_name : 기본적으로 사용할 캐릭터셋 지정합니다.

--no-set-names : --set-charset 옵션을 끕니다. --skip-set-charset과 비슷합니다.

--set-charset : 기본적으로 켜지는 옵션입니다. 덤프시 SET NAMES문을 표기합니다.

 

+ 복제 옵션

--apply-slave-statements : --dump-slave 옵션으로 생성 된 슬레이브 덤프의 경우 CHANGE MASTER TO 문 앞에 STOP SLAVE 문을 추가하고 출력 끝에 START SLAVE 문을 추가하세요.

 

--delete-master-logs : 마스터 복제 서버에서 덤프 조작을 수행 한 후 PURGE BINARY LOGS 문을 서버로 전송하여 바이너리 로그를 삭제합니다. 이 옵션은 자동으로 --master-data를 활성화합니다.

 

--dump-slave[=value] :

이 옵션은 복제 슬레이브 서버를 덤프하여 덤프 서버와 동일한 마스터를 갖는 슬레이브로 다른 서버를 설정하는 데 사용할 수있는 덤프 파일을 생성하는 데 사용된다는 점을 제외하고 --master-data와 유사합니다. 덤프 출력에 덤프 된 슬레이브 마스터의 2 진 로그 좌표 (파일 이름 및 위치)를 나타내는 CHANGE MASTER TO 문이 포함됩니다. CHANGE MASTER TO 문은 SHOW SLAVE STATUS 출력에서 ​​Relay_Master_Log_File 및 Exec_Master_Log_Pos의 값을 읽고 각각 MASTER_LOG_FILE 및 MASTER_LOG_POS에 사용합니다. 슬레이브가 복제를 시작해야하는 마스터 서버 위치(포지션)입니다.

 

--dump-slave는 --master-data 옵션에 의해 수행되는 것처럼 덤프 된 서버의 좌표 대신 마스터의 좌표를 사용합니다. 또한이 옵션을 지정하면 --master-data 옵션이 재정의되고 (사용되는 경우) 무시되고 효과적으로 무시됩니다.

 

주의사항

덤프를 적용 할 서버가 gtid_mode = ON 및 MASTER_AUTOPOSITION = 1을 사용하는 경우 이 옵션을 사용하지 마십시오.

 

옵션 값은 --master-data와 동일한 방식으로 처리됩니다 (값을 설정하지 않거나 1을 설정하면 CHANGE MASTER TO 문이 덤프에 기록되고, 2를 설정하면 명령문이 기록되지만 SQL 주석에 포함됨) 다른 옵션을 활성화 또는 비활성화하고 잠금이 처리되는 방식에서 --master-data와 동일한 효과를 가집니다.

 

이 옵션을 사용하면 mysqldump가 덤프 전에 슬레이브 SQL 스레드를 중지한 후 다시 시작합니다.

 

--dump-slave와 함께 --apply-slave-statements 및 --include-master-host-port 옵션도 사용할 수 있습니다.

 

--include-master-host-port

--dump-slave 옵션으로 생성 된 슬레이브 덤프의 CHANGE MASTER TO 문의 경우, 슬레이브 마스터의 호스트 이름 및 TCP / IP 포트 번호에 MASTER_HOST 및 MASTER_PORT 옵션을 추가하십시오.

 

--master-data :

마스터 복제 서버를 덤프하여 다른 서버를 마스터의 슬레이브로 설정하는 데 사용할 수 있는 덤프 파일을 생성하려면이 옵션을 사용하세요. 덤프 출력에 덤프 된 서버의 바이너리 로그 포지션 (파일 이름 및 위치)를 나타내는 CHANGE MASTER TO 문이 포함됩니다. 덤프 파일을 슬레이브에로드 한 후 슬레이브가 복제를 시작해야하는 마스터 서버 포지션(위치)입니다.

 

옵션 값이 2 인 경우 CHANGE MASTER TO 문은 SQL 주석으로 작성되므로 정보용입니다. 덤프 파일을 다시로드 할 때는 영향을 미치지 않습니다. 옵션 값이 1 인 경우 명령문은 주석으로 작성되지 않으며 덤프 파일을 다시로드 할 때 적용됩니다. 옵션 값을 지정하지 않으면 기본값은 1입니다.

 

이 옵션에는 RELOAD 권한이 필요하며  바이너리 로그가 사용 가능해야합니다.

 

참고 : 옵션에 따른 출력 내용 일부

change master = 1
--
-- Position to start replication or point-in-time recovery from
--
CHANGE MASTER TO MASTER_LOG_FILE='binary_log.000003', MASTER_LOG_POS=154;

change master = 2
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='binary_log.000003', MASTER_LOG_POS=154;

 

--master-data 옵션은 --lock-tables를 자동으로 끕니다. 또한 --single-transaction 옵션도 지정하지 않는 한 --lock-all-tables를 설정합니다. 이 경우 전역 읽기 잠금은 덤프 시작시 짧은 시간 동안만 획득됩니다 (설명 참조 --single-transaction). 모든 경우에,  로그에 대한 어떠한 행위도 덤프의 정확한 순간에 발생합니다.

 

--dump-slave 옵션을 사용하여 마스터의 기존 슬레이브를 덤프하여 슬레이브를 설정할 수도 있습니다. --dump-slave 옵션은 --master-data를 무시하고 두 옵션을 모두 사용하는 경우 무시되도록합니다.

 

--set-gtid-purged=value :

이 옵션을 사용하면 SET @@ GLOBAL.gtid_purged 문을 출력에 추가할지 여부를 표시하여 덤프 파일에 기록 된 GTID (Global Transaction ID) 정보를 제어 할 수 있습니다. 이 옵션을 사용하면 덤프 파일을 다시로드하는 동안 이진 로깅을 비활성화하는 명령문이 출력에 기록 될 수도 있습니다.

다음 표는 허용되는 옵션 값을 보여줍니다. 기본값은 AUTO입니다.

값       의미

OFF SET 문법을 추가하지 않습니다.

ON       SET 문법을 추가합니다. 서버에 GTID사용을 설정하지 않았다면 에러가 발생합니다.

AUTO 서버에 GTID를 사용으로 설정했다면 자동으로 SET 문법을 추가합니다.

 

GTID 기반 복제를 사용하는 서버에서 부분 덤프하려면 --set-gtid-purged = {ON | OFF} 옵션을 지정해야 합니다. 덤프 된 서버의 일부 데이터만 사용하여 새 복제 슬레이브를 배포하려는 경우 ON을 사용하세요. 토폴로지 내에서 테이블을 복사하여 테이블을 복구하려는 경우 OFF를 사용하세요. 연결되지 않은 복제 토폴로지간에 테이블을 복사하려는 경우 OFF를 사용하세요.

 

--set-gtid-purged 옵션은 덤프 파일을 다시로드 할 때 이진 로깅에 다음과 같은 영향을 미칩니다.

# --set-gtid-purged = OFF : SET @@ SESSION.SQL_LOG_BIN = 0; 출력에 추가되지 않습니다.

# --set-gtid-purged = ON : SET @@ SESSION.SQL_LOG_BIN = 0; 출력에 추가됩니다.

# --set-gtid-purged = AUTO : SET @@ SESSION.SQL_LOG_BIN = 0; 덤프중인 서버에서 GTID가 활성화 된 경우 (즉, AUTO가 ON으로 평가 된 경우) 출력에 추가됩니다.

 

+ 포멧 옵션

 --compact :

좀더 콤팩트한 덤프를 받습니다. 이 옵션은 --skip-add-drop-table, --skip-add-locks, --skip-comments, --skip-disable-keys, and --skip-set-charset의 옵션들을 사용합니다.

 

--compatible=name :

다른 데이터베이스 시스템 또는 이전 MySQL 서버와 더 호환되는 덤프를 생성합니다. name 값은 ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options 또는 no_field_options 일 수 있습니다. 여러 값을 사용하려면 쉼표로 구분합니다. 이 값은 서버 SQL 모드 설정을 위한 해당 옵션과 동일한 의미를 갖습니다.

 

이 옵션은 다른 서버와의 호환성을 보장하지 않습니다. 덤프 출력의 호환성을 높이기 위해 현재 사용 가능한 SQL 모드 값만 사용합니다. 예를 들어 --compatible-oracle은 오라클 타입이나 오라클 코멘트를 사용하는 데이터 타입과 맵핑되지 않습니다.

 

--complete-insert, -c

컬럼 이름을 포함하는 완전한 INSERT 문을 사용하세요.

 

--create-options

CREATE TABLE 문법안에 모든 MySQL 관련 테이블 옵션을 포함합니다.

 

--fields-terminated-by : 필드 구분자

--fields-enclosed-by : 필드를 구분지을 수 있는 시작과 끝 문자

'문자' -> 싱글쿼테이션(')을 의미

--fields-optionally-enclosed-by : 필요할경우 필요한 구분자를 더 추가 

--fields-escaped-by : 해당필드는 뺀다.

--lines-terminated-by : 라인(행) 구분자.

 

--hex-blob : 덤프 대상 테이블중에서 이진 값을 가지는 컬럼의 값들을 16진수 문자열로 변환

 

 --quote-names, -Q :

(`) 문자 안에있는 식별자 (예 : 데이터베이스, 테이블 및 열 이름). ANSI_QUOTES SQL 모드가 활성화 된 경우 식별자는 "문자로 인용됩니다. 이 옵션은 기본적으로 활성화되어 있습니다. --skip-quote-names로 비활성화 할 수 있지만 --compatible과 같은 옵션 뒤에는이 옵션을 지정해야합니다. --quote-names를 활성화 할 수 있습니다.

 

--result-file=file_name, -r file_name

주어진 파일 이름으로 직접 출력합니다. 덤프를 생성하는 동안 오류가 발생하더라도 결과 파일이 작성되고 이전 내용이 겹쳐 써집니다.

줄 바꿈 (\n : newline) 문자가 캐리지 리턴/줄 바꿈(\r\n: carriage return/newline) 순서로 변환되지 않도록하려면 Windows에서 이 옵션을 사용해야합니다.

 

--tab=dir_name, -T dir_name

탭으로 구분 된 텍스트 형식의 데이터 파일을 생성합니다. 덤프 된 각 테이블에 대해 mysqldump는 테이블을 작성하는 CREATE TABLE 문이 포함 된 tbl_name.sql 파일을 작성하고 서버는 해당 데이터가 포함 된 tbl_name.txt 파일을 작성합니다. 옵션 값은 파일을 쓸 디렉토리입니다.

 

+ 필터링 옵션

--all-databases, -A

모든 데이터베이스의 모든 테이블을 덤프하십시오. 이는 --databases 옵션을 사용하고 명령 줄에서 모든 데이터베이스의 이름을 지정하는 것과 같습니다.

 

--databases, -B :

여러 데이터베이스를 덤프합니다. 일반적으로 mysqldump는 명령 행의 이름 인수를 데이터베이스 이름으로, 그 다음 이름을 테이블 이름으로 인식합니다. 이 옵션을 사용하면 모든 이름 인수를 데이터베이스 이름으로 사용합니다. CREATE DATABASE 및 USE 문은 각각의 새 데이터베이스 전에 출력에 포함됩니다.

 

이 옵션은 INFORMATION_SCHEMA 및 performance_schema 데이터베이스를 덤프하는 데 사용될 수 있으며 일반적으로 --all-databases 옵션으로도 덤프되지 않습니다. --skip-lock-tables 옵션도 사용하세요.

 

--events, -E :

덤프 된 데이터베이스에 대한 이벤트 스케줄러 이벤트를 출력에 포함합니다. 이 옵션에는 해당 데이터베이스에 대한 EVENT 권한이 필요합니다.

 

--events를 사용하여 생성 된 출력에는 이벤트를 작성하기위한 CREATE EVENT 문이 포함됩니다. 그러나 이러한 명령문에는 이벤트 작성 및 수정 시간 소인과 같은 속성이 포함되지 않으므로 이벤트가 다시 로드될 때 재로드 시간과 동일한 시간 소인으로 작성됩니다.

 

원래 시간 소인 속성으로 이벤트를 작성해야하는 경우 --events를 사용하지 마세요. 대신 mysql 데이터베이스에 대한 적절한 권한이있는 MySQL 계정을 사용하여 mysql.event 테이블의 내용을 직접 덤프하고 다시로드하세요.

 

 --ignore-error=error[,error]...

특정 에러를 무시합니다. mysqldump를 수행하는동안 콤마로 구분된 에러를 입력하면 해당에러에 대해서는 무시합니다. —force 옵션은 모든 에러를 무시하고 수행합니다.

 

 --ignore-table=db_name.tbl_name

특정데이터베이스의 특정 테이블에 대해서 dump하지 않습니다. 여러개의 테이블을 덤프하지 않으려면 여러번 입력합니다. 이 옵션은 view또한 무시합니다.

 

---no-data : 테이블 행 정보를 쓰지 않습니다 (즉, 테이블 내용을 덤프하지 않습니다). 이는 테이블에 대해 CREATE TABLE 문만 덤프하려는 경우에 유용합니다 (예 : 덤프 파일을로드하여 빈 테이블 사본을 작성하는 경우).

 

--routines, -R : 

덤프 된 데이터베이스에 대한 저장 루틴 (프로 시저 및 함수)을 출력에 포함합니다. 이 옵션에는 mysql.proc 테이블에 대한 SELECT 권한이 필요합니다.

 

--routines를 사용하여 생성 된 출력에는 루틴을 작성하기위한 CREATE PROCEDURE 및 CREATE FUNCTION 문이 포함됩니다. 그러나 이 명령문에는 루틴 작성 및 수정 타임 스탬프와 같은 속성이 포함되지 않으므로 루틴을 다시 로드하면 타임 스탬프가 다시 로드시간과 동일한 상태로 작성됩니다.

 

루틴을 원래 시간 소인 속성으로 작성해야하는 경우 --routines를 사용하지 마세요. 대신 mysql 데이터베이스에 대한 적절한 권한이있는 MySQL 계정을 사용하여 mysql.proc 테이블의 내용을 직접 덤프하고 다시로드하세요.

 

--tables

--databases 또는 -B 옵션을 재정의합니다. mysqldump는 옵션 뒤에 오는 모든 이름 인수를 테이블 이름으로 간주합니다.

 

--triggers

덤프 된 각 테이블에 대한 트리거를 출력에 포함합니다. 이 옵션은 기본적으로 활성화되어 있습니다. 사용하지 않을 시 --skip-triggers로 비활성화하세요.

 

테이블의 트리거를 덤프 할 수 있으려면 테이블에 대한 TRIGGER 특권이 있어야합니다.

 

여러 트리거가 허용됩니다. mysqldump는 트리거를 활성화 순서대로 덤프하여 덤프 파일이 다시로드 될 때 동일한 활성화 순서로 트리거가 작성되도록 합니다. 그러나 mysqldump 덤프 파일에 동일한 트리거 이벤트 및 조치 시간이 있는 테이블에 대해 여러 트리거가 포함 된 경우 여러 트리거를 지원하지 않는 이전 서버로 덤프 파일을 로드하려고 하면 오류가 발생합니다.

(해결 방법은 2.12.3 절.“다운 그레이드 노트”를 참조하세요. 트리거를 이전 서버와 호환되도록 변환 할 수 있습니다.)

 

--where='where_condition', -w 'where_condition'

주어진 WHERE 조건에 의해 선택된 행만 덤프합니다. 명령 해석기에 특수한 공백이나 다른 문자가 포함 된 경우 조건 주위의 인용 부호는 필수입니다.

예제)

--where="user='jimf'"

-w"userid>1"

-w"userid<1"

 

 

 

+ 성능옵션

--disable-keys :

각 테이블에 대해 INSERT 문을 /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; 과 /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; 문으로 생성합니다. 이렇게하면 모든 행이 삽입 된 후에 인덱스가 작성되므로 덤프 파일을 더 빨리로드 할 수 있습니다. 이 옵션은 MyISAM 테이블의 고유하지 않은 인덱스에만 유효합니다.

 

--extended-insert : 다중 인서트 문법. 병렬 인서트

여러 VALUES 목록을 포함하는 다중 행 구문을 사용하여 INSERT 문을 작성하십시오. 결과적으로 덤프 파일이 작아지고 파일을 다시로드 할 때 삽입 속도가 빨라집니다.

예) "INSERT INTO tab VALUES ('1','2'),('3’,’4’)

 

--insert-ignore

INSERT 문 대신 INSERT IGNORE 문을 작성하세요.

 

--max-allowed-packet=value

클라이언트 / 서버 통신을위한 버퍼의 최대 크기입니다. 기본값은 24MB이고 최대 값은 1GB입니다.

원격에서 dump시 반드시 값을 늘리셔야 좋은 효과를 볼 수 있습니다.

 

 --net-buffer-length=value

클라이언트 / 서버 통신을위한 버퍼의 초기 크기입니다. --extended-insert 또는 --opt 옵션과 같이 여러 행 INSERT 문을 만들 때 mysqldump는 최대 --net-buffer-length 바이트 길이의 행을 만듭니다. 이 변수를 늘리면 MySQL 서버 net_buffer_length 시스템 변수에 최소한이 값이 큰지 확인하십시오.

 

--opt

기본적으로 활성화 된 이 옵션은 --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset으로 조합되어 있습니다. 빠른 덤프 작업을 제공하고 MySQL 서버로 빠르게 다시로드 할 수있는 덤프 파일을 생성합니다.

 

--opt 옵션은 기본적으로 활성화되어 있으므로 --skip-opt 옵션 만 지정하면 여러 기본 설정을 해제 할 수 있습니다. --opt의 영향을 받는 옵션들의 집합을 선택적으로 활성화 또는 비활성화하는 방법에 대한 내용은 mysqldump 옵션 그룹에 대한 설명을 참조하세요.

 

 --quick, -q

큰 테이블을 덤프할때 유리합니다. 일반적인 덤프는 서버의 메모리에 올린 후 데이터를 내려받지만 이 옵션을 이용하면 자동으로 메모리에 올리지 않고 바로 디스크에 쓰기를 합니다. --opt옵션에 자동으로 포함이 됩니다.

 

--skip-opt : 기본으로 들어가는 옵션들을 모두 제거합니다. 원하는 옵션들만 넣을시 이용합니다.

 

+ 트랜잭션옵션

 --add-locks

각 테이블 덤프를 LOCK TABLES 및 UNLOCK TABLES 문으로 둘러싸세요. 덤프 파일을 다시로드 할 때 삽입 속도가 빨라집니다. 8.2.4.1 절.“INSERT 문 최적화”를 참조하세요.

 

--flush-logs :

덤프를 시작하기 전에 MySQL 서버 로그 파일을 비웁니다. 이 옵션에는 RELOAD 권한이 필요합니다. 이 옵션을 --all-databases 옵션과 함께 사용하면 덤프 된 각 데이터베이스에 대해 로그가 플러시됩니다. --lock-all-tables, --master-data 또는 --single-transaction을 사용하는 경우는 예외입니다. 이 경우 모든 테이블이 FLUSH TABLES WITH에 의해 잠긴 순간에 해당하는 로그는 한 번만 플러시됩니다. 

덤프와 로그 플러시를 정확히 동시에 수행하려면 --lock-all-tables, --master-data 혹은 --single-transaction과 함께 --flush-logs를 사용해야합니다. 또한 Binary log를 증분 덤프용으로 사용하는 경우, 풀덤프와 증분 덤프의 구분을 명확히 할 수 있습니다.

 

--flush-privileges

mysql 데이터베이스를 덤프 한 후 FLUSH PRIVILEGES 문을 덤프에 추가합니다. 이 옵션은 덤프에 mysql 데이터베이스와 적절한 복원을 위해 mysql 데이터베이스의 데이터에 의존하는 다른 데이터베이스가 들어있을 때마다 사용해야합니다.

 

--lock-all-tables, -x

모든 데이터베이스에서 모든 테이블을 잠급니다. 이는 전체 덤프 기간 동안 글로벌 읽기 잠금을 획득함으로써 달성됩니다. 이 옵션은 --single-transaction 및 --lock-tables를 자동으로 끕니다.

 

--lock-tables, -l : 

덤프 된 각 데이터베이스에 대해 덤프하기 전에 덤프 할 모든 테이블을 잠금니다. MyISAM 테이블의 경우 동시 삽입이 가능하도록 READ LOCAL로 테이블이 잠 깁니다. InnoDB와 같은 트랜잭션 테이블의 경우 --single-transaction은 테이블을 전혀 잠글 필요가 없기 때문에 --lock-tables보다 훨씬 좋은 옵션입니다.

 

--lock-tables는 각 데이터베이스에 대한 테이블을 개별적으로 잠그기 때문에 이 옵션은 덤프 파일의 테이블이 데이터베이스간에 논리적으로 일관성을 보장하지 않습니다. 다른 데이터베이스의 테이블은 완전히 다른 상태로 덤프 될 수 있습니다.

 

--opt와 같은 일부 옵션은 자동으로 --lock-tables를 활성화합니다. 이를 무시하려면 옵션 목록 끝에 --skip-lock-tables를 사용하십시오.

 

 --no-autocommit

각 덤프 된 테이블에 대한 INSERT 문을 SET autocommit = 0 및 COMMIT 문으로 묶습니다.

 

 --order-by-primary

각 테이블의 행을 기본 키 또는 해당 인덱스가 존재하는 경우 첫 번째 고유 인덱스로 정렬하여 덤프합니다. 이는 MyISAM 테이블을 덤프하여 InnoDB 테이블에로드 할 때 유용하지만 덤프 작업이 상당히 오래 걸립니다.

 

 --shared-memory-base-name=name

Windows에서는 공유 메모리를 사용하여 로컬 서버에 연결하는 데 사용할 공유 메모리 이름입니다. 기본값은 MYSQL입니다. 공유 메모리 이름은 대소 문자를 구분합니다.

 

이 옵션은 공유 메모리 연결을 지원하기 위해 shared_memory 시스템 변수를 사용하여 서버를 시작한 경우에만 적용됩니다.

 

--single-transaction

이 옵션은 트랜잭션 분리 모드를 REPEATABLE READ로 설정하고 데이터를 덤프하기 전에 START TRANSACTION SQL 문을 서버로 보냅니다. InnoDB와 같은 트랜잭션 테이블에서만 유용합니다. 왜냐하면 애플리케이션을 차단하지 않고 START TRANSACTION이 발행 될 때 데이터베이스의 일관성있는 상태를 덤프하기 때문입니다.

 

이 옵션을 사용할 때 InnoDB 테이블 만 일관성있는 상태로 덤프됩니다. 예를 들어이 옵션을 사용하는 동안 덤프 된 MyISAM 또는 MEMORY 테이블은 여전히 ​​상태를 변경할 수 있습니다.

 

--single-transaction 덤프가 처리되는 동안 유효한 덤프 파일 (올바른 테이블 내용 및 이진 로그 좌표)을 보장하기 위해 다른 연결에서는 ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE 문을 사용하지 않아야합니다. 표. 일관성있는 읽기는 해당 명령문과 분리되지 않으므로 덤프 할 테이블에서 명령문을 사용하면 mysqldump가 수행하는 SELECT가 테이블 컨텐츠를 검색하여 잘못된 컨텐츠를 얻거나 실패 할 수 있습니다.

 

LOCK TABLES로 인해 보류중인 트랜잭션이 암시 적으로 커밋되므로 --single-transaction 옵션과 --lock-tables 옵션은 상호 배타적입니다.

 

큰 테이블을 덤프하려면 --single-transaction 옵션을 --quick 옵션과 결합하세요.

 

+ 옵션그룹

--opt :

빠른 덤프 작업을 수행하기 위해 함께 작동하는 여러 설정을 켭니다. --opt가 기본적으로 켜져 있기 때문에 이러한 모든 설정은 기본적으로 켜져 있습니다. 따라서 --opt를 지정하는 경우는 거의 없습니다. 대신 명령 줄에서 나중에 관련 옵션을 지정하여 선택적으로 특정 설정을 다시 활성화하는 --skip-opt를 지정하여 이러한 설정을 그룹으로 해제 할 수 있습니다.

 

--compact :

선택적 명령문 및 주석이 출력에 표시되는지 여부를 제어하는 ​​여러 설정을 끕니다. 다시이 옵션 다음에 특정 설정을 다시 활성화하는 다른 옵션을 사용하거나 --skip-compact 양식을 사용하여 모든 설정을 켤 수 있습니다.

 

-- 옵션그룹 정리

그룹 옵션의 효과를 선택적으로 활성화하거나 비활성화하면 옵션이 처음부터 끝까지 처리되므로 순서가 중요합니다. 예를 들어 --disable-keys --lock-tables --skip-opt는 의도 한 효과가 없습니다. 자체적으로 --skip-opt와 동일합니다.

 

 

 

■ 정리

보시는 바와 같이 참 많은 옵션들이 있습니다. 이 옵션들은 특정한 상황 외에는 별로 쓰지 않으며 몇몇 옵션만을 주로 사용하게 될 것입니다. 또한 fields로 시작하는 옵션은 이기종 데이터베이스간 마이그레이션이나 특수한 상황이 아닌이상 쓰일일이 그리 많지 않습니다.

혹시 덤프 수행중 무조건 들어가야 하는 옵션이 있다면 my.cnf에 별도 섹션을 만들어 옵션을 추가해 주시면 됩니다.

[mysqldmp]

옵션명=값

이렇게 해주시면 mysqldump를 수행할 때 기본적으로 이 옵션은 들어가게 되며 기타 추가적인 옵션을 mysqldump 명령어를 수행할 때 입력해 주시면 됩니다. 주로 ssl 연결을 사용한다면 ssl 옵션이라던지 로그를 반드시 출력해야 한다면 로그옵션등을 추가해 주시면 좋을 것 같습니다.

 

■ 사용방법 :

# 참고사항

위에서 옵션설명에서 말했듯이 기본적으로 --opt 옵션 즉, --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset이 기본적으로 들어가게 됩니다. 만약 내 마음대로 옵션을 지정하고 싶다면 --skip-opt를 지정하고 난 후 내가 원하는 옵션들을 추가해 주시면 됩니다. 

 

+ 덤프방법 예제들

- 모든 데이터베이스를 덤프받는 방법

shell> mysqldump --user=root --password --single-transaction --all-databases > all_databases.sql

이 옵션을 명시하면 현재 서버의 모든 데이터베이스를 덤프하게 됩니다. 대신 덤프 용량이 커질 수 있습니다.

참고로 시스템 스키마인 information_schema와 performance_schema는 백업이 안되며 mysql의 정보만 백업이 됩니다.
user의 계정정보도 백업이 됩니다.

 

- 특정 데이터베이스만 받는 방법

shell > mysqldump --user=root --password --single-transaction --databases database1 database2 > each_databases.sql

 --databases옵션뒤에 원하는 데이터베이스명을 입력하면 지정된 데이터베이스만 덤프하게 됩니다. 띄워쓰기로 DB명을 하나씩 입력합니다.

 

- 특정 데이터 베이스의 테이블만을 덤프받는 방법

shell> mysqldump --user=root --password --single-transaction db_name table_name1 table_name2 > specify_database_tables.sql

별도의 옵션없이 mysqldump 명령의 마지막에 여러개의 오브젝트명을 명시하면, mysqldump는 첫번째 오브젝트는 데이터베이스명, 그 다음부터는 테이블명으로 인식하게 됩니다. 그래서 해당 데이터베이스에 있는 각 테이블들만을 덤프하게 됩니다.

 

- MySQL 테이블 & 루틴 전부 덤프

shell > mysqldump --user=root --password --host hostname --hex-blob --routines --triggers --databases database1 database2 > specify_database_objects.sql

기본적으로 데이터베이스와 테이블만을 덤프받게 되는데 루틴과 트리거를 지정하면 모두 덤프받을 수 있습니다.

 

- MySQL의 루틴만 덤프

shell > mysqldump --user=root --password --host hostname --routines --no-create-info --no-data --no-create-db database_name > specify_routine.sql

루틴만 덤프받고 싶을 때 사용합니다.

 

- View의 덤프

shell > mysqldump --user=root --password --host hostname --single-transaction --routines --no-create-info --no-data --no-create-db database_name > specify_routine.sql

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

 

- 특정 조건인 데이타만 덤프

shell > mysqldump  --user=root --password  --host hostname --single-transaction --databases database_name --ignore-table=database1.table1  --ignore-table=database1.table2 --where='id>10000'

> mydump.sql

특정 조건을 가진 데이터만 덤프할 수 있습니다. where 조건을 주어 id가 10,000 이상인 데이타만 덤프합니다.물론 원하는 다른 조건으로 덤프도 가능합니다.

 

- 일관된 데이터 덤프 받기

mysqldump를 이용하여 일관된 데이터를 덤프받고자 한다면, MyISAM은 lock-tables 옵션을 InnoDB에서는 --single-transaction 옵션을 사용해야만 합니다.

InnoDB에서 single-transaction 옵션이 사용되면, 전체 table에 대한 lock을 걸지 않고 Transaction 기능을 이용하여 덤프를 하기 때문에 덤프중에도 DML 작업(insert, update, delete)이 가능합니다.

그렇지만 MyISAM같은 경우 Table 단위의 Lock이 발생합니다. 그래서 Lock이 걸리면 DML작업이 불가능 합니다.

MyISAM같은 경우는 일관된 데이터 덤프라기 보다는 백업의 성능을 높이기 위한 방법이라고 보는게 더 타당할것 같습니다.