mysql 의 데이터를 백업받는 방법은 여러가지가 있다.

# mysql 에서 제공하는 mysqldump 를 이용하는 방법
phpMyAdmin 같은 툴을 이용하는 방법
mysql console 에서 INTO OUTFILE 등으로 백업하는 방법

# mysqldump 같은 명령도 php 내의 system 함수등을 이용해 할 수도 있다.
또한 INTO OUTFILE  mysql console 명령도 php 에서 쿼리로 날려서 할 수도 있다.
( http://www.php-mysql-tutorial.com/perform-mysql-backup-php.php )

# 여기에서 언급하고자 하는 것은
PHP 에서 mysql_query 명령으로  INTO OUTFILE 쿼리를 이용해 백업받는 부분이다.

이 방법을 사용할 때 주의할 부분을 정리하고자 한다.

1. 먼저 mysql console 접속할 USER 의 File_Priv 권한이 'Y' 이어야 한다.
  update user set File_Priv = 'Y' where User = '{유저이름}' ;  등으로 바꿔준다.
  설정을 바꾼 후 바로 적용이 되지 않고, # mysqladmin reload  등으로 설정을 적용시킨다.

2. 파일이 저장되는 경로 및 퍼미션(권한)을 확인한다. 확실한 방법은 절대경로를 써준다.
 예) /data/backup/xxxx.sql
 %주의% 절대경로를 써주지 않으면, mysql 데이터가 있는 경로에 저장이 된다.( /var/lib/mysql/{DB명} )
권한(퍼미션) 부분은 파일이 mysql 의 user/group 으로 저장이 되기 때문에
보통(nobody , www-data) 의 권한으로 된 경로는 other 권한에 쓰기 권한이 있어야 한다.
안되어 있다면, # chmod 777 {경로}  처럼 바꾸어 준다.

3. 적당한 쿼리값을 INTO OUTFILE '{저장할 파일명}'  형식으로 저장한다.
  mysql connection 을 생성 후에 (in PHP)

$query = " SELECT * INTO OUTFILE '$backupFile'  fields terminated by '|'  FROM $tableName ; " ;
mysql_query( $query ) ;

$backupFile , $tableName 은 적당한 값으로 설정.

4. 해당 백업파일로 복구할때는 LOAD DATA INFILE
  백업시   fields ...  에 써 넣은 옵션을 그대로 써준다.

$query = " LOAD DATA INFILE '$backupFile'  INTO TABLE $tableName  fields terminated by '|' ; " ;
mysql_query( $query ) ;

이때도 절대경로를 써주는 것이 좋다.
그냥 xxx.sql 등의 파일이름만 적으면 /var/lib/mysql/{DB}/xxx.sql 을 찾게 된다.


# 참조 :
http://dev.mysql.com/doc/refman/5.0/en/select.html
http://dev.mysql.com/doc/refman/5.0/en/load-data.html

예전 1,2회에 이어 3편.

이번 주제는 "Mysql Dump 시 charset - latin1 문제" 와 몇가지 팁

# 주의점 : 4.1.x 이상버전은 내부데이터는 utf8 로 처리된다.

현재 4.1.x 버전에서 dump 시 latin1 문제.
 - 내부는 utf8 로 처리되는데, 테이블은 latin1 이고, 데이터는 euckr 로 저장된 경우
 - 4.0.x 의 경우 내부는 latin1 이고 데이터는 euckr 인 경우 -> 4.1 로 이전시 문제.
 - 예전 4.0.x 버전에서 4.1.x 로 업그레이드시에 꼬여버린 상황인듯.

mysqldump --default-character-set=latin1 {DB} > dump.sql  형식
으로 백업해야 하는데, 그냥 mysqldump {DB} > dump.sql 한 경우에 문제가 발생한다.
그냥 dump 한 경우 utf8 로 인식하고 dump 되기 때문에 charset 이 꼬이는(?) 듯 하다.
그래서, 이것을 그냥 mysql {DB} < dump.sql 로 복구하면 데이터가 깨져서 저장된다.

