컴퓨터 / Computer

InnoDB 로 변경하기

공유지기 0 1,898 2017.03.21 01:31

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

번호 제목 글쓴이 날짜 조회 추천 비추
35 XP 서비스팩 1, 2, 3 공유지기 05.21 24 1 0
34 구글 번역기 설정 페이지 공유지기 04.21 246 0 0
33 PC용 리눅스 운영체제 Ubuntu 공유지기 2013.07.14 1453 0 0
32 [게임] 탱크 슈팅 게임 BZFlag 공유지기 2013.06.01 1720 0 0
31 이윰빌더에서 아미나빌더로 이전 공유지기 04.13 228 0 0
30 아미나빌더(Amina Builder) Upgrade : (아미나 1.4.3 + 그누 5.1.2) -> ( … 공유지기 04.09 302 0 0
29 phpmyadmin 에서 테이블이 편집안되는 현상 공유지기 02.19 429 0 0
28 그림 파일 확장자를 판단하여 읽어들이는 코드 공유지기 02.06 422 0 0
27 웹 방화벽 Mod_security 설치 댓글+3 공유지기 01.18 522 0 0
26 CentOS 5.x 대에서 yum 문제 해결하기 공유지기 01.18 478 0 0
25 php 에서 number_format, filter_var 사용하기 공유지기 2017.12.24 534 0 0
24 MySQL/MariaDB 5.1 에서 5.5 업그레이드시 strict mode 해제 방법 공유지기 2017.12.23 568 0 0
23 CentOS 7에서 특정 ip와 특정 포트만 허용하기 공유지기 2017.12.20 612 0 0
22 phpmyadmin 설치하기 공유지기 2017.12.20 494 0 0
21 CentOS 7 방화벽 중 80포트 개방 공유지기 2017.12.20 736 0 0
20 CentOS 에 MariaDB 설치하기 공유지기 2017.12.20 516 0 0
19 NTPD를 이용한 시간 동기화 공유지기 2017.12.19 614 0 0
18 iptables 를 활용하여 ip 블럭하기 공유지기 2017.11.26 677 0 0
17 프라이버시 보호 브라우저 10선 공유지기 2017.11.03 775 0 0
16 XWidget 공유지기 2017.09.17 914 0 0