MYSQL ibdata1 파일 삭제해도 될까? 용량 줄이는 방법

mysql 5.5 버전 이상에서 innoDB 엔진을 사용하면 .ibd 파일이 쌓입니다. 이 파일은 mysql의 테이블 스페이스, 인덱스 등 데이터를 포함하는 파일입니다. ibd 파일은 용량이 줄어들지 않는 게 특징입니다. 예를 들어, table이라는 테이블에 1GB의 데이터를 INSERT하면 table.ibd 파일의 용량은 1GB +입니다. 이때 table 테이블에서 500MB의 데이터를 지우더라도 table.ibd의 용량은 동일하게 1GB입니다. 테이블 스페이스가 여전히 존재하기 때문입니다.

ibdata1 파일이란?

ibdata1은 innoDB 테이블에 대한 메타 데이터를 포함하는 파일입니다. 테이블 스페이스, 인덱스 등을 포함합니다. 이와 함께 ib_logfile0, ib_logfile1 파일 역시 innoDB 스토리지 엔진에 필요한 파일들입니다. redo(리두) 로그라고 불리며 이 파일들은 순환으로 덮어쓰기를 하기 때문에 정해진 최대 용량에서 더 이상 늘어나진 않습니다.

ibdata1 파일 삭제해도 될까?

결론부터 말하면 ibdata1, *.ibd 등의 파일은 삭제하면 안 됩니다. 삭제 시 mysql 실행이 안 되는 문제가 발생할 수 있습니다. 그 말은 DB를 이용하는 서비스의 중단을 의미합니다.

ibdata1 파일 용량 줄이는 방법

※ 참고 : 해당 내용은 제가 필요해서 알아보며 정리한 기록입니다. 저도 아직 충분히 검증해보지 못했으니 현업에서 서비스되고 있는 제품에 바로 활용하지는 마시기 바랍니다.

 

먼저 확인할 것은 mysql의 버전입니다. 5.6.6 이하인 경우 기본 값으로 ibdata1 파일에 모든 메타 데이터가 저장됩니다. ibdata1의 용량이 클 가능성이 높습니다.

 

예를 들어, table_name1 테이블에 10GB, table_name2 테이블에 10GB의 데이터가 저장됐다면 ibdata1 파일 용량은 20GB + 입니다. 이 상황에서 table_name2를 삭제해도 ibdata1 용량은 그대로입니다. 테이블 공간이 여전히 존재하기 때문입니다. 이처럼 DB 데이터를 삭제하고도 남은 공간을 회수할 수 없는 문제가 있습니다. 그래서 단일 ibdata1에 모든 메타 데이터를 저장하는 방식은 좋지 않습니다.

1. innodb_file_per_table 설정

메타 데이터를 각 테이블 별로 나누어 저장하는 방법이 있습니다. /etc/my.cnf 파일을 수정하면 됩니다.

# vi /etc/my.cnf
[mysqld]
innodb_file_per_table

이 설정은 mysql 5.6.6 이상부터 기본 값입니다. 위 설정을 저장하고 mysql을 다시 시작하면 신규 테이블 생성 시 *.ibd 형태로 개별 파일이 생성됩니다.

mysql 재기동을 하지 않고 설정을 저장하려면 mysql에서 아래 쿼리를 수행하는 방법도 있습니다.

mysql> set global innodb_file_per_table=1;

2. ibdata1 개별 파일로 추출

mysql 프롬프트를 통해 테이블 데이터를 개별 파일로 추출할 수 있습니다. 예를 들어 DB_table이라는 데이터베이스에 있는 table_name 테이블을 추출하려면 아래와 같이 진행합니다.

mysql> use DB_table

mysql> optimize table_name;

이렇게 하면 DB_table이라는 폴더가 생기고 그 안에 table_name.ibd라는 파일이 생성됩니다. 그러나 추출을 해도 ibdata1의 용량은 줄어들지 않습니다.

3. ibdata1 공간 회수

목표는 ibdata1의 사용되지 않는 공간을 회수하는 것입니다. 과정을 간단히 요약하면 백업 → DB 삭제 → ibdata1 삭제 → DB 복원 → ibdata1 재생성입니다.

 

1. mysql 서비스를 중지합니다.

service mysqld stop

 

2. 데이터베이스를 백업합니다.

문제 발생 시 복원을 위해 때문에 데이터베이스 백업은 필수입니다.

mkdir /backup 

cd /var/lib 

cp -r mysql /backup

 

3. 모든 데이터베이스의 mysqldump 백업을 진행합니다.

mysqldump란 mysql 데이터베이스를 백업하는 도구입니다. *.sql 형태의 파일을 생성해서 복원 시 해당 파일을 수행하는 형태입니다. 모든 테이블에 대한 삭제, 생성, 삽입 명령이 포함됩니다.

mysqldump -u root -ptmppassword --all-databases > /backup/all-database.sql

 

4. 데이터베이스를 삭제합니다.

information_schema와 mysql 데이터베이스는 삭제하면 안 됩니다.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| DB_table           |
| mysql              |
+--------------------+

mysql> drop database DB_table;

 

5. ibdata, ib_logfile 파일을 삭제합니다.

cd /var/lib/mysql/

rm ibdata1

rm ib_logfile0

rm ib_logfile1

 

6. mysql을 시작합니다.

service mysqld start

 

7. 백업한 mysqldump 파일로 모든 데이터베이스 복원합니다.

mysql -u root -ptmppassword --all-databases < /backup/all-database.sql

 

8. 데이터베이스가 시작되면서 ibdata1 파일이 알아서 생성됩니다.

이제 ibdata1은 사용되지 않는 공간이 회수되어 용량이 줄어든 상태입니다.