MySQL 5.0 으로 업그레이드 할 때 캐릭터셋 문제 해결

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

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

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 데이터가 혼합되어 있습니다. 새 데이터베이스도 캐릭터셋을 혼합해서 사용 할 예정이고, 기존의 코드는 고치고 싶지 않습니다. 서버 세팅만으로의 마이그레이션을 원하는거죠.

먼저 덤프를 뜹니다. 모든 디비가 한 캐릭터셋이라면 mysqldump -A 로 떠도 되겠지만, 섞여 있다면 캐릭터셋이 다른 디비끼리 따로 뜨는게 나을겁니다.

database 생성부분을 캐릭터셋에 맞게 손봐줍니다.

한 캐릭터셋인 경우: 덤프 뜬 sql 파일을 수정하여 뒤에 CHARACTER SET 옵션을 붙입니다. euckr 의 경우 vi 에서 다음 명령어로 가능합니다.

:%s/^\(CREATE DATABASE.*\);/\1 

               DEFAULT CHARACTER SET euckr COLLATE euckr_korean_ci;/ 

나중에 ALTER 하는 방법도 있긴 하지만, 테이블 생성될때 디비 캐릭터셋을 따라가기 때문에 테이블 하나하나 고쳐주느니 디비를 고쳐주는 편이 편할겁니다

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

sql을 디비에 부어줍니다.

한꺼번에 뜬 경우, --default-character-set 옵션을 지정하고 붓습니다.

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

디비끼리 뜬 경우, 역시 지정하지만 각 디비에 맞게 지정하고 붓습니다.

mysql -p db1 --default-character-set=euckr 

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

my.cnf를 수정합니다. 요넘이 중요..
[mysqld] 항목에 다음을 추가합니다.

init_connect=SET character_set_results=NULL 

이거 안하면 지맘대로 latin1 로 변환하죠. 이거가 일반적으로 가장 큰 문제입니다.
요넘을 NULL 로 지정해주면 엄한 변환을 하지 않습니다.

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

If you do not want the server to perform any conversion of result sets,

 set character_set_results to NULL: 

뭐 요점은 character_set_results 를 NULL 로 잡는거죠.. 여기저기 검색해봤지만 이 방법을 쓰는 분은 찾을 수 없더군요..

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

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

• 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 로 하면 그대로 뽑아줍니다.

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

저렇게 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>";
}
?>

 

etc/my.cnf 파일에 설정을 해주야 할꺼 같네요..

mysql> show variables like 'c%';

나오는 캐릭터셋이 같은지 보시고 틀리면 수정해주세요.
[client]
port            = 3306
Socket          = /tmp/mysql.sock
ini_connect = set names euckr
default_character_set=euckr

# The MySQL server
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-locking
init_connect = set names euckr
default_character_set=euckr
language=korean