[EhostICT] mysql 백업 및 복구

1. 기본 백업 command 사용법 

mysqldump -u 사용자 -p database명 > database명.sql

2. 주요 옵션

옵션	의미	Default	비고
-A, --all-databases	모든 db dump
--databases	dump 할 db 명 기술하면 해당 db 만 dump		--database 옵션이 주어질 경우 CREATE DATABASE 과 USE 구문이 덤프 파일의 첫 부분에 추가됨.
--add-drop-database	DROP DATABASE 구문 추가하여 create database 전에 기존 database 삭제		기존 데이타가 삭제되니 주의
--add-drop-table	DROP TABLE 구문 추가하여 table 생성전 기존 table 삭제		drop table 을 제외하려면 --skip-add-drop-table 옵션 사용
-l, --lock-tables	Lock all tables for read.		--skip-lock-tables 로 제외가능. 이 옵션보다는 아래의 --single-transaction 사용 추천
--single-transaction	lock 을 걸지 않고도 dump 파일의 정합성 보장		InnoDB 일때만 사용 가능
--ignore-table	제외할 테이블명을 주면 dump에서 제외		여러개의 테이블을 제외할 경우 테이블명마다 앞에 --ignore-table 옵션을 주어야 함. , 로 여러개의 테이블을 주어도 한 테이블만 처리
--no-create-db	CREATE DATABASE 구문 제외		dump한 파일을 load 하려는 환경의 database 명이 다를 경우 유용함
--max_allowed_packet	서버와 주고 받을 최대 패킷 사이즈 설정	1M	첨부파일등이 DB 에 있을 경우 이 값을 크게 해야 한다. mysqldump: ‘max_allowed_packet’ 에러 처리 참고
--triggers	trigger 도 dump		--skip-triggers 로 제외할 수 있음
--routines	stored procedure 와 function 도 dump		MySQL 5 는 trigger는 기본적으로 덤프하나 function, procedure는 덤프하지 않음

 

 

3. 사용예

 모든 디비 및 tored procedure 와 function dump

 mysqldump --single-transaction –-routines --all-databases -h localhost -u root -pmypwd > mydump.sql

 

db1, db2 만 백업

mysqldump  --single-transaction --databases db1 db2 -h localhost -u root -pmypwd > mydump.sql

 

db1만 백업하고 create, use 구문 미포함

mysqldump  --single-transaction db1 -h localhost -u root -pmypwd > mydump.sql

 

db1만 포함하고 db내 table1, table2만 제외

 mysqldump  --single-transaction --databases db1 --ignore-table=db1.table1  --ignore-table=db1.table2 -h localhost -u root -pmypwd > mydump.sql

 

데이터는 제외하고 디비 구조만 백업

mysqldump  --single-transaction db1 -h localhost -u root -pmypwd -d > mydump.sql

복구

mysql -u root -pmypwd db2 <  mydump.sql

 

4. 참고

 mysql dump 파일에 insert 라인별 분리를 원한다면

/etc/my.cnf 에  

[mysqldump]

quick

quote-names

extended-insert