Computer/MySQL

mysql 백업/복구

알찬돌삐 2012. 8. 10. 16:03

 

최근들어 mysql 서버이전을 하면서 캐릭터셋 문제에 봉착하는 사람들이 많아졌다.
그런 분들을 위해 과정을 정리해 봤다 도움이 되었으면 한다.
아래의 정리는 경험을 바탕으로 적었기에 문제는 없을거 같지만 모든 환경을 테스트한것이 아니기에
이게 정답이다라고 할수는 없을것이기에 부족한 부분은 조언을 주셨으면 한다.

보통 3.x대 또는 4.x대에서 오랫동안 사이트를 운영하다 이번에 5.x 로 업하시는 분들이 이런 문제에 많이
봉착한다. 3.x 나 4.x 에서는 국내에 호스팅 환경이 euc-kr, euckr 이 대세였기에...
하지만 요즘은 utf8을 기본 지원하고 extra character set 으로 euckr 을 구축하는게 호스팅사들이 많은거 같다.

Type A 기본 캐릭터셋 euckr 서버에서 기본 캐릭터셋 utf8 서버로의 이전
    1. euckr => euckr 로의 이전
    2. euckr => utf8 로의 이전
Type B 기본 캐릭터셋 utf8 서버에서 기본 캐릭터셋 euckr 서버로의 이전
    3. utf8 => euckr 로의 이전
    4. utf8 => utf8 로의 이전

1. euckr => euckr 로의 이전
    1) mysqldump 를 이용하여 mysql의 sql문을 dump를 받는다.
    2) backup.sql 이란 화일을 받았다면 EditPlus 같은 프로그램으로 최상단에 "set names euckr;" 입력한다.
        Create 문에 "Type=???;" 또는 "ENGINE=???;"를 "Type=??? DEFAULT CHARSET=euckr;" 또는
        "ENGINE=??? DEFAULT CHARSET=euckr;" 로 변경한다.
        EditPlus 에서는 Ctrl + H 로 변경창이 쓴다.
    3) mysql -u user -p < backup.sql
    4) db connection 을 맺는 화일또는 공통설정화일에 아래의 쿼리를 먼저 서버로 날린다.
        "set names euckr;"
        보통 웹프로그램은 별도의 화일을 하나로 두어 include 같은 방식을 취한다.
  5) 위와같이 정의를 함으써 utf8서버에서도 euckr 을 사용할수 있게 되는것이다.

        현재 변환 작업(euckr=>utf 베이스의 euckr)에서 "Data too long for column"이와 같은 오류는
        utf8 베이스의 mysql 서버에 euckr 데이터가 utf8로 입력되어 나오는 오류이다.
        그것을 예방하는것이 "set names euckr" 이며
        아래의 3가지 옵션을 변경하는 것이다.
        character_set_client    euckr
        character_set_connection    euckr
        character_set_results    euckr
        참고로 예전 Mysql 3.x 대에서 euc-kr 이라고 했었던 환경에서는 한글은 varchar(10)에서
        5글자 밖에 들어가지 않았다. 즉 한글자가 2개의 char 값을 차지 했는데...
        최근 버젼들은 euckr 환경이라도 varchar(10)에 한글 10자가 모두 들어간다.

        이전테스트를 하다보니 "샆"이란 문자가 입력이 되질 않았다.
        OS의 문제인지 mysql 의 문제인지 모르지만 국내 주소에 포스코 ?? 샾은 오류가 나서 "삽"으로
        바꿨다. 포스코?? 샵 아파트 주민분들에게 욕들어 먹는건 아닐런지 ㅡㅡ;;

