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

Arcy의 이미지

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

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

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]
";
}
?>

댓글

keedi의 이미지

유용한 팁이군요! :-)
^_^)b

---------------------------
Smashing Watermelons~!!
Whatever Nevermind~!!

Kim Do-Hyoung Keedi

----
use perl;

Keedi Kim

codebank의 이미지

좋은 팁이네요.

저는 set names euckr을 이용합니다.
제 경우에는 db연결을 단하나의 파일에 만들어 놓았기 때문에 일일이 찾아서 수정하는
수고를 덜 수가 있었죠.
------------------------------
좋은 하루 되세요.

------------------------------
좋은 하루 되세요.

정태영의 이미지

http://b.mytears.org/2006/08/362

예전에 저도 비슷한 문제로 좀 고생한 적이 있어서 -_ㅜ

--
오랫동안 꿈을 그리는 사람은 그 꿈을 닮아간다...

http://mytears.org ~(~_~)~
나 한줄기 바람처럼..

오랫동안 꿈을 그리는 사람은 그 꿈을 닮아간다...

http://mytears.org ~(~_~)~
나 한줄기 바람처럼..

dhunter의 이미지

저는 그냥 기존의 워크어라운드로 해결했습니다.

no-auto-rehash
default-character-set=euckr
 
[mysqld]
old_passwords=1
skip-character-set-client-handshake
default-character-set=euckr
 
썩 좋은 방법은 아니지만, 제로보드가 암호화를 MySQL password 함수를 써서 어찌 할 수도 없고...
일단 여기에서는 블로그와 제로보드가 동시에 굴러가는지 유니코드와 EUC-KR 이 공존해야 해서 이런 솔루션이 존재하는거군요. 참고하겠습니다 ^_^
---
from bzImage
It's blue paper

from bzImage
It's blue paper

김정균의 이미지

대충 읽어보니 상당히 어렵군요 :-)
제가 KLDP data 들을 mysql 5 로 migration 할 때는, my.cnf 에

character-set-client-handshake = 0

하나 설정해 놓고, database/table 생성시에 Default Charset = 'euc-kr' 옵션 주고.. dump 된 data 를 밀어 넣은 것으로 끝났습니다만.. ^^;

Arcy의 이미지

예전 포스트대로 하면 데이터 집어넣을때 문제가 생겨, 방법을 바꾸었습니다.

하는 김에 백업과 리스토어도 좀 간단하게 정리했습니다. 이 방법으로 마이그레이션 한것도 잘 돌아가는군요.

humansoft의 이미지

독립 서버를 한 대 사용 중이고,
서버에 MySQL 4.0.25를 사용 중인데, 한국어 ko-euc-kr 로 셋팅되어 있고, 다수의 DB가 돌고 있습니다.

이번에 새로 솔루션을 하나 설치하였는데, 부득이 utf8버전을 설치하였습니다.
새로 추가된 DB를 phpMyadmin에서 열어보면 한글 부분이 깨져 보입니다. 웹 솔루션에서는 한글 부분이 아무 문제는 없는데, phpMyadmin 를 이용한 테이블 작업시에 오류가 생길 것이 예상되어 불안합니다.

utf8 버전의 DB를 다른 서버로 옮기는 수밖에 없는 것인지요?
같이 혼용하여 사용하면서 웹솔루션에서도 바로 나타나고, phpMyadmin 에서도 한글이 바로 나타나게 할 수도 있는 것인지요?

Arcy의 이미지

UTF-8 데이터베이스라도, 해당 디비 세팅을 euc-kr 로 하면 해당 디비는 euc-kr 로 보입니다.

ALTER DATABASE `디비` DEFAULT CHARACTER SET euckr COLLATE euckr_korean_ci
ALTER TABLE `테이블` DEFAULT CHARACTER SET euckr COLLATE euckr_korean_ci

등의 명령어로 바꾸실 수 있고, phpMyAdmin 의 "테이블 작업" 탭에서도 가능합니다.

데이터베이스가 골라진 상태에서 "테이블 작업" 탭을 누르면 데이터베이스의 캐릭터셋을,
테이블이 골라진 상태에서 "테이블 작업" 탭을 누르면 테이블의 캐릭터셋을 바꿀 수 있습니다.

이 캐릭터셋이 제대로 지정되지 않은 경우엔 정렬이나 글자 수 때문에 문제가 생길 수 있으니 주의하세요.

익명 사용자의 이미지

감사합니다ㅠㅠ

댓글 달기

