올코의 우당탕탕 공부하기

데이터를 다루다 보면 원하는 데이터로 가공을 하는 과정에서 join이 쓰이는 경우가 많다. 

필자도 인턴을 하면서 데이터들을 다루다보면 분산되어 있는 데이터들을 하나의 원하는 데이터로 가공을 하는 과정에서 join을 많이 사용한다. 데이터가 워낙 크다보니 index를 잘 이용해야만 join을 사용하여도 원하는 결과가 나왔다. 

같은 스키마를 가진 다른 디비에서 같은 쿼리로 데이터를 join 하면서 select를 하는데 어떤 DB에서는 explain을 통해 보았을 때 index를 잘 타면서 데이터를 빠르게 추출할 수 있지만 어떤 DB에서는 index를 타지 않는 경우가 있었다. 이러한 경우 때문에 한참 멘붕을 겪었었는데 이러한 경우가 문자열값을 가지는 column을 join 하였을 때만 나타났다. 사수님께 여쭤봐서 결론을 찾게 되었다. 여러 디비 서버에 데이터를 분산하여 저장하다 보면 character set이 변경되는 경우가 생길 수 있다. 이러한 과정에서 join을 하는 데이블들이 다른 character set을 사용하고 있으면 같은 column이더라도 index를 못 타게 된다. 이러한 문제를 해결하기 위한 방법을 아래에 정리하고자 한다.

테이블의 character set을 확인하기 위한 쿼리

이때 특정한 column의 character set을 확인하고자 할 때는 아래의 쿼리를 사용하면 된다.

테이블의 character set을 변경하기 위한 쿼리

이 때 character set은 문자가 컴퓨터에 어떠한 코드로 저장되는 지에 대한 규칙의 집합이다. 대표적으로 ASKII, utf8, euckr(utf8, euckr은 한글 지원 코드)가 있다. 

그러나 데이터베이스에서 저장된 값들을 검색하거나 정렬 등의 작업을 위해 문자들 간에 서로 비교를 하기 위해서는 collation이라는 규칙이 필요하다. 예를들어 A 테이블의 id가 utf8_general_ci 이고, B 테이블의 id가 utf8_unicode_ci 일 때  

위의 쿼리를 통해 두 테이블을 조인하려 한다면 index를 타지 못한다. 

왜냐하면 두 테이블의 id는 같은 character set을 가지지만 다른 collation을 가지므로 DB내에서 비교가 불가하게 되어 join을 걸어도 index를 타지 못한다. 그러므로 테이블의 character set을 변경해주거나 처음 테이블을 생성할 때 collate를 같이 지정해주는 것이 더 좋다.

예를 들어 테이블의  character set을 변경하고자 할 때는 

두번째 숙제인 외부에서 데이터를 저장해주기 위해 MySql에 접근할 수 있는 계정을 만드는 과정을 써보고자 한다.

신기하게도 첫 시작인 MySql에 접속하는 부분에서 부터 문제가 생겼다....(ㅠㅠ)

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

이런 에러 메세지가 출력되었다. 구글링을 해보니 나처럼 MySql을 설치한 후인 경우에는 MySql 클라이언트만 설치 된 경우와 MySql이 시작되지 않는 두가지의 상황에 의해서 상황이 발생한다고 한다.

MySql을 설치할 때 MySql-server도 같이 설치를 했었으나 혹시 몰라서  

rpm -qa | grep ^mysql

 명령어를 통해서 확인 해봤더니 

community-server-5.6.36-2.el7.x86_64

버전이 깔려있음을 확인할 수 있었다. 그러므로 나의 경우에 맞는 MySql이 시작되지 않은 경우의 방법으로 생각을 해보고 해결해 보고자 하였다.

service mysqld start 

명령어를 통해서 MySql 서비스를 시작하니 mysql -u root -p 명령어를 통해 MySql에 접속을 할 수 있었다.

 이제 본격적으로 MySql에 접근할 수 있는 계정을 등록하는 과정을 진행해보려 한다. 나는 항상 내가 잘못을 하는 건지 온갖 에러들이 나는데 이번에도 역시 이슈들이 생겼다!

우선 현재 데이터베이스의 사용자를 확인하기 위해 MySql database를 선택하여 host, user password를 확인해보려 한다.

select host, user, password from user;

현재 아무 계정도 등록을 안해서 local과 관련된 루트계정만 떴다. 이제부터 외부에서 접근을 할 수 있는 계정을 생성을 한다. MySql에 접근을 하는 계정을 만드는 명령어는 create user이라는 명령어를 사용한다.

create user userID;

userID부분에 자신이 원하는 ID를 넣어주면 된다. 그리고 아이디에 대하여 로컬계정인지, 외부 계정인지 를 설정하고, 비밀번호를 설정해 주도록 해줘야 하는데 이 부분에서 이슈가 하나 발생하였다. 

create user 'userID'@'%' identified by 'password';
//'%'는 외부에서 접근하기 위한 계정임을 나타내는 것, 내부에서 접근하기 위한 계정은 localhost를 써준다

ERROR 1396 (HY000): Operation CREATE USER failed for 'accessId'@'%'

위처럼 에러가 났다. 구글링을 해보니 stackoverflow에 비슷한 에러가 올라왔다. 대강 해석해보면 이미 userID로 아이디를 만들고 다시 userID로 만들어서 설정을 하려해서 충돌이 난 것 같다. 해결 방법으로는 userID를 drop 명령어를 통해 삭제하고, mysql privilege를 flush 해주어야 한다. privilege flush는 사용자를 추가, 삭제, 권한을 변경했을 때 MySql에 변경사항을 업데이트하기 위한 명령어이다. 이 과정을 명령어로 보게 되면 아래와 같다.

drop user 'userID';
flush privileges;
create user 'userID'@'%' identified by 'password';

위의 과정을 끝내고 host, user, password from user을 다시 확인해보면 host가 %인 user가 보이게 될 것이다. 외부에서 접근이 가능한지 확인하기 위해서 새로운 터미널창에서 명령어를 통해 접근을 해보았다.

mysql -h서버주소 -u아이디 -p패스워드

위의 명령어를 통해 접속을 하면 아래와 같이 정상적으로 접속이 됨을 확인할 수 있다. 접속이 정상적으로 되면 외부에서 접속 가능한 계정을 성공적으로 만든 것이다.

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 13

Server version: 5.6.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>