Immigrate to file-per-table mode in MySQL 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.
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:\&gt; net stop mysql
C:\&gt; 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';" &gt; C:\ConvertInnoDBToInnoDB.sql
notepad C:\ConvertInnoDBToInnoDB.sql


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

mysql&gt; source C:\ConvertInnoDBToInnoDB.sql


Give it a Try !!!