Overview

The InnoDB data format is a data storage engine, which provides greater data reliability and performance enhancements over the older MyISAM format. As of MySQL 5.5, InnoDB is also now the default data storage engine.

By default, InnoDB stores all table data in the system tablespace (/var/lib/mysql/ibdata1), resulting in one large, monolithic file. Seperating this out into a “per table” approach will reduce the chances of corruption, as well as increasing the flexibility for transferring tables and running file based backups.

Instructions

Warning! Make sure your database has been backed up first before making any changes. Especially as you are making low level changes, this is critical.

  1. Backup your database. This is important enough that I’ve repeated it again!
  2. Edit /etc/my.cnf and insert the following in the [mysqld] section:
    innodb_file_per_table=1
  3. Restart MySQL:
    /etc/init.d/mysqld restart
  4. Login to MySQL and issue the following: 
    SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', table_name, ' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE' AND engine = 'InnoDB';
  5. This will call generate a list of ALTER TABLE commands, which you will then need to run in order to convert to Per Table storage.
    Note: This won’t actually free up any disk space already allocated to /var/lib/mysql/ibdata1

  6. Monitor the outcome of all ALTER commands to ensure they have been run correctly.

MySQL Documentation Reference: https://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html

Was this article helpful?

Related Articles