MySQL separately stores the table space Innodb_file_per_table.

MySQL can use multiple engines. When we choose to use the InnoDB engine, the ibdata1 file will become larger and larger over time, occupying a large amount of disk space.

So what are stored in ibdata1 and why are they getting bigger and bigger? First, let's check the ibdata1 structure. ibdata1 is a common tablespace of InnoDB. By default, it stores all tablespaces in ibdata1. Therefore, the infinite growth of ibdata1 files requires release.

The Innodb_file_per_table parameter of MySQL controls the storage mode of tablespaces. By configuring the Innodb_file_per_table parameter, you can store all tablespaces in ibdata1 instead of using exclusive tablespaces to separately store tablespaces.

The following describes the specific operations.

1) export all data in the database

# Mysqldump-u root-p -- all-database & gt;/tmp/all-database.dump

2) delete data in 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) configure innodb_file_per_table

# Vi/etc/my. cnf
Innodb_data_home_dir =/var/lib/mysql
Innodb_data_file_path = ibdata1: 1G; ibdata2: 200 M: autoextend
Innodb_file_per_table

Enable exclusive tablespace, and specify the size of ibdata1 to 1 GB, and the size of ibdata2 to 200 MB, which is automatically expanded.

6) start MySQL

# Service mysqld start

7) import data

Import the exported database to the database.

# Mysql-u root-p </tmp/all-database.dump

When the exclusive tablespace is enabled, ibdata1 is not required because the following data is stored in ibdata1.

Metadata of the InnoDB table
Buffer
UNDO log