How to cleanup InnoDB storage

Submitted by Jochus on Mon, 11/03/2013 - 21:33 | Posted in: Database


After deleting some tables/databases on my MySQL server, I noticed the ibdata1 file is never shrinking. After almost a year, my MySQL ibdata1 file became really too large.
More information on this issue:


  • Dump (e.g., with mysqldump) all databases into a .sql text file
    $ mysqldump -u root -p --all-databases > bigdump.sql
  • Drop all databases (except for mysql and information_schema)
  • Login to mysql and run 'SET GLOBAL innodb_fast_shutdown = 0;' (This will completely flush all remaining transactional changes from ib_logfile0 and ib_logfile1)
  • Shutdown MySQL
  • Add the following lines to /etc/my.cnf:
  • Delete ibdata* and ib_logfile*
  • Start MySQL
  • Import databases
    $ mysql -u root -p < bigdump.sql

Add new comment

The content of this field is kept private and will not be shown publicly.


  • Lines and paragraphs break automatically.
  • You can caption images (data-caption="Text"), but also videos, blockquotes, and so on.
  • Web page addresses and email addresses turn into links automatically.
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <bash>, <cpp>, <css>, <html5>, <java>, <javascript>, <php>, <sql>, <xml>. The supported tag styles are: <foo>, [foo].
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.