Need help? Call us on 1300 789 260

MySQL - Converting to Per Table Data for InnoDB

Difficulty: Advanced


  • Your own server (dedicated or virtual)
  • SSH login details


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.


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:
  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:

Was this article helpful? Yes No

Having trouble? We’re here to help!

We’ve built our company with a serious focus on quality service. Feel free to give us a call!

1300 789 260