컴퓨터 / Computer

InnoDB 로 변경하기

공유지기 0 5,587 2017.03.21 01:31

0_fivEmSUN_cafc0046fceae53820e6e11ed19a3a0d3034ec3c.jpg 


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

 

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

 

 


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

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

 

   mysqldump --all-databases -p > mysql.dump 

 

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

   mysqldump -u root --all-databases -p > mysql.dump 


 

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

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

 

[mysqld]

#innodb_file_per_table 

innodb_file_per_table=ON

innodb_buffer_pool_size = 512M

innodb_additional_mem_pool_size = 20M

innodb_flush_log_at_trx_commit = 2

innodb_log_file_size = 100M

innodb_log_buffer_size = 2M

innodb_lock_wait_timeout = 50

innodb_flush_method = O_DSYNC

#innodb_flush_method = O_DIRECT

max_connections = 500

query_cache_size = 128M

innodb_data_file_path = ibdata1:100M;ibdata2:100M:autoextend

innodb_autoextend_increment = 10M


 


요즘은 모두 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

 service mysql restart 

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


 마) db restore 

   mysql -u root -p < mysql.dump

 

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


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


 가) alter table  

mysql>       ALTER TABLE table_name ENGINE=InnoDB;

 

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

 

Comments

맨위로↑