MySQL - Converting to Per Table Data File for InnoDB

Issue with shared InnoDB /var/lib/mysql/ibdata1 storage
InnoDB tables currently store data and indexes into a shared tablespace (/var/lib/mysql/ibdata1). Due to the shared tablespace, data corruption for one InnoDB table can result in MySQL failing to start up on the entire machine. Repairing InnoDB corruption can be extremely difficult to perform and can result in data loss for tables that were not corrupted originally during that repair process.

Since MySQL 5.5 will be using InnoDB as the default storage engine, it is important to consider the consequences of continuing to utilize the shared tablespace in /var/lib/mysql/ibdata1Changing to per-table tablespace with innodb_file_per_table

As an option to resolve the issue, MySQL has a configuration variable called innodb_file per_table. To use this variable, the following could be placed into /etc/my.cnf to convert InnoDB to a per table file for each InnoDB engine table:

innodb_file_per_table=1

After adding the line, MySQL would need to be restarted on the machine.
The result for using that line in /etc/my.cnf would cause any databases after the line is added to create .idb files in /var/lib/mysql/database/ location. Please note that the shared tablespace will still hold internal data dictionary and undo logs.

Converting old InnoDB tables
Any old databases with InnoDB tables set to previously share the tablespace in ibdata1 will still be using that file, so those old databases would need to be switched to the new system. The following command in MySQL CLI would create a list of InnoDB engine tables and a command to run for each to convert them to the new innodb_file_per_table system:

select concat('alter table ',TABLE_SCHEMA ,'.',table_name,' ENGINE=InnoDB;') as command FROM INFORMATION_SCHEMA.tables where table_type='BASE TABLE' and engine = 'InnoDB';

An example for Roundcube on my test machine shows the following return upon running the prior command:

alter table roundcube.cache ENGINE=InnoDB;
alter table roundcube.contacts ENGINE=InnoDB;
alter table roundcube.identities ENGINE=InnoDB;
alter table roundcube.messages ENGINE=InnoDB;
alter table roundcube.session ENGINE=InnoDB;
alter table roundcube.users ENGINE=InnoDB;

You would then simply need to issue the commands noted by MySQL CLI to then covert each table to the new innodb_file_per_table format.

Please note that these commands would only need to be run in MySQL command line for the conversion.

You can use the following script:

#!/bin/bash
MYSQL="$(which mysql)"
MYSQLUSER="root"
MYSQLPASS="mypassword"
# no need to change anything below...
#####################################################
TBLS=$(mysql -u $MYSQLUSER -p$MYSQLPASS -Bse "select concat(TABLE_SCHEMA ,'.',table_name) as tbl FROM INFORMATION_SCHEMA.tables where table_type='BASE TABLE' and engine = 'InnoDB';")
for tbl in $TBLS
do
echo "Converting table $tbl"
mysql -u $MYSQLUSER -p$MYSQLPASS -Bse "alter table $tbl ENGINE=InnoDB;"
done

Possible Issues for Converting Old InnoDB Tables
1. Possible system load might occur during the conversion
2. Possible issues with drive space filling up for the conversion