MySQL ibdata1 파일 축소 및 제거하는 방법 총정리

ibdata1 파일이란? 왜 문제가 될까요?

MySQL에서 InnoDB 스토리지 엔진을 사용하다 보면 시간이 지날수록 ibdata1 파일이 끝없이 커지는 현상을 겪게 됩니다. 더 큰 문제는, 이 파일은 한 번 커지면 자체적으로 줄어들지 않는다는 점입니다. 이는 MySQL의 오랜 특징 중 하나로, 많은 관리자를 곤란하게 만드는 요소입니다.

예를 들어 대용량 테이블을 삭제하거나 데이터베이스를 정리해도 디스크 공간은 회수되지 않고 ibdata1 파일 안에 그대로 남아 있게 됩니다.

ibdata1 파일을 실제로 축소하는 방법

ibdata1 파일을 축소하는 유일한 방법은 모든 데이터베이스를 삭제하고, InnoDB 관련 파일을 제거한 뒤, mysqldump로 백업해 둔 데이터를 다시 불러오는 것입니다. 작업 순서는 다음과 같습니다.

1. mysqldump 명령으로 모든 데이터베이스를 백업합니다.
2. MySQL 서버를 중지합니다.
3. ibdata1, ib_logfile0, ib_logfile1 등 InnoDB 파일을 삭제합니다.
4. 아래 설명할 innodb_file_per_table 옵션을 활성화합니다.
5. MySQL 서버를 다시 시작합니다.
6. 백업해 둔 덤프 파일을 이용해 데이터를 복원합니다.

주의: 작업 전에는 반드시 전체 백업을 완료한 후 진행하세요. 파일을 삭제하면 기존 데이터는 복구할 수 없습니다.

테이블별 개별 파일 저장(innodb_file_per_table) 설정하기

근본적인 해결책은 각 테이블과 인덱스를 별도의 파일로 저장하도록 MySQL을 구성하는 것입니다. 이렇게 하면 테이블을 삭제할 때 해당 파일도 함께 제거되어 디스크 공간이 즉시 회수됩니다.

이 기능은 MySQL 5.6.6 버전부터 기본적으로 활성화되어 있습니다. 따라서 최신 버전을 사용한다면 별도의 설정 없이 바로 사용할 수 있습니다.

서버가 테이블마다 개별 파일을 사용하도록 설정하려면 my.cnf(또는 Windows 환경에서는 my.ini) 파일을 수정해야 합니다.

MySQL 5.6.6 미만 버전인 경우

사용 중인 MySQL 버전이 5.6.6보다 낮다면 my.cnf 파일의 [mysqld] 섹션에 다음 옵션을 직접 추가해야 합니다.

[mysqld]
    innodb_file_per_table = 1

설정을 추가한 후 MySQL 서버를 재시작하면, 이후 생성되는 모든 InnoDB 테이블은 .ibd 확장자를 가진 개별 파일로 저장됩니다. 이를 통해 ibdata1 파일의 무분별한 증가를 예방하고, 테이블 단위의 공간 관리가 훨씬 수월해집니다.