mysql 4.1 > mysql.5.0

기존 freebsd 를 사용하던 서버를 centOS 로 대체하면서 어플리케이션들 역시 마이그레이션중이다.

centos 에 기본 패키지로 설치뒨 mysql 5.x 로 기존 4.x 에서 사용하던 DB 를 가져오려 하는데 한글 인코딩 문제로 헤매는중.. mysql 은 다 좋은데 인코딩 문제때문에 항상 새로운 버젼이 나올때마다 삽질을 좀 하게 된다. 비영어권들에서 항상겪는 문제.. ㅠㅠ

MySQL 5.0 으로 업그레이드 할 때 UTF-8, EUC-KR 같이 쓰는 경우 캐릭터셋 문제 해결방법, v1.3

거의 일주일을 이 문제 붙잡고, 멀티바이트 문자권 사용자의 설움을 너무 느꼈습니다..

일단 테스트 환경은 다음과 같습니다.

old: Fedora core 3, MySQL 3.23.58-16.FC3.1
new: CentOS 5, mysql-5.0.22-2.1

다 기본 패키지입니다. (전 직접 rpm 리빌드하지 않는 한 컴파일을 직접 하지 않습니다.) 5.0 으로 업글 문제이지만, 4.0 에서 4.1 이라던가 5.1 로 등등, charset 문제되는 머신은 대부분 해당되리라 생각합니다.

예전 머신 업글하려고 새 머신을 따로 세팅해서 마이그레이션 하는 덕분에 다양한 테스트를 해 볼 수 있었습니다. 서비스 정상화의 스트레스를 그다지 받지 않았고요.

적은 문제도 아니고 다양한 workaround 가 있지만 가능한 정석대로 해결하려고 노력한 결과입니다.

---------------

기존 디비는 euc-kr 데이터와 utf-8 데이터가 혼합되어 있습니다. 새 데이터베이스도 캐릭터셋을 혼합해서 사용 할 예정이고, 기존의 코드는 고치고 싶지 않습니다. 서버 세팅만으로의 마이그레이션을 원하는거죠.

결론은 my.cnf 의 [mysqld] 에 init_connect=set names binary 를 추가하는겁니다. 아래는 마이그레이션 과정에 신경써야 할 일을 적었습니다.

1. my.cnf를 수정합니다. [mysqld] 항목에 다음을 추가합니다.

   character_set_server=euckr
   
   init_connect=SET names binary
   
   

   기존 디비가 UTF-8 위주였다면 euckr 대신 utf8 을 지정합니다. character_set_server 은 새 database 생성 시, CHARSET 옵션을 주지 않았을 경우에만 사용됩니다. 새 database 만들때의 기본값인거죠.

2. 그리고 덤프를 뜹니다. 캐릭터셋이 다른 디비끼리 따로 떠도 되지만, 섞여 있어도 mysqldump -A 로 한번에 떠도 됩니다.
3. 덤프를 손봅니다.
   1. 캐릭터셋이 한가지라면 이 부분은 건너뛰어도 무방합니다.
   2. 캐릭터셋이 섞여 있는 경우 database 생성부분을 캐릭터셋에 맞게 손봐줍니다.

      다른 database의 캐릭터셋에 맞게 DEFAULT CHARACTER SET 을 설정해줍니다,

      character_set_server 에 설정된 캐릭터셋과 동일한 database 라면 냅둬도 그대로 들어갑니다.

      원래:
      

      (..생략..)
      
      CREATE DATABASE /*!32312 IF NOT EXISTS*/ ctest-euc-kr;
      
      (..생략..)
      
      CREATE DATABASE /*!32312 IF NOT EXISTS*/ ctest-utf-8;
      
      (..생략..)
      
      

      수정:

      (..생략..)
      
      CREATE DATABASE /*!32312 IF NOT EXISTS*/ ctest_euc_kr;
      
      (..생략..)
      
      CREATE DATABASE /*!32312 IF NOT EXISTS*/ ctest_utf_8 CHARACTER SET utf8;
      
      (..생략..)
      
      

   3. 디비끼리 따로 뜬 경우엔 sql create 가 없으니 손으로 디비를 하나씩 만들어줍니다. 만들때 디비에 charset을 지정해줍니다.
4. sql을 디비에 부어줍니다.

   디음과 같이 붓습니다. 그럼 변환 안하고 그대로 넣습니다.
   

   mysql -p --default-character-set=binary < dump.sql
   
   

   혹시 디비끼리 뜬 경우, 각 디비에 맞게 지정하고 부어도 됩니다.
   

   mysql -p db1 --default-character-set=euckr < db1.sql
   
   mysql -p db2 --default-character-set=utf8 < db2.sql
   
   