2. euckr => utf8 로의 이전
    1) mysqldump 를 이용하여 mysql의 sql문을 dump를 받는다.
    2) backup.sql 이란 화일을 받았다면 EditPlus 같은 프로그램으로 최상단에 "set names utf8;" 입력한다.
        Create 문에 "Type=???;" 또는 "ENGINE=???;"를 "Type=??? DEFAULT CHARSET=utf8;" 또는
        "ENGINE=??? DEFAULT CHARSET=utf8;" 로 변경한다.
        EditPlus 에서는 Ctrl + H 로 변경창이 쓴다.
        그리고 EditPlus에서 저장할때 utf-8 형식으로 저장을 하자
  3) mysql -u user -p < backup.sql
    4) db connection 을 맺는 화일또는 공통설정화일에 아래의 쿼리를 먼저 서버로 날린다.
      "set names utf8;"

        "set names utf8;" 쿼리가 별도로 필요없는 경우도 있는데 이경우는 리눅스 계열이며
        서버 OS 캐릭터셋 자체가 UTF-8인 경우이다. 윈도우의 경우에는 DB와 웹이 UTF8로 설정되었다고
        하더라도 실제 커넥션을 맺어서 확인해 보면 latin1 이 설정되어 있는 경우가 많다.
        아래는 PHP를 통해 실제 윈도우서버(mysql utf8로 설정)에서
        character set 관련 설정을 출력해본 예제이다.
        <?
        $connect = mysql_connect('localhost','user명','user비밀번호');
        $DBresult = mysql_query("show variables like 'char%'",$connect);
        $rows = mysql_affected_rows();
        for ($i=0; $i<$rows; $i++) {
            mysql_data_seek($DBresult,$i);
            $result = mysql_fetch_array($DBresult);
            echo "$result[0] : $result[1]<BR>";
        }

        echo "<BR>";
        mysql_query("set names utf8",$connect);

        $DBresult = mysql_query("show variables like 'char%'",$connect);
      $rows = mysql_affected_rows();
      for ($i=0; $i<$rows; $i++) {
          mysql_data_seek($DBresult,$i);
          $result = mysql_fetch_array($DBresult);
          echo "$result[0] : $result[1]<BR>";
      }

        결과는 아래와 같았다 물론 이와 다를수도 있다. 다만 한번쯤 default character 가 utf8 이라도
        위의 소스로 한번쯤 확인해보는게 좋다.
        자신이 utf8로 설정했다고 하면서 실제로 utf8이 아닌경우도 상당히 많다.
        사실은 저도 이것때문에 삽질을 좀 했다는....

        character_set_client : latin1
        character_set_connection : latin1
        character_set_database : utf8
        character_set_filesystem : binary
        character_set_results : latin1
        character_set_server : utf8
        character_set_system : utf8
        character_sets_dir : C:\\Program Files\\MySQL\\MySQL Server 5.0\\share\\charsets\\

        character_set_client : utf8
        character_set_connection : utf8
        character_set_database : utf8
        character_set_filesystem : binary
        character_set_results : utf8
        character_set_server : utf8
        character_set_system : utf8
        character_sets_dir : C:\\Program Files\\MySQL\\MySQL Server 5.0\\share\\charsets\\

3. utf8 => euckr 로의 이전
  1) mysqldump 를 이용하여 mysql의 sql문을 dump를 받는다.
  2) backup.sql 이란 화일을 받았다면 EditPlus 같은 프로그램으로 최상단에 "set names euckr;" 입력한다.
        Create 문에 "Type=???;" 또는 "ENGINE=???;"를 "Type=??? DEFAULT CHARSET=euckr;" 또는
        "ENGINE=??? DEFAULT CHARSET=euckr;" 로 변경한다.
        EditPlus 에서는 Ctrl + H 로 변경창이 쓴다.
        그리고 EditPlus에서 저장할때 utf-8 형식이 아닌 기본형식으로 저장을 하자
  3) mysql -u user -p < backup.sql
  4) db connection 을 맺는 화일또는 공통설정화일에 아래의 쿼리를 먼저 서버로 날린다.
      "set names euckr;"
        최신버젼에서 Mysql 3.x 대 버젼의 euc-kr 로 이전을 한다면 그건 하지않는것이 ㅡㅡ;;
        그 이유는 위에서 언급한것처럼 한글의 글자 처리(2byte 처리)가 다르기 때문이다.
        그런 이유로 테이블을 다시 설정하거나 최악의 경우 다시 설계해야 하는 경우가 생긴다.


