26

I've created some very large databases and have since dropped a few. I've noticed my disk space has not recovered as much as I had expected. For instance, the last database I added actually used up all of my free space and aborted, so I dropped that schema. Before this I had 12.4 GB free, now I only have 7.52 GB free.
What's going on here? How do I get my ~5 GB back?

VenerableAgents
  • 645
  • 1
  • 8
  • 16
  • 1
    InnoDB or MyISAM tables? – bot403 May 12 '11 at 20:14
  • 1
    http://crazytoon.com/2007/04/03/mysql-ibdata-files-do-not-shrink-on-database-deletion-innodb/ – Brad May 12 '11 at 20:16
  • possible duplicate of [How to shrink/purge ibdata1 file in MySQL](http://stackoverflow.com/questions/3456159/how-to-shrink-purge-ibdata1-file-in-mysql) – gpoo Feb 06 '15 at 19:03

1 Answers1

24

From http://dev.mysql.com/doc/refman/5.1/en/innodb-data-log-reconfiguration.html:

Currently, you cannot remove a data file from the tablespace. To decrease the size of your tablespace, use this procedure:

  1. Use mysqldump to dump all your InnoDB tables.

  2. Stop the server.

  3. Remove all the existing tablespace files, including the ibdata and ib_log files. If you want to keep a backup copy of the information, then copy all the ib* files to another location before the removing the files in your MySQL installation.

  4. Remove any .frm files for InnoDB tables.

  5. Configure a new tablespace.

  6. Restart the server.

  7. Import the dump files.

Innodb creates a filesystem (the "tablespace") within the data files themselves. It never "shrinks" the data files when data is removed, since the reorganization of the data within the file could be costly (there's no guarantee that the data removed was at the end, or even contiguous). By recreating the database as described above, it makes the file as large as necessary for all the data, but no larger.

Community
  • 1
  • 1
Chris Morgan
  • 2,080
  • 15
  • 19
  • 8
    A more helpful version of the same answer: http://stackoverflow.com/questions/3456159/how-to-shrink-purge-ibdata1-file-in-mysql/3456885#3456885 – Tgr Dec 06 '12 at 17:31
  • Here is the correct link for the answer provided: http://dev.mysql.com/doc/refman/5.1/en/innodb-resize-system-tablespace.html – Travis Oct 13 '15 at 19:01