MySQL DB에 .txt 파일 import하는 방법

보통 MySQL DB로 데이터를 import할 때는 phpMyAdmin에서 .csv 형태로 밀어넣는 것이 가장 편하다.
하지만 데이터 용량이 커져버리면 안된다고 나온다.
이럴때 극복할만한 방안은 다음과 같다.

- 일단 db를 사용할 수 있는 command prompt로 들어간다.
c:> mysql -u (username) -p
Enter password: (type password)

mysql>

내 경우는 APMSetup을 사용하고 있으므로 윈도 태스크바 아이콘의 APMSetup 모니터에서 MySQL 콘솔을 선택하면 되겠다.

- 설치되어 있는 DB를 보려면 show databases; 명령어를 사용한다.
mysql> show databases;

- Import할 DB로 이동한다. 예로 DB 이름이 'test'인 경우 다음과 같이 사용한다.
mysql> use test;

- 먼저 import할 table을 만들어야 한다. 적당한 column 갯수와 그 형식을 지정하여야 하는데, 이 부분은 phpMyAdmin으로 처리하는 편이 편하므로 pass.

- table을 만들었다면 다음과 같이 .txt 파일을 import한다.
예로 table 명칭이 mytable이고 .txt 파일의 경로가 "c:/myfolder/a.txt"라면 다음과 같이 사용한다.

mysql> LOAD DATA LOCAL INFILE 'c:/myfolder/a.txt' INTO TABLE mytable
           CHARACTER SET 'utf8'
           FIELDS TERMINATED BY '\t'
           ENCLOSED BY '"' LINES TERMINATED BY '\n';

실제로 CHARACTER SET 부분이나 ENCLOSED, LINES 부분은 table이 만들어져 있다면 필요없는 부분이다.

- 참고 : 보통 데이터 파일들은 header 부분에 column 명칭이 들어가는데, 이것도 그냥 db로 들어가버린다. 따라서 접속해서 맨 윗행을 지워야 할 것이다.

APMSetup7 사용시 MySql 설정방법

윈도 환경에서 편리해서 APMSetup을 이용해서 php를 짜고 있다. 이때 Apache와 MySql5가 같이 설치되어 여간 편리한 게 아니다.

그런데 쓰다 보니 default로 깔리는 characterset 문제가 있다.
이를 조정하기 위하여 APMSetup의 MySql server 세팅을 이용할라 치면 반영이 안된다.

APMSetup 홈페이지에서 이걸 해결하는 방법을 알았다.
원래 C:/APM_Setup/Server/MySQL5 디렉토리 아래 my.ini 파일로 MySql 설정을 하는데
APMSetup에서 표시하는 my.ini 파일의 경로가 C:/APM_Setup/Server/MySQL5/data에 있어서 그렇다.
데스크탑 태스크바에서 실컷 설정해놓고 나면 반영이 안된다. 해결은 다음과 같이 한다.

1. 일단 태스크바에서 APMSetup - 서버환경설정 - MYSQL 설정 탭을 이용하여 원하는 대로 설정한다.

2. C:/APM_Setup/Server/MySQL5/data/my.ini 파일을 C:/APM_Setup/Server/MySQL5로 복사한다.

3. MySQL을 다시 시작한다. 그러면 반영된다.

csv 파일을 이용한 mysql DB 만들기

아마 이 내용도 너무 쉬워서 어디에도 참조할 만한 글이 없는지도 모르겠다.
기본적인 이해가 동반되어야 하는 것이지만, 내가 기억하기 위해 글을 남긴다.

DB를 주로 쓰고 SQL문을 잘 쓴다면야 뭐 필요도 없는 기능일 수도 있지만,
뭔가 데이터를 DB에 넣을 때 엑셀로 정리해 놓은 것을 그냥 밀어야 할 경우도 있다.
이때 편리한 기능이 csv 파일을 그대로 mysql에 집어넣는 것인데,
phpMyAdmin에서 이 기능을 지원한다.

0. 먼저 DB에 사용할 Table을 형식에 맞게 형성해 주어야 한다.
주의점은 없지만, 통상적인 개념으로는 첫 행에 값의 레이블을 주고 둘째 행부터 값을 넣는 것이 일반적이다. 이를 열로 처리했을 때는 어떻게 되는지 실험해보질 않았다.

1. 해당 테이블을 선택하고 import 탭으로 오면, 기본 화면은 그림과 같다.

