이번시간에는 기존의 데이터베이스를 dump 할 경우 보다 효율적으로 처리하는 방법에 대해 알아보겠습니다.
Dump에는 import와 export 가 있습니다.
먼저 export에 대해 알아보겠습니다.
Export Dump file
MySQL의 특정 데이터베이스에 있는 내용을 Dump file 로 export 하기 위한 Command는 아래와 같습니다.
mysqldump -uroot -p --databases chat_db --no-autocommit=1 --single-transaction=1 --extended-insert=1 > C:\Users\{username}\java\dump.sql위 내용은 MySQL의 chat_db 데이터베이스에 있는 내용들을 dump.sql 파일로 변환한것이며, 파일 저장 위치는 'C:\Users{username}\java' 로 지정한 명령어 입니다.
여기서 주의깊게 볼 내용은 mysqldump 의 옵션입니다.
dump 옵션
--no-autocommit=1
auto commit 기능을 off 하며, 입력하려는 테이블의 입력이 모두 완료될 때 까지 모두 입력을 한 뒤 commit을 수행하는 내용입니다.
만약 작업 중 실패한다면 해당 내용은 commit이 되지 않았으므로 처음부터 다시 진행해야하는 번거로움은 있습니다.
하지만 대부분의 dump 데이터들은 그 양이 많을텐데 왜 이 옵션이 기본 설정값으로 autocommit인지 이해가 되지 않는부분입니다.
(필요한 경우에만 autocommit을 하게 하는게 더 올바른 정책이 아닐까 ?)
매번 INSERT 시 마다 커밋을 한다는 것은 비용이 매우 크므로 이 옵션은 필수라고 생각됩니다.
아래는 해당 옵션을 적용한 내용과 적용하지 않은 내용의 .sql 파일의 일부입니다.
적용 전
INSERT 시작 부분
INSERT 종료 부분
적용 후
INSERT 시작 부분
INSERT 종료 부분
다음 블로그에서 추가적으로 set autocommit=0 과 START TRANSACTION 에 대해서 알아보겠습니다.
--single-transaction=1
한 트랜잭션 내에서 덤프를 수행합니다. 이 옵션은 데이터베이스가 InnoDB 스토리지 엔진을 사용할 때 매우 유용합니다.
InnoDB의 경우, 덤프 중 데이터베이스가 변경되지 않도록 스냅샷을 생성해 일관성을 보장합니다.
LOCK TABLES 명령을 사용하지 않으므로, 데이터베이스를 덤프하는 동안 읽기-쓰기 작업이 가능하여 서비스 중단을 방지합니다.
(하지만 no autocommit과 함께 사용하는 것은 바람직하지 않습니다. 두 옵션이 충돌 할 수 있습니다.)
--extended-insert=1
INSERT INTO 'A' VALUES (1, 10);
INSERT INTO 'A' VALUES (2, 20);
를
INSERT INTO 'A' VALUES (1, 10), (2, 20), ...
로 변경하게 해줍니다.
--no-create-db
Dump 시 sql file을 보게되면 기존의 데이터베이스이름을 사용하도록 되어있습니다.
sql파일은 아래와 같이 시작합니다.
CREATE DATABASE {origin database name}
USE {origin database name}이렇게 하면 쿼리 실행 수가 줄어들어 덤프 파일의 크기가 작아지고, 덤프 파일을 불러올 때도 성능이 향상됩니다.
이 옵션은 덤프 파일의 속도와 효율성을 개선하는 데 매우 유용합니다.
이는 --no-create-db 의 기본 설정값이 false 이기 때문입니다.
그래서 만약 데이터베이스 이름을 변경해주고싶을 경우 sql file내의 database name 을 원하는 이름으로 변경해주셔도 됩니다.
추가적으로 위에 적힌 --no-database-db 를 사용하게 되면 CREATE DATABASE 명령어가 없어지게 됩니다.
최적화 관점에서 옵션 설명
--extended-insert=1: 가장 효율적인 옵션 중 하나로, 덤프 파일의 크기를 줄이고, 나중에 덤프 파일을 불러올 때 성능을 크게 개선합니다.
--no-autocommit=1: 대용량 데이터 처리에서 디스크 I/O를 줄이는 데 유리합니다. 각 INSERT 후에 자동으로 커밋되지 않고 트랜잭션이 완료될 때 한 번에 커밋되므로 성능을 최적화합니다. 그러나 --single-transaction과는 충돌할 수 있으므로 이 두 옵션을 함께 사용할 때는 주의해야 합니다.
--single-transaction=1: 덤프 중에 데이터베이스에 대한 읽기/쓰기가 동시에 가능한 점에서 매우 유용합니다. 데이터베이스 잠금 없이 일관성 있는 스냅샷을 생성합니다.
-d / --no-data
-d 옵션을 사용하게 되면 INSERT 쿼리 없이 데이터 스키마만 dump할 수 있습니다.
mysqldump -u DB사용자명 -p -d DB명 > 파일명
mysqldump -uroot -p --databases chat_db -d > C:\Users\{username}\java\dump.sqlImport Dump file
C:\Users\{username}\java 에 testdb.sql 파일이 존재하고 이 내용을 MySQL의 testdb에 반영을 원할 경우 아래의 명령어로 import 를 수행할 수 있습니다.
사실 해당 sql 파일을 실행하는 명령어입니다.
$> mysql -u[사용자아이디] -p[패스워드] 데이터베이스명 < 경로 및 덤프 파일명.sql
// ex) mysql -uroot -p testdb < C:\Users\{username}\java\testdb.sql참고
https://dev.mysql.com/doc/refman/8.4/en/optimizing-innodb-bulk-data-loading.html
https://sunkyu.tistory.com/72055
https://cheershennah.tistory.com/173
