[IT 알아보기]

[EhostICT] mysql 백업 및 복구

이호스트ICT 2018. 7. 26. 11:57

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-databaseDROP DATABASE 구문 추가하여 create database 전에 기존 database 삭제(오류)기존 데이타가 삭제되니 주의
--add-drop-tableDROP TABLE 구문 추가하여 table 생성전 기존 table 삭제(tick)drop table 을 제외하려면 --skip-add-drop-table 옵션 사용
-l, --lock-tablesLock all tables for read.(tick)--skip-lock-tables 로 제외가능. 이 옵션보다는 아래의 --single-transaction 사용 추천
--single-transactionlock 을 걸지 않고도 dump 파일의 정합성 보장(오류)InnoDB 일때만 사용 가능
--ignore-table제외할 테이블명을 주면 dump에서 제외(오류)여러개의 테이블을 제외할 경우 테이블명마다 앞에 --ignore-table 옵션을 주어야 함. , 로 여러개의 테이블을 주어도 한 테이블만 처리
--no-create-dbCREATE DATABASE 구문 제외(오류)dump한 파일을 load 하려는 환경의 database 명이 다를 경우 유용함
--max_allowed_packet 서버와 주고 받을 최대 패킷 사이즈 설정1M첨부파일등이 DB 에 있을 경우 이 값을 크게 해야 한다. mysqldump: ‘max_allowed_packet’ 에러 처리 참고
--triggerstrigger 도 dump(tick)--skip-triggers 로 제외할 수 있음
--routinesstored 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