MySQL DB 백업 및 load 하기 (mysqldump 이용)

MySQL DB를 사용하다 보면 간간히 DB 백업이 필요할 때가 발생합니다.

Test 용도에서의 사용 뿐 아니라 Production으로 사용중인 DB라면 더욱 더 자주 백업이 필요하게 됩니다.

이때를 위해 MySQL DB 백업 방법을 간단히 정리하였습니다.

 

---

1. 명령어

 

mysqldump [옵션] DB명(Table명) > /디렉터리/덤프파일명

 

- 전체 DB 덤프

$ mysqldump -h localhost -u root -p패스워드 --all-databases > /디렉터리/덤프파일명

 

- 특정 DB 덤프

$ mysqldump -h localhost -u root -p패스워드 --databases DB명 > /디렉터리/덤프파일명

2. 옵션

 

옵션	의미	Default	비고
-n, --no-create-db	CREATE DATABASE 구문 제외	X	dump한 파일을 load 하려는 환경의 database 명이 다를 경우 유용함
-t, --no-create-info	CREATE TABLE 구문제외	X	dump한 이후에 load하려는 환경의 table 스키마가 추가/변경 되었을 경우 유용함
--add-drop-database	DROP DATABASE 구문을 추가하여 create database 전에 기존 database 삭제	X	기존 데이터가 삭제됨으로 주의해서 사용
--add-drop-table	DROP TABLE 구문을 추가하여 table 생성 전에 기존 table 삭제	O	DROP TABLE을 제외하려면 --skip-add-drop-table 옵션 사용
--databases	dump할 DB명을 기술하면 해당 DB만 dump		--databases 옵션이 주어질 경우 CREATE DATABASE와 USE 구문이 덤프 파일의 첫 부분에 추가됨.
-c, --complete-insert	INSER INTO 구문 생성시 컬럼 이름도 포함	X	dump한 이후에 load하려는 환경의 table 스키마가 추가/변경되었을 경우 유용 (Column count doesn't match value count 에러 발생시)
-l, --lock-tables	모든 table에 대해 READ LOCK 이 걸린다.	O	--skip-lock-tables 로 제외 가능. 이 옵션보다는 아래의 --single-transaction 사용을 추천
--single-transaction	LOCK을 걸지 않고도 dump 파일의 정합성을 보장	X	InnoDB 일때만 사용 가능
--routines	Stored procedeure와 function도 dump	X	MySQL 5는 trigger는 기본적으로 덤프하나 function, procedure는 덤프하지 않음
--triggers	trigger도 dump	O	--skip-triggers로 제외할 수 있음
-A, --all-databases	모든 DB dump
--ignore-table	제외할 테이블명을 주면 dump에서 제외	X	여러개의 테이블을 제외할 경우 테이블 명마다 앞에 --ignore-table 옵션을 주어야한다. ,로 여러개의 테이블을 주어도 한 테이블만 처리

3. 실행 예시

 

- testdb1 과 testdb2만 백업

 $ mysqldump --single-transaction --databases testdb1, testdb2 -h localhost -u root -pmysqlpw > mysql_dump.sql

 

- CREATE DATABASE 구문 미포함 + INSERT에 컬럼명 추가

 

$ mysqldump --single-transaction --no-create-db --no-create-info --complete-insert -h localhost -u root -pmysqlpw > mysql_dump.sql

* 컬럼명을 포함할 경우 primary key가 auto increment 일 경우 아래 에러가 발생하고 INSERT 실패함. 이럴 경우 mysqldump로는 방법이 없고 별도의 VIEW를 만들고 VIEW를 export 하거나 SELECT INTO 구문을 사용해야 함.
" ERROR 1062 (23000) at line 445: Duplicate entry '1' for key 'PRIMARY' "