디스크 여유 공간 부족할 때 MySQL 데이터 용량 줄이기

앞서 MySQL 데이터 용량 줄이는 방법 을 작성했는데 추가로 디스크 용량이 많이 부족한 상태에서 대안으로 활용해 볼만한 방법에 대해 작성한다.

데이터를 delete 하고 남아있는 테이터를 원격지에 백업한 뒤 truncate 후 다시 insert 하는 방법이다.

이것도 테이블 용량이 크면 시간 많이 걸리는 건 어쩔 수가 없는데 delete 하는 데이터 양이 많고 복원하는 데이터가 작으면 나름 써볼만 하다.

SQL 백업으로 MySQL ibd 파일 용량 줄이기

이전 글과 마찬가지로 TEST 테이블에 1.2GB 데이터를 쌓아놨다.

[root@localhost BigDB]# ls -lh | grep TEST
-rw-rw----. 1 mysql mysql 4.5K Jan 21 20:50 TEST.frm
-rw-rw----. 1 mysql mysql 1.2G Jan 21 20:56 TEST.ibd

절반의 데이터를 delete 한다.

MariaDB [BigDB]> DELETE FROM TEST WHERE TEXT LIKE '%B';
Query OK, 512000 rows affected (11.661 sec)

ibd 파일 용량은 여전히 1.2GB다.

[root@localhost BigDB]# ls -lh | grep TEST
-rw-rw----. 1 mysql mysql 4.5K Jan 21 20:50 TEST.frm
-rw-rw----. 1 mysql mysql 1.2G Jan 21 23:03 TEST.ibd

이제 남아있는 테이블을 백업할 건데 백업 파일을 다른 컴퓨터에 저장하기 위해 HeidiSQL을 이용한다.

데이터베이스 우클릭 > 데이터를 SQL로 내보내기 를 클릭한다.

데이터는 INSERT (신규 데이터를 삽입) 으로 설정하고 저장 경로를 정한 뒤에 내보내기를 클릭한다.

생성된 백업 파일을 확인하고 테이블을 truncate 한다.

truncate table TEST;

테이블을 비웠으면 이제 백업한 sql을 실행한다.

파일 > SQL 파일 실행 을 클릭하여 백업한 sql을 실행한다.

복원이 완료되면 결론적으로 용량이 절반으로 줄어든다.

[root@localhost BigDB]# ls -lh | grep TEST
-rw-rw----. 1 mysql mysql 4.5K Jan 22 23:04 TEST.frm
-rw-rw----. 1 mysql mysql 588M Jan 23 00:11 TEST.ibd

뻔한 말이긴하지만 이 방법도 마냥 추천하긴 어렵다. 원격으로 수행하기 때문에 속도가 느린 편인데 예제로 600MB insert 하는데도 2분 쯤 걸렸다.