[mySQL5.5] 09장. InnoDB

n  InnoDB Storage Engine

- ROW_FORMAT=DYNAMIC : , long column values are stored fully off-page
- ROW_FORMAT=COMPRESSED

 

1. 현재 기본 file format을 확인합니다.

root>show variables like 'innodb_file_format';

+--------------------+----------+

| Variable_name      | Value    |

+--------------------+----------+

| innodb_file_format | Antelope |

+--------------------+----------+

 

2. Barracuda 형식일때는 DYNAMIC 와 COMPRESSED 가 가능합니다.
그러므로, Antelope인 경우 생성하면 warning이 발생합니다. 하지만 , 테이블은 잘 생성됩니다.
root>CREATE TABLE name

      (column1 INT PRIMARY KEY)

      ENGINE=InnoDB

      ROW_FORMAT= DYNAMIC ;

Query OK, 0 rows affected, 2 warnings (0.00 sec)

 

3. 이번에는 . Barracuda형식으로 변경후 테이블을 생성해봅니다.

root>set  global innodb_file_format=barracuda;

Query OK, 0 rows affected (0.00 sec)

 

root>select @@innodb_file_format;

+----------------------+

| @@innodb_file_format |

+----------------------+

| Barracuda            |

+----------------------+

1 row in set (0.00 sec)

 

4. innodb_file_per_table을 ON으로 설정해야만 합니다.

root>set GLOBAL innodb_file_per_table=ON;

root>select @@innodb_file_per_table;

+-------------------------+

| @@innodb_file_per_table |

+-------------------------+

|                       1 |

+-------------------------+

 

5. 이번에는 테이블을 재 생성해 봅니다. Warning없이 아주 잘 생성됩니다.

root>drop table name;

 

root> CREATE TABLE name

 (column1 INT PRIMARY KEY)

 ENGINE=InnoDB

 ROW_FORMAT= DYNAMIC ;

Query OK, 0 rows affected (0.00 sec)

 

1 row in set (0.00 sec)

n  InnoDB System Tablespace관련 파라미터

 

1. System Tablespace 위치 및 파일의 크기 를 확인합니다.

root>show variables like '%innodb_data_%';

+-----------------------+------------------------+

| Variable_name         | Value                  |

+-----------------------+------------------------+

| innodb_data_file_path | ibdata1:10M:autoextend |            < ------기본크기 100M

| innodb_data_home_dir  |                        |

+-----------------------+------------------------+

 

2. 리두로그 그룹의 개수와 리두 로그 파일의 크기를 확인합니다.

root>show variables like '%innodb_log%';

+---------------------------+---------+

| Variable_name             | Value   |

+---------------------------+---------+

| innodb_log_buffer_size    | 8388608 |

| innodb_log_file_size      | 5242880 |           < ------기본크기 50M

| innodb_log_files_in_group | 2       |           < ------그룹은 2개

| innodb_log_group_home_dir | ./      |          < ------위치는 data 디렉토리

+---------------------------+---------+

root>show variables like '%innodb_file_per%';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| innodb_file_per_table | OFF   |

+-----------------------+-------+

1 row in set (0.00 sec)

 

 

 

n  Tablespace size 줄이는 방법

1. 현재 InnoDB 테이블을 조회해봅니다.
mysql>  select table_name,table_schema

    -> from tables where engine='innodb';

+-----------------+--------------+

| table_name      | table_schema |

+-----------------+--------------+

| geom            | test         |

| my_backup       | test         |

| name            | test         |

| City            | world_innodb |

| Country         | world_innodb |

| CountryLanguage | world_innodb |

+-----------------+--------------+

 

2. Innodb 테이블을 백업합니다.

  1) 백업을 위한 script를 생성합니다.

SELECT CONCAT("mysqldump -uroot -poracle  ",

                              TABLE_SCHEMA," ", TABLE_NAME, " >> ",

                              TABLE_SCHEMA,".bak.sql")

                              FROM TABLES WHERE engine='innodb'

                              INTO OUTFILE '/home/oracle/backup.sh';

 

-bash-3.2$ more backup.sh

mysqldump -uroot -poracle  test geom >> test.bak.sql

…..