그래서, 그냥 넣고 다시 latin1 으로 dump 를 받는다.
 1. mysqldump --default-character-set=latin1 {DB} > dump.sql 으로 제대로(?) dump 받는다.
 2. latin1 으로 된 부분을  euckr/utf8 등으로 변경 ( set names euckr , charset 도 euckr 로 변경 )
 3. 다시 restore 했을때는 깨지지 않는다.


# 이때 4.1.x 이상 -> 4.0.x 로 백업/복구 한다면...
  * mysqldump --create-options --compatible=mysql40 db_name > dump_file
      o --compatible=mysql40  옵션을 넣어서 백업하면 4.0.x 형태로 dump 된다.
  * euckr 로 맞추어야 한다면..
      o --default-character-set=euckr 를 추가해 준다.

# 참고
  * http://www.hackszine.com/blog/archive/2007/05/mysql_database_migration_latin.html
  * http://www.phpschool.com/gnuboard4/bbs/board.php?bo_table=tipntech&wr_id=37225
  * http://www.duppio.com/wk/index.php/Change_the_character_set_from_latin1_to_utf8_in_MySql
  * http://www.phpschool.com/gnuboard4/bbs/board.php?bo_table=qna_db&wr_id=83799

ps. 요약식이라 정리가 덜 되어 이해하기 힘들 듯.
  다음에는 mysql 4.1.x 버전과 5.x 버전차이로 인해 my.cnf 의 옵션이 다르게 먹히는 문제를 살펴볼 예정.
  정확히 php 에서 mysql client 접속시의 문제.

이전편 1회에 이어서..

처리순서를 간단히 정리하자면.(1회의 언급한 내용)

1. 업그레이드 전 dump (euckr , utf8 확인)
2. mysql 업그레이드 ... ( 3.x , 4.0.x -> 4.1.x or 5.x )
   
3. set names euckr 명시 (euckr 환경인경우)
   
4. table 의 Engine , Charset 를 조정.
5. 덤프 데이터 restore
6. 확인.

# 위 업그레이드 절차를 좀더 자세히 설명한다.

1. 업그레이드 전 dump (euckr , utf8 확인)
   • 명령은 간단한다.(추가적인 옵션등은 검색)
     
     • mysqldump {DB_name} > DB_name.sql
     • mysqldump {DB_name} -u {user_name} -p{password} > DB_name.sql
     • mysqldump -F -n --add-drop-table {DB_name}> DB_name.sql
       
       
2. mysql 업그레이드 ... ( 3.x , 4.0.x -> 4.1.x or 5.x )
   • 각 배포판, APM 배포판 별로 업그레이드 한다.
     • 업그레이드 방법은 별도로 찾아본다.
   • 위 단계에서 dump 파일이 있으니, 안심해도 된다.
   • 다시 롤백을 해야할 상황은 없길 바란다.
     
     
3. set names euckr 명시 (euckr 환경인경우)
   • dump 파일을 euckr , utf8 에 맞게 iconv 같은 것으로 변환을 하는 경우도 있는데, 굳이 필요 없다.
   • 이전 환경이 euckr 이면 dump 파일 맨 위에 "set names euckr" , utf8 이면 "set names utf8" 이라고 한줄 추가해 준다.
     
     
4. table 의 Engine , Charset 를 조정.
   • 4.0.x 를 덤프하면 create table 부분에  " ) Type=MyISAM " 이라고 되어 있을 것이다
   • 덤프파일에서 Engine , Charset 을 적절히 바꾼다.
     • " ) ENGINE=InnoDB DEFAULT CHARSET=euckr " 형태로 바꾸어 준다.
     • 각각 InnoDB/MyISAM , euckr/utf8 로 적절한 환경으로 바꾸어 준다.
       
       
5. 덤프 데이터 restore
   • 위 수정된 덤프파일을 저장한다.
   • mysql {DB_name} < DB_name.sql 으로 복구한다.
     
     
