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 :
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