How to setup innodb_file_per_table in running mysql server with databases

1) MySQLDump all databases into a SQL text file (call it SQLData.sql)

2) Drop all databases (except mysql schema, phpmyadmin/mysql databases)

3) Stop mysql

4) Add the following lines to your /etc/my.cnf

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

Note : Whatever your set for innodb _ buffer _ pool _ size, make sure innodb _ log _ file _ size is 25% of innodb _ buffer _ pool _ size

5) Delete ibdata1, ib _ logfile0 and ib _ logfile1

At this point, there should only be the mysql schema in /var/lib/mysql

6) Restart mysql

This will recreate ibdata1 at 10MB, ib _ logfile0 and ib _ logfile1 at 1G each

7) Reload SQLData.sql into mysql to restore your data

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

Now 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.