여기에 세팅해 주어야 할 부분을 짚어보자.
1-1. 파일 올리기 : SQL 텍스트파일의 위치 - 찾아보기... 버튼을 이용해서 집어넣는다.
1-2. 파일 문자셋 : 한글 코딩인 euckr을 선택
1-3. 레이블(Label) 건너뛰기 : 만일 엑셀에서 데이터 레이블을 이용했다면 (값이 바로 나오는 게 아니라 첫 행이 값의 레이블이라면) Number of records(queries) to skip from start 값을 1로 해서 건너뛰게 해준다.
1-4. 필드 구분자 : 물론 csv를 선택해야 하는데, 이때 사용하는 필드 구분자는 , (쉼표)이다. csv 파일 자체가 쉼표로 구분되는 형식문서인데, 왜 phpMyAdmin에서는 csv import의 기본 필드 구분자를 ; (세미콜론)으로 해 놓았는지 아직은 모르겠다.

2. 위 과정을 잘 거치고 "실행" 버튼을 누르면, 해당 테이블에 자료가 잘 들어갔다는 메시지와 함께 값을 볼 수 있다.

#### 2016-08-05 추가

MySQL의 경우에는 phpMyAdmin에서 import를 지원한다.
그런데 .txt의 경우에는 용량이 넘치면 (현재는 350MB) 하다가 중단되어버린다.
그동안 버전업이 되었는지 이게 축차적으로 작업이 된다. (Parallel import)
한번 메모리 한계까지 작업하고 나서 파일란이 지워지면서 import가 다시 뜬다.
읽어보면 같은 파일을 다시 지정해주면 import 완료지점 다음부터 다시 작업한다고 한다.
아마도 .txt가 아니라 .tsv (Oracle text import 형식) 이기 때문일까?

그럼에도 큰 .txt나 .tsv의 경우에는 여전히 command line을 사용하는 것이 빠르고 안정적인 듯하다.
직접 해보니 파일 크기에 비하여 너무 자주 서버리고 라인수가 맞지도 않는 듯.

phpMyAdmin 접속시 에러메시지 : Cannot start session without errors, please check errors given in your PHP and/or webserver log file and configure your PHP installation properly.

관련 참조 : http://allref.tistory.com/3

일하다 보면 여러가지 어려움에 처하게 되는 것이 자연스러운 일이다.
그중에서도 뭔가 이유를 모르겠는데 안되는 것은 더욱 그렇다.

APMSetup의 php 환경에서 mysql에 phpMyAdmin을 이용하여 접속하고자 할 때, 제목과 같은 에러가 발생하는 경우가 있다.
Cannot start session without errors, please check errors given in your PHP and/or webserver log file and configure your PHP installation properly.

분명히 ID와 PW를 바르게 입력하였는데도 안되는데, 이는 컴마다 세팅이 틀려 어떤 컴에서는 조작 없이도 잘 되고 어떤 컴에서는 조작을 해야 들어가게 된다. 해석으로 보자면 SESSION을 제대로 시작할 수 없다고 하는 것인데, SESSION을 서버에서 사용하고 있는 나로서는 mysql에 접속할 때 SESSION을 건드린다는 인식밖에는 가질 수 없게 만드는 것이기도 하다.

여하튼 해결하는 방법은 비교적 간단하다.

1.  APMSetup의 경우 서버 세팅을 잡는 php.ini 파일 내용에 보면 session.auto_start = 0으로 되어 있는 항목이 있다. 이를 session.auto_start = 1로 바꾸어 준다.

2. Apache와 mysql을 재시작해준다. 그러면 잘 접속이 된다.

하지만 이게 해결이라고 하기에는 설명이 부족한 편이다. 그렇다면 mysql에 접속하려고 한다면 SESSION을 어떤 방식으로든 변경한다는 것인데, 이는 현재 서버에 접속하고 있는 클라이언트의 동작에 영향을 미친다는 이야기가 된다. 따라서 서버에서 mysql에 접속할 때에는 공지를 통해 클라이언트에 경고를 주는 방향이 좋겠다.

Ruby on Rails 이용시 MySql 접속방법

수업 관계로 Ruby와 Ruby on Rails를 이용해야만 한다. 하나의 시스템에 웹 서버를 몇개씩 설치하는 것은 참으로 귀찮은 일인데, 이게 충돌을 한다면 더더욱 귀찮은 일이 되어버린다. 하지만 내가 할 수 있는 일은 그런 것들 뿐이니 선택의 여지가 없다고 본다.

