MySQL holds table space separately innodb_file_per_table

In MySQL, what is the role of innodb_file_per_table parameters, in fact, many friends do not know, today we look at this MySQL alone storage table space Innodb_file_per_table parameters after the detailed explanation you understand OH.

MySQL can use a variety of engines, when we choose to use the InnoDB engine, ibdata1 This file will become more and more large over time, occupy a large amount of disk space.

So, what do you keep in ibdata1, and why is it getting bigger? First we confirm the structure of the IBDATA1, IBDATA1 is the common table space InnoDB, the default configuration is to put all the table space into the ibdata1, so that the ibdata1 file unlimited growth needs to be released.

MySQL's innodb_file_per_table parameter controls the way the table space is stored, by configuring the Innodb_file_per_table parameter to store all the tablespace in ibdata1 instead of using the exclusive tablespace to store the tablespace separately.

Here's a look at the specific operation.

1) Export all data in the database

# mysqldump-u root-p--all-database >/tmp/all-database.dump

2) Delete data from the database

# mysql-u root-p mysql> drop database dbname;

3) Stop MySQL

# Service mysqld Stop

4) Delete the Ibdata1 file

# mv/var/lib/mysql/ibdata1/tmp # mv/var/lib/mysql/ib_logfile0/tmp # mv/var/lib/mysql/ib_logfile1/tmp

5) Configuration Innodb_file_per_table

# vi/etc/my.cnfinnodb_data_home_dir =/var/lib/mysql= ibdata1:1g;ibdata2:200m: autoextendinnodb_file_per_table

Opens the exclusive tablespace and specifies that the IBDATA1 size is 1g,ibdata2 size 200M and expands automatically.

6) Start MySQL

# Service mysqld Start

7) Import data

Import the database that you just brought out to the database.

# mysql-u Root-p </tmp/all-database.dump

Opening the exclusive table space does not mean that you do not need to ibdata1, because the following data is stored in ibdata1.

InnoDB Metadata for tables
Buffer
Undo Log

MySQL holds table space separately innodb_file_per_table