따봉 구걸남's 블로그

mysql 터미널에서 데이터를 dump & load 하는 방법 알아보자 

아래의 환경에서 테이블을 1개 dump & load 한다고 가정하자 
source db : dumpdb
source table : dumpt
source user : mydba

1. mysqldump

 # dump 
 
    mysqldump -u유저명 -p  -h 소스디비IP -P포트번호 디비명 테이블명 > 타겟파일명.sql
ex) mysqldump -umydba -p  -h 소스디비IP -P포트번호 --no-create-db --no-create-info --set-gtid-purged-OFF --single-transaction dumpdb dumpt > dumpt_bak.sql
덤프 범위 지정 옵션
--databases: 덤프할 데이터베이스들을 지정
--all-databases: 모든 데이터베이스를 덤프
--ignore-table: 특정 테이블을 덤프에서 제외

덤프 내용 제어 옵션
--no-create-info: CREATE TABLE 문을 포함하지 않음 (데이터만 덤프)
--no-data: 테이블 구조만 덤프하고 데이터는 포함하지 않음
--add-drop-database: 덤프 파일에 DROP DATABASE 문 포함
--add-drop-table: 덤프 파일에 DROP TABLE 문 포함

성능 및 일관성 관련 옵션
--single-transaction: InnoDB 테이블에 대해 일관된 덤프를 생성 (락 없이)
--quick (-q): 대용량 테이블 덤프 시 메모리 사용을 줄임
--opt: 여러 유용한 옵션들을 한 번에 활성화 (기본값)

출력 형식 관련 옵션
--extended-insert (-e): 다중 행 INSERT 문 사용 (로드 속도 향상)
--complete-insert: INSERT 문에 컬럼명 포함

특수 목적 옵션
--master-data: 복제 설정을 위한 CHANGE MASTER TO 문 포함
--where: 특정 조건에 맞는 데이터만 덤프
--set-gtid-purged=OFF 옵션의 의미와 용도
GTID 정보 제외: 이 옵션을 사용하면 덤프 파일에 GTID 정보를 포함시키지 않습니다
데이터 이동 시 유용: 서로 다른 GTID 환경 간에 데이터를 이동할 때 유용합니다
복제 토폴로지 유지: 기존의 복제 토폴로지를 유지하면서 테이블을 복사하거나 복구할 때 사용합니다
바이너리 로깅 영향: 이 옵션을 사용하면 덤프 파일을 다시 로드할 때 바이너리 로깅을 비활성화하는 명령문(SET @@SESSION.SQL_LOG_BIN = 0;)이 추가되지 않습니다

 # load 
 
1-1) 타겟디비에 접속 후 load

mysql -u유저명 -p  -h 타겟디비IP -P포트번호 타겟디비명 테이블명
source 타겟파일명.sql

1-2) 디비접속과 load 를 한줄로 실행 

mysql -u유저명 -p  -h 타겟디비IP -P포트번호 타겟디비명 테이블명 < 타겟파일명.sql

2. UTIL (mysqlsh) 사용

2-1) mysqlsh 에 붙어서 작업

 # dump

mysqlsh mysql://유저명@소스디비IP:포트
JS > util.dumpTables(schema, tables, outputUrl[, options])
 ex) util.dumpTables (" dumpdb", ["dumpt"], "/data01/work/dumpdir", {dataOnly: true, threads: 4})

* 한줄로 실행하기
mysqlsh -h 디비ip -P포트 -u 유저명 -p --log-file=/경로/파일명.log -e "util.dumpTable(...상동)"

 - 두번째 테이블 항목에 여러개를 추가하는 것이 가능
 - 멀티스레드로 dump 가능. default 는 1이다.

 # load

mysqlsh mysql://유저명@타겟디비IP:포트
JS > util.loadDump(url[, options]) 
 ex) util.loadDump('/data01/work/dumpdir', {schema:"dumpdb", threads: 4, ignoreExistingObjects: true})

 - load 시 thread 는 디폴트가 4로 생략해도 무방하다. 
 - 타겟에 이미 테이블이 존재한다면 ignoreExistingObjects 를 사용한다. dump시 dataOnly 를 사용하면 안써도됨

3. 정리..

mysqldump 는 결과파일을 사람이 편집도 가능한 sql문을 생성하는 로지컬 백업이다. 
mysqlsh 과는 호환이 되지 않는다. 

mysql쉘의 장점 : 

 - 멀티스레드로 병렬실행이 가능 
 - 파일압축
 - 진행률 확인가능 
 
백업을 지원하는 또 다른 유틸인 util.exportTable 의 경우는 CSV, TSV 등 다양한 텍스트 형식으로 백업이 가능하지만 
단일 테이블 작업만 가능하고, util.importTable 로 로드할수 있다. 


 출처 : https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html