The dreaded InnoDB "ibdata1" slush file

[feldon27]

So MySQL stores all innoDB tables in a single slush file called "ibdata1" which NEVER shrinks despite the optimization or even deletion of tables.

In 2010, Tristan posted a thread "innodb_file_per_table - Converting to Per Table Data for InnoDB" advising cPanel users to change a setting in their MySQL config to store innodb tables as individual files. Without this setting this file just perpetually grows with ABSOLUTELY no way to shrink it.

After one follows the ALTER TABLE steps as described by Tristan, the ibdata1 file does not go away, and indeed you've now duplicated all the innodb data on your server, eating up twice as much space that can never be cleaned up. Thus far, no thread on these forums has ever addressed the question of what server operators with existing databases are supposed to do to reclaim this space. All threads on the topic warn against deleting ibdata1 and/or advise hiring a MySQL expert.

Can we please have some instructions on how to safely migrate away from using the ibdata1 file?

Also can we please see this setting enabled by default in the MySQL config file? Having all innodb tables in a single ever-growing file seems an particularly dangerous setting on a shared host situation and yet it's been this way on cPanel for at least 7 years.

If I do mysqldump of every innodb table on the server, delete the ibdata1 file, and then restore those tables, will that catch everything? Can I then safely delete the ibdata1 file? Are there other important things in the ibdata1 file that will be lost? My ibdata1 file is currently 34GB. Actual useful data in that file is less than 10GB. I have enough space on the server to backup all those tables, but I do not have enough space on the server to keep a full backup of the ibdata1 file AND the extra innodb files.

Please advise.

 

[feldon27]

I have found two articles that explain how to completely backup all your innodb tables, either delete or regenerate the ibdata1 file, and then restore the data:

windows - Howto: Clean a mysql InnoDB storage engine? - Stack Overflow

How to shrink an ibdata1 file with minimal MySQL downtime | e r i k i m h d o t c o m

Both sound somewhat scary, but what's the alternative? I have ghost innodb tables eating up 35GB on my server.

 

[cPanelMichael]

Hello

The following third-party URL is likely the best guide on how to do this:

MySQL innodb not releasing disk space after deleting data rows from table - Stack Overflow

If you would like to see a change in the default behavior, please ensure you submit a feature request via:

Submit A Feature Request

Thank you.

 

[feldon27]

Thank you for the link!

As for the request to add a single entry of "innodb_file_per_table=1" to my.cnf, well if it hasn't happened after 7 years, how likely is it to happen now? Also, isn't the MySQL -> MariaDB transition right around the corner? Hopefully this setting is defauled on day 1 with MariaDB and then we don't have to worry about it going forward.

From what I've read, on a shared server with multiple domains, there is absolutely no advantage to innoDB using a shared file. I only found huge disadvantages. For one thing, if the ibdata1 file is corrupted in a crash, then data recovery of ALL innoDB tables on the entire server is unlikely. That alone is a reason to NEVER leave the server set to use 1 file.

 

[kernow]

innodb_file_per_table is by default ON since Mysql 5.6.6