Since MySQL version 4.1, InnoDB has provided two alternatives for how tables are stored on disk. You can create a new table and its indexes in the shared system tablespace, physically stored in the ibdata files. Or, you can store a new table and its indexes in a separate tablespace (a .ibd file). The storage layout for each InnoDB table is determined by the configuration parameter innodb_file_per_table at the time the table is created.
In MySQL 5.5 and higher, the configuration parameter innodb_file_per_table is dynamic, and can be set ON or OFF using the SET GLOBAL. Previously, the only way to set this parameter was in the MySQL option file (my.cnf or my.ini), and changing it required shutting down and restarting the server.
In MySQL 5.5 and higher, the configuration parameter innodb_file_per_table is dynamic, and can be set ON or OFF using the SET GLOBAL. Previously, the only way to set this parameter was in the MySQL option file (my.cnf or my.ini), and changing it required shutting down and restarting the server.
The default setting is OFF, so new tables and indexes are created in the system tablespace. Dynamically changing the value of this parameter requires the SUPER privilege and immediately affects the operation of all connections.
example
[root@test mysql]# /service/mysql/bin/mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.25-log zhongwc_DB
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database innotest;
Query OK, 1 row affected (0.00 sec)
mysql> show create database innotest;
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| innotest | CREATE DATABASE `innotest` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> use innotest
Database changed
mysql> create table innoper1 engine = innodb as select * from zwc.employee;
Query OK, 1000000 rows affected (5.99 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
mysql> create table innoper2 engine = innodb as select * from zwc.employee_myisam;
Query OK, 1000000 rows affected (5.04 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
mysql> exit
Bye
[root@test mysql]# pwd
/data/mysql
[root@test mysql]# cd innotest/
[root@test innotest]# ls -lh
total 113M
-rw-rw---- 1 mysql mysql 61 Jan 10 15:17 db.opt
-rw-rw---- 1 mysql mysql 8.5K Jan 10 15:17 innoper1.frm
-rw-rw---- 1 mysql mysql 56M Jan 10 15:18 innoper1.ibd
-rw-rw---- 1 mysql mysql 8.5K Jan 10 15:18 innoper2.frm
-rw-rw---- 1 mysql mysql 56M Jan 10 15:18 innoper2.ibd
mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)