2

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. – Ahmad Abuhasna Jun 20 '16 at 7:08
3

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.
|improve this answer|||||
  • 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 Jun 21 '16 at 3:49
  • You must dump all innodb tables before deleting ibdata1. (And reload.) – Rick James Jul 1 '16 at 3:48
  • Even file_per_table tables have some info in ibdata1. – Rick James Jul 1 '16 at 3:49
0

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

|improve this answer|||||
  • Only 1 step is necessary. And it does not shrink ibdata1. – Rick James Jul 1 '16 at 3:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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