MySQL - Changing from system tablespace to file per table The MySQL database I am working on has the InnoDB tables and indexes stored in the system tablespace which is now > 20Gb. Some years ago I managed a MySQL database that made use of a file per table and looking to the future this seems the way to go. The documentation says that this can be done by setting “inno_file_per_table = 1” in my.cnf. Obviously I would need to back up and reload the data into the separate tables but the procedures I have seen are not that clear. Besides my  database schema there are also schemas present for information_schema, mysql and performance schema which I suspect come into the equation. MySQL is running under Mint Linux and although I have seen an answer to this question under Windows  I am not 100% confident on translating this to a Linux OS. What is the best way to achieve this change. It is acceptable at this stage for there to be a few hours downtime.

Thank you
STEP #1

Add the option to my.cnf

[mysqld]
innodb_file_per_table = 1


STEP #2

If you are running MySQL 5.6, login to mysql and run 

mysql&gt; SET GLOBAL innodb_file_per_table = 1;


If you are not running MySQL 5.6, restart mysqld

# service mysql restart


STEP #3

Write a script to create NULL ALTER TABLE commands

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p ${MYSQL_PASS}"

SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'"
SQL="${SQL} ORDER BY (data_length+index_length)"
mysql ${MYSQL_CONN} -ANe"${SQL}" &gt; Extract_InnoDB_From_ibdata1.sql


STEP #4

View the script

less Extract_InnoDB_From_ibdata1.sql


If you are OK with it, then  run it

mysql ${MYSQL_CONN} &lt; Extract_InnoDB_From_ibdata1.sql


CAVEAT

This will not shrink ibdata1. It merely extracts all InnoDB tables from it. ibdata1 will no longer grow beyond the 20GB due to the tables themselves.

If you want to shrink ibdata1, please refer to my post How do I shrink the innodb file ibdata1 without dumping all databases?

GIVE IT A TRY !!!