InnoDB engine stand-alone table space innodb_file_per_table

Students who have used MySQL have just started to touch the MyISAM table engine, the database of this engine will create three files: Table structure, table index, table data space. We can migrate a database directory directly to a different database and work properly. When you use InnoDB, however, everything changes.

InnoDB default will store all database InnoDB engine table data in a shared space: ibdata1, so feel uncomfortable, add and delete the database, ibdata1 file does not automatically shrink, the backup of a single database will become a problem. You can usually only export data using mysqldump and then import to resolve this issue.

In the MySQL configuration file [mysqld] section, add the innodb_file_per_table parameter.

You can modify InnoDB as a stand-alone tablespace pattern, and each table in each database generates a data space.

Stand-alone table space:

Advantages:

1. Each table has a self-contained table space.
2. The data and indexes for each table will exist in the table space themselves.
3. You can implement a single table to move through different databases.
4. Space can be recycled (except for the drop table operation, the meter is not able to recycle)

A) The drop table operation automatically reclaims the tablespace, if for statistical analysis or a daily value table, delete a large amount of data can pass: ALTER TABLE TableName ENGINE=INNODB;

b) The use of Turncate table for Innodb-plugin InnoDB also shrinks the space.

c) For tables that use stand-alone table spaces, no matter how they are deleted, the fragmentation of the tablespace does not affect performance too much and there is a chance to process it.

Disadvantages:

Single table increased too large, such as more than 100 g.

Conclusion:

Shared tablespace has few advantages over insert operations. Others do not have a separate table space to perform well. When you enable stand-alone table spaces, make a reasonable adjustment: Innodb_open_files.

InnoDB hot Backup (cold) Table space CP will not face a lot of useless copy. And the InnoDB hot backup and table space management commands can be used to achieve single-move.

1.innodb_file_per_table settings. Open method:
Set under [Mysqld] in MY.CNF
Innodb_file_per_table=1

2. Check to see if it is turned on:
Mysql> Show variables like '%per_table% ';

3. Turn off the exclusive table space
Innodb_file_per_table=0 closing a separate table space
Mysql> Show variables like '%per_table% ';

More MySQL Innodb engine optimization http://linux.chinaunix.net/techdoc/database/2009/04/28/1109193.shtml

InnoDB engine stand-alone table space innodb_file_per_table