When innodb_file_per_table is enabled, InnoDB stores data and indexes for each newly created table in a separate .ibd file instead of the system tablespace.

I have to summarize the steps in order to post all in one post:

1- use mysqldump to export your desired databases (call it SQLData.sql).

2- Drop all databases (except mysql schema)

3- service mysql stop

4- Add the following lines to /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

* Sidenote: Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.

5- rm -f /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile

6- service mysql start

* if mysql does not start it may be due to insufficient memory. Try to reduce innodb_buffer_pool_size and innodb_log_file_size occordingly.

7- Reload SQLData.sql into mysql

ibdata1 will grow but only contain table metadata. Each InnoDB table will exist outside of ibdata1.


Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table

- mytable.frm (Storage Engine Header)
- mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)

ibdata1 will never contain InnoDB data and Indexes anymore.

With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable; and the file /var/lib/mysql/mydb/ mytable.ibd will actually shrink.

#mysql #InnoDB #innodb_file_per_table #optimize_table