Reclaim disk space after drop database in mysql

mysql drop-database
VenerableAgents picture VenerableAgents · May 12, 2011 · Viewed 40.7k times · Source

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?

Answer

Chris Morgan picture Chris Morgan · May 12, 2011

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.

Related questions

Error in dropping a database in MySQL (can't rmdir '.\oro', errno: 41)

I can't delete a database from mysql. The error is like ERROR 1010 (HY000): Error dropping database (can't rmdir '.\oro', errno: 41) I tried to drop the database 'oro' using phpmyadmin and it also showing the same error message and alerts "…

Read More
Drop database return "Error dropping database errno: 66" in MySQL

Consider: DROP DATABASE db_name; ERROR 1010 (HY000): Error dropping database (can't rmdir './db_name', errno: 66) The problem is that I don't know where the file/directory is located - this file is missing in /usr/local/mysql/bin/... How …

Read More
"ERROR 2013 (HY000): Lost connection to MySQL server during query" while dropping database

I have an empty database that I cannot drop. Initially, it contained a table that I couldn't SELECT from. So I DROP'ed all tables from that database and tried to DROP the database without success : mysql> drop database my_…

Read More