[MySQL] InnoDB 데이타 파일 추가(system tablespace)

개요

초기 MySQL은 InnoDB 데이타 파일을 기본적으로 하나만 생성을 한다. 이렇게 운영하다 데이터가 많이 쌓일 경우 Disk I/O 문제가 발생할 소지가 있다. 이를 완화하기 위해 데이터파일을 여러개 만들어 Disk I/O를 분산시켜야 할 경우가 있다. 또는 파일시스템 Full 로 인하여 추가적인 데이타 파일을 생성이 필요할 경우도 발생한다. 여기서는 다중 데이터파일을 생성하는 방법에 대해서 알아 보도록 한다.

구성환경

mysql 5.7.26 / CentOS 7.6

 

데이타파일 추가 절차 요약

1. 모든 InnoDB 테이블 Dump 백업(mysql 스키마 포함)

2. mysql server 중지

3. ibddata,ib_log 포함한 모든 테이블스페이스 삭제(.ibd) - mysql 스키마에 위치한 ibd 파일(테이블) 삭제

4. InnoDB 파일에 대한 .frm 파일 삭제

5. 신규 테이블스페이스에 대한 데이타 파일 설정

6. mysql server 시작

7. 덤프파일 복원(import)

 

예) 데이타파일 추가 절차

현재 구성 정보

 1) my.cnf 파일에서 데이타파일 확인

[mysqld] innodb_file_per_table = OFF innodb_data_home_dir=/usr/local/mysql/data innodb_data_file_path=ibdata1:12M:autoextend  => 현재 1개의 파일로 구성됨

Or 아래 명령어와 확인

mysql> show variables like 'innodb_data_file_path%' ; +-----------------------+------------------------------------+ | Variable_name         | Value                              | +-----------------------+------------------------------------+ | innodb_data_file_path | ibdata1:12M:autoextend | +-----------------------+------------------------------------+ 1 row in set (0.00 sec) mysql> select tablespace_name,file_name,file_type from information_schema.files ; +------------------+-------------------------------+------------+ | tablespace_name  | file_name                     | file_type  | +------------------+-------------------------------+------------+ | innodb_system    | /usr/local/mysql/data/ibdata1 | TABLESPACE |   => system tablespace | innodb_temporary | /usr/local/mysql/data/ibtmp1  | TEMPORARY  | | ts01             | ./ts01.ibd                    | TABLESPACE |            => general tablespace +------------------+-------------------------------+------------+ 4 rows in set (0.00 sec) mysql>

 2) 데이터베이스 : hoyadb, hoyadb2 

 3) general tablespace( CREATE TABLESPACE 문으로 생성한 테이블스페이스) 

   - general tablespace : ts01 ( 파일명 ts01.ibd)

   - general tablespace 조회

mysql> select tablespace_name,file_name,file_type from information_schema.files ; +------------------+-------------------------------+------------+ | tablespace_name  | file_name                     | file_type  | +------------------+-------------------------------+------------+ | innodb_system    | /usr/local/mysql/data/ibdata1 | TABLESPACE | | ts01              | ./ts01.ibd                    | TABLESPACE | | innodb_temporary | /usr/local/mysql/data/ibtmp1  | TEMPORARY  | +------------------+-------------------------------+------------+ 4 rows in set (0.00 sec) mysql>

 

1. 모든 InnoDB 테이블 Dump 백업

 1.1 InnoDB엔진을 사용하는 테이블의 스키마 확인 및 백업

mysql> SELECT TABLE_SCHEMA,TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE  ENGINE='InnoDB'; +--------------------+---------------------------+ | TABLE_SCHEMA       | TABLE_NAME                | +--------------------+---------------------------+ | information_schema | COLUMNS                   | | information_schema | EVENTS                    | | information_schema | OPTIMIZER_TRACE           | | information_schema | PARAMETERS                | | information_schema | PARTITIONS                | | information_schema | PLUGINS                   | | information_schema | PROCESSLIST               | | information_schema | ROUTINES                  | | information_schema | TRIGGERS                  | | information_schema | VIEWS                     | | hoyadb             | t1                        | | hoyadb             | ts1_t1                    | | hoyadb2            | ts1_hoya2_t1              | | hoyadb2            | ts1_t1                    | | mysql              | engine_cost               | | mysql              | gtid_executed             | | mysql              | help_category             | | mysql              | help_keyword              | | mysql              | help_relation             | | mysql              | help_topic                | | mysql              | innodb_index_stats        | | mysql              | innodb_table_stats        | | mysql              | plugin                    | | mysql              | server_cost               | | mysql              | servers                   | | mysql              | slave_master_info         | | mysql              | slave_relay_log_info      | | mysql              | slave_worker_info         | | mysql              | time_zone                 | | mysql              | time_zone_leap_second     | | mysql              | time_zone_name            | | mysql              | time_zone_transition      | | mysql              | time_zone_transition_type | | sys                | sys_config                | +--------------------+---------------------------+ 34 rows in set (0.02 sec) mysql> exit shell> mysqldump --databases hoyadb  --tables t1 ts1_t1 -uroot -p  >  hoyadb.dmp shell> mysqldump --databases hoyadb2 -- tables ts1_hoya2_t1 ts1_t1  -uroot -p  > hoyadb2.dmp

 

 1.2 mysql 스키마 백업

