Years ago, MySQL started allowing InnoDB to possess separate tablespace per table instead of a massive unified ibdata1 file. Tablespace is basically a logical storage area that contains table information – references, if you will. Every table, regardless of the storage engine, has one, and without it the table will be unrecognizable to MySQL. MyISAM stores this data on a per-table basis already, and InnoDB is capable of doing this as well. But, a while back it was common (and required) for InnoDB tablespace to be stored in a single metaphorically-speaking conglomerate of a file called ibdata1. There are a few issues with this implementation:
- Can use a lot of disk space
- Some table operations (such as OPTIMIZE) are slower, and can continually increase the size of ibdata1
- A single corruption within ibdata1 can render all InnoDB tables inaccessible
- Likewise, restoring individual InnoDB tables from raw files (such as a backup) is typically impossible
Nowadays it’s generally recommended to store InnoDB data in individual files. All cPanel servers since the early support of MySQL 5.5 use innodb_file_per_table by default, as do all new MySQL 5.6 installations unless this feature is specifically disabled. Enabling it is easy – simply add the following line to /etc/my.cnf and restart MySQL:
BUT, if you already have databases containing InnoDB tables, you’re not quite off the hook yet. Only new tablespace will be written there, leaving existing space stuck in ibdata1. Fortunately, addressing this is rather easy, but there are some gotchas. You’re typically looking at two possible solutions:
A) Do an in-place conversion, which will leave ibdata1 intact (aka, at the same size) while tablespace is actually stored in individual files. This can be very wasteful. If your ibdata1 file is massive, you’re going to be stuck with it, as it is currently NOT possible to reliably shrink it unless you use solution B below. I’ll point out that there are proofs of concept out there for truncating ibdata1 to a certain size, but no one recommends doing it on a production server.
I’m not going to into detail about the conversion process, because there is already a step-by-step guide for this provided on the cPanel forums.
After converting tablespace to per-table files, while those tables will no longer rely on the unified storage, you still can’t simply delete or truncate ibdata1. You will hose InnoDB.
B) Dump and re-import InnoDB tables. This is the more scalable and preferred method, however, if you have a ton of InnoDB data, it could result in loss of data written to the tables between the time you dump and re-import. Doing this basically allows you to to get rid of the ibdata file, which will be recreated, but at a much smaller default size.
This post will focus more on option #2, which is the process of exporting and re-importing InnoDB tables and dropping dependency on ibdata1.
Before you begin, I’d highly suggest making a backup of your MySQL data. Either dump all the databases, or stop MySQL and create a copy of the entire folder.
Update: It has been mentioned in the comments that when you switch to file per table, you should also update the open_files_limit setting in my.cnf to at least the number of InnoDB tables you have on your system, or preferably higher to accommodate growth.
Step 1: Dump all InnoDB Tables
We’re basically going to export all the InnoDB tables. As mentioned, if you have a lot of data, you may lose anything written to the tables between the time they are exported and the time they are imported. If this is a concern, you may want to consider starting InnoDB in recovery mode which will essentially make the InnoDB tables read-only (MyISAM tables are not affected). To do this, edit /etc/my.cnf and add:
innodb_force_recovery = 4
And restart MySQL. Keep in mind again that any transaction writing to InnoDB tables will be blocked, which could render some applications reliant on them to be non-functional while recovery mode is active.
There are several methods for exporting InnoDB data, but we have a handy-dandy script that will do all this for you. Please make sure your /home folder has at least as much free space as the size of your MySQL data directory. You can find the script here, called innodb_export_import.py:
Take note of the output of the script which will give you the name of the folder containing the database dumps. We’ll use this later on.
Step 2: Adjust MySQL Configuration and Remove ib*
If you started MySQL with innodb_force_recovery above 0, make sure to disable this again. Add the following to /etc/my.cnf:
Then in /var/lib/mysql (or whatever your MySQL datadir is), move the following files somewhere else. I would not suggest deleting them just yet:
Now start MySQL. The above files will be recreated, but should be much smaller.
Step 3: Re-import data
We’re now going to re-import all the data that we exported previously. If you used our script to the export, run it again, passing it the name of the directory you were given in step 1:
./innodb_export_import.py –import –dir=/path/to/export/dir
Our script will also move the existing .ibd files out the way in case you’ve either already attempted the conversion or are dealing with a corrupted InnoDB instance, then recreate the tables from the dumps.
Step 4: Test
Our script will also to a basic test to make sure all InnoDB tables are accessible. To run:
You can review the resulting log file, displayed at the end of the process.