InnoDB 로 변경하기

MySQL(혹은 MariaDB)에서 DB 엔진을 선택할 수 있는데, 많이 쓰는 것이 MyISAM과 InnoDB 이다. 게시판을 많이 쓰는 사이트에서는 MyISAM을 써도 되지만 쇼핑몰에서는 InnoDB를 쓰는 것이 좋다. ( 두 엔진 비교 : - http://chiccoder.tistory.com/24 )

이 글에서는 MyISAM을 InnoDB로 바꾸는 방법을 보여 줄 것이다. (CentOS 기준)

1. 전체 작업 방식
 가) db dump  

작업 시작 전에 가장 중요한 것은 작업이 잘못되었을 때 되돌릴 수 있는 상태를 만들어 놔야 한다. 아래 명령어로 DB 전체를 백업 받는다. 

위 명령어는 MySQL 사용자를 지정하지 않았다. root로 할 경우 아래와 같이.  

* 만약 http가 돌고 있는 중이라면 누군가가 접속하여 DB에 접근할 수도 있으니 http는 작업하는 동안 잠시 중지시켜두는 것이 좋다.

 나) 아래와 같이 my.cnf 변경 (etc/my.cnf 혹은 etc/my.cnf.d/server.cnf)
 

요즘은 모두 InnoDB로 설정되어 있는데, innodb_file_per_table=ON 이 설정이 없으면 ibdata 파일이 무척 커진다. 이 문서를 작성하려고 InnoDB로 변경한 이유도 ibdata 파일이 3GB 이상으로 커졌기 때문이다.  기본적으로 위 내용과 같이 설정을 하면 쇼핑몰에서는 큰 문제가 없을 것이다.

innodb_file_per_table=ON  : 테이블마다 ibd 파일을 만든다. 이 설정이 없으면 ibdata  파일이 무척 커지니 꼭 확인해야 한다.

innodb_buffer_pool_size = 512M : 버퍼 사이즈를 설정.

innodb_additional_mem_pool_size = 20M : pool이 full 되면 추가로 증설할 메모리.

innodb_flush_log_at_trx_commit = 2 

innodb_log_file_size = 100M : ib_logfile0 파일의 크기를 정함. 굳이 100MB까지 설정하지 않아도 됨.

innodb_log_buffer_size = 2M  

innodb_lock_wait_timeout = 50

innodb_flush_method = O_DSYNC : 이 부분은 찾아보니 아래의 O_DIRECT 대신에 O_DSYNC를 선택하면 좋다고 하여.

#innodb_flush_method = O_DIRECT

max_connections = 500

query_cache_size = 128M : Query Cache를 설정했는데 128MB 사이즈로. 이 부분이 매우 효과가 컸다.

innodb_data_file_path = ibdata1:100M;ibdata2:100M:autoextend   : ibdata 파일을 100MB로 설정했고 용량이 넘칠 경우 ibdata2를 쓰도록 했다.

innodb_autoextend_increment = 10M : 용량이 더 넘치면 10MB 단위로 증가하도록 설정. 

 다) 변환 작업 

아래 작업들은 1의 나)를 설정하지 않은 채 진행하였을때 ibdata 파일이 매우 큰 상태일때 진행. 

  - ibdata, log 파일 삭제
  - sql 파일 수정 : %s/MyISAM/InnoDB/c   혹은 sed -e ‘s/ENGINE=MyISAM/Engine=InnoDB/g’ mysql.dump > mysqlnew.dump
    - 기존 DB 삭제
    - 새 DB 생성

 라) mysql restart

당연하지만 위 명령어는 root 상태에서 실행.      이렇게 실행하면 ibdata 파일과 log가 깔끔하게 정리되어 있다.

 마) db restore 

이제 1의 가)에서 백업 받았던 자료를 되돌린다. 


2. 부분 작업 방식
1의 경우는 DB 전체로 백업하고 InnoDB로 바꾸는 과정이고 지금 2의 경우는 개별적으로 처리하는 것이다. 

 가) alter table  

mysql 접속 상태에서 테이블명을 하나씩 바꾼다. 물론 이 경우에도 http 를 중지시킨 상태에서 진행하는 것이 좋다. 

 나) optimize table
     - https://dev.mysql.com/doc/refman/5.5/en/optimize-table.html
그리고 위 링크에 따라서 테이블을 최적화해 준다. 

이 문서는 주관적으로 작업을 하여 기록하였으니 무작정 따라하기보다는 참고문서들을 읽고 신중하게 진행하기 바란다. (백업은 항상 필수) 



참고문서
 - http://www.33gram.com/mysql-myisam%EC%9D%84-innodb%EB%A1%9C-%EB%B3%80%EA%B2%BD%ED%95%98%EA%B8%B0/
 - http://www.mysqlkorea.com/gnuboard4/bbs/board.php?bo_table=community_03&wr_id=2159
 - https://sir.kr/pg_tip/12705
 - http://chiccoder.tistory.com/24
 - http://okky.kr/article/194468
 - http://cwscool.tistory.com/182
 - https://dev.mysql.com/doc/refman/5.5/en/optimize-table.html
 - https://dev.mysql.com/doc/refman/5.7/en/converting-tables-to-innodb.html