How to Convert InnoDB to innodb_file_per_table and Shrink ibdata1
- Written by Vanessa Vasile
- Published in Howto, MySQL, Scripts
- 12 Comments
- Permalink
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:
innodb_file_per_table
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.
http://www.liquidweb.com/kb/how-to-back-up-mysql-databases-from-the-command-line/
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:
https://github.com/thecpaneladmin/innodb-tools
To use:
./innodb_export_import.py –export
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:
innodb_file_per_table
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:
- ibdata1
- ib_logfile*
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:
./innodb_export_import.py –verify
You can review the resulting log file, displayed at the end of the process.
12 Comments
Is this (your script) something that can be done on any MySQL database in virtually any environment? We are running a Windows 2003 server that hosts an in-house web app with a 128GB ibdata1 file. We want to shrink that file obviously, and it seems to make sense to convert to the innodb_file_per_table method. We don’t have a DB admin, and I usually have to gently stumble through upgrades (with multiple backups available).
Since the script is Python and uses Windows-compatible classes, it probably will work on Windows, but has not been tested. You might want to copy everything to another server and test it first to make sure it works. Depending on how many tables you have, you can also probably just do this manually by dumping the database, removing the ib* files, and re-importing after enabling FPT. That’s essentially what this script does, it just automates the process.
I came across this yesterday after realizing that I had a 22GB ibdata1 file which is causing multiple issues like incremental backups are backing up the full 22GB file instead of individual files, which would make the backups a lot smaller and easier to restore. Not to mention that if the one file becomes corrupted then ALL the databases are going to fail.
I did notice one thing that you did not mention though… When I woke up this morning the database was not accessible! I knew it had something to do with the change I had made yesterday, but not sure why. After doing some research I saw that there were other settings that need to be taken in to consideration like:
innodb_open_files – Should be set to a number higher than the number of tables in all databases (to be on the safe side) The default is 300, I believe. My databases have around 240 tables each, so this crashed my server very quickly. I set it to 3000, but may actually set it a lot higher for future expansion.
open_files_limit – I set this to the same as the innodb_open_files just in case they are somehow related.
# —–
innodb_file_per_table=1
innodb_open_files=10000
open_files_limit=10000
There may be other settings to consider, but these fixed my issue earlier today. I had been thinking about building a new server with all SSD drives, so I will be doing that and then migrating the tables over to there with these new settings on the new server. I might as well since I will have to rebuild all the tables to take advantage of the setting change. 🙂
Thanks for the article.
Thanks for the feedback. I’ll update the article shortly to mention the open file limit. I haven’t heard of this being a common issue but I can see how it would cause a problem if the setting was left at default values.
https://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html
It mentions under the third “Potential Disadvantages of File-Per-Table Tablespaces’ bullet in the link above that ” mysqld must keep one open file handle per table, which may impact performance if you have numerous tables in file-per-table tablespaces.”
I am assuming that means one filer per InnoDB table across all databases in the system. When I was getting the errors yesterday it was complaining about tables in databases that I haven’t used in a while.
Thanks very much for the excellent article and script. They worked like a charm. Small note: –export –path=~username/path does not expand to the user home directory, and using the same path on import gets confused.
Hi Vanessa,
Thanks for sharing, it’s really appreciated because R1Soft do really bad job with InnoDB restore.
I’m trying to export InnoDB tables with your python script on my cPanel server with MariaDB10.1.13 but I have those errors:
Error dumping table *******_*******.******
Error dumping table *******_*******.******
Error dumping table *******_*******.******
SUMMARY:
Tables Exported: 0
Tables Failed: 22284
Databases Total: 503
Tables Total: 22284
Do you have an advise for me ?
If dump_table def i made print output for debug:
The output is:
Error dumping table roundcube.cache
mysqldump: unknown option ‘–add-drop-trigger’
I comment those lines:
if mysql_version >= 56:
dump_opts = ‘–add-drop-trigger’ # Only supported in mysqldump for 5.6
Please note the script is not tested with MariaDB 10. We are working on this.
Hi Vanessa,
Do you have any eta for this compatibility ?
Hi Quentin, the latest version of the script in Github supports MariaDB 10+
Pingback: Shrinking mysql ibdata1 file - adhocshare