Mysql InnoDB – 테이블 당 테이블스페이스 사용하기

여러분은 각각의 InnoDB 테이블과 인덱스 파일을 각자의 파일에 저장할 수가 있다. 이러한 특성을 “다중 테이블스페이스 (multiple tablespaces)”라고 하는데, 그 이유는 실제로 각각의 테이블이 자신만의 테이블스페이스를 가지고 있기 때문이다.

 

다중 테이블스페이스를 사용하는 것은 특정 테이블을 물리적으로 구분된 여러 개의 디스크에 분산 하기를 원하거나 또는 남아 있는 InnoDB 테이블에 대한 인터럽트 없이 하나의 테이블 백업을 리스토어 하고자 하는 사용자에게 편리하다.

 

아래의 라인을 my.cnf의 [mysqld] 섹션에 추가하면 다중 테이블스페이스를 활성화 시킬 수가 있다.

 

[mysqld]

innodb_file_per_table

 

서버를 재 구동하면, InnoDB는 새롭게 생성된 각 테이블을 그 테이블이 속한 데이터베이스 디렉토리에 있는 자신의 tbl_name.ibd 파일에 저장한다. 이런 동작은 MyISAM 스토리지 엔진과 유사하지만, MyISAM은 테이블을 데이터 파일 tbl_name.MYD 과 인덱스 파일 tbl_name.MYI로 구분하게 된다. InnoDB의 경우, 데이터와 인덱스는 .ibd 파일에 함께 저장된다.tbl_name.frm 파일 역시 생성되기는 한다.

 

만일 innodb_file_per_table 라인을 my.cnf에서 삭제한 후에 서버를 재 구동시키면,InnoDB는 공유 테이블스페이스 파일 내부에 테이블을 다시 생성한다.

 

innodb_file_per_table는 테이블 생성에만 영향을 주며, 현재 존재하는 테이블에는 접속을 하지 않는다. 만일 여러분이 이 옵션을 사용해서 서버를 구동시킨다면, 새로운 테이블은 .ibd 파일을 사용해서 생성되지만, 공유 테이블스페이스에 존재하는 테이블에는 계속해서 접속할 수가 있다. 만일 이 옵션을 제거하고 서버를 재 구동시킨다면, 새로운 테이블은 공유 테이블스페이스에서 생성되지만, 다중 테이블스페이스를 사용해서 생성된 모든 테이블에 대한 접속을 계속해서 할 수가 있다.

 

Note: InnoDB는 항상 공유 테이블스페이스가 필요한데, 그 이유는 자신의 내부 데이터 디렉토리와 운도 로그 (undo log)를 그곳에 저장하기 때문이다. .ibd 파일만으로는 InnoDB가 연산하기에는 부족한 것이다.

 

Note: 여러분은 MyISAM 테이블 파일을 사용해서 하는 것과 같이.ibd 파일을 데이터베이스 디렉토리 간에 자유롭게 이동시킬 수는 없다. 그 이유는 InnoDB 공유 테이블스페이스에 저장되어 있는 테이블 정의문에는 데이터베이스 이름이 포함되어 있기 때문이며, 또한 InnoDB는 반드시 트랜젝션 ID와 로그 시퀀스 번호의 일관성을 보존해야 하기 때문이다.

 

.ibd 파일과 이에 연관된 테이블을 하나의 데이터베이스에서 다른 데이터베이스로 이동시키기 위해서는, RENAME TABLE 명령문은 사용한다:

 

RENAME TABLE db1.tbl_name TO db2.tbl_name;

 

.ibd 파일의 “클린 (clean)” 백업을 가지고 있다면, 이것을 MySQL 을 설치할 때 리스토어 할 수가 있다:

 

1. 아래의 ALTER TABLE 명령문을 입력한다:

 

ALTER TABLE tbl_name DISCARD TABLESPACE;

 

Caution: 이 명령문은 현재의 .ibd 파일을 삭제한다.

 

2. 백업 파일 .ibd을 적당한 데이터 베이스 디렉토리에 다시 넣는다.
3. 아래의 ALTER TABLE 명령문을 입력한다:

 

ALTER TABLE tbl_name IMPORT TABLESPACE;

 

위의 문장에서 나오는 “클린 (clean)” .ibd 파일 백업은 다음과 같은 의미를 갖는다:

• .ibd 파일에는 트랜젝션에 의해서 실행되지 않는 수정 사항은 없다.
• .ibd 파일에는 병합되지 않은 삽입 버퍼 엔트리가 없다.
• .ibd 파일에서 모든 삭제-표시 인덱스 레코드가 깨끗이 정리되었다.
• Mysqld는 .ibd 파일의 모든 수정된 페이지를 버퍼 풀에서 파일로 플러시 했다.

여러분은 아래의 방법을 사용해서 클린업 백업 .ibd 파일을 만들 수가 있다:

 

1. mysqld 서버의 모든 활동을 중지 시키고 모든 트랜젝션을 실행한다.
2. SHOW ENGINE INNODB STATUS가 데이터베이스에는 현재 동작 중인 트랜젝션이 더 이상 존재하지 않음을 보여주고, InnoDB의 메인 쓰레드 상태가 Waiting for server activity가 될 때까지 기다린다. 그렇게 되면, 여러분은 .ibd 파일을 복사할 수 있다.

 

.ibd 파일의 클린 복사를 만드는 또 다른 방법은 상용 InnoDB Hot Backup 툴을 사용하는 것이다:

 

1. InnoDB Hot Backup을 사용해서 InnoDB 설치를 백업한다.
2. 백업상에서 두 번째 mysqld 서버를 실행하고, 이 서버가 백업에 있는 .ibd 파일을 클린업 하도록 만든다.