Mysql 서버에서 직접 export, import

[export] 

대량 데이터를 서버에서 csv파일로 저장하기 위해서는 

 

$ mysql -u root -p user

명령어로 서버에서 직접 mysql에 들어간다음

 

mysql 명령어 입력을 다음과 같이 하면 특정 폴더에 내려받기를 할 수 있다. 

select * from [테이블명]  INTO OUTFILE '/tmp/csvfile.csv' FIELDS ENCLOSED BY '"' TERMINATED BY';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';

--- 이때 경로는 tmp 폴더로 하는것이 편리할듯 하다 권한관련 에러가 잘 난다. 

 

[import]

csv 파일을 테이블에 바로 import 하기 위해서는 위와같이 서버에서 mysql에 바로 접속해서 import 시킨다. 

 

LOAD DATA LOCAL INFILE '/home/user/countryIP.csv' INTO TABLE [테이블명] FIELDS ENCLOSED BY '"' TERMINATED BY ',' ESCAPED BY '"' LINES TERMINATED BY '\n';

 

TERMINATED 같은 경우는 위와 같이 \r\n 두개를 다 쓸수 있고 \n만 쓸수도 있다 

구분하는 것을 " " , " " 로 구분하겠다는 의미로 사용하고 다음 row를 판단하는 기준은 TERMINATED를 이용해서 판단한다는 의미이다.

 

위와 같이 서버에서 import export를 하게 되면 속도가 엄청 빠르다. 

 

 

---

LOAD DATA INFILE

 

개념

---

텍스트 파일을 읽어서 테이블로 데이터를 입력하는(import 하는) 명령어이다.

 

사용 이유

---

일일이 INSERT 구문을 쓰는 노가다(반복작업) 방지.

기본 INSERT 구문을 쓰는 것보다 15~20배 정도 속도가 빠름.

DB to DB 데이터 마이그레이션 작업 할 때 사용

 

 

조건

---

파일 이름은 알파벳 문자열로 주어져야 한다.

import 하려는 파일 즉,데이터로 쓰려고 하는 파일의 권한을 가지고 있어야됨

 - 데이터베이스 디렉토리에 존재

 - 누구나 읽을수 있는 파일 권한

 

 

문법

---

표준 양식

LOAD DATA INFILE '{file_name}'

    INTO TABLE {table_name}
    CHARACTER SET utf8
    FIELDS
       TERMINATED BY '{field_terminator}'  # 각 필드 구분 문자 (예: CSV라면 컴마)
        OPTIONALLY ENCLOSED BY '"'  # 필요할 경우, 따옴표(")로 구분
    LINE TERMINATED BY '\n'
    IGNORE 1 LINES  # 제목이 포함된 첫 번째 줄은 생략
    (col1, col2, ... )  # 컬럼명

 

상황1. Linux(리눅스)

/usr/local/garaDate.csv 라는 파일 import 하기

LOAD DATA INFILE '/usr/local/garaData.csv'
REPLACE INTO TABLE `testDB`.`computerValue` COLUMNS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 LINES
(@id, @cpu_value, @memory_value,@graphics_card_value,@time_value)
SET `id` = @id, `cpu_value` = @cpu_value, `memory_value` = @memory_value,
`graphics_card_value` = @graphics_card_value,
`time_value` = STR_TO_DATE(@time_value,'%Y-%m-%d %H:%i');

주의 : LOAD DATA LOCAL INFILE이 아니라 LOAD DATA INFILE 입니다.

 

LOAD DATA LOCAL INFILE

 

개념

---

Local 옵션을 넣을 수 있다.

말 그대로 Linux(리눅스) DB 서버에 있는 파일을 넣는게 아닌

Local에 있는 파일, 즉 사용자의 노트북,컴퓨터 디스크에 존재하는 파일을 넣을 때 사용한다.

 

오해의 소지가 있을 수 있는데 DB 서버에 로컬 파일을 넣을 수 있습니다.

 

조건

---

local 명령어를 추가해서 사용할 경우에는 서버와 클라이언트

모두 local-infile 옵션이 on으로 되어 있어야만 사용가능 

local-infile 옵션 설정 확인 커맨드

show variables like 'local%';

 local-infile 옵션 ON으로 설정 하는법

mysql -u 아이디 -p -h 서버명 DB명 --local-infile=1

 

문법

---

표준 양식

LOAD DATA LOCAL INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

 

상황2. 로컬 윈도우

바탕화면에 있는 "garaData.csv" 라는 파일 원격 DB 서버에 import 하기

LOAD DATA LOCAL INFILE 'C:/Users/juju(사용자이름)/Desktop/garaData.csv'
REPLACE INTO TABLE `localtest`.`computervalue`(테이블이름)
COLUMNS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 LINES
(@id, @cpu_value, @memory_value,@graphics_card_value,@time_value)
SET `id` = @id, `cpu_value` = @cpu_value, `memory_value` = @memory_value,
`graphics_card_value` = @graphics_card_value,
`time_value` = STR_TO_DATE(@time_value,'%Y-%m-%d %H:%i');

주의 : 윈도우 파일경로는 \(역슬래쉬)가 아니라 /(슬래쉬)이다.

        LOAD DATA INFILE이 아니라 LOAD DATA LOCAL INFILE 입니다.

C:\Users\user\Desktop (X)

C:/Users/user/Desktop (O)

만약 \(역슬래쉬)를 사용하고 싶다면 \\
C:\\Users\\user\\Desktop (O)

 

 

 

LOAD DATA INFILE 최근 에러 확인 명령어

---

가장 최근 1건만 확인 가능하다.

show warnings;

 

 

대표 예외(오류)

---

권한 거부 오류

---

/* SQL 오류 (29): File 'C:\Users\juju\Desktop\garaData.csv' not found (Errcode: 13 "Permission denied") */

local-infile 옵션 설정 확인 커맨드

show variables like 'local%';

 

 local-infile 옵션 ON으로 설정 하는법

mysql -u 아이디 -p -h 서버명 DB명 --local-infile=1

 

 

데이터에 포맷 오류

---

String 타입을 date에 넣거나

int 타입의 허용 범위를 넘거나 할 때 나는 오류이다.

 

주로 날짜 date , datetime 쓸 때 진짜 많이 나는 에러이다.

 

/* SQL 오류 (1292): Incorrect datetime value: '2018-11-23 10:47

' for column `localtest`.`computervalue`.`time_value` at row 1 */

 

잘못된 구분-String인데 Datetime에 넣을려고 시도

`time_value`= @time_value (X)

 

올바른 구분-String to Datetime으로 변경 후 데이터 삽입

`time_value` = STR_TO_DATE(@time_value,'%Y-%m-%d %H:%i'); (O)

 

 

LOAD DATA INFILE 사용법

MariaDB 공식 홈페이지

https://mariadb.com/kb/en/library/load-data-infile/

MySQL 공식 홈페이지

https://dev.mysql.com/doc/refman/5.7/en/load-data.html