6. 확인(my.cnf 등의 옵션 조정)
   • 데이터가 이상없는지 확인.
   • euckr 환경인 경우
     • my.cnf 의 mysqld 항목에 init_connect = 'set names euckr' 를 추가한다.
   • utf8 환경이라도 init_connect = 'set names utf8' 이라고 넣어주는 것이 좋다.
     • php 에서 위 설정을 해주지 않으면 latin1 으로 기본 설정된다.
   • chartset 에 관한 부분은 추후 더 정리할 예정.

# 업그레이드시 주의사항

1. 테이블 charset 변경시 : alter table {테이블명} convert to character set utf8 명령으로 하면.
   
   • multibyte 를 사용하는 db 의 경우 필드 값이 반으로 줄어 버림
   • 그래서 dump 로 처리하는 것이 좋음
   • 덤프파일의 create table 항목의 charset 을 변경하는 방식으로 처리.
     
2. 4.0.x 에서 dump 시 euckr , utf8 인지 확인
   • euckr 인 경우 덤프파일의 맨위에 set names euckr , utf8 은 set names utf8 를 삽입
   • 이부분만 주의하면 무리없이 진행가능.
     
3. php 에서는 기본이 latin1 으로 잡힘
   • my.cnf 에 default-character-set 을 설정확인.
     
   • init_connect ='set names euckr' 최종 값이 설정됨.
   • init_connect 가 php 의 설정에 영향을 미침.
4. dump 파일의 ENGINE , CHARSET 을 변경가능 (InnoDB , MyISAM) , (euckr , utf8)

ps. 좀더 자세한 설명이지만, 배포판별 mysql 업그레이드 방법, 세부 mysql 옵션등의 설명은 생략하였다.
   더 자세한 설명은 메뉴얼을 참조한다.

많은 웹사이트들이 APM - Apache + PHP + Mysql 조합으로 운영되고 있다.

Apache 는 1.x 버전에서 2.x 로의 버전 업그레이드가 되었고,
PHP 는 4.x , 5.x 버전으로 업그레이드가 되고 있다. 이제 6 버전이 개발중에 있다.

mysql 의 버전은 3.x 버전을 거쳐, 4.0.x , 4.1.x , 5.x  버전으로 업그레이드가 되었다.
그런데, mysql 의 버전 업그레이드를 하면서 내부적인 변화때문에 데이터의 호환성, 변경 문제를 겪게 되었다.
크게 (3.x ,4.0.x)  와  (4.1.x ,5.x) 의 두 그룹 사이의 업그레이드에 주의하면 된다.

한글 환경을 사용하는 국내에서는 charset(euckr , utf8) 에 조금만 주의를 하면
어렵지 않게 업그레이드 할 수 있다.

약간은 철지난 이슈이긴 하지만, 몇가지 주의사항 및
쉽게 업그레이드 할 수 있는 방법을 정리하고자 한다.

먼저 자신의 환경을 파악한다. euckr 환경인지, utf8 환경인지.
업그레이드시 euckr 환경으로 할지, utf8 환경으로 할지 등을 점검하고 업그레이드 한다.

가장 흔한 조합은
1. euckr 환경에서 사용하다가 업그레이드 euckr 환경을 계속 유지.
2. euckr 환경에서 utf8 환경으로 업그레이드
3. 테이블이 latin1 형식에서 euckr , utf8 환경으로 업그레이드
이 정도일 듯 하다.

가장 쉽고, 최선의 방법은 dump 후 restore 하는 방법이다.(당연하다고? ^^ )
아무튼 무작정 업그레이드 시도하기전에 백업은 필수!!

처리순서를 간단히 정리하자면.

1. 업그레이드 전 dump (euckr , utf8 확인)
2. mysql 업그레이드 ... ( 3.x , 4.0.x -> 4.1.x or 5.x )
   
3. set names euckr 명시 (euckr 환경인경우)
   
4. table 의 Engine , Charset 를 조정.
5. 덤프 데이터 restore
6. 확인.

구체적인 방법은 다음회에...(2회 보기)

ps. 총 3-4 회에 걸쳐 정리할 예정.