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.
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.
Last edited: