DB 원격연결 및 loaddata, dump

0. 배경

프로젝트 진행중에 데이터 전처리를 진행하고 전처리한 데이터를 DB에 넣는 작업을 해야했다. DB는 MySQL을 사용했고, EC2 서버에서 작동중이었다. 데이터는 내 로컬에서 전처리를 진행했기 때문에 DB에 원격으로 접속해 전처리한 csv파일을 insert해줘야 했다.

1. 원격접속을 위한 User만들기

원격접속을 하려면 DB User가 다른 ip에서 접속가능한 권한을 줘야했고, 외부에서 접근이 가능하기 때문에 해당 User의 권한을 특정 database만 사용할 수 있게 제한해야 했다. 물론 root에도 외부접속 권한을 줄 수 있지만 잘못하면 root를 다른 사람이 사용해 우리 DB를 망쳐놓을 수 있기 때문에 User를 하나 만들어 줬다.

CREATE USER 'userName'@'%' IDENTIFIED BY 'password'; 
FLUSH PRIVILEGES;
GRANT ALL PRIVILEGES ON DBName.* TO'userName'@'%'; 
FLUSH PRIVILEGES;

user를 생성하는데 @뒤 '%'는 어느 주소에서 접근하는것을 허용하는지를 입력하는 부분이다 '%'는 외부접속 모두를 허용한다는 뜻이다. 특정 ip주소로 한정할 수 있다.
권한을 주는 3번째 라인의 DBName.*는 특정 DB에서 어떤 행동에 대한 권한을 줄것인지를 지정해주는 부분이다. 나는 외부에서 workbench나 cmd를 이용해 작업을 진행했기 때문에 모든 권한을 줬다.

2. 원격접속하기 - workbench

• 1번에 Connection Name을 지정해준다. workbench에 표시되는 이름이기에 workbench에 중복되는 Connection Name이 없다면 아무거나 지정해줘도 상관없다.
• 2번에 DB가 있는 서버나 디바이스의 ip주소나 도메인을 입력해 준다. 도메인이 있다면 도메인을 입력해야 하는것같다. 처음에는 ip주소를 입력했을때 연결이 안되다가 발급받은 도메인을 사용하니 바로 연결이 됐었다. 포트번호는 mysql이 사용중인 포트번호를 지정해주면 된다. 특별히 변경하지 않았다면 3306번 포트를 사용중일 것이다.
• 3번에 아까 만들었던 외부접속을 허용한 User의 Name을 입력한다.
• 4번을 눌러 아까 만들었던 User의 password를 입력한다.
• 위 정보를 모두 입력했다면 5번을 눌러 연결이 정상적으로 되는지 테스트해본다. 정상적으로 연결된다면 아래와 같은 창이 나온다. 아래에 연결한 DB의 정보가 더 출력된다.
  
• 이렇게 연결이 성공적으로 되었다면 ok를 눌러 커넥션을 생성해준다.

2.원격접속하기 -CMD

간단한 명령어로 접속할 수 있다.

mysql -h [DomainName] -u [UserName] -p [DBName]

나는 기본 포트인 3306을 사용했기 때문에 포트번호를 명시하지 않았다. 다른 포트번호를 사용중이라면 --port [PortNum] 옵션을 이용해 명시해 주면된다.

3. csv파일 insert하기 - workbench

워크벤치에서 csv파일 insert과정 자체는 아주 간단하지만 매우 느리고 encoding오류가 좀 있어 cmd보다 불편하게 느껴졌다.

workbench 좌측에 있는 Schema에서 오른쪽을 눌러 Table Data Import Wizard를 눌러 원하는 csv파일을 선택하고 next를 눌러 insert해주면 된다.
아래처럼 encoding 오류가 발생할 수 있다.
나는 jupyter notebook에서 분명 csv파일을 저장할때 utf-8형식으로 저장했는데 'cp949' codec can't decode byte 0xec in position 0: illegal multibyte sequence 오류가 발생해 매우 억울했다.

원인은 모르지만 해결방법은 안다.

메모장으로 csv파일을 열어서 다른이름으로 저장을 누른뒤 인코딩 방식을 utf8에서 ANSI로 변경하고 .csv파일로 저장한다.
이후 엑셀에서 변환한 파일을 열고 다시 csv파일로 저장하면 별 문제없이 import될것이다.

3. csv파일 insert하기 - CMD

LOAD DATA LOCAL INFILE 'csv File Path'
(REPLACE) INTO TABLE tableName
FIELDS 	
	TERMINATED BY ','
    -- OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

원격으로 DB에 접속한 뒤 LOAD DATA 명령어를 이용하면 된다.
현재 로컬에 있는 파일을 원격 DB로 넣기 위해서는 LOCAL INFILE을 이용해야 한다. 해당 명령어를 이용하기 위해서 처음 DB에 접속할때 --local_infile 옵션을 추가해야한다.

• 2번째 라인의 REPLACE를 사용하면 PK가 겹치는 데이터가 있을 경우 Input 파일에 있는 데이터로 덮어씌워진다. REPLACE를 적어주지 않으면 겹치는 PK가 있으면 해당 데이터가 insert되지 않는다.
• FILEDS 는 데이터 컬럼을 어떻게 구분할지 옵션을 지정해 주는 부분이다. TERMINATED BY는 해당 문자열로 컬럼을 구분한다는 뜻이고 OPTIONALLY ENCLOSED BY는 해당 문자열로 둘러쌓여 있는 데이터에 구분자가 들어가도 구분자가 아닌 그냥 문자로 인식한다는 뜻이다. 이 옵션은 하나의 컬럼에 들어가는 모든 데이터가 ""로 둘러쌓여야 적용되는것으로 보인다.
• LINES TERMINATED BY 는 해당 문자를 구분자로 ROW를 나눈다는 뜻이다.
• IGNORE 1 LINES는 파일의 첫번째 라인은 무시한다는 뜻이다. 대부분 CSV파일의 1라인은 컬럼명이 들어가기 때문에 사용했다.
• 데이터를 넣을 컬럼을 지정할 수도 있다. 나는 CSV파일에 있는 컬럼과 TABLE의 컬럼이 일치했기에 생략했다.

나는 insert해야하는 데이터가 총 100만건이었기 때문에 cmd의 loaddata를 사용했다. workbench는 앞서 말했듯 insert 속도가 굉장히 느리다.

4. dump

데이터를 모두 insert하고 백업을 위해 dump파일을 만들어야 했다. 이 또한 cmd에서 진행했다.

mysqldump -h [DomainName] -u [UserName] -p [DBName] > [SavePath].sql

mysql에 접속하지 않고 위 명령어를 입력해주면 된다. 아래처럼
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces 에러가 발생할 수 있는데 dump파일은 정상적으로 생성되니 걱정하지 않아도 된다.