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?

  • if you are using MySQL 5.6.6 or higher then innodb_file_per_table is enabled by default. Commented Jun 20, 2016 at 7:08

2 Answers 2


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.
  • With MyISAM tables, I can delete the ibdata1 and I still have the data. If I put the database in innodb_file_per_table then delete the ibdata1 (I lose all the data in my innodb_per_file tables. So their is still a reliance on ibdata for innodb - even in per file table mode.
    – Henry
    Commented Jun 21, 2016 at 3:49
  • You must dump all innodb tables before deleting ibdata1. (And reload.)
    – Rick James
    Commented Jul 1, 2016 at 3:48
  • Even file_per_table tables have some info in ibdata1.
    – Rick James
    Commented Jul 1, 2016 at 3:49

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

  • Only 1 step is necessary. And it does not shrink ibdata1.
    – Rick James
    Commented Jul 1, 2016 at 3:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.