MySQL - mysqldump

MySQL 백업 도구인 mysqldump 는 적은 양의 데이터를 백업할 때 가장 간편하게 사용할 수 있는 유틸리티 

 

 

필요한 권한 

SELECT - 백업 대상 테이블의 데이터를 조회하는데 필요한 권한 

SHOW VIEW - 뷰 객체를 백업할 때 뷰를 조회하기 위한 권한이 필요 

TRIGGER - 트리거 객체를 백업하기 위한 권한 

LOCK TALBES -  --single-transaction 옵션을 사용하지 않고 백업을 수행할 대 대상 테이블의 데이터가 변경되는 것을 막기 위해 테이블 단위의 잠금 권한이 필요 

FILE - mysqldump 명령의 결과로 백업 파일이 생성되고 이를 디스크에 저장하기 위한 권한이 필요 

CREATE - 백업된 파일로 복구할 때 필요한 권한으로, 백업 파일에 기록된 객체를 생성할 수 있는 권한이 필요 

ALTER DATABASE - 백업된 파일로 복구할 때 필요한 권한으로, 백업 파일에는 데이터베이스의 콜레이션을 변경하기 위한 ALTER DATABASE 구문이 포함되는 경우가 있는데, 이는 프로시저나 트리거를 백업할 때 해당 캐릭터 인코딩을 보존하는 데 사용 , 복구 시 해당 구문을 실행하기 위한 권한이 필요 

 

 

사용법과 옵션 

전체 데이터베이스 백업하기 

mysqldump --all-databases [기타 옵션 추가 ...] -u[유저명] -p[패스워드] > /data/backup.sql

특정 데이터베이스만 백업하기 

 하나 이상의 데이터베이스를 한번에 백업할 수 있는데 ,--databases 옵션 뒤에 testdb db1 db2 와 같이 빈칸으로 구분해서 백업할 데이터베이스명을 나열 

mysqldump --databases testdb [기타옵션추가..] -u[유저명] -p[패스워드] > /data/testdb_backup.sql

 

특정 테이블만 백업하기 

mysqldump --tables testdb table1 table2 [기타 옵션 추가..] -u[유저명] -p[패스워드] > /data/table1_tabl2_backup.sql

 

복원하기 

 mysqldump -u root -p 디비명 < 파일명.sql
 
 
 # 덤프파일 경로지정
 
 mysql -e "source /path/backup.sql" [DBNAME]

 

 

mysqldump 옵션 

접속에 사용되는 옵션 

 --user = [유저명] 또는 -u [유저명] 

백업 대상 MySQL 서버에 접속하기 위한 MySQL 서버 계정의 이름

 

--password=[패스워드] 또는 -p [패스워드]

백업 대상 서버에 접속하기 위한 MySQL 서버 계정의 암호

 

--host=[호스트이름]

 

--port=[포트번호]

 

--compress 

데이터 압축 백업 명령을 실행하는 클라이언트와 백업 대상 MySQL 서버가 모두 압축 기능을 지원한다면 클라이언트와 서버 간에 전송되는 모든 정보를 압축 

 

DDL 옵션

--add-drop-database

백업 결과 파일의 각 CREATE DATABASE 구문 이전에 DROP DATABASE 구문을 추가

 

--add-drop-table

백업 결과 파일의 각 CREATE TABLE 구문 이전에 DROP TABLE 구문을 추가 

 

--add-drop-trigger

백업 결과 파일의 각 CREATE TRIGGER 구문 이전에 DROP TRIGGER 구문을 추가 

 

--no-create-db 또는 -n 

백업 결과 파일에 CREATE DATABASE 구문이 포함되지 않게 하는 옵션 , 데이터만 복원하고자 할 때 해당 옵션 추가 

 

--no-create-info 또는 -t

백업 결과 파일에 CREATE TABLE 구문이 포함되지 않게 하는 옵션 ,이미 해당 테이블이 존재해서 데이터만 복원하고자 할 때 사용

 

