Converting from InnoDB tables to innodb_file_per_table

Question

I run MySQL and I have 7 schemas, 6 of the 7 schemas are MyISAM.

In one of my schemas I have 20 tables that are InnoDB, the rest are MyISAM.

I really want to convert those 20 tables over to file_per_table.

I read some old documentation on how to do this, but I'm wondering if this approach will work.

1. Export the foreign keys off the 20 tables.
2. Drop the foreign keys.
3. Convert the tables to MyISAM.
4. Stop MySQL.
5. Change the my.cnf/ini to put MySQL in innodb_file_per_table mode.
6. Delete the idbdata files.
7. Start MySQL.
8. Switch those tables back to InnoDB (which should be in file mode now).
9. Re-apply the foreign keys.

Am I missing any steps? Or am I making a mistake somewhere?

Answer 1

The process you mentioned works fine but it takes much amount of time if the tables are bigger in size Here the way you can do it quickly .but it requires some down time depends on the table size .

1. Take the full backup of 20 tables that are InnoDB.
2. Drop those 20 tables that are InnoDB
3. Stop mysql
4. Delete the ibdata files
5. Start the mysql services with innodb_file_per_table=1 in my.cnf
6. Restore those 20 tables that are InnoDB with taken backup.

Answer 2

Restart the mysql services with innodb_file_per_table=on in my.cnf and rename table to another database and again to original data base then it will create file per table option