mysqlimport명령어를 이용하여 로컬의 텍스트 파일이나 dump파일을 MySQL db의 table에 값 저장하기

mysql의 table을 생성할 때 로컬의 특정 파일이 담고 있는 내용을 table의 값으로 저장해야하는 경우가 있다.

이때 로컬의 특정 파일은 dump 파일 일수도 있고, 일반적인 txt 파일 일수도 있다.

test.txt에 ,로 구분되어 다음과 같은 내용이 담겨 있다고 하면

홍길동,남,010-1234-4567,YES

장길동,여,011-0123-1111,NO

...

이 내용을 mysql의 특정 table의 내용으로 저장하는 작업을 할수도 있다.

혹은 로컬의 특정 파일의 내용이 다음과 같이 |로 필드의 값이 구분되어 있다고 할 경우

12      |       74109    |

30      |       29        |

36      |       184914  |

37      |       42        |

46      |       39        |

67      |       32033   |

76      |       155892  |

77      |       74311   |

79      |       74313   |

80      |       155892 |

90      |       34102   |

91      |       34103   |

.....

이럴 경우 mysqlimport 명령어를 이용하면 된다.

mysqlimport  -uroot  -p  --local  --fields-terminated-by="|"  yyy  merged.dmp

여기서 -uroot는 mysql 사용자 계정의 id가 root라는 뜻이고 -p옵션은 mysql 사용자 계정의 비번을 입력하겠다는 것이다. 

이 경우는 비번을 입력하지 않으면 위의 명령어를 실행할 때 비번을 묻게 된다.

--local 옵션을 주지 않으면 로컬 파일인 merged.dmp를 읽지 못하고 다음과 같은 에러를 발생 시킨다.

mysqlimport: Error: 13, Can't get stat of '/var/lib/mysql/yyy/merged.dmp' (Errcode: 2 - No such file or directory), when using table: merged

이 에러는 DB 명으로 입력한 yyy를 디렉토리 경로로 인식하는 문제다.

그 다음 옵션인 --fields-terminated-by="|"는 table저장할 데이터가 들어 있는 merged.dmp의 내용이 위에서 보는바와 같이 |를 기준으로 필드의 값이 구분되어 있다.

따라서 필드 구분자를 지정해 줘야 한다. 만일 ,로 구분되어 있다면 --fields-terminated-by=","와 같이 하면된다.

그다음 옵션인 yyy는 Database 이름이다. yyy라는 데이터베이스 안에 merged라는 table에 merged.dmp가 가지고 있는 내용을 table의 각 레코드와 필드에 값을 저장하게 된다.

이때 만일 다음과 같은 에러가 발생할 것이다.

mysqlimport: Error: 1290, The MySQL server is running with the --secure-file-priv option so it cannot execute this statement, when using table: merged

이 에러는 보안 상의 이유로 특정 디렉토리에 있는 파일에 대해서만 위의 명령어를 실행할수 있도록 설정되어 있다는 뜻이고 그 특정 디렉토리가 어느 곳인지를 알려면

mysql -u사용자id -p사용자비번

으로 들어가서 

mysql>show variables like 'secure%';

를 실행하면

+------------------+--------------------------+

| Variable_name    | Value             |

+------------------+--------------------------+

| secure_auth      | ON                  |

| secure_file_priv  | /aaa/bbb/ccc/     |

+------------------+---------------------------+

와 같은 정보가 나올 것이다. 이것은 /aaa/bbb/ccc/라는 디렉토리에서만 mysqlimport로 특정 파일의 내용을 mysql db의 table에 값을 저장할수 있다는 뜻이다.

만일 secure-file-priv 자체를 off 시키고 싶다면

우분투의 경우 /etc/mysql/mysql.cnf 파일 안의 맨 끝에 

secure-file-priv=""를 한줄 추가한 후

# service mysql restart

를 실행해서 mysql을 재실행해 주면 mysql.cnf의 설정대로 적용이 되어 secure-file-priv를 off하게 된다.

보안을 위해 이 작업 후에는 역시 mysql.cnf의 

secure-file-priv="/aaa/bbb/ccc/"로 변경후 역시 mysql을 restart시키는 것이 신상에 좋을 것이다.

혹은 mysqlimport와 동일한 기능을 다음과 같이도 가능하다.

mysql에 접속해서(mysql -u사용자id -p)

mysql> LOAD DATA INFILE '/var/lib/mysql-files/merged.dmp' INTO TABLE merged FIELDS TERMINATED BY '\t|\t' LINES TERMINATED BY '\t|\n' (old_tax_id, new_tax_id);

여기서 /var/lib/mysql-files/merged.dmp는 mysql db의 table에 저장할 데이터가 들어 있는 로컬의 (텍스트)파일,

FIELDS TERMINATED BY '\t|\t'는 /var/lib/mysql-files/merged.dmp 파일의 필드 구분자가 '\t|\t'라는 뜻이고

LINES TERMINATED BY '\t|\n'는 /var/lib/mysql-files/merged.dmp 파일의 row(레코드) 구분자가 '\t|\n'라는 뜻이고

(old_tax_id, new_tax_id)는 /var/lib/mysql-files/merged.dmp 파일의 내용을 읽어서 저장할 merged라는 table의 필드명이다.

대용량 데이터를 처리해본 결과 mysqlimport에 비해서 LOAD DATA INFILE의 방식이 훨씬 안정적으로 동작했다.