MySQL mysqldump로 full backup, DB table 백업, 복원 (중요:대용량 복원은 MyISAM엔진으로 변경하세요)

Mysql의 전체 DB를 백업 복원이 간단하게 할 수 있습니다.

백업은 mysqldump명령으로 하고, 복원은 mysql을 이용합니다.

여기서, sql Dump를 받는 속도는 Engine의 종류에 거의 차이가 없습니다.

그러나, dump받은 sql를 복원할때, 

대용량 Data처리시 DB Engine Type에 따라 상당한 속도 차이가 크게 납니다.

대용량 DB backup ->Restore작업시 DB table Engine 타입을 MyISAM으로 주시길 바랍니다.

InnoDB 장점(?)들 때문에, 대용량 Data Insert 속도가 MyISAM 보다 엄청나게 느립니다.

실제 예로 600,000 rows(Disk size:190M정도)의 데이터를 

sql 복원할 때, InnoDB의 경우 5시간 넘게 작업을 해도 끝나지 않습니다.

이 경우, DB 테이블의 Engine를 MyISAM으로 변경하면, 10분도 안되서 복원이 완료 됩니다.

(마지막 부분에 MyISAM과 InnoDB의 차이를 기록해 두겠습니다.)

(백업) Export:

mysqldump -u root -p --all-databases > alldb.sql

Look up the documentation for mysqldump. You may want to use some of the options mentioned in comments:

mysqldump -u root -p --opt --all-databases > alldb.sql
mysqldump -u root -p --all-databases --skip-lock-tables > alldb.sql

(복원) Import:

mysql -u root -p < alldb.sql

1. 덤프 뜨기

$> mysqldump -u[사용자아이디] -p 데이터베이스명 [테이블명] > 저장될 파일명 

(예: mysqldump -uroot -p mydb t1 t2 t3  > mydb_dump.sql )
DB명=mydb, 테이블명=t1, t2, t3 

2. 복구하기 

$> mysql -u[사용자아이디] -p [디비명] < 덤프파일명 

(예: mysql -uroo -p mydb < mydb_dump.sql)
DB명이 mydb인 곳에 백업된 데이터를 sql명령으로 복원

sql 덤프 파일을 가지고 복구를 하는 방법도 간단합니다.

대용량 복원시, 위의 설명처럼  주의 사항으로 DB Engine Type를 잘 확인하시기 바랍니다.

---

출처:  Export and Import all MySQL databases at one time.

참고: MySQL Dump 뜨는 방법 및 복구 방법

InnoDB vs MyISAM 차이점

DB의 Engine는 테이블로 InnoDB와 MyISAM을 줄 수있습니다.

실질적으로 InnoDB 또는 MyISAM 엔진를 사용할지의 선택의 고민중에서

단순한 형태, 연관 관계(relationship)가 복잡하지 않고, 

Row Level의 트랜젝센 관리가 필요 없다면

MyISAM이 속도면에서 빠릅니다.

추가로 대용량의 Dump된 파일을 복원할 때 엔진타입이 InnoDB이면, 

너무 속도가 느려서 작업이 불가능할 정도입니다.

이경우 DB table의 엔진 타입을 일시적으로 MyISAM으로 변경해주시면 작업 속도에 유용합니다.

참고로 DB table 엔진 변경하는 명령들

Convert A Single Table To InnoDB

mysql -Bse ‘ALTER TABLE database_name.table_name ENGINE=InnoDB;’

 

Convert A Single Table To MyISAM:

mysql -Bse ‘ALTER TABLE database_name.table_name ENGINE=MyISAM;’

(참고: https://www.liquidweb.com/kb/mysql-performance-myisam-vs-innodb/ )

마지막으로 

DB table의 정보가 변경이 자주 되지 않는다면, MyISAM방식의 엔진이 속도가 더 빠릅니다.

DB table의 정보가 자주 변경된다면, InnoDB방식의 엔진이 속도가 더 빠릅니다.