Filtered HTML

  • 텍스트에 BBCode 태그를 사용할 수 있습니다. URL은 자동으로 링크 됩니다.
  • 사용할 수 있는 HTML 태그: <p><div><span><br><a><em><strong><del><ins><b><i><u><s><pre><code><cite><blockquote><ul><ol><li><dl><dt><dd><table><tr><td><th><thead><tbody><h1><h2><h3><h4><h5><h6><img><embed><object><param><hr>
  • 다음 태그를 이용하여 소스 코드 구문 강조를 할 수 있습니다: <code>, <blockcode>, <apache>, <applescript>, <autoconf>, <awk>, <bash>, <c>, <cpp>, <css>, <diff>, <drupal5>, <drupal6>, <gdb>, <html>, <html5>, <java>, <javascript>, <ldif>, <lua>, <make>, <mysql>, <perl>, <perl6>, <php>, <pgsql>, <proftpd>, <python>, <reg>, <spec>, <ruby>. 지원하는 태그 형식: <foo>, [foo].
  • web 주소와/이메일 주소를 클릭할 수 있는 링크로 자동으로 바꿉니다.

BBCode

  • 텍스트에 BBCode 태그를 사용할 수 있습니다. URL은 자동으로 링크 됩니다.
  • 다음 태그를 이용하여 소스 코드 구문 강조를 할 수 있습니다: <code>, <blockcode>, <apache>, <applescript>, <autoconf>, <awk>, <bash>, <c>, <cpp>, <css>, <diff>, <drupal5>, <drupal6>, <gdb>, <html>, <html5>, <java>, <javascript>, <ldif>, <lua>, <make>, <mysql>, <perl>, <perl6>, <php>, <pgsql>, <proftpd>, <python>, <reg>, <spec>, <ruby>. 지원하는 태그 형식: <foo>, [foo].
  • 사용할 수 있는 HTML 태그: <p><div><span><br><a><em><strong><del><ins><b><i><u><s><pre><code><cite><blockquote><ul><ol><li><dl><dt><dd><table><tr><td><th><thead><tbody><h1><h2><h3><h4><h5><h6><img><embed><object><param>
  • web 주소와/이메일 주소를 클릭할 수 있는 링크로 자동으로 바꿉니다.

Textile

  • 다음 태그를 이용하여 소스 코드 구문 강조를 할 수 있습니다: <code>, <blockcode>, <apache>, <applescript>, <autoconf>, <awk>, <bash>, <c>, <cpp>, <css>, <diff>, <drupal5>, <drupal6>, <gdb>, <html>, <html5>, <java>, <javascript>, <ldif>, <lua>, <make>, <mysql>, <perl>, <perl6>, <php>, <pgsql>, <proftpd>, <python>, <reg>, <spec>, <ruby>. 지원하는 태그 형식: <foo>, [foo].
  • You can use Textile markup to format text.
  • 사용할 수 있는 HTML 태그: <p><div><span><br><a><em><strong><del><ins><b><i><u><s><pre><code><cite><blockquote><ul><ol><li><dl><dt><dd><table><tr><td><th><thead><tbody><h1><h2><h3><h4><h5><h6><img><embed><object><param><hr>

Markdown

  • 다음 태그를 이용하여 소스 코드 구문 강조를 할 수 있습니다: <code>, <blockcode>, <apache>, <applescript>, <autoconf>, <awk>, <bash>, <c>, <cpp>, <css>, <diff>, <drupal5>, <drupal6>, <gdb>, <html>, <html5>, <java>, <javascript>, <ldif>, <lua>, <make>, <mysql>, <perl>, <perl6>, <php>, <pgsql>, <proftpd>, <python>, <reg>, <spec>, <ruby>. 지원하는 태그 형식: <foo>, [foo].
  • Quick Tips:
    • Two or more spaces at a line's end = Line break
    • Double returns = Paragraph
    • *Single asterisks* or _single underscores_ = Emphasis
    • **Double** or __double__ = Strong
    • This is [a link](http://the.link.example.com "The optional title text")
    For complete details on the Markdown syntax, see the Markdown documentation and Markdown Extra documentation for tables, footnotes, and more.
  • web 주소와/이메일 주소를 클릭할 수 있는 링크로 자동으로 바꿉니다.
  • 사용할 수 있는 HTML 태그: <p><div><span><br><a><em><strong><del><ins><b><i><u><s><pre><code><cite><blockquote><ul><ol><li><dl><dt><dd><table><tr><td><th><thead><tbody><h1><h2><h3><h4><h5><h6><img><embed><object><param><hr>

Plain text

  • HTML 태그를 사용할 수 없습니다.
  • web 주소와/이메일 주소를 클릭할 수 있는 링크로 자동으로 바꿉니다.
  • 줄과 단락은 자동으로 분리됩니다.
댓글 첨부 파일
이 댓글에 이미지나 파일을 업로드 합니다.
파일 크기는 8 MB보다 작아야 합니다.
허용할 파일 형식: txt pdf doc xls gif jpg jpeg mp3 png rar zip.
CAPTCHA
이것은 자동으로 스팸을 올리는 것을 막기 위해서 제공됩니다.