InnoDB per-table tablespaces – split ibdata1 to smaller chunks

Today I had to import 3GB of InnoDB tables in MySQL. Unfortunately, while importing the server run out of disk space – which caused whole server to grind to a halt. Naively I tried to delete imported data to free up space… I was in for an unpleasant surprise.

By default when MySQL uses InnoDB engine it stores most of the information in single file called ibdata1. One downside is that once ibdata1 file grows it cannot shrink – even if you delete all InnoDB tables. For some reason MySQL is set to use single file instead of per-table tablespaces similar to MyISAM.

Enabling per-table tablespaces is easy just add innodb_file_per_table to my.cnf file. Problem is that all newly created tables, only new tables, will be in separate files. It seems that there’s no easy way to convert old tables and reclaim the space taken by ibdata1. There are 3 ways and two are basically export-drop-delete-import type of solutions:

1. Convert all InnoDB tables to MyISAM
2. Export only InnoDB tables, drop them, delete ibdata1 and import InnoDB tables.
3. Export all databases, delete ibdata1 and import everything back.

I choose option 2 because I luckily had only 40 InnoDB tables and much more using MyISAM. For details on how to apply each solution and down/up sides of each read MySQL: Reducing ibdata1.

Share Article

Follow Us

Return to Previous Page