프로젝트 진행중에 데이터 전처리를 진행하고 전처리한 데이터를 DB에 넣는 작업을 해야했다. DB는 MySQL을 사용했고, EC2 서버에서 작동중이었다. 데이터는 내 로컬에서 전처리를 진행했기 때문에 DB에 원격으로 접속해 전처리한 csv파일을 insert해줘야 했다.
원격접속을 하려면 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를 이용해 작업을 진행했기 때문에 모든 권한을 줬다.
간단한 명령어로 접속할 수 있다.
mysql -h [DomainName] -u [UserName] -p [DBName]
나는 기본 포트인 3306을 사용했기 때문에 포트번호를 명시하지 않았다. 다른 포트번호를 사용중이라면 --port [PortNum] 옵션을 이용해 명시해 주면된다.
워크벤치에서 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될것이다.
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 옵션을 추가해야한다.
나는 insert해야하는 데이터가 총 100만건이었기 때문에 cmd의 loaddata를 사용했다. workbench는 앞서 말했듯 insert 속도가 굉장히 느리다.
데이터를 모두 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파일은 정상적으로 생성되니 걱정하지 않아도 된다.