■ mysqlsh를 이용한 데이터 덤프 및 엑스포트 방법
테이블 엑스포트 및 덤프 방법은 util.exportTable 및 util.importTable을 이용하여 하는 방법이 있습니다.
참고로 util.dumpTable(), util.dumpTable(), util.exportTable() 및 util.loadDump을 이용하는 방법이 더 최근의 방법인데 이 방법은 다음에 바로 알아보도록 하겠습니다.
먼저 위의 2가지를 이용하여 테이블 단위로 덤프 및 임포트를 하는 방법에 대해 알아보겠습니다.
■ util.exportTable, util.importTable을 이용하여 데이터 엑스포트 및 임포트를 할시 장점
select... into outfile로 엑스포트 하는것보다 속도가 훨씬 더 빠릅니다.
마찬가지로 임포트시 스레드를 몇개 만들어 병렬로 임포트하기 때문에 임포트또한 훨씬 빠릅니다.
전체 데이터베이스 레벨이 아닌 테이블 단위의 레벨 백업 및 임포트일 경우 월등히 빠릅니다.
■ 테스트 환경
참고로 모든 데이터 및 덤프는 sysbench에서 생성한 sbtest로 테스트합니다. 사용방법은 다음 포스트의 맨아래 벤치마크 방법을 참고해서 생성합니다.
https://myinfrabox.tistory.com/65
[Performance] Sysbench 설치 및 사용방법
■ SysBench 히스토리 및 아키텍처 sysbench는 2004 년 Peter Zaitsev에 의해 만들어졌고 곧 Alexey Kopytov가 개발을 인수했습니다. 버전 0.4.12에서 개발이 중단되었습니다. Alexey는 2016년에 다시 작업하기 시작
myinfrabox.tistory.com
■ 사용방법 및 옵션
▶︎ util.exportTable
기본적으로 테이블 데이터 엑스포트 유틸리티는 MySQL Shell의 병렬 테이블 가져오기 유틸리티에 대한 기본 형식의 데이터 파일을 생성합니다. 미리 설정된 옵션을 사용하여 DOS 또는 UNIX 시스템용 CSV 파일과 TSV 파일을 엑스포트 할 수 있습니다. 테이블 엑스포트 유틸리티는 JSON 데이터를 생성할 수 없습니다. SELECT...INTO OUTFILE 문에 대해 필드 및 라인 처리 옵션을 설정하여 임의 형식의 데이터 파일을 생성할 수도 있습니다.
util.exportTable은 파티션된 테이블과 하위 파티션된 테이블과 함께 사용할 수 있지만 이러한 테이블을 특별하게 처리하지는 않습니다. 이 유틸리티는 릴리스 버전에 관계없이 항상 테이블당 하나의 파일을 만듭니다.
• 기본 사용방법
mysqlsh JS > util.exportTable(table, outputUrl[, options])
mysqlsh JS > util.exportTable("hr.employees", "file:///home/hanna/exports/employees.txt")
• 옵션
1. where
특정 필터값으로 엑스포트를 하고싶을 때 사용
util.exportTable("sakila.actor", "dump.csv", {"where" : "actor_id > 150"})
2. partition
테이블이 파티셔닝되어 있다면 특정 파티션만을 선택하여 엑스포트 가능
util.exportTable("schema.table", "dump.csv", {"partitions" : ["p1", "p2"]}
- 엑스포트 파일 형식(dialect)
default|csv|csv-unix|tsv 형식으로 엑스포트를 합니다. 또한 지정된 형식에 따라 내보낸 데이터 파일의 형식에 대한 필드 및 줄 처리 옵션 세트를 지정합니다. 설정을 변경하기 위해 lineTerminatingBy, fieldsTerminatingBy, fieldsEnclosedBy, fieldsOptionallyEnclosed 및 fieldsEscapedBy 옵션 중 하나 이상을 지정하여 선택한 dialect를 추가 사용자 정의를 위한 기반으로 사용할 수도 있습니다.
기본 엑스포트 옵션은 해당 문의 기본 설정과 함께 SELECT...INTO OUTFILE 문을 사용하여 생성되는 것과 같은 데이터 파일을 생성합니다. DOS 또는 UNIX 시스템용 CSV 파일(.csv) 및 TSV 파일(.tsv)을 내보내는 데 다른 옵션을 사용할 수 있습니다.
각 dialect에 적용되는 옵션은 다음과 같습니다.
| dialect | linesTerminatedBy | fieldsTerminatedBy | fieldsEnclosedBy | fieldsOptionallyEnclosed | fieldsEscapedBy |
| default | [LF] | [TAB] | [empty] | false | \ |
| csv | [CR][LF] | , | ' ' | ture | \ |
| csv-unix | [LF] | , | ' ' | false | \ |
| tsv | [CR][LF] | [TAB] | '' | true | \ |
3. linesTerminatedBy
덤프 파일의 각 행을 종료하는 데 사용되는 하나 이상의 문자(또는 빈 문자열)입니다. 옵션 내용을 지정하지 않은경우 줄 바꿈 문자(\n)입니다.
이 옵션은 SELECT...INTO OUTFILE 문의 LINES TERMINATED BY 옵션과 동일합니다.
이 유틸리티는 빈 문자열로 설정된 SELECT...INTO OUTFILE 문의 LINES STARTING BY 옵션에 해당하는 옵션을 제공하지 않습니다.
4. fieldsTerminatedBy
덤프 파일의 각 필드를 종료하는 하나 이상의 문자(또는 빈 문자열)입니다. 옵션 내용을 지정하지 않은 경우 탭 문자(\t)입니다.
이 옵션은 SELECT...INTO OUTFILE 문의 FIELDS TERMINATED BY 옵션과 동일합니다.
5. fieldsEnclosedBy
덤프 파일의 각 필드를 묶는 단일 문자(또는 빈 문자열)입니다. 옵션 내용을 지정하지 않은경우 빈 문자열입니다.
이 옵션은 SELECT...INTO OUTFILE 문의 FIELDS ENCLOSED BY 옵션과 동일합니다.
6. fieldsOptionallyEnclosed
fieldsEnclosedBy에 지정된 문자가 덤프 파일의 모든 필드를 묶는 것인지(false), 아니면 CHAR, BINARY, TEXT 또는 ENUM과 같은 문자열 데이터 유형이 있는 경우에만 필드를 묶는 것인지(true) 알려주는 옵션 입니다.
지정하지 않은 경우 false입니다. 이 옵션을 사용하면 fieldsEnclosedBy 옵션이 SELECT...INTO OUTFILE 문의 FIELDS OPTIONALLY ENCLOSED BY 옵션과 동일해집니다.
7. fieldsEscapedBy
덤프 파일에서 이스케이프 시퀀스를 시작하는 문자입니다. 옵션 내용을 지정하지 않는 경우 백슬래시(\)입니다. 이 옵션은 SELECT...INTO OUTFILE 문의 FIELDS ESCAPED BY 옵션과 동일합니다.
이 옵션을 빈 문자열로 설정하면 문자가 이스케이프되지 않습니다. 이는 SELECT...INTO OUTFILE에서 사용하는 특수 문자를 이스케이프해야 하기 때문에 권장되지 않습니다.
• 기타 다른 옵션들
1. maxRate
내보내기 중 데이터 읽기 처리량에 대한 스레드당 초당 최대 바이트 수입니다. 단위 접미사 k는 킬로바이트, M은 메가바이트, G는 기가바이트를 사용할 수 있습니다.
예를 들어 100M을 설정하면 처리량이 스레드당 초당 100MB로 제한됩니다. 0(기본값)을 설정하거나 옵션을 빈 문자열로 설정하면 제한이 설정되지 않음을 의미합니다.
2. showProgress
내보내기 진행률 정보를 표시(true)하거나 숨깁니다(false). MySQL Shell이 대화형 모드일 때와 같이 stdout이 터미널(tty)인 경우 기본값은 true이고, 그렇지 않은 경우 false입니다.
진행률 정보에는 내보낼 예상 총 행 수, 지금까지 내보낸 행 수, 완료율, 초당 행 및 바이트 단위의 처리량이 포함됩니다.
3. compression
내보낸 데이터 파일을 쓸 때 사용할 압축 유형입니다. 기본값은 압축 없음(none)입니다. 압축 옵션 사용시 gzip 압축(gzip) 또는 zstd 압축(zstd)을 사용합니다.
4. defaultCharacterSet
엑스포트를 위해 MySQL Shell에서 서버로 열리는 세션 연결 중에 사용되는 문자 집합입니다. 기본값은 utf8mb4입니다.
시스템 변수 Character_set_client, Character_set_connection 및 Character_set_results의 세션 값은 각 연결에 대해 이 값으로 설정됩니다.
문자 세트는 Character_set_client 시스템 변수에서 허용되고 MySQL 인스턴스에서 지원되어야 합니다.
■ 실 사용방법 및 결과
▶︎ 엑스포트 테스트
• 특정 테이블 엑스포트
mysqlsh-js > util.exportTable("sbtest.sbtest1", "file:///root/sbtest1.sql");
Initializing - done
Gathering information - done
Running data dump using 1 thread.
NOTE: Progress information uses estimated values and may not be accurate.
Starting data dump
104% (2.50M rows / ~2.40M rows), 75.34K rows/s, 14.77 MB/s
Dump duration: 00:00:33s
Total duration: 00:00:33s
Data size: 488.82 MB
Rows written: 2500000
Bytes written: 488.82 MB
Average throughput: 14.66 MB/s
The dump can be loaded using:
util.importTable("file:///mysql/mysqldump/sbtest1.sql", {
"characterSet": "utf8mb4",
"schema": "sbtest",
"table": "sbtest1"
})
• 데이터 확인
shell> tail -10 sbtest1.sql
2499991 1247178 09967604588-51213133811-81561030498-14424725886-62571802089-58488935831-80474643867-02192277940-07095554446-57367488382 06622669710-68704761219-08983260158-09415958829-20513820394
2499992 1122960 33265450122-07227243191-76670346502-91145543802-05598573032-70704153663-53045596113-24396134776-52485043202-56253563999 20135390212-85060974542-08457349099-27637852224-15231799359
2499993 1059286 82251962612-45134470681-67953251491-62061045049-35343670700-60389469777-31468448014-97568234590-43956181685-07194045752 34916033120-64452774427-13693348191-32300389189-97950362112
2499994 1256247 50391802437-36070676809-15145304074-33579088803-84520957076-95261308093-00803597917-33902983028-35274959389-59671605353 89716661821-99994072789-47206723821-27928206140-12958482314
2499995 1247373 43742906773-18622471409-11960133352-85908962999-73092789091-12543483925-20497860694-25626934615-66190853952-31316557189 26419802985-75236465202-52609140444-42709063968-14683858221select... into outfile 형식으로 엑스포트 됨.
▶︎ 옵션 사용해서 사용방법
• 특정 테이블 엑스포트
mysqlsh-js > util.exportTable("sbtest.sbtest2", "file:///root/sbtest1.csv", {defaultCharacterSet: "utf8", showProgress: true, fieldsOptionallyEnclosed: true, fieldsTerminatedBy: ",", linesTerminatedBy: "\n", fieldsEnclosedBy: '"', defaultCharacterSet: "utf8", showProgress: true, dialect: "csv"})
Initializing - done
Gathering information - done
Running data dump using 1 thread.
NOTE: Progress information uses estimated values and may not be accurate.
Starting data dump
104% (2.50M rows / ~2.40M rows), 75.66K rows/s, 15.14 MB/s
Dump duration: 00:00:33s
Total duration: 00:00:33s
Data size: 498.82 MB
Rows written: 2500000
Bytes written: 498.82 MB
Average throughput: 15.07 MB/s
The dump can be loaded using:
util.importTable("file:///root/sbtest1.csv", {
"characterSet": "utf8",
"dialect": "csv",
"fieldsEnclosedBy": "\"",
"fieldsOptionallyEnclosed": true,
"fieldsTerminatedBy": ",",
"linesTerminatedBy": "\n",
"schema": "sbtest",
"table": "sbtest2"
})
• processlist 확인
mysqlsh-SQL > show processlist;
+-------+-----------------+---------------------+------+------------------+--------+-----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+-----------------+---------------------+------+------------------+--------+-----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
.....
| 16370 | root | localhost | NULL | Query | 0 | init | show processlist |
| 16372 | root | localhost | NULL | Sleep | 4 | | NULL |
| 16373 | root | localhost | NULL | Query | 4 | executing | SELECT SQL_NO_CACHE `id`,`k`,`c`,`pad` FROM `sbtest`.`sbtest4` ORDER BY `id` /* mysqlsh exportTable, |
+-------+-----------------+---------------------+------+------------------+--------+-----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
• 데이터 확인
shell > more sbtest1.csv
1,1248209,"83868641912-28773972837-60736120486-75162659906-27563526494-20381887404-41576422241-93426793964-56405065102-33518432330","67847967377-48000963322-62604785301-91415491898-96926520291"
2,1254922,"38014276128-25250245652-62722561801-27818678124-24890218270-18312424692-92565570600-36243745486-21199862476-38576014630","23183251411-36241541236-31706421314-92007079971-60663066966"
3,1260652,"33973744704-80540844748-72700647445-87330233173-87249600839-07301471459-22846777364-58808996678-64607045326-48799346817","38615512647-91458489257-90681424432-95014675832-60408598704"
4,1255034,"37002370280-58842166667-00026392672-77506866252-09658311935-56926959306-83464667271-94685475868-28264244556-14550208498","63947013338-98809887124-59806726763-79831528812-45582457048"
5,1249531,"44257470806-17967007152-32809666989-26174672567-29883439075-95767161284-94957565003-35708767253-53935174705-16168070783","34551750492-67990399350-81179284955-79299808058-21257255869"
6,1251461,"37216201353-39109531021-11197415756-87798784755-02463049870-83329763120-57551308766-61100580113-80090253566-30971527105","05161542529-00085727016-35134775864-52531204064-98744439797"
7,1253428,"33071042495-29920376648-91343430102-79082003121-73317691963-02846712788-88069761578-14885283975-44409837760-90760298045","91798303270-64988107984-08161247972-12116454627-22996445111"
8,1249844,"73754818686-04889373966-18668178968-56957589012-31352882173-91882653509-59577900152-88962682169-52981807259-62646890059","76460662325-41613089656-42706083314-81833284991-17063140920"
9,1318171,"26482547570-00155460224-12388481921-23289186371-78242522654-77998886134-73270876420-50821093220-31442690639-11588920653","30508501104-50823269125-88107014550-70202920684-95842308929"
10,1258438,"05677017559-47107518969-97509137401-28934334557-14497052050-61906823704-44077628507-24840441785-05187301456-27797851637","29489382504-13697582598-09964978366-26554639515-36136545002"캐릭터는 ""로 묶여있고 라인은 "\n"로 되어 있음. 필드 구분자는 ","로 되어 있음. 옵션을 지정한대로 엑스포트가 됨.
▶︎ 압축 테스트
• 압축 옵션
mysqlsh-js > util.exportTable("sbtest.sbtest5", "file:///root/sbtest5.csv.gz", {defaultCharacterSet: "utf8", showProgress: true, fieldsOptionallyEnclosed: true, fieldsTerminatedBy: ",", linesTerminatedBy: "\n", fieldsEnclosedBy: '"', defaultCharacterSet: "utf8", showProgress: true, dialect: "csv",compression: "gzip"})
mysqlsh-js > util.exportTable("sbtest.sbtest5", "file:///root/sbtest5.csv.gz", {defaultCharacterSet: "utf8", showProgress: true, fieldsOptionallyEnclosed: true, fieldsTerminatedBy: ",", linesTerminatedBy: "\n", fieldsEnclosedBy: '"', defaultCharacterSet: "utf8", showProgress: true, dialect: "csv",compression: "gzip"})
Initializing - done
Gathering information - done
Running data dump using 1 thread.
NOTE: Progress information uses estimated values and may not be accurate.
Starting data dump
104% (2.50M rows / ~2.40M rows), 78.22K rows/s, 15.59 MB/s uncompressed, 7.99 MB/s compressed
Dump duration: 00:00:31s
Total duration: 00:00:31s
Uncompressed data size: 498.82 MB
Compressed data size: 255.92 MB
Compression ratio: 1.9
Rows written: 2500000
Bytes written: 255.92 MB
Average uncompressed throughput: 16.03 MB/s
Average compressed throughput: 8.22 MB/s
The dump can be loaded using:
util.importTable("file:///root/sbtest5.csv.gz", {
"characterSet": "utf8",
"dialect": "csv",
"fieldsEnclosedBy": "\"",
"fieldsOptionallyEnclosed": true,
"fieldsTerminatedBy": ",",
"linesTerminatedBy": "\n",
"schema": "sbtest",
"table": "sbtest5"
})
• 압축 결과
shell > file sbtest5.csv.gz
sbtest5.csv.gz: gzip compressed data, max speed, from Unix, original size modulo 2^32 498816072 gzip compressed data, unknown method, ASCII, extra field, from FAT filesystem (MS-DOS, OS/2, NT), original size modulo 2^32 498816072
▶︎ util.importTable
MySQL Shell의 병렬 테이블 가져오기 유틸리티인 util.importTable()은 대용량 데이터 파일에 대해 MySQL 테이블로 빠르게 데이터를 가져옵니다. 이 유틸리티는 입력 데이터 파일을 분석하고 이를 청크로 배포한 후 병렬 연결을 사용하여 대상 MySQL 서버에 청크를 업로드합니다. 이 유틸리티는 LOAD DATA 문을 사용하여 표준 단일 스레드 업로드보다 몇 배 더 빠르게 대규모 데이터 가져오기를 완료할 수 있습니다.
병렬 테이블 가져오기 유틸리티를 실행할 때 데이터 파일의 필드와 MySQL 테이블의 열 간의 매핑을 지정합니다. LOAD DATA 문과 마찬가지로 필드 및 줄 처리 옵션을 설정하여 임의 형식의 데이터 파일을 처리할 수 있습니다. 파일이 여러 개인 경우 모든 파일의 형식이 동일해야 합니다. 유틸리티의 기본 dialect는 해당 문의 기본 설정과 함께 SELECT...INTO OUTFILE 문을 사용하여 생성된 파일에 매핑됩니다. 또한 이 유틸리티에는 CSV 파일(DOS 또는 UNIX 시스템에서 생성됨), TSV 파일 및 JSON에 대한 표준 데이터 형식에 매핑되는 사전 설정된 dialect가 있으며 필요에 따라 필드 및 줄 처리 옵션을 사용하여 이를 사용자 정의할 수 있습니다. JSON 데이터는 행당 문서 형식이어야 합니다.
• 주의 사항
- 데이터를 로드하기 위해서는 데이터 베이스 및 임포트하려는 테이블이 사전에 미리 정의되어 있어야 합니다. 엑스포트를 했던 테이블의 DDL을 가져와 타겟 디비에 해당 DDL을 적용시켜 두어야 합니다.
- 병렬 테이블 가져오기 유틸리티는 LOAD DATA LOCAL INFILE 문을 사용하여 데이터를 업로드하므로 대상 서버에서 local_infile 시스템 변수를 ON으로 설정해야 합니다. 병렬 테이블 가져오기 유틸리티를 실행하기 전에 SQL 모드에서 다음 명령문을 실행하여 이를 수행해야 합니다.
mysql> SET GLOBAL local_infile = 1;
• 기본 사용방법
- js 모드
1. 사용 예제
mysqlsh-js > importTable ({file_name | file_list}, options)2. 실 사용법
mysqlsh-js > util.importTable("/tmp/productrange.csv", {schema: "mydb", table: "products", dialect: "csv-unix", skipRows: 1, showProgress: true})
- python모드
1. 기본 사용법
mysql-py> \use mydb
mysql-py> util.import_table("/tmp/productrange.csv", {"dialect": "csv-unix"})
2. 옵션 지정 사용법
mysql-py> util.import_table("/tmp/productrange.csv", {"schema": "mydb", "table": "products", "dialect": "csv-unix", "skipRows": 1, "showProgress": True})
3. 다중 파일 임포트방법
mysqlsh-js > \py
mysql-py> util.import_table(
[
"data_a.csv",
"data_b*",
"data_c*",
"data_d.tsv.zst",
"data_e.tsv.zst",
"data_f.tsv.gz",
"/backup/replica3/2021_01_12/data_g.tsv",
"/backup/replica3/2021_01_13/*.tsv",
],
{"schema": "mydb", "table": "productrange"}
)
4. mysqlsh 터미널 이용방법
shell > mysqlsh mysql://root:@127.0.0.1:3366 --ssl-mode=DISABLED -- util import-table /r/mytable.dump --schema=mydb --table=regions --bytes-per-chunk=10M --linesTerminatedBy=$'\r\n'
• 옵션 설명
1. schema: "db_name"
연결된 MySQL 서버의 대상 데이터베이스 이름입니다. 이 옵션을 생략하면 유틸리티는 연결 URI 문자열, \use 명령 또는 MySQL 셸 옵션에 지정된 대로 현재 MySQL 셸 세션에 사용 중인 스키마 이름을 식별하고 사용하려고 시도합니다. 스키마 이름이 지정되지 않고 세션에서 식별될 수 없는 경우 오류가 반환됩니다.
2. table: "table_name"
대상 관계형 테이블의 이름입니다. 이 옵션을 생략하면 유틸리티는 테이블 이름이 확장자가 없는 데이터 파일의 이름이라고 가정합니다. 대상 테이블은 대상 데이터베이스에 존재해야 합니다.
columns: array of column names
가져오기 파일의 열 이름을 포함하는 문자열 배열로, 대상 관계형 테이블의 열에 매핑되는 순서대로 제공됩니다. 가져온 데이터에 대상 테이블의 모든 열이 포함되어 있지 않거나 가져온 데이터의 필드 순서가 테이블의 열 순서와 다른 경우 이 옵션을 사용합니다. 이 옵션을 생략하면 입력 라인에 대상 테이블의 각 열에 대해 일치하는 필드가 포함될 것으로 예상됩니다.
MySQL Shell 8.0.22에서는 이 옵션을 사용하여 LOAD DATA 문과 동일한 방식으로 가져오기 파일 또는 입력 전처리를 위한 파일에서 열을 캡처할 수 있습니다. 배열의 열 이름 대신 정수 값을 사용하면 가져오기 파일의 해당 열이 사용자 변수 @int(예: @1)로 캡처됩니다. 선택한 데이터를 삭제하거나 decodeColumns 옵션을 사용하여 데이터를 변환하고 대상 테이블의 열에 할당할 수 있습니다.
MySQL 셸 JavaScript 모드의 이 예에서는 가져오기 파일의 두 번째 및 네 번째 열이 사용자 변수 @1 및 @2에 할당되고 이를 대상 테이블의 열에 할당할 수 있는 decodeColumns 옵션이 없습니다. 폐기되었습니다.
mysql-js> util.importTable('file.txt', {
table: 't1',
columns: ['column1', 1, 'column2', 2, 'column3']
});
3. decodeColumns: dictionary
columns 옵션에 의해 사용자 변수로 캡처된 가져오기 파일 열을 대상 테이블의 열에 할당하고, LOAD DATA 문의 SET 절과 동일하게 전처리 변환을 지정하는 키-값 쌍 사전입니다. 이 옵션은 MySQL Shell 8.0.22에서 사용할 수 있습니다.
MySQL Shell의 JavaScript 모드에 대한 이 예에서는 데이터 파일의 첫 번째 입력 열이 대상 테이블의 첫 번째 열로 사용됩니다. columns 옵션으로 변수 @1에 할당된 두 번째 입력 열은 나누기 연산을 거쳐 대상 테이블의 두 번째 열의 값으로 사용된다.
mysql-js> util.importTable('file.txt', {
columns: ['column1', 1],
decodeColumns: {'column2': '@1 / 100'}
});
MySQL 셸 JavaScript 모드의 이 예에서는 데이터 파일의 입력 열이 모두 변수에 할당된 다음 다양한 방식으로 변환되어 대상 테이블의 열을 채우는 데 사용됩니다.
mysql-js> util.importTable('file.txt', {
table: 't1',
columns: [1, 2],
decodeColumns: {
'a': '@1',
'b': '@2',
'sum': '@1 + @2',
'multiple': '@1 * @2',
'power': 'POW(@1, @2)'
}
});
4. SkipRows: 숫자
가져오기 파일 시작 부분에서 이 행 수를 건너뛰거나, 가져오기 파일이 여러 개인 경우 파일 목록에 포함된 모든 파일 시작 부분에서 이 행 수를 건너뜁니다. 이 옵션을 사용하면 테이블 업로드에서 열 이름이 포함된 초기 헤더 행을 생략할 수 있습니다. 기본값은 행을 건너뛰지 않는 것입니다.
5. replaceDuplicates(중복교체): [true|false]
기본 키 또는 고유 인덱스 값이 기존 행과 동일한 입력 행을 교체해야 하는지(true) 아니면 건너뛰어야 하는지(false) 여부입니다. 기본값은 거짓입니다.
6. dialect: [default|csv|csv-unix|tsv|json]
지정된 파일 형식에 적합한 필드 및 줄 처리 옵션 세트를 사용하십시오. 설정을 변경하기 위해 lineTerminatingBy, fieldsTerminatingBy, fieldsEnclosedBy, fieldsOptionallyEnclosed 및 fieldsEscapedBy 옵션 중 하나 이상을 지정하여 선택한 방언을 추가 사용자 정의를 위한 기반으로 사용할 수도 있습니다. 기본 방언은 해당 문의 기본 설정과 함께 SELECT...INTO OUTFILE 문을 사용하여 생성된 파일에 매핑됩니다. 이는 MySQL Shell의 테이블 내보내기 유틸리티에서 생성된 출력 파일의 기본값입니다. CSV 파일(DOS 또는 UNIX 시스템에서 생성됨), TSV 파일 및 JSON 데이터에 적합한 다른 방언을 사용할 수 있습니다. 각 방언에 적용되는 설정은 다음과 같습니다.
| dialect | linesTerminatedBy | fieldsTerminatedBy | fieldsEnclosedBy | fieldsOptionallyEnclosed | fieldsEscapedBy |
| default | [LF] | [TAB] | [empty] | false | \ |
| csv | [CR][LF] | , | ' ' | ture | \ |
| csv-unix | [LF] | , | ' ' | false | \ |
| tsv | [CR][LF] | [TAB] | '' | true | \ |
| json | [LF] | [LF] | [empty] | false | [empty] |
7. lineTerminatingBy: "characters"
입력 데이터 파일의 각 줄을 종료하는 하나 이상의 문자(또는 빈 문자열)입니다. 기본값은 지정된 방언과 동일하며, 방언 옵션이 생략된 경우 줄 바꿈 문자(\n)입니다. 이 옵션은 LOAD DATA 문의 LINES TERMINATED BY 옵션과 동일합니다. 유틸리티는 빈 문자열로 설정된 LOAD DATA 문의 LINES STARTING BY 옵션에 해당하는 옵션을 제공하지 않습니다.
8. fieldsTerminatingBy: "characters"
입력 데이터 파일의 각 필드를 종료하는 하나 이상의 문자(또는 빈 문자열)입니다. 기본값은 지정된 방언과 동일하며, 방언 옵션이 생략된 경우 탭 문자(\t)입니다. 이 옵션은 LOAD DATA 문의 FIELDS TERMINATED BY 옵션과 동일합니다.
9. fieldsEnclosedBy: "character"
입력 데이터 파일의 각 필드를 묶는 단일 문자(또는 빈 문자열)입니다. 기본값은 지정된 방언과 동일하며, 방언 옵션이 생략된 경우 빈 문자열입니다. 이 옵션은 LOAD DATA 문의 FIELDS ENCLOSED BY 옵션과 동일합니다.
10. fieldsOptionallyEnclosed: [ true | false ]
fieldsEnclosedBy에 지정된 문자가 입력 데이터 파일의 모든 필드를 포함하는지(false), 아니면 일부 경우에만 필드를 포함하는지(true) 여부입니다. 기본값은 지정된 방언과 동일하며, 방언 옵션이 생략된 경우 false입니다. 이 옵션을 사용하면 fieldsEnclosedBy 옵션이 LOAD DATA 문의 FIELDS OPTIONALLY ENCLOSED BY 옵션과 동일해집니다.
11. fieldsEscapedBy: "character"
입력 데이터 파일에서 이스케이프 시퀀스를 시작하는 문자입니다. 이것이 제공되지 않으면 이스케이프 시퀀스 해석이 발생하지 않습니다. 기본값은 지정된 방언과 동일하며, 방언 옵션이 생략된 경우 백슬래시(\)입니다. 이 옵션은 LOAD DATA 문의 FIELDS ESCAPED BY 옵션과 동일합니다.
12. characterSet: "charset"
MySQL Shell 8.0.21에 추가되었습니다. 이 옵션은 가져오는 동안 입력 데이터가 해석되는 문자 세트 인코딩을 지정합니다. 옵션을 바이너리로 설정하면 가져오는 동안 변환이 수행되지 않습니다. 이 옵션을 생략하면 가져오기에서는 Character_set_database 시스템 변수에 지정된 문자 세트를 사용하여 입력 데이터를 해석합니다.
13. bytesPerChunk: "size"
여러 입력 데이터 파일 목록의 경우 이 옵션을 사용할 수 없습니다. 단일 입력 데이터 파일의 경우 이 옵션은 스레드가 대상 서버에 대한 각 LOAD DATA 호출에 대해 보내는 바이트 수(행 끝에 도달하는 데 필요한 추가 바이트 포함)를 지정합니다. 유틸리티는 스레드가 선택하여 대상 서버로 보낼 수 있도록 데이터를 이 크기의 청크로 배포합니다. 청크 크기는 바이트 수로 지정하거나 접미사 k(킬로바이트), M(메가바이트), G(기가바이트)를 사용하여 지정할 수 있습니다. 예를 들어 bytesPerChunk="2k"는 스레드가 약 2KB의 청크를 보내도록 합니다. 최소 청크 크기는 131072바이트이고 기본 청크 크기는 50M입니다.
14. threads: number
입력 파일의 데이터를 대상 서버로 보내는 데 사용할 최대 병렬 스레드 수입니다. 스레드 수를 지정하지 않은 경우 기본 최대값은 8입니다. 여러 입력 데이터 파일 목록의 경우 유틸리티는 지정된 수 또는 최대 스레드 수를 작성합니다. 단일 입력 데이터 파일의 경우 유틸리티는 다음 공식을 사용하여 이 최대값까지 생성할 적절한 스레드 수를 계산합니다.
min{max{1, threads}, chunks}}
여기서 스레드는 최대 스레드 수이고 청크는 데이터가 분할될 청크 수입니다. 이는 파일 크기를 bytesPerChunk 크기로 나눈 다음 1을 더하여 계산됩니다. 계산을 통해 최대 스레드 수가 실제로 전송될 청크 수를 초과하는 경우 유틸리티는 필요한 것보다 더 많은 스레드를 생성하지 않습니다.
압축된 파일은 청크로 배포할 수 없으므로 대신 유틸리티는 병렬 연결을 사용하여 한 번에 여러 파일을 업로드합니다. 입력 데이터 파일이 하나만 있는 경우 압축 파일 업로드에는 단일 연결만 사용할 수 있습니다.
15. maxRate: "rate"
스레드당 초당 바이트 수로 표시되는 데이터 처리량의 최대 제한입니다. 클라이언트 호스트나 대상 서버의 네트워크나 I/O 또는 CPU가 포화되는 것을 방지해야 하는 경우 이 옵션을 사용하십시오. 최대 속도는 바이트 수로 지정하거나 접미사 k(킬로바이트), M(메가바이트), G(기가바이트)를 사용하여 지정할 수 있습니다. 예를 들어, maxRate="5M"은 각 스레드를 초당 5MB의 데이터로 제한합니다. 이는 8개의 스레드에 대해 40MB/초의 전송 속도를 제공합니다. 기본값은 0이며, 이는 제한이 없음을 의미합니다.
16. showProgress: [ true | false ]
가져오기 진행률 정보를 표시(true)하거나 숨깁니다(false). stdout이 터미널(tty)이면 기본값은 true이고, 그렇지 않으면 false입니다.
17. sessionInitSql: list of strings(문자열 목록)
대상 MySQL 인스턴스에 데이터를 로드하는 데 사용되는 각 클라이언트 세션 시작 시 실행할 SQL 문의 목록입니다. 이 옵션을 사용하여 세션 변수를 변경할 수 있습니다. 이 옵션은 MySQL Shell 8.0.30에서 사용할 수 있습니다. 예를 들어, 다음 문은 가져오기 과정에서 유틸리티가 사용하는 세션에 대해 대상 MySQL 인스턴스의 바이너리 로깅을 건너뛰고 인덱스 생성에 사용할 수 있는 스레드 수를 늘립니다.
18. sessionInitSQL: ["SET SESSION sql_log_bin=0;", "SET SESSION innodb_ddl_threads=8,"]
SQL 문을 실행하는 동안 오류가 발생하면 가져오기가 중지되고 오류 메시지가 반환됩니다.
• 실 사용방법 및 결과
1. 엑스포트한 테이블과 같은 테이블 생성
mysqlsh-js > \sql;
mysqlsh-SQL > SET GLOBAL local_infile = 1;
mysqlsh-SQL > create database imp;
CREATE TABLE imp.sbtest1 (
`id` int NOT NULL AUTO_INCREMENT,
`k` int NOT NULL DEFAULT '0',
`c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=2500001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
mysqlsh-js > \js
mysqlsh-js > util.importTable("/root/sbtest1.csv", {schema: "imp", table: "sbtest1", dialect: "csv", skipRows: 0, showProgress: true, fieldsOptionallyEnclosed: true, fieldsTerminatedBy: ",", linesTerminatedBy: "\n",fieldsEnclosedBy: '"',threads: 5, bytesPerChunk: "50M"});
Importing from file '/root/sbtest1.csv' to table `imp`.`sbtest1` in MySQL Server at /tmp%2Fmysql.sock using 5 threads
[Worker001] sbtest1.csv: Records: 251294 Deleted: 0 Skipped: 0 Warnings: 0
[Worker002] sbtest1.csv: Records: 251265 Deleted: 0 Skipped: 0 Warnings: 0
[Worker003] sbtest1.csv: Records: 250037 Deleted: 0 Skipped: 0 Warnings: 0
[Worker004] sbtest1.csv: Records: 251294 Deleted: 0 Skipped: 0 Warnings: 0
[Worker000] sbtest1.csv: Records: 251853 Deleted: 0 Skipped: 0 Warnings: 0
[Worker001] sbtest1.csv: Records: 250037 Deleted: 0 Skipped: 0 Warnings: 0
[Worker002] sbtest1.csv: Records: 250037 Deleted: 0 Skipped: 0 Warnings: 0
[Worker003] sbtest1.csv: Records: 244109 Deleted: 0 Skipped: 0 Warnings: 0
[Worker000] sbtest1.csv: Records: 250036 Deleted: 0 Skipped: 0 Warnings: 0
[Worker004] sbtest1.csv: Records: 250038 Deleted: 0 Skipped: 0 Warnings: 0
100% (498.82 MB / 498.82 MB), 5.63 MB/s
File '/root/sbtest1.csv' (498.82 MB) was imported in 46.5562 sec at 10.71 MB/s
Total rows affected in imp.sbtest1: Records: 2500000 Deleted: 0 Skipped: 0 Warnings: 0
2. 프로세스 확인
mysqlsh-SQL > show processlist;
+-------+-----------------+---------------------+------+------------------+--------+-----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+-----------------+---------------------+------+------------------+--------+-----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
......
| 16435 | root | localhost | NULL | Query | 0 | init | show processlist |
| 16445 | root | localhost | imp | Query | 3 | executing | LOAD DATA LOCAL INFILE '/root/sbtest1.csv' INTO TABLE `imp`.`sbtest1` FIELDS TERMINATED BY ',' OPTIO |
| 16446 | root | localhost | imp | Query | 3 | executing | LOAD DATA LOCAL INFILE '/root/sbtest1.csv' INTO TABLE `imp`.`sbtest1` FIELDS TERMINATED BY ',' OPTIO |
| 16447 | root | localhost | imp | Query | 3 | executing | LOAD DATA LOCAL INFILE '/root/sbtest1.csv' INTO TABLE `imp`.`sbtest1` FIELDS TERMINATED BY ',' OPTIO |
| 16448 | root | localhost | imp | Query | 3 | executing | LOAD DATA LOCAL INFILE '/root/sbtest1.csv' INTO TABLE `imp`.`sbtest1` FIELDS TERMINATED BY ',' OPTIO |
| 16449 | root | localhost | imp | Query | 3 | executing | LOAD DATA LOCAL INFILE '/root/sbtest1.csv' INTO TABLE `imp`.`sbtest1` FIELDS TERMINATED BY ',' OPTIO |
+-------+-----------------+---------------------+------+------------------+--------+-----------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
15 rows in set (0.0004 sec)
참고 사이트
https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-table-export.html
MySQL :: MySQL Shell 8.0 :: 11.3 Table Export Utility
11.3 Table Export Utility MySQL Shell's table export utility util.exportTable(), introduced in MySQL Shell 8.0.22, exports a MySQL relational table into a data file, either on the local server or in an Oracle Cloud Infrastructure Object Storage bucket. Th
dev.mysql.com
https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-parallel-table.html
MySQL :: MySQL Shell 8.0 :: 11.4 Parallel Table Import Utility
11.4 Parallel Table Import Utility MySQL Shell's parallel table import utility util.importTable() provides rapid data import to a MySQL relational table for large data files. The utility analyzes an input data file, distributes it into chunks, and uploads
dev.mysql.com
https://www.mydbops.com/blog/faster-logical-backup-of-a-single-table-in-mysql/
Faster logical backup of a single table in MySQL.
Logical backup’s are of great use in data migration across cloud environments and table level recoveries. The new Mysql shell 8.0.22 ,has introduced a couple of...
www.mydbops.com
MySQL Shell 8.0.22: Data Export/Import Utilities Tested with MySQL 8/5.7/5.6
Testing MySQL Shell 8.0.22 data export/import utilities in MySQL 5.6, 5.7, and 8x.
www.percona.com
'Databases > MySQL' 카테고리의 다른 글
| [MySQL] mysqlsh의 util.dumpInstance, util.dumpSchemas, util.dumpTables를 이용하여 덤프하고 util.loadDump로 로드하기 (0) | 2024.02.29 |
|---|---|
| [MySQL] PERSIST, RESET PERSIST를 통한 서버 파라미터 관리 (0) | 2024.01.01 |
| [MySQL] Logrotate - 로그 분할 (0) | 2023.07.02 |
| [MySQL] 서버 자동 환경 설정(MySQL 8.0) (0) | 2023.03.19 |
| [MySQL] 리눅스의 파일 열기 갯수 변경 및 자원 제한 확인 (0) | 2023.03.19 |