4. utf8 => utf8 로의 이전
  1) mysqldump 를 이용하여 mysql의 sql문을 dump를 받는다.
  2) backup.sql 이란 화일을 받았다면 EditPlus 같은 프로그램으로 최상단에 "set names utf8;" 입력한다.
      Create 문에 "Type=???;" 또는 "ENGINE=???;"를 "Type=??? DEFAULT CHARSET=utf8;" 또는
        "ENGINE=??? DEFAULT CHARSET=utf8;" 로 변경한다.
      EditPlus 에서는 Ctrl + H 로 변경창이 쓴다.
      그리고 UTF-8로 저장이 되어있지 않다면 EditPlus에서 저장할때 utf-8 형식으로 저장을 하자
  3) mysql -u user -p < backup.sql
  4) 기존의 "set names utf8"이 설정되어 있다면 상관없지만 없다면
        db connection 을 맺는 화일또는 공통설정화일에 아래의 쿼리를 먼저 서버로 날린다.
      "set names utf8;"
        위에서도 비슷하게 언급했듯이... show variables like 'char%' 쿼리를 통해
        character_set_client    utf8
        character_set_connection    utf8
        character_set_results    utf8
        이 부분이 다르게 설정했다면 꼭 "set names utf8;" 이부분이 필요하다.



※참고
보통 backup.sql 화일의 언어셋을 변환하는경우 EditPlus 나 그외 편집툴로 바로 바꾸는 경우도 있지만
화일이 큰 경우는 iconv 를 이용하는것도 괜찮다..
iconv -c -s -f $input_encording -t $output_encording $input_file > $output_file
 


var md5_norobot_key = '84506442dda92f2efef35c9d84cfea99'; // 글자수 제한 var char_min = parseInt(0); // 최소 var char_max = parseInt(0); // 최대

 
 
   칼솜   07-06-13 10:42 
와~ 전후좌우 다 통하는게 정리를 하셨네요 ^^
수고하셨습니다. 그런데 Type=MyISAM 이 4.1부터 ENGINE=MyISAM으로 바뀐것이라고
알고 있는데 확인해 주실수 있나요?
감사합니다 ^^
 
와~ 전후좌우 다 통하는게 정리를 하셨네요 ^^ 수고하셨습니다. 그런데 Type=MyISAM 이 4.1부터 ENGINE=MyISAM으로 바뀐것이라고 알고 있는데 확인해 주실수 있나요? 감사합니다 ^^
 
 
   전진하는아르고   07-06-13 10:43 
아 넵... 복사해서 붙여넣기를 했더니.. ㅋㅋㅋ
 
아 넵... 복사해서 붙여넣기를 했더니.. ㅋㅋㅋ
 
 
   Mersia   07-06-13 20:29 
ENGINE 로 변경이 된것은 맞으나 하위버전과의 호환성문제때문에 Type 도 먹힙니다.

5.x 이상에서는 덤프를 뜨면 ENGINE 로 나옵니다.
 
ENGINE 로 변경이 된것은 맞으나 하위버전과의 호환성문제때문에 Type 도 먹힙니다. 5.x 이상에서는 덤프를 뜨면 ENGINE 로 나옵니다.
 
 
   아쿠아™   07-06-13 20:41 
Type 이었다가 Engine 으로 바꼈어요.
하위버전 호완성을 위해서 걍 남겨둔거욤;;;;;;;
 
Type 이었다가 Engine 으로 바꼈어요. 하위버전 호완성을 위해서 걍 남겨둔거욤;;;;;;;
 
 
   blaer   07-06-14 00:48 
iconv랑 sed써서 변환하는 방법도 괜찮습니다.. ^^
 
iconv랑 sed써서 변환하는 방법도 괜찮습니다.. ^^
 
 
   낭망백수   07-06-14 12:08 
근데 한가지 의문점이...
서버가 utf-8 이냐 euc-kr 이냐는
한글을 담은 data field 가 utf-8이냐 euc-kr 이냐와는 상관없는것 아닌가요?

가장 간단하게 접근할 수 있는 방법은
이전의 해당 DB(DBMS 아님) 의 charset 이 무엇이냐를 판별한 다음 그걸 변경할 것이냐를 결정한 후,
덤프의 charset 을 변경하고,
새 DB의 charset 을 결정한 대로 다시 집어넣는 것이죠.

그러니까 해당 datafield 에만 최대한 집중하면 된다는 뜻입니다.
나머지는 mysql서버와 mysql클라이언트, 그리고 mysql서버가 돌아가는 시스템환경의
charset 인데 이건 웹어플리케이션에서 가져다 쓰는 data 와는 무관하지 않은가 생각하는 것입니다.