mysqldump -uroot -poracle  world_innodb CountryLanguage >> world_innodb.bak.sql

   2) backup.sh을 실행하면, test.bak.sql  world_innodb.bak.sql이 생성됩니다.
-bash-3.2$ ./ backup.sh

-bash-3.2$ ls

backup.sh  test.bak.sql  world_innodb.bak.sql

3) more test.bak.sql의 내용을 확인해 보면,
DROP TABLE IF EXISTS `geom`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `geom` (

  `g` geometry DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;

 

--

-- Dumping data for table `geom`

--

 

LOCK TABLES `geom` WRITE;

/*!40000 ALTER TABLE `geom` DISABLE KEYS */;

INSERT INTO `geom` VALUES ( 

3. 서버를 shutdown합니다.

[root@EDYDR52P0 mysql]# /etc/init.d/mysql stop

Shutting down MySQL...                                     [  OK  ]

4. 현재 테이블스페이스 크기를 확인합니다.

[root@EDYDR52P0 mysql]# pwd

/var/lib/mysql

[root@EDYDR52P0 mysql]# ls -l ib*

-rw-rw---- 1 mysql mysql 18874368 Jan  5 21:32 ibdata1

-rw-rw---- 1 mysql mysql  5242880 Jan  5 21:32 ib_logfile0

-rw-rw---- 1 mysql mysql  5242880 Jan  4 17:17 ib_logfile1

 

5. 모든 테이블스페이 파일을 백업 후 삭제합니다.
[root@EDYDR52P0 mysql]# mv ibdata1 ibdata1.old

[root@EDYDR52P0 mysql]# mv ib_logfile0 ib_logfile0.old

[root@EDYDR52P0 mysql]# mv ib_logfile1 ib_logfile1.old

[root@EDYDR52P0 mysql]# rm –f  ib*

[root@EDYDR52P0 mysql]# ls  ib*

ibdata1.old  ib_logfile0.old  ib_logfile1.old

 

6. Innodb 테이블의 frm을 삭제합니다.world_innodb와 test디비에 대해 모두 삭제합니다.
[root@EDYDR52P0 mysql]# cd world_innodb/

[root@EDYDR52P0 world_innodb]# ls

City.frm  Country.frm  CountryLanguage.frm  db.opt

[root@EDYDR52P0 world_innodb]# rm –f*.frm

 

7. 새로운 테이블스페이크기를 설정합니다.
# vi /etc/my.cnf에서 [mysqld]로 이동합니다.

[mysqld]
innodb_data_file_path = ibdata1:10M;ibdata2:10M:autoextend< -----#을 uncomment, 최소크기가 10M로 2개

 

8. 서버를 시작합니다.

[root@EDYDR52P0 mysql]# /etc/init.d/mysql start

Starting MySQL.                                            [  OK  ]

 

mysql> set global innodb_file_per_table=ON;

 

9. dump 파일을 import(restore)합니다.
-bash-3.2$ ls *.sql

test.bak.sql  world_innodb.bak.sql

-bash-3.2$ mysql test < test.bak.sql

-bash-3.2$ mysql world_innodb < world_innodb.bak.sql

 

10. 테이블스페이스 파일 크기를 확인해 보면, 초기 크기로 10M로 줄어 있음을 알수 있답니다.

[root@EDYDR52P0 mysql]# ls -l ib*

-rw-rw---- 1 mysql mysql 10485760 Jan  5 22:54 ibdata1

-rw-rw---- 1 mysql mysql 10485760 Jan  5 22:52 ibdata2

-rw-rw---- 1 mysql mysql  5242880 Jan  5 22:54 ib_logfile0

-rw-rw---- 1 mysql mysql  5242880 Jan  5 22:52 ib_logfile1

 

11. world_innodb로 접속하여 , 데이터베이스가 정상 작동함을 확인해야 되겠지요?

$mysql

mysql> use world_innodb;

Database changed

mysql> show tables;

+------------------------+

| Tables_in_world_innodb |

+------------------------+

| City                   |

| Country                |

| CountryLanguage        |

+------------------------+

3 rows in set (0.00 sec)

mysql> select count(*) from City;

+----------+

| count(*) |

+----------+

|     4079 |

+----------+

1 row in set (0.00 sec)