1

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.

Improve this question

1 Answer 1

Reset to default
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 !!!

Improve this answer
1
  • 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.
    – Melvyn
    Commented Mar 27, 2015 at 17:38

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.