Solved MySQL ibdata1 excessive size

[JaredBusch]

I have a FreePBX 14 box (used to be FreePBX 13 and was upgraded months ago) that is sending disk space alerts.

Looking into things, the file /var/lib/mysql/ibdata1 is growing out of hand. 13GB and growing.

[[email protected] ~]$ sudo ls -la /var/lib/mysql/ibdata1 
-rw-rw----. 1 mysql mysql 13054771200 Aug 11 14:48 /var/lib/mysql/ibdata1

Google tells me this has something to do with the InnoDB settings, but the only solution I found to fix it involved dropping databases and restoring them.

[JaredBusch]

http://www.pc-freak.net/blog/fix-mysql-ibdata-file-size-ibdata1-file-growing-large-preventing-ibdata1-eating-disk-space/

[Emad R]

@jaredbusch

I remember php my admin had alot of GUI options for optimizing DB and flushing it.

Can you recreate the DB somewhere locally, like VM, then connect to it via mysql workbench ? this way you can safely try solutions:

https://dba.stackexchange.com/questions/57250/optimize-a-mysql-database-using-mysql-workbench

[Obsolesce]

Full export via CLI then drop DB and import? Is that what the fix is? Seems like there should be a way to clean it up without doing that if 8 understand correctly.

[JaredBusch]

http://www.pc-freak.net/blog/fix-mysql-ibdata-file-size-ibdata1-file-growing-large-preventing-ibdata1-eating-disk-space/

[matteo nunziati]

@jaredbusch this is a bit old but maybe can help.
Unfortunately it seems there is no solution to stop the file to grow.

[jmoore]

@obsolesce I think that is what I had to do a couple years ago once. No idea why it started doing it and couldnt figure out any other way.