mysql> SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='mysql' and ENGINE='InnoDB'; +---------------------------+ | TABLE_NAME                | +---------------------------+ | engine_cost               | | gtid_executed             | | help_category             | | help_keyword              | | help_relation             | | help_topic                | | innodb_index_stats        | | innodb_table_stats        | | plugin                    | | server_cost               | | servers                   | | slave_master_info         | | slave_relay_log_info      | | slave_worker_info         | | time_zone                 | | time_zone_leap_second     | | time_zone_name            | | time_zone_transition      | | time_zone_transition_type | +---------------------------+ 19 rows in set (0.00 sec) mysql>

shell> mysqldump --databases mysql --tables engine_cost gtid_executed .....  time_zone_transition_type  -uroot -p  > mysql_schema_innodb.dmp

 

2. mysql server 중지

shell> /etc/init.d/mysqld.server stop

 

3. ibddata,ib_log 포함한 모든 테이블스페이스 삭제(.ibd) - mysql 스키마에 위치한 ibd 파일(테이블) 삭제

 3.1 ibddata,ib_log 포함한 모든 테이블스페이스 삭제(.ibd)

shell> rm ibdata1 ib_logfile0 ib_logfile1  => 시스템 테이블스페이스 및 로그 파일 삭제 shell> rm ts01.ibd  => general tablespace 파일 삭제

 3.2 mysql 스키마에 위치한 .ibd, .frm 파일(테이블) 삭제

  - 취소선으로 되어 있는 파일 삭제(.frm, .ibd 파일 삭제)

shell> rm engine_cost.frm gtid_executed.frm ..... time_zone_transition_type.frm

 

4. InnoDB 테이블의 .frm 파일 삭제

shell> rm  t1.frm ts1_t1.frm       => hoyadb  스키마 .frm 파일 삭제 shell> rm ts1_hoya2_t1.frm ts1_t1   => hoyadb2  스키마 .frm 파일 삭제

 

5. 신규 테이블스페이스에 대한 데이타 파일 설정

 my.cnf 파일 수정

[mysqld] innodb_file_per_table = OFF innodb_data_home_dir=/usr/local/mysql/data innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend  => 데이타 파일 추가

 

6. mysql server 시작

shell> /etc/init.d/mysqld.server stop

 

7. 덤프파일 복원(import)

 7.1 mysql 스키마의  innodb 테이블 복구

shell> mysql mysql -uroot -p mysql < mysql_schema_innodb.dmp Enter password:

 7.2 general tablespace 생성

mysql> create tablespace  ts01 add datafile  'ts01.ibd' ;     Query OK, 0 rows affected (0.00 sec) mysql>

 7.3 데이터베이스 복구

shell> mysql mysql -uroot -p hoyadb < hoyadb.dmp  Enter password: shell> mysql mysql -uroot -p hoyadb2 < hoyadb2.dmp  Enter password:

 

8. 확인

mysql> select tablespace_name,file_name,file_type from information_schema.files ; +------------------+-------------------------------+------------+ | tablespace_name  | file_name                     | file_type  | +------------------+-------------------------------+------------+ | innodb_system    | /usr/local/mysql/data/ibdata1 | TABLESPACE | | innodb_system    | /usr/local/mysql/data/ibdata2 | TABLESPACE | | innodb_temporary | /usr/local/mysql/data/ibtmp1  | TEMPORARY  | | ts1              | ./ts01.ibd                    | TABLESPACE | +------------------+-------------------------------+------------+ 4 rows in set (0.00 sec) mysql>