I was importing a database dump – almost 1GB worth:
mysql -uroot -ppassword < mysqldump.sql
When I received warnings that the hard drive was almost full. I panicked and Ctrl-Ced the import. Twice.
Looking at my DB I can see that the import was canceled. However when I look in /var/lib/mysql/
I can see ibdata1
is still too big – I guess it still has all the imported data in it. It's about twice the size it should be for the data available in the DB.
I ran mysqlcheck -optimize -A
but if anything it made the ibdata1
file larger.
How can I clear this orphaned data out? I have about 100MB left on the hard drive …
Best Answer
This is a known bug in MySQL: 1341. It's been going on for 8 years now. You might want to try a workaround, posted on StackOverflow:
That
ibdata1
isn't shrinking is a particularly annoying feature of MySQL. Theibdata1
file canĀ“t actually be shrunk unless you delete all databases, remove the files and reload a dump.But you can configure MySQL so that each table, including its indexes, is stored as a separate file. In that way
ibdata1
will not grow as large.It was a while ago I did this. However, to setup your server to use separate files for each table you need to change
my.cnf
in order to enable this:http://dev.mysql.com/doc/refman/5.5/en/multiple-tablespaces.html
As you want to reclaim the space from
ibdata1
you actually have to delete the file:mysqldump
of all databases, procedures, triggers etcibdata1
andib_log
filesWhen you start MySQL in step 5 the
ibdata1
andib-log
files will be recreated.Now you're fit to go. When you create a new database for analysis, the tables will be located in separate
ibd*
files, not inibdata1
. As you usually drop the database soon after, theibd*
files will be deleted.http://dev.mysql.com/doc/refman/5.1/en/drop-database.html
Or, if you want to keep the database running whili you do this, you could try this:
Enable the innodb_file_per_table option without shutting down the database. The idea is:
A detailed article can be found here.