혹 제가 잘못 알고 있는 더욱 복잡한 상황이 있을수도 있겠습니다만,
간단하게 접근하면 훨씬 간단하게 해결할 수 있을것 같아서 커멘트 답니다.

꾸벅~!
 
근데 한가지 의문점이... 서버가 utf-8 이냐 euc-kr 이냐는 한글을 담은 data field 가 utf-8이냐 euc-kr 이냐와는 상관없는것 아닌가요? 가장 간단하게 접근할 수 있는 방법은 이전의 해당 DB(DBMS 아님) 의 charset 이 무엇이냐를 판별한 다음 그걸 변경할 것이냐를 결정한 후, 덤프의 charset 을 변경하고, 새 DB의 charset 을 결정한 대로 다시 집어넣는 것이죠. 그러니까 해당 datafield 에만 최대한 집중하면 된다는 뜻입니다. 나머지는 mysql서버와 mysql클라이언트, 그리고 mysql서버가 돌아가는 시스템환경의 charset 인데 이건 웹어플리케이션에서 가져다 쓰는 data 와는 무관하지 않은가 생각하는 것입니다. 혹 제가 잘못 알고 있는 더욱 복잡한 상황이 있을수도 있겠습니다만, 간단하게 접근하면 훨씬 간단하게 해결할 수 있을것 같아서 커멘트 답니다. 꾸벅~!
 
 
   전진하는아르고   07-06-14 14:51 
음... mysql 에 charset 관련 설정이 여러개가 있는데..
system database 이런건 솔찍히 중요하지 않습니다.
맞습니다.
데이터 백업시 중요시 되는건 백업sql데이터의 문서charset과 set names로 변경되는
character_set_client
character_set_connection
character_set_results
이 옵션만 정확하면 됩니다.

제가 이렇게 구차하게 나눠둔건..
default캐릭터셋 환경이 같고 캐리터셋이 같은경우 거의 백업 및 복구시 문제될게 없지만
다른 캐릭터셋에 관해서라면 조금씩 문제가 상이하기 때문입니다.

그리고 아시는분들이야 제가 정리한것보다 더 잘하실수 있겠지만..
보통 캐릭터셋 질문하시는분들을 보면 아직 서버운영에 미숙한 분들이 꽤 있습니다.
그 분들을 위해 좀더 상세히 나누고 예상될수 있는 문제들을 적어놓은 것입니다.

훨씬 간단하게 정리못해서 죄송합니다  ^^;;
능력이 거기까지 밖에 TT
 
음... mysql 에 charset 관련 설정이 여러개가 있는데.. system database 이런건 솔찍히 중요하지 않습니다. 맞습니다. 데이터 백업시 중요시 되는건 백업sql데이터의 문서charset과 set names로 변경되는 character_set_client character_set_connection character_set_results 이 옵션만 정확하면 됩니다. 제가 이렇게 구차하게 나눠둔건.. default캐릭터셋 환경이 같고 캐리터셋이 같은경우 거의 백업 및 복구시 문제될게 없지만 다른 캐릭터셋에 관해서라면 조금씩 문제가 상이하기 때문입니다. 그리고 아시는분들이야 제가 정리한것보다 더 잘하실수 있겠지만.. 보통 캐릭터셋 질문하시는분들을 보면 아직 서버운영에 미숙한 분들이 꽤 있습니다. 그 분들을 위해 좀더 상세히 나누고 예상될수 있는 문제들을 적어놓은 것입니다. 훨씬 간단하게 정리못해서 죄송합니다 ^^;; 능력이 거기까지 밖에 TT
 
 
   낭망백수   07-06-15 00:43 
참, 궁금한게 앞서느라 잘 정리해두신것 수고하셨다는 말씀을 못드렸네요. ^^;
수고하셨구요. 많은 분들에게 좋은 밑거름이 될 것임에는 틀림없는 자료입니다. ㅎㅎ;;;

꾸벅~!

 

이 글은 스프링노트에서 작성되었습니다.

.

'Computer > MySQL' 카테고리의 다른 글

mysql 캐릭터셋  (0) 2012.08.10
mysql 변수  (0) 2012.08.10
mysql procedure function  (0) 2012.08.10
mysql innodb  (0) 2012.08.10
mysql event  (0) 2012.08.10