5. 끝, 한글 문제가 없는지 확인합니다.
   1. 디비의 한글 데이터 표시에 문제가 없는지,
   2. 새로 디비에 한글 데이터를 넣었을 때 것도 잘 표시되는지,
   3. 정렬엔 문제가 없는지 봅니다.

클라이언트에서 사용하는 캐릭터셋을 믿을 수 있다면 binary 로 지정하여 변환하지 말아야 합니다.

이거 안하면 지맘대로 latin1 로 변환하죠. 이거가 일반적으로 가장 큰 문제입니다.
요넘을 binary 로 지정하면 character_set_client / character_set_results / character_set_connection 세녀석을 binary 로 세팅해서, 입출력 글자를 변환하지 않습니다.

관련 내용은 다음과 같습니다.
http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html
http://dev.mysql.com/doc/refman/5.0/en/charset-charsets.html

| binary   | Binary pseudo charset       | binary              |

뭐 요점은 character_set_results, character_set_connection, character_set_client, character_set_connection 을 binary 로 잡는겁니다. 캐릭터셋을 변환하지 않아 데이터가 그대로 오가죠.. 기존의 3.x, 4.0 과 같은 방법입니다. 하지만 데이터베이스는 캐릭터셋이 지정되어 있어 정렬 등에 문제가 없습니다.

---------------

기존의 해법들과 그 문제점들입니다.

• mysql_connect 를 한 뒤 mysql_query 로 set names euckr 명령어를 날려주세요.

  저도 제시해봤던 방법이지만, 코드 다 고쳐야 합니다. -_- 마이그레이션의 후유증이 너무 커요.. 앞으로도의 코드도 저렇게 짜야 하죠.

• my.cnf 에서 default-character-set 을 여기저기 설정해주세요. charset_set_client 등등 변경이나 character-set-client-handshake 옵션도 해주시고요.

  mysql 서버를 사용하는 누구나 그 캐릭터셋을 사용해야만 합니다 이러면. 디비마다 캐릭터셋 변경 가능한 보람이 없죠. 게다가 쉘에서의 mysql에서는 되지만, php 에서는 저것조차 무시하고 그냥 latin1 을 먹습니다.

• my.cnf 의 init_connect 에서 set names 를 넣어주세요.

  마찬가지로 모든 데이터베이스, 모든 테이블이 같은 캐릭터셋을 먹게 됩니다. 다른 캐릭터셋을 쓰려면 코드상에 set names 로 쓸 캐릭터셋을 정의해줘야 하고요. 그냥 result 를 NULL 로 하면 그대로 뽑아줍니다.

• SET character_set_result 를 NULL 로 잡아주세요.

  뽑을땐 문제가 없는데, 데이터 넣을때는 character_set_client 와 character_set_connection 값의 영향을 받습니다. -_-

이 방법에 문제가 전혀 없는가.. 도 생각은 해 보았습니다.

• 저렇게 response NULL 로 해놓고 다른거 신경 안쓰면 정렬 문제는?

  캐릭터셋이 이미 디비에서 설정되어 있고 테이블은 그거 따라가므로 result conversion 은 별 상관 없으리라 봅니다. 3.x 나 4.0 에서 문제되던 부분이 해결되는거죠.

• 그럼 저건 대체 왜 있는 옵션인데?

  뭐 멀티바이트 못 읽는 서양녀석들은 깨진 글자 받지 않아 좋겠죠. 데이터베이스에 어떤 캐릭터셋이 들어있든요.

  디비에 euc-kr, ujis, utf-8 등등 다양한 변환되지 않은 디비나 테이블이 존재하고, 그걸 한꺼번에 UTF-8 로 꺼내서 표시하고 싶다면, charset_set_result 를 utf8 로 지정하여 디비 꺼낼때 conversion 이 되지 않나 싶습니다. 뭐 디비를 utf-8 로 변환하면 골치가 덜 아프겠지만 저장공간의 절약이나 한 디비에서 원래 캐릭터셋으로와 utf-8 로 입출력을 노린다면 활용은 가능하겠습니다.

• 그냥 UTF-8 로 다 변환하지 뭣하러 이럽니까?

  그럴만한 사정이 되지 않는 상황도 있는겁니다. 사실 저는 아닙니다만;; 그냥 해결해보고 싶었습니다.

가능한 손 적게 대고 해결하려고 해서 아주 깔끔하지만은 않습니다. 해보시고, 문제점 있으면 댓글 달아주세요.

---------------

아래는 php 환경에서 현재 캐릭터셋 설정 보는 소스입니다.

<?php
$id = mysql_connect('localhost', 'id', 'pass');
$res = mysql_query("SHOW LOCAL VARIABLES LIKE 'character_set%'", $id );
while ($p = mysql_fetch_row($res)) {
    echo "$p[0] : $p[1]<BR>";
}
?>

일단 my.cnf 의 mysqld 블럭에

skip-character-set-client-handshake
default-character-set=euckr

를 추가하는것으로 해결.