공교롭게도, 아니 당연하게도 PHP와 Ruby on Rails는 충돌이 있다. 둘다 MySql이라는 데이터베이스를 사용하므로 세팅이 다른 것이 당연하고, 각자의 세팅에 맞게 접속을 해 줘야 한다. 공짜라 그런가, 아니면 내가 뭘 설치하기 귀찮아서 그런지는 잘 모르겠지만 커멘드 라인에서 아직은 사용하는 단계이므로 접속을 위해서는 뭔가 해 줘야 한다.

일단은 PHP를 기동하는 APMSetup Monitor를 기동해서 PHP를 멈춘다. 이때 MySql도 함께 작동을 중지시켜야 Ruby on Rails를 기동시킬 수 있다. 그러고 나서 InstantRails를 기동시키면 함께 설치된 MySql이 기동된다. 비록 같은 데이터베이스이지만 자료를 저장하거나 생성하는 path가 다르므로 접속하는 방법도 다르다.

InstantRails에서는 그냥 패스워드 없이 접속이 된다. 즉 커멘드 라인에서 mysql -u root 라고만 치는 것으로 접속이 된다. 물론 만들어진 테이블들은 InstantRails 폴더 내의 MySql 폴더에 저장이 되겠고 Ruby on Rails 상에서 작동할 것으로 생각된다.

APMSETUP 이용시 MySql command line 접속방법

너무 간단한 이야기라서 별로 효용은 없을 듯도 하지만, 내 기억력의 한계를 극복하는 길은 오직 기록하는 수밖에 없다. 이번 수업에 사용될 command line에서 SQL 입력을 위한 MySql 접속 문제는 어떻게 보면 매우 일반적인 문제가 될 수 있겠다.

일단은 MySql이 설치되어 있다고 가정하면, path가 잡혀 있을 것이기 때문에 어디서든 mysql.exe 명령을 통해서 command line 스타일의 MySql 억세스가 가능한 것으로 이론적으로는 이해할 수 있겠다. 그렇다면 단순히

C:>mysql

이라고 입력하면 접속이 그냥 되어야 하는데...

이게 말처럼 되지를 않고 (내가 설치한 php용 환경인 APMSETUP의 경우) "ERROR 1045 (28000) : Access denied for user 'ODBC'@'localhost' (using password: NO)" 라는 에러 메시지가 뜨게 된다. 간단히 설명을 찾아보니 계정 권한 문제니 설정된 사용자 및 암호를 이용해서 접속을 해야 한다는 건데, 내가 뭐 세팅한 것도 없고 뭘 어떻게 고쳐야 할지 모르겠다. MySql을 지우고 다시 깔으라는 이야기까지 나오는데 이건 빈대 잡겠다고 집 태우는 격이고...

다시 좀 찾다가 http://blog.naver.com/PostView.nhn?blogId=hmoai&logNo=50033002305&redirect=Dlog&widgetTypeCall=true에서 힌트를 얻었다. APMSETUP으로 MySql을 설치한 경우 아이디 root의 암호 설정이 apmsetup이라는... 따라서 아래와 같이 입력을 하면 되겠다.

C:>mysql -u root -papmsetup

직관적으로 이해하기에 -u는 사용자(user) 옵션이고 -p는 암호(password) 옵션으로 보이는데, 웃기게도 사용자는 띄어쓰기를 해야 하지만 암호는 띄어쓰기를 하면 안된다. 저렇게 입력을 하고 나면 성공적으로 MySql command line에 접속할 수 있고, 나올 때는 간단히 exit를 쳐 주면 된다.

참고 : 비록 하드디스크의 어느 곳에서나 접속이 가능하지만, 데이터베이스의 생성은 MySql>data 폴더에 생성된다. APMSETUP 기준으로 C:\APM_Setup\Server\MySQL5\data가 된다.

참고 2 : 그런데도 각종 억세스는 현재 접속한 디렉토리에서 사용한다. 예를 들어 .csv등 모종의 파일을 데이터베이스에 넣으려고 한다면, .csv 파일이 위치한 곳에서 mysql 명령어로 접속을 하는 것이 포인트. 또는 해당 디렉토리를 지정해 주면 되는데, 이건 php와 동일하다.

mysql> load data local infile "./data/supermarket.csv" into table supermarket fields terminated by ',' lines terminated by '\r\n';