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' "