파일 포맷 옵션

--tab=[경로]

백업 결과 파일을 탭 문자열로 나뉜 텍스트 형식의 파일로 생성 , 이 옵션을 사용하면 CREATE TABLE 구문은 '테이블이름.sql' 파일로 생성되고 해당 테이블의 데이터는 '테이블이름.txt' 파일로 생성된다.

txt파일의 데이터는 각 컬럼 값이 탭으로 구분되고 각 행 데이터는 한 줄씩 기록

 

백업 필터링 옵션

 --all-databases 

백업 대상 MySQL 서버의 모든 데이터베이스를 백업

 

--databases 

한 개 이상의 특정 데이터베이스를 지정해 백업할 때 사용하는 옵션

 

--events 

백업 대상 데이터베이스에 이벤트가 있다면 event 생성 구문을 백업 결과 파일에 함께 기록 

 

--ignore-table=데이터베이스 이름.테이블 이름

특정 테이블은 제외하고 백업할 때 사용하는 옵션, 제외할 테이블의 데이터베이스 이름과 테이블 이름을 명시하는 식으로 사용

 

--no-data 

백업 대상 객체를 생성하는 DDL 구문만 백업 결과 파일에 기록하고 백업 대상의 데이터 내용은 백업하지 않는다.

 

--routines 

백업 대상 데이터베이스가 스토어드 루틴를 포함한다면 해당 객체를 백업 파일에 기록 

 

--tables 

특정 테이블만 백업할 때 사용하는 옵션 , 이때 테이블의 데이터베이스 이름을 함께 붙여야한다.

 

--where='조건절' 또는 -w '조건절'

원하는 조건의 데이터만 백업할 때 사용하는 옵션 

성능 관련 옵션

 --quick 

백업 대상 데이터베이스에서 백업할 행 데이터 전체를 한 번에 읽어서 백업한다. 만일 이 옵션을 사용하지 않는다면 행데이터를 백업 파일에 기록하기 전에 메모리에 먼저 버퍼링하고 백업이 수행되므로 백업 소요 시간이 길어질 수 있다.

그러므로 이 옵션은 백업 대상 테이블 크기가 클 때 유용하게 사용.

 

 

트랜잭션 옵션 

 

--single-transaction 

이 옵션을 지정하면 백업 대상 MYSQL 서버에 백업을 위해 접속한 세션의 트랜잭션 격리 수준을 REPEATABLE READ 로 변경하고, 백업 시작 직전에 START TRANSACTION 명령을 실행한 뒤 백업을 진행한다. 

이 옵션을 이용하면 백업을 수행할 때 InnoDB 스토리지 엔진과 같이 트랜잭션을 지원하는 스토리지 엔진을 사용하는 테이블에 대해서는 START TRANSACTION 구문이 실행된 시점의 일관된 데이터를 백업할 수 있다.

 

이 옵션을 이용해 백업을 수행할 때는 다른 세션에서는 DDL 명령을 실행해서는 안된다. 

큰 테이블을 백업해야 한다면 --single-transaction 과 --quick 옵션을 함께 사용하는 것을 권장

 

장단점 

장점

1. 백업 결과 파일을 조회하면 백업된 테이블의 스키마 구조와 데이터를 SQL 구문으로 직접 확인할 수 있으며, 필요하다면 파일을 직접 수정해 복원에 사용할 수 있다.

 

2. mysqldump의 옵션을 적절히 사용한다면 사용자가 원하는 형식과 데이터만 백업할 수 있다. 

 

단점

1. SQL 구문을 실행해 데이터를 새로 적재하는 논리적인 백업 복원 방식은 시간이 오래 걸리는 단점이 있기 대문에 백업 대상 데이터의 크기가 크다면 데이터베이스의 실제 데이터 파일을 백업하는 물리적인 백업 방식을 권고. 

 

Reference

DBA를 위한 MySQL 운영 기술