Immigrate to file-per-table mode in MySQL

Question

I have 20 MySQL databases and in each there are about 20 tables. All the data is saved in a file called ibData1. Now I discovered about the file per table mode but if I change the value in the my.ini file what would happen?

How can I immigrate to file-per-table mode and change all the data structure?

I prefer to have a directory for each Database where each table is saved in a different file.

Answer 1

This is so funny. Yesterday, someone asked how to do the opposite and I answered that question : Migrate from innodb_file_per_table to off in MySQL

Here the steps for what you want

STEP #1

Set innodb_file_per_table to 0 in my.ini

[mysqld]
innodb_file_per_table = 1

STEP #2

Login in Command Prompt in Administrator mode and run

C:\> net stop mysql
C:\> net start mysql

STEP #3

Convert every InnoDB table to InnoDB again

set MYSQLUSER=root
set MYSQLPASS=rootpass
mysql -u%MYSQLUSER% -p%MYSQLPASS% -ANe"SELECT CONCAT('ALTER TABLE ', table_schema, '.', table_name, ' ENGINE=InnoDB;') FROM information_schema.tables WHERE engine='InnoDB';" > C:\ConvertInnoDBToInnoDB.sql
notepad C:\ConvertInnoDBToInnoDB.sql

Once you view the script and are satisfied, login to mysql and run this

mysql> source C:\ConvertInnoDBToInnoDB.sql

Give it a Try !!!

• Melvyn: This method seems incomplete. I need to keep the big ibdata1, cause moving it outof the way and having it replaced with a blank ibdata1/iblog causes "table not found" errors, as the metadata is still in the old ibdata1 you used to move your data out to the files. You've got a more complete method in another post and should probably augment this answer with these caveats.