Immigrate to file-per-table mode in MySQL

Posted on

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

Give it a Try !!!

Leave a Reply

Your email address will not be published. Required fields are marked *