How to cleanup InnoDB storage

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

Problem

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: http://bugs.mysql.com/bug.php?id=1341.

Solution

  • 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:
    innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
  • 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.
About text formats

Full HTML

